Java Code Examples for java.sql.PreparedStatement#setNull()

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

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

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 setSQL(PreparedStatement pStatement) throws SQLException {
	if (!field.isNull()) {
		pStatement.setDouble(fieldSQL, ((DecimalDataField) field).getDouble());
	} else {
		pStatement.setNull(fieldSQL, java.sql.Types.DECIMAL);
	}

}
 

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

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

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

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

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

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

/**
 *  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);
   	}
}
 

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

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

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

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

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

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

/**
 * 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;
        }
    }
}