Example usage for java.sql PreparedStatement setTimestamp

List of usage examples for java.sql PreparedStatement setTimestamp

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

Introduction

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

Prototype

void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Timestamp value.

Usage

From source file:dk.netarkivet.harvester.datamodel.HarvestDefinitionDBDAO.java

/**
 * Create a harvest definition in Database. The harvest definition object
 * should not have its ID set unless we are in the middle of migrating.
 *
 * @param harvestDefinition//from ww w  .  j  a va  2s  . c  o m
 *            A new harvest definition to store in the database.
 * @return The harvestId for the just created harvest definition.
 * @see HarvestDefinitionDAO#create(HarvestDefinition)
 */
@Override
public synchronized Long create(HarvestDefinition harvestDefinition) {
    Connection connection = HarvestDBConnection.get();
    PreparedStatement s = null;
    try {
        Long id = harvestDefinition.getOid();
        if (id == null) {
            id = generateNextID(connection);
        }

        connection.setAutoCommit(false);
        s = connection.prepareStatement(
                "INSERT INTO harvestdefinitions " + "( harvest_id, name, comments, numevents, submitted,"
                        + "  isactive, edition, audience ) " + "VALUES ( ?, ?, ?, ?, ?, ?, ?,? )");
        s.setLong(1, id);
        DBUtils.setName(s, 2, harvestDefinition, Constants.MAX_NAME_SIZE);
        DBUtils.setComments(s, 3, harvestDefinition, Constants.MAX_COMMENT_SIZE);
        s.setLong(4, harvestDefinition.getNumEvents());
        Date submissiondate = new Date();
        // Don't set on object, as we may yet rollback
        s.setTimestamp(5, new Timestamp(submissiondate.getTime()));
        s.setBoolean(6, harvestDefinition.getActive());
        final int edition = 1;
        s.setLong(7, edition);
        s.setString(8, harvestDefinition.getAudience());
        s.executeUpdate();
        s.close();
        if (harvestDefinition instanceof FullHarvest) {
            FullHarvest fh = (FullHarvest) harvestDefinition;
            s = connection.prepareStatement("INSERT INTO fullharvests " + "( harvest_id, maxobjects, maxbytes,"
                    + " maxjobrunningtime, previoushd, isindexready)" + "VALUES ( ?, ?, ?, ?, ?, ? )");
            s.setLong(1, id);
            s.setLong(2, fh.getMaxCountObjects());
            s.setLong(3, fh.getMaxBytes());
            s.setLong(4, fh.getMaxJobRunningTime());
            if (fh.getPreviousHarvestDefinition() != null) {
                s.setLong(5, fh.getPreviousHarvestDefinition().getOid());
            } else {
                s.setNull(5, Types.BIGINT);
            }
            s.setBoolean(6, fh.getIndexReady());
            s.executeUpdate();
        } else if (harvestDefinition instanceof PartialHarvest) {
            PartialHarvest ph = (PartialHarvest) harvestDefinition;
            // Get schedule id
            long scheduleId = DBUtils.selectLongValue(connection,
                    "SELECT schedule_id FROM schedules WHERE name = ?", ph.getSchedule().getName());
            s = connection.prepareStatement("INSERT INTO partialharvests "
                    + "( harvest_id, schedule_id, nextdate ) " + "VALUES ( ?, ?, ? )");
            s.setLong(1, id);
            s.setLong(2, scheduleId);
            DBUtils.setDateMaybeNull(s, 3, ph.getNextDate());
            s.executeUpdate();
            createHarvestConfigsEntries(connection, ph, id);
        } else {
            String message = "Harvest definition " + harvestDefinition + " is of unknown class "
                    + harvestDefinition.getClass();
            log.warn(message);
            throw new ArgumentNotValid(message);
        }
        connection.commit();

        // Now that we have committed, set new data on object.
        harvestDefinition.setSubmissionDate(submissiondate);
        harvestDefinition.setEdition(edition);
        harvestDefinition.setOid(id);

        // saving after receiving id
        saveExtendedFieldValues(connection, harvestDefinition);

        return id;
    } catch (SQLException e) {
        String message = "SQL error creating harvest definition " + harvestDefinition + " in database" + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } finally {
        DBUtils.closeStatementIfOpen(s);
        DBUtils.rollbackIfNeeded(connection, "creating", harvestDefinition);
        HarvestDBConnection.release(connection);
    }
}

