private void checkPrepareBindExecuteFetchDate(Connection connection) throws Exception { final String sql0 = "select cast(? as varchar(20)) as c\n" + "from (values (1, 'a'))"; final String sql1 = "select ? + interval '2' day as c from (values (1, 'a'))"; final Date date = Date.valueOf("2015-04-08"); final long time = date.getTime(); PreparedStatement ps; ParameterMetaData parameterMetaData; ResultSet resultSet; ps = connection.prepareStatement(sql0); parameterMetaData = ps.getParameterMetaData(); assertThat(parameterMetaData.getParameterCount(), equalTo(1)); ps.setDate(1, date); resultSet = ps.executeQuery(); assertThat(resultSet.next(), is(true)); assertThat(resultSet.getString(1), is("2015-04-08")); ps.setTimestamp(1, new Timestamp(time)); resultSet = ps.executeQuery(); assertThat(resultSet.next(), is(true)); assertThat(resultSet.getString(1), is("2015-04-08 00:00:00.0")); ps.setTime(1, new Time(time)); resultSet = ps.executeQuery(); assertThat(resultSet.next(), is(true)); assertThat(resultSet.getString(1), is("00:00:00")); ps.close(); ps = connection.prepareStatement(sql1); parameterMetaData = ps.getParameterMetaData(); assertThat(parameterMetaData.getParameterCount(), equalTo(1)); ps.setDate(1, date); resultSet = ps.executeQuery(); assertTrue(resultSet.next()); assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2)))); assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2)))); ps.setTimestamp(1, new Timestamp(time)); resultSet = ps.executeQuery(); assertTrue(resultSet.next()); assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2)))); assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2)))); ps.setObject(1, new java.util.Date(time)); resultSet = ps.executeQuery(); assertTrue(resultSet.next()); assertThat(resultSet.getDate(1), equalTo(new Date(time + TimeUnit.DAYS.toMillis(2)))); assertThat(resultSet.getTimestamp(1), equalTo(new Timestamp(time + TimeUnit.DAYS.toMillis(2)))); resultSet.close(); ps.close(); connection.close(); }
public ParameterMetaData getParameterMetaData() throws SQLException { try { return wrapped.getParameterMetaData(); } catch (SQLException e) { throw new UcanaccessSQLException(e); } }
public synchronized int getIndexOf( WebSocketMessageDTO criteria, List<Integer> opcodes, List<Integer> inScopeChannelIds) throws DatabaseException { try { String query = "SELECT COUNT(m.message_id) " + "FROM websocket_message AS m " + "LEFT OUTER JOIN websocket_message_fuzz f " + "ON m.message_id = f.message_id AND m.channel_id = f.channel_id " + "<where> AND m.message_id < ?"; PreparedStatement stmt = buildMessageCriteriaStatement(query, criteria, opcodes, inScopeChannelIds); int paramsCount = stmt.getParameterMetaData().getParameterCount(); stmt.setInt(paramsCount, criteria.id); try { return executeAndGetSingleIntValue(stmt); } finally { stmt.close(); } } catch (SQLException e) { throw new DatabaseException(e); } }
private void checkStatementExecuteQuery(Connection connection, boolean prepare) throws SQLException { final String sql = "select * from (\n" + " values (1, 'a'), (null, 'b'), (3, 'c')) as t (c1, c2)"; final Statement statement; final ResultSet resultSet; final ParameterMetaData parameterMetaData; if (prepare) { final PreparedStatement ps = connection.prepareStatement(sql); statement = ps; parameterMetaData = ps.getParameterMetaData(); resultSet = ps.executeQuery(); } else { statement = connection.createStatement(); parameterMetaData = null; resultSet = statement.executeQuery(sql); } if (parameterMetaData != null) { assertThat(parameterMetaData.getParameterCount(), equalTo(0)); } final ResultSetMetaData metaData = resultSet.getMetaData(); assertEquals(2, metaData.getColumnCount()); assertEquals("C1", metaData.getColumnName(1)); assertEquals("C2", metaData.getColumnName(2)); assertTrue(resultSet.next()); assertTrue(resultSet.next()); assertTrue(resultSet.next()); assertFalse(resultSet.next()); resultSet.close(); statement.close(); connection.close(); }
private boolean checkExistenceByQuery( PreparedStatement pstmt, BaseSchema baseSchema, String... params) throws SQLException { int paramIdx = 1; boolean result = false; if (baseSchema.getSchemaName() != null && !baseSchema.getSchemaName().isEmpty()) { pstmt.setString(paramIdx++, baseSchema.getSchemaName().toUpperCase()); } for (; paramIdx <= pstmt.getParameterMetaData().getParameterCount(); paramIdx++) { pstmt.setString(paramIdx, params[paramIdx - 1].toUpperCase()); } ResultSet rs = null; try { rs = pstmt.executeQuery(); while (rs.next()) { if (rs.getString(1).toUpperCase().equals(params[params.length - 1].toUpperCase())) { result = true; break; } } } finally { CatalogUtil.closeQuietly(rs); } return result; }
private void testParameterMetaData(Connection conn) throws SQLException { PreparedStatement prep = conn.prepareStatement("SELECT ?, ?, ? FROM DUAL"); ParameterMetaData pm = prep.getParameterMetaData(); assertEquals("java.lang.String", pm.getParameterClassName(1)); assertEquals("VARCHAR", pm.getParameterTypeName(1)); assertEquals(3, pm.getParameterCount()); assertEquals(ParameterMetaData.parameterModeIn, pm.getParameterMode(1)); assertEquals(Types.VARCHAR, pm.getParameterType(1)); assertEquals(0, pm.getPrecision(1)); assertEquals(0, pm.getScale(1)); assertEquals(ResultSetMetaData.columnNullableUnknown, pm.isNullable(1)); assertEquals(pm.isSigned(1), true); assertThrows(ErrorCode.INVALID_VALUE_2, pm).getPrecision(0); assertThrows(ErrorCode.INVALID_VALUE_2, pm).getPrecision(4); prep.close(); assertThrows(ErrorCode.OBJECT_CLOSED, pm).getPrecision(1); Statement stat = conn.createStatement(); stat.execute("CREATE TABLE TEST3(ID INT, NAME VARCHAR(255), DATA DECIMAL(10,2))"); PreparedStatement prep1 = conn.prepareStatement("UPDATE TEST3 SET ID=?, NAME=?, DATA=?"); PreparedStatement prep2 = conn.prepareStatement("INSERT INTO TEST3 VALUES(?, ?, ?)"); checkParameter(prep1, 1, "java.lang.Integer", 4, "INTEGER", 10, 0); checkParameter(prep1, 2, "java.lang.String", 12, "VARCHAR", 255, 0); checkParameter(prep1, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2); checkParameter(prep2, 1, "java.lang.Integer", 4, "INTEGER", 10, 0); checkParameter(prep2, 2, "java.lang.String", 12, "VARCHAR", 255, 0); checkParameter(prep2, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2); PreparedStatement prep3 = conn.prepareStatement("SELECT * FROM TEST3 WHERE ID=? AND NAME LIKE ? AND ?>DATA"); checkParameter(prep3, 1, "java.lang.Integer", 4, "INTEGER", 10, 0); checkParameter(prep3, 2, "java.lang.String", 12, "VARCHAR", 0, 0); checkParameter(prep3, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2); stat.execute("DROP TABLE TEST3"); }
private void testLikeIndex(Connection conn) throws SQLException { Statement stat = conn.createStatement(); stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))"); stat.execute("INSERT INTO TEST VALUES(1, 'Hello')"); stat.execute("INSERT INTO TEST VALUES(2, 'World')"); stat.execute("create index idxname on test(name);"); PreparedStatement prep, prepExe; prep = conn.prepareStatement("EXPLAIN SELECT * FROM TEST WHERE NAME LIKE ?"); assertEquals(1, prep.getParameterMetaData().getParameterCount()); prepExe = conn.prepareStatement("SELECT * FROM TEST WHERE NAME LIKE ?"); prep.setString(1, "%orld"); prepExe.setString(1, "%orld"); ResultSet rs = prep.executeQuery(); rs.next(); String plan = rs.getString(1); assertTrue(plan.indexOf(".tableScan") >= 0); rs = prepExe.executeQuery(); rs.next(); assertEquals("World", rs.getString(2)); assertFalse(rs.next()); prep.setString(1, "H%"); prepExe.setString(1, "H%"); rs = prep.executeQuery(); rs.next(); String plan1 = rs.getString(1); assertTrue(plan1.indexOf("IDXNAME") >= 0); rs = prepExe.executeQuery(); rs.next(); assertEquals("Hello", rs.getString(2)); assertFalse(rs.next()); stat.execute("DROP TABLE IF EXISTS TEST"); }
public java.sql.ParameterMetaData getParameterMetaData() throws SQLException { checkOpen(); try { return _stmt.getParameterMetaData(); } catch (SQLException e) { handleException(e); return null; } }
public ParameterMetaData getParameterMetaData() throws SQLException { Profiler profiler = _profilerPoint.start(); try { return _preparedStatement.getParameterMetaData(); } finally { profiler.finish(); } }
public ParameterMetaData getParameterMetaData() throws SQLException { String methodCall = "getParameterMetaData()"; try { return (ParameterMetaData) reportReturn(methodCall, realPreparedStatement.getParameterMetaData()); } catch (SQLException s) { reportException(methodCall, s); throw s; } }
@Test public void test() throws SQLException { JdbcTemplate template = new JdbcTemplate(); DataSource source = template.getDataSource(); Connection connection = source.getConnection(); PreparedStatement statement = connection.prepareStatement("insert into TM.POINT values (?,?,?,?,?)"); @SuppressWarnings("unused") ParameterMetaData metaData = statement.getParameterMetaData(); connection.close(); }
protected void showStatementState(String when, Statement s) throws SQLException { super.showStatementState(when, s); System.out.println(" getResultSetHoldability() " + rsHoldability(s.getResultSetHoldability())); if (s instanceof PreparedStatement) { PreparedStatement ps = (PreparedStatement) s; ParameterMetaData psmd = ps.getParameterMetaData(); System.out.println(" Parameter Count " + psmd.getParameterCount()); for (int i = 1; i <= psmd.getParameterCount(); i++) { System.out.println(" " + i + " type " + psmd.getParameterType(i)); } } }
private void resolveEnumValueMapper(PreparedStatement st, int index) { if (enumValueMapper == null) { try { resolveEnumValueMapper(st.getParameterMetaData().getParameterType(index)); } catch (Exception e) { // because some drivers do not implement this LOG.debugf( "JDBC driver threw exception calling java.sql.ParameterMetaData#getParameterType; " + "falling back to ordinal-based enum mapping [%s] : %s", enumClass.getName(), e.getMessage()); treatAsOrdinal(); } } }
private void checkParameter( PreparedStatement prep, int index, String className, int type, String typeName, int precision, int scale) throws SQLException { ParameterMetaData meta = prep.getParameterMetaData(); assertEquals(className, meta.getParameterClassName(index)); assertEquals(type, meta.getParameterType(index)); assertEquals(typeName, meta.getParameterTypeName(index)); assertEquals(precision, meta.getPrecision(index)); assertEquals(scale, meta.getScale(index)); }
/** * Executes a prepared statement. * * @param stmt prepared statement * @param ctx query context * @return result * @throws QueryException query exception */ private NodeCache executePrepStmt(final PreparedStatement stmt, final QueryContext ctx) throws QueryException { // Get parameters for prepared statement final ANode params = (ANode) checkType(expr[1].item(ctx, input), NodeType.ELM); if (!params.qname().eq(E_PARAMS)) PARWHICH.thrw(input, params.qname()); try { final int placeCount = stmt.getParameterMetaData().getParameterCount(); // Check if number of parameters equals number of place holders if (placeCount != countParams(params)) PARAMS.thrw(input); else setParameters(params.children(), stmt); final boolean result = stmt.execute(); return result ? buildResult(stmt.getResultSet()) : new NodeCache(); } catch (final SQLException ex) { throw SQLEXC.thrw(input, ex.getMessage()); } }
@Test public void testPrepareBindExecuteFetchVarbinary() throws Exception { final Connection connection = ljs(); final String sql = "select x'de' || ? as c from (values (1, 'a'))"; final PreparedStatement ps = connection.prepareStatement(sql); final ParameterMetaData parameterMetaData = ps.getParameterMetaData(); assertThat(parameterMetaData.getParameterCount(), equalTo(1)); ps.setBytes(1, new byte[] {65, 0, 66}); final ResultSet resultSet = ps.executeQuery(); assertTrue(resultSet.next()); assertThat(resultSet.getBytes(1), equalTo(new byte[] {(byte) 0xDE, 65, 0, 66})); resultSet.close(); ps.close(); connection.close(); }
/** * Retrieves list of {@link WebSocketMessageDTO}, but loads only parts of the payload. * * @param criteria * @param opcodes * @param inScopeChannelIds * @param offset * @param limit * @param payloadPreviewLength * @return Messages that fulfill given template. * @throws DatabaseException */ public synchronized List<WebSocketMessageDTO> getMessages( WebSocketMessageDTO criteria, List<Integer> opcodes, List<Integer> inScopeChannelIds, int offset, int limit, int payloadPreviewLength) throws DatabaseException { try { String query = "SELECT m.message_id, m.channel_id, m.timestamp, m.opcode, m.payload_length, m.is_outgoing, " + "m.payload_utf8, m.payload_bytes, " + "f.fuzz_id, f.state, f.fuzz " + "FROM websocket_message AS m " + "LEFT OUTER JOIN websocket_message_fuzz f " + "ON m.message_id = f.message_id AND m.channel_id = f.channel_id " + "<where> " + "ORDER BY m.timestamp, m.channel_id, m.message_id " + "LIMIT ? " + "OFFSET ?"; PreparedStatement stmt; try { stmt = buildMessageCriteriaStatement(query, criteria, opcodes, inScopeChannelIds); } catch (SQLException e) { if (getConnection().isClosed()) { return new ArrayList<>(0); } throw e; } try { int paramsCount = stmt.getParameterMetaData().getParameterCount(); stmt.setInt(paramsCount - 1, limit); stmt.setInt(paramsCount, offset); stmt.execute(); return buildMessageDTOs(stmt.getResultSet(), true, payloadPreviewLength); } finally { stmt.close(); } } catch (SQLException e) { throw new DatabaseException(e); } }
private void checkPrepareBindExecuteFetch(Connection connection) throws SQLException { final String sql = "select cast(? as integer) * 3 as c, 'x' as x\n" + "from (values (1, 'a'))"; final PreparedStatement ps = connection.prepareStatement(sql); final ResultSetMetaData metaData = ps.getMetaData(); assertEquals(2, metaData.getColumnCount()); assertEquals("C", metaData.getColumnName(1)); assertEquals("X", metaData.getColumnName(2)); try { final ResultSet resultSet = ps.executeQuery(); fail("expected error, got " + resultSet); } catch (SQLException e) { assertThat(e.getMessage(), equalTo("exception while executing query: unbound parameter")); } final ParameterMetaData parameterMetaData = ps.getParameterMetaData(); assertThat(parameterMetaData.getParameterCount(), equalTo(1)); ps.setInt(1, 10); final ResultSet resultSet = ps.executeQuery(); assertTrue(resultSet.next()); assertThat(resultSet.getInt(1), equalTo(30)); assertFalse(resultSet.next()); resultSet.close(); ps.setInt(1, 20); final ResultSet resultSet2 = ps.executeQuery(); assertFalse(resultSet2.isClosed()); assertTrue(resultSet2.next()); assertThat(resultSet2.getInt(1), equalTo(60)); assertThat(resultSet2.wasNull(), is(false)); assertFalse(resultSet2.next()); resultSet2.close(); ps.setObject(1, null); final ResultSet resultSet3 = ps.executeQuery(); assertTrue(resultSet3.next()); assertThat(resultSet3.getInt(1), equalTo(0)); assertThat(resultSet3.wasNull(), is(true)); assertFalse(resultSet3.next()); resultSet3.close(); ps.close(); connection.close(); }
private void testSubquery(Connection conn) throws SQLException { Statement stat = conn.createStatement(); stat.execute("CREATE TABLE TEST(ID INT)"); stat.execute("INSERT INTO TEST VALUES(1),(2),(3)"); PreparedStatement prep = conn.prepareStatement( "select x.id, ? from " + "(select * from test where id in(?, ?)) x where x.id*2 <> ?"); assertEquals(4, prep.getParameterMetaData().getParameterCount()); prep.setInt(1, 0); prep.setInt(2, 1); prep.setInt(3, 2); prep.setInt(4, 4); ResultSet rs = prep.executeQuery(); rs.next(); assertEquals(1, rs.getInt(1)); assertEquals(0, rs.getInt(2)); assertFalse(rs.next()); stat.execute("DROP TABLE TEST"); }
@SuppressWarnings("deprecation") private void testUnsupportedOperations(Connection conn) throws Exception { PreparedStatement prep = conn.prepareStatement("select ? from dual"); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep).addBatch("select 1"); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .executeUpdate("create table test(id int)"); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .executeUpdate("create table test(id int)", new int[0]); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .executeUpdate("create table test(id int)", new String[0]); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .executeUpdate("create table test(id int)", Statement.RETURN_GENERATED_KEYS); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .execute("create table test(id int)"); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .execute("create table test(id int)", new int[0]); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .execute("create table test(id int)", new String[0]); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .execute("create table test(id int)", Statement.RETURN_GENERATED_KEYS); assertThrows(ErrorCode.METHOD_NOT_ALLOWED_FOR_PREPARED_STATEMENT, prep) .executeQuery("select * from dual"); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, prep).setURL(1, new URL("http://www.acme.com")); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, prep).setRowId(1, (RowId) null); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, prep) .setUnicodeStream(1, (InputStream) null, 0); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, prep).setArray(1, (Array) null); ParameterMetaData meta = prep.getParameterMetaData(); assertTrue(meta.toString(), meta.toString().endsWith("parameterCount=1")); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, meta).isWrapperFor(Object.class); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, meta).unwrap(Object.class); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).isWrapperFor(Object.class); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).unwrap(Object.class); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).createSQLXML(); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).createArrayOf("Integer", new Object[0]); assertThrows(ErrorCode.FEATURE_NOT_SUPPORTED_1, conn).createStruct("Integer", new Object[0]); }
@Override public MetaData[] getMetaData() throws DataLinkException { try { ParameterMetaData meta = stmt.getParameterMetaData(); int size = meta.getParameterCount(); MetaData[] metadata = new MetaData[size]; for (int i = 0; i < size; i++) { metadata[i] = new MetaData( i < params.length ? params[i] : null, SQLDataLinkFactory.getKind(meta.getParameterType(i + 1))); } return metadata; } catch (SQLException ex) { throw new DataLinkException(ex); } }
private void sendParameterDescription(Prepared p) throws IOException { try { PreparedStatement prep = p.prep; ParameterMetaData meta = prep.getParameterMetaData(); int count = meta.getParameterCount(); startMessage('t'); writeShort(count); for (int i = 0; i < count; i++) { int type; if (p.paramType != null && p.paramType[i] != 0) { type = p.paramType[i]; } else { type = PgServer.PG_TYPE_VARCHAR; } server.checkType(type); writeInt(type); } sendMessage(); } catch (Exception e) { sendErrorResponse(e); } }
public List<Venda> selecionarPorFunc(String nome) throws Exception { try { String sql; if (nome != null && !nome.isEmpty()) { sql = "SELECT * FROM venda v,funcionario f where v.fun_mat = f.fun_mat and fun_nome LIKE concat(?,'%') ORDER BY ped_num"; } else { sql = "SELECT * FROM venda ORDER BY ped_num"; } PreparedStatement ps = Conexao.getConexao().prepareStatement(sql); if (ps.getParameterMetaData().getParameterCount() > 0) { ps.setString(1, nome); } ResultSet rs = ps.executeQuery(); preencheLista(rs); return getLista(); } catch (Exception erro) { throw new Exception("Erro ao localizar Venda pelo Nome: " + erro.getMessage()); } }
// // Find all the methods for java.sql objects in the Connection which raise // SQLFeatureNotSupportedException. // private void connectionWorkhorse( Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList) throws Exception { vetSavepoint(conn, unsupportedList, notUnderstoodList); vetLargeObjects(conn, unsupportedList, notUnderstoodList); DatabaseMetaData dbmd = conn.getMetaData(); PreparedStatement ps = conn.prepareStatement("select * from sys.systables where tablename = ?"); ps.setString(1, "foo"); ParameterMetaData parameterMetaData = ps.getParameterMetaData(); ResultSet rs = ps.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); Statement stmt = conn.createStatement(); CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SET_RUNTIMESTATISTICS(0)"); ParameterMetaData csmd = cs.getParameterMetaData(); // // The vetObject() method calls all of the methods in these objects // in a deterministic order, calling the close() method last. // Inspect these objects in an order which respects the fact that // the objects are closed as a result of calling vetObject(). // vetObject(dbmd, unsupportedList, notUnderstoodList); vetObject(stmt, unsupportedList, notUnderstoodList); vetObject(csmd, unsupportedList, notUnderstoodList); vetObject(cs, unsupportedList, notUnderstoodList); vetObject(rsmd, unsupportedList, notUnderstoodList); vetObject(rs, unsupportedList, notUnderstoodList); vetObject(parameterMetaData, unsupportedList, notUnderstoodList); vetObject(ps, unsupportedList, notUnderstoodList); vetObject(conn, unsupportedList, notUnderstoodList); // No need to close the objects. They were closed by vetObject(). }
/** * 填充PreparedStatement * * @param pstmt * @param parameterElements * @return 返回下次填充的起始位置 * @throws SQLException * @throws ServiceInvocationException */ public static int fulfillPreparedStatement( PreparedStatement pstmt, List<Element> parameterElements, int startIndex) throws SQLException, ServiceInvocationException { ParameterMetaData parameterMetaData = pstmt.getParameterMetaData(); int preparedParamCount = parameterMetaData.getParameterCount(); int paramCount = parameterElements.size(); int currentParamIndex = 0; if (preparedParamCount > 0) { for (int i = 0; i < paramCount; i++) { currentParamIndex = i + startIndex; int type = parameterMetaData.getParameterType(currentParamIndex); Element element = parameterElements.get(i); String strValue = null; NodeList nodeList = element.getChildNodes(); for (int k = 0; k < nodeList.getLength(); k++) { Node node = nodeList.item(k); if (node.getNodeType() == Node.TEXT_NODE) { strValue = ((Text) node).getNodeValue(); } } switch (type) { case java.sql.Types.CHAR: case java.sql.Types.VARCHAR: case java.sql.Types.LONGVARCHAR: // case java.sql.Types.NCHAR: // case java.sql.Types.NVARCHAR: // case java.sql.Types.LONGNVARCHAR: pstmt.setString(currentParamIndex, strValue); break; case java.sql.Types.NUMERIC: case java.sql.Types.DECIMAL: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { if (strValue.indexOf(".") == -1) { Long theLong = Long.valueOf(strValue); pstmt.setBigDecimal(currentParamIndex, BigDecimal.valueOf(theLong)); } else { Double d = Double.valueOf(strValue); pstmt.setBigDecimal(currentParamIndex, BigDecimal.valueOf(d)); } } break; case java.sql.Types.BOOLEAN: case java.sql.Types.BIT: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { pstmt.setBoolean(currentParamIndex, Boolean.valueOf(strValue)); } break; case java.sql.Types.TINYINT: case java.sql.Types.SMALLINT: case java.sql.Types.INTEGER: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { pstmt.setInt(currentParamIndex, Integer.valueOf(strValue)); } break; case java.sql.Types.BIGINT: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { pstmt.setLong(currentParamIndex, Long.valueOf(strValue)); } break; case java.sql.Types.REAL: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { pstmt.setFloat(currentParamIndex, Float.valueOf(strValue)); } break; case java.sql.Types.FLOAT: case java.sql.Types.DOUBLE: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { pstmt.setDouble(currentParamIndex, Double.valueOf(strValue)); } break; case java.sql.Types.BINARY: case java.sql.Types.VARBINARY: case java.sql.Types.LONGVARBINARY: case java.sql.Types.BLOB: case java.sql.Types.CLOB: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { byte[] content = null; try { content = Base64.decodeBase64(strValue.getBytes("UTF-8")); } catch (Exception e) { e.printStackTrace(); } if (content == null) { pstmt.setNull(currentParamIndex, type); } else { pstmt.setBytes(currentParamIndex, content); } } break; case java.sql.Types.DATE: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { java.sql.Date sqlDate = java.sql.Date.valueOf(strValue); pstmt.setDate(currentParamIndex, sqlDate); } break; case java.sql.Types.TIME: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { java.sql.Time t = java.sql.Time.valueOf(strValue); pstmt.setTime(currentParamIndex, t); } break; case java.sql.Types.TIMESTAMP: if (StringUtils.isEmpty(strValue)) { pstmt.setNull(currentParamIndex, type); } else { String dt = strValue; if (dt.indexOf("T") >= 0) { // 如果是“YYYY-MM-DDThh:mm:ss”这种格式,则将T替换成空格 dt = dt.replace("T", " "); } java.sql.Timestamp ts = Timestamp.valueOf(dt); pstmt.setTimestamp(currentParamIndex, ts); } break; } } } return currentParamIndex + 1; }
private void testCasewhen(Connection conn) throws SQLException { Statement stat = conn.createStatement(); stat.execute("CREATE TABLE TEST(ID INT)"); stat.execute("INSERT INTO TEST VALUES(1),(2),(3)"); PreparedStatement prep; ResultSet rs; prep = conn.prepareStatement( "EXPLAIN SELECT COUNT(*) FROM TEST " + "WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID"); prep.setInt(1, 1); rs = prep.executeQuery(); rs.next(); String plan = rs.getString(1); trace(plan); rs.close(); prep = conn.prepareStatement( "EXPLAIN SELECT COUNT(*) FROM TEST " + "WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID"); prep.setInt(1, 1); rs = prep.executeQuery(); rs.next(); plan = rs.getString(1); trace(plan); prep = conn.prepareStatement( "SELECT COUNT(*) FROM TEST WHERE CASEWHEN(ID=1, ID, ID)=? GROUP BY ID"); prep.setInt(1, 1); rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertFalse(rs.next()); prep = conn.prepareStatement( "SELECT COUNT(*) FROM TEST WHERE CASE ID WHEN 1 THEN ID WHEN 2 THEN ID ELSE ID END=? GROUP BY ID"); prep.setInt(1, 1); rs = prep.executeQuery(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertFalse(rs.next()); prep = conn.prepareStatement("SELECT * FROM TEST WHERE ? IS NULL"); prep.setString(1, "Hello"); rs = prep.executeQuery(); assertFalse(rs.next()); assertThrows(ErrorCode.UNKNOWN_DATA_TYPE_1, conn) .prepareStatement("select ? from dual union select ? from dual"); prep = conn.prepareStatement("select cast(? as varchar) from dual union select ? from dual"); assertEquals(2, prep.getParameterMetaData().getParameterCount()); prep.setString(1, "a"); prep.setString(2, "a"); rs = prep.executeQuery(); rs.next(); assertEquals("a", rs.getString(1)); assertEquals("a", rs.getString(1)); assertFalse(rs.next()); stat.execute("DROP TABLE TEST"); }
/** Sets the given parameter Known subclasses: see PostgresStatement.execute */ protected void setObject(int i, Object param) throws SQLException { // See php/4358, php/43b8, php/43d8, and php/43p8. ParameterMetaData pmd = _preparedStmt.getParameterMetaData(); int type = pmd.getParameterType(i); switch (type) { case Types.OTHER: { // See php/43b8 String typeName = pmd.getParameterTypeName(i); if (typeName.equals("interval")) { _preparedStmt.setObject(i, param); } else { try { Class<?> cl = Class.forName("org.postgresql.util.PGobject"); Constructor<?> constructor = cl.getDeclaredConstructor(); Object object = constructor.newInstance(); Method method = cl.getDeclaredMethod("setType", new Class[] {String.class}); method.invoke(object, new Object[] {typeName}); method = cl.getDeclaredMethod("setValue", new Class[] {String.class}); method.invoke(object, new Object[] {param}); _preparedStmt.setObject(i, object, type); } catch (ClassNotFoundException e) { throw new SQLException(e); } catch (NoSuchMethodException e) { throw new SQLException(e); } catch (InvocationTargetException e) { throw new SQLException(e.getCause()); } catch (IllegalAccessException e) { throw new SQLException(e); } catch (InstantiationException e) { throw new SQLException(e); } } break; } case Types.DOUBLE: { // See php/43p8. String typeName = pmd.getParameterTypeName(i); if (typeName.equals("money")) { String s = param.toString(); if (s.length() == 0) { throw new IllegalArgumentException(L.l("argument `{0}' cannot be empty", param)); } else { String money = s; if (s.charAt(0) == '$') s = s.substring(1); else money = "$" + money; try { // This will throw an exception if not double while // trying to setObject() would not. The error would // come late, otherwise. See php/43p8. Double.parseDouble(s); } catch (Exception ex) { throw new IllegalArgumentException( L.l("cannot convert argument `{0}' to money", param)); } try { Class<?> cl = Class.forName("org.postgresql.util.PGmoney"); Constructor<?> constructor = cl.getDeclaredConstructor(new Class[] {String.class}); Object object = constructor.newInstance(new Object[] {money}); _preparedStmt.setObject(i, object, Types.OTHER); } catch (ClassNotFoundException e) { throw new SQLException(e); } catch (NoSuchMethodException e) { throw new SQLException(e); } catch (InvocationTargetException e) { throw new SQLException(e.getCause()); } catch (IllegalAccessException e) { throw new SQLException(e); } catch (InstantiationException e) { throw new SQLException(e); } break; } } // else falls to default case } default: _preparedStmt.setObject(i, param, type); } }
public ParameterMetaData getParameterMetaData() throws SQLException { return delegate.getParameterMetaData(); }
public ParameterMetaData getParameterMetaData() throws SQLException { this.assertPs(); return ps.getParameterMetaData(); }
public ParameterMetaData getParameterMetaData() throws SQLException { return statement.getParameterMetaData(); }