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:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void delete(final Standplaats standplaats) {
    try {/* ww w . java2s.co  m*/
        deleteNevenadressen(TypeAdresseerbaarObject.STANDPLAATS, standplaats);
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("delete from bag_standplaats"
                        + " where bag_standplaats_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?");
                ps.setLong(1, standplaats.getIdentificatie());
                ps.setLong(2, standplaats.getAanduidingRecordCorrectie());
                ps.setTimestamp(3, new Timestamp(standplaats.getBegindatumTijdvakGeldigheid().getTime()));
                if (standplaats.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(4, Types.TIMESTAMP);
                else
                    ps.setTimestamp(4, new Timestamp(standplaats.getEinddatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException(e);
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void delete(final OpenbareRuimte openbareRuimte) {
    try {/*from   ww  w  .  j av  a  2 s .co  m*/
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("delete from bag_openbare_ruimte"
                        + " where bag_openbare_ruimte_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?");
                ps.setLong(1, openbareRuimte.getIdentificatie());
                ps.setLong(2, openbareRuimte.getAanduidingRecordCorrectie());
                ps.setTimestamp(3, new Timestamp(openbareRuimte.getBegindatumTijdvakGeldigheid().getTime()));
                if (openbareRuimte.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(4, Types.TIMESTAMP);
                else
                    ps.setTimestamp(4, new Timestamp(openbareRuimte.getEinddatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException(e);
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void insertNummeraanduidingen(final List<Nummeraanduiding> nummeraanduidingen) throws DAOException {
    try {//from w w w. j  a  va  2s . c o  m
        jdbcTemplate.batchUpdate("insert into bag_nummeraanduiding (" + "bag_nummeraanduiding_id,"
                + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "huisnummer," + "officieel,"
                + "huisletter," + "huisnummertoevoeging," + "postcode," + "begindatum_tijdvak_geldigheid,"
                + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "type_adresseerbaar_object,"
                + "bron_documentdatum," + "bron_documentnummer," + "nummeraanduiding_status,"
                + "bag_woonplaats_id," + "bag_openbare_ruimte_id"
                + ") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setLong(1, nummeraanduidingen.get(i).getIdentificatie());
                        ps.setInt(2, nummeraanduidingen.get(i).getAanduidingRecordInactief().ordinal());
                        ps.setLong(3, nummeraanduidingen.get(i).getAanduidingRecordCorrectie());
                        ps.setInt(4, nummeraanduidingen.get(i).getHuisnummer());
                        ps.setInt(5, nummeraanduidingen.get(i).getOfficieel().ordinal());
                        if (nummeraanduidingen.get(i).getHuisletter() == null)
                            ps.setNull(6, Types.VARCHAR);
                        else
                            ps.setString(6, nummeraanduidingen.get(i).getHuisletter());
                        if (nummeraanduidingen.get(i).getHuisnummertoevoeging() == null)
                            ps.setNull(7, Types.VARCHAR);
                        else
                            ps.setString(7, nummeraanduidingen.get(i).getHuisnummertoevoeging());
                        if (nummeraanduidingen.get(i).getPostcode() == null)
                            ps.setNull(8, Types.VARCHAR);
                        else
                            ps.setString(8, nummeraanduidingen.get(i).getPostcode());
                        ps.setTimestamp(9, new Timestamp(
                                nummeraanduidingen.get(i).getBegindatumTijdvakGeldigheid().getTime()));
                        if (nummeraanduidingen.get(i).getEinddatumTijdvakGeldigheid() == null)
                            ps.setNull(10, Types.TIMESTAMP);
                        else
                            ps.setTimestamp(10, new Timestamp(
                                    nummeraanduidingen.get(i).getEinddatumTijdvakGeldigheid().getTime()));
                        ps.setInt(11, nummeraanduidingen.get(i).getInOnderzoek().ordinal());
                        ps.setInt(12, nummeraanduidingen.get(i).getTypeAdresseerbaarObject().ordinal());
                        ps.setDate(13, new Date(nummeraanduidingen.get(i).getDocumentdatum().getTime()));
                        ps.setString(14, nummeraanduidingen.get(i).getDocumentnummer());
                        ps.setInt(15, nummeraanduidingen.get(i).getNummeraanduidingStatus().ordinal());
                        if (nummeraanduidingen.get(i).getGerelateerdeWoonplaats() == null)
                            ps.setNull(16, Types.INTEGER);
                        else
                            ps.setLong(16, nummeraanduidingen.get(i).getGerelateerdeWoonplaats());
                        ps.setLong(17, nummeraanduidingen.get(i).getGerelateerdeOpenbareRuimte());
                    }

                    @Override
                    public int getBatchSize() {
                        return nummeraanduidingen.size();
                    }
                });
    } catch (DataAccessException e) {
        throw new DAOException("Error inserting nummeraanduidingen", e);
    }
}

From source file:architecture.common.spring.jdbc.core.ExtendedJdbcTemplate.java

public int[] batchUpdate(String sql, final List<ParameterMapping> parameterMappings,
        final List<Map<String, Object>> parameters) {
    return batchUpdate(sql, new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            Map<String, Object> row = parameters.get(i);
            for (ParameterMapping mapping : parameterMappings) {
                JdbcType jdbcType = mapping.getJdbcType();
                Object value = row.get(mapping.getProperty());
                Object valueToUse = value;

                if (valueToUse == null && mapping.getJavaType() == Date.class) {
                    valueToUse = new Date();
                }//  w  w w .j  a va2s  .c om

                if (valueToUse instanceof Date && jdbcType == JdbcType.VARCHAR) {
                    valueToUse = DateFormatUtils.format((Date) valueToUse, mapping.getPattern());
                }

                if (valueToUse instanceof String && jdbcType == JdbcType.VARCHAR) {
                    String stringValue = (String) valueToUse;
                    if (!StringUtils.isEmpty(mapping.getEncoding())) {
                        if (!StringUtils.isEmpty(stringValue)) {
                            String[] encoding = StringUtils.split(mapping.getEncoding(), ">");
                            try {
                                if (encoding.length == 2)
                                    valueToUse = new String(stringValue.getBytes(encoding[0]), encoding[1]);
                                else if (encoding.length == 1)
                                    valueToUse = new String(stringValue.getBytes(), encoding[0]);

                            } catch (UnsupportedEncodingException e) {
                                logger.error(e);
                            }
                        }
                    }
                }
                if (valueToUse == null)
                    ps.setNull(mapping.getIndex(), jdbcType.TYPE_CODE);
                else
                    ps.setObject(mapping.getIndex(), valueToUse, jdbcType.TYPE_CODE);
            }
        }

        public int getBatchSize() {
            return parameters.size();
        }
    });
}

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

/**
 * Executes an update against the database. The rows to be updated
 * are selected using <code>criteria</code> and updated using the values
 * in <code>updateValues</code>.
 *
 * @param criteria selects which rows of which table should be updated.
 * @param updateValues Which columns to update with which values, not null.
 * @param connection the database connection to use, not null.
 *
 * @return the number of affected rows./*from   w  w  w.  j av  a  2 s  .c  om*/
 *
 * @throws TorqueException if updating fails.
 */
public int doUpdate(org.apache.torque.criteria.Criteria criteria, ColumnValues updateValues,
        Connection connection) throws TorqueException {
    Query query = SqlBuilder.buildQuery(criteria);
    query.setType(Query.Type.UPDATE);

    query.getFromClause().clear();
    String fullTableName = SqlBuilder.getFullTableName(getTableMap().getFullyQualifiedTableName(),
            criteria.getDbName());
    query.getFromClause().add(new FromElement(fullTableName));

    List<JdbcTypedValue> replacementObjects = new ArrayList<JdbcTypedValue>();
    for (Map.Entry<Column, JdbcTypedValue> updateValue : updateValues.entrySet()) {
        Column column = updateValue.getKey();
        query.getSelectClause().add(column.getColumnName());
        replacementObjects.add(updateValue.getValue());
    }

    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = connection.prepareStatement(query.toString());
        int position = 1;
        for (JdbcTypedValue replacementObject : replacementObjects) {
            Object value = replacementObject.getValue();
            if (value != null) {
                preparedStatement.setObject(position, value);
            } else {
                preparedStatement.setNull(position, replacementObject.getJdbcType());
            }
            position++;
        }
        List<Object> replacements = setPreparedStatementReplacements(preparedStatement,
                query.getPreparedStatementReplacements(), position - 1);
        long startTime = System.currentTimeMillis();
        log.debug("Executing update " + query.toString() + " using update parameters " + replacementObjects
                + " and query parameters " + replacements);

        int affectedRows = preparedStatement.executeUpdate();
        long queryEndTime = System.currentTimeMillis();
        log.trace("update took " + (queryEndTime - startTime) + " milliseconds");

        preparedStatement.close();
        preparedStatement = null;
        return affectedRows;
    } catch (SQLException e) {
        throw ExceptionMapper.getInstance().toTorqueException(e);
    } finally {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                log.warn("error closing prepared statement", e);
            }
        }
    }
}

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

/**
 * Executes an update against the database. The rows to be updated
 * are selected using <code>criteria</code> and updated using the values
 * in <code>updateValues</code>.
 *
 * @param criteria selects which rows of which table should be updated.
 * @param updateValues Which columns to update with which values, not null.
 * @param connection the database connection to use, not null.
 *
 * @return the number of affected rows.//from w ww  . java  2s  . co  m
 *
 * @throws TorqueException if updating fails.
 *
 * @deprecated Please use doUpdate(org.apache.torque.criteria.Criteria,
 *                 ColumnValues, Connection).
 *             This method will be removed in a future version of Torque.
 */
@Deprecated
public int doUpdate(Criteria criteria, ColumnValues updateValues, Connection connection)
        throws TorqueException {
    Query query = SqlBuilder.buildQuery(criteria);
    query.setType(Query.Type.UPDATE);

    query.getFromClause().clear();
    String fullTableName = SqlBuilder.getFullTableName(getTableMap().getFullyQualifiedTableName(),
            criteria.getDbName());
    query.getFromClause().add(new FromElement(fullTableName));

    List<JdbcTypedValue> replacementObjects = new ArrayList<JdbcTypedValue>();
    for (Map.Entry<Column, JdbcTypedValue> updateValue : updateValues.entrySet()) {
        Column column = updateValue.getKey();
        query.getSelectClause().add(column.getColumnName());
        replacementObjects.add(updateValue.getValue());
    }

    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = connection.prepareStatement(query.toString());
        int position = 1;
        for (JdbcTypedValue replacementObject : replacementObjects) {
            Object value = replacementObject.getValue();
            if (value != null) {
                preparedStatement.setObject(position, value);
            } else {
                preparedStatement.setNull(position, replacementObject.getJdbcType());
            }
            position++;
        }
        List<Object> replacements = setPreparedStatementReplacements(preparedStatement,
                query.getPreparedStatementReplacements(), position - 1);
        long startTime = System.currentTimeMillis();
        log.debug("Executing update " + query.toString() + " using update parameters " + replacementObjects
                + " and query parameters " + replacements);

        int affectedRows = preparedStatement.executeUpdate();
        long queryEndTime = System.currentTimeMillis();
        log.trace("update took " + (queryEndTime - startTime) + " milliseconds");

        preparedStatement.close();
        preparedStatement = null;
        return affectedRows;
    } catch (SQLException e) {
        throw ExceptionMapper.getInstance().toTorqueException(e);
    } finally {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                log.warn("error closing prepared statement", e);
            }
        }
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void delete(final Verblijfsobject verblijfsobject) {
    try {//from w ww.ja v a  2 s  .  c  om
        deleteGebruikersdoelen(verblijfsobject);
        deleteNevenadressen(TypeAdresseerbaarObject.VERBLIJFSOBJECT, verblijfsobject);
        deleteGerelateerdePanden(verblijfsobject);
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("delete from bag_verblijfsobject"
                        + " where bag_verblijfsobject_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?");
                ps.setLong(1, verblijfsobject.getIdentificatie());
                ps.setLong(2, verblijfsobject.getAanduidingRecordCorrectie());
                ps.setTimestamp(3, new Timestamp(verblijfsobject.getBegindatumTijdvakGeldigheid().getTime()));
                if (verblijfsobject.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(4, Types.TIMESTAMP);
                else
                    ps.setTimestamp(4,
                            new Timestamp(verblijfsobject.getEinddatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException(e);
    }
}

From source file:nl.ordina.bag.etl.dao.AbstractBAGDAO.java

@Override
public void delete(final Nummeraanduiding nummeraanduiding) {
    try {/*from   w  w w.ja  v  a  2s . com*/
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("delete from bag_nummeraanduiding"
                        + " where bag_nummeraanduiding_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?" + " and einddatum_tijdvak_geldigheid = ?");
                ps.setLong(1, nummeraanduiding.getIdentificatie());
                ps.setLong(2, nummeraanduiding.getAanduidingRecordCorrectie());
                ps.setTimestamp(3, new Timestamp(nummeraanduiding.getBegindatumTijdvakGeldigheid().getTime()));
                if (nummeraanduiding.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(4, Types.TIMESTAMP);
                else
                    ps.setTimestamp(4,
                            new Timestamp(nummeraanduiding.getEinddatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException(e);
    }
}

From source file:org.openbel.framework.core.kam.JdbcKAMLoaderImpl.java

/**
 * Saves an entry to the object table./*from w ww . j ava 2  s . c  o m*/
 *
 * @param tid {@code int}, the object type id
 * @param v {@link String}, the non-null object value
 * @return {@code int}, the object primary key
 * @throws SQLException - Thrown if a sql error occurred saving an entry to
 * the object table
 */
protected int saveObject(int tid, String v) throws SQLException {
    final String objectsIdColumn = (dbConnection.isPostgresql() ? OBJECTS_ID_COLUMN_POSTGRESQL
            : OBJECTS_ID_COLUMN);
    PreparedStatement ps = getPreparedStatement(OBJECTS_SQL, new String[] { objectsIdColumn });
    ResultSet rs = null;

    if (v == null) {
        throw new InvalidArgument("object value cannot be null");
    }

    try {
        String encryptedString = encryptionService.encrypt(v);

        // Insert into objects_text if we are over MAX_VARCHAR_LENGTH
        Integer objectsTextId = null;
        if (encryptedString.length() > MAX_VARCHAR_LENGTH) {
            final String objectsTextColumn = (dbConnection.isPostgresql() ? OBJECTS_TEXT_COLUMN_POSTGRESQL
                    : OBJECTS_TEXT_COLUMN);
            PreparedStatement otps = getPreparedStatement(OBJECTS_TEXT_SQL, new String[] { objectsTextColumn });
            ResultSet otrs = null;
            StringReader sr = null;

            try {
                sr = new StringReader(encryptedString);
                otps.setClob(1, sr, encryptedString.length());
                otps.execute();
                otrs = otps.getGeneratedKeys();
                if (otrs.next()) {
                    objectsTextId = otrs.getInt(1);
                }
            } finally {
                close(otrs);

                if (sr != null) {
                    sr.close();
                }
            }
        }

        // FIXME Hardcoding objects_type to 1?
        ps.setInt(1, 1);

        if (objectsTextId == null) {
            // insert value into objects table
            ps.setString(2, encryptedString);
            ps.setNull(3, Types.INTEGER);
        } else {
            ps.setNull(2, Types.VARCHAR);
            ps.setInt(3, objectsTextId);
        }

        ps.execute();
        rs = ps.getGeneratedKeys();
        int oid;
        if (rs.next()) {
            oid = rs.getInt(1);
        } else {
            throw new IllegalStateException("object insert failed.");
        }
        return oid;
    } catch (EncryptionServiceException e) {
        throw new SQLException("Unable to encrypt string for object table.", e);
    } finally {
        close(rs);

    }
}

From source file:com.wso2telco.core.dbutils.AxiataDbService.java

/**
 * Insert merchant provision.//ww  w .j  a  v a 2s .  c om
 *
 * @param appID the app id
 * @param subscriber the subscriber
 * @param operator the operator
 * @param merchants the merchants
 * @return true, if successful
 * @throws Exception the exception
 */
public boolean insertMerchantProvision(Integer appID, String subscriber, String operator, String[] merchants)
        throws Exception {

    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    PreparedStatement pst = null;

    try {
        con = DbUtils.getAxiataDBConnection();

        st = con.createStatement();
        String sql = "SELECT id " + "FROM operators " + "WHERE operatorname = '" + operator + "'";

        rs = st.executeQuery(sql);

        int operatorid = 0;
        if (rs.next()) {
            operatorid = rs.getInt("id");
        } else {
            throw new Exception("Operator Not Found");
        }

        pst = null;
        for (int i = 0; i < merchants.length; i++) {
            sql = "INSERT INTO merchantopco_blacklist (application_id, operator_id, subscriber, merchant) VALUES "
                    + "(?, ?, ?, ?)";

            pst = con.prepareStatement(sql);
            if (appID == null) {
                pst.setNull(1, Types.INTEGER);
            } else {
                pst.setInt(1, appID);
            }
            pst.setInt(2, operatorid);
            pst.setString(3, subscriber);
            pst.setString(4, merchants[i]);
            pst.executeUpdate();
        }

    } catch (Exception e) {
        DbUtils.handleException("Error while Provisioning Merchant", e);
    } finally {
        DbUtils.closeAllConnections(st, con, rs);
        DbUtils.closeAllConnections(pst, null, null);
    }
    return true;
}