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.feedzai.commons.sql.abstraction.engine.impl.H2Engine.java

@Override
protected int entityToPreparedStatement(final DbEntity entity, final PreparedStatement ps,
        final EntityEntry entry, final boolean useAutoInc) throws DatabaseEngineException {

    int i = 1;/*from w w w .j av  a  2 s  . c  om*/
    for (DbColumn column : entity.getColumns()) {
        if ((column.isAutoInc() && useAutoInc)) {
            continue;
        }

        try {
            final Object val;
            if (column.isDefaultValueSet() && !entry.containsKey(column.getName())) {
                val = column.getDefaultValue().getConstant();
            } else {
                val = entry.get(column.getName());
            }

            switch (column.getDbColumnType()) {
            case BLOB:
                ps.setBytes(i, objectToArray(val));

                break;
            case CLOB:
                if (val == null) {
                    ps.setNull(i, Types.CLOB);
                    break;
                }

                if (val instanceof String) {
                    StringReader sr = new StringReader((String) val);
                    ps.setCharacterStream(i, sr);
                } else {
                    throw new DatabaseEngineException("Cannot convert " + val.getClass().getSimpleName()
                            + " to String. CLOB columns only accept Strings.");
                }
                break;
            default:
                ps.setObject(i, val);
            }
        } catch (Exception ex) {
            throw new DatabaseEngineException("Error while mapping variables to database", ex);
        }

        i++;
    }

    return i - 1;
}

From source file:com.feedzai.commons.sql.abstraction.engine.impl.DB2Engine.java

@Override
protected int entityToPreparedStatement(final DbEntity entity, final PreparedStatement ps,
        final EntityEntry entry, final boolean useAutoInc) throws DatabaseEngineException {
    int i = 1;//w ww. j  a  va2s  .  c  o m
    for (DbColumn column : entity.getColumns()) {
        if (column.isAutoInc() && useAutoInc) {
            continue;
        }

        try {
            final Object val;
            if (column.isDefaultValueSet() && !entry.containsKey(column.getName())) {
                val = column.getDefaultValue().getConstant();
            } else {
                val = entry.get(column.getName());
            }
            switch (column.getDbColumnType()) {
            /*
             * CLOB and BLOB are handled the same way in DB2 since CLOB is not supported.
             */
            case CLOB:
            case BLOB:
                ps.setBytes(i, objectToArray(val));

                break;
            case BOOLEAN:
                Boolean b = (Boolean) val;
                if (b == null) {
                    ps.setObject(i, null);
                } else if (b) {
                    ps.setObject(i, "1");
                } else {
                    ps.setObject(i, "0");
                }

                break;
            default:
                ps.setObject(i, val);
            }
        } catch (Exception ex) {
            throw new DatabaseEngineException("Error while mapping variables to database", ex);
        }

        i++;
    }

    return i - 1;
}

From source file:org.orbisgis.corejdbc.internal.ReadRowSetImpl.java

private PreparedStatement createBatchQuery(Connection connection, Long firstPk, boolean cacheData,
        int queryOffset, int limit, boolean queryPk) throws SQLException {
    StringBuilder command = new StringBuilder();
    if (cachedColumnNames == null) {
        cacheColumnNames();//from  w  ww . ja  v  a  2s .c o m
    }
    command.append("SELECT ");
    if (queryPk) {
        command.append(pk_name);
        if (cacheData) {
            command.append(",");
        }
    }
    if (cacheData) {
        command.append(select_fields);
    }
    command.append(" FROM ");
    command.append(getTable());
    if (firstPk != null || !select_where.isEmpty()) {
        command.append(" WHERE ");
        if (!select_where.isEmpty()) {
            command.append(select_where);
        }
        if (firstPk != null) {
            if (!select_where.isEmpty()) {
                command.append(" AND ");
            }
            command.append(pk_name);
            command.append(" >= ?");
        }
    }
    if (isH2 || !pk_name.equals(MetaData.POSTGRE_ROW_IDENTIFIER)) {
        command.append(" ORDER BY ");
        command.append(pk_name);
    }
    command.append(" LIMIT ");
    command.append(limit);
    if (queryOffset > 0) {
        command.append(" OFFSET ");
        command.append(queryOffset);
    }
    PreparedStatement st = connection.prepareStatement(command.toString());
    for (Map.Entry<Integer, Object> entry : parameters.entrySet()) {
        st.setObject(entry.getKey(), entry.getValue());
    }
    if (firstPk != null) {
        if (isH2 || !pk_name.equals(MetaData.POSTGRE_ROW_IDENTIFIER)) {
            st.setLong(parameters.size() + 1, firstPk);
        } else {
            Ref pkRef = new Tid(firstPk);
            st.setRef(parameters.size() + 1, pkRef);
        }
    }
    return st;
}

