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 insertPanden(final List<Pand> panden) throws DAOException {
    try {/* w w  w  .  j ava2 s.  c  o m*/
        jdbcTemplate.batchUpdate("insert into bag_pand (" + "bag_pand_id," + "aanduiding_record_inactief,"
                + "aanduiding_record_correctie," + "officieel," + "pand_geometrie," + "bouwjaar,"
                + "pand_status," + "begindatum_tijdvak_geldigheid," + "einddatum_tijdvak_geldigheid,"
                + "in_onderzoek," + "bron_documentdatum," + "bron_documentnummer"
                + ") values (?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setLong(1, panden.get(i).getIdentificatie());
                        ps.setInt(2, panden.get(i).getAanduidingRecordInactief().ordinal());
                        ps.setLong(3, panden.get(i).getAanduidingRecordCorrectie());
                        ps.setInt(4, panden.get(i).getOfficieel().ordinal());
                        ps.setString(5, panden.get(i).getPandGeometrie());
                        ps.setInt(6, panden.get(i).getBouwjaar());
                        ps.setString(7, panden.get(i).getPandStatus());
                        ps.setTimestamp(8,
                                new Timestamp(panden.get(i).getBegindatumTijdvakGeldigheid().getTime()));
                        if (panden.get(i).getEinddatumTijdvakGeldigheid() == null)
                            ps.setNull(9, Types.TIMESTAMP);
                        else
                            ps.setTimestamp(9,
                                    new Timestamp(panden.get(i).getEinddatumTijdvakGeldigheid().getTime()));
                        ps.setInt(10, panden.get(i).getInOnderzoek().ordinal());
                        ps.setDate(11, new Date(panden.get(i).getDocumentdatum().getTime()));
                        ps.setString(12, panden.get(i).getDocumentnummer());
                    }

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

From source file:com.oltpbenchmark.benchmarks.auctionmark.AuctionMarkLoader.java

/**
 * Load the tuples for the given table name
 * @param tableName//w  ww  . j av  a  2 s  .  co  m
 */
protected void generateTableData(String tableName) throws SQLException {
    LOG.info("*** START " + tableName);
    final AbstractTableGenerator generator = this.generators.get(tableName);
    assert (generator != null);

    // Generate Data
    final Table catalog_tbl = benchmark.getCatalog().getTable(tableName);
    assert (catalog_tbl != null) : tableName;
    final List<Object[]> volt_table = generator.getVoltTable();
    final String sql = SQLUtil.getInsertSQL(catalog_tbl);
    final PreparedStatement stmt = conn.prepareStatement(sql);
    final int types[] = catalog_tbl.getColumnTypes();

    while (generator.hasMore()) {
        generator.generateBatch();

        //            StringBuilder sb = new StringBuilder();
        //            if (tableName.equalsIgnoreCase("USER_FEEDBACK")) { //  || tableName.equalsIgnoreCase("USER_ATTRIBUTES")) {
        //                sb.append(tableName + "\n");
        //                for (int i = 0; i < volt_table.size(); i++) {
        //                    sb.append(String.format("[%03d] %s\n", i, StringUtil.abbrv(Arrays.toString(volt_table.get(i)), 100)));
        //                }
        //                LOG.info(sb.toString() + "\n");
        //            }

        for (Object row[] : volt_table) {
            for (int i = 0; i < row.length; i++) {
                if (row[i] != null) {
                    stmt.setObject(i + 1, row[i]);
                } else {
                    stmt.setNull(i + 1, types[i]);
                }
            } // FOR
            stmt.addBatch();
        } // FOR
        try {
            stmt.executeBatch();
            conn.commit();
            stmt.clearBatch();
        } catch (SQLException ex) {
            if (ex.getNextException() != null)
                ex = ex.getNextException();
            LOG.warn(tableName + " - " + ex.getMessage());
            throw ex;
            // SKIP
        }

        this.tableSizes.put(tableName, volt_table.size());

        // Release anything to the sub-generators if we have it
        // We have to do this to ensure that all of the parent tuples get
        // insert first for foreign-key relationships
        generator.releaseHoldsToSubTableGenerators();
    } // WHILE
    stmt.close();

    // Mark as finished
    if (this.fail == false) {
        generator.markAsFinished();
        synchronized (this) {
            this.finished.add(tableName);
            LOG.info(String.format("*** FINISH %s - %d tuples - [%d / %d]", tableName,
                    this.tableSizes.get(tableName), this.finished.size(), this.generators.size()));
            if (LOG.isDebugEnabled()) {
                LOG.debug("Remaining Tables: "
                        + CollectionUtils.subtract(this.generators.keySet(), this.finished));
            }
        } // SYNCH
    }
}

From source file:org.jamwiki.db.CacheQueryHandler.java

/**
 *
 *///from  ww  w. j ava2 s . c o  m
@Override
public void insertTopicVersions(List<TopicVersion> topicVersions) {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    boolean useBatch = (topicVersions.size() > 1);
    try {
        conn = DatabaseConnection.getConnection();
        if (!this.autoIncrementPrimaryKeys()) {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION);
        } else if (useBatch) {
            // generated keys don't work in batch mode
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT);
        } else {
            stmt = conn.prepareStatement(STATEMENT_INSERT_TOPIC_VERSION_AUTO_INCREMENT,
                    Statement.RETURN_GENERATED_KEYS);
        }
        int topicVersionId = -1;
        if (!this.autoIncrementPrimaryKeys() || useBatch) {
            // manually retrieve next topic version id when using batch
            // mode or when the database doesn't support generated keys.
            topicVersionId = DatabaseConnection.executeSequenceQuery(STATEMENT_SELECT_TOPIC_VERSION_SEQUENCE);
        }
        for (TopicVersion topicVersion : topicVersions) {
            if (!this.autoIncrementPrimaryKeys() || useBatch) {
                // FIXME - if two threads update the database simultaneously then
                // it is possible that this code could set the topic version ID
                // to a value that is different from what the database ends up
                // using.
                topicVersion.setTopicVersionId(topicVersionId++);
            }
            StringReader sr = null;
            try {
                int index = 1;
                stmt.setInt(index++, topicVersion.getTopicVersionId());
                if (topicVersion.getEditDate() == null) {
                    topicVersion.setEditDate(new Timestamp(System.currentTimeMillis()));
                }
                stmt.setInt(index++, topicVersion.getTopicId());
                stmt.setString(index++, topicVersion.getEditComment());
                //pass the content into a stream to be passed to Cach
                sr = new StringReader(topicVersion.getVersionContent());
                stmt.setCharacterStream(index++, sr, topicVersion.getVersionContent().length());
                if (topicVersion.getAuthorId() == null) {
                    stmt.setNull(index++, Types.INTEGER);
                } else {
                    stmt.setInt(index++, topicVersion.getAuthorId());
                }
                stmt.setInt(index++, topicVersion.getEditType());
                stmt.setString(index++, topicVersion.getAuthorDisplay());
                stmt.setTimestamp(index++, topicVersion.getEditDate());
                if (topicVersion.getPreviousTopicVersionId() == null) {
                    stmt.setNull(index++, Types.INTEGER);
                } else {
                    stmt.setInt(index++, topicVersion.getPreviousTopicVersionId());
                }
                stmt.setInt(index++, topicVersion.getCharactersChanged());
                stmt.setString(index++, topicVersion.getVersionParamString());
            } finally {
                if (sr != null) {
                    sr.close();
                }
            }
            if (useBatch) {
                stmt.addBatch();
            } else {
                stmt.executeUpdate();
            }
            if (this.autoIncrementPrimaryKeys() && !useBatch) {
                rs = stmt.getGeneratedKeys();
                if (!rs.next()) {
                    throw new SQLException("Unable to determine auto-generated ID for database record");
                }
                topicVersion.setTopicVersionId(rs.getInt(1));
            }
        }
        if (useBatch) {
            stmt.executeBatch();
        }
    } catch (SQLException e) {
        throw new UncategorizedSQLException("insertTopicVersions", null, e);
    } finally {
        DatabaseConnection.closeConnection(conn, stmt, rs);
    }
}

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

