Java Code Examples for java.sql.PreparedStatement#setNull()
The following examples show how to use
java.sql.PreparedStatement#setNull() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: SQLUtils.java From linstor-server with GNU General Public License v3.0 | 6 votes |
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); } }
Example 2
Source File: Migration_2019_11_21_UnifyResourceAndSnapshot.java From linstor-server with GNU General Public License v3.0 | 6 votes |
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(); } }
Example 3
Source File: BaseTypeHandler.java From mybatis with Apache License 2.0 | 6 votes |
@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); } }
Example 4
Source File: StoreServerTableResultTransaction.java From Plan with GNU Lesser General Public License v3.0 | 5 votes |
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(); } } }; }
Example 5
Source File: SQLiteSpecific.java From CloverETL-Engine with GNU Lesser General Public License v2.1 | 5 votes |
@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); } }
Example 6
Source File: ByteGetterMapping.java From butterfly-persistence with Apache License 2.0 | 5 votes |
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); } }
Example 7
Source File: SQLUtils.java From linstor-server with GNU General Public License v3.0 | 5 votes |
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); } }
Example 8
Source File: PostgresServerStatementCachePreparedStatementIT.java From sql-layer with GNU Affero General Public License v3.0 | 5 votes |
@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(); }
Example 9
Source File: ScalarAggregateOperationIT.java From spliceengine with GNU Affero General Public License v3.0 | 5 votes |
@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(); } }
Example 10
Source File: SQLUtils.java From linstor-server with GNU General Public License v3.0 | 5 votes |
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); } }
Example 11
Source File: MonetaryAmountUserType.java From cacheonix-core with GNU Lesser General Public License v2.1 | 5 votes |
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()); } }
Example 12
Source File: AbstractCopySQLData.java From CloverETL-Engine with GNU Lesser General Public License v2.1 | 5 votes |
/** * 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); } }
Example 13
Source File: ApiCatalogDAO.java From entando-core with GNU Lesser General Public License v3.0 | 5 votes |
@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); } }
Example 14
Source File: SQLQuery.java From micro-integrator with Apache License 2.0 | 5 votes |
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); } }
Example 15
Source File: SalaryType.java From tutorials with MIT License | 5 votes |
@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()); } }
Example 16
Source File: ShortPreparedStatementIndexSetter.java From SimpleFlatMapper with MIT License | 5 votes |
@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); } }
Example 17
Source File: MultipleInsertsLeveragingPutAllDUnit.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
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"); }
Example 18
Source File: ParameterMetaDataJdbc30Test.java From gemfirexd-oss with Apache License 2.0 | 4 votes |
/** * 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(); }
Example 19
Source File: NullValueKeyIT.java From spanner-jdbc with MIT License | 4 votes |
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)); }
Example 20
Source File: AbstractHive_1_1QLProcessor.java From nifi with Apache License 2.0 | 4 votes |
/** * 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; } } }