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:net.firejack.platform.core.utils.db.DBUtils.java

private static void insertDataToTargetTable(TablesMapping mapping, Connection sourceConnection,
        Connection targetConnection) throws SQLException {
    Map<Column, Column> columnMapping = mapping.getColumnMapping();
    if (columnMapping.isEmpty()) {
        logger.warn("No columns are detected - no data to insert.");
    } else {//  ww  w.  j  a  v a 2s  .c  om
        ResultSet rs = selectDataFromSource(sourceConnection, mapping);

        String insertQuery = populateInsertQuery(mapping);
        PreparedStatement insertStatement = targetConnection.prepareStatement(insertQuery);
        targetConnection.setAutoCommit(false);
        try {
            int currentStep = 1;
            while (rs.next()) {
                for (int i = 1; i <= columnMapping.size(); i++) {
                    insertStatement.setObject(i, rs.getObject(i));
                }
                insertStatement.addBatch();
                if (++currentStep > DEFAULT_BATCH_SIZE) {
                    insertStatement.executeBatch();
                    targetConnection.commit();
                    currentStep = 1;
                }
            }
            if (currentStep != 1) {
                insertStatement.executeBatch();
                targetConnection.commit();
            }
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
            targetConnection.rollback();
        } finally {
            insertStatement.close();
            rs.close();
        }
    }
}

From source file:hnu.helper.DataBaseConnection.java

/**
 * Create and Execute an SQL PreparedStatement and returns true if
 * everything went ok. Can be used for DML and DDL.
 * Borrows from apache.commons.scaffold.sql.StatementUtils (see
 * jakarta-commons-sandbox/scaffold)/*from   w ww .ja  v  a 2s .  c om*/
 */
public static boolean execute(String sql, Object[] parameters) {
    DataBaseConnection db = new DataBaseConnection();
    boolean returnValue = false;
    Connection conn = db.getDBConnection();
    PreparedStatement pStmt = null;
    Statement stmt = null;

    log.debug("About to execute: " + sql);
    try {
        if (parameters == null || (parameters.length == 0)) {
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        } else {
            pStmt = conn.prepareStatement(sql);
            for (int i = 0; i < parameters.length; i++) {
                log.debug("parameter " + i + ": " + parameters[i]);
                pStmt.setObject(i + 1, parameters[i]);
            }
            pStmt.executeUpdate();
        }
        log.debug(".. executed without exception (hope to return 'true') ");
        returnValue = true;
    } catch (SQLException ex) {
        log.error("Error executing: '" + sql + "'", ex);
        returnValue = false;
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception ex) {
            log.error("Couldn't close the statement or connection.", ex);
        }
    }
    return returnValue;
}

From source file:com.example.querybuilder.server.Jdbc.java

public static void initializeParameters(PreparedStatement preparedStatement, Object... parameters) {
    for (int columnOffset = 0, columnNumber = 1; columnOffset < parameters.length; columnOffset++, columnNumber++) {
        Object value = parameters[columnOffset];
        try {/*from   www. ja  va  2s .  co m*/
            if (value == null) {
                int parameterType = preparedStatement.getParameterMetaData().getParameterType(columnNumber);
                preparedStatement.setNull(columnNumber, parameterType);
            } else {
                preparedStatement.setObject(columnNumber, value);
            }
        } catch (SQLException e) {
            throw new SqlRuntimeException(
                    e.toString() + "\n" + "columnNumber=" + columnNumber + ", value=" + value);
        }
    }
}

From source file:com.streamsets.pipeline.stage.origin.jdbc.table.BaseTableJdbcSourceIT.java

protected static void setParamsToPreparedStatement(PreparedStatement ps, int paramIdx, int sqlType,
        Object value) throws SQLException {
    switch (sqlType) {
    case Types.DATE:
        ps.setDate(paramIdx, new java.sql.Date(((Date) value).getTime()));
        break;/*  w  ww.  j a v  a2 s  .  c  o  m*/
    case Types.TIME:
        ps.setTime(paramIdx, new java.sql.Time(((Date) value).getTime()));
        break;
    case Types.TIMESTAMP:
        ps.setTimestamp(paramIdx, new java.sql.Timestamp(((Date) value).getTime()));
        break;
    default:
        ps.setObject(paramIdx, value);
    }
}

From source file:com.hangum.tadpole.rdb.core.editors.main.utils.plan.CubridExecutePlanUtils.java

/**
 * cubrid execute plan/*  w ww.j  a v  a2s  .c om*/
 * 
 * @param userDB
 * @param reqQuery
 * @return
 * @throws Exception
 */