From source file:com.flexoodb.engines.FlexJAXBMappedDBDataEngine.java

private void updatePreparedStatement(String tablename, Hashtable<String, Object[]> fieldswithcontent,
        PreparedStatement ps) throws Exception {

    Enumeration en = fieldswithcontent.keys();
    int i = 0;//from ww w .  j av a2  s  .  c o  m
    while (en.hasMoreElements()) {
        i++;

        try {
            String field = (String) en.nextElement();
            Object[] o2 = fieldswithcontent.get(field);

            String type = (String) o2[0];
            Object o = o2[1];

            //System.out.println(field+" "+type+" "+o);
            if (type.equals("string")) {
                ps.setString(i, (String) o);
            } else if (type.equals("byte[]") || type.equals("base64Binary")) {
                ps.setBinaryStream(i, new ByteArrayInputStream((byte[]) o));
            } else if (type.equals("dateTime")) {
                XMLGregorianCalendar cal = (XMLGregorianCalendar) o;
                Date d = null;

                if (cal.toString().indexOf("0003-11-30") > -1) {
                    ps.setString(i, "0000-00-00 00:00:00");
                } else {
                    d = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString());
                    ps.setTimestamp(i, new java.sql.Timestamp(d.getTime()));
                }
            } else if (type.equals("date")) {
                XMLGregorianCalendar cal = (XMLGregorianCalendar) o;
                Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(cal.toString());
                ps.setDate(i, java.sql.Date.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(d1)));
            } else if (type.equals("time")) {
                XMLGregorianCalendar cal = (XMLGregorianCalendar) o;
                String c = cal.toString();
                c = c.replaceFirst("0003-11-30", "0000-00-00");
                Date d1 = (new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S")).parse(c);
                ps.setTime(i, new java.sql.Time(d1.getTime()));

            } else if (type.equals("integer")) {
                ps.setInt(i, ((BigInteger) o).intValue());
            } else if (type.equals("double")) {
                ps.setDouble(i, (Double) o);
            } else if (type.equals("float")) {
                ps.setFloat(i, (Float) o);
            } else if (type.equals("long")) {
                ps.setLong(i, (Long) o);
            } else {
                throw new Exception("unknown type [" + type + "] for field [" + field
                        + "] encountered while trying to update table [" + tablename + "].");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }

    }
}

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

