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 vote down vote up
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 vote down vote up
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 vote down vote up
@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 vote down vote up
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 vote down vote up
@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 vote down vote up
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 vote down vote up
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 vote down vote up
@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 vote down vote up
@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 vote down vote up
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 vote down vote up
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 vote down vote up
/**
 *  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 vote down vote up
@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 vote down vote up
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 vote down vote up
@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 vote down vote up
@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 vote down vote up
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 vote down vote up
/**
        * 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 vote down vote up
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 vote down vote up
/**
 * 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;
        }
    }
}