public static String plan(UserDBDAO userDB, final RequestQuery reqQuery) throws Exception {
    String sql = reqQuery.getSql();
    if (!sql.toLowerCase().startsWith("select")) {
        logger.error("[cubrid execute plan ]" + sql);
        throw new Exception("This statment not select. please check.");
    }
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pstmt = null;

    try {
        conn = TadpoleSQLManager.getInstance(userDB).getDataSource().getConnection();
        conn.setAutoCommit(false); //     auto commit? false  .

        sql = StringUtils.trim(sql).substring(6);
        if (logger.isDebugEnabled())
            logger.debug("[qubrid modifying query]" + sql);
        sql = "select " + RECOMPILE + sql;

        pstmt = conn.prepareStatement(sql);
        if (reqQuery.getSqlStatementType() == SQL_STATEMENT_TYPE.PREPARED_STATEMENT) {
            final Object[] statementParameter = reqQuery.getStatementParameter();
            for (int i = 1; i <= statementParameter.length; i++) {
                pstmt.setObject(i, statementParameter[i - 1]);
            }
        }
        ((CUBRIDStatement) pstmt).setQueryInfo(true);
        rs = pstmt.executeQuery();

        String plan = ((CUBRIDStatement) pstmt).getQueryplan(); //  ?    .
        if (logger.isDebugEnabled())
            logger.debug("cubrid plan text : " + plan);

        return plan;
    } finally {
        if (rs != null)
            rs.close();
        if (pstmt != null)
            pstmt.close();
        if (conn != null)
            conn.close();
    }
}

From source file:com.useekm.indexing.postgis.IndexedStatement.java

public static void addNewBatch(PreparedStatement stat, IndexedStatement indexedStatement) throws SQLException {
    int idx = 1;//from   ww  w  .  j  ava 2s.com
    if (indexedStatement.objectDate != null)
        stat.setDate(idx++, new java.sql.Date(indexedStatement.objectDate.getTime()));
    else
        stat.setDate(idx++, null);
    stat.setString(idx++, indexedStatement.objectLanguage);
    stat.setObject(idx++, indexedStatement.objectSpatial);
    stat.setString(idx++, indexedStatement.objectString);
    stat.setString(idx++, indexedStatement.objectTsVectorConfig);
    stat.setString(idx++, indexedStatement.objectType);
    stat.setBoolean(idx++, indexedStatement.objectUri);
    stat.setString(idx++, indexedStatement.predicate);
    stat.setString(idx++, indexedStatement.subject);
    stat.addBatch();
}

From source file:org.eclipse.ecr.core.storage.sql.extensions.EmbeddedFunctions.java

/**
 * Checks if access to a document is allowed.
 * <p>//w w w. j  av  a2 s. c o  m
 * This implements in SQL the ACL-based security policy logic.
 *
 * @param conn the database connection
 * @param id the id of the document
 * @param principals the allowed identities
 * @param permissions the allowed permissions
 */
public static boolean isAccessAllowed(Connection conn, Serializable id, Set<String> principals,
        Set<String> permissions) throws SQLException {
    if (log.isDebugEnabled()) {
        logDebug("isAccessAllowed " + id + " " + principals + " " + permissions);
    }
    PreparedStatement ps1 = null;
    PreparedStatement ps2 = null;
    PreparedStatement ps3 = null;
    try {
        ps1 = conn.prepareStatement( //
                "SELECT \"GRANT\", \"PERMISSION\", \"USER\" FROM \"ACLS\" " + "WHERE ID = ? ORDER BY POS");
        ps2 = conn.prepareStatement("SELECT PARENTID FROM HIERARCHY WHERE ID = ?");
        boolean first = true;
        do {
            /*
             * Check permissions at this level.
             */
            ps1.setObject(1, id);
            ResultSet rs = ps1.executeQuery();
            while (rs.next()) {
                boolean grant = rs.getShort(1) != 0;
                String permission = rs.getString(2);
                String user = rs.getString(3);
                if (log.isDebugEnabled()) {
                    logDebug(" -> " + user + " " + permission + " " + grant);
                }
                if (principals.contains(user) && permissions.contains(permission)) {
                    if (log.isDebugEnabled()) {
                        logDebug(" => " + grant);
                    }
                    return grant;
                }
            }
            /*
             * Nothing conclusive found, repeat on the parent.
             */
            ps2.setObject(1, id);
            rs = ps2.executeQuery();
            Serializable newId;
            if (rs.next()) {
                newId = (Serializable) rs.getObject(1);
                if (rs.wasNull()) {
                    newId = null;
                }
            } else {
                // no such id
                newId = null;
            }
            if (first && newId == null) {
                // there is no parent for the first level
                // we may have a version on our hands, find the live doc
                ps3 = conn.prepareStatement("SELECT VERSIONABLEID FROM VERSIONS WHERE ID = ?");
                ps3.setObject(1, id);
                rs = ps3.executeQuery();
                if (rs.next()) {
                    newId = (Serializable) rs.getObject(1);
                    if (rs.wasNull()) {
                        newId = null;
                    }
                } else {
                    // no such id
                    newId = null;
                }
            }
            first = false;
            id = newId;
        } while (id != null);
        /*
         * We reached the root, deny access.
         */
        if (log.isDebugEnabled()) {
            logDebug(" => false (root)");
        }
        return false;
    } finally {
        if (ps1 != null) {
            ps1.close();
        }
        if (ps2 != null) {
            ps2.close();
        }
        if (ps3 != null) {
            ps3.close();
        }
    }
}

