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.efaps.admin.datamodel.AttributeType.java

/**
 * @param _sql      SQL Statement to be executed
 * @param _criteria filter criteria//  w  ww . j  a  v a2  s . com
 * @return true if successful
 * @throws CacheReloadException on error
 */
private static boolean getAttributeTypeFromDB(final String _sql, final Object _criteria)
        throws CacheReloadException {
    boolean ret = false;
    ConnectionResource con = null;
    try {
        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();
                String uuid = rs.getString(3);
                uuid = uuid == null ? null : uuid.trim();

                if (AttributeType.LOG.isDebugEnabled()) {
                    AttributeType.LOG.debug(
                            "read attribute type '" + name + "' " + "(id = " + id + ", uuid = '" + uuid + "')");
                }

                final AttributeType attrType = new AttributeType(id, uuid, name, rs.getString(4).trim(),
                        rs.getString(5).trim());
                if (rs.getInt(6) != 0) {
                    attrType.alwaysUpdate = true;
                }
                if (rs.getInt(7) != 0) {
                    attrType.createUpdate = true;
                }
                AttributeType.cacheAttributeType(attrType);
            }
            ret = true;
            rs.close();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        con.commit();
    } catch (final SQLException e) {
        throw new CacheReloadException("could not read roles", e);
    } catch (final EFapsException e) {
        throw new CacheReloadException("could not read roles", e);
    } finally {
        if (con != null && con.isOpened()) {
            try {
                con.abort();
            } catch (final EFapsException e) {
                throw new CacheReloadException("could not read roles", e);
            }
        }
    }
    return ret;
}

From source file:org.silverpeas.dbbuilder.sql.QueryExecutor.java

public static List<Map<String, Object>> executeLoopQuery(Connection connection, String query,
        Object[] parameters) throws Exception {
    Statement stmt = null;/* w  w  w  . j  av a2  s .c o m*/
    PreparedStatement pstmt = null;
    ArrayList array = new ArrayList();
    ResultSet results = null;
    try {
        if (parameters == null) {
            stmt = connection.createStatement();
            results = stmt.executeQuery(query);
        } else {
            pstmt = connection.prepareStatement(query);
            for (int i = 0; i < parameters.length; i++) {
                pstmt.setObject(i + 1, parameters[i]);
            }
            results = pstmt.executeQuery();
        }
        ResultSetMetaData meta = results.getMetaData();
        // Tant qu'on a des enregistrements dans le result set
        while (results.next()) {
            // Stockage d'un enregistrement
            HashMap<String, Object> h = new HashMap<String, Object>(meta.getColumnCount());
            // Pour chaque colonne du result set
            for (int i = 1; i <= meta.getColumnCount(); i++) {
                Object ob = results.getObject(i);
                h.put(meta.getColumnLabel(i).toUpperCase(), ob);
            }
            array.add(h);
        }
    } catch (SQLException sqlex) {
    } finally {
        DbUtils.closeQuietly(results);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(pstmt);
    }
    return array;
}

From source file:org.rhq.plugins.database.DatabaseQueryUtility.java

private static void bindParameters(PreparedStatement statement, Object... parameters) throws SQLException {
    int i = 1;//www  .  j  ava 2 s . c  o m
    for (Object p : parameters) {
        if (p instanceof String) {
            statement.setString(i++, (String) p);
        } else if (p instanceof Number) {
            statement.setDouble(i++, ((Number) p).doubleValue());
        } else {
            statement.setObject(i++, p);
        }
    }
}

From source file:com.jsmartdb.framework.manager.EntityWhere.java

static void setPreparedStatementWhereValues(PreparedStatement pstmt, boolean containsInnerSelect)
        throws SQLException {
    List<Object> whereBuilderValues = EntityContext.getBuilderValues();
    if (containsInnerSelect) {
        whereBuilderValues.addAll(whereBuilderValues);
    }/*ww  w.  ja  v  a 2s  .c  o m*/
    for (int index = 1; index < whereBuilderValues.size() + 1; index++) {
        pstmt.setObject(index, whereBuilderValues.get(index - 1));
    }
}