From source file:es.juntadeandalucia.panelGestion.negocio.utiles.JDBCConnector.java

public int executeFeatureInsertLowLevel(String sql, SimpleFeature feature, List<ColumnVO> columns)
        throws Exception {
    Exception error = null;/*from   w ww .ja v a 2 s  .  com*/

    int numRowsAffected = 0;

    if (columns.size() > 0) {
        Connection connection = null;
        PreparedStatement preparedStmnt = null;

        try {
            DataSource dataSource = poolDataSources.get(schemaId);
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            preparedStmnt = connection.prepareStatement(sql);

            int paramPosition = 1;
            for (ColumnVO column : columns) {
                String dataValue = null;
                Object attribute = feature.getAttribute(column.getFilePosition());
                if (attribute != null) {
                    dataValue = attribute.toString();
                }
                Integer dataType = column.getSqlType();
                if (dataType == Types.OTHER) { // it is a geometry
                    // ((org.postgresql.PGConnection)connection).addDataType(column.getName(),
                    // column.getTypeClass());
                    preparedStmnt.setObject(paramPosition, dataValue);
                } else {
                    if (StringUtils.isEmpty(dataValue)) {
                        preparedStmnt.setNull(paramPosition, dataType);
                    } else {
                        preparedStmnt.setObject(paramPosition, dataValue, dataType);
                    }
                }
                paramPosition++;
            }

            numRowsAffected = preparedStmnt.executeUpdate();

            connection.commit();
        } catch (SQLException e) {
            error = e;
        } finally {
            if (preparedStmnt != null) {
                try {
                    preparedStmnt.close();
                } catch (SQLException se2) {
                    log.warn("No se pudo cerrar el statment: ".concat(se2.getLocalizedMessage()));
                }
            }
            if (connection != null) {
                try {
                    if (error != null) {
                        connection.rollback();
                    }
                } catch (SQLException se) {
                    log.warn("Se produjo un error al manejar la conexin: ".concat(se.getLocalizedMessage()));
                }
                try {
                    connection.close();
                } catch (SQLException se) {
                    log.warn("Se produjo un error al intentar cerrar la conexin: "
                            .concat(se.getLocalizedMessage()));
                }
            }
        }
        if (error != null) {
            throw error;
        }
    }
    return numRowsAffected;
}

From source file:org.orbisgis.corejdbc.internal.ReadRowSetImpl.java

protected PreparedStatement createPreparedStatement(Connection connection, String fields,
        String additionalWhere) throws SQLException {
    PreparedStatement st = connection
            .prepareStatement("SELECT " + fields + " " + getCommandWithoutFields(additionalWhere));
    for (Map.Entry<Integer, Object> entry : parameters.entrySet()) {
        st.setObject(entry.getKey(), entry.getValue());
    }//from  w  ww .j  ava2s .  c  o  m
    return st;
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testStringsWithPreparedStatementSetter() throws Exception {
    final Integer argument = new Integer(99);
    doTestStrings(new JdbcTemplateCallback() {
        public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
            template.query(sql, new PreparedStatementSetter() {
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setObject(1, argument);
                }// w ww . j  av  a2 s. c  o  m
            }, rch);
        }
    }, true, null, null, null, argument);
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

/**
 * Test update with dynamic SQL./*from   ww w . j  a  v a2 s  . co m*/
 */
public void testSqlUpdateWithArguments() throws Exception {
    final String sql = "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ? and PR = ?";
    int rowsAffected = 33;

    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    mockStatement.setObject(1, new Integer(4));
    ctrlStatement.setVoidCallable();
    mockStatement.setObject(2, new Float(1.4142), Types.NUMERIC, 2);
    ctrlStatement.setVoidCallable();
    mockStatement.executeUpdate();
    ctrlStatement.setReturnValue(33);
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    mockConnection.prepareStatement(sql);
    ctrlConnection.setReturnValue(mockStatement);
    ctrlStatement.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource);
    int actualRowsAffected = template.update(sql,
            new Object[] { new Integer(4), new SqlParameterValue(Types.NUMERIC, 2, new Float(1.4142)) });
    assertTrue("Actual rows affected is correct", actualRowsAffected == rowsAffected);
    ctrlStatement.verify();
}

