Example usage for java.sql PreparedStatement setObject

Introduction

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

Prototype

default void setObject(int parameterIndex, Object x, SQLType targetSqlType) throws SQLException 

Source Link

Document

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

Usage

From source file:edu.ncsa.sstde.indexing.postgis.PostgisIndexer.java

private PreparedStatement createSqlQuery(SqlQueryBuilder builder) throws SQLException {
    String sql = builder.getSQL();
    boolean success = false;
    if (builder.limit > 0) {
        sql += " limit ?";
    }/*from www. j ava2  s  .c o  m*/
    PreparedStatement ps = getConnection().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    try {

        int bindingIdx = 1;

        for (Binding binding : builder.inputBindings)
            ps.setObject(bindingIdx++, binding.value, binding.type);
        success = true;

        // if (builder.limit > 0) {

        // ps.setFetchSize((int)builder.limit + 1);
        // }
        return ps;
    } finally {
        if (!success)
            IndexedStatement.closeQuietly(ps);
    }
}

---

From source file:org.apache.phoenix.mapreduce.index.IndexScrutinyMapper.java

private Map<String, Pair<Long, List<Object>>> buildTargetStatement(PreparedStatement targetStatement)
        throws SQLException {
    Map<String, Pair<Long, List<Object>>> targetPkToSourceValues = new HashMap<>(currentBatchValues.size());
    int rsIndex = 1;
    for (Pair<Long, List<Object>> batchTsRow : currentBatchValues) {
        List<Object> batchRow = batchTsRow.getSecond();
        // our original query against the source table (which provided the batchRow) projected
        // with the data table PK cols first, so the first numTargetPkCols form the PK
        String targetPkHash = getPkHash(batchRow.subList(0, numTargetPkCols));
        targetPkToSourceValues.put(targetPkHash, batchTsRow);
        for (int i = 0; i < numTargetPkCols; i++) {
            ColumnInfo targetPkInfo = targetTblColumnMetadata.get(i);
            Object value = batchRow.get(i);
            if (value == null) {
                targetStatement.setNull(rsIndex++, targetPkInfo.getSqlType());
            } else {
                targetStatement.setObject(rsIndex++, value, targetPkInfo.getSqlType());
            }//w ww.  j  ava 2 s  .c o  m
        }
    }
    return targetPkToSourceValues;
}

---

From source file:org.plasma.sdo.jdbc.service.JDBCSupport.java

protected void execute(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values, Connection con) {
    PreparedStatement statement = null;
    try {// w  w  w  .  java  2 s .  co  m
        statement = con.prepareStatement(sql.toString());

        StringBuilder paramBuf = null;
        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            paramBuf = new StringBuilder();
            paramBuf.append("[");
        }

        int i = 1;
        for (PropertyPair pair : values.values()) {
            int jdbcType = converter.toJDBCDataType(pair.getProp(), pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(pair.getProp(), pair.getValue());
            statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            if (log.isDebugEnabled()) {
                if (i > 1) {
                    paramBuf.append(", ");
                }
                paramBuf.append("(");
                paramBuf.append(jdbcValue.getClass().getSimpleName());
                paramBuf.append("/");
                paramBuf.append(converter.getJdbcTypeName(jdbcType));
                paramBuf.append(")");
                paramBuf.append(String.valueOf(jdbcValue));
            }
            i++;
        }
        if (log.isDebugEnabled()) {
            paramBuf.append("]");
            log.debug("params: " + paramBuf.toString());
        }
        statement.executeUpdate();
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
}

---

From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java

@Override
public void executeInsert(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values) {
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    try {/*from   w ww . j a v a  2  s .  c o  m*/

        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }

        statement = con.prepareStatement(sql.toString());

        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();
            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }
        }

        statement.execute();
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }

    }
}

---

From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java