From source file:gridool.util.jdbc.JDBCUtils.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with 
 * the given objects.//w  ww. j  a va2 s. co  m
 * 
 * @param params Query replacement parameters; <code>null</code> is a valid value to pass in.
 */
public static void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException {
    if (params == null) {
        return;
    }
    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type.  Oddly, NULL and 
            // OTHER don't work with Oracle's drivers.
            stmt.setNull(i + 1, Types.VARCHAR);
        }
    }
}

From source file:org.intermine.sql.DatabaseUtil.java

/**
 * Set the default value in a column for all values.
 * @param con A connection to the database to use
 * @param tableName the table where update the column
 * @param columnName the column to Update
 * @param newValue the value to update//  ww  w.j  a va 2  s  . c o m
 * @throws SQLException if there is a database problem
 *
 * Note, it is the user's responsibility to ensure the connection given is closed.
 */
public static void updateColumnValue(Connection con, String tableName, String columnName, Object newValue)
        throws SQLException {
    if (DatabaseUtil.columnExists(con, tableName, columnName)) {
        String sql = "UPDATE " + tableName + " SET " + columnName + " = ?";
        PreparedStatement stmt = con.prepareStatement(sql);
        stmt.setObject(1, newValue);
        LOG.info(stmt.toString());
        stmt.executeUpdate();
    }
}

From source file:ca.sqlpower.object.SPVariableHelper.java

/**
 * Helper method that takes a connection and a SQL statement which includes variable and 
 * converts all that in a nifty prepared statement ready for execution, on time for Christmas.
 * @param connection A connection object to use in order to generate the prepared statement.
 * @param sql A SQL string which might include variables.
 * @param variableHelper A {@link SPVariableHelper} object to resolve the variables.
 * @return A {@link PreparedStatement} object ready for execution.
 * @throws SQLException Might get thrown if we cannot generate a {@link PreparedStatement} with the supplied connection.
 *///w  ww.ja  v  a  2s . co m
public static PreparedStatement substituteForDb(Connection connection, String sql,
        SPVariableHelper variableHelper) throws SQLException {

    // Make sure that the registry is ready.
    SPResolverRegistry.init(variableHelper.getContextSource());

    StringBuilder text = new StringBuilder();
    Matcher matcher = varPattern.matcher(sql);
    List<Object> vars = new LinkedList<Object>();

    // First, change all vars to '?' markers.
    int currentIndex = 0;
    while (!matcher.hitEnd()) {
        if (matcher.find()) {
            String variableName = matcher.group(1);
            if (variableName.equals("$")) {
                vars.add("$");
            } else {
                vars.add(variableHelper.resolve(variableName));
            }
            text.append(sql.substring(currentIndex, matcher.start()));
            text.append("?");
            currentIndex = matcher.end();
        }
    }
    text.append(sql.substring(currentIndex));

    // Now generate a prepared statement and inject it's variables.
    PreparedStatement ps = connection.prepareStatement(text.toString());
    for (int i = 0; i < vars.size(); i++) {
        ps.setObject(i + 1, vars.get(i));
    }

    return ps;
}

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

/**
 * oracle query plan? . /*from ww  w.  jav  a 2  s  .  c  o  m*/
 * 
 * @param userDB
 * @param reqQuery
 * @param planTableName
 * @throws Exception
 */
public static void plan(UserDBDAO userDB, RequestQuery reqQuery, String planTableName,
        java.sql.Connection javaConn, String statement_id) throws Exception {

    PreparedStatement pstmt = null;
    try {
        String query = PartQueryUtil.makeExplainQuery(userDB, reqQuery.getSql());
        query = StringUtils.replaceOnce(query, PublicTadpoleDefine.STATEMENT_ID, statement_id);
        query = StringUtils.replaceOnce(query, PublicTadpoleDefine.DELIMITER, planTableName);

        pstmt = javaConn.prepareStatement(query);
        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]);
            }
        }
        pstmt.execute();
    } finally {
        try {
            if (pstmt != null)
                pstmt.close();
        } catch (Exception e) {
        }
    }

}