@Override
public void insertPanden(final List<Pand> panden) throws DAOException {
    try {//from ww  w.java 2  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:edu.ku.brc.specify.conversion.ConvertMiscData.java

/**
 * /*from   ww  w  .j a  v a  2  s. c  om*/
 */
public static void convertObservations(final Connection oldDBConn, final Connection newDBConn,
        final int disciplineID) {
    IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn);

    String sql = "SELECT cc.CollectionObjectCatalogID, o.ObservationID, o.Text1, o.Text2, o.Number1, o.Remarks ";
    String baseSQL = " FROM collectionobjectcatalog AS cc Inner Join observation AS o ON cc.CollectionObjectCatalogID = o.BiologicalObjectID";
    String ORDERBY = " ORDER BY cc.CollectionObjectCatalogID";

    Calendar cal = Calendar.getInstance();
    Timestamp tsCreated = new Timestamp(cal.getTimeInMillis());
    IdMapperIFace coMapper = IdMapperMgr.getInstance().get("collectionobjectcatalog",
            "CollectionObjectCatalogID");
    if (coMapper == null) {
        coMapper = IdMapperMgr.getInstance().addTableMapper("collectionobjectcatalog",
                "CollectionObjectCatalogID", false);
    }

    int totalCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) " + baseSQL);
    if (totalCnt < 1)
        return;

    Statement stmt = null;
    PreparedStatement pStmt = null;
    PreparedStatement updateStmt = null;
    PreparedStatement insertStmt = null;
    PreparedStatement updateCOStmt = null;
    try {
        pStmt = newDBConn.prepareStatement(
                "SELECT co.CollectionObjectAttributeID FROM collectionobject AS co WHERE co.CollectionObjectID = ? AND co.CollectionObjectAttributeID IS NOT NULL");
        updateStmt = newDBConn.prepareStatement(
                "UPDATE collectionobjectattribute SET Text1=?, Text2=?, Number1=?, Remarks=? WHERE CollectionObjectAttributeID = ?");
        insertStmt = newDBConn.prepareStatement(
                "INSERT INTO collectionobjectattribute (Version, TimestampCreated, CollectionMemberID, CreatedByAgentID, Text1, Text2, Number1, Remarks) VALUES(0, ?, ?, ?, ?, ?, ?, ?)",
                Statement.RETURN_GENERATED_KEYS);
        updateCOStmt = newDBConn.prepareStatement(
                "UPDATE collectionobject SET CollectionObjectAttributeID=? WHERE CollectionObjectID = ?");

        int cnt = 0;

        stmt = oldDBConn.createStatement();
        ResultSet rs = stmt.executeQuery(sql + baseSQL + ORDERBY);
        while (rs.next()) {
            int ccId = rs.getInt(1);
            String text1 = rs.getString(3);
            String text2 = rs.getString(4);
            Integer number1 = rs.getInt(5);
            String remarks = rs.getString(6);
            Integer newId = coMapper.get(ccId);
            if (newId == null) {
                log.error("Old Co Id [" + ccId + "] didn't map to new ID.");
                continue;
            }

            pStmt.setInt(1, newId);
            ResultSet rs2 = pStmt.executeQuery();
            if (rs2.next()) {
                updateStmt.setString(1, text1);
                updateStmt.setString(2, text2);
                updateStmt.setInt(3, number1);
                updateStmt.setString(4, remarks);
                updateStmt.setInt(5, rs2.getInt(1));
                if (updateStmt.executeUpdate() != 1) {
                    log.error("Error updating collectionobjectattribute");
                }
            } else {
                int memId = BasicSQLUtils.getCountAsInt(
                        "SELECT CollectionMemberID FROM collectionobject WHERE CollectionObjectID = " + newId);
                insertStmt.setTimestamp(1, tsCreated);
                insertStmt.setInt(2, memId);
                insertStmt.setInt(3, 1); // Created By Agent
                insertStmt.setString(4, text1);
                insertStmt.setString(5, text2);
                insertStmt.setInt(6, number1);
                insertStmt.setString(7, remarks);

                if (insertStmt.executeUpdate() != 1) {
                    log.error("Error inserting collectionobjectattribute");
                }

                int newCOAId = BasicSQLUtils.getInsertedId(insertStmt);

                updateCOStmt.setInt(1, newCOAId);
                updateCOStmt.setInt(2, newId);
                if (updateCOStmt.executeUpdate() != 1) {
                    log.error(
                            "Error updating collectionobject newCOAId[" + newCOAId + "] newId[" + newId + "]");
                }
            }
            rs2.close();

            cnt++;
            if (cnt % 1000 == 0) {
                System.out.println(String.format("%d / %d", cnt, totalCnt));
            }
        }
        rs.close();

    } catch (Exception e) {
        e.printStackTrace();

    } finally {
        try {
            if (stmt != null)
                stmt.close();
            if (pStmt != null)
                pStmt.close();
            if (updateStmt != null)
                updateStmt.close();
            if (insertStmt != null)
                insertStmt.close();
            if (updateCOStmt != null)
                updateCOStmt.close();

        } catch (SQLException ex) {
        }
    }
}

From source file:fr.aliacom.obm.common.contact.ContactDaoJdbcImpl.java

