下面列出了java.sql.PreparedStatement#setNull ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
private void createLayerDrbdRscDfnEntry(PreparedStatement insert, String rscName, String snapName)
throws SQLException
{
if (layerDrbdRscDfnSet.add(new Key(rscName, snapName)))
{
insert.setString(1, rscName);
insert.setString(2, DFLT_RSC_NAME_SUFFIX);
insert.setString(3, snapName);
insert.setInt(4, InternalApiConsts.DEFAULT_PEER_SLOTS); // we have nothing else saved
insert.setInt(5, InternalApiConsts.DEFAULT_AL_STRIPES);
insert.setLong(6, InternalApiConsts.DEFAULT_AL_SIZE);
insert.setNull(7, Types.INTEGER); // null as tcpPort
insert.setString(8, DFLT_TRANSPORT_TYPE);
insert.setNull(9, Types.VARCHAR); // no secret
insert.executeUpdate();
}
}
@Override
public void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
//特殊情况，设置NULL
if (parameter == null) {
if (jdbcType == null) {
//如果没设置jdbcType，报错啦
throw new TypeException("JDBC requires that the JdbcType must be specified for all nullable parameters.");
}
try {
//设成NULL
ps.setNull(i, jdbcType.TYPE_CODE);
} catch (SQLException e) {
throw new TypeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . " +
"Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. " +
"Cause: " + e, e);
}
} else {
//非NULL情况，怎么设还得交给不同的子类完成, setNonNullParameter是一个抽象方法
setNonNullParameter(ps, i, parameter, jdbcType);
}
}
public static void setJsonIfNotNullAsBlob(PreparedStatement stmt, int idx, Object obj) throws SQLException
{
if (obj != null)
{
try
{
stmt.setBytes(idx, OBJ_MAPPER.writeValueAsBytes(obj));
}
catch (IOException exc)
{
throw new LinStorDBRuntimeException(
"Exception occurred while serializing to json array: " + obj.toString(),
exc
);
}
}
else
{
stmt.setNull(idx, Types.BLOB);
}
}
@Override
public void set(PreparedStatement target, Short value, int columnIndex, Context context) throws SQLException {
if (value != null) {
target.setShort(columnIndex, value);
} else {
target.setNull(columnIndex, Types.SMALLINT);
}
}
private Executable insertNewValues(int tableID) {
String sql = "INSERT INTO " + TABLE_NAME + '(' +
TABLE_ID + ',' +
SERVER_UUID + ',' +
VALUE_1 + ',' +
VALUE_2 + ',' +
VALUE_3 + ',' +
VALUE_4 + ',' +
VALUE_5 +
") VALUES (?,?,?,?,?,?, ?)";
return new ExecBatchStatement(sql) {
@Override
public void prepare(PreparedStatement statement) throws SQLException {
int maxColumnSize = Math.min(table.getMaxColumnSize(), 5); // Limit to maximum 5 columns, or how many column names there are.
for (Object[] row : table.getRows()) {
statement.setInt(1, tableID);
statement.setString(2, serverUUID.toString());
for (int i = 0; i < maxColumnSize; i++) {
Object value = row[i];
setStringOrNull(statement, 3 + i, value != null ? StringUtils.truncate(value.toString(), 250) : null);
}
// Rest are set null if not 5 columns wide.
for (int i = maxColumnSize; i < 5; i++) {
statement.setNull(3 + i, Types.VARCHAR);
}
statement.addBatch();
}
}
};
}
@Override
public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
if (Objects.isNull(value))
st.setNull(index, Types.BIGINT);
else {
Salary salary = (Salary) value;
st.setLong(index, SalaryCurrencyConvertor.convert(salary.getAmount(),
salary.getCurrency(), localCurrency));
st.setString(index + 1, salary.getCurrency());
}
}
private void setBigIntValue(int queryType, String value, String paramType,
PreparedStatement sqlQuery, int i) throws SQLException {
Long val = null;
if (value != null) {
val = new Long(value);
}
if (QueryTypes.IN.equals(paramType)) {
if (queryType == SQLQuery.DS_QUERY_TYPE_NORMAL) {
if (value == null) {
sqlQuery.setNull(i + 1, Types.BIGINT);
} else {
sqlQuery.setLong(i + 1, val);
}
} else {
if (value == null) {
((CallableStatement) sqlQuery).setNull(i + 1, Types.BIGINT);
} else {
((CallableStatement) sqlQuery).setLong(i + 1, val);
}
}
} else if (QueryTypes.INOUT.equals(paramType)) {
if (value == null) {
((CallableStatement) sqlQuery).setNull(i + 1, Types.BIGINT);
} else {
((CallableStatement) sqlQuery).setLong(i + 1, val);
}
((CallableStatement) sqlQuery).registerOutParameter(i + 1, Types.BIGINT);
} else {
((CallableStatement) sqlQuery).registerOutParameter(i + 1, Types.BIGINT);
}
}
@Override
public void saveApiStatus(ApiMethod method) {
Connection conn = null;
PreparedStatement stat = null;
try {
conn = this.getConnection();
conn.setAutoCommit(false);
String resourceCode = ApiResource.getCode(method.getNamespace(), method.getResourceName());
this.resetApiStatus(resourceCode, method.getHttpMethod(), conn);
stat = conn.prepareStatement(SAVE_API_STATUS);
int isActive = (method.isActive()) ? 1 : 0;
stat.setString(1, resourceCode);
stat.setString(2, method.getHttpMethod().toString());
stat.setInt(3, isActive);
int authentication = (method.getRequiredAuth()) ? 1 : 0;
stat.setInt(4, authentication);
if (null != method.getRequiredPermission() && method.getRequiredPermission().trim().length() > 0) {
stat.setString(5, method.getRequiredPermission());
} else {
stat.setNull(5, Types.VARCHAR);
}
int isHidden = (null != method.getHidden() && method.getHidden()) ? 1 : 0;
stat.setInt(6, isHidden);
stat.executeUpdate();
conn.commit();
} catch (Throwable t) {
this.executeRollback(conn);
_logger.error("Error while saving api status", t);
throw new RuntimeException("Error while saving api status", t);
} finally {
closeDaoResources(null, stat, conn);
}
}
/**
* Need a vector field for DataTypes
*
* @param pStatement The new SQL value
* @exception SQLException Description of Exception
* @since October 7, 2002
*/
@Override
public void setSQL(PreparedStatement pStatement) throws SQLException {
if (!field.isNull()) {
pStatement.setString(fieldSQL, field.toString());
}else{
pStatement.setNull(fieldSQL, java.sql.Types.ARRAY);
}
}
public void nullSafeSet(PreparedStatement statement,
Object value,
int index)
throws HibernateException, SQLException {
if (value == null) {
statement.setNull(index, Types.NUMERIC);
statement.setNull(index+1, Types.VARCHAR);
} else {
MonetaryAmount currency = (MonetaryAmount)value;
statement.setBigDecimal(index, currency.getValue());
statement.setString(index+1, currency.getCurrency().getCurrencyCode());
}
}
public static void setIntIfNotNull(PreparedStatement stmt, int idx, Integer val) throws SQLException
{
if (val != null)
{
stmt.setInt(idx, val);
}
else
{
stmt.setNull(idx, Types.INTEGER);
}
}
@Override
protected void starting(Description description) {
try {
PreparedStatement ps = spliceClassWatcher.prepareStatement(INSERT);
for (int i = 0; i < size-1; i++) {
ps.setString(1, format("user%s", i + 1));
ps.setInt(2, i);
stats.add(i);
ps.executeUpdate();
}
ps.setString(1, format("user%s", size));
ps.setInt(2, Integer.MAX_VALUE - 1);
stats.add(Integer.MAX_VALUE - 1);
ps.executeUpdate();
// spliceClassWatcher.splitTable(TABLE_NAME, CLASS_NAME, size / 3);
ps = spliceClassWatcher.prepareStatement("insert into " + nullTableWatcher.toString() + " values (?,?)");
for (int i = 0; i < size; i++) {
if (i % 2 == 0) {
ps.setNull(1, Types.INTEGER);
} else
ps.setInt(1, i);
ps.setInt(2, i * 2);
ps.executeUpdate();
}
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
spliceClassWatcher.closeAll();
}
}
@Test
public void testSetNull() throws Exception {
// valid: fully qualified type to setNull()
PreparedStatement pstmt = getConnection().prepareStatement("INSERT INTO texttable (te) VALUES (?)");
if(pstmt instanceof PGStatement) {
PGStatement pgp = (PGStatement) pstmt;
pgp.setPrepareThreshold(2);
}
pstmt.setNull(1, Types.VARCHAR);
pstmt.executeUpdate();
// valid: fully qualified type to setObject()
pstmt.setObject(1, null, Types.VARCHAR);
pstmt.executeUpdate();
// valid: setObject() with partial type info and a typed "null object instance"
com.foundationdb.sql.jdbc.util.PGobject dummy = new com.foundationdb.sql.jdbc.util.PGobject();
dummy.setType("VARCHAR");
dummy.setValue(null);
pstmt.setObject(1, dummy, Types.OTHER);
pstmt.executeUpdate();
// setObject() with no type info
pstmt.setObject(1, null);
pstmt.executeUpdate();
// setObject() with insufficient type info
pstmt.setObject(1, null, Types.OTHER);
pstmt.executeUpdate();
// setNull() with insufficient type info
pstmt.setNull(1, Types.OTHER);
pstmt.executeUpdate();
pstmt.close();
}
public static void setBooleanIfNotNull(PreparedStatement stmt, int idx, Boolean val) throws SQLException
{
if (val != null)
{
stmt.setBoolean(idx, val);
}
else
{
stmt.setNull(idx, Types.BOOLEAN);
}
}
protected void insertObjectDo(Object value, PreparedStatement statement, int index) throws SQLException {
if(value != null){
statement.setByte(index, ((Byte) value).byteValue());
} else {
statement.setNull(index, java.sql.Types.TINYINT);
}
}
@Override
public void setSQL(PreparedStatement pStatement) throws SQLException {
if (!field.isNull()) {
pStatement.setDouble(fieldSQL, ((DecimalDataField) field).getDouble());
} else {
pStatement.setNull(fieldSQL, java.sql.Types.DECIMAL);
}
}
/**
* Determines how to map the given value to the appropriate JDBC data jdbcType and sets the parameter on the
* provided PreparedStatement
*
* @param stmt the PreparedStatement to set the parameter on
* @param attrName the name of the attribute that the parameter is coming from - for logging purposes
* @param parameterIndex the index of the HiveQL parameter to set
* @param parameterValue the value of the HiveQL parameter to set
* @param jdbcType the JDBC Type of the HiveQL parameter to set
* @throws SQLException if the PreparedStatement throws a SQLException when calling the appropriate setter
*/
protected void setParameter(final PreparedStatement stmt, final String attrName, final int parameterIndex, final String parameterValue, final int jdbcType) throws SQLException {
if (parameterValue == null) {
stmt.setNull(parameterIndex, jdbcType);
} else {
try {
switch (jdbcType) {
case Types.BIT:
case Types.BOOLEAN:
stmt.setBoolean(parameterIndex, Boolean.parseBoolean(parameterValue));
break;
case Types.TINYINT:
stmt.setByte(parameterIndex, Byte.parseByte(parameterValue));
break;
case Types.SMALLINT:
stmt.setShort(parameterIndex, Short.parseShort(parameterValue));
break;
case Types.INTEGER:
stmt.setInt(parameterIndex, Integer.parseInt(parameterValue));
break;
case Types.BIGINT:
stmt.setLong(parameterIndex, Long.parseLong(parameterValue));
break;
case Types.REAL:
stmt.setFloat(parameterIndex, Float.parseFloat(parameterValue));
break;
case Types.FLOAT:
case Types.DOUBLE:
stmt.setDouble(parameterIndex, Double.parseDouble(parameterValue));
break;
case Types.DECIMAL:
case Types.NUMERIC:
stmt.setBigDecimal(parameterIndex, new BigDecimal(parameterValue));
break;
case Types.DATE:
stmt.setDate(parameterIndex, new Date(Long.parseLong(parameterValue)));
break;
case Types.TIME:
stmt.setTime(parameterIndex, new Time(Long.parseLong(parameterValue)));
break;
case Types.TIMESTAMP:
stmt.setTimestamp(parameterIndex, new Timestamp(Long.parseLong(parameterValue)));
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGNVARCHAR:
case Types.LONGVARCHAR:
stmt.setString(parameterIndex, parameterValue);
break;
default:
stmt.setObject(parameterIndex, parameterValue, jdbcType);
break;
}
} catch (SQLException e) {
// Log which attribute/parameter had an error, then rethrow to be handled at the top level
getLogger().error("Error setting parameter {} to value from {} ({})", new Object[]{parameterIndex, attrName, parameterValue}, e);
throw e;
}
}
}
private void insertRecords() throws SQLException {
String sql = "insert into table_with_null_keys (id1, id2, name) values (?, ?, ?)";
PreparedStatement ps = getConnection().prepareStatement(sql);
// records without null values
ps.setLong(1, 1L);
ps.setLong(2, 1L);
ps.setString(3, "record without null values");
ps.addBatch();
ps.clearParameters();
ps.setLong(1, 1L);
ps.setLong(2, 2L);
ps.setString(3, "record without null values");
ps.addBatch();
ps.clearParameters();
ps.setLong(1, 2L);
ps.setLong(2, 2L);
ps.setString(3, "record without null values");
ps.addBatch();
// records with null values
ps.clearParameters();
ps.setLong(1, 3L);
ps.setNull(2, Types.BIGINT);
ps.setString(3, "record with null values");
ps.addBatch();
ps.clearParameters();
ps.setLong(1, 1L);
ps.setNull(2, Types.BIGINT);
ps.setString(3, "record with null values");
ps.addBatch();
ps.executeBatch();
getConnection().commit();
// check record count
assertEquals(5L, getRecordCount());
// assert both non-null and null values
assertEquals((Object) 1L, (Object) getId2(1L, 1L));
assertNull(getId2(1L, null));
assertNull(getId2(3L, null));
}
/**
* Testing a prepared statement.
*
* @exception SQLException if database access errors or other errors occur
*/
public void testPreparedStatement () throws SQLException {
//next testing a prepared statement
PreparedStatement ps = prepareStatement("insert into t values(?, ?, ?, ?, ?)");
ps.setNull(1, java.sql.Types.CHAR);
ps.setInt(2, 1);
ps.setNull(3, java.sql.Types.INTEGER);
ps.setBigDecimal(4,new BigDecimal("1"));
ps.setNull(5, java.sql.Types.DATE);
ParameterMetaData paramMetaData = ps.getParameterMetaData();
assertEquals("Unexpected parameter count", 5, paramMetaData.getParameterCount());
//expected values to be stored in a 2dim. array
String [][] parameterMetaDataArray0 = {
//isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
{"PARAMETER_NULLABLE", "false", "5", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"},
{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
{"PARAMETER_NULLABLE", "true", "10", "0", "4", "INTEGER", "java.lang.Integer", "PARAMETER_MODE_IN"},
{"PARAMETER_NULLABLE", "true", "38", "0", "3", "DECIMAL", "java.math.BigDecimal", "PARAMETER_MODE_IN"},
{"PARAMETER_NULLABLE", "false", "10", "0", "91", "DATE", "java.sql.Date", "PARAMETER_MODE_IN"}};
testParameterMetaData(paramMetaData, parameterMetaDataArray0);
/*
* JCC seems to report these parameters as MODE_UNKNOWN, where as Derby uses MODE_IN
* JCC behaviour with network server matches its behaviour with DB2
* getPrecision() returns 0 for CHAR/DATE/BIT types for Derby. JCC shows maxlen
*/
ps.execute();
/*
* bug 4533 - associated parameters should not be included in the parameter meta data list
* Following statement systab will generate 4 associated parameters for the 2
* user parameters. This results in total 6 parameters for the prepared statement
* internally. But we should only show 2 user visible parameters through
* getParameterMetaData().
*/
ps = prepareStatement("select * from sys.systables where " +
" CAST(tablename AS VARCHAR(128)) like ? and CAST(tableID AS CHAR(36)) like ?");
ps.setString (1, "SYS%");
ps.setString (2, "8000001%");
paramMetaData = ps.getParameterMetaData();
assertEquals("Unexpected parameter count", 2, paramMetaData.getParameterCount());
//expected values to be stored in a 2dim. array
String parameterMetaDataArray1 [][] = {
//isNullable, isSigned, getPrecision, getScale, getParameterType, getParameterTypeName, getParameterClassName, getParameterMode
{"PARAMETER_NULLABLE", "false", "128", "0", "12", "VARCHAR", "java.lang.String", "PARAMETER_MODE_IN"},
{"PARAMETER_NULLABLE", "false", "36", "0", "1", "CHAR", "java.lang.String", "PARAMETER_MODE_IN"}};
testParameterMetaData(paramMetaData, parameterMetaDataArray1);
ps.execute();
ps.close();
}
public void testMultipleInsertSamePreptmnt() throws Exception {
startVMs(1, 3);
serverSQLExecute(1, "create schema emp");
serverSQLExecute(1, "create table emp.EMPLOYEE(lastname varchar(30), depId int) " +
"partition by (depId)"+ getSuffix());
setUpBatchObserver();
PreparedStatement pstmnt = TestUtil.getPreparedStatement("INSERT INTO emp.employee VALUES (?, ?)");
pstmnt.setString(1, "Jones");
pstmnt.setInt(2, 33);
pstmnt.addBatch();
pstmnt.setString(1, "Rafferty");
pstmnt.setInt(2, 31);
pstmnt.addBatch();
pstmnt.setString(1, "Robinson");
pstmnt.setInt(2, 34);
pstmnt.addBatch();
int[] status = pstmnt.executeBatch();
checkObservedNumber(3);
for(int i=0; i<3; i++) {
assertEquals(status[i], 1);
}
String jdbcSQL = "select * from emp.Employee";
pstmnt.setString(1, "Steinberg");
pstmnt.setInt(2, 33);
pstmnt.addBatch();
pstmnt.setString(1, "Smith");
pstmnt.setInt(2, 34);
pstmnt.addBatch();
pstmnt.setString(1, "John");
pstmnt.setNull(2, Types.INTEGER);
pstmnt.addBatch();
status = pstmnt.executeBatch();
checkObservedNumber(3);
for(int i=0; i<3; i++) {
assertEquals(status[i], 1);
}
sqlExecuteVerify(null, new int[] {1}, jdbcSQL, goldenTextFile, "multInsert");
}