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:com.webpagebytes.cms.local.WPBLocalDataStoreDao.java

private int buildStatementForInsertUpdate(Object obj, Set<String> ignoreFields,
        PreparedStatement preparedStatement, Connection connection)
        throws SQLException, WPBSerializerException {
    Class<? extends Object> kind = obj.getClass();
    Field[] fields = kind.getDeclaredFields();
    int fieldIndex = 0;
    for (int i = 0; i < fields.length; i++) {
        Field field = fields[i];//from  w  ww  .  ja  v  a 2  s  . c o m
        field.setAccessible(true);
        boolean storeField = (field.getAnnotation(WPBAdminFieldKey.class) != null)
                || (field.getAnnotation(WPBAdminFieldStore.class) != null)
                || (field.getAnnotation(WPBAdminFieldTextStore.class) != null);
        if (storeField) {
            String fieldName = field.getName();
            if (ignoreFields != null && ignoreFields.contains(fieldName)) {
                continue;
            }
            fieldIndex = fieldIndex + 1;
            Object value = null;
            try {
                PropertyDescriptor pd = new PropertyDescriptor(fieldName, kind);
                value = pd.getReadMethod().invoke(obj);
            } catch (Exception e) {
                throw new WPBSerializerException("Cannot get property value", e);
            }
            if (field.getType() == Long.class) {
                Long valueLong = (Long) value;
                if (valueLong != null) {
                    preparedStatement.setLong(fieldIndex, valueLong);
                } else {
                    preparedStatement.setNull(fieldIndex, Types.BIGINT);
                }
            } else if (field.getType() == String.class) {

                String valueString = (String) value;
                if (field.getAnnotation(WPBAdminFieldStore.class) != null
                        || field.getAnnotation(WPBAdminFieldKey.class) != null) {
                    if (valueString != null) {
                        preparedStatement.setString(fieldIndex, valueString);
                    } else {
                        preparedStatement.setNull(fieldIndex, Types.VARCHAR);
                    }
                } else if (field.getAnnotation(WPBAdminFieldTextStore.class) != null) {
                    if (valueString != null) {
                        Clob clob = connection.createClob();
                        clob.setString(1, valueString);
                        preparedStatement.setClob(fieldIndex, clob);
                    } else {
                        preparedStatement.setNull(fieldIndex, Types.CLOB);
                    }
                }
            } else if (field.getType() == Integer.class) {
                Integer valueInt = (Integer) value;
                if (valueInt != null) {
                    preparedStatement.setInt(fieldIndex, valueInt);
                } else {
                    preparedStatement.setNull(fieldIndex, Types.INTEGER);
                }
            } else if (field.getType() == Date.class) {
                Date date = (Date) value;
                if (date != null) {
                    java.sql.Timestamp sqlDate = new java.sql.Timestamp(date.getTime());
                    preparedStatement.setTimestamp(fieldIndex, sqlDate);
                } else {
                    preparedStatement.setNull(fieldIndex, Types.DATE);
                }
            }
        }
    }
    return fieldIndex;
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

private void setPreparedStatmentForCurrentColumn(PreparedStatement ps, int index, CSVColumnState column,
        ProfileRecipientFields bean, ImportProfile importProfile, ValidatorResults validatorResults)
        throws SQLException {
    String value = Toolkit.getValueFromBean(bean, column.getColName());
    if (column.getType() == CSVColumnState.TYPE_NUMERIC && column.getColName().equals("gender")) {
        if (StringUtils.isEmpty(value) || value == null) {
            ps.setInt(index, 2);/*w  ww.ja  va2 s .c o  m*/
        } else {
            if (GenericValidator.isInt(value) && Integer.valueOf(value) <= 5 && Integer.valueOf(value) >= 0) {
                ps.setInt(index, Integer.valueOf(value));
            } else {
                final Integer intValue = importProfile.getGenderMapping().get(value);
                ps.setInt(index, intValue);
            }
        }

    } else if (column.getType() == CSVColumnState.TYPE_CHAR) {
        if (value == null) {
            ps.setNull(index, Types.VARCHAR);
        } else {
            String columnName = column.getColName();
            if (columnName.equals("email")) {
                value = value.toLowerCase();
                if (validatorResults != null
                        && !ImportUtils.checkIsCurrentFieldValid(validatorResults, "email", "checkRange")) {
                    throw new ImportRecipientsToolongValueException(value);
                }
            } else if (importProfile.getKeyColumns().contains(columnName)
                    || (importProfile.getKeyColumns().isEmpty()
                            && columnName.equals(importProfile.getKeyColumn()))) {
                // range validation for keyColumn
                if (validatorResults != null
                        && !ImportUtils.checkIsCurrentFieldValid(validatorResults, columnName, "checkRange")) {
                    throw new ImportRecipientsToolongValueException(value);
                }
            }
            if (AgnUtils.isOracleDB()) {
                ps.setString(index, value);
            } else if (AgnUtils.isMySQLDB()) {
                if (column.isNullable() && value.isEmpty()) {
                    ps.setNull(index, Types.VARCHAR);
                } else {
                    ps.setString(index, value);
                }
            }

        }
    } else if (column.getType() == CSVColumnState.TYPE_NUMERIC) {
        if (StringUtils.isEmpty(value) || value == null) {
            ps.setNull(index, Types.NUMERIC);
        } else {
            ps.setDouble(index, Double.valueOf(value));
        }
    } else if (column.getType() == CSVColumnState.TYPE_DATE) {
        if (StringUtils.isEmpty(value) || value == null) {
            ps.setNull(index, Types.DATE);
        } else {
            Date date = ImportUtils.getDateAsString(value, importProfile.getDateFormat());

            ps.setTimestamp(index, new Timestamp(date.getTime()));
        }
    }
}

From source file:com.draagon.meta.manager.db.driver.GenericSQLDriver.java

/**
 * Sets a specific value on a prepared statement
 *//*  w  ww  . j  a v a 2 s  . co m*/
protected void setStatementValue(PreparedStatement s, MetaField f, int index, Object value)
        throws SQLException {
    int j = index;

    switch (f.getType()) {
    case MetaField.BOOLEAN: {
        if (value == null) {
            s.setNull(j, Types.BIT);
        } else if (value instanceof Boolean) {
            s.setBoolean(j, ((Boolean) value).booleanValue());
        } else {
            s.setBoolean(j, Boolean.valueOf(value.toString()).booleanValue());
        }
    }
        break;

    case MetaField.BYTE: {
        if (value == null) {
            s.setNull(j, Types.TINYINT);
        } else if (value instanceof Byte) {
            s.setByte(j, ((Byte) value).byteValue());
        } else {
            s.setByte(j, Byte.valueOf(value.toString()).byteValue());
        }
    }
        break;

    case MetaField.SHORT: {
        if (value == null) {
            s.setNull(j, Types.SMALLINT);
        } else if (value instanceof Short) {
            s.setShort(j, ((Short) value).shortValue());
        } else {
            s.setShort(j, Short.valueOf(value.toString()).shortValue());
        }
    }
        break;

    case MetaField.INT: {
        if (value == null) {
            s.setNull(j, Types.INTEGER);
        } else if (value instanceof Integer) {
            s.setInt(j, ((Integer) value).intValue());
        } else {
            s.setInt(j, Integer.valueOf(value.toString()).intValue());
        }
    }
        break;

    case MetaField.DATE: // NOTE DATE IS TREATED AS LONG!
    {
        if (value == null) {
            s.setNull(j, Types.TIMESTAMP);
        } else if (value instanceof java.util.Date) {
            s.setTimestamp(j, new Timestamp(((java.util.Date) value).getTime()));
        } else {
            s.setTimestamp(j, new Timestamp(Long.valueOf(value.toString()).longValue()));
        }
    }
        break;

    case MetaField.LONG: {
        if (value == null) {
            s.setNull(j, Types.BIGINT);
        } else if (value instanceof Long) {
            s.setLong(j, ((Long) value).longValue());
        } else {
            s.setLong(j, Long.valueOf(value.toString()).longValue());
        }
    }
        break;

    // WARNING:  This should not be a valid key
    case MetaField.FLOAT: {
        if (value == null) {
            s.setNull(j, Types.FLOAT);
        } else if (value instanceof Float) {
            s.setFloat(j, ((Float) value).floatValue());
        } else {
            s.setFloat(j, Float.valueOf(value.toString()).floatValue());
        }
    }
        break;

    // WARNING:  This should not be a valid key
    case MetaField.DOUBLE: {
        if (value == null) {
            s.setNull(j, Types.DOUBLE);
        } else if (value instanceof Double) {
            s.setDouble(j, ((Double) value).doubleValue());
        } else {
            s.setDouble(j, Double.valueOf(value.toString()).doubleValue());
        }
    }
        break;

    case MetaField.STRING:
        if (value == null) {
            s.setNull(j, Types.VARCHAR);
        } else {
            s.setString(j, value.toString());
        }
        break;

    case MetaField.OBJECT:
        //if ( value == null )
        //  s.setNull( j, Types.BLOB );
        //else
        s.setObject(j, value);
        break;
    }
}

From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java

public int executeFeatureInsertLowLevel(String sql, SimpleFeature feature, List<ColumnVO> columns)
        throws Exception {
    Exception error = null;//from ww  w.j av a2 s .  c o m

    int numRowsAffected = 0;

    if (columns.size() > 0) {
        Connection connection = null;
        PreparedStatement preparedStmnt = null;

        try {
            DataSource dataSource = poolDataSources.get(schemaId);
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            preparedStmnt = connection.prepareStatement(sql);

            int paramPosition = 1;
            for (ColumnVO column : columns) {
                String dataValue = null;
                Object attribute = feature.getAttribute(column.getFilePosition());
                if (attribute != null) {
                    dataValue = attribute.toString();
                }
                Integer dataType = column.getSqlType();
                if (dataType == Types.OTHER) { // it is a geometry
                    // ((org.postgresql.PGConnection)connection).addDataType(column.getName(),
                    // column.getTypeClass());
                    preparedStmnt.setObject(paramPosition, dataValue);
                } else {
                    if (StringUtils.isEmpty(dataValue)) {
                        preparedStmnt.setNull(paramPosition, dataType);
                    } else {
                        preparedStmnt.setObject(paramPosition, dataValue, dataType);
                    }
                }
                paramPosition++;
            }

            numRowsAffected = preparedStmnt.executeUpdate();

            connection.commit();
        } catch (SQLException e) {
            error = e;
        } finally {
            if (preparedStmnt != null) {
                try {
                    preparedStmnt.close();
                } catch (SQLException se2) {
                    log.warn("No se pudo cerrar el statment: ".concat(se2.getLocalizedMessage()));
                }
            }
            if (connection != null) {
                try {
                    if (error != null) {
                        connection.rollback();
                    }
                } catch (SQLException se) {
                    log.warn("Se produjo un error al manejar la conexin: ".concat(se.getLocalizedMessage()));
                }
                try {
                    connection.close();
                } catch (SQLException se) {
                    log.warn("Se produjo un error al intentar cerrar la conexin: "
                            .concat(se.getLocalizedMessage()));
                }
            }
        }
        if (error != null) {
            throw error;
        }
    }
    return numRowsAffected;
}

From source file:org.apache.qpid.server.store.derby.DerbyMessageStore.java

private void insertConfiguredObject(ConfiguredObjectRecord configuredObject) throws AMQStoreException {
    if (_stateManager.isInState(State.ACTIVE)) {
        try {/*from  w ww.  j a  v  a  2  s.  c  o m*/
            Connection conn = newAutoCommitConnection();
            try {
                PreparedStatement stmt = conn.prepareStatement(FIND_CONFIGURED_OBJECT);
                try {
                    stmt.setString(1, configuredObject.getId().toString());
                    ResultSet rs = stmt.executeQuery();
                    try {
                        // If we don't have any data in the result set then we can add this configured object
                        if (!rs.next()) {
                            PreparedStatement insertStmt = conn
                                    .prepareStatement(INSERT_INTO_CONFIGURED_OBJECTS);
                            try {
                                insertStmt.setString(1, configuredObject.getId().toString());
                                insertStmt.setString(2, configuredObject.getType());
                                if (configuredObject.getAttributes() == null) {
                                    insertStmt.setNull(3, Types.BLOB);
                                } else {
                                    byte[] attributesAsBytes = configuredObject.getAttributes()
                                            .getBytes(UTF8_CHARSET);
                                    ByteArrayInputStream bis = new ByteArrayInputStream(attributesAsBytes);
                                    insertStmt.setBinaryStream(3, bis, attributesAsBytes.length);
                                }
                                insertStmt.execute();
                            } finally {
                                insertStmt.close();
                            }
                        }
                    } finally {
                        rs.close();
                    }
                } finally {
                    stmt.close();
                }
            } finally {
                conn.close();
            }
        } catch (SQLException e) {
            throw new AMQStoreException("Error inserting of configured object " + configuredObject
                    + " into database: " + e.getMessage(), e);
        }
    }
}

From source file:dbutils.DbUtilsTemplate.java

public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {

    // check the parameter count, if we can
    ParameterMetaData pmd = null;
    if (!pmdKnownBroken) {
        pmd = stmt.getParameterMetaData();
        int stmtCount = pmd.getParameterCount();
        int paramsCount = params == null ? 0 : params.length;

        if (stmtCount != paramsCount) {
            throw new SQLException(
                    "Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount);
        }//from w w  w  .ja v  a 2s . c  om
    }

    // nothing to do here
    if (params == null) {
        return;
    }

    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type. Oddly, NULL and
            // OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!pmdKnownBroken) {
                try {
                    /*
                     * It's not possible for pmdKnownBroken to change from
                     * true to false, (once true, always true) so pmd cannot
                     * be null here.
                     */
                    sqlType = pmd.getParameterType(i + 1);
                } catch (SQLException e) {
                    pmdKnownBroken = true;
                }
            }
            stmt.setNull(i + 1, sqlType);
        }
    }
}