From source file:org.cloudgraph.rdb.service.GraphQuery.java

private List<List<PropertyPair>> findResults(Query query, SelectionCollector collector, PlasmaType type,
        Connection con) {/*from w w  w . java 2 s.  c om*/
    Object[] params = new Object[0];
    RDBDataConverter converter = RDBDataConverter.INSTANCE;

    AliasMap aliasMap = new AliasMap(type);

    // construct a filter adding to alias map
    RDBFilterAssembler filterAssembler = null;
    Where where = query.findWhereClause();
    if (where != null) {
        filterAssembler = new RDBFilterAssembler(where, type, aliasMap);
        params = filterAssembler.getParams();
    }

    RDBOrderingAssembler orderingDeclAssembler = null;
    OrderBy orderby = query.findOrderByClause();
    if (orderby != null)
        orderingDeclAssembler = new RDBOrderingAssembler(orderby, type, aliasMap);
    RDBGroupingAssembler groupingDeclAssembler = null;
    GroupBy groupby = query.findGroupByClause();
    if (groupby != null)
        groupingDeclAssembler = new RDBGroupingAssembler(groupby, type, aliasMap);

    String rootAlias = aliasMap.getAlias(type);
    StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("SELECT DISTINCT "); // FIXME: only necessary if

    // FIXME: determine if any selected column(s) are LOB and don't use
    // DISTINCT in this case
    boolean hasLob = false;
    int i = 0;
    Set<Property> props = collector.getProperties(type);
    for (Property prop : props) {
        if (prop.isMany() && !prop.getType().isDataType())
            continue;
        if (i > 0)
            sqlQuery.append(", ");
        sqlQuery.append(rootAlias);
        sqlQuery.append(".");
        sqlQuery.append(((PlasmaProperty) prop).getPhysicalName());
        i++;
    }

    // construct a FROM clause from alias map
    sqlQuery.append(" FROM ");
    Iterator<PlasmaType> it = aliasMap.getTypes();
    int count = 0;
    while (it.hasNext()) {
        PlasmaType aliasType = it.next();
        String alias = aliasMap.getAlias(aliasType);
        if (count > 0)
            sqlQuery.append(", ");
        sqlQuery.append(this.statementUtil.getQualifiedPhysicalName(aliasType));
        sqlQuery.append(" ");
        sqlQuery.append(alias);
        count++;
    }

    // append WHERE filter
    if (filterAssembler != null) {
        sqlQuery.append(" ");
        sqlQuery.append(filterAssembler.getFilter());
    }

    if (orderingDeclAssembler != null) {
        sqlQuery.append(" ");
        sqlQuery.append(orderingDeclAssembler.getOrderingDeclaration());
    }

    if (groupingDeclAssembler != null) {
        sqlQuery.append(" ");
        sqlQuery.append(groupingDeclAssembler.getGroupingDeclaration());
    }

    // set the result range
    RDBMSVendorName vendor = PlasmaRuntime.getInstance().getRDBMSProviderVendor(DataAccessProviderName.JDBC);
    switch (vendor) {
    case ORACLE:
        if (query.getStartRange() != null && query.getEndRange() != null) {
            long offset = query.getStartRange() - 1; // inclusive
            if (offset < 0)
                offset = 0;
            long rowcount = query.getEndRange() - offset;
            StringBuilder buf = new StringBuilder();

            // Pagination wrapper making sure ordering occurs before any
            // ROWNUM selected by using
            // a nested SELECT.
            if (offset == 0) {
                buf.append("SELECT * FROM (");
                buf.append(sqlQuery);
                buf.append(") WHERE ROWNUM <= ");
                buf.append(rowcount);
            } else {
                // For offsets uses limiting condition on ROWNUM itself
                // as well as a
                // ROWNUM alias to enable Oracle STOPKEY processing
                // which helps performance.
                buf.append("SELECT * FROM (SELECT ");
                buf.append(PAGE_ALIAS);
                buf.append(".*, ROWNUM AS ");
                buf.append(ROWNUM_ALIAS);
                buf.append(" FROM (");
                buf.append(sqlQuery);
                buf.append(") ");
                buf.append(PAGE_ALIAS);
                buf.append(") ");
                buf.append("WHERE ");
                buf.append(ROWNUM_ALIAS);
                buf.append(" >= ");
                buf.append(query.getStartRange());
                buf.append(" AND ROWNUM <= ");
                buf.append(rowcount);
            }

            sqlQuery = buf;
        }
        break;
    case MYSQL:
        if (query.getStartRange() != null && query.getEndRange() != null) {
            long offset = query.getStartRange() - 1; // inclusive
            if (offset < 0)
                offset = 0;
            long rowcount = query.getEndRange() - offset;
            sqlQuery.append(" LIMIT "); // e.g. LIMIT offset,numrows
            sqlQuery.append(String.valueOf(offset));
            sqlQuery.append(",");
            sqlQuery.append(String.valueOf(rowcount));
        }
        break;
    default:
    }

    List<List<PropertyPair>> rows = new ArrayList<List<PropertyPair>>();
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        statement = con.prepareStatement(sqlQuery.toString(), ResultSet.TYPE_FORWARD_ONLY, /*
                                                                                            * ResultSet
                                                                                            * .
                                                                                            * TYPE_SCROLL_INSENSITIVE
                                                                                            * ,
                                                                                            */
                ResultSet.CONCUR_READ_ONLY);
        // statement.setFetchSize(32);
        // log.debug("setting fetch size 32");

        // set params
        // FIXME: params are pre-converted
        // to string in filter assembly
        int paramCount = 0;
        if (filterAssembler != null) {
            params = filterAssembler.getParams();
            if (params != null) {
                paramCount = params.length;
                for (i = 0; i < params.length; i++)
                    statement.setObject(i + 1, params[i]);
            }
        }

        // execute
        long before = System.currentTimeMillis();
        statement.execute();
        long after = System.currentTimeMillis();

        if (log.isDebugEnabled()) {
            if (params == null || params.length == 0) {
                log.debug("executed: " + sqlQuery.toString() + " (" + String.valueOf(after - before) + ")");
            } else {
                StringBuilder paramBuf = new StringBuilder();
                paramBuf.append(" [");
                for (int p = 0; p < params.length; p++) {
                    if (p > 0)
                        paramBuf.append(", ");
                    paramBuf.append(String.valueOf(params[p]));
                }
                paramBuf.append("]");
                log.debug("executed: " + sqlQuery.toString() + " " + paramBuf.toString() + " ("
                        + String.valueOf(after - before) + ")");
            }
        }

        // read results
        before = System.currentTimeMillis();
        int numresults = 0;
        rs = statement.getResultSet();
        int numcols = rs.getMetaData().getColumnCount();
        ResultSetMetaData rsMeta = rs.getMetaData();
        List<PropertyPair> row = null;
        PropertyPair pair = null;
        while (rs.next()) {
            row = new ArrayList<PropertyPair>();
            rows.add(row);
            for (i = 1; i <= numcols; i++) {
                String columnName = rsMeta.getColumnLabel(i); // mysql 5.5
                // returns
                // original
                // table col
                // name for
                // views
                if (columnName == null)
                    columnName = rsMeta.getColumnName(i);
                if (ROWNUM_ALIAS.equals(columnName))
                    continue;
                int columnType = rsMeta.getColumnType(i);

                PlasmaProperty prop = (PlasmaProperty) type.getProperty(columnName);
                PlasmaProperty valueProp = prop;
                while (!valueProp.getType().isDataType()) {
                    valueProp = this.statementUtil.getOppositePriKeyProperty(valueProp);
                }

                Object value = converter.fromJDBCDataType(rs, i, columnType, valueProp);
                if (value != null) {
                    pair = new PropertyPair(prop, value);
                    pair.setColumn(i);
                    if (!valueProp.equals(prop))
                        pair.setValueProp(valueProp);
                    row.add(pair);
                }
            }
            numresults++;
        }
        after = System.currentTimeMillis();
        if (log.isDebugEnabled())
            log.debug("read " + numresults + " results (" + String.valueOf(after - before) + ")");
    } catch (Throwable t) {
        StringBuffer buf = this.generateErrorDetail(t, sqlQuery.toString(), filterAssembler);
        log.error(buf.toString());
        throw new DataAccessException(t);
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
    }
    return rows;
}

