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

The following examples show how to use java.sql.PreparedStatement#setNull() . These examples are extracted from open source projects. 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 want to check out the right sidebar which shows the related API usage.
Example 1
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 2
Source Project: mybatis   File: BaseTypeHandler.java    License: 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 3
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 4
@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 5
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 6
Source Project: tutorials   File: SalaryType.java    License: 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 7
Source Project: micro-integrator   File: SQLQuery.java    License: 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 8
@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 9
/**
 *  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 10
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 11
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 12
@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 13
@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 14
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 15
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 16
@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 17
/**
 * 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;
        }
    }
}
 
Example 18
Source Project: spanner-jdbc   File: NullValueKeyIT.java    License: 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 19
/**
        * 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 20
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");
  
}