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.eclipse.ecr.core.storage.sql.jdbc.JDBCMapper.java

@Override
public Serializable getRootId(Serializable repositoryId) throws StorageException {
    String sql = sqlInfo.getSelectRootIdSql();
    try {//ww  w.  j a va2  s.co m
        if (logger.isLogEnabled()) {
            logger.logSQL(sql, Collections.singletonList(repositoryId));
        }
        PreparedStatement ps = connection.prepareStatement(sql);
        try {
            ps.setObject(1, repositoryId);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                if (logger.isLogEnabled()) {
                    logger.log("  -> (none)");
                }
                return null;
            }
            Column column = sqlInfo.getSelectRootIdWhatColumn();
            Serializable id = column.getFromResultSet(rs, 1);
            if (logger.isLogEnabled()) {
                logger.log("  -> " + model.MAIN_KEY + '=' + id);
            }
            // check that we didn't get several rows
            if (rs.next()) {
                throw new StorageException("Row query for " + repositoryId + " returned several rows: " + sql);
            }
            return id;
        } finally {
            closeStatement(ps);
        }
    } catch (Exception e) {
        checkConnectionReset(e);
        throw new StorageException("Could not select: " + sql, e);
    }
}

From source file:org.sakaiproject.nakamura.lite.storage.jdbc.KeyValueIndexer.java