private ContactUpdates findUpdatedContacts(String sql, Date timestamp, AccessToken at) throws SQLException {
    Connection con = null;//from w  w w  .  j a  v  a  2s. c  o m
    PreparedStatement ps = null;
    ResultSet rs = null;

    ContactUpdates upd = new ContactUpdates();
    try {

        List<Contact> contacts = new ArrayList<Contact>();
        Set<Integer> archivedContactIds = new TreeSet<Integer>();

        con = obmHelper.getConnection();
        ps = con.prepareStatement(sql);

        int idx = 1;
        ps.setInt(idx++, at.getObmId());
        ps.setInt(idx++, at.getObmId());
        ps.setInt(idx++, at.getObmId());
        ps.setInt(idx++, at.getObmId());
        ps.setTimestamp(idx++, new Timestamp(timestamp.getTime()));
        ps.setTimestamp(idx++, new Timestamp(timestamp.getTime()));
        ps.setTimestamp(idx++, new Timestamp(timestamp.getTime()));
        rs = ps.executeQuery();

        Map<EntityId, Contact> entityContact = new HashMap<EntityId, Contact>();
        while (rs.next()) {
            boolean archived = rs.getBoolean("contact_archive");
            Contact c = contactFromCursor(rs);
            if (!archived) {
                entityContact.put(c.getEntityId(), c);
                contacts.add(c);
            } else {
                archivedContactIds.add(c.getUid());
            }
        }
        rs.close();
        rs = null;

        if (!entityContact.isEmpty()) {
            loadPhones(con, entityContact);
            loadIMIdentifiers(con, entityContact);
            loadWebsites(con, entityContact);
            loadAddresses(at, con, entityContact);
            loadEmails(con, entityContact);
            loadBirthday(con, entityContact);
            loadAnniversary(con, entityContact);
        }

        upd.setArchived(archivedContactIds);
        upd.setContacts(contacts);

    } finally {
        obmHelper.cleanup(con, ps, rs);
    }
    return upd;
}

From source file:com.concursive.connect.web.modules.documents.dao.FileItem.java

/**
 * Description of the Method/* w  ww. j ava 2  s .c  o  m*/
 *
 * @param db Description of Parameter
 * @return Description of the Returned Value
 * @throws SQLException Description of Exception
 */
public boolean insert(Connection db) throws SQLException {
    if (!isValid()) {
        LOG.debug("Object validation failed");
        return false;
    }

    boolean result = false;
    boolean doCommit = false;
    try {
        if (doCommit = db.getAutoCommit()) {
            db.setAutoCommit(false);
        }
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO project_files "
                + "(folder_id, subject, client_filename, filename, version, size, ");
        sql.append("enabled, downloads, ");
        if (entered != null) {
            sql.append("entered, ");
        }
        if (modified != null) {
            sql.append("modified, ");
        }
        sql.append(" link_module_id, link_item_id, "
                + " enteredby, modifiedby, default_file, image_width, image_height, comment, featured_file) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ");
        if (entered != null) {
            sql.append("?, ");
        }
        if (modified != null) {
            sql.append("?, ");
        }
        sql.append("?, ?, ?, ?, ?, ?, ?, ?, ?) ");

        int i = 0;
        PreparedStatement pst = db.prepareStatement(sql.toString());
        if (folderId > 0) {
            pst.setInt(++i, folderId);
        } else {
            pst.setNull(++i, java.sql.Types.INTEGER);
        }
        pst.setString(++i, subject);
        pst.setString(++i, clientFilename);
        pst.setString(++i, filename);
        pst.setDouble(++i, version);
        pst.setInt(++i, size);
        pst.setBoolean(++i, enabled);
        pst.setInt(++i, downloads);
        if (entered != null) {
            pst.setTimestamp(++i, entered);
        }
        if (modified != null) {
            pst.setTimestamp(++i, modified);
        }
        pst.setInt(++i, linkModuleId);
        pst.setInt(++i, linkItemId);
        pst.setInt(++i, enteredBy);
        pst.setInt(++i, modifiedBy);
        pst.setBoolean(++i, defaultFile);
        pst.setInt(++i, imageWidth);
        pst.setInt(++i, imageHeight);
        pst.setString(++i, comment);
        pst.setBoolean(++i, featuredFile);
        pst.execute();
        pst.close();
        id = DatabaseUtils.getCurrVal(db, "project_files_item_id_seq", -1);
        // New default item
        if (defaultFile) {
            updateDefaultRecord(db, linkModuleId, linkItemId, id);
        }
        // Insert the version information
        if (doVersionInsert) {
            FileItemVersion thisVersion = new FileItemVersion();
            thisVersion.setId(this.getId());
            thisVersion.setSubject(subject);
            thisVersion.setClientFilename(clientFilename);
            thisVersion.setFilename(filename);
            thisVersion.setVersion(version);
            thisVersion.setSize(size);
            thisVersion.setEnteredBy(enteredBy);
            thisVersion.setModifiedBy(modifiedBy);
            thisVersion.setImageWidth(imageWidth);
            thisVersion.setImageHeight(imageHeight);
            thisVersion.setComment(comment);
            thisVersion.insert(db);
        }
        logUpload(db);
        if (doCommit) {
            db.commit();
        }
        result = true;
    } catch (Exception e) {
        e.printStackTrace(System.out);
        if (doCommit) {
            db.rollback();
        }
        throw new SQLException(e.getMessage());
    } finally {
        if (doCommit) {
            db.setAutoCommit(true);
        }
    }
    return result;
}