@Override
public void execute(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values) {
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    try {/*from   w  ww  . j a  v a 2s  . c  om*/
        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }
        statement = con.prepareStatement(sql.toString());
        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();

            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }

            if (pair.getOldValue() != null) {
                Object jdbcOldValue = converter.toJDBCDataValue(valueProp, pair.getOldValue());
                if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                    statement.setObject(pair.getOldValueColumn(), jdbcOldValue, jdbcType);
                } else {
                    byte[] bytes = (byte[]) jdbcOldValue;
                    long len = bytes.length;
                    ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                    statement.setBinaryStream(pair.getOldValueColumn(), is, len);
                    if (streams == null)
                        streams = new ArrayList<InputStream>();
                    streams.add(is);
                }
            }
        }
        statement.executeUpdate();
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
    }
}

---

From source file:org.plasma.sdo.jdbc.service.JDBCSupport.java

protected List<PropertyPair> executeInsert(PlasmaType type, StringBuilder sql, Map<String, PropertyPair> values,
        Connection con) {//  w w  w.  j  a va2s .  c  o m
    List<PropertyPair> resultKeys = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    ResultSet generatedKeys = null;
    try {
        statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);

        StringBuilder paramBuf = null;
        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            paramBuf = new StringBuilder();
            paramBuf.append("[");
        }

        int i = 1;
        for (PropertyPair pair : values.values()) {
            int jdbcType = converter.toJDBCDataType(pair.getProp(), pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(pair.getProp(), pair.getValue());
            statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            if (log.isDebugEnabled()) {
                if (i > 1) {
                    paramBuf.append(", ");
                }
                paramBuf.append("(");
                paramBuf.append(jdbcValue.getClass().getSimpleName());
                paramBuf.append("/");
                paramBuf.append(converter.getJdbcTypeName(jdbcType));
                paramBuf.append(")");
                paramBuf.append(String.valueOf(jdbcValue));
            }
            i++;
        }
        if (log.isDebugEnabled()) {
            paramBuf.append("]");
            log.debug("params: " + paramBuf.toString());
        }
        statement.execute();
        generatedKeys = statement.getGeneratedKeys();
        //if (generatedKeys.next()) {
        //   resultKeys.add(generatedKeys.getObject(1));
        //}
        ResultSetMetaData rsMeta = generatedKeys.getMetaData();
        int numcols = rsMeta.getColumnCount();
        if (log.isDebugEnabled())
            log.debug("returned " + numcols + " keys");

        if (generatedKeys.next()) {
            // FIXME; without metadata describing which properties
            // are actually a sequence, there us guess work
            // involved in matching the values returned
            // automatically from PreparedStatment as they
            // are anonymous in terms of the column names
            // making it impossible to match them to a metadata
            // property. 
            List<Property> pkPropList = type.findProperties(KeyType.primary);
            if (pkPropList == null || pkPropList.size() == 0)
                throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'");
            if (pkPropList.size() > 1)
                throw new DataAccessException("multiple pri-key properties found for type '" + type.getName()
                        + "' - cannot map to generated keys");
            PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0);

            for (i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                if (log.isDebugEnabled())
                    log.debug("returned key column '" + columnName + "'");
                int columnType = rsMeta.getColumnType(i);
                Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop);
                PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                resultKeys.add(pair);
            }
        }
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }

    return resultKeys;
}

---

From source file:org.cloudgraph.rdb.filter.RDBStatementExecutor.java