From source file:org.efaps.admin.datamodel.Status.java

/**
 * @param _sql      SQL Statement to be executed
 * @param _criteria filter criteria//  w ww  . jav a 2  s.c  om
 * @return true if successful
 * @throws CacheReloadException on error
 */
private static boolean getStatusFromDB(final String _sql, final Object _criteria) throws CacheReloadException {
    final boolean ret = false;
    ConnectionResource con = null;
    try {
        final List<Object[]> values = new ArrayList<Object[]>();
        con = Context.getThreadContext().getConnectionResource();
        PreparedStatement stmt = null;
        try {
            stmt = con.getConnection().prepareStatement(_sql);
            stmt.setObject(1, _criteria);
            final ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                values.add(new Object[] { rs.getLong(1), rs.getLong(2), rs.getString(3).trim(),
                        rs.getString(4).trim() });
            }
            rs.close();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        con.commit();
        for (final Object[] row : values) {
            final long id = (Long) row[0];
            final long typeid = (Long) row[1];
            final String key = (String) row[2];

            Status.LOG.debug("read status '{}' (id = {}) + key = {}", typeid, id, key);

            final Type type = Type.get(typeid);
            Status.get(type.getUUID());
        }
    } catch (final SQLException e) {
        throw new CacheReloadException("could not read types", e);
    } catch (final EFapsException e) {
        throw new CacheReloadException("could not read types", e);
    } finally {
        if ((con != null) && con.isOpened()) {
            try {
                con.abort();
            } catch (final EFapsException e) {
                throw new CacheReloadException("could not read types", e);
            }
        }
    }
    return ret;
}

From source file:org.efaps.admin.datamodel.Status.java

/**
 * @param _sql      SQL Statement to be executed
 * @param _criteria filter criteria/*  www  .  j  av  a  2  s .  c  o  m*/
 * @return true if successful
 * @throws CacheReloadException on error
 */
private static boolean getStatusGroupFromDB(final String _sql, final Object _criteria)
        throws CacheReloadException {
    boolean ret = false;
    ConnectionResource con = null;
    try {
        final List<Object[]> values = new ArrayList<Object[]>();
        con = Context.getThreadContext().getConnectionResource();
        PreparedStatement stmt = null;
        try {
            stmt = con.getConnection().prepareStatement(_sql);
            stmt.setObject(1, _criteria);
            final ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                values.add(new Object[] { rs.getLong(1), rs.getLong(2), rs.getString(3).trim(),
                        rs.getString(4).trim() });
            }
            rs.close();
        } finally {
            if (stmt != null) {
                stmt.close();
            }
        }
        con.commit();
        for (final Object[] row : values) {
            final long id = (Long) row[0];
            final long typeid = (Long) row[1];
            final String key = (String) row[2];
            final String desc = (String) row[3];

            Status.LOG.debug("read status '{}' (id = {}) + key = {}", typeid, id, key);

            final Type type = Type.get(typeid);
            final Cache<UUID, StatusGroup> cache = InfinispanCache.get()
                    .<UUID, StatusGroup>getCache(Status.UUIDCACHE4GRP);
            StatusGroup statusGroup;
            if (cache.containsKey(type.getUUID())) {
                statusGroup = cache.get(type.getUUID());
            } else {
                statusGroup = new StatusGroup(type);
            }
            final Status status = new Status(statusGroup, id, key, desc);
            statusGroup.put(status.getKey(), status);
            Status.cacheStatus(status);
            Status.cacheStatusGroup(statusGroup);
            ret = true;
        }

    } catch (final SQLException e) {
        throw new CacheReloadException("could not read types", e);
    } catch (final EFapsException e) {
        throw new CacheReloadException("could not read types", e);
    } finally {
        if ((con != null) && con.isOpened()) {
            try {
                con.abort();
            } catch (final EFapsException e) {
                throw new CacheReloadException("could not read types", e);
            }
        }
    }
    return ret;
}