public DisposableIterator<Map<String, Object>> find(final String keySpace, final String columnFamily,
        Map<String, Object> properties, final CachingManager cacheManager) throws StorageClientException {
    String[] keys = null;//  w  ww.  java2  s . co  m
    if (properties != null && properties.containsKey(StorageConstants.CUSTOM_STATEMENT_SET)) {
        String customStatement = (String) properties.get(StorageConstants.CUSTOM_STATEMENT_SET);
        keys = new String[] { customStatement + "." + keySpace + "." + columnFamily,
                customStatement + "." + columnFamily, customStatement,
                "block-find." + keySpace + "." + columnFamily, "block-find." + columnFamily, "block-find" };
    } else {
        keys = new String[] { "block-find." + keySpace + "." + columnFamily, "block-find." + columnFamily,
                "block-find" };
    }

    final boolean rawResults = properties != null && properties.containsKey(StorageConstants.RAWRESULTS);

    String sql = client.getSql(keys);
    if (sql == null) {
        throw new StorageClientException("Failed to locate SQL statement for any of  " + Arrays.toString(keys));
    }

    String[] statementParts = StringUtils.split(sql, ';');

    StringBuilder tables = new StringBuilder();
    StringBuilder where = new StringBuilder();
    StringBuilder order = new StringBuilder();
    StringBuilder extraColumns = new StringBuilder();

    // collect information on paging
    long page = 0;
    long items = 25;
    if (properties != null) {
        if (properties.containsKey(StorageConstants.PAGE)) {
            page = Long.valueOf(String.valueOf(properties.get(StorageConstants.PAGE)));
        }
        if (properties.containsKey(StorageConstants.ITEMS)) {
            items = Long.valueOf(String.valueOf(properties.get(StorageConstants.ITEMS)));
        }
    } else {
        properties = ImmutableMap.of();
    }
    long offset = page * items;

    // collect information on sorting
    String[] sorts = new String[] { null, "asc" };
    String _sortProp = (String) properties.get(StorageConstants.SORT);
    if (_sortProp != null) {
        String[] _sorts = StringUtils.split(_sortProp);
        if (_sorts.length == 1) {
            sorts[0] = _sorts[0];
        } else if (_sorts.length == 2) {
            sorts[0] = _sorts[0];
            sorts[1] = _sorts[1];
        }
    }

    List<Object> parameters = Lists.newArrayList();
    int set = 0;
    for (Entry<String, Object> e : properties.entrySet()) {
        Object v = e.getValue();
        String k = e.getKey();
        if (shouldFind(keySpace, columnFamily, k) || (v instanceof Map)) {
            if (v != null) {
                // check for a value map and treat sub terms as for OR terms.
                // Only go 1 level deep; don't recurse. That's just silly.
                if (v instanceof Map) {
                    // start the OR grouping
                    where.append(" (");
                    @SuppressWarnings("unchecked")
                    Set<Entry<String, Object>> subterms = ((Map<String, Object>) v).entrySet();
                    for (Iterator<Entry<String, Object>> subtermsIter = subterms.iterator(); subtermsIter
                            .hasNext();) {
                        Entry<String, Object> subterm = subtermsIter.next();
                        String subk = subterm.getKey();
                        Object subv = subterm.getValue();
                        // check that each subterm should be indexed
                        if (shouldFind(keySpace, columnFamily, subk)) {
                            set = processEntry(statementParts, tables, where, order, extraColumns, parameters,
                                    subk, subv, sorts, set);
                            // as long as there are more add OR
                            if (subtermsIter.hasNext()) {
                                where.append(" OR");
                            }
                        }
                    }
                    // end the OR grouping
                    where.append(") AND");
                } else {
                    // process a first level non-map value as an AND term

                    if (v instanceof Iterable<?>) {
                        for (Object vo : (Iterable<?>) v) {
                            set = processEntry(statementParts, tables, where, order, extraColumns, parameters,
                                    k, vo, sorts, set);
                            where.append(" AND");
                        }
                    } else {
                        set = processEntry(statementParts, tables, where, order, extraColumns, parameters, k, v,
                                sorts, set);
                        where.append(" AND");
                    }
                }
            } else if (!k.startsWith("_")) {
                LOGGER.debug("Search on {}:{} filter dropped due to null value.", columnFamily, k);
            }
        } else {
            if (!k.startsWith("_")) {
                LOGGER.warn("Search on {}:{} is not supported, filter dropped ", columnFamily, k);
            }
        }
    }
    if (where.length() == 0) {
        return new DisposableIterator<Map<String, Object>>() {

            private Disposer disposer;

            public boolean hasNext() {
                return false;
            }

            public Map<String, Object> next() {
                return null;
            }

            public void remove() {
            }

            public void close() {
                if (disposer != null) {
                    disposer.unregisterDisposable(this);
                }
            }

            public void setDisposer(Disposer disposer) {
                this.disposer = disposer;
            }

        };
    }

    if (sorts[0] != null && order.length() == 0) {
        if (shouldFind(keySpace, columnFamily, sorts[0])) {
            String t = "a" + set;
            if (statementParts.length > STMT_EXTRA_COLUMNS) {
                extraColumns.append(MessageFormat.format(statementParts[STMT_EXTRA_COLUMNS], t));
            }
            tables.append(MessageFormat.format(statementParts[STMT_TABLE_JOIN], t));
            parameters.add(sorts[0]);
            where.append(MessageFormat.format(statementParts[STMT_WHERE_SORT], t)).append(" AND");
            order.append(MessageFormat.format(statementParts[STMT_ORDER], t, sorts[1]));
        } else {
            LOGGER.warn("Sort on {}:{} is not supported, sort dropped", columnFamily, sorts[0]);
        }
    }

    final String sqlStatement = MessageFormat.format(statementParts[STMT_BASE], tables.toString(),
            where.toString(), order.toString(), items, offset, extraColumns.toString());

    PreparedStatement tpst = null;
    ResultSet trs = null;
    try {
        LOGGER.debug("Preparing {} ", sqlStatement);
        tpst = client.getConnection().prepareStatement(sqlStatement);
        client.inc("iterator");
        tpst.clearParameters();
        int i = 1;
        for (Object params : parameters) {
            tpst.setObject(i, params);
            LOGGER.debug("Setting {} ", params);

            i++;
        }

        long qtime = System.currentTimeMillis();
        trs = tpst.executeQuery();
        qtime = System.currentTimeMillis() - qtime;
        if (qtime > client.getSlowQueryThreshold() && qtime < client.getVerySlowQueryThreshold()) {
            JDBCStorageClient.SQL_LOGGER.warn("Slow Query {}ms {} params:[{}]", new Object[] { qtime,
                    sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) });
        } else if (qtime > client.getVerySlowQueryThreshold()) {
            JDBCStorageClient.SQL_LOGGER.error("Very Slow Query {}ms {} params:[{}]", new Object[] { qtime,
                    sqlStatement, Arrays.toString(parameters.toArray(new String[parameters.size()])) });
        }
        client.inc("iterator r");
        LOGGER.debug("Executed ");

        // pass control to the iterator.
        final PreparedStatement pst = tpst;
        final ResultSet rs = trs;
        final ResultSetMetaData rsmd = rs.getMetaData();
        tpst = null;
        trs = null;
        return client.registerDisposable(new PreemptiveIterator<Map<String, Object>>() {

            private Map<String, Object> nextValue = Maps.newHashMap();
            private boolean open = true;

            @Override
            protected Map<String, Object> internalNext() {
                return nextValue;
            }

            @Override
            protected boolean internalHasNext() {
                try {
                    if (open && rs.next()) {
                        if (rawResults) {
                            Builder<String, Object> b = ImmutableMap.builder();
                            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                                b.put(String.valueOf(i), rs.getObject(i));
                            }
                            nextValue = b.build();
                        } else {
                            String id = rs.getString(1);
                            nextValue = client.internalGet(keySpace, columnFamily, id, cacheManager);
                            LOGGER.debug("Got Row ID {} {} ", id, nextValue);
                        }
                        return true;
                    }
                    close();
                    nextValue = null;
                    LOGGER.debug("End of Set ");
                    return false;
                } catch (SQLException e) {
                    LOGGER.error(e.getMessage(), e);
                    close();
                    nextValue = null;
                    return false;
                } catch (StorageClientException e) {
                    LOGGER.error(e.getMessage(), e);
                    close();
                    nextValue = null;
                    return false;
                }
            }

            @Override
            public void close() {
                if (open) {
                    open = false;
                    try {
                        if (rs != null) {
                            rs.close();
                            client.dec("iterator r");
                        }
                    } catch (SQLException e) {
                        LOGGER.warn(e.getMessage(), e);
                    }
                    try {
                        if (pst != null) {
                            pst.close();
                            client.dec("iterator");
                        }
                    } catch (SQLException e) {
                        LOGGER.warn(e.getMessage(), e);
                    }
                    super.close();
                }

            }
        });
    } catch (SQLException e) {
        LOGGER.error(e.getMessage(), e);
        throw new StorageClientException(e.getMessage() + " SQL Statement was " + sqlStatement, e);
    } finally {
        // trs and tpst will only be non null if control has not been passed
        // to the iterator.
        try {
            if (trs != null) {
                trs.close();
                client.dec("iterator r");
            }
        } catch (SQLException e) {
            LOGGER.warn(e.getMessage(), e);
        }
        try {
            if (tpst != null) {
                tpst.close();
                client.dec("iterator");
            }
        } catch (SQLException e) {
            LOGGER.warn(e.getMessage(), e);
        }
    }
}

