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:com.hangum.tadpole.rdb.core.editors.main.composite.ResultSetComposite.java

/**
 * prepared statement  .//from  w w  w  . j a v a2 s . co  m
 * 
 * @param preparedStatement
 * @param statementParameter
 * @return
 */
private ResultSet _runSQLSelect(final PreparedStatement preparedStatement, final Object[] statementParameter)
        throws Exception {

    Future<ResultSet> queryFuture = execServiceQuery.submit(new Callable<ResultSet>() {
        @Override
        public ResultSet call() throws SQLException {
            for (int i = 1; i <= statementParameter.length; i++) {
                preparedStatement.setObject(i, statementParameter[i - 1]);
            }
            return preparedStatement.executeQuery();
        }
    });

    /* SELECT ALRM_DATE ? select??  ?? ?  ? ? ?.
     * Caused by: java.lang.NullPointerException
       at oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:973)
     */
    return queryFuture.get();
}

From source file:de.iritgo.aktario.jdbc.Insert.java

/**
 * Perform the command.//w  w  w . ja v a  2s.co  m
 */
public void perform() {
    if (properties.get("table") == null) {
        Log.logError("persist", "Insert", "Missing table name");

        return;
    }

    int size = 0;

    if (properties.get("size") != null) {
        size = ((Integer) properties.get("size")).intValue();
    }

    JDBCManager jdbcManager = (JDBCManager) Engine.instance().getManager("persist.JDBCManager");
    DataSource dataSource = jdbcManager.getDefaultDataSource();

    Connection connection = null;
    PreparedStatement stmt = null;

    ArrayList columns = new ArrayList(8);
    ArrayList columnValues = new ArrayList(8);

    for (Iterator i = properties.entrySet().iterator(); i.hasNext();) {
        Map.Entry entry = (Map.Entry) i.next();

        if (((String) entry.getKey()).indexOf("column.") == 0) {
            columns.add(((String) entry.getKey()).substring(7));
            columnValues.add(entry.getValue());
        }
    }

    int numColumns = columns.size();

    StringBuffer sqlColumns = new StringBuffer("(id");

    for (int i = 0; i < numColumns; ++i) {
        sqlColumns.append(", " + (String) columns.get(i));
    }

    sqlColumns.append(")");

    StringBuffer sqlValues = new StringBuffer("(?");

    for (int i = 0; i < numColumns; ++i) {
        sqlValues.append(", ?");
    }

    sqlValues.append(")");

    String sql = "insert into " + properties.getProperty("table") + " " + sqlColumns.toString() + " values "
            + sqlValues.toString();

    try {
        connection = dataSource.getConnection();

        stmt = connection.prepareStatement(sql);

        if (size <= 0) {
            stmt.setLong(1, Engine.instance().getPersistentIDGenerator().createId());

            for (int col = 0; col < numColumns; ++col) {
                stmt.setObject(col + 2, columnValues.get(col));
            }

            stmt.execute();
        } else {
            for (int row = 0; row < size; ++row) {
                stmt.setLong(1, Engine.instance().getPersistentIDGenerator().createId());

                for (int col = 0; col < numColumns; ++col) {
                    stmt.setObject(col + 2, ((Object[]) columnValues.get(col))[row]);
                }

                stmt.execute();
            }
        }

        stmt.close();

        Log.logVerbose("persist", "Insert", "INSERT |" + sql + "|");
    } catch (SQLException x) {
        Log.logError("persist", "Insert", "Error while executing sql |" + sql + "|: " + x);
    } finally {
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

From source file:com.netspective.axiom.schema.table.BasicTable.java

public Rows getRowsByWhereCond(ConnectionContext cc, String whereCond, Object[] bindValues)
        throws NamingException, SQLException {
    Rows resultRows = createRows();// w  w  w.jav  a 2  s.c o m

    StringBuffer findRecsToDeleteSql = new StringBuffer("select ");
    Columns columns = getColumns();
    for (int i = 0; i < columns.size(); i++) {
        if (i > 0)
            findRecsToDeleteSql.append(", ");
        findRecsToDeleteSql.append(columns.get(i).getName());
    }
    findRecsToDeleteSql.append(" from " + cc.getDatabasePolicy().resolveTableName(this));
    if (whereCond != null) {
        findRecsToDeleteSql.append(" ");
        if (!whereCond.startsWith("where"))
            findRecsToDeleteSql.append("where");
        findRecsToDeleteSql.append(" ");
        findRecsToDeleteSql.append(whereCond);
    }

    PreparedStatement stmt = cc.getConnection().prepareStatement(findRecsToDeleteSql.toString());
    try {
        if (bindValues != null) {
            for (int i = 0; i < bindValues.length; i++)
                stmt.setObject(i + 1, bindValues[i]);
        }
        ResultSet rs = stmt.executeQuery();
        try {
            while (rs.next()) {
                Row resultRow = createRow();
                resultRow.getColumnValues().populateValues(rs, ColumnValues.RESULTSETROWNUM_SINGLEROW);
                resultRows.addRow(resultRow);
            }
        } finally {
            rs.close();
        }
    } finally {
        stmt.close();
    }

    return resultRows;
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyPlantsFromGBIF.java

/**
 * //  ww w  .  jav a 2  s. c  o  m
 */
public void processNullKingdom() {
    PrintWriter pw = null;
    try {
        pw = new PrintWriter("gbif_plants_from_null.log");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }

    System.out.println("----------------------- Searching NULL ----------------------- ");

    String gbifWhereStr = "FROM raw WHERE kingdom IS NULL";

    long startTime = System.currentTimeMillis();

    String cntGBIFSQL = "SELECT COUNT(*) " + gbifWhereStr;// + " LIMIT 0,1000";
    String gbifSQL = gbifSQLBase + gbifWhereStr;

    System.out.println(cntGBIFSQL);

    long totalRecs = BasicSQLUtils.getCount(srcConn, cntGBIFSQL);
    long procRecs = 0;
    int secsThreshold = 0;

    String msg = String.format("Query: %8.2f secs", (double) (System.currentTimeMillis() - startTime) / 1000.0);
    System.out.println(msg);
    pw.println(msg);
    pw.flush();

    startTime = System.currentTimeMillis();

    Statement gStmt = null;
    PreparedStatement pStmt = null;

    try {
        pw = new PrintWriter("gbif_plants_from_null.log");

        pStmt = dstConn.prepareStatement(pSQL);

        System.out.println("Total Records: " + totalRecs);
        pw.println("Total Records: " + totalRecs);

        gStmt = srcConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        gStmt.setFetchSize(Integer.MIN_VALUE);

        ResultSet rs = gStmt.executeQuery(gbifSQL);
        ResultSetMetaData rsmd = rs.getMetaData();

        while (rs.next()) {
            String genus = rs.getString(16);
            if (genus == null)
                continue;

            String species = rs.getString(17);

            if (isPlant(colStmtGN, colStmtGNSP, genus, species)
                    || isPlant(colDstStmtGN, colDstStmtGNSP, genus, species)) {

                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    Object obj = rs.getObject(i);
                    pStmt.setObject(i, obj);
                }

                try {
                    pStmt.executeUpdate();

                } catch (Exception ex) {
                    System.err.println("For Old ID[" + rs.getObject(1) + "]");
                    ex.printStackTrace();
                    pw.print("For Old ID[" + rs.getObject(1) + "] " + ex.getMessage());
                    pw.flush();
                }

                procRecs++;
                if (procRecs % 10000 == 0) {
                    long endTime = System.currentTimeMillis();
                    long elapsedTime = endTime - startTime;

                    double avergeTime = (double) elapsedTime / (double) procRecs;

                    double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs
                            - procRecs) / HRS;

                    int seconds = (int) (elapsedTime / 60000.0);
                    if (secsThreshold != seconds) {
                        secsThreshold = seconds;

                        msg = String.format(
                                "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                                ((double) (elapsedTime)) / HRS, avergeTime,
                                100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                        System.out.println(msg);
                        pw.println(msg);
                        pw.flush();
                    }
                }
            }
        }

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (gStmt != null) {
                gStmt.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            pw.close();

        } catch (Exception ex) {

        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");
}

From source file:org.ensembl.healthcheck.util.ConnectionBasedSqlTemplateImpl.java

private void bindParamsToPreparedStatement(PreparedStatement st, Object[] arguments) throws SQLException {
    int i = 0;/* w  w  w .j  av  a 2  s .  c om*/
    if (arguments != null) {

        for (Object arg : arguments) {
            i++;
            if (arg == null) {
                st.setNull(i, Types.NULL);
            } else if (arg instanceof String) {
                st.setString(i, (String) arg);
            } else if (arg instanceof Integer) {
                st.setInt(i, (Integer) arg);
            } else if (arg instanceof Boolean) {
                st.setBoolean(i, (Boolean) arg);
            } else if (arg instanceof Short) {
                st.setShort(i, (Short) arg);
            } else if (arg instanceof Date) {
                st.setTimestamp(i, new java.sql.Timestamp(((Date) arg).getTime()));
            } else if (arg instanceof java.sql.Date) {
                st.setDate(i, new java.sql.Date(((Date) arg).getTime()));
            } else if (arg instanceof Double) {
                st.setDouble(i, (Double) arg);
            } else if (arg instanceof Long) {
                st.setLong(i, (Long) arg);
            } else if (arg instanceof BigDecimal) {
                st.setObject(i, arg);
            } else if (arg instanceof BigInteger) {
                st.setObject(i, arg);
            } else { // Object
                try {
                    ByteArrayOutputStream bytesS = new ByteArrayOutputStream();
                    ObjectOutputStream out = new ObjectOutputStream(bytesS);
                    out.writeObject(arg);
                    out.close();
                    byte[] bytes = bytesS.toByteArray();
                    bytesS.close();
                    st.setBytes(i, bytes);
                } catch (IOException e) {
                    throw new SQLException(
                            "Could not serialize object " + arg + " for use in a PreparedStatement ");
                }
            }
        }
    }
}

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

public List<DrawableFile<?>> getFilesInGroup(GroupKey<?> key) throws TskCoreException {
    List<DrawableFile<?>> files = new ArrayList<>();
    dbReadLock();//from   www . j  a  va  2 s  .  c  o  m
    try {
        PreparedStatement statement = null;

        /* I hate this! not flexible/generic/maintainable we could have the
         * DrawableAttribute provide/create/configure the correct statement
         * but they shouldn't be coupled like that -jm */
        switch (key.getAttribute().attrName) {
        case CATEGORY:
            return getFilesWithCategory((Category) key.getValue());
        default:
            statement = getGroupStatment(key.getAttribute());
        }

        statement.setObject(1, key.getValue());

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

    return files;
}

From source file:de.innovationgate.webgate.api.jdbc.custom.JDBCSource.java

private void applyQueryParameters(Map parameters, PreparedStatement stmt, String query) {
    if (parameters.containsKey(WGDatabase.QUERYOPTION_QUERY_PARAMETERS)) {
        for (Map.Entry paramEntry : (Set<Map.Entry>) ((Map) parameters
                .get(WGDatabase.QUERYOPTION_QUERY_PARAMETERS)).entrySet()) {
            String paramName = String.valueOf(paramEntry.getKey());
            try {
                // If it contains only digits it is used as index parameter
                if (INDEX_PARAMETER_NAME.matcher(paramName).matches()) {
                    Integer paramIndex = Integer.parseInt(paramName);
                    stmt.setObject(paramIndex, paramEntry.getValue());
                }/*from  w ww.ja v a  2 s. c  o m*/
                /* We can't do that because some default parameters come as named parameters. So we just ignore them.
                else {
                throw new WGQueryException(query, "Only indexed parameters (use ? in query and index number as parameter name) are supported on this database type");
                }*/

                /* Unfortunately not possible since this would need a CallableStatement which has other issues. See #00000156
                // Else it is used as named parameter
                else {
                stmt.setObject(paramName, paramEntry.getValue());
                }*/

            }

            catch (Exception e) {
                WGFactory.getLogger().error("Exception setting SQL query parameter " + paramEntry.getKey(), e);
            }
        }
    }
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyPlantsFromGBIF.java

/**
 * /*from  w ww .j a  v a 2s  .c o m*/
 */
public void processNonNullNonPlantKingdom() {
    PrintWriter pw = null;
    try {
        pw = new PrintWriter("gbif_plants_from_nonnull.log");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }

    System.out.println("----------------------- Search non-Plantae ----------------------- ");

    String gbifWhereStr = "FROM raw WHERE kingdom = '%s'";

    Vector<String> nonPlantKingdoms = new Vector<String>();
    String sqlStr = "SELECT * FROM (select kingdom, count(kingdom) as cnt from plants.raw WHERE kingdom is not null AND NOT (lower(kingdom) like '%plant%') group by kingdom) T1 ORDER BY cnt desc;";
    for (Object[] obj : BasicSQLUtils.query(sqlStr)) {
        String kingdom = (String) obj[0];
        Integer count = (Integer) obj[1];

        System.out.println(kingdom + " " + count);
        pw.println(kingdom + " " + count);
        if (!StringUtils.contains(kingdom.toLowerCase(), "plant")) {
            nonPlantKingdoms.add(kingdom);
        }
    }

    long startTime = System.currentTimeMillis();

    for (String kingdom : nonPlantKingdoms) {
        String where = String.format(gbifWhereStr, kingdom);

        String cntGBIFSQL = "SELECT COUNT(*) " + where;
        String gbifSQL = gbifSQLBase + where;

        System.out.println(cntGBIFSQL);

        long totalRecs = BasicSQLUtils.getCount(srcConn, cntGBIFSQL);
        long procRecs = 0;
        int secsThreshold = 0;

        String msg = String.format("Query: %8.2f secs",
                (double) (System.currentTimeMillis() - startTime) / 1000.0);
        System.out.println(msg);
        pw.println(msg);
        pw.flush();

        startTime = System.currentTimeMillis();

        Statement gStmt = null;
        PreparedStatement pStmt = null;

        try {
            pStmt = dstConn.prepareStatement(pSQL);

            System.out.println("Total Records: " + totalRecs);
            pw.println("Total Records: " + totalRecs);
            pw.flush();

            gStmt = srcConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            gStmt.setFetchSize(Integer.MIN_VALUE);

            ResultSet rs = gStmt.executeQuery(gbifSQL);
            ResultSetMetaData rsmd = rs.getMetaData();

            while (rs.next()) {
                String genus = rs.getString(16);
                if (genus == null)
                    continue;

                String species = rs.getString(17);

                if (isPlant(colStmtGN, colStmtGNSP, genus, species)
                        || isPlant(colDstStmtGN, colDstStmtGNSP, genus, species)) {

                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        Object obj = rs.getObject(i);
                        pStmt.setObject(i, obj);
                    }

                    try {
                        pStmt.executeUpdate();

                    } catch (Exception ex) {
                        System.err.println("For Old ID[" + rs.getObject(1) + "]");
                        ex.printStackTrace();
                        pw.print("For Old ID[" + rs.getObject(1) + "] " + ex.getMessage());
                        pw.flush();
                    }

                    procRecs++;
                    if (procRecs % 10000 == 0) {
                        long endTime = System.currentTimeMillis();
                        long elapsedTime = endTime - startTime;

                        double avergeTime = (double) elapsedTime / (double) procRecs;

                        double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs
                                - procRecs) / HRS;

                        int seconds = (int) (elapsedTime / 60000.0);
                        if (secsThreshold != seconds) {
                            secsThreshold = seconds;

                            msg = String.format(
                                    "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                                    ((double) (elapsedTime)) / HRS, avergeTime,
                                    100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                            System.out.println(msg);
                            pw.println(msg);
                            pw.flush();
                        }
                    }
                }
            }

        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            try {
                if (gStmt != null) {
                    gStmt.close();
                }
                if (pStmt != null) {
                    pStmt.close();
                }
                pw.close();

            } catch (Exception ex) {

            }
        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");

}

From source file:com.nextep.designer.dbgm.services.impl.DataService.java

private void fillStorageValues(IStorageHandle handle, Collection<Object> values) throws SQLException {
    Connection conn = null;/*from  www  .j a v  a2  s. c  om*/
    PreparedStatement stmt = null;
    try {
        conn = storageService.getLocalConnection();
        final String insertStmt = handle.getInsertStatement();
        final int expectedArgCount = insertStmt.length() - insertStmt.replace("?", "").length(); //$NON-NLS-1$ //$NON-NLS-2$
        stmt = conn.prepareStatement(insertStmt);
        int i = 1;
        for (Object o : values) {
            // TODO : Testing nullity to workaround some derby jdbc problem (temporary)
            if (i <= expectedArgCount) {
                if (o == null) {
                    stmt.setNull(i++, Types.VARCHAR);
                } else {
                    stmt.setObject(i++, o);
                }
            } else {
                /*
                 * Normally, this should append when source and target data sets have different
                 * structure (more columns in target or in source), but this might also hide
                 * some other bug (not sure about what will happen when column are swapped
                 * between source and target), so we log in debug mode.
                 */
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug("DataSet DEBUG : parameter " + (i++) //$NON-NLS-1$
                            + " ignored while filling storage handle for query " + insertStmt //$NON-NLS-1$
                            + " with values : " + values); //$NON-NLS-1$
                }
            }
        }
        while (i <= expectedArgCount) {
            stmt.setNull(i++, Types.VARCHAR);
        }
        stmt.execute();
    } finally {
        safeClose(null, stmt, conn, false);
    }
}

From source file:org.apache.jackrabbit.core.persistence.db.DatabasePersistenceManager.java

/**
 * Executes the given SQL statement with the specified parameters.
 * If a <code>SQLException</code> is encountered and
 * <code>autoReconnect==true</code> <i>one</i> attempt is made to re-establish
 * the database connection and re-execute the statement.
 *
 * @param sql    statement to execute/*from   w w  w  . j  a v a2s . c o  m*/
 * @param params parameters to set
 * @return the <code>Statement</code> object that had been executed
 * @throws SQLException if an error occurs
 */
protected Statement executeStmt(String sql, Object[] params) throws SQLException {
    int trials = autoReconnect ? 2 : 1;
    while (true) {
        PreparedStatement stmt = (PreparedStatement) preparedStatements.get(sql);
        try {
            for (int i = 0; i < params.length; i++) {
                if (params[i] instanceof SizedInputStream) {
                    SizedInputStream in = (SizedInputStream) params[i];
                    stmt.setBinaryStream(i + 1, in, (int) in.getSize());
                } else {
                    stmt.setObject(i + 1, params[i]);
                }
            }
            stmt.execute();
            resetStatement(stmt);
            return stmt;
        } catch (SQLException se) {
            if (--trials == 0) {
                // no more trials, re-throw
                throw se;
            }
            log.warn("execute failed, about to reconnect... {}", se.getMessage());

            // try to reconnect
            if (reestablishConnection()) {
                // reconnect succeeded; check whether it's possible to
                // re-execute the prepared stmt with the given parameters
                for (int i = 0; i < params.length; i++) {
                    if (params[i] instanceof SizedInputStream) {
                        SizedInputStream in = (SizedInputStream) params[i];
                        if (in.isConsumed()) {
                            // we're unable to re-execute the prepared stmt
                            // since an InputStream paramater has already
                            // been 'consumed';
                            // re-throw previous SQLException
                            throw se;
                        }
                    }
                }

                // try again to execute the statement
                continue;
            } else {
                // reconnect failed, re-throw previous SQLException
                throw se;
            }
        }
    }
}