Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

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

Introduction

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

Prototype

void setObject(int parameterIndex, Object x) throws SQLException;

Source Link

Document

Sets the value of the designated parameter using the given object.

Usage

From source file:mil.army.usace.data.nativequery.rdbms.NativeRdbmsQuery.java

public void setParams(PreparedStatement st, Object[] params) throws SQLException {
    if (params != null) {
        for (int i = 0; i < params.length; i++) {
            st.setObject(i + 1, (Object) params[i]);
        }/*  www  . j  av  a  2 s . co m*/
    }
}

From source file:org.sleuthkit.autopsy.imageanalyzer.datamodel.DrawableDB.java

public List<Long> getFileIDsInGroup(GroupKey<?> groupKey) throws TskCoreException {

    if (groupKey.getAttribute().isDBColumn) {
        switch (groupKey.getAttribute().attrName) {
        case CATEGORY:
            return manager.getFileIDsWithCategory((Category) groupKey.getValue());
        case TAGS:
            return manager.getFileIDsWithTag((TagName) groupKey.getValue());
        }/*from   w w  w . j  ava 2 s . c  o  m*/
    }
    List<Long> files = new ArrayList<>();
    dbReadLock();
    try {
        PreparedStatement statement = getGroupStatment(groupKey.getAttribute());
        statement.setObject(1, groupKey.getValue());

        try (ResultSet valsResults = statement.executeQuery()) {
            while (valsResults.next()) {
                files.add(valsResults.getLong(OBJ_ID));
            }
        }
    } catch (SQLException ex) {
        LOGGER.log(Level.WARNING,
                "failed to get file for group:" + groupKey.getAttribute() + " == " + groupKey.getValue(), ex);
    } finally {
        dbReadUnlock();
    }

    return files;
}

From source file:com.netspective.axiom.policy.AnsiDatabasePolicy.java

protected int executeAndIgnoreStatistics(ConnectionContext cc, String sql, Object[] bindValues,
        Object[] addlParams) throws NamingException, SQLException {
    if (log.isTraceEnabled())
        log.trace(getDmlDebugText(sql, bindValues));

    try {/*from w w w. j  a  v  a2s  . co m*/
        Connection conn = cc.getConnection();
        PreparedStatement stmt = conn.prepareStatement(sql);

        int bindNum = 0;
        if (bindValues != null) {
            for (int i = 0; i < bindValues.length; i++) {
                Object bindValue = bindValues[i];
                if (bindValue != null) {
                    bindNum++;
                    stmt.setObject(bindNum, bindValue);
                }
            }
        }
        if (addlParams != null) {
            for (int i = 0; i < addlParams.length; i++) {
                Object bindValue = addlParams[i];
                if (bindValue != null) {
                    bindNum++;
                    stmt.setObject(bindNum, bindValue);
                }
            }
        }

        int result = stmt.executeUpdate();
        stmt.close();
        return result;
    } catch (SQLException e) {
        log.error(getDmlDebugText(sql, bindValues), e);
        throw e;
    }
}

From source file:org.accada.epcis.repository.query.QueryOperationsBackendSQL.java

/**
 * @param session/*from   w w  w  .  j  av  a2 s .com*/
 * @param vocElemUri
 * @param attribute
 * @throws SQLException
 */
private void fetchAttributes(final QueryOperationsSession session, final String vocType, final String vocUri,
        final List<String> filterAttrNames, final List<AttributeType> attributes) throws SQLException {
    String vocTablename = getVocabularyTablename(vocType);
    StringBuilder sql = new StringBuilder();
    List<Object> sqlParams = new ArrayList<Object>();
    sql.append("SELECT attribute, value FROM ").append(vocTablename).append(" AS voc, ");
    sql.append(vocTablename).append("_attr AS attr WHERE voc.id=attr.id AND voc.uri=?");
    sqlParams.add(vocUri);
    if ("voc_Any".equals(vocTablename)) {
        sql.append(" AND voc.vtype=?");
        sqlParams.add(vocType);
    }
    if (filterAttrNames != null && !filterAttrNames.isEmpty()) {
        // filter by attribute names
        sql.append(" AND attribute IN (?");
        sqlParams.add(filterAttrNames.get(0));
        for (int i = 1; i < filterAttrNames.size(); i++) {
            sql.append(",?");
            sqlParams.add(filterAttrNames.get(i));
        }
        sql.append(")");
    }
    PreparedStatement ps = session.getPreparedStatement(sql.toString());
    LOG.debug("SQL: " + sql.toString());
    for (int i = 0; i < sqlParams.size(); i++) {
        ps.setObject(i + 1, sqlParams.get(i));
        if (LOG.isDebugEnabled()) {
            LOG.debug("     param" + i + " = " + sqlParams.get(i));
        }
    }

    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        AttributeType attr = new AttributeType();
        attr.setId(rs.getString(1));
        attr.getContent().add(rs.getString(2));
        attributes.add(attr);
    }
    rs.close();
}

