Example usage for java.sql PreparedStatement clearParameters

Introduction

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

Prototype

void clearParameters() throws SQLException;

Source Link

Document

Clears the current parameter values immediately.

Usage

From source file:org.sakaiproject.search.index.impl.JDBCClusterIndexStore.java

private void removeDBSegment(Connection connection, SegmentInfo rmsi) throws SQLException {
    PreparedStatement segmentDelete = null;
    try {//from   ww w .  ja va 2s  .  com
        if (rmsi.isInDb()) {
            segmentDelete = connection
                    .prepareStatement("delete from search_segments where name_ = ? and version_ = ?");
            segmentDelete.clearParameters();
            segmentDelete.setString(1, rmsi.getName());
            segmentDelete.setLong(2, rmsi.getVersion());
            segmentDelete.execute();

            String sharedSegment = getSharedFileName(rmsi.getName(), sharedStructuredStorage);
            if (sharedSegment != null) {
                File f = new File(sharedSegment);
                if (f.exists()) {
                    if (!f.delete()) {
                        log.warn("unable to delete " + f.getPath());
                    }
                }
            }

            if (searchService.hasDiagnostics()) {
                log.info("\tRemoved Segment From Database [" + rmsi + "]");
            }
        }
    } finally {
        try {
            if (segmentDelete != null) {
                segmentDelete.close();
            }
        } catch (Exception ex) {
            log.debug(ex);
        }
    }
}

---

From source file:org.sakaiproject.search.index.impl.JDBCClusterIndexStore.java