From source file:org.apache.torque.util.BasePeerImpl.java

/**
 * Utility method which executes a given sql statement
 * as prepared statement.//from ww w. ja v  a 2 s.c o m
 * This method should be used for update, insert, and delete statements.
 * Use executeQuery() for selects.
 *
 * @param statementString A String with the sql statement to execute.
 * @param con The database connection to use.
 * @param replacementValues values to use as placeholders in the query.
 *        or null or empty if no placeholders need to be filled.
 *
 * @return The number of rows affected.
 *
 * @throws TorqueException if executing the statement fails.
 */
public int executeStatement(String statementString, Connection con, List<JdbcTypedValue> replacementValues)
        throws TorqueException {
    int rowCount = -1;
    PreparedStatement statement = null;
    try {
        statement = con.prepareStatement(statementString);
        if (replacementValues != null) {
            int position = 1;
            for (JdbcTypedValue replacementValue : replacementValues) {
                if (replacementValue.getValue() == null) {
                    statement.setNull(position, replacementValue.getJdbcType());
                } else {
                    statement.setObject(position, replacementValue.getValue(), replacementValue.getJdbcType());
                }
                ++position;
            }
        }
        rowCount = statement.executeUpdate();
    } catch (SQLException e) {
        throw new TorqueException(e);
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new TorqueException(e);
            }
        }
    }
    return rowCount;
}

