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.artifactory.storage.db.util.JdbcHelper.java

private void setParamsToStmt(PreparedStatement pstmt, Object[] params) throws SQLException {
    int i = 1;//from  www . java2  s . co m
    for (Object param : params) {
        if (param instanceof Iterable) {
            for (Object p : (Iterable) param) {
                pstmt.setObject(i++, p);
            }
        } else if (param instanceof BlobWrapper) {
            BlobWrapper blobWrapper = (BlobWrapper) param;
            if (blobWrapper.getLength() < 0) {
                pstmt.setBinaryStream(i++, blobWrapper.getInputStream());
            } else {
                pstmt.setBinaryStream(i++, blobWrapper.getInputStream(), blobWrapper.getLength());
            }
        } else {
            pstmt.setObject(i++, param);
        }
    }
}

From source file:org.ut.biolab.medsavant.server.db.PooledConnection.java

/**
 * Utility method to make it easier to execute SELECT-style queries.
 *
 * @param query a query, possibly containing '?' placeholder elements
 * @param args arguments for the placeholders
 * @return a ResultSet containing the results of the query
 * @throws SQLException/*from   w  w w  . jav a2 s.co  m*/
 */
public ResultSet executePreparedQuery(PreparedStatement stmt, Object... args) throws SQLException {
    for (int i = 0; i < args.length; i++) {
        stmt.setObject(i + 1, args[i]);
    }
    return stmt.executeQuery();
}

From source file:org.ut.biolab.medsavant.server.db.PooledConnection.java

/**
 * Utility method to make it easier to execute data-manipulation calls which don't
 * return a result./*from w  w  w . jav  a 2 s  .c  om*/
 *
 * @param query a query, possibly containing '?' placeholder elements
 * @param args arguments for the placeholders
 * @throws SQLException
 */
public void executePreparedUpdate(PreparedStatement stmt, Object... args) throws SQLException {
    for (int i = 0; i < args.length; i++) {
        stmt.setObject(i + 1, args[i]);
    }
    stmt.executeUpdate();
}

From source file:org.jboss.bqt.framework.AbstractQuery.java

private void setParameters(PreparedStatement stmt, Object[] params) throws SQLException {
    for (int i = 0; i < params.length; i++) {
        stmt.setObject(i + 1, params[i]);
    }/*from  w  w w  .  j  a v  a  2  s .c o  m*/
}

From source file:at.alladin.rmbt.db.dao.QoSTestResultDao.java

/**
 * /*from w ww .ja v a 2 s .co m*/
 * @param result
 * @throws SQLException
 */
public int save(QoSTestResult result) throws SQLException {
    String sql;

    PreparedStatement ps = null;

    if (result.getUid() == null) {
        sql = "INSERT INTO qos_test_result (test_uid, result, qos_test_uid, success_count, failure_count) VALUES (?,?::json,?,?,?)";
        ps = conn.prepareStatement(sql);
        ps.setLong(1, result.getTestUid());
        ps.setObject(2, result.getResults());
        ps.setLong(3, result.getQoSTestObjectiveId());
        ps.setInt(4, result.getSuccessCounter());
        ps.setInt(5, result.getFailureCounter());
    } else {
        sql = "UPDATE qos_test_result SET test_uid = ?, result = ?::json, qos_test_uid = ?, success_count = ?, failure_count = ? WHERE uid = ?";
        ps = conn.prepareStatement(sql);
        ps.setLong(1, result.getTestUid());
        ps.setObject(2, result.getResults());
        ps.setLong(3, result.getQoSTestObjectiveId());
        ps.setInt(4, result.getSuccessCounter());
        ps.setInt(5, result.getFailureCounter());
        ps.setLong(6, result.getUid());
    }

    return ps.executeUpdate();
}

From source file:org.castor.jpa.functional.AbstractSpringBaseTest.java