From source file:org.batoo.jpa.jdbc.dbutils.QueryRunner.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with the given objects.
 * //from  w  w w.j a v  a2 s.  c  om
 * @param statement
 *            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
 */
private void fillStatement(PreparedStatement statement, Object... params) throws SQLException {
    // use local variable for performance
    boolean pmdKnownBroken = this.pmdKnownBroken;
    ParameterMetaData pmd = this.pmd;
    final boolean hasLob = this.hasLob;

    if (pmdKnownBroken) {
        ((PreparedStatementProxy) statement).setParamCount(params.length);
    } else {
        ((PreparedStatementProxy) statement).setParamCount(-1);
    }

    // if the jdbc adaptor wants to modify the parameters we let it do it its own way
    final JdbcAdaptor jdbcAdaptor = this.jdbcAdaptor;
    if ((jdbcAdaptor != null) && jdbcAdaptor.modifiesParameters()) {
        pmd = this.pmd = statement.getParameterMetaData();

        jdbcAdaptor.modifyParameters(pmd, params);
    }

    for (int i = 0; i < params.length; i++) {
        final Object param = params[i];
        if ((param != null) && (param != Void.TYPE)) {
            if (hasLob && (param instanceof Clob)) {
                if (this.jdbcAdaptor instanceof OracleAdaptor) {
                    statement.setCharacterStream(i + 1, ((Clob) param).getCharacterStream());
                } else {
                    statement.setClob(i + 1, (Clob) param);
                }
            } else if (hasLob && (param instanceof Blob)) {
                if (this.jdbcAdaptor instanceof OracleAdaptor) {
                    statement.setBinaryStream(i + 1, ((Blob) param).getBinaryStream());
                } else {
                    statement.setBlob(i + 1, (Blob) param);
                }
            } else {
                statement.setObject(i + 1, param);
            }
        } else {
            if (!pmdKnownBroken && (pmd == null)) {
                pmd = this.pmd = statement.getParameterMetaData();
            }

            // 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 (!pmdKnownBroken) {
                try {
                    sqlType = pmd.getParameterType(i + 1);
                } catch (final SQLException e) {
                    pmdKnownBroken = this.pmdKnownBroken = true;
                }
            }
            if (param != Void.TYPE) {
                statement.setNull(i + 1, sqlType);
            }
        }
    }
}

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