@Override
public List<PropertyPair> executeInsertWithGeneratedKeys(PlasmaType type, StringBuilder sql,
        Map<String, PropertyPair> values) {
    List<PropertyPair> resultKeys = new ArrayList<PropertyPair>();
    PreparedStatement statement = null;
    List<InputStream> streams = null;
    ResultSet generatedKeys = null;
    try {/*from  w ww.jav a2  s  .c om*/

        if (log.isDebugEnabled()) {
            log.debug("execute: " + sql.toString());
            StringBuilder paramBuf = createParamDebug(values);
            log.debug("params: " + paramBuf.toString());
        }

        statement = con.prepareStatement(sql.toString(), PreparedStatement.RETURN_GENERATED_KEYS);

        for (PropertyPair pair : values.values()) {
            PlasmaProperty valueProp = pair.getProp();
            if (pair.getValueProp() != null)
                valueProp = pair.getValueProp();
            int jdbcType = converter.toJDBCDataType(valueProp, pair.getValue());
            Object jdbcValue = converter.toJDBCDataValue(valueProp, pair.getValue());
            if (jdbcType != Types.BLOB && jdbcType != Types.VARBINARY) {
                statement.setObject(pair.getColumn(), jdbcValue, jdbcType);
            } else {
                byte[] bytes = (byte[]) jdbcValue;
                long len = bytes.length;
                ByteArrayInputStream is = new ByteArrayInputStream(bytes);
                statement.setBinaryStream(pair.getColumn(), is, len);
                if (streams == null)
                    streams = new ArrayList<InputStream>();
                streams.add(is);
            }
        }

        statement.execute();
        generatedKeys = statement.getGeneratedKeys();
        ResultSetMetaData rsMeta = generatedKeys.getMetaData();
        int numcols = rsMeta.getColumnCount();
        if (log.isDebugEnabled())
            log.debug("returned " + numcols + " keys");

        if (generatedKeys.next()) {
            // FIXME; without metadata describing which properties
            // are actually a sequence, there is guess work
            // involved in matching the values returned
            // automatically from PreparedStatment as they
            // are anonymous in terms of the column names
            // making it impossible to match them to a metadata
            // property.
            List<Property> pkPropList = type.findProperties(KeyType.primary);
            if (pkPropList == null || pkPropList.size() == 0)
                throw new DataAccessException("no pri-key properties found for type '" + type.getName() + "'");
            if (pkPropList.size() > 1)
                throw new DataAccessException("multiple pri-key properties found for type '" + type.getName()
                        + "' - cannot map to generated keys");
            PlasmaProperty prop = (PlasmaProperty) pkPropList.get(0);
            // FIXME: need to find properties per column by physical name
            // alias
            // in case where multiple generated pri-keys
            for (int i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnName(i);
                if (log.isDebugEnabled())
                    log.debug("returned key column '" + columnName + "'");
                int columnType = rsMeta.getColumnType(i);
                Object value = converter.fromJDBCDataType(generatedKeys, i, columnType, prop);
                PropertyPair pair = new PropertyPair((PlasmaProperty) prop, value);
                resultKeys.add(pair);
            }
        }
    } catch (Throwable t) {
        throw new DataAccessException(t);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        if (streams != null)
            try {
                for (InputStream stream : streams)
                    stream.close();
            } catch (IOException e) {
                log.error(e.getMessage(), e);
            }
    }

    return resultKeys;
}

---

From source file:org.jumpmind.vaadin.ui.sqlexplorer.TabularResultLayout.java

protected void followTo(ForeignKey foreignKey) {
    Collection<Object> selectedRows = grid.getSelectedRows();
    if (selectedRows.size() > 0) {
        log.info("Following foreign key to " + foreignKey.getForeignTableName());

        if (queryPanel == null) {
            if (explorer != null) {
                queryPanel = explorer.openQueryWindow(db);
            } else {
                log.error("Failed to find current or create new query tab");
            }/*from   w w w  .  j  a  va2s.  c o m*/
        }

        Table foreignTable = foreignKey.getForeignTable();
        if (foreignTable == null) {
            foreignTable = db.getPlatform().getTableFromCache(foreignKey.getForeignTableName(), false);
        }

        Reference[] references = foreignKey.getReferences();
        for (Reference ref : references) {
            if (ref.getForeignColumn() == null) {
                ref.setForeignColumn(foreignTable.getColumnWithName(ref.getForeignColumnName()));
            }
        }

        String sql = createFollowSql(foreignTable, references, selectedRows.size());

        try {
            PreparedStatement ps = ((DataSource) db.getPlatform().getDataSource()).getConnection()
                    .prepareStatement(sql);
            int i = 1;
            for (Object row : selectedRows) {
                for (Reference ref : references) {
                    Object targetObject = grid.getContainerDataSource().getItem(row)
                            .getItemProperty(ref.getLocalColumnName()).getValue();
                    int targetType = ref.getForeignColumn().getMappedTypeCode();
                    ps.setObject(i, targetObject, targetType);
                    i++;
                }
            }
            sql = ps.toString().substring(ps.toString().indexOf("select "));
            queryPanel.executeSql(sql, false);
        } catch (SQLException e) {
            log.error("Failed to follow foreign key", e);
        }
    }
}