@Override
public void insertLigplaatsen(final List<Ligplaats> ligplaatsen) throws DAOException {
    try {/*w  w w.  j  a v  a  2s.c  o  m*/
        transactionTemplate.execute(new TransactionCallbackWithoutResult() {
            @Override
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                jdbcTemplate.batchUpdate("insert into bag_ligplaats (" + "bag_ligplaats_id,"
                        + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "officieel,"
                        + "ligplaats_status," + "ligplaats_geometrie," + "begindatum_tijdvak_geldigheid,"
                        + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "bron_documentdatum,"
                        + "bron_documentnummer," + "bag_nummeraanduiding_id"
                        + ") values (?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
                            @Override
                            public void setValues(PreparedStatement ps, int i) throws SQLException {
                                ps.setLong(1, ligplaatsen.get(i).getIdentificatie());
                                ps.setInt(2, ligplaatsen.get(i).getAanduidingRecordInactief().ordinal());
                                ps.setLong(3, ligplaatsen.get(i).getAanduidingRecordCorrectie());
                                ps.setInt(4, ligplaatsen.get(i).getOfficieel().ordinal());
                                ps.setInt(5, ligplaatsen.get(i).getLigplaatsStatus().ordinal());
                                ps.setString(6, ligplaatsen.get(i).getLigplaatsGeometrie());
                                ps.setTimestamp(7, new Timestamp(
                                        ligplaatsen.get(i).getBegindatumTijdvakGeldigheid().getTime()));
                                if (ligplaatsen.get(i).getEinddatumTijdvakGeldigheid() == null)
                                    ps.setNull(8, Types.TIMESTAMP);
                                else
                                    ps.setTimestamp(8, new Timestamp(
                                            ligplaatsen.get(i).getEinddatumTijdvakGeldigheid().getTime()));
                                ps.setInt(9, ligplaatsen.get(i).getInOnderzoek().ordinal());
                                ps.setDate(10, new Date(ligplaatsen.get(i).getDocumentdatum().getTime()));
                                ps.setString(11, ligplaatsen.get(i).getDocumentnummer());
                                ps.setLong(12, ligplaatsen.get(i).getHoofdAdres());
                            }

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

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

@Override
public void update(final Pand pand) throws DAOException {
    try {//from  w  w w .  j av a  2  s . co  m
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_pand set"
                        + " aanduiding_record_inactief = ?," + " officieel = ?," + " pand_geometrie = ?,"
                        + " bouwjaar = ?," + " pand_status = ?," + " einddatum_tijdvak_geldigheid = ?,"
                        + " in_onderzoek = ?," + " bron_documentdatum = ?," + " bron_documentnummer = ?"
                        + " where bag_pand_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, pand.getAanduidingRecordInactief().ordinal());
                ps.setInt(2, pand.getOfficieel().ordinal());
                ps.setString(3, pand.getPandGeometrie());
                ps.setInt(4, pand.getBouwjaar());
                ps.setString(5, pand.getPandStatus());
                if (pand.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(6, Types.TIMESTAMP);
                else
                    ps.setTimestamp(6, new Timestamp(pand.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(7, pand.getInOnderzoek().ordinal());
                ps.setDate(8, new Date(pand.getDocumentdatum().getTime()));
                ps.setString(9, pand.getDocumentnummer());
                ps.setLong(10, pand.getIdentificatie());
                ps.setLong(11, pand.getAanduidingRecordCorrectie());
                ps.setTimestamp(12, new Timestamp(pand.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating pand: " + pand.getIdentificatie(), e);
    }
}

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

@Override
public void insertStandplaatsen(final List<Standplaats> standplaatsen) throws DAOException {
    try {//w  w w .j  av a 2s  .c  o m
        transactionTemplate.execute(new TransactionCallbackWithoutResult() {
            @Override
            protected void doInTransactionWithoutResult(TransactionStatus status) {
                jdbcTemplate.batchUpdate("insert into bag_standplaats (" + "bag_standplaats_id,"
                        + "aanduiding_record_inactief," + "aanduiding_record_correctie," + "officieel,"
                        + "standplaats_status," + "standplaats_geometrie," + "begindatum_tijdvak_geldigheid,"
                        + "einddatum_tijdvak_geldigheid," + "in_onderzoek," + "bron_documentdatum,"
                        + "bron_documentnummer," + "bag_nummeraanduiding_id"
                        + ") values (?,?,?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
                            @Override
                            public void setValues(PreparedStatement ps, int i) throws SQLException {
                                ps.setLong(1, standplaatsen.get(i).getIdentificatie());
                                ps.setInt(2, standplaatsen.get(i).getAanduidingRecordInactief().ordinal());
                                ps.setLong(3, standplaatsen.get(i).getAanduidingRecordCorrectie());
                                ps.setInt(4, standplaatsen.get(i).getOfficieel().ordinal());
                                ps.setInt(5, standplaatsen.get(i).getStandplaatsStatus().ordinal());
                                ps.setString(6, standplaatsen.get(i).getStandplaatsGeometrie());
                                ps.setTimestamp(7, new Timestamp(
                                        standplaatsen.get(i).getBegindatumTijdvakGeldigheid().getTime()));
                                if (standplaatsen.get(i).getEinddatumTijdvakGeldigheid() == null)
                                    ps.setNull(8, Types.TIMESTAMP);
                                else
                                    ps.setTimestamp(8, new Timestamp(
                                            standplaatsen.get(i).getEinddatumTijdvakGeldigheid().getTime()));
                                ps.setInt(9, standplaatsen.get(i).getInOnderzoek().ordinal());
                                ps.setDate(10, new Date(standplaatsen.get(i).getDocumentdatum().getTime()));
                                ps.setString(11, standplaatsen.get(i).getDocumentnummer());
                                ps.setLong(12, standplaatsen.get(i).getHoofdAdres());
                            }

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

From source file:edu.pitt.apollo.db.ApolloDbUtils.java

public BigInteger[] addSimulationRun(RunMessage runMessage, int md5CollisionId,
        SoftwareIdentification identificationOfSoftwareToRun, int sourceSoftwareIdKey,
        SoftwareIdentification destinationSoftwareForRunSimulationMessage, Authentication authentication)
        throws ApolloDatabaseException, Md5UtilsException {

    String userName = authentication.getRequesterId();
    String password = authentication.getRequesterPassword();

    runMessage.setAuthentication(new Authentication());

    String[] userIdTokens = parseUserId(userName);
    userName = userIdTokens[0];/* www  . j  a  va 2  s. co m*/

    Integer softwareKey = null;
    if (identificationOfSoftwareToRun != null) {
        softwareKey = getSoftwareIdentificationKey(identificationOfSoftwareToRun);
    }
    int userKey = getUserKey(userName, password);

    BigInteger simulationGroupId = null;
    String additionalInsertField = "";
    String additionalParamHolder = "";
    BigInteger[] runIdSimulationGroupId = new BigInteger[2];
    String md5 = md5Utils.getMd5(runMessage);

    try (Connection conn = datasource.getConnection()) {

        simulationGroupId = getNewSimulationGroupId();
        runIdSimulationGroupId[1] = simulationGroupId;
        additionalInsertField = ", simulation_group_id";
        additionalParamHolder = ",?";

        String query = "INSERT IGNORE INTO run (md5_hash_of_run_message, software_id, requester_id, last_service_to_be_called, md5_collision_id "
                + additionalInsertField + ") VALUES (?, ?, ?, ?, ? " + additionalParamHolder + ")";
        PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, md5);
        if (softwareKey != null) {
            pstmt.setInt(2, softwareKey);
        } else {
            pstmt.setNull(2, Types.INTEGER);
        }
        pstmt.setInt(3, userKey);
        pstmt.setInt(4, 1);
        pstmt.setInt(5, md5CollisionId);
        pstmt.setLong(6, simulationGroupId.longValue());

        ResultSet rs;
        int rowsAffected = pstmt.executeUpdate();

        if (rowsAffected > 0) {
            rs = pstmt.getGeneratedKeys();
        } else {
            query = "SELECT id FROM run WHERE md5_hash_of_run_message = ? and md5_collision_id = ?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, md5);
            pstmt.setInt(2, md5CollisionId);
            rs = pstmt.executeQuery();
        }

        BigInteger runId;
        if (rs.next()) {
            runId = new BigInteger(rs.getString(1));
        } else {
            throw new ApolloDatabaseRecordNotInsertedException("Record not inserted!");
        }

        List<BigInteger> runIds = new ArrayList<>();
        runIds.add(runId);
        if (!(runMessage instanceof RunSimulationsMessage)) {
            addRunIdsToSimulationGroup(simulationGroupId, runIds);
        }

        // ALSO NEED TO ADD serialized runSimulationMessage(JSON) to
        // run_data_content table...
        // use insertDataContentForRun for this
        int dataContentKey = addTextDataContent(jsonUtils.getJSONString(runMessage));
        int runDataDescriptionId = getRunDataDescriptionId(ContentDataFormatEnum.TEXT, "run_message.json",
                ContentDataTypeEnum.RUN_MESSAGE, sourceSoftwareIdKey,
                getSoftwareIdentificationKey(destinationSoftwareForRunSimulationMessage));
        // int runDataId = the following line returns the runDataId, but
        // it's not used at this point.
        associateContentWithRunId(new BigInteger(String.valueOf(runId)), dataContentKey, runDataDescriptionId);

        runIdSimulationGroupId[0] = runId;
        if (runIdSimulationGroupId.length == 2) {
            runIdSimulationGroupId[1] = simulationGroupId;
        }

        updateStatusOfRun(runId, MethodCallStatusEnum.LOADED_RUN_CONFIG_INTO_DATABASE,
                "Adding config information to the database for runId: " + runId.toString());

        return runIdSimulationGroupId;
        // } catch (ClassNotFoundException ex) {
        //     throw new ApolloDatabaseException(
        //             "ClassNotFoundException attempting to add simulation run: "
        //                     + ex.getMessage());
    } catch (SQLException ex) {
        throw new ApolloDatabaseException("SQLException attempting to add simulation run: " + ex.getMessage());
    }
}

From source file:cz.lbenda.dataman.db.RowDesc.java

@SuppressWarnings("ConstantConditions")
private <T> void putToPS(ColumnDesc columnDesc, T value, PreparedStatement ps, int position)
        throws SQLException {
    if (value == null) {
        ps.setObject(position, null);/*w w w .  java  2  s. c  o m*/
        return;
    }
    BinaryData bd = value instanceof BinaryData ? (BinaryData) value : null;
    switch (columnDesc.getDataType()) {
    case STRING:
        ps.setString(position, (String) value);
        break;
    case BOOLEAN:
        ps.setBoolean(position, (Boolean) value);
        break;
    case TIMESTAMP:
        ps.setTimestamp(position, (Timestamp) value);
        break;
    case DATE:
        ps.setDate(position, (Date) value);
        break;
    case TIME:
        ps.setTime(position, (Time) value);
        break;
    case BYTE:
        ps.setByte(position, (Byte) value);
        break;
    case SHORT:
        ps.setShort(position, (Short) value);
        break;
    case INTEGER:
        ps.setInt(position, (Integer) value);
        break;
    case LONG:
        ps.setLong(position, (Long) value);
        break;
    case FLOAT:
        ps.setFloat(position, (Float) value);
        break;
    case DOUBLE:
        ps.setDouble(position, (Double) value);
        break;
    case DECIMAL:
        ps.setBigDecimal(position, (BigDecimal) value);
        break;
    case UUID:
        ps.setBytes(position, AbstractHelper.uuidToByteArray((UUID) value));
        break;
    case ARRAY:
        throw new UnsupportedOperationException("The saving changes in ARRAY isn't supported.");
        // ps.setArray(position, (Array) value); break; // FIXME the value isn't in type java.sql.Array
    case BYTE_ARRAY:
        if (bd == null || bd.isNull()) {
            ps.setBytes(position, null);
        } else {
            try {
                ps.setBytes(position, IOUtils.toByteArray(bd.getInputStream()));
            } catch (IOException e) {
                throw new SQLException(e);
            }
        }
        break;
    case CLOB:
        if (bd == null || bd.isNull()) {
            ps.setNull(position, Types.CLOB);
        } else {
            ps.setClob(position, bd.getReader());
        }
        break;
    case BLOB:
        if (bd == null || bd.isNull()) {
            ps.setNull(position, Types.BLOB);
        } else {
            ps.setBlob(position, bd.getInputStream());
        }
        break;
    case OBJECT:
        ps.setObject(position, value);
    }
}

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

/**
 * Inserts a record into a database table.
 * <p>/*from w ww  .  j a v a 2  s  .c  o  m*/
 * If the primary key is included in Criteria, then that value will
 * be used to insert the row.
 * <p>
 * Otherwise, if the primary key can be generated automatically,
 * the generated key will be used for the insert and will be returned.
 * <p>
 * If no value is given for the primary key is defined and it cannot
 * be generated automatically or the table has no primary key,
 * the values will be inserted as specified and null will be returned.
 *
 * @param insertValues Contains the values to insert, not null.
 * @param connection the connection to use for the insert, not null.
 *
 * @return the primary key of the inserted row (if the table
 *         has a primary key) or null (if the table does not have
 *         a primary key).
 *
 * @throws TorqueException if a database error occurs.
 */
public ObjectKey doInsert(ColumnValues insertValues, Connection connection) throws TorqueException {
    if (insertValues == null) {
        throw new TorqueException("insertValues is null");
    }
    if (connection == null) {
        throw new TorqueException("connection is null");
    }
    String databaseNameFromInsertValues = insertValues.getDbName();
    if (databaseNameFromInsertValues == null) {
        databaseNameFromInsertValues = getDatabaseName();
    }
    Database database = Torque.getDatabase(databaseNameFromInsertValues);
    Object keyInfo = getIdMethodInfo();
    IdGenerator keyGen = database.getIdGenerator(getTableMap().getPrimaryKeyMethod());

    SimpleKey id = null;
    // can currently generate only single column pks, therefore a single
    // columnMap is ok
    ColumnMap primaryKey = null;
    if (keyGen != null) {
        // fail on multiple pks
        primaryKey = getTableMap().getPrimaryKey();

        // primaryKey will be null if there is no primary key
        // defined for the table we're inserting into.
        if (keyGen.isPriorToInsert() && primaryKey != null && !insertValues.containsKey(primaryKey)) {
            id = getId(primaryKey, keyGen, connection, keyInfo);
            insertValues.put(primaryKey, new JdbcTypedValue(id.getValue(), id.getJdbcType()));
        }
    }

    List<String> columnNames = new ArrayList<String>();
    List<JdbcTypedValue> replacementObjects = new ArrayList<JdbcTypedValue>();
    for (Map.Entry<Column, JdbcTypedValue> columnValue : insertValues.entrySet()) {
        Column column = columnValue.getKey();
        columnNames.add(column.getColumnName());
        JdbcTypedValue value = columnValue.getValue();
        replacementObjects.add(value);
    }

    String fullTableName = SqlBuilder.getFullTableName(getTableMap().getFullyQualifiedTableName(),
            databaseNameFromInsertValues);
    StringBuilder query = new StringBuilder("INSERT INTO ").append(fullTableName).append("(")
            .append(StringUtils.join(columnNames, ",")).append(") VALUES (");
    for (int i = 0; i < columnNames.size(); ++i) {
        if (i != 0) {
            query.append(",");
        }
        query.append("?");
    }
    query.append(")");

    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = connection.prepareStatement(query.toString());
        int position = 1;
        for (JdbcTypedValue replacementObject : replacementObjects) {
            Object value = replacementObject.getValue();
            if (value != null) {
                if (replacementObject.getJdbcType() != Types.BLOB
                        && replacementObject.getJdbcType() != Types.CLOB) {
                    preparedStatement.setObject(position, value, replacementObject.getJdbcType());
                } else {
                    preparedStatement.setObject(position, value);
                }
            } else {
                preparedStatement.setNull(position, replacementObject.getJdbcType());
            }
            position++;
        }
        long startTime = System.currentTimeMillis();
        log.debug("Executing insert " + query.toString() + " using parameters " + replacementObjects);

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

        preparedStatement.close();
        preparedStatement = null;
    } 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);
            }
        }
    }

    // If the primary key column is auto-incremented, get the id
    // now.
    if (keyGen != null && keyGen.isPostInsert() && primaryKey != null
            && !insertValues.containsKey(primaryKey)) {
        id = getId(primaryKey, keyGen, connection, keyInfo);
    }

    return id;
}

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

@Override
public void update(final Woonplaats woonplaats) throws DAOException {
    try {//from   ww  w.j  ava  2  s .co m
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement("update bag_woonplaats set"
                        + " aanduiding_record_inactief = ?," + " woonplaats_naam = ?,"
                        + " woonplaats_geometrie = ?," + " officieel = ?,"
                        + " einddatum_tijdvak_geldigheid = ?," + " in_onderzoek = ?,"
                        + " bron_documentdatum = ?," + " bron_documentnummer = ?," + " woonplaats_status = ?"
                        + " where bag_woonplaats_id = ?" + " and aanduiding_record_correctie = ?"
                        + " and begindatum_tijdvak_geldigheid = ?");
                ps.setInt(1, woonplaats.getAanduidingRecordInactief().ordinal());
                ps.setString(2, woonplaats.getWoonplaatsNaam());
                ps.setString(3, woonplaats.getWoonplaatsGeometrie());
                ps.setInt(4, woonplaats.getOfficieel().ordinal());
                if (woonplaats.getEinddatumTijdvakGeldigheid() == null)
                    ps.setNull(5, Types.TIMESTAMP);
                else
                    ps.setTimestamp(5, new Timestamp(woonplaats.getEinddatumTijdvakGeldigheid().getTime()));
                ps.setInt(6, woonplaats.getInOnderzoek().ordinal());
                ps.setDate(7, new Date(woonplaats.getDocumentdatum().getTime()));
                ps.setString(8, woonplaats.getDocumentnummer());
                ps.setInt(9, woonplaats.getWoonplaatsStatus().ordinal());
                ps.setLong(10, woonplaats.getIdentificatie());
                ps.setLong(11, woonplaats.getAanduidingRecordCorrectie());
                ps.setTimestamp(12, new Timestamp(woonplaats.getBegindatumTijdvakGeldigheid().getTime()));
                return ps;
            }
        });
    } catch (DataAccessException e) {
        throw new DAOException("Error updating woonplaats: " + woonplaats.getIdentificatie(), e);
    }
}