From source file:org.fosstrak.epcis.repository.query.QueryOperationsBackendSQL.java

/**
 * @param session/*  w  ww .  j  av a2s .c  o m*/
 * @param vocElemUri
 * @param attribute
 * @throws SQLException
 */
private void fetchAttributes(final QueryOperationsSession session, final String vocType, final String vocUri,
        final List<String> filterAttrNames, final List<AttributeType> attributes) throws SQLException {
    String vocTablename = getVocabularyTablename(vocType);
    StringBuilder sql = new StringBuilder();
    List<Object> sqlParams = new ArrayList<Object>();
    sql.append("SELECT attribute, value FROM ").append(vocTablename).append(" AS voc, ");
    sql.append(vocTablename).append("_attr AS attr WHERE voc.id=attr.id AND voc.uri=?");
    sqlParams.add(vocUri);
    if ("voc_Any".equals(vocTablename)) {
        sql.append(" AND voc.vtype=?");
        sqlParams.add(vocType);
    }
    if (filterAttrNames != null && !filterAttrNames.isEmpty()) {
        // filter by attribute names
        sql.append(" AND attribute IN (?");
        sqlParams.add(filterAttrNames.get(0));
        for (int i = 1; i < filterAttrNames.size(); i++) {
            sql.append(",?");
            sqlParams.add(filterAttrNames.get(i));
        }
        sql.append(")");
    }
    PreparedStatement ps = session.getPreparedStatement(sql.toString());
    LOG.debug("SQL: " + sql.toString());
    for (int i = 0; i < sqlParams.size(); i++) {
        ps.setObject(i + 1, sqlParams.get(i));
        if (LOG.isDebugEnabled()) {
            LOG.debug("     param" + i + " = " + sqlParams.get(i));
        }
    }

    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        AttributeType attr = new AttributeType();
        attr.setId(rs.getString(1));

        //replaced by nkef of "attr.getContent().add(rs.getString(2));" with
        attr.getOtherAttributes().put(new QName("value"), rs.getString(2));

        attributes.add(attr);
    }
    rs.close();
}

From source file:org.openmrs.module.htmlformentry.uuid.GenerateUuid.java

/**
 * Does the work of adding UUIDs to all rows.
 *
 * @see liquibase.change.custom.CustomTaskChange#execute(liquibase.database.Database)
 *//* w  ww . j  a  va  2s  . c o  m*/
@Override
public void execute(Database database) throws CustomChangeException {

    // if we're in a "generate sql file" mode, quit early
    if (Context.getRuntimeProperties().size() == 0) {
        return;
    }

    if (tableNamesArray == null || tableNamesArray.length == 0) {
        throw new CustomChangeException("At least one table name in the 'tableNames' parameter is required",
                null);
    }

    JdbcConnection connection = (JdbcConnection) database.getConnection();

    // loop over all tables
    for (String tableName : tableNamesArray) {
        try {
            Statement idStatement = null;
            PreparedStatement updateStatement = null;
            try {
                String idSql = genericIdSql.replaceAll("tablename", tableName);
                String updateSql = genericUpdateSql.replaceAll("tablename", tableName);

                // hacky way to deal with tables that don't follow the tableNam_id convention
                for (Map.Entry<String, String> idException : idExceptionsMap.entrySet()) {
                    idSql = idSql.replaceFirst(idException.getKey(), idException.getValue());
                    updateSql = updateSql.replaceFirst(idException.getKey(), idException.getValue());
                }
                idStatement = connection.createStatement();
                updateStatement = connection.prepareStatement(updateSql);

                // Map<Integer, UUID> uuids = new HashMap<Integer, UUID>();

                ResultSet ids = idStatement.executeQuery(idSql);
                while (ids.next()) {
                    updateStatement.setObject(2, ids.getObject(1)); // set the primary key number
                    updateStatement.setString(1, UUID.randomUUID().toString()); // set the uuid for this row
                    updateStatement.executeUpdate();
                }
            } finally {
                if (idStatement != null) {
                    idStatement.close();
                }
                if (updateStatement != null) {
                    updateStatement.close();
                }
            }

        } catch (DatabaseException e) {
            throw new CustomChangeException("Unable to set uuid on table: " + tableName, e);
        } catch (SQLException e) {
            throw new CustomChangeException("Unable to set uuid on table: " + tableName, e);
        }
    }
}