protected final void verifyPersistentBook(Book book) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;//from   w  w w.j  av  a2  s.c o  m

    int fetchSize = 0;
    long isbn = 0;
    String title = null;

    try {
        // Load the book from the database.
        connection = this.dataSource.getConnection();
        preparedStatement = connection.prepareStatement("SELECT isbn, title FROM book WHERE isbn = ?");
        preparedStatement.setObject(1, Long.valueOf(book.getIsbn()));
        resultSet = preparedStatement.executeQuery();

        fetchSize = resultSet.getFetchSize();
        resultSet.next();

        // Get values from result set.
        isbn = resultSet.getLong(1);
        title = resultSet.getString(2);

    } catch (SQLException e) {
        fail("Could not verify book instance: " + e.getMessage());
        e.printStackTrace();
    } finally {
        // Release resources.
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    // Verify result.
    assertEquals(1, fetchSize);
    assertEquals(book.getIsbn(), isbn);
    assertEquals(book.getTitle(), title);
}

From source file:com.emc.vipr.sync.source.SqlBlobSource.java

@Override
public void sync(SqlSyncObject syncObject, SyncFilter filterChain) {
    filterChain.filter(syncObject);/*w  w w .  j  av  a 2  s.c om*/

    // update DB with new object ID
    if (updateSql != null) {
        Object sqlId = syncObject.getRawSourceIdentifier();
        String objectId = syncObject.getTargetIdentifier();

        // Run the update SQL
        Connection con = null;
        PreparedStatement ps = null;

        try {

            con = dataSource.getConnection();
            ps = con.prepareStatement(updateSql);
            ps.setObject(updateIdColumn, sqlId);
            ps.setString(updateTargetIdColumn, objectId);

            ps.executeUpdate();
        } catch (Exception e) {
            l4j.error(String.format("SQL update failed [%s -> %s]", sqlId, objectId), e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    // Ignore
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    // Ignore
                }
            }
        }
    }
}

From source file:org.apache.phoenix.util.PhoenixRuntimeTest.java

@Test
public void testGetPkColsDataTypes() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl(), new Properties());
    int i = 0;/*from ww  w  .  j a  va 2s .c  o m*/
    PDataType[] pTypes = PDataType.values();
    int size = pTypes.length;
    StringBuilder sb = null;
    try {
        for (i = 0; i < size; i++) {
            PDataType pType = pTypes[i];
            String sqlTypeName = pType.getSqlTypeName();
            if (sqlTypeName.equalsIgnoreCase("VARBINARY ARRAY")) {
                // we don't support VARBINARY ARRAYS yet
                // JIRA - https://issues.apache.org/jira/browse/PHOENIX-1329
                continue;
            }
            if (pType.isArrayType() && PDataType.arrayBaseType(pType).isFixedWidth()
                    && PDataType.arrayBaseType(pType).getByteSize() == null) {
                // Need to treat array type whose base type is of fixed width whose byte size is not known as a special case. 
                // Cannot just use the sql type name returned by PDataType.getSqlTypeName().
                String baseTypeName = PDataType.arrayBaseType(pType).getSqlTypeName();
                sqlTypeName = baseTypeName + "(15)" + " " + PDataType.ARRAY_TYPE_SUFFIX;
            } else if (pType.isFixedWidth() && pType.getByteSize() == null) {
                sqlTypeName = sqlTypeName + "(15)";
            }
            String columnName = "col" + i;
            String tableName = "t" + i;

            sb = new StringBuilder(100);

            // create a table by using the type name as returned by PDataType
            sb.append("CREATE TABLE " + tableName + " (");
            sb.append(columnName + " " + sqlTypeName + " NOT NULL PRIMARY KEY, V1 VARCHAR)");
            conn.createStatement().execute(sb.toString());

            // generate the optimized query plan by going through the pk of the table.
            PreparedStatement stmt = conn
                    .prepareStatement("SELECT * FROM " + tableName + " WHERE " + columnName + " = ?");
            Integer maxLength = pType.isFixedWidth() && pType.getByteSize() == null ? 15 : null;
            stmt.setObject(1, pType.getSampleValue(maxLength));
            QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt);

            // now go through the utility method, get column name and type name and
            // try creating another table with the returned info. Use the query plan generated above.
            // If table can be created with the returned sql type name, then great!
            // It would mean "Roundtrip" of column data type name works.
            List<Pair<String, String>> pkCols = new ArrayList<Pair<String, String>>();
            List<String> dataTypes = new ArrayList<String>();
            PhoenixRuntime.getPkColsDataTypesForSql(pkCols, dataTypes, plan, conn, true);

            tableName = "newt" + i;
            columnName = "newCol" + i;
            String roundTripSqlTypeName = dataTypes.get(0);

            // create a table by using the type name as returned by the utility method
            sb = new StringBuilder(100);
            sb.append("CREATE TABLE " + tableName + " (");
            sb.append(columnName + " " + roundTripSqlTypeName + " NOT NULL PRIMARY KEY)");
            conn.createStatement().execute(sb.toString());
        }
    } catch (Exception e) {
        fail("Failed sql: " + sb.toString() + ExceptionUtils.getStackTrace(e));
    }
}

