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:org.apache.ode.bpel.extvar.jdbc.JdbcExternalVariableModule.java

RowVal execSelect(DbExternalVariable dbev, Locator locator)
        throws SQLException, ExternalVariableModuleException {
    RowKey rowkey = dbev.keyFromLocator(locator);
    if (__log.isDebugEnabled())
        __log.debug("execSelect: " + rowkey);

    if (rowkey.missingDatabaseGeneratedValues()) {
        return null;
    }/*from  ww  w.j  a v a 2  s.c  om*/

    if (rowkey.missingValues()) {
        throw new IncompleteKeyException(rowkey.getMissing());
    }

    RowVal ret = dbev.new RowVal();
    Connection conn = dbev.dataSource.getConnection();
    PreparedStatement stmt = null;
    try {
        if (__log.isDebugEnabled())
            __log.debug("Prepare statement: " + dbev.select);
        stmt = conn.prepareStatement(dbev.select);
        int idx = 1;
        for (Object k : rowkey) {
            if (__log.isDebugEnabled())
                __log.debug("Set key parameter " + idx + ": " + k);
            stmt.setObject(idx++, k);
        }

        ResultSet rs = stmt.executeQuery();
        try {
            if (rs.next()) {
                for (Column c : dbev._columns) {
                    Object val;
                    int i = c.idx + 1;
                    if (c.isDate())
                        val = rs.getDate(i);
                    else if (c.isTimeStamp())
                        val = rs.getTimestamp(i);
                    else if (c.isTime())
                        val = rs.getTime(i);
                    else if (c.isInteger())
                        val = new Long(rs.getLong(i));
                    else if (c.isReal())
                        val = new Double(rs.getDouble(i));
                    else if (c.isBoolean())
                        val = new Boolean(rs.getBoolean(i));
                    else
                        val = rs.getObject(i);
                    if (__log.isDebugEnabled())
                        __log.debug("Result column index " + c.idx + ": " + val);
                    ret.set(c.idx, val);
                }
            } else
                return null;
        } finally {
            rs.close();
        }
    } finally {
        if (stmt != null)
            stmt.close();
        try {
            conn.close();
        } catch (SQLException e) {
            // ignore
        }
    }

    return ret;
}

From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java

public Frame fetch(StatementHandle h, List<TypedValue> parameterValues, long offset, int fetchMaxRowCount) {
    if (LOG.isTraceEnabled()) {
        LOG.trace("fetching " + h + " offset:" + offset + " fetchMaxRowCount:" + fetchMaxRowCount);
    }/*  w  ww . ja  v a  2  s  .c  o  m*/
    try {
        final StatementInfo statementInfo = Objects.requireNonNull(statementCache.getIfPresent(h.id),
                "Statement not found, potentially expired. " + h);
        if (statementInfo.resultSet == null || parameterValues != null) {
            if (statementInfo.statement instanceof PreparedStatement) {
                final PreparedStatement preparedStatement = (PreparedStatement) statementInfo.statement;
                if (parameterValues != null) {
                    for (int i = 0; i < parameterValues.size(); i++) {
                        TypedValue o = parameterValues.get(i);
                        preparedStatement.setObject(i + 1, o.toJdbc(calendar));
                    }
                }
                if (preparedStatement.execute()) {
                    statementInfo.resultSet = preparedStatement.getResultSet();
                }
            }
        }
        if (statementInfo.resultSet == null) {
            return Frame.EMPTY;
        } else {
            return JdbcResultSet.frame(statementInfo.resultSet, offset, fetchMaxRowCount, calendar);
        }
    } catch (SQLException e) {
        throw propagate(e);
    }
}

From source file:org.deegree.sqldialect.oracle.OracleGeometryConverter.java

@Override
public void setParticle(PreparedStatement stmt, Geometry particle, int paramIndex) throws SQLException {
    try {/* w w  w.j  ava  2  s  . c o m*/
        if (particle == null) {
            stmt.setNull(paramIndex, Types.STRUCT, "MDSYS.SDO_GEOMETRY");
        } else {
            Geometry compatible = getCompatibleGeometry(particle);
            // TODO clarify if this was only a wkt/wkb requirement ?!
            // (background Envelope -> Optimized Rectangles in Oracle are preferred and faster for SDO_RELATE
            // filters )
            //
            // if ( compatible instanceof Envelope ) {
            // compatible = compatible.getConvexHull();
            // }
            OracleConnection ocon = getOracleConnection(stmt.getConnection());
            Object struct = new SDOGeometryConverter().fromGeometry(ocon, isrid, compatible, true);
            stmt.setObject(paramIndex, struct);
        }
    } catch (Throwable t) {
        t.printStackTrace();
        throw new IllegalArgumentException();
    }
}

From source file:com.netspective.axiom.sql.Query.java

