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();
}