@Override
public void addDataline(IDataSet set, IDataLine... lines) {
    Connection localConn = null;/*from w w w.j a v  a  2  s.co  m*/
    PreparedStatement stmt = null;
    IStorageHandle handle = set.getStorageHandle();
    if (handle == null) {
        storageService.createDataSetStorage(set);
        handle = set.getStorageHandle();
    }
    try {
        localConn = storageService.getLocalConnection();
        final String insertStmt = handle.getInsertStatement();
        stmt = localConn.prepareStatement(insertStmt);
        for (IDataLine line : lines) {
            int col = 1;
            // For repository handles, we specify the row id
            // if (handle.isRepositoryHandle()) {
            if (line.getRowId() == 0) {
                stmt.setNull(col++, Types.BIGINT);
            } else {
                stmt.setLong(col++, line.getRowId());
            }
            // }
            // Processing line data
            for (IReference r : set.getColumnsRef()) {
                final IColumnValue value = line.getColumnValue(r);
                Object valueObj = null;
                if (value != null) {
                    valueObj = value.getValue();
                    if (valueObj != null) {
                        stmt.setObject(col, valueObj);
                    } else {
                        IBasicColumn c = (IBasicColumn) VersionHelper.getReferencedItem(r);
                        int jdbcType = storageService.getColumnSqlType(set, c);
                        stmt.setNull(col, jdbcType);
                    }
                }
                // Incrementing column index
                col++;

            }
            stmt.addBatch();
        }
        stmt.executeBatch();
        localConn.commit();
    } catch (SQLException e) {
        LOGGER.error(DBGMMessages.getString("service.data.addDatalineFailed") + e.getMessage(), //$NON-NLS-1$
                e);
    } finally {
        safeClose(null, stmt, localConn, false);
    }

}