protected PreparedStatement createStatement(ConnectionContext cc, Object[] overrideParams, boolean scrollable)
        throws NamingException, SQLException {
    Connection conn = cc.getConnection();
    PreparedStatement stmt = null;
    DbmsSqlText sqlText = getSqlText(cc);
    String sql = sqlText.getSql(cc);
    if (scrollable)
        stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    else// w ww  .  java2  s .c  om
        stmt = conn.prepareStatement(sql);

    if (overrideParams != null) {
        for (int i = 0; i < overrideParams.length; i++)
            stmt.setObject(i + 1, overrideParams[i]);
    } else {
        final QueryParameters parameters = sqlText.getParams();
        if (parameters != null)
            parameters.apply(cc, stmt);
    }
    return stmt;
}

From source file:com.oltpbenchmark.benchmarks.seats.SEATSProfile.java

/**
 * Save the profile information into the database 
 *//*from   w w  w.  j  a  v a  2s. c o m*/
protected final void saveProfile(Connection conn) throws SQLException {
    PreparedStatement stmt = null;

    // CONFIG_PROFILE
    Table catalog_tbl = this.catalog.getTable(SEATSConstants.TABLENAME_CONFIG_PROFILE);
    assert (catalog_tbl != null);
    stmt = conn.prepareStatement(SQLUtil.getInsertSQL(catalog_tbl));
    int param_idx = 1;
    stmt.setObject(param_idx++, this.scale_factor); // CFP_SCALE_FACTOR
    stmt.setObject(param_idx++, this.airport_max_customer_id.toJSONString()); // CFP_AIPORT_MAX_CUSTOMER
    stmt.setObject(param_idx++, this.flight_start_date); // CFP_FLIGHT_START
    stmt.setObject(param_idx++, this.flight_upcoming_date); // CFP_FLIGHT_UPCOMING
    stmt.setObject(param_idx++, this.flight_past_days); // CFP_FLIGHT_PAST_DAYS
    stmt.setObject(param_idx++, this.flight_future_days); // CFP_FLIGHT_FUTURE_DAYS
    stmt.setObject(param_idx++, this.flight_upcoming_offset); // CFP_FLIGHT_OFFSET
    stmt.setObject(param_idx++, this.reservation_upcoming_offset); // CFP_RESERVATION_OFFSET
    stmt.setObject(param_idx++, this.num_reservations); // CFP_NUM_RESERVATIONS
    stmt.setObject(param_idx++, JSONUtil.toJSONString(this.code_id_xref)); // CFP_CODE_ID_XREF
    int result = stmt.executeUpdate();
    stmt.close();
    assert (result == 1);
    if (LOG.isDebugEnabled())
        LOG.debug("Saved profile information into " + catalog_tbl.getName());

    // CONFIG_HISTOGRAMS
    catalog_tbl = this.catalog.getTable(SEATSConstants.TABLENAME_CONFIG_HISTOGRAMS);
    stmt = conn.prepareStatement(SQLUtil.getInsertSQL(catalog_tbl));
    for (Entry<String, Histogram<String>> e : this.airport_histograms.entrySet()) {
        param_idx = 1;
        stmt.setObject(param_idx++, e.getKey()); // CFH_NAME
        stmt.setObject(param_idx++, e.getValue().toJSONString()); // CFH_DATA
        stmt.setObject(param_idx++, 1); // CFH_IS_AIRPORT
        result = stmt.executeUpdate();
        assert (result == 1);
    } // FOR
    if (LOG.isDebugEnabled())
        LOG.debug("Saved airport histogram information into " + catalog_tbl.getName());

    for (Entry<String, Histogram<String>> e : this.histograms.entrySet()) {
        param_idx = 1;
        stmt.setObject(param_idx++, e.getKey()); // CFH_NAME
        stmt.setObject(param_idx++, e.getValue().toJSONString()); // CFH_DATA
        stmt.setObject(param_idx++, 0); // CFH_IS_AIRPORT
        result = stmt.executeUpdate();
        assert (result == 1);
    } // FOR
    stmt.close();
    if (LOG.isDebugEnabled())
        LOG.debug("Saved benchmark histogram information into " + catalog_tbl.getName());

    return;
}

From source file:org.batoo.jpa.core.impl.jdbc.dbutils.AbstractQueryRunner.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with the given objects.
 * /*from w w w .j  av a 2s  . com*/
 * @param stmt
 *            PreparedStatement to fill
 * @param params
 *            Query replacement parameters; <code>null</code> is a valid value to pass in.
 * @throws SQLException
 *             if a database access error occurs
 */
public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {

    // check the parameter count, if we can
    ParameterMetaData pmd = null;
    if (!this.pmdKnownBroken) {
        pmd = stmt.getParameterMetaData();
        final int stmtCount = pmd.getParameterCount();
        final int paramsCount = params == null ? 0 : params.length;

        if (stmtCount != paramsCount) {
            throw new SQLException(
                    "Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount);
        }
    }

    // nothing to do here
    if (params == null) {
        return;
    }

    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type. Oddly, NULL and
            // OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!this.pmdKnownBroken) {
                try {
                    sqlType = pmd.getParameterType(i + 1);
                } catch (final SQLException e) {
                    this.pmdKnownBroken = true;
                }
            }
            stmt.setNull(i + 1, sqlType);
        }
    }
}

