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.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.java

private void parametrize(final DataRow parameters, final String[] params, final PreparedStatement pstmt,
        final boolean expandArrays, final int parameterOffset) throws SQLException {
    pstmt.clearParameters();
    int paramIndex = parameterOffset;
    for (int i = 0; i < params.length; i++) {
        final String param = params[i];
        final Object pvalue = parameters.get(param);
        if (pvalue == null) {
            // this should work, but some driver are known to die here.
            // they should be fed with setNull(..) instead; something
            // we cant do as JDK1.2's JDBC does not define it.
            pstmt.setObject(paramIndex + 1, null);
            logger.debug("Parametrize: " + (paramIndex + 1) + " set to <null>");
            paramIndex++;//  w  w w  .j a v  a 2  s. c  om
        } else if (expandArrays && pvalue instanceof Object[]) {
            final Object[] values = (Object[]) pvalue;
            if (values.length > 0) {
                for (int j = 0; j < values.length; j++) {
                    final Object ivalue = values[j];
                    if (ivalue instanceof java.sql.Date || ivalue instanceof java.sql.Time
                            || ivalue instanceof Timestamp) {
                        pstmt.setObject(paramIndex + 1, ivalue);
                    } else if (ivalue instanceof Date) {
                        // for now we're going to convert java.util.Date to java.sql.Timestamp
                        // this seems to be a better fit for most jdbc drivers/databases
                        // if problems come from this, we can create workaround them as discovered
                        final Date d = (Date) ivalue;
                        pstmt.setObject(paramIndex + 1, new Timestamp(d.getTime()));
                    } else {
                        pstmt.setObject(paramIndex + 1, ivalue);
                    }
                    logger.debug("Parametrize: Array: " + (paramIndex + 1) + ": " + ivalue);
                    paramIndex++;
                }
            } else {
                pstmt.setObject(paramIndex + 1, null);
                logger.debug("Parametrize: Array: " + (paramIndex + 1) + " set to <null> for empty array");
                paramIndex++;
            }
        } else {
            if (pvalue instanceof java.sql.Date || pvalue instanceof java.sql.Time
                    || pvalue instanceof Timestamp) {
                pstmt.setObject(paramIndex + 1, pvalue);
            } else if (pvalue instanceof Date) {
                // see comment above about java.util.Date/java.sql.Timestamp conversion
                final Date d = (Date) pvalue;
                pstmt.setObject(paramIndex + 1, new Timestamp(d.getTime()));
            } else {
                pstmt.setObject(paramIndex + 1, pvalue);
            }
            logger.debug("Parametrize: " + (paramIndex + 1) + ": " + pvalue);
            paramIndex++;
        }
    }
}

---

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

private void migrateFrequenciesToCodedValue(JdbcConnection connection, Set<String> uniqueFrequencies)
        throws CustomChangeException, SQLException, DatabaseException {
    PreparedStatement updateDrugOrderStatement = null;
    Boolean autoCommit = null;//ww w  .  jav a  2s  .  c o  m
    try {
        autoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        updateDrugOrderStatement = connection
                .prepareStatement("update drug_order set frequency = ? where frequency_text = ?");

        updateDrugOrderStatement.setNull(1, Types.INTEGER);
        updateDrugOrderStatement.setNull(2, Types.VARCHAR);
        updateDrugOrderStatement.executeUpdate();
        updateDrugOrderStatement.clearParameters();

        for (String frequency : uniqueFrequencies) {
            if (StringUtils.isBlank(frequency)) {
                updateDrugOrderStatement.setNull(1, Types.INTEGER);
            } else {
                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) {
                    throw new CustomChangeException(
                            "No order frequency found for concept " + conceptIdForFrequency);
                }

                updateDrugOrderStatement.setInt(1, orderFrequencyId);
            }
            updateDrugOrderStatement.setString(2, frequency);
            updateDrugOrderStatement.executeUpdate();
            updateDrugOrderStatement.clearParameters();
        }
        connection.commit();
    } catch (DatabaseException e) {
        handleError(connection, e);
    } catch (SQLException e) {
        handleError(connection, e);
    } finally {
        if (autoCommit != null) {
            connection.setAutoCommit(autoCommit);
        }
        if (updateDrugOrderStatement != null) {
            updateDrugOrderStatement.close();
        }
    }
}

---

From source file:org.sakaiproject.search.journal.impl.DbJournalManager.java