From source file:com.tremolosecurity.provisioning.core.providers.BasicDB.java

private void insertCreate(User user, Set<String> attributes, Map<String, Attribute> attrs, Connection con,
        Map<String, Object> request) throws SQLException, ProvisioningException {

    int approvalID = 0;

    if (request.containsKey("APPROVAL_ID")) {
        approvalID = (Integer) request.get("APPROVAL_ID");
    }/*from  www .j  a  va2s. c o  m*/

    Workflow workflow = (Workflow) request.get("WORKFLOW");

    StringBuffer insert = new StringBuffer();
    insert.append("INSERT INTO ").append(this.userTable).append(" (");
    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            getFieldName(attr, insert).append(",");
        }
    }

    insert.setLength(insert.length() - 1);
    insert.append(") values (");
    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            insert.append("?,");
        }
    }
    insert.setLength(insert.length() - 1);

    insert.append(")");

    PreparedStatement ps = con.prepareStatement(insert.toString(), Statement.RETURN_GENERATED_KEYS);
    int i = 1;

    for (String attr : attributes) {
        if (attrs.get(attr) != null) {

            Attribute.DataType dataType = attrs.get(attr).getDataType();

            switch (dataType) {
            case string:
                ps.setString(i, attrs.get(attr).getValues().get(0));
                break;
            case intNum:
                ps.setInt(i, Integer.parseInt(attrs.get(attr).getValues().get(0)));
                break;
            case longNum:
                ps.setLong(i, Long.parseLong(attrs.get(attr).getValues().get(0)));
                break;

            case date:
                ps.setDate(i, new Date(ISODateTimeFormat.date()
                        .parseDateTime(attrs.get(attr).getValues().get(0)).getMillis()));
                break;
            case timeStamp:
                ps.setTimestamp(i, new Timestamp(ISODateTimeFormat.dateTime()
                        .parseDateTime(attrs.get(attr).getValues().get(0)).getMillis()));
                break;
            }

            i++;
        }

    }

    ps.executeUpdate();
    ResultSet rs = ps.getGeneratedKeys();

    int id;

    if (rs.next() && !this.driver.contains("oracle")) {

        id = (int) rs.getInt(1);
    } else {
        StringBuffer select = new StringBuffer();
        select.append("SELECT ");
        this.getFieldName(this.userPrimaryKey, select).append(" FROM ").append(this.userTable)
                .append(" WHERE ");
        this.getFieldName(this.userName, select).append("=?");
        PreparedStatement getUserId = con.prepareStatement(select.toString()); //con.prepareStatement( + this.userPrimaryKey + " FROM " + this.userTable + " WHERE " + this.userName + "=?");
        getUserId.setString(1, user.getUserID());
        ResultSet userResult = getUserId.executeQuery();
        userResult.next();
        id = (int) userResult.getInt(this.userPrimaryKey);

        userResult.close();
        getUserId.close();
    }

    this.cfgMgr.getProvisioningEngine().logAction(this.name, true, ActionType.Add, approvalID, workflow,
            "userName", user.getUserID());

    for (String attr : attributes) {
        if (attrs.get(attr) != null) {
            this.cfgMgr.getProvisioningEngine().logAction(this.name, false, ActionType.Add, approvalID,
                    workflow, attr, attrs.get(attr).getValues().get(0));
        }
    }

    if (user.getGroups().size() > 0) {
        switch (this.groupMode) {
        case None:
            break;
        case One2Many:
            insert.setLength(0);
            insert.append("INSERT INTO ").append(this.groupTable).append(" (").append(this.groupUserKey)
                    .append(",").append(this.groupName).append(") VALUES (?,?)");
            ps = con.prepareStatement(insert.toString());

            for (String groupName : user.getGroups()) {
                ps.setInt(1, id);
                ps.setString(2, groupName);
                ps.executeUpdate();
                this.cfgMgr.getProvisioningEngine().logAction(this.name, false, ActionType.Add, approvalID,
                        workflow, "group", groupName);
            }

            break;
        case Many2Many:
            many2manySetGroupsCreate(user, insert, con, id, request);
            break;
        }

    }
}