private void updateLocalPatchBLOB(Connection connection) throws SQLException, IOException {
    if (log.isDebugEnabled())
        log.debug("Updating local patch ");
    PreparedStatement segmentSelect = null;
    ResultSet resultSet = null;//  w w  w.j  a v a  2  s  . co m
    try {
        segmentSelect = connection
                .prepareStatement("select version_, packet_ from search_segments where name_ = ?");
        segmentSelect.clearParameters();
        segmentSelect.setString(1, INDEX_PATCHNAME);
        resultSet = segmentSelect.executeQuery();
        if (resultSet.next()) {
            InputStream packetStream = null;
            try {
                long version = resultSet.getLong(1);
                packetStream = resultSet.getBinaryStream(2);
                clusterStorage.unpackPatch(packetStream);
                if (log.isDebugEnabled())
                    log.debug("Updated Patch from DB " + version);
            } finally {
                try {
                    packetStream.close();
                } catch (Exception ex) {
                    log.debug(ex);
                }
            }
        } else {
            if (log.isDebugEnabled())
                log.debug("Didnt find patch in database, this is Ok ");
        }
    } finally {
        try {
            resultSet.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
        try {
            segmentSelect.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
    }

}

---

From source file:org.sakaiproject.search.index.impl.JDBCClusterIndexStore.java

/**
 * updte a segment from the database//from   w  ww  .  ja  va2s .c om
 * 
 * @param connection
 * @param addsi
 */
protected void updateLocalSegmentBLOB(Connection connection, SegmentInfo addsi)
        throws SQLException, IOException {
    if (log.isDebugEnabled())
        log.debug("Updating local segment from databse " + addsi);
    PreparedStatement segmentSelect = null;
    ResultSet resultSet = null;
    try {
        segmentSelect = connection
                .prepareStatement("select version_, packet_ from search_segments where name_ = ?");
        segmentSelect.clearParameters();
        segmentSelect.setString(1, addsi.getName());
        resultSet = segmentSelect.executeQuery();
        if (resultSet.next()) {
            InputStream packetStream = null;
            try {
                long version = resultSet.getLong(1);
                packetStream = resultSet.getBinaryStream(2);
                clusterStorage.unpackSegment(addsi, packetStream, version);
                if (log.isDebugEnabled())
                    log.debug("Updated Packet from DB to versiob " + version);
            } finally {
                try {
                    packetStream.close();
                } catch (Exception ex) {
                    log.debug(ex);
                }
            }
        } else {
            log.error("Didnt find segment in database");
        }
    } finally {
        try {
            resultSet.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
        try {
            segmentSelect.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
    }

}

---

From source file:org.openmrs.util.databasechange.CreateCodedOrderFrequencyForDrugOrderFrequencyChangeset.java

private void insertUniqueFrequencies(JdbcConnection connection, Set<String> uniqueFrequencies)
        throws CustomChangeException, SQLException, DatabaseException {
    PreparedStatement insertOrderFrequencyStatement = null;
    Boolean autoCommit = null;/*from   www.ja va  2  s.c  om*/
    try {
        autoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        insertOrderFrequencyStatement = connection.prepareStatement("insert into order_frequency "
                + "(concept_id, creator, date_created, retired, uuid) values (?, ?, ?, ?, ?)");

        Date date = new Date(new java.util.Date().getTime());

        for (String frequency : uniqueFrequencies) {
            if (StringUtils.isBlank(frequency)) {
                continue;
            }

            Integer conceptIdForFrequency = UpgradeUtil.getConceptIdForUnits(frequency);
            if (conceptIdForFrequency == null) {
                throw new CustomChangeException("No concept mapping found for frequency: " + frequency);
            }

            Integer orderFrequencyId = UpgradeUtil.getOrderFrequencyIdForConceptId(
                    connection.getUnderlyingConnection(), conceptIdForFrequency);
            if (orderFrequencyId != null) {
                //a single concept is mapped to more than one text or there is an order frequency already
                continue;
            }

            //Generating UUID for order frequency. Generated UUIDs will be the same if concepts UUIDs are the same.
            String uuid = UpgradeUtil.getConceptUuid(connection.getUnderlyingConnection(),
                    conceptIdForFrequency);
            uuid += "-6925ebb0-7c69-11e3-baa7-0800200c9a66"; //Adding random value for order frequency
            uuid = UUID.nameUUIDFromBytes(uuid.getBytes()).toString();

            insertOrderFrequencyStatement.setInt(1, conceptIdForFrequency);
            insertOrderFrequencyStatement.setInt(2, 1);
            insertOrderFrequencyStatement.setDate(3, date);
            insertOrderFrequencyStatement.setBoolean(4, false);
            insertOrderFrequencyStatement.setString(5, uuid);

            insertOrderFrequencyStatement.executeUpdate();
            insertOrderFrequencyStatement.clearParameters();
        }
        connection.commit();
    } catch (DatabaseException e) {
        handleError(connection, e);
    } catch (SQLException e) {
        handleError(connection, e);
    } finally {
        if (autoCommit != null) {
            connection.setAutoCommit(autoCommit);
        }
        if (insertOrderFrequencyStatement != null) {
            insertOrderFrequencyStatement.close();
        }
    }
}

---

From source file:com.commander4j.db.JDBDespatch.java

public boolean update() {
    boolean result = false;

    logger.debug("update [" + getDespatchNo() + "]");

    if (isValid(false) == true) {
        try {//  w  ww . ja va2s. co  m
            PreparedStatement stmtupdate;
            stmtupdate = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement(
                    Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBDespatch.update"));

            stmtupdate.setTimestamp(1, getDespatchDate());
            stmtupdate.setString(2, getLocationIDFrom());
            stmtupdate.setString(3, getStatus());
            stmtupdate.setString(4, getTrailer());
            stmtupdate.setString(5, getHaulier());
            stmtupdate.setInt(6, getTotalPallets());
            stmtupdate.setString(7, getLocationIDTo());
            stmtupdate.setLong(8, getTransactionRef());
            stmtupdate.setString(9, getLoadNo());
            stmtupdate.setString(10, getJourneyRef());
            stmtupdate.setString(11, getDespatchNo());

            stmtupdate.execute();

            stmtupdate.clearParameters();
            Common.hostList.getHost(getHostID()).getConnection(getSessionID()).commit();
            stmtupdate.close();

            if (getJourneyRef().equals(getJourneyRefOLD()) == false) {
                if (journey.getJourneyRefProperties(getJourneyRefOLD())) {
                    if (getJourneyRefOLD().equals("") == false) {
                        journey.setDespatchNo("");
                        journey.setStatus("Unassigned");
                        journey.update();
                    }
                }

                if (journey.getJourneyRefProperties(getJourneyRef())) {
                    if (getJourneyRef().equals("") == false) {
                        journey.setDespatchNo(getDespatchNo());
                        journey.setStatus("Assigned");
                        journey.update();
                    }
                }
                setJourneyRefOLD(getJourneyRef());
            }

            result = true;
        } catch (SQLException e) {
            setErrorMessage(e.getMessage());
        }
    }

    return result;
}

---

From source file:org.sakaiproject.search.index.impl.JDBCClusterIndexStore.java

/**
 * get a list of all DB segments ordered by version
 * /*from  ww  w  .  j  av  a  2 s . c  o  m*/
 * @param connection
 * @return
 */
private List<SegmentInfo> getDBSegments(Connection connection) throws SQLException {
    PreparedStatement segmentAllSelect = null;
    ResultSet resultSet = null;
    List<SegmentInfo> dbsegments = new ArrayList<SegmentInfo>();
    try {
        segmentAllSelect = connection
                .prepareStatement("select version_, name_ from search_segments where name_ <> ? ");
        segmentAllSelect.clearParameters();
        segmentAllSelect.setString(1, INDEX_PATCHNAME);
        resultSet = segmentAllSelect.executeQuery();
        while (resultSet.next()) {
            final long version = resultSet.getLong(1);
            final String name = resultSet.getString(2);
            SegmentInfo si = SegmentInfoImpl.newSharedSegmentInfo(name, version, localStructuredStorage,
                    searchIndexDirectory);
            dbsegments.add(si);
            if (log.isDebugEnabled())
                log.debug("DB Segment " + si);
        }
    } finally {
        try {
            resultSet.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
        try {
            segmentAllSelect.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
    }
    return dbsegments;
}

---

From source file:com.commander4j.db.JDBUserReport.java

public boolean update() {
    boolean result = false;

    logger.debug("update [" + getReportID() + "]");

    try {//from   ww  w. j a va 2  s . c o  m
        PreparedStatement stmtupdate;
        stmtupdate = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).prepareStatement(
                Common.hostList.getHost(getHostID()).getSqlstatements().getSQL("JDBUserReport.update"));

        stmtupdate.setInt(1, getSequence());
        stmtupdate.setString(2, getDescription());
        stmtupdate.setString(3, getSQL());
        stmtupdate.setString(4, getModuleID());
        stmtupdate.setString(5, getDestination());
        stmtupdate.setString(6, getEnabled());
        stmtupdate.setString(7, getPrivate());
        stmtupdate.setString(8, getUserID());
        stmtupdate.setString(9, getGroupID());
        stmtupdate.setString(10, getParamDateRangeRequired());
        stmtupdate.setString(11, getSavePath());
        stmtupdate.setString(12, getPreview());
        stmtupdate.setString(13, getPromptSaveAs());
        stmtupdate.setString(14, getEmailEnabled());
        stmtupdate.setString(15, getEmailPromptEnabled());
        stmtupdate.setString(16, getEmailAddresses());
        stmtupdate.setString(17, getReportID());

        stmtupdate.execute();
        stmtupdate.clearParameters();

        Common.hostList.getHost(getHostID()).getConnection(getSessionID()).commit();
        stmtupdate.close();
        result = true;

    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }

    return result;
}

---

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

/**
 * Update the list of seedlists for the given domain, keeping IDs where
 * applicable./*from   w  w w.j  a  va2 s .co m*/
 * @param c A connection to the database
 * @param d A domain to update.
 * @throws SQLException
 *             If any database problems occur during the update process.
 */
private void updateSeedlists(Connection c, Domain d) throws SQLException {
    Map<String, Long> oldNames = DBUtils.selectStringLongMap(c,
            "SELECT name, seedlist_id FROM seedlists " + "WHERE domain_id = ?", d.getID());
    PreparedStatement s = c.prepareStatement(
            "UPDATE seedlists SET " + "comments = ?, " + "seeds = ? " + "WHERE name = ? AND domain_id = ?");
    for (Iterator<SeedList> sls = d.getAllSeedLists(); sls.hasNext();) {
        SeedList sl = sls.next();
        if (oldNames.containsKey(sl.getName())) {
            DBUtils.setComments(s, 1, sl, Constants.MAX_COMMENT_SIZE);
            DBUtils.setClobMaxLength(s, 2, sl.getSeedsAsString(), Constants.MAX_SEED_LIST_SIZE, sl, "seedlist");
            s.setString(3, sl.getName());
            s.setLong(4, d.getID());
            s.executeUpdate();
            s.clearParameters();
            sl.setID(oldNames.get(sl.getName()));
            oldNames.remove(sl.getName());
        } else {
            insertSeedlist(c, d, sl);
        }
    }
    s.close();
    s = c.prepareStatement("DELETE FROM seedlists " + "WHERE seedlist_id = ?");
    for (Long gone : oldNames.values()) {
        // Check that we're not deleting something that's in use
        // Since deletion is very rare, this is allowed to take
        // some time.
        String usages = DBUtils.getUsages(c,
                "SELECT configurations.name" + "  FROM configurations, config_seedlists"
                        + " WHERE configurations.config_id = " + "config_seedlists.config_id"
                        + "   AND config_seedlists.seedlist_id = ?",
                gone, gone);
        if (usages != null) {
            String name = DBUtils.selectStringValue(c, "SELECT name FROM seedlists WHERE seedlist_id = ?",
                    gone);
            String message = "Cannot delete seedlist " + name + " as it is used in " + usages;
            log.debug(message);
            throw new PermissionDenied(message);
        }
        s.setLong(1, gone);
        s.executeUpdate();
        s.clearParameters();
    }
}

---

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

/**
 * Create the xref table for passwords used by configurations.
 * @param c /*from  w w  w  . jav  a 2 s  .co  m*/
 *            A connection to the database
 * @param d
 *            A domain to operate on.
 * @param dc
 *            A configuration to create xref table for.
 * @throws SQLException
 *             If any database problems occur during the insertion of
 *             password entries for the given domain configuration
 */
private void createConfigPasswordsEntries(Connection c, Domain d, DomainConfiguration dc) throws SQLException {
    PreparedStatement s = c.prepareStatement("INSERT INTO config_passwords " + "( config_id, password_id ) "
            + "SELECT config_id, password_id " + "  FROM configurations, passwords"
            + " WHERE configurations.domain_id = ?" + "   AND configurations.name = ?"
            + "   AND passwords.name = ?" + "   AND passwords.domain_id = configurations.domain_id");
    for (Iterator<Password> passwords = dc.getPasswords(); passwords.hasNext();) {
        Password p = passwords.next();
        s.setLong(1, d.getID());
        s.setString(2, dc.getName());
        s.setString(3, p.getName());
        s.executeUpdate();
        s.clearParameters();
    }
}

---

From source file:nl.nn.adapterframework.jdbc.JdbcTransactionalStorage.java

protected String storeMessageInDatabase(Connection conn, String messageId, String correlationId,
        Timestamp receivedDateTime, String comments, String label, Serializable message)
        throws IOException, SQLException, JdbcException, SenderException {
    PreparedStatement stmt = null;
    try {//  w  w w  .  j  a v a 2  s .  c om
        IDbmsSupport dbmsSupport = getDbmsSupport();
        if (log.isDebugEnabled())
            log.debug("preparing insert statement [" + insertQuery + "]");
        if (!dbmsSupport.mustInsertEmptyBlobBeforeData()) {
            stmt = conn.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
        } else {
            stmt = conn.prepareStatement(insertQuery);
        }
        stmt.clearParameters();
        int parPos = 0;

        if (StringUtils.isNotEmpty(getTypeField())) {
            stmt.setString(++parPos, type);
        }
        if (StringUtils.isNotEmpty(getSlotId())) {
            stmt.setString(++parPos, getSlotId());
        }
        if (StringUtils.isNotEmpty(getHostField())) {
            stmt.setString(++parPos, host);
        }
        if (StringUtils.isNotEmpty(getLabelField())) {
            stmt.setString(++parPos, label);
        }
        stmt.setString(++parPos, messageId);
        stmt.setString(++parPos, correlationId);
        stmt.setTimestamp(++parPos, receivedDateTime);
        stmt.setString(++parPos, comments);
        if (type.equalsIgnoreCase(TYPE_MESSAGELOG_PIPE) || type.equalsIgnoreCase(TYPE_MESSAGELOG_RECEIVER)) {
            if (getRetention() < 0) {
                stmt.setTimestamp(++parPos, null);
            } else {
                Date date = new Date();
                Calendar cal = Calendar.getInstance();
                cal.setTime(date);
                cal.add(Calendar.DAY_OF_MONTH, getRetention());
                stmt.setTimestamp(++parPos, new Timestamp(cal.getTime().getTime()));
            }
        } else {
            stmt.setTimestamp(++parPos, null);
        }

        if (!isStoreFullMessage()) {
            if (isOnlyStoreWhenMessageIdUnique()) {
                stmt.setString(++parPos, messageId);
                stmt.setString(++parPos, slotId);
            }
            stmt.execute();
            return null;
        }
        if (!dbmsSupport.mustInsertEmptyBlobBeforeData()) {
            ByteArrayOutputStream out = new ByteArrayOutputStream();

            if (isBlobsCompressed()) {
                DeflaterOutputStream dos = new DeflaterOutputStream(out);
                ObjectOutputStream oos = new ObjectOutputStream(dos);
                oos.writeObject(message);
                dos.close();
            } else {
                ObjectOutputStream oos = new ObjectOutputStream(out);
                oos.writeObject(message);
            }

            stmt.setBytes(++parPos, out.toByteArray());
            if (isOnlyStoreWhenMessageIdUnique()) {
                stmt.setString(++parPos, messageId);
                stmt.setString(++parPos, slotId);
            }
            stmt.execute();
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                return rs.getString(1);
            } else {
                return null;
            }
        }
        if (isOnlyStoreWhenMessageIdUnique()) {
            stmt.setString(++parPos, messageId);
            stmt.setString(++parPos, slotId);
        }
        stmt.execute();
        int updateCount = stmt.getUpdateCount();
        if (log.isDebugEnabled())
            log.debug("update count for insert statement: " + updateCount);
        if (updateCount > 0) {
            if (log.isDebugEnabled())
                log.debug("preparing select statement [" + selectKeyQuery + "]");
            stmt = conn.prepareStatement(selectKeyQuery);
            ResultSet rs = null;
            try {
                // retrieve the key
                rs = stmt.executeQuery();
                if (!rs.next()) {
                    throw new SenderException("could not retrieve key of stored message");
                }
                String newKey = rs.getString(1);
                rs.close();

                // and update the blob
                if (log.isDebugEnabled())
                    log.debug("preparing update statement [" + updateBlobQuery + "]");
                stmt = conn.prepareStatement(updateBlobQuery);
                stmt.clearParameters();
                stmt.setString(1, newKey);

                rs = stmt.executeQuery();
                if (!rs.next()) {
                    throw new SenderException("could not retrieve row for stored message [" + messageId + "]");
                }
                //                  String newKey = rs.getString(1);
                //                  BLOB blob = (BLOB)rs.getBlob(2);
                Object blobHandle = dbmsSupport.getBlobUpdateHandle(rs, 1);
                OutputStream out = dbmsSupport.getBlobOutputStream(rs, 1, blobHandle);
                //               OutputStream out = JdbcUtil.getBlobUpdateOutputStream(rs,1);
                if (isBlobsCompressed()) {
                    DeflaterOutputStream dos = new DeflaterOutputStream(out);
                    ObjectOutputStream oos = new ObjectOutputStream(dos);
                    oos.writeObject(message);
                    oos.close();
                    dos.close();
                } else {
                    ObjectOutputStream oos = new ObjectOutputStream(out);
                    oos.writeObject(message);
                    oos.close();
                }
                out.close();
                dbmsSupport.updateBlob(rs, 1, blobHandle);
                return newKey;

            } finally {
                if (rs != null) {
                    rs.close();
                }
            }
        } else {
            if (isOnlyStoreWhenMessageIdUnique()) {
                return "already there";
            } else {
                throw new SenderException(
                        "update count for update statement not greater than 0 [" + updateCount + "]");
            }
        }

    } finally {
        if (stmt != null) {
            stmt.close();
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• »