From source file:com.espertech.esperio.db.core.MultiKeyMultiValueTable.java

/**
 * Determine if the key exists./*from w ww .  ja va2  s.  c  om*/
 * @param connection db connection
 * @param keys key values
 * @return indicator whether row exists
 * @throws StoreExceptionDBRel failed operation
 */
public boolean isExistsKey(Connection connection, Object[] keys) {
    StringBuilder builder = new StringBuilder();
    builder.append("select 1 from ");
    builder.append(tableName);

    builder.append(" where ");
    String delimiter = "";
    for (String keyField : keyFieldNames) {
        builder.append(delimiter);
        builder.append(keyField);
        builder.append("=?");
        delimiter = " and ";
    }

    String query = builder.toString();
    PreparedStatement statement = null;
    try {
        if ((ExecutionPathDebugLog.isDebugEnabled) && (log.isDebugEnabled())) {
            log.debug("Executing query '" + query + "' for keys '" + print(keys) + "'");
        }
        statement = connection.prepareStatement(query);
        for (int i = 0; i < keys.length; i++) {
            statement.setObject(i + 1, keys[i]);
        }

        ResultSet rs = statement.executeQuery();
        if (!rs.next()) {
            return false;
        }

        return true;
    } catch (SQLException ex) {
        String message = "Failed to invoke : " + query + " :" + ex.getMessage();
        log.error(message, ex);
        storeExceptionHandler.handle(message, ex);
        throw new StoreExceptionDBRel(message, ex);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
        }
    }
}

From source file:com.espertech.esperio.db.core.MultiKeyMultiValueTable.java