From source file:net.unicon.mercury.fac.rdbms.RdbmsMessageFactory.java

private void insertInitialUsageDate(String username, Timestamp ts, Connection conn) throws SQLException {
    PreparedStatement pstmt = null;

    try {//from www  . j a va  2 s. c om
        int i = 1;
        pstmt = conn.prepareStatement(qm.getQuery("SET_INITIAL_USAGE"));
        pstmt.setString(i++, username);
        pstmt.setTimestamp(i++, ts);
        pstmt.execute();
    } finally {
        closeStatement(pstmt);
    }
}

From source file:dk.netarkivet.harvester.datamodel.HarvestDefinitionDBDAO.java

/**
 * Activates or deactivates a partial harvest definition. This method is
 * actually to be used not to have to read from the DB big harvest
 * definitions and optimize the activation / deactivation, it is sort of a
 * lightweight version of update.//  ww w  .j  a  va2 s  . c o  m
 *
 * @param harvestDefinition
 *            the harvest definition object.
 */
@Override
public synchronized void flipActive(SparsePartialHarvest harvestDefinition) {
    ArgumentNotValid.checkNotNull(harvestDefinition, "HarvestDefinition harvestDefinition");

    Connection c = HarvestDBConnection.get();
    PreparedStatement s = null;
    try {
        if (harvestDefinition.getOid() == null || !exists(c, harvestDefinition.getOid())) {
            final String message = "Cannot update non-existing " + "harvestdefinition '"
                    + harvestDefinition.getName() + "'";
            log.debug(message);
            throw new PermissionDenied(message);
        }

        c.setAutoCommit(false);
        s = c.prepareStatement("UPDATE harvestdefinitions SET " + "name = ?, " + "comments = ?, "
                + "numevents = ?, " + "submitted = ?," + "isactive = ?," + "edition = ?, audience = ? "
                + "WHERE harvest_id = ? AND edition = ?");
        DBUtils.setName(s, 1, harvestDefinition, Constants.MAX_NAME_SIZE);
        DBUtils.setComments(s, 2, harvestDefinition, Constants.MAX_COMMENT_SIZE);
        s.setInt(3, harvestDefinition.getNumEvents());
        s.setTimestamp(4, new Timestamp(harvestDefinition.getSubmissionDate().getTime()));
        s.setBoolean(5, !harvestDefinition.isActive());
        long nextEdition = harvestDefinition.getEdition() + 1;
        s.setLong(6, nextEdition);
        s.setString(7, harvestDefinition.getAudience());
        s.setLong(8, harvestDefinition.getOid());
        s.setLong(9, harvestDefinition.getEdition());
        int rows = s.executeUpdate();
        // Since the HD exists, no rows indicates bad edition
        if (rows == 0) {
            String message = "Somebody else must have updated " + harvestDefinition + " since edition "
                    + harvestDefinition.getEdition() + ", not updating";
            log.debug(message);
            throw new PermissionDenied(message);
        }
        s.close();

        // Now pull more strings
        s = c.prepareStatement(
                "UPDATE partialharvests SET " + "schedule_id = " + "    (SELECT schedule_id FROM schedules "
                        + "WHERE schedules.name = ?), " + "nextdate = ? " + "WHERE harvest_id = ?");
        s.setString(1, harvestDefinition.getScheduleName());
        DBUtils.setDateMaybeNull(s, 2, harvestDefinition.getNextDate());
        s.setLong(3, harvestDefinition.getOid());
        rows = s.executeUpdate();
        log.debug(rows + " partialharvests records updated");
        s.close();
        c.commit();
    } catch (SQLException e) {
        throw new IOFailure("SQL error while updating harvest definition " + harvestDefinition + "\n"
                + ExceptionUtils.getSQLExceptionCause(e), e);
    } finally {
        DBUtils.rollbackIfNeeded(c, "updating", harvestDefinition);
        HarvestDBConnection.release(c);
    }
}

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

@Override
public void insertLigplaatsen(final List<Ligplaats> ligplaatsen) throws DAOException {
    try {//from w w  w .ja  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);
    }
}