From source file:org.hxzon.util.db.springjdbc.StatementCreatorUtils.java

/**
 * Set the specified PreparedStatement parameter to null,
 * respecting database-specific peculiarities.
 *///from w  w  w  .  j  a v  a 2  s. c om
private static void setNull(PreparedStatement ps, int paramIndex, int sqlType, String typeName)
        throws SQLException {
    if (sqlType == SqlTypeValue.TYPE_UNKNOWN) {
        boolean useSetObject = false;
        Integer sqlTypeToUse = null;
        DatabaseMetaData dbmd = null;
        String jdbcDriverName = null;
        boolean checkGetParameterType = !shouldIgnoreGetParameterType;
        if (checkGetParameterType && !driversWithNoSupportForGetParameterType.isEmpty()) {
            try {
                dbmd = ps.getConnection().getMetaData();
                jdbcDriverName = dbmd.getDriverName();
                checkGetParameterType = !driversWithNoSupportForGetParameterType.contains(jdbcDriverName);
            } catch (Throwable ex) {
                logger.debug("Could not check connection metadata", ex);
            }
        }
        if (checkGetParameterType) {
            try {
                sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex);
            } catch (Throwable ex) {
                if (logger.isDebugEnabled()) {
                    logger.debug(
                            "JDBC 3.0 getParameterType call not supported - using fallback method instead: "
                                    + ex);
                }
            }
        }
        if (sqlTypeToUse == null) {
            // JDBC driver not compliant with JDBC 3.0 -> proceed with database-specific checks
            sqlTypeToUse = Types.NULL;
            try {
                if (dbmd == null) {
                    dbmd = ps.getConnection().getMetaData();
                }
                if (jdbcDriverName == null) {
                    jdbcDriverName = dbmd.getDriverName();
                }
                if (checkGetParameterType) {
                    driversWithNoSupportForGetParameterType.add(jdbcDriverName);
                }
                String databaseProductName = dbmd.getDatabaseProductName();
                if (databaseProductName.startsWith("Informix")
                        || jdbcDriverName.startsWith("Microsoft SQL Server")) {
                    useSetObject = true;
                } else if (databaseProductName.startsWith("DB2") || jdbcDriverName.startsWith("jConnect")
                        || jdbcDriverName.startsWith("SQLServer")
                        || jdbcDriverName.startsWith("Apache Derby")) {
                    sqlTypeToUse = Types.VARCHAR;
                }
            } catch (Throwable ex) {
                logger.debug("Could not check connection metadata", ex);
            }
        }
        if (useSetObject) {
            ps.setObject(paramIndex, null);
        } else {
            ps.setNull(paramIndex, sqlTypeToUse);
        }
    } else if (typeName != null) {
        ps.setNull(paramIndex, sqlType, typeName);
    } else {
        ps.setNull(paramIndex, sqlType);
    }
}