private void runInsert(Connection connection, String query, Object[] keys, Object[] values) {
    PreparedStatement statement = null;
    try {/*from   w  w w .j  ava2  s . co m*/
        if ((ExecutionPathDebugLog.isDebugEnabled) && (log.isDebugEnabled())) {
            log.debug("Executing query '" + query + "' for keys '" + print(keys) + "'");
        }
        statement = connection.prepareStatement(query);
        int index = 1;
        for (Object key : keys) {
            statement.setObject(index, key);
            index++;
        }
        for (Object value : values) {
            statement.setObject(index, value);
            index++;
        }
        statement.executeUpdate();
    } catch (SQLException ex) {
        String message = "Failed to invoke : " + query + " :" + ex.getMessage();
        if ((ex.getSQLState() != null) && (ex.getSQLState().equals("23000"))) {
            throw new StoreExceptionDBDuplicateRow(message, ex);
        }
        log.error(message, ex);
        storeExceptionHandler.handle(message, ex);
        throw new StoreExceptionDBRel(message, ex);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
        }
    }
}

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

public int executeUpdateAndIgnoreStatistics(ConnectionContext cc, Object[] overrideParams)
        throws NamingException, SQLException {
    if (log.isTraceEnabled())
        trace(cc, overrideParams);//from w  ww.java2  s .  c om

    PreparedStatement stmt = null;
    try {
        Connection conn = cc.getConnection();
        DbmsSqlText sqlText = getSqlText(cc);
        String sql = sqlText.getSql(cc);
        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);
        }

        int executeStmtResult = stmt.executeUpdate();
        return executeStmtResult;
    } catch (SQLException e) {
        log.error(createExceptionMessage(cc, overrideParams), e);
        throw e;
    } finally {
        if (stmt != null)
            stmt.close();
    }
}

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

private void scanPKs(String table, String sql, Set allowedIds, List fkTables) throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;//from  www. j av  a  2s  .c om
    try {
        pstmt = this.connection.getConnection().prepareStatement(sql);
        for (Iterator iterator = allowedIds.iterator(); iterator.hasNext();) {
            Object pk = iterator.next(); // id being scanned
            if (this.logger.isDebugEnabled()) {
                this.logger.debug("Executing sql for ? = " + pk);
            }
            pstmt.setObject(1, pk);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                for (int i = 0; i < fkTables.size(); i++) {
                    String newTable = (String) fkTables.get(i);
                    Object fk = rs.getObject(i + 1);
                    if (fk != null) {
                        if (this.logger.isDebugEnabled()) {
                            this.logger.debug("New ID: " + newTable + "->" + fk);
                        }
                        addPKToScan(newTable, fk);
                    } else {
                        this.logger.warn("Found null FK for relationship  " + table + "=>" + newTable);
                    }
                }
            }
        }
    } catch (SQLException e) {
        logger.error("scanPKs()", e);
    } finally {
        // new in the finally block. has been in the catch only before
        SQLHelper.close(rs, pstmt);
    }
}

From source file:com.espertech.esperio.db.core.MultiKeyMultiValueTable.java

private boolean runUpdate(Connection connection, String query, Object[] keys, Object[] values) {
    PreparedStatement statement = null;
    try {/*  w w  w .j ava2 s . c o m*/
        if ((ExecutionPathDebugLog.isDebugEnabled) && (log.isDebugEnabled())) {
            log.debug("Executing query '" + query + "' for keys '" + print(keys) + "'");
        }
        statement = connection.prepareStatement(query);
        int index = 1;
        for (int i = 0; i < values.length; i++) {
            statement.setObject(index, values[i]);
            index++;
        }
        for (int i = 0; i < keys.length; i++) {
            statement.setObject(index, keys[i]);
            index++;
        }
        int rows = statement.executeUpdate();
        if ((ExecutionPathDebugLog.isDebugEnabled) && (log.isDebugEnabled())) {
            log.debug("Update yielded " + rows + " rows");
        }
        return rows != 0;
    } catch (SQLException ex) {
        String message = "Failed to invoke : " + query + " :" + ex.getMessage();
        log.error(message, ex);
        storeExceptionHandler.handle(message, ex);
        throw new StoreExceptionDBRel(message, ex);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
        }
    }
}

From source file:com.espertech.esperio.db.core.MultiKeyMultiValueTable.java

/**
 * Read all rows starting with the key values supplied, key value must start at the first and can between the 1st and last key.
 * @param connection to use/* www.  j av  a 2  s  .c  om*/
 * @param keys to use
 * @return list of objects
 */