From source file:org.jspresso.hrsample.backend.JspressoUnitOfWorkTest.java

/**
 * Tests merge modes.//from w w  w  .  j  av a  2 s  .c om
 */
@Test
public void testMergeModes() {
    final HibernateBackendController hbc = (HibernateBackendController) getBackendController();

    EnhancedDetachedCriteria crit = EnhancedDetachedCriteria.forClass(City.class);
    final City c1 = hbc.findFirstByCriteria(crit, EMergeMode.MERGE_CLEAN_EAGER, City.class);
    String name = c1.getName();

    JdbcTemplate jdbcTemplate = getApplicationContext().getBean("jdbcTemplate", JdbcTemplate.class);
    jdbcTemplate.execute(new ConnectionCallback<Object>() {

        @Override
        public Object doInConnection(Connection con) throws SQLException {
            PreparedStatement ps = con.prepareStatement("UPDATE CITY SET NAME = ? WHERE ID = ?");
            ps.setString(1, "test");
            ps.setObject(2, c1.getId());
            assertEquals(1, ps.executeUpdate());
            return null;
        }
    });
    final City c2 = hbc.findById(c1.getId(), EMergeMode.MERGE_CLEAN_LAZY, City.class);

    assertSame(c1, c2);
    assertEquals(name, c2.getName());

    final City c3 = hbc.findById(c1.getId(), EMergeMode.MERGE_CLEAN_EAGER, City.class);
    assertSame(c1, c3);
    assertEquals("test", c3.getNameRaw());

    jdbcTemplate.execute(new ConnectionCallback<Object>() {

        @Override
        public Object doInConnection(Connection con) throws SQLException {
            PreparedStatement ps = con
                    .prepareStatement("UPDATE CITY SET NAME = ?, VERSION = VERSION+1 WHERE ID = ?");
            ps.setString(1, "test2");
            ps.setObject(2, c1.getId());
            assertEquals(1, ps.executeUpdate());
            return null;
        }
    });

    final City c4 = hbc.findById(c1.getId(), EMergeMode.MERGE_KEEP, City.class);
    assertSame(c1, c4);
    assertEquals("test", c4.getNameRaw());

    final City c5 = hbc.findById(c1.getId(), EMergeMode.MERGE_CLEAN_LAZY, City.class);
    assertSame(c1, c5);
    assertEquals("test2", c5.getNameRaw());
}

From source file:com.netspective.axiom.policy.AnsiDatabasePolicy.java

protected int executeAndRecordStatistics(ConnectionContext cc, QueryExecutionLog qel, String identifer,
        String sql, Object[] bindValues, Object[] addlParams) throws NamingException, SQLException {
    if (log.isTraceEnabled())
        log.trace(getDmlDebugText(sql, bindValues));

    QueryExecutionLogEntry logEntry = qel.createNewEntry(cc, identifer);
    try {/*from  w w w . j a va2s . c o  m*/
        logEntry.registerGetConnectionBegin();
        Connection conn = cc.getConnection();
        logEntry.registerGetConnectionEnd(conn);
        PreparedStatement stmt = conn.prepareStatement(sql);

        logEntry.registerBindParamsBegin();
        int bindNum = 0;
        if (bindValues != null) {
            for (int i = 0; i < bindValues.length; i++) {
                Object bindValue = bindValues[i];
                if (bindValue != null) {
                    bindNum++;
                    stmt.setObject(bindNum, bindValue);
                }
            }
        }
        if (addlParams != null) {
            for (int i = 0; i < addlParams.length; i++) {
                Object bindValue = addlParams[i];
                if (bindValue != null) {
                    bindNum++;
                    stmt.setObject(bindNum, bindValue);
                }
            }
        }
        logEntry.registerBindParamsEnd();

        logEntry.registerExecSqlBegin();
        int result = stmt.executeUpdate();
        stmt.close();
        logEntry.registerExecSqlEndSuccess();
        return result;
    } catch (SQLException e) {
        logEntry.registerExecSqlEndFailed();
        log.error(getDmlDebugText(sql, bindValues), e);
        throw e;
    } finally {
        logEntry.finalize(cc, log);
    }
}

