下面列出了java.sql.PreparedStatement#setObject ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* Helper method for *SetObject* tests.
* Insert data into the given PreparedStatement, or any of its subclasses, with the following structure:
* 1 - `id` INT
* 2 - `ot1` VARCHAR
* 3 - `ot2` BLOB
* 4 - `odt1` VARCHAR
* 5 - `odt2` BLOB
*
* @param pstmt
* @return the row count of inserted records.
* @throws Exception
*/
private int insertTestDataOffsetDTTypes(PreparedStatement pstmt) throws Exception {
pstmt.setInt(1, 1);
pstmt.setObject(2, testOffsetTime, JDBCType.VARCHAR);
pstmt.setObject(3, testOffsetTime);
pstmt.setObject(4, testOffsetDateTime, JDBCType.VARCHAR);
pstmt.setObject(5, testOffsetDateTime);
assertEquals(1, pstmt.executeUpdate());
if (pstmt instanceof CallableStatement) {
CallableStatement cstmt = (CallableStatement) pstmt;
cstmt.setInt("id", 2);
cstmt.setObject("ot1", testOffsetTime, JDBCType.VARCHAR);
cstmt.setObject("ot2", testOffsetTime);
cstmt.setObject("odt1", testOffsetDateTime, JDBCType.VARCHAR);
cstmt.setObject("odt2", testOffsetDateTime);
assertEquals(1, cstmt.executeUpdate());
return 2;
}
return 1;
}
public static void paramTest(String type, Object param1, Object[] param2,
Object[] param3, int[] out, ResultSet[] rs1) throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:default:connection");
try {
PreparedStatement pstmt = conn.prepareStatement(
"values(cast(? as " + type + "))");
if (param1 != null) {
pstmt.setObject(1, param1);
}
else {
pstmt.setNull(1, pstmt.getMetaData().getColumnType(1));
}
rs1[0] = pstmt.executeQuery();
} finally {
conn.close();
}
//java.lang.reflect.Array.set(param2, 0, param1);
param2[0] = param1;
out[0] = 5;
}
/**
*
* @param sql
* @param params
* @return
*/
public int ExecuteNonQuery(String sql,Object[] params){
int reNum=-1;
Connection conn=null;
PreparedStatement ps=null;
try{
ps=conn.prepareStatement(sql);
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
reNum=ps.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
this.close(ps, null, conn);
}
return reNum;
}
@Override
protected boolean insertBatch(PreparedStatement pstmt)
throws SQLException {
int i = 0;
pstmt.clearParameters();
for (JdbcEntryData pendingEntry : PostgresBufferedInserter.this.pendingInserts) {
for (JdbcEntryDatum datum : pendingEntry) {
pstmt.setObject(++i, datum.getVal());
}
}
log.debug("Executing SQL " + pstmt);
return pstmt.execute();
}
@Override
public void write(PreparedStatement statement) throws SQLException {
Preconditions.checkNotNull(values);
Preconditions.checkNotNull(columnMetadata);
for(int i = 0 ; i < values.size() ; i++) {
Object value = values.get(i);
ColumnInfo columnInfo = columnMetadata.get(i);
if(value == null) {
statement.setNull(i + 1, columnInfo.getSqlType());
} else {
statement.setObject(i + 1, value , columnInfo.getSqlType());
}
}
}
/**
* Fills in the given fields in the queue into the given prepared statement
* @param ps
* @param searchValues
* @param firstField
* @return
* @throws SQLException
*/
private static PreparedStatement fillInWhereClause(PreparedStatement ps, Queue<Map.Entry<String, FieldType>> searchValues, int firstField) throws SQLException{
//insert all values in the prepared statement in the order
//in which the values had been put in the queue
for (Map.Entry<String, FieldType> entry : searchValues){
switch(entry.getValue()) {
case STRING:
ps.setString(firstField, entry.getKey());
break;
case DATE:
ps.setTimestamp(firstField, new Timestamp(Long.parseLong(entry.getKey())));
break;
case LONG:
ps.setLong(firstField, Long.parseLong(entry.getKey()));
break;
case DOUBLE:
ps.setDouble(firstField, Double.parseDouble(entry.getKey()));
break;
case UUID:
ps.setObject(firstField, UUID.fromString(entry.getKey()));
break;
case BOOLEAN:
ps.setBoolean(firstField, Boolean.valueOf(entry.getKey()));
break;
}
firstField++;
}
return ps;
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i,
String parameter, JdbcType jdbcType) throws SQLException {
PGobject jsonObject = new PGobject();
jsonObject.setType("json");
jsonObject.setValue(parameter);
ps.setObject(i, jsonObject);
}
@Override
public int setSubqueryParameters(PreparedStatement statement, ArrayBasedQueryParameters parameters, int startIndex)
throws SQLException {
if (parameters.getSubqueryParameterValues().length > 0) {
for (Object value : parameters.getSubqueryParameterValues()) {
statement.setObject(startIndex, value);
startIndex++;
}
}
return parameters.getSubqueryParameterValues().length;
}
@Override
protected void set(PreparedStatement st, Range range, int index, SharedSessionContractImplementor session) throws SQLException {
if (range == null) {
st.setNull(index, Types.OTHER);
} else {
PGobject object = new PGobject();
object.setType(determineRangeType(range));
object.setValue(range.asString());
st.setObject(index, object);
}
}
/**
* Test BigDecimal with scale as parameter.
*/
public void testBigDecimalSetObjectWithScale() throws Exception
{
getConnection().setAutoCommit(false);
String sql = "CREATE TABLE numtab (num NUMERIC(10,6))";
Statement stmt = createStatement();
assertUpdateCount(stmt, 0, sql);
stmt.close();
commit();
// make a big decimal from string
BigDecimal bdFromString = new BigDecimal("2.33333333");
sql = "INSERT INTO numtab VALUES(?)";
PreparedStatement ps = prepareStatement(sql);
// setObject using the big decimal value
int scale = 2;
ps.setObject(1, bdFromString, java.sql.Types.DECIMAL, scale);
assertUpdateCount(ps, 1);
ps.close();
// check the value
sql = "SELECT num FROM numtab";
stmt = createStatement();
ResultSet rs = stmt.executeQuery(sql);
rs.next();
// Check that the correct scale was set
BigDecimal expected
= bdFromString.setScale(scale, BigDecimal.ROUND_DOWN);
BigDecimal actual = (BigDecimal)rs.getObject(1);
assertEquals("Actual value: " + actual
+ "does not match expected value: " + expected,
expected.compareTo(actual), 0);
rs.close();
stmt.close();
commit();
}
@Test
public void test() {
try {
jdbcTemplate.update("delete from complextbl_0000");
jdbcTemplate.update("delete from complextbl_0001");
Connection connection = dataSource.getConnection();
connection.setAutoCommit(false);
PreparedStatement ps1 = connection.prepareStatement("insert into complextbl_0000 (id,name) values(?,?)");
ps1.setObject(1, 10);
ps1.setObject(2, "TEST");
ps1.execute();
System.out.println("stop run");
// this time exchange and wait untill exchange complete
connection.rollback();
PreparedStatement ps2 = connection.prepareStatement("insert into complextbl_0001 (id,name) values(?,?)");
ps2.setObject(1, 10);
ps2.setObject(2, "TEST");
ps2.execute();
connection.commit();
} catch (SQLException e) {
Assert.fail(ExceptionUtils.getFullStackTrace(e));
} finally {
jdbcTemplate.update("delete from complextbl_0000");
jdbcTemplate.update("delete from complextbl_0001");
}
}
@Test
public void assertClearParameters() throws SQLException {
for (PreparedStatement each : preparedStatements) {
Object obj = new Object();
each.setObject(1, obj);
each.setObject(2, obj, 0);
each.setObject(3, null);
each.setObject(4, null);
each.setObject(5, obj, 0, 0);
assertThat(((ShardingSpherePreparedStatement) each).getParameters().size(), is(5));
each.clearParameters();
assertTrue(((ShardingSpherePreparedStatement) each).getParameters().isEmpty());
}
}
/**
* Apply a PreparedStatement repeatedly with the set of parameter
* vectors. (Any null params are assumed to be of type CHAR).
* @param action the ps to execute
* @param table an array of parameter vectors to use for each
* execution of the PreparedStatement
*/
public static void apply(PreparedStatement action, Object[][] table)
throws SQLException {
for (int row = 0; row < table.length; ++row) {
for (int col = 0; col < table[row].length; ++col) {
Object obj = table[row][col];
if (obj == null) {
action.setNull(col+1,java.sql.Types.CHAR);
continue;
}
action.setObject(col+1, obj);
}
action.execute();
}
}
/**
* SNOW-88426: skip bind parameter index check if prepare fails and defer the error checks to execute
*/
@Test
public void testSelectWithBinding() throws Throwable
{
try (Connection connection = init())
{
connection.createStatement().execute("create or replace table TESTNULL(created_time timestamp_ntz, mid int)");
PreparedStatement ps;
ResultSet rs;
try
{
// skip bind parameter index check if prepare fails and defer the error checks to execute
ps = connection.prepareStatement(
"SELECT 1 FROM TESTNULL WHERE CREATED_TIME = TO_TIMESTAMP(?, 3) and MID = ?"
);
ps.setObject(1, 0);
ps.setObject(2, null);
ps.setObject(1000, null); // this won't raise an exception.
rs = ps.executeQuery();
assertFalse(rs.next());
rs.close();
ps.close();
// describe is success and do the index range check
ps = connection.prepareStatement(
"SELECT 1 FROM TESTNULL WHERE CREATED_TIME = TO_TIMESTAMP(?::NUMBER, 3) and MID = ?"
);
ps.setObject(1, 0);
ps.setObject(2, null);
ps.setObject(1000, null); // this won't raise an exception.
rs = ps.executeQuery();
assertFalse(rs.next());
rs.close();
ps.close();
}
finally
{
connection.createStatement().execute("drop table if exists TESTNULL");
}
}
}
public PreparedStatement createUpdateStatementForIndexTable(Connection connection,
AbstractIndexEntity entity) throws Exception {
String tableName =
entityMappingHolder.tableToEntityNameMap.inverse().get(entity.getClass().getSimpleName());
LinkedHashMap<String, ColumnInfo> columnInfoMap =
entityMappingHolder.columnInfoPerTable.get(tableName);
StringBuilder sqlBuilder = new StringBuilder("UPDATE " + tableName + " SET ");
String delim = "";
LinkedHashMap<String, Object> parameterMap = new LinkedHashMap<>();
for (ColumnInfo columnInfo : columnInfoMap.values()) {
String columnNameInDB = columnInfo.columnNameInDB;
if (!columnNameInDB.equalsIgnoreCase(BASE_ID)
&& !AUTO_UPDATE_COLUMN_SET.contains(columnNameInDB)) {
Object val = columnInfo.field.get(entity);
if (val != null) {
if (Enum.class.isAssignableFrom(val.getClass())) {
val = val.toString();
}
sqlBuilder.append(delim);
sqlBuilder.append(columnNameInDB);
sqlBuilder.append("=");
sqlBuilder.append("?");
delim = ",";
parameterMap.put(columnNameInDB, val);
}
}
}
//ADD WHERE CLAUSE TO CHECK FOR ENTITY ID
sqlBuilder.append(" WHERE base_id=?");
parameterMap.put(BASE_ID, entity.getBaseId());
LOG.debug("Update statement:{}" , sqlBuilder);
int parameterIndex = 1;
PreparedStatement prepareStatement = connection.prepareStatement(sqlBuilder.toString());
for (Entry<String, Object> paramEntry : parameterMap.entrySet()) {
String dbFieldName = paramEntry.getKey();
ColumnInfo info = columnInfoMap.get(dbFieldName);
LOG.debug("Setting value: {} for {}", paramEntry.getValue(), dbFieldName);
prepareStatement.setObject(parameterIndex++, paramEntry.getValue(), info.sqlType);
}
return prepareStatement;
}
/**
* Test DistinctGroupedAggregateResultSet
*/
public void testDistinctGroupedAggregateResultSet() throws Exception {
// [sjigyasu] The test is invalid for GFXD because it tries to
// do a drop while a transaction is progress.
if (isTransactional) {
return;
}
createTestTable("emp", ES+","+DNO+")", "emp_data");
PreparedStatement del =
prepareStatement("delete from emp where mgrname = ?");
// DistinctGroupedAggregateResultSet,
// ProjectRestrictResultSet,ScrollInsensitiveResultSet,
// TableScanResultSet
PreparedStatement tst = prepareStatement
("select count(distinct mgrname) nummgrs, dno "+
"from emp group by dno having dno <> ?");
testDistinctGroupedAggregateResultSet(tst, del);
Statement s = createStatement();
s.executeUpdate("drop table emp");
createTestTable("emp", ES+","+DNO+")", "emp_data");
// re-execute on the new table
testDistinctGroupedAggregateResultSet(tst, del);
// restore data
s.executeUpdate("delete from emp");
s.executeUpdate(insertFrom("emp", "emp_data"));
commit();
// make sure another transaction has exclusive locks
Statement s2 = c2.createStatement();
if (!isAvoidGFXDBugs()) { // unsupported isolation level
s2.executeUpdate("update emp set c0 = c0");
// re-execute with different isolation level (will get lock timeout
// with other isolation levels)
getConnection().
setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
tst.setObject(1, dept[0][1]);
JDBC.assertFullResultSet(tst.executeQuery(),
new Object[][] {{i1, k51}, {i2, k52}},
false);
}
tst.close();
del.close();
s.close();
s2.close();
}
/**
* {@inheritDoc}
*/
public Iterator query(Database model, String sql, Collection parameters, Table[] queryHints) throws DatabaseOperationException
{
Connection connection = borrowConnection();
PreparedStatement statement = null;
ResultSet resultSet = null;
Iterator answer = null;
try
{
statement = connection.prepareStatement(sql);
int paramIdx = 1;
for (Iterator iter = parameters.iterator(); iter.hasNext(); paramIdx++)
{
Object arg = iter.next();
if (arg instanceof BigDecimal)
{
// to avoid scale problems because setObject assumes a scale of 0
statement.setBigDecimal(paramIdx, (BigDecimal)arg);
}
else
{
statement.setObject(paramIdx, arg);
}
}
resultSet = statement.executeQuery();
answer = createResultSetIterator(model, resultSet, queryHints);
return answer;
}
catch (SQLException ex)
{
throw new DatabaseOperationException("Error while performing a query", ex);
}
finally
{
// if any exceptions are thrown, close things down
// otherwise we're leaving it open for the iterator
if (answer == null)
{
closeStatement(statement);
returnConnection(connection);
}
}
}
/**
* QueryExecutorStoredProc can be executed with <I>ON ALL/ON TABLE</I>
* to execute a SQL SELECT QUERY.
*
* We intentionally catch throwable for distributed query execution.
*
* @param inQuery SQL SELECT Query to be executed
* @param paramValueList List of values for bind parameters (i.e. ? marks)
* @param errorStateValue Output parameter of error status
* @param resultSet Output resultset
* @param pCtx ProcedureExecutionContext internally passed by GemFireXD driver
*/
@SuppressWarnings("PMD.AvoidCatchingThrowable")
public static void executeSelect(
String inQuery,
@SuppressWarnings("rawtypes") List paramValueList,
int[] errorStateValue,
ResultSet[] resultSet,
ProcedureExecutionContext pCtx)
throws SQLException {
if (Log.getLogWriter().fineEnabled()) {
Log.getLogWriter().fine(
"QueryExecutorStoredProc-executeSelect entering" +
" inQuery=" + inQuery +
" paramValueList=" + paramValueList +
" errorStateValue=" + LogUtils.getErrorStateValueArrayStr(errorStateValue) +
" resultSet=" + LogUtils.getResultSetArrayStr(resultSet, 20));
}
Connection conn = pCtx.getConnection();
PreparedStatement pStmt;
try {
pStmt = conn.prepareStatement(inQuery);
int columnPosition = 1;
for (Object paramValue : paramValueList) {
pStmt.setObject(columnPosition, paramValue);
columnPosition++;
}
resultSet[0] = pStmt.executeQuery();
} catch (Throwable e) {
errorStateValue[0] = 1;
if (Log.getLogWriter().fineEnabled()) {
Log.getLogWriter().fine(
"QueryExecutorStoredProc-executeSelect" +
" ERROR=" + e);
}
}
if (Log.getLogWriter().fineEnabled()) {
Log.getLogWriter().fine(
"QueryExecutorStoredProc-executeSelect exiting" +
" errorStateValue[0]=" + errorStateValue[0]);
}
}
@Test
public void testIssue102() throws Exception
{
// null int or long should be... null !
System.out.println();
System.out.println("Test Issue #102");
System.out.println("--------------");
Statement stmt = con.createStatement();
//java.util.Date now = new java.util.Date();
// Create the target Column family with each basic data type available on Cassandra
String createCF = "CREATE COLUMNFAMILY t102 (bigint_col bigint PRIMARY KEY, null_int_col int , null_bigint_col bigint, not_null_int_col int);";
stmt.execute(createCF);
stmt.close();
con.close();
// open it up again to see the new CF
con = DriverManager.getConnection(String.format("jdbc:cassandra://%s:%d/%s?loadbalancing=TokenAwarePolicy(RoundRobinPolicy())",HOST,PORT,KEYSPACE));
System.out.println("con.getMetaData().getDatabaseProductName() = " + con.getMetaData().getDatabaseProductName());
System.out.println("con.getMetaData().getDriverName() = " + con.getMetaData().getDriverName());
Statement statement = con.createStatement();
/*
* INSERT INTO test.t80(bigint_col , ascii_col , blob_col , boolean_col , decimal_col , double_col ,
float_col , inet_col , int_col , text_col , timestamp_col , uuid_col ,
timeuuid_col , varchar_col , varint_col )
values(1, 'test', TextAsBlob('test'), true, 5.1, 5.123142 ,
4.2134432 , '192.168.1.1', 1 , 'text' , '2015-01-01 10:10:10' , now() ,
now(), 'test' , 3435 );
*
*/
String insert = "INSERT INTO t102(bigint_col,not_null_int_col) values(?,?);";
PreparedStatement pstatement = con.prepareStatement(insert);
pstatement.setObject(1, 1L); // bigint
pstatement.setObject(2, 1); // int
pstatement.execute();
ResultSet result = statement.executeQuery("SELECT * FROM t102 where bigint_col=1;");
AssertJUnit.assertTrue(result.next());
AssertJUnit.assertEquals(1L, result.getLong("bigint_col"));
System.out.println("null_bigint_col = " + result.getLong("null_bigint_col"));
AssertJUnit.assertEquals(0L,result.getLong("null_bigint_col"));
AssertJUnit.assertTrue(result.wasNull());
AssertJUnit.assertEquals(0,result.getInt("null_int_col"));
AssertJUnit.assertTrue(result.wasNull());
AssertJUnit.assertEquals(1,result.getInt("not_null_int_col"));
AssertJUnit.assertFalse(result.wasNull());
statement.close();
pstatement.close();
}
/**
* Sets the designated parameter to the byte array of the given
* <code>ByteArrayOutputStream</code>. Will set parameter value to null if the
* <code>ByteArrayOutputStream</code> is null.
* Wraps <code>{@link PreparedStatement#setObject(int, java.lang.Object, int)}</code> rather than
* <code>{@link PreparedStatement#setBytes(int, byte[])}</code> as required by the
* DB2 v7 database.
*/
@Override
protected void setBytes(PreparedStatement ps, int index, ByteArrayOutputStream baos) throws SQLException {
ps.setObject(index, ((baos == null) ? null : baos.toByteArray()), java.sql.Types.BLOB);
}