From source file:com.stratelia.webactiv.util.DBUtil.java

/**
 * Centralization in order to sets the parameters on a prepare statement.
 * @param preparedStatement/*from  w  w w . j a v a 2s  . com*/
 * @param parameters
 * @throws SQLException
 */
public static <O> void setParameters(PreparedStatement preparedStatement, Collection<O> parameters)
        throws SQLException {
    int paramIndex = 1;
    for (Object parameter : parameters) {
        if (parameter == null) {
            preparedStatement.setObject(paramIndex, null);
        } else if (parameter instanceof String) {
            preparedStatement.setString(paramIndex, (String) parameter);
        } else if (parameter instanceof Enum) {
            preparedStatement.setString(paramIndex, ((Enum) parameter).name());
        } else if (parameter instanceof Integer) {
            preparedStatement.setInt(paramIndex, (Integer) parameter);
        } else if (parameter instanceof Long) {
            preparedStatement.setLong(paramIndex, (Long) parameter);
        } else if (parameter instanceof Timestamp) {
            preparedStatement.setTimestamp(paramIndex, (Timestamp) parameter);
        } else if (parameter instanceof Date) {
            preparedStatement.setDate(paramIndex, new java.sql.Date(((Date) parameter).getTime()));
        } else if (parameter instanceof UserDetail) {
            preparedStatement.setString(paramIndex, ((UserDetail) parameter).getId());
        } else {
            throw new IllegalArgumentException("SQL parameter type not handled: " + parameter.getClass());
        }
        paramIndex++;
    }
}

From source file:com.streamsets.pipeline.lib.jdbc.multithread.TableReadContext.java

private static void setParamVal(PreparedStatement ps, int paramIdx, int sqlType, String paramVal)
        throws SQLException, StageException {
    Utils.checkState(OffsetQueryUtil.SQL_TYPE_TO_FIELD_TYPE.containsKey(sqlType),
            Utils.format("Unsupported Partition Offset Type: {}", sqlType));
    //All Date/Time Types are stored as long offsets
    //Parse string to get long.
    switch (sqlType) {
    case Types.TIME:
        ps.setTime(paramIdx, new java.sql.Time(Long.valueOf(paramVal)));
        break;/*from   w  w  w. j a  va2  s . com*/
    case Types.DATE:
        ps.setDate(paramIdx, new java.sql.Date(Long.valueOf(paramVal)));
        break;
    case Types.TIMESTAMP:
        Timestamp ts = TableContextUtil.getTimestampForOffsetValue(paramVal);
        ps.setTimestamp(paramIdx, ts);
        break;
    default:
        ps.setObject(paramIdx,
                Field.create(OffsetQueryUtil.SQL_TYPE_TO_FIELD_TYPE.get(sqlType), paramVal).getValue());
    }
}

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

/**
 * @param _sql sql statement to be executed
 * @param _criteria filter criteria//from   w w  w .j  av a 2s.  c o m
 * @throws CacheReloadException on error
 * @return false
 */
private static boolean getSystemConfigurationFromDB(final String _sql, final Object _criteria)
        throws CacheReloadException {
    boolean ret = false;
    ConnectionResource con = null;
    try {
        boolean closeContext = false;
        if (!Context.isThreadActive()) {
            Context.begin();
            closeContext = true;
        }
        SystemConfiguration sysConfig = null;
        con = Context.getThreadContext().getConnectionResource();
        PreparedStatement stmt = null;
        try {
            stmt = con.getConnection().prepareStatement(_sql);
            stmt.setObject(1, _criteria);
            final ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                final long id = rs.getLong(1);
                final String name = rs.getString(2).trim();
                final String uuid = rs.getString(3).trim();
                SystemConfiguration.LOG.debug("read SystemConfiguration '{}' (id = {}), format = '{}'", name,
                        id);
                sysConfig = new SystemConfiguration(id, name, uuid);
            }
            ret = true;
            rs.close();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        con.commit();
        if (closeContext) {
            Context.rollback();
        }
        if (sysConfig != null) {
            sysConfig.readConfig();
            SystemConfiguration.cacheSytemConfig(sysConfig);
        }
    } catch (final SQLException e) {
        throw new CacheReloadException("could not read SystemConfiguration", e);
    } catch (final EFapsException e) {
        throw new CacheReloadException("could not read SystemConfiguration", e);
    } finally {
        if ((con != null) && con.isOpened()) {
            try {
                con.abort();
            } catch (final EFapsException e) {
                throw new CacheReloadException("could not read SystemConfiguration", e);
            }
        }
    }
    return ret;
}