public List<Object[]> readAllSubkeyed(Connection connection, Object[] keys) {
    StringBuilder builder = new StringBuilder();
    builder.append("select ");

    String delimiter = "";
    for (String keyField : keyFieldNames) {
        builder.append(delimiter);
        builder.append(keyField);
        delimiter = ",";
    }
    for (String valueField : valueFieldNames) {
        builder.append(delimiter);
        builder.append(valueField);
        delimiter = ",";
    }
    builder.append(" from ");
    builder.append(tableName);
    builder.append(" where ");
    delimiter = "";
    for (int i = 0; i < keys.length; i++) {
        builder.append(delimiter);
        builder.append(keyFieldNames[i]);
        builder.append("=?");
        delimiter = " and ";
    }

    String query = builder.toString();
    PreparedStatement statement = null;
    try {
        if ((ExecutionPathDebugLog.isDebugEnabled) && (log.isDebugEnabled())) {
            log.debug("Executing query '" + query + "' for keys '" + print(keys) + "'");
        }
        statement = connection.prepareStatement(query);
        for (int i = 0; i < keys.length; i++) {
            statement.setObject(i + 1, keys[i]);
        }

        ResultSet rs = statement.executeQuery();
        if (!rs.next()) {
            return Collections.EMPTY_LIST;
        }

        List<Object[]> result = new ArrayList<Object[]>();
        do {
            Object[] row = new Object[keyFieldNames.length + valueFieldNames.length];
            int index = 0;
            for (int i = 0; i < keyFieldNames.length; i++) {
                row[index] = DBUtil.getValue(rs, index + 1, keyTypes[i]);
                index++;
            }
            for (int i = 0; i < valueFieldNames.length; i++) {
                row[index] = DBUtil.getValue(rs, index + 1, valueTypes[i]);
                index++;
            }
            result.add(row);
        } while (rs.next());

        return result;
    } catch (SQLException ex) {
        String message = "Failed to invoke : " + query + " :" + ex.getMessage();
        log.error(message, ex);
        storeExceptionHandler.handle(message, ex);
        throw new StoreExceptionDBRel(message, ex);
    } finally {
        try {
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
        }
    }
}

From source file:org.nuxeo.ecm.core.storage.sql.jdbc.dialect.DialectOracle.java

@Override
public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException {
    switch (idType) {
    case VARCHAR:
        ps.setObject(index, value);
        break;/*from  ww  w  .  ja  va 2 s  .c o  m*/
    case SEQUENCE:
        setIdLong(ps, index, value);
        break;
    default:
        throw new AssertionError("Unknown id type: " + idType);
    }
}

From source file:org.efaps.admin.common.SystemConfiguration.java

/**
 * Read the config.//from  w w w.  j a va2  s. c om
 * @throws CacheReloadException on error
 */
private void readConfig() throws CacheReloadException {
    ConnectionResource con = null;
    try {
        boolean closeContext = false;
        if (!Context.isThreadActive()) {
            Context.begin();
            closeContext = true;
        }
        final List<Object[]> dbValues = new ArrayList<>();
        con = Context.getThreadContext().getConnectionResource();
        PreparedStatement stmt = null;
        try {
            stmt = con.getConnection().prepareStatement(SystemConfiguration.SQL_CONFIG);
            stmt.setObject(1, getId());
            final ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                dbValues.add(new Object[] { rs.getLong(1), rs.getString(2), rs.getString(3), rs.getLong(4),
                        rs.getString(5) });
            }
            rs.close();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        con.commit();
        if (closeContext) {
            Context.rollback();
        }
        for (final Object[] row : dbValues) {
            final Long typeId = (Long) row[0];
            final String key = (String) row[1];
            final String value = (String) row[2];
            final Long companyId = (Long) row[3];
            final String appkey = (String) row[4];
            final Type type = Type.get(typeId);
            final ConfType confType;
            if (type.equals(CIAdminCommon.SystemConfigurationLink.getType())) {
                confType = ConfType.LINK;
            } else if (type.equals(CIAdminCommon.SystemConfigurationObjectAttribute.getType())) {
                confType = ConfType.OBJATTR;
            } else {
                confType = ConfType.ATTRIBUTE;
            }
            this.values.add(new Value(confType, key, value, companyId, appkey));
        }
    } catch (final SQLException e) {
        throw new CacheReloadException("could not read SystemConfiguration attributes", e);
    } catch (final EFapsException e) {
        throw new CacheReloadException("could not read SystemConfiguration attributes", e);
    } finally {
        if ((con != null) && con.isOpened()) {
            try {
                con.abort();
            } catch (final EFapsException e) {
                throw new CacheReloadException("could not read SystemConfiguration attributes", e);
            }
        }
    }
}