From source file:com.netspective.axiom.sql.Query.java

protected PreparedStatement createStatement(ConnectionContext cc, Object[] overrideParams, boolean scrollable,
        QueryExecutionLogEntry logEntry) throws NamingException, SQLException {
    logEntry.registerGetConnectionBegin();
    Connection conn = cc.getConnection();
    logEntry.registerGetConnectionEnd(conn);
    PreparedStatement stmt = null;
    DbmsSqlText sqlText = getSqlText(cc);
    String sql = sqlText.getSql(cc);
    if (scrollable)
        stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    else//w w  w  . j ava  2 s. c  o m
        stmt = conn.prepareStatement(sql);

    logEntry.registerBindParamsBegin();
    if (overrideParams != null) {
        for (int i = 0; i < overrideParams.length; i++)
            stmt.setObject(i + 1, overrideParams[i]);
    } else {
        final QueryParameters parameters = sqlText.getParams();
        if (parameters != null)
            parameters.apply(cc, stmt);
    }
    logEntry.registerBindParamsEnd();
    return stmt;
}

From source file:org.dbunit.database.PrimaryKeyFilter.java

private void addReverseEdge(ForeignKeyRelationshipEdge edge, Set idsToScan) throws SQLException {
    logger.debug("addReverseEdge(edge={}, idsToScan=) - start", edge, idsToScan);

    String fkTable = (String) edge.getFrom();
    String fkColumn = edge.getFKColumn();
    String pkColumn = getPKColumn(fkTable);
    // NOTE: make sure the query below is compatible standard SQL
    String sql = "SELECT " + pkColumn + " FROM " + fkTable + " WHERE " + fkColumn + " = ? ";

    PreparedStatement pstmt = null;
    ResultSet rs = null;// w  w  w  . j a  v a  2s.  co m
    try {
        if (this.logger.isDebugEnabled()) {
            this.logger.debug("Preparing SQL query '" + sql + "'");
        }
        pstmt = this.connection.getConnection().prepareStatement(sql);
        for (Iterator iterator = idsToScan.iterator(); iterator.hasNext();) {
            Object pk = iterator.next();
            if (this.logger.isDebugEnabled()) {
                this.logger.debug("executing query '" + sql + "' for ? = " + pk);
            }
            pstmt.setObject(1, pk);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Object fk = rs.getObject(1);
                addPKToScan(fkTable, fk);
            }
        }
    } finally {
        SQLHelper.close(rs, pstmt);
    }
}

From source file:org.apache.oozie.util.db.SqlStatement.java

/**
 * Prepare the SQL Statement that is generated and assign the values to prepared statement. setValues should be
 * called to set the Real Values for place holders
 *
 * @param conn Connection//from w ww.j  ava2 s  .  c o m
 * @return Prepared SQL Statement
 * @throws SQLException
 */
public PreparedStatement prepareAndSetValues(Connection conn) throws SQLException {
    String stmt = toString();
    if (forUpdate && !Schema.isHsqlConnection(conn)) {
        stmt += " FOR UPDATE";
    }
    PreparedStatement pStmt = conn.prepareStatement(stmt);
    int i = 1;
    for (Object value : this.values) {
        pStmt.setObject(i, value);
        i++;
    }
    log.trace(XLog.Info.get().createPrefix() + " Preparing : " + stmt);
    log.trace(XLog.Info.get().createPrefix() + " Values : " + values);
    return pStmt;
}

From source file:org.apache.sqoop.common.test.db.DatabaseProvider.java

/**
 * Execute given insert query in a new statement object and return
 * generated IDs./*from   w ww.j  av  a 2s. c o m*/
 *
 * @param query Query to execute
 * @return Generated ID.
 */
public Long executeInsertQuery(String query, Object... args) {
    LOG.info("Executing query: " + query);
    ResultSet rs = null;

    try {
        PreparedStatement stmt = databaseConnection.prepareStatement(query,
                PreparedStatement.RETURN_GENERATED_KEYS);
        for (int i = 0; i < args.length; ++i) {
            if (args[i] instanceof String) {
                stmt.setString(i + 1, (String) args[i]);
            } else if (args[i] instanceof Long) {
                stmt.setLong(i + 1, (Long) args[i]);
            } else if (args[i] instanceof Boolean) {
                stmt.setBoolean(i + 1, (Boolean) args[i]);
            } else {
                stmt.setObject(i + 1, args[i]);
            }
        }

        stmt.execute();
        rs = stmt.getGeneratedKeys();
        if (rs.next()) {
            return rs.getLong(1);
        }
    } catch (SQLException e) {
        LOG.error("Error in executing query", e);
        throw new RuntimeException("Error in executing query", e);
    } finally {
        closeResultSetWithStatement(rs);
    }

    return -1L;
}