Example usage for java.sql PreparedStatement setNull

List of usage examples for java.sql PreparedStatement setNull

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setNull

Introduction

In this page you can find the example usage for java.sql PreparedStatement setNull.

Prototype

void setNull(int parameterIndex, int sqlType) throws SQLException;

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

From source file:org.moqui.impl.entity.EntityJavaUtil.java

public static void setPreparedStatementValue(PreparedStatement ps, int index, Object value, FieldInfo fi,
        boolean useBinaryTypeForBlob, EntityFacade efi) throws EntityException {
    try {/*from w  w w  . ja  v a2s  .  c  o m*/
        // allow setting, and searching for, String values for all types; JDBC driver should handle this okay
        if (value instanceof CharSequence) {
            ps.setString(index, value.toString());
        } else {
            switch (fi.typeValue) {
            case 1:
                if (value != null) {
                    ps.setString(index, value.toString());
                } else {
                    ps.setNull(index, Types.VARCHAR);
                }
                break;
            case 2:
                if (value != null) {
                    Class valClass = value.getClass();
                    if (valClass == Timestamp.class) {
                        ps.setTimestamp(index, (Timestamp) value, efi.getCalendarForTzLc());
                    } else if (valClass == java.sql.Date.class) {
                        ps.setDate(index, (java.sql.Date) value, efi.getCalendarForTzLc());
                    } else if (valClass == java.util.Date.class) {
                        ps.setTimestamp(index, new Timestamp(((java.util.Date) value).getTime()),
                                efi.getCalendarForTzLc());
                    } else {
                        throw new IllegalArgumentException("Class " + valClass.getName()
                                + " not allowed for date-time (Timestamp) fields, for field " + fi.entityName
                                + "." + fi.name);
                    }
                } else {
                    ps.setNull(index, Types.TIMESTAMP);
                }
                break;
            case 3:
                Time tm = (Time) value;
                // logger.warn("=================== setting time tm=${tm} tm long=${tm.getTime()}, cal=${cal}")
                if (value != null) {
                    ps.setTime(index, tm, efi.getCalendarForTzLc());
                } else {
                    ps.setNull(index, Types.TIME);
                }
                break;
            case 4:
                if (value != null) {
                    Class valClass = value.getClass();
                    if (valClass == java.sql.Date.class) {
                        java.sql.Date dt = (java.sql.Date) value;
                        // logger.warn("=================== setting date dt=${dt} dt long=${dt.getTime()}, cal=${cal}")
                        ps.setDate(index, dt, efi.getCalendarForTzLc());
                    } else if (valClass == Timestamp.class) {
                        ps.setDate(index, new java.sql.Date(((Timestamp) value).getTime()),
                                efi.getCalendarForTzLc());
                    } else if (valClass == java.util.Date.class) {
                        ps.setDate(index, new java.sql.Date(((java.util.Date) value).getTime()),
                                efi.getCalendarForTzLc());
                    } else {
                        throw new IllegalArgumentException("Class " + valClass.getName()
                                + " not allowed for date fields, for field " + fi.entityName + "." + fi.name);
                    }
                } else {
                    ps.setNull(index, Types.DATE);
                }
                break;
            case 5:
                if (value != null) {
                    ps.setInt(index, ((Number) value).intValue());
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 6:
                if (value != null) {
                    ps.setLong(index, ((Number) value).longValue());
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 7:
                if (value != null) {
                    ps.setFloat(index, ((Number) value).floatValue());
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 8:
                if (value != null) {
                    ps.setDouble(index, ((Number) value).doubleValue());
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 9:
                if (value != null) {
                    Class valClass = value.getClass();
                    // most common cases BigDecimal, Double, Float; then allow any Number
                    if (valClass == BigDecimal.class) {
                        ps.setBigDecimal(index, (BigDecimal) value);
                    } else if (valClass == Double.class) {
                        ps.setDouble(index, (Double) value);
                    } else if (valClass == Float.class) {
                        ps.setFloat(index, (Float) value);
                    } else if (value instanceof Number) {
                        ps.setDouble(index, ((Number) value).doubleValue());
                    } else {
                        throw new IllegalArgumentException("Class " + valClass.getName()
                                + " not allowed for number-decimal (BigDecimal) fields, for field "
                                + fi.entityName + "." + fi.name);
                    }
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 10:
                if (value != null) {
                    ps.setBoolean(index, (Boolean) value);
                } else {
                    ps.setNull(index, Types.BOOLEAN);
                }
                break;
            case 11:
                if (value != null) {
                    try {
                        ByteArrayOutputStream os = new ByteArrayOutputStream();
                        ObjectOutputStream oos = new ObjectOutputStream(os);
                        oos.writeObject(value);
                        oos.close();
                        byte[] buf = os.toByteArray();
                        os.close();

                        ByteArrayInputStream is = new ByteArrayInputStream(buf);
                        ps.setBinaryStream(index, is, buf.length);
                        is.close();
                    } catch (IOException ex) {
                        throw new EntityException(
                                "Error setting serialized object, for field " + fi.entityName + "." + fi.name,
                                ex);
                    }
                } else {
                    if (useBinaryTypeForBlob) {
                        ps.setNull(index, Types.BINARY);
                    } else {
                        ps.setNull(index, Types.BLOB);
                    }
                }
                break;
            case 12:
                if (value instanceof byte[]) {
                    ps.setBytes(index, (byte[]) value);
                    /*
                    } else if (value instanceof ArrayList) {
                        ArrayList valueAl = (ArrayList) value;
                        byte[] theBytes = new byte[valueAl.size()];
                        valueAl.toArray(theBytes);
                        ps.setBytes(index, theBytes);
                    */
                } else if (value instanceof ByteBuffer) {
                    ByteBuffer valueBb = (ByteBuffer) value;
                    ps.setBytes(index, valueBb.array());
                } else if (value instanceof Blob) {
                    Blob valueBlob = (Blob) value;
                    // calling setBytes instead of setBlob
                    // ps.setBlob(index, (Blob) value)
                    // Blob blb = value
                    ps.setBytes(index, valueBlob.getBytes(1, (int) valueBlob.length()));
                } else {
                    if (value != null) {
                        throw new IllegalArgumentException("Type not supported for BLOB field: "
                                + value.getClass().getName() + ", for field " + fi.entityName + "." + fi.name);
                    } else {
                        if (useBinaryTypeForBlob) {
                            ps.setNull(index, Types.BINARY);
                        } else {
                            ps.setNull(index, Types.BLOB);
                        }
                    }
                }
                break;
            case 13:
                if (value != null) {
                    ps.setClob(index, (Clob) value);
                } else {
                    ps.setNull(index, Types.CLOB);
                }
                break;
            case 14:
                if (value != null) {
                    ps.setTimestamp(index, (Timestamp) value);
                } else {
                    ps.setNull(index, Types.TIMESTAMP);
                }
                break;
            // TODO: is this the best way to do collections and such?
            case 15:
                if (value != null) {
                    ps.setObject(index, value, Types.JAVA_OBJECT);
                } else {
                    ps.setNull(index, Types.JAVA_OBJECT);
                }
                break;
            }
        }
    } catch (SQLException sqle) {
        throw new EntityException("SQL Exception while setting value [" + value + "]("
                + (value != null ? value.getClass().getName() : "null") + "), type " + fi.type + ", for field "
                + fi.entityName + "." + fi.name + ": " + sqle.toString(), sqle);
    } catch (Exception e) {
        throw new EntityException(
                "Error while setting value for field " + fi.entityName + "." + fi.name + ": " + e.toString(),
                e);
    }
}

From source file:org.hyperic.hibernate.usertypes.EncryptedStringType.java

/**
 * Extracts the unencrypted value from the {@link LazyDecryptableValue} instance and ensures its encrypted prior to<br>  
 * binding it to the resultset parameter 
 *///from www  .  jav  a 2  s . co  m
public final void nullSafeSet(final PreparedStatement st, final Object value, final int index)
        throws HibernateException, SQLException {
    this.delegate.lazyInit();

    if (value == null) {
        st.setNull(index, nullableSqlType);
    } else {
        String strValue = this.convertToString(value);

        if (!SecurityUtil.isMarkedEncrypted(strValue)) {
            // [HHQ-5592] MAYA temporarily save the cleartext value
            // for debugging purposes only
            String originalValue = strValue;
            if (log.isDebugEnabled()) {
                log.debug("value before encryption=" + originalValue);
            }
            strValue = this.encryptor.encrypt(strValue);

            if (log.isDebugEnabled()) {
                log.debug("value after encryption=" + strValue);
            }
            // MAYA Note: this might throw an exception,
            // if encryption was defective
            try {
                String decryptedValue = this.encryptor.decrypt(strValue);
                if (!originalValue.equals(decryptedValue)) {
                    StringBuilder logMessageBuilder = new StringBuilder("original value={");
                    logMessageBuilder.append(originalValue).append("} differs from the decrypted value={")
                            .append(decryptedValue).append("}");
                    log.error(logMessageBuilder.toString());
                } // EO if original and decrypted values differ
            } catch (EncryptionOperationNotPossibleException e) {
                log.warn("could not decrypt value=" + value);
                throw e;
            } // EO catch  EncryptionOperationNotPossibleException           

        } //EO if value was not already encrypted 

        st.setString(index, strValue);
    } //EO else if value was not null 
}

From source file:chh.utils.db.source.common.JdbcClient.java

private void setPreparedStatementParams(PreparedStatement preparedStatement, List<Column> columnList)
        throws SQLException {
    int index = 1;
    for (Column column : columnList) {
        Class columnJavaType = Util.getJavaType(column.getSqlType());
        if (column.getVal() == null) {
            preparedStatement.setNull(index, column.getSqlType());
        } else if (columnJavaType.equals(String.class)) {
            preparedStatement.setString(index, (String) column.getVal());
        } else if (columnJavaType.equals(Integer.class)) {
            preparedStatement.setInt(index, (Integer) column.getVal());
        } else if (columnJavaType.equals(Double.class)) {
            preparedStatement.setDouble(index, (Double) column.getVal());
        } else if (columnJavaType.equals(Float.class)) {
            preparedStatement.setFloat(index, (Float) column.getVal());
        } else if (columnJavaType.equals(Short.class)) {
            preparedStatement.setShort(index, (Short) column.getVal());
        } else if (columnJavaType.equals(Boolean.class)) {
            preparedStatement.setBoolean(index, (Boolean) column.getVal());
        } else if (columnJavaType.equals(byte[].class)) {
            preparedStatement.setBytes(index, (byte[]) column.getVal());
        } else if (columnJavaType.equals(Long.class)) {
            preparedStatement.setLong(index, (Long) column.getVal());
        } else if (columnJavaType.equals(Date.class)) {
            preparedStatement.setDate(index, (Date) column.getVal());
        } else if (columnJavaType.equals(Time.class)) {
            preparedStatement.setTime(index, (Time) column.getVal());
        } else if (columnJavaType.equals(Timestamp.class)) {
            preparedStatement.setTimestamp(index, (Timestamp) column.getVal());
        } else {/* w ww .ja  v a 2 s  .  c  om*/
            throw new RuntimeException(
                    "Unknown type of value " + column.getVal() + " for column " + column.getColumnName());
        }
        ++index;
    }
}

From source file:org.callimachusproject.behaviours.SqlDatasourceSupport.java

private void setValue(PreparedStatement insert, int col, Value value, Integer type) throws SQLException {
    if (value == null) {
        insert.setNull(col, type);
    } else if (value instanceof Literal) {
        Literal lit = (Literal) value;/*  ww  w. j  a  v a2s.co  m*/
        URI datatype = lit.getDatatype();
        if (datatype == null) {
            insert.setString(col, value.stringValue());
        } else if (XMLDatatypeUtil.isCalendarDatatype(datatype)) {
            GregorianCalendar cal = lit.calendarValue().toGregorianCalendar();
            insert.setDate(col, new java.sql.Date(cal.getTimeInMillis()), cal);
        } else {
            insert.setString(col, value.stringValue());
        }
    } else {
        insert.setString(col, value.stringValue());
    }
}

From source file:data.DefaultExchanger.java

protected void setClob(PreparedStatement ps, short index, JsonNode node, String column) throws SQLException {
    String value = node.get(column).textValue();
    if (value == null) {
        ps.setNull(index, Types.CLOB);
    } else {/*  ww w  .  ja va2 s .  c  o  m*/
        Clob clob = ps.getConnection().createClob();
        clob.setString(1, value);
        ps.setClob(index, clob);
    }
}

From source file:org.spring.data.gemfire.app.dao.provider.JdbcUserDao.java

protected PreparedStatement setTimestamp(final PreparedStatement statement, final int parameterIndex,
        final Timestamp value) throws SQLException {
    if (value != null) {
        statement.setTimestamp(parameterIndex, value);
    } else {/*from   w  w w  .  ja  va  2  s.co  m*/
        statement.setNull(parameterIndex, Types.TIMESTAMP);
    }

    return statement;
}

From source file:jp.co.tis.gsp.tools.dba.dialect.Dialect.java

/**
 * stmt???????//w w w . j  a v  a 2 s  .c o m
 * @param stmt I/O
 * @param parameterIndex parameter index
 * @param value set value
 * @param sqlType sql type
 * @throws SQLException error
 */
public void setObjectInStmt(PreparedStatement stmt, int parameterIndex, String value, int sqlType)
        throws SQLException {
    if (sqlType == UN_USABLE_TYPE) {
        stmt.setNull(parameterIndex, Types.NULL);
    } else if (StringUtil.isBlank(value) || "".equals(value)) {
        stmt.setNull(parameterIndex, sqlType);
    } else {
        stmt.setObject(parameterIndex, value, sqlType);
    }
}

From source file:permit.Address.java

public String setFields(PreparedStatement pstmt) {

    String msg = "";
    String today = Helper.getToday();
    int jj = 1;/* w ww. java2  s  .  c  o  m*/
    try {
        if (address.equals(""))
            pstmt.setNull(jj++, Types.VARCHAR);
        else
            pstmt.setString(jj++, address);
        if (loc_lat.equals(""))
            pstmt.setNull(jj++, Types.VARCHAR);
        else
            pstmt.setString(jj++, loc_lat);
        if (loc_long.equals(""))
            pstmt.setNull(jj++, Types.VARCHAR);
        else
            pstmt.setString(jj++, loc_long);
    } catch (Exception ex) {
        msg += ex;
        logger.error(msg);
    }
    return msg;
}

From source file:org.tec.webapp.jdbc.entity.support.PreparedStatementBuilder.java

/**
 * Set a prepared statement field based on type
 *
 * @param stmt PrepapredStatement/*from w  w w .jav a 2 s.c  om*/
 * @param index index of the parameter in the stmt
 * @param param the param value and metadata
 */
protected void setParameter(PreparedStatement stmt, int index, Parameter param) {
    try {
        if (param.getData() == null) {
            stmt.setNull(index, param.getType().getVendorTypeNumber());
        } else {
            switch (param.getType()) {
            case BOOLEAN:
                stmt.setBoolean(index, (Boolean) param.getData());
                break;
            case DATE:
                /*
                 * java.sql.Date date = TextHelper.parseDate(param.getData()); if
                 * (null == date) { throw new
                 * SQLException("failed to set parameter: stmt=" + stmt + " index="
                 * + index + " param=" + param); } stmt.setDate(index, date);
                 */
                break;
            case TIME:
                /*
                 * Time time = TextHelper.parseTime(param.getData()); if (null ==
                 * time) { throw new SQLException("failed to set parameter: stmt=" +
                 * stmt + " index=" + index + " param=" + param); }
                 * stmt.setTime(index, time);
                 */
                break;
            case TIMESTAMP:
                /*
                 * Timestamp ts = TextHelper.parseTimestamp(param.getData()); if
                 * (null == ts) { throw new
                 * SQLException("failed to set parameter: stmt=" + stmt + " index="
                 * + index + " param=" + param); } stmt.setTimestamp(index, ts);
                 */
                break;
            case INTEGER:
                if (param.getData() instanceof Long) {
                    Long l = (Long) param.getData();
                    stmt.setLong(index, l);
                } else {
                    Integer i = (Integer) param.getData();
                    stmt.setInt(index, i);
                }
                break;
            case FLOAT:
                Float f = (Float) param.getData();
                stmt.setFloat(index, f);
                break;
            default: // set string for non explicit types
                String tmp = StringUtils.replaceEachRepeatedly((String) param.getData(), INVALID_TEXT_CHARS,
                        CORRECT_TEXT_CHARS);
                stmt.setString(index, tmp);
                break;
            }
        }
    } catch (Throwable e) {
        throw new RuntimeException("failed to process parameter " + param, e);
    }
}

From source file:org.entando.entando.aps.system.services.guifragment.GuiFragmentDAO.java

protected void updateGuiFragment(GuiFragment guiFragment, Connection conn) {
    PreparedStatement stat = null;
    try {/*w  w  w .j  a v  a  2 s. c om*/
        stat = conn.prepareStatement(UPDATE_GUIFRAGMENT);
        int index = 1;
        if (StringUtils.isNotBlank(guiFragment.getWidgetTypeCode())) {
            stat.setString(index++, guiFragment.getWidgetTypeCode());
        } else {
            stat.setNull(index++, Types.VARCHAR);
        }
        if (StringUtils.isNotBlank(guiFragment.getPluginCode())) {
            stat.setString(index++, guiFragment.getPluginCode());
        } else {
            stat.setNull(index++, Types.VARCHAR);
        }
        stat.setString(index++, guiFragment.getGui());
        stat.setString(index++, guiFragment.getCode());
        stat.executeUpdate();
    } catch (Throwable t) {
        _logger.error("Error updating guiFragment {}", guiFragment.getCode(), t);
        throw new RuntimeException("Error updating guiFragment", t);
    } finally {
        this.closeDaoResources(null, stat);
    }
}