From source file:com.emc.ecs.sync.source.SqlBlobSource.java

@Override
public void sync(SqlSyncObject syncObject, SyncFilter filterChain) {
    filterChain.filter(syncObject);/*from www . j a v  a2s  . co m*/

    // update DB with new object ID
    if (updateSql != null) {
        Object sqlId = syncObject.getRawSourceIdentifier();
        String objectId = syncObject.getTargetIdentifier();

        // Run the update SQL
        Connection con = null;
        PreparedStatement ps = null;

        try {

            con = dataSource.getConnection();
            ps = con.prepareStatement(updateSql);
            ps.setObject(updateIdColumn, sqlId);
            ps.setString(updateTargetIdColumn, objectId);

            ps.executeUpdate();
        } catch (Exception e) {
            log.error(String.format("SQL update failed [%s -> %s]", sqlId, objectId), e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    // Ignore
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    // Ignore
                }
            }
        }
    }
}

From source file:com.mirth.connect.connectors.jdbc.DatabaseDispatcherQuery.java

@Override
public Response send(DatabaseDispatcherProperties connectorProperties, ConnectorMessage connectorMessage)
        throws DatabaseDispatcherException {
    long dispatcherId = connector.getDispatcherId();
    SimpleDataSource dataSource = dataSources.get(dispatcherId);

    if (dataSource == null) {
        dataSource = new SimpleDataSource();
        dataSources.put(dispatcherId, dataSource);
    }//from www  .  j  av  a2 s  . c  om

    PreparedStatement statement = null;

    try {
        Connection connection = dataSource.getConnection(connectorProperties);
        statement = connection.prepareStatement(connectorProperties.getQuery());
        int i = 1;

        for (Object param : connectorProperties.getParameters()) {
            statement.setObject(i++, param);
        }

        /*
         * We do not use Statement.executeUpdate() here because it could prevent users from
         * executing a stored procedure. Executing a stored procedure in Postgres (and possibly
         * other databases) is done via SELECT myprocedure(), which breaks executeUpdate() since
         * it returns a result, even if the procedure itself returns void.
         */
        statement.execute();
        int numRows = statement.getUpdateCount();
        String responseData = null;
        String responseMessageStatus = null;

        if (numRows == -1) {
            responseMessageStatus = "Database write success";
        } else {
            responseMessageStatus = "Database write success, " + numRows + " rows updated";
        }

        return new Response(Status.SENT, responseData, responseMessageStatus);
    } catch (Exception e) {
        throw new DatabaseDispatcherException("Failed to write to database", e);
    } finally {
        DbUtils.closeQuietly(statement);
    }
}