/**
 * @see org.sakaiproject.search.journal.api.JournalManager#prepareSave(long)
 *///from  www. java2  s .  co m
public JournalManagerState prepareSave(long transactionId) throws IndexJournalException {
    PreparedStatement insertPst = null;
    JournalManagerStateImpl jms = new JournalManagerStateImpl(transactionId);
    try {

        Connection connection = datasource.getConnection();
        jms.connection = connection;

        insertPst = connection.prepareStatement(
                "insert into search_journal (txid, txts, indexwriter, status) values ( ?,?,?,?)");
        insertPst.clearParameters();
        insertPst.setLong(1, transactionId);
        insertPst.setLong(2, System.currentTimeMillis());
        insertPst.setString(3, serverId);
        insertPst.setString(4, "prepare");
        if (insertPst.executeUpdate() != 1) {
            throw new IndexJournalException("Failed to update index journal");
        }

    } catch (IndexJournalException ijex) {
        throw ijex;
    } catch (Exception ex) {
        throw new IndexJournalException("Failed to transfer index ", ex);
    } finally {
        try {
            insertPst.close();
        } catch (Exception ex) {
            log.debug(ex);
        }
    }
    return jms;
}

---

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

private void migrateUnitsToCodedValue(JdbcConnection connection, Set<String> uniqueUnits)
        throws CustomChangeException, SQLException, DatabaseException {
    PreparedStatement updateDrugOrderStatement = null;
    Boolean autoCommit = null;/*from  w  w w.ja va2 s  .co m*/
    try {
        autoCommit = connection.getAutoCommit();
        connection.setAutoCommit(false);

        updateDrugOrderStatement = connection
                .prepareStatement("update drug_order set dose_units = ? where units = ?");

        updateDrugOrderStatement.setNull(1, Types.INTEGER);
        updateDrugOrderStatement.setNull(2, Types.VARCHAR);
        updateDrugOrderStatement.executeUpdate();
        updateDrugOrderStatement.clearParameters();

        for (String unit : uniqueUnits) {
            if (StringUtils.isBlank(unit)) {
                updateDrugOrderStatement.setNull(1, Types.INTEGER);
            } else {
                Integer conceptIdForUnit = UpgradeUtil.getConceptIdForUnits(unit);
                if (conceptIdForUnit == null) {
                    throw new CustomChangeException("No concept mapping found for unit: " + unit);
                }
                String dosingUnitsConceptSetUuid = UpgradeUtil.getGlobalProperty(
                        connection.getUnderlyingConnection(),
                        OpenmrsConstants.GP_DRUG_DOSING_UNITS_CONCEPT_UUID);
                List<Integer> dosingUnitsconceptIds = UpgradeUtil
                        .getMemberSetIds(connection.getUnderlyingConnection(), dosingUnitsConceptSetUuid);
                if (!dosingUnitsconceptIds.contains(conceptIdForUnit)) {
                    throw new CustomChangeException(
                            "Dosing unit '" + unit + "' is not among valid concepts defined in global property "
                                    + OpenmrsConstants.GP_DRUG_DOSING_UNITS_CONCEPT_UUID);
                }

                updateDrugOrderStatement.setInt(1, conceptIdForUnit);
            }
            updateDrugOrderStatement.setString(2, unit);
            updateDrugOrderStatement.executeUpdate();
            updateDrugOrderStatement.clearParameters();
        }

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

---

From source file:org.dspace.storage.rdbms.DatabaseManager.java

/**
 * Iterate over the given parameters and add them to the given prepared statement. 
 * Only a select number of datatypes are supported by the JDBC driver.
 *
 * @param statement/*from w w w .j a v a 2  s. c o m*/
 *          The unparameterized statement.
 * @param parameters
 *          The parameters to be set on the statement.
 */
protected static void loadParameters(PreparedStatement statement, Object[] parameters) throws SQLException {
    statement.clearParameters();

    int idx = 1;
    for (Object parameter : parameters) {
        if (parameter instanceof String) {
            statement.setString(idx, (String) parameter);
        } else if (parameter instanceof Long) {
            statement.setLong(idx, ((Long) parameter).longValue());
        } else if (parameter instanceof Integer) {
            statement.setInt(idx, ((Integer) parameter).intValue());
        } else if (parameter instanceof Short) {
            statement.setShort(idx, ((Short) parameter).shortValue());
        } else if (parameter instanceof Date) {
            statement.setDate(idx, (Date) parameter);
        } else if (parameter instanceof Time) {
            statement.setTime(idx, (Time) parameter);
        } else if (parameter instanceof Timestamp) {
            statement.setTimestamp(idx, (Timestamp) parameter);
        } else if (parameter instanceof Double) {
            statement.setDouble(idx, ((Double) parameter).doubleValue());
        } else if (parameter instanceof Float) {
            statement.setFloat(idx, ((Float) parameter).floatValue());
        } else if (parameter == null) {
            throw new SQLException("Attempting to insert null value into SQL query.");
        } else {
            throw new SQLException("Attempting to insert unknown datatype (" + parameter.getClass().getName()
                    + ") into SQL statement.");
        }

        idx++;
    }
}

---

From source file:org.broad.igv.dev.db.SQLCodecSource.java

private CloseableTribbleIterator query(String chr, int start, int end) throws IOException {
    initQueryStatement();//from ww w .  j av  a2 s . c  om
    PreparedStatement statement = queryStatement;
    Set<Integer> bins = calculateBins(start, end);
    //System.out.println("number of bins: " + bins.size());
    if (bins.size() < MAX_BINS && binnedQueryStatement != null) {
        statement = binnedQueryStatement;
    }

    try {
        statement.clearParameters();
        statement.setString(1, chr);
        statement.setInt(3, end);
        int[] cols = new int[] { 2, 4, 5 };
        for (Integer cc : cols) {
            statement.setInt(cc, start);
        }

        if (statement == binnedQueryStatement) {
            int qnum = 6;
            for (Integer bin : bins) {
                statement.setInt(qnum, bin);
                qnum++;
            }

            for (; qnum <= statement.getParameterMetaData().getParameterCount(); qnum++) {
                statement.setNull(qnum, Types.INTEGER);
            }
        }

    } catch (SQLException e) {
        log.error(e);
        throw new IOException(e);
    }

    return loadIterator(statement);
}

---

From source file:org.sakaiproject.content.impl.db.test.CheckBlobSafety.java

@Test
public void testBlob() {
    try {//from  www. j av  a2 s. co  m
        Random r = new Random();
        int blockSize = 4095; // use an odd size to get byte boundaries
        int nblocks = 512;
        int maxSize = blockSize * nblocks;
        byte[] b = new byte[maxSize];
        byte[] bin = new byte[maxSize];
        log.info("Loading Random Data " + maxSize);
        r.nextBytes(b);
        log.info("Loaded Random Data");

        log.info("Got Connection");
        PreparedStatement pstout = null;
        PreparedStatement pstin = null;
        InputStream instream = null;
        ResultSet rs = null;
        try {
            pstout = con.prepareStatement(p.getProperty("insert.statement"));
            pstin = con.prepareStatement(p.getProperty("select.statement"));
            for (int i = 1; i < nblocks; i += 5) {
                int size = blockSize * i;
                pstout.clearParameters();

                pstout.setBinaryStream(1, new ByteArrayInputStream(b), size);
                pstout.setInt(2, i);
                pstout.executeUpdate();
                log.info("Loaded record  " + i + " of size " + (size) + " bytes");
                con.commit();
                i++;
            }
            for (int i = 1; i < nblocks; i += 5) {
                int size = blockSize * i;
                pstin.clearParameters();
                pstin.setInt(1, i);
                rs = pstin.executeQuery();
                if (rs.next()) {
                    instream = rs.getBinaryStream(1);
                    DataInputStream din = new DataInputStream(instream);
                    din.readFully(bin, 0, size);
                    for (int j = 0; j < size; j++) {
                        Assert.assertEquals("Byte Missmatch record " + i + " offset " + j, b[j], bin[j]);
                    }
                    log.info("Checked Record " + i + " of size " + size + " bytes");
                    din.close();
                    instream.close();
                    rs.close();
                    i++;
                } else {
                    Assert.assertEquals("Didnt get any record at " + i, true, false);
                }
                con.commit();
            }
        } finally {
            try {
                pstin.close();
            } catch (SQLException e) {

            }
            try {
                pstout.close();
            } catch (SQLException e) {

            }
            try {
                instream.close();
            } catch (Exception ex) {
            }
            try {
                rs.close();
            } catch (Exception ex) {
            }

        }
    } catch (Exception ex) {
        log.error("Failed ", ex);
    }

}

---

From source file:org.dspace.storage.rdbms.MockDatabaseManager.java

/**
 * Iterate over the given parameters and add them to the given prepared statement.
 * Only a select number of datatypes are supported by the JDBC driver.
 *
 * @param statement/*from w  ww.  j  a v a2 s.c  o  m*/
 *          The unparameterized statement.
 * @param parameters
 *          The parameters to be set on the statement.
 */
@Mock
protected static void loadParameters(PreparedStatement statement, Object[] parameters) throws SQLException {

    statement.clearParameters();

    for (int i = 0; i < parameters.length; i++) {
        // Select the object we are setting.
        Object parameter = parameters[i];
        int idx = i + 1; // JDBC starts counting at 1.

        if (parameter == null) {
            throw new SQLException("Attempting to insert null value into SQL query.");
        }
        if (parameter instanceof String) {
            statement.setString(idx, (String) parameters[i]);
        } else if (parameter instanceof Integer) {
            int ii = ((Integer) parameter).intValue();
            statement.setInt(idx, ii);
        } else if (parameter instanceof Double) {
            double d = ((Double) parameter).doubleValue();
            statement.setDouble(idx, d);
        } else if (parameter instanceof Float) {
            float f = ((Float) parameter).floatValue();
            statement.setFloat(idx, f);
        } else if (parameter instanceof Short) {
            short s = ((Short) parameter).shortValue();
            statement.setShort(idx, s);
        } else if (parameter instanceof Long) {
            long l = ((Long) parameter).longValue();
            statement.setLong(idx, l);
        } else if (parameter instanceof Date) {
            Date date = (Date) parameter;
            statement.setDate(idx, date);
        } else if (parameter instanceof Time) {
            Time time = (Time) parameter;
            statement.setTime(idx, time);
        } else if (parameter instanceof Timestamp) {
            Timestamp timestamp = (Timestamp) parameter;
            statement.setTimestamp(idx, timestamp);
        } else {
            throw new SQLException("Attempting to insert unknown datatype (" + parameter.getClass().getName()
                    + ") into SQL statement.");
        }
    }
}

---

From source file:com.spvp.dal.MySqlDatabase.java

@Override
public Boolean ucitajGradoveUBazu(ArrayList<Grad> gradovi) throws SQLException {

    Connection conn = null;/*from w  w  w. j  a v  a2s.c  o  m*/
    Boolean status = false;

    try {
        conn = getConnection();

        PreparedStatement pstmt = conn.prepareStatement(
                "INSERT INTO gradovi (ime, longitude, latitude, veci_centar)" + "VALUES(?,?,?,?)");
        for (Grad x : gradovi) {

            pstmt.clearParameters();
            pstmt.setString(1, x.getImeGrada());
            pstmt.setDouble(2, x.getLongitude());
            pstmt.setDouble(3, x.getLatitude());
            pstmt.setBoolean(4, x.getVeciCentar());

            pstmt.addBatch();
        }

        pstmt.executeBatch();
        status = true;

    } catch (SQLException ex) {
        Logger.getLogger(MySqlDatabase.class.getName()).log(Level.SEVERE, null, ex);

    } finally {

        if (conn != null)
            conn.close();
    }

    return status;
}

---

From source file:com.agiletec.plugins.jpcrowdsourcing.aps.system.services.ideainstance.IdeaInstanceDAO.java

private void addIdeaInstanceGroups(String code, List<String> groups, Connection conn) {
    if (null == groups || groups.isEmpty())
        return;/*from w w w  . j ava  2 s. c  o  m*/
    PreparedStatement stat = null;
    try {
        stat = conn.prepareStatement(ADD_IDEAINSTANCE_GROUP);
        Iterator<String> groupIter = groups.iterator();

        while (groupIter.hasNext()) {
            String groupName = groupIter.next();
            int i = 1;
            stat.setString(i++, code);
            stat.setString(i++, groupName);
            stat.addBatch();
            stat.clearParameters();
        }
        stat.executeBatch();
    } catch (Throwable t) {
        _logger.error("Error creating ideainstance-group relation for {}", code, t);
        throw new RuntimeException("Error creating ideainstance-group relation for " + code, t);
    } finally {
        this.closeDaoResources(null, stat, null);
    }
}

---

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