---

From source file:com.jaspersoft.jasperserver.util.JasperJdbcContainer.java

/**
 * Given a array of jdbc parameters and a PreparedStatment loops through the parameter array
 * and populates the PreparedStatement/*from w  ww.  java2 s  .  c  o  m*/
 * @param params jdbc parameters extracted from a CachedRowSet 
 * @param pstmt  the PreparedStatment to populate
 * @throws SQLException
 */
private void insertParameters(Object[] params, PreparedStatement pstmt) throws SQLException {

    Object[] param = null;

    for (int i = 0; i < params.length; i++) {

        if (params[i] instanceof Object[]) {
            param = (Object[]) params[i];
            if (param.length == 2) {
                if (param[0] == null) {
                    pstmt.setNull(i + 1, ((Integer) param[1]).intValue());
                    continue;
                }

                if (param[0] instanceof java.sql.Date || param[0] instanceof java.sql.Time
                        || param[0] instanceof java.sql.Timestamp) {
                    if (param[1] instanceof java.util.Calendar) {
                        pstmt.setDate(i + 1, (java.sql.Date) param[0], (java.util.Calendar) param[1]);
                        continue;
                    } else {
                        throw new SQLException("Unknown Parameter, expected java.util.Calendar");
                    }
                }

                if (param[0] instanceof Reader) {
                    pstmt.setCharacterStream(i + 1, (Reader) param[0], ((Integer) param[1]).intValue());
                    continue;
                }

                /*
                 * What's left should be setObject(int, Object, scale)
                 */
                if (param[1] instanceof Integer) {
                    pstmt.setObject(i + 1, param[0], ((Integer) param[1]).intValue());
                    continue;
                }

            } else if (param.length == 3) {

                if (param[0] == null) {
                    pstmt.setNull(i + 1, ((Integer) param[1]).intValue(), (String) param[2]);
                    continue;
                }

                // need to find and fix inputstreams
                if (param[0] instanceof java.io.InputStream) {
                    //logger.fine("Found parameter of type input stream");
                } //inputstream if

                /*
                 * no point at looking at the first element now; what's left
                 * must be the setObject() cases.
                 */
                if (param[1] instanceof Integer && param[2] instanceof Integer) {
                    pstmt.setObject(i + 1, param[0], ((Integer) param[1]).intValue(),
                            ((Integer) param[2]).intValue());
                    continue;
                }
                throw new SQLException("Unexpected Parameter");

            } else {
                // common case - this catches all SQL92 types
                pstmt.setObject(i + 1, params[i]);
                continue;
            }
        } else {
            // Try to get all the params to be set here
            pstmt.setObject(i + 1, params[i]);
            //logger.finest("Param" + i+ ": " + params[i]);
        }
    } //for
      //logger.exiting(getClass().getName(), "insertParameters");
}

---

From source file:org.seasar.dbflute.logic.replaceschema.loaddata.impl.DfAbsractDataWriter.java

protected boolean processXml(String tableName, String columnName, String value, PreparedStatement ps,
        int bindCount, Map<String, DfColumnMeta> columnInfoMap) throws SQLException {
    if (value == null) {
        return false; // basically no way
    }// w  w w  .  j  ava 2 s .co m
    final DfColumnMeta columnInfo = columnInfoMap.get(columnName);
    if (columnInfo != null) {
        if (getBasicProperties().isDatabasePostgreSQL()) {
            final String dbTypeName = columnInfo.getDbTypeName();
            if (!dbTypeName.startsWith("xml")) {
                return false;
            }
            value = filterXmlValue(value);
            ps.setObject(bindCount, value, Types.OTHER);
            return true;
        }
    }
    // unsupported when meta data is not found
    return false;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• »