From source file:com.cloudera.sqoop.manager.SqlManager.java

/**
 * Executes an arbitrary SQL statement./*from   www  . j  a va 2  s.c om*/
 * @param stmt The SQL statement to execute
 * @param fetchSize Overrides default or parameterized fetch size
 * @return A ResultSet encapsulating the results or null on error
 */
protected ResultSet execute(String stmt, Integer fetchSize, Object... args) throws SQLException {
    // Release any previously-open statement.
    release();

    PreparedStatement statement = null;
    statement = this.getConnection().prepareStatement(stmt, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    if (fetchSize != null) {
        LOG.debug("Using fetchSize for next query: " + fetchSize);
        statement.setFetchSize(fetchSize);
    }
    this.lastStatement = statement;
    if (null != args) {
        for (int i = 0; i < args.length; i++) {
            statement.setObject(i + 1, args[i]);
        }
    }

    LOG.info("Executing SQL statement: " + stmt);
    return statement.executeQuery();
}

From source file:com.taobao.datax.plugins.writer.oraclejdbcwriter.OracleJdbcWriter.java

/**
 * ??flushdb//from w ww.j a  v a 2s. c  o m
 * 
 * @throws SQLException
 */
private void flushDuplicatedBuffer() throws SQLException {
    if (this.onDuplicatedSql == null || this.onDuplicatedSql.isEmpty()) {
        throw new DataExchangeException("On duplicated sql is empty,duplicated lines processing failed.");
    }
    Iterator<Line> lines = this.duplicatedLineBuffer.iterator();
    PreparedStatement ps = null;
    try {
        ps = this.connection.prepareStatement(this.onDuplicatedSql);
    } catch (SQLException e) {
        e.printStackTrace();
        logger.error("Prepare on duplicated sql error.");
        throw new DataExchangeException(e);
    }
    String[] idxs = StringUtils.split(this.duplidatedKeyIndices, ',');
    int[] iidxs = new int[idxs.length];
    for (int i = 0; i < idxs.length; i++) {
        iidxs[i] = Integer.parseInt(idxs[i]);
    }
    int deleteCount = 0;
    int deleteSuccessCount = 0;
    while (lines.hasNext()) {
        Line line = lines.next();
        try {
            for (int i = 0; i < idxs.length; i++) {
                ps.setObject(i + 1, line.getField(iidxs[i]));
                int num = ps.executeUpdate();
                deleteSuccessCount += num;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            // delete failed remove this line
            lines.remove();
            failCount++;
            if (failCount >= this.limit) {
                throw new DataExchangeException("Too many failed lines(" + failCount + ") .");
            } else {
                continue;
            }
        }
        if (deleteCount++ >= this.commitCount) {
            this.connection.commit();
            deleteCount = 0;
            logger.info("Delete " + deleteCount + " duplicated lines .");
        }
    }
    logger.info(
            deleteSuccessCount + "/" + this.duplicatedLineBuffer.size() + " duplicated line(s) are deleted .");
    this.connection.commit();
    ps.close();
    ps = this.connection.prepareStatement(this.insert);
    lines = this.duplicatedLineBuffer.iterator();
    int linesCount = 0;
    int insertSuccessCount = 0;
    while (lines.hasNext()) {
        Line line = lines.next();
        try {
            for (int i = 0; i < line.getFieldNum(); i++) {
                ps.setObject(i + 1, line.getField(i));
            }
            ps.execute();
            insertSuccessCount++;
        } catch (SQLException e) {
            e.printStackTrace();
            failCount++;
            if (failCount >= this.limit) {
                throw new DataExchangeException("Too many failed lines(" + failCount + ") .");
            } else {
                continue;
            }
        }
        if (linesCount++ == this.commitCount) {
            logger.info(lines + " committed by worker " + Thread.currentThread().getName()
                    + " after duplicated lines deleted.");
            linesCount = 0;
            this.connection.commit();

        }
    }
    ps.close();
    this.connection.commit();
    logger.info(insertSuccessCount + "/" + this.duplicatedLineBuffer.size()
            + " duplicated line(s) are inserted again .");
    this.duplicatedLineBuffer.clear();
}