From source file:org.openanzo.datasource.nodecentric.sql.Backup.java

/**
 * Runs the restoreNamedGraph prepared statement.
  * <code>//w  w w .j ava  2s. c  om
 *          INSERT INTO NAMEDGRAPHS (HSTART,HEND, ID, METAID,UUID,REVISION,LASTMODIFIEDBY,COMMITTED) VALUES (?,?, ?, ?,?, ?,?,0);     
 * </code>
 *
 *@param stmtProvider
 *         factory and cache of PreparedStatments
 *@param connection
 *          connection to underlying database
 *
 *@param start template parameter
 *@param end template parameter
 *@param namedgraphid template parameter
 *@param metadataId template parameter
 *@param uuid template parameter
 *@param revision template parameter
 *@param lastModifiedBy template parameter
 *
 *@return  int
 *@throws  org.openanzo.jdbc.utils.RdbException
 */
public static int restoreNamedGraph(final org.openanzo.jdbc.utils.PreparedStatementProvider stmtProvider,
        final java.sql.Connection connection, long start, Long end, long namedgraphid, long metadataId,
        long uuid, long revision, long lastModifiedBy) throws org.openanzo.jdbc.utils.RdbException {
    java.sql.PreparedStatement ps = null;
    //long startTimer=System.currentTimeMillis();
    try {
        ps = stmtProvider.getPreparedSQLStatement(restoreNamedGraph, new String[] {}, connection);
        int argc = 1;
        ps.setLong(argc++, start);
        if (end == null) {
            ps.setNull(argc++, java.sql.Types.BIGINT);
        } else {
            ps.setLong(argc++, end);
        }
        ps.setLong(argc++, namedgraphid);
        ps.setLong(argc++, metadataId);
        ps.setLong(argc++, uuid);
        ps.setLong(argc++, revision);
        ps.setLong(argc++, lastModifiedBy);
        int counter = 0;
        try {
            counter = ps.executeUpdate();
        } catch (java.sql.SQLException sqle) {
            if (sqle.getErrorCode() == 1205) {
                int retries = 0;
                while (retries < 5) {
                    try {
                        Thread.sleep(5000);
                    } catch (InterruptedException ie) {
                        throw sqle;
                    }
                    try {
                        counter = ps.executeUpdate();
                        break;
                    } catch (java.sql.SQLException sqleInner) {
                        if (sqleInner.getErrorCode() == 1205) {
                            retries++;
                        } else {
                            throw sqleInner;
                        }
                    }
                }
                if (retries >= 5) {
                    throw sqle;
                }
            } else {
                throw sqle;
            }
        }
        return counter;

    } catch (java.sql.SQLException e) {
        throw new org.openanzo.jdbc.utils.RdbException(
                org.openanzo.exceptions.ExceptionConstants.RDB.FAILED_EXECUTING_SQL, e, "restoreNamedGraph",
                stmtProvider.getSqlString(restoreNamedGraph),
                "" + "start=" + (start) + "," + "end=" + ((end != null) ? end.toString() : "null") + ","
                        + "namedgraphid=" + (namedgraphid) + "," + "metadataId=" + (metadataId) + "," + "uuid="
                        + (uuid) + "," + "revision=" + (revision) + "," + "lastModifiedBy=" + (lastModifiedBy),
                "");
    } finally {
        if (ps != null) {
            try {
                ps.close();
            } catch (java.sql.SQLException sqle) {
                if (log.isDebugEnabled())
                    log.debug(org.openanzo.exceptions.LogUtils.RDB_MARKER, "Error closing prepared statement",
                            sqle);
            }
        }
        //long endtimer=(System.currentTimeMillis()-startTimer);
        //if(endtimer>CUTOFF)System.out.println("[restoreNamedGraph]"+endtimer);
    }
}