Example usage for java.sql PreparedStatement 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.entrystore.rowstore.store.impl.PgDataset.java

/**
 * Initializes the object by loading all information from the database.
 *///from   w  w  w .j a  v a  2s  .  co m
private void initFromDb() {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = rowstore.getConnection();
        stmt = conn.prepareStatement("SELECT * FROM " + PgDatasets.TABLE_NAME + " WHERE id = ?");
        PGobject uuid = new PGobject();
        uuid.setType("uuid");
        uuid.setValue(getId());
        stmt.setObject(1, uuid);
        log.info("Loading dataset " + getId() + " from database");
        log.debug("Executing: " + stmt);
        rs = stmt.executeQuery();
        if (rs.next()) {
            this.status = rs.getInt("status");
            this.created = rs.getTimestamp("created");
            this.dataTable = rs.getString("data_table");
        } else {
            throw new IllegalStateException("Unable to initialize Database object from database");
        }
    } catch (SQLException e) {
        SqlExceptionLogUtil.error(log, e);
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                SqlExceptionLogUtil.error(log, e);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                SqlExceptionLogUtil.error(log, e);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                SqlExceptionLogUtil.error(log, e);
            }
        }
    }
}

---

From source file:cn.labthink.ReadAccess330.java

private void generateCellData(Integer testID, int cellno) throws SQLException {
    Vector columns = null;//from  www  .  j av a  2s. c o  m
    Vector data = null;

    data = new Vector();
    columns = new Vector();

    //                PreparedStatement st = con.prepareStatement("SELECT * FROM RESULTS WHERE TESTID = ? AND CELLID = ? ORDER BY RESULTID");
    PreparedStatement st = conn.prepareStatement(
            "SELECT TIMESPAN,TESTTEMPERATURE,ABOVEHUMIDITY,BELOWHUMIDITY,ABOVEPRESSURE,BELOWPRESSURE,FLOWRATE,OPPM,WPPM,OTR,WVTR,SENSORTEMP,AMBTEMP,TESTTYPE,AMTEMPERATURE,AMHUMIDITY,PRODUCETIME,COFFICIENT  FROM RESULTS WHERE TESTID = ? AND CELLID = ? ORDER BY RESULTID");
    st.setInt(1, testID);
    st.setObject(2, cellno);

    rs = st.executeQuery();

    ResultSetMetaData md = rs.getMetaData();
    int columnCount = md.getColumnCount();
    //store column names 
    for (int i = 1; i <= columnCount; i++) {
        columns.add(md.getColumnName(i));
        //                System.out.println(md.getColumnName(i));
    }
    columns.ensureCapacity(columnCount);

    Vector row;
    while (rs.next()) {

        row = new Vector(columnCount);
        for (int i = 1; i <= columnCount; i++) {
            row.add(rs.getString(i));
            //                    System.out.print(rs.getString(i));
            //                    System.out.print(",");
        }
        //                System.out.print("\r\n");
        data.add(row);

        //Debugging    
    }

    outputexcelCell(columns, data, cellno);
}

---

From source file:org.adempiere.pipo2.IDFinder.java

/**
 * Get ID from column value for a table.
 *
 * @param tableName/*  w w w.  java2s.c  o m*/
 * @param columName
 * @param value
 * @param AD_Client_ID
 * @param trxName
 */
public static int findIdByColumn(String tableName, String columnName, Object value, int AD_Client_ID,
        boolean ignorecase, String trxName) {
    int id = 0;

    if (value == null)
        return id;

    //construct cache key
    StringBuilder key = new StringBuilder();
    key.append(tableName).append(".").append(columnName).append("=").append(value.toString())
            .append(" AND AD_Client_ID=").append(AD_Client_ID);

    //check cache
    if (idCache.containsKey(key.toString()))
        return idCache.get(key.toString());

    StringBuffer sqlB = new StringBuffer("SELECT ").append(tableName).append("_ID FROM ").append(tableName)
            .append(" WHERE ").append(" AD_Client_ID IN (0, ?) AND ");

    Object[] params = null;
    String[] columns = null;
    if (columnName.indexOf(",") > 0 && value instanceof String) {
        columns = columnName.split("[,]");
        String[] values = ((String) value).split("[,]");
        List<Object> paramList = new ArrayList<Object>();
        for (int i = 0; i < columns.length; i++) {
            if (i > 0)
                sqlB.append(" AND ");
            if (ignorecase) {
                sqlB.append("UPPER(").append(columns[i]).append(")=? ");
            } else {
                sqlB.append(columns[i]).append("=? ");
            }
            try {
                byte[] bytes = Hex.decodeHex(values[i].toCharArray());
                String s = new String(bytes, "UTF-8");
                if (ignorecase) {
                    paramList.add(s.toUpperCase());
                } else {
                    paramList.add(s);
                }
            } catch (DecoderException e) {
                throw new RuntimeException(e);
            } catch (UnsupportedEncodingException e) {
                throw new RuntimeException(e);
            }
        }
        params = paramList.toArray();
    } else {
        if (ignorecase && value != null && value instanceof String) {
            sqlB.append("UPPER(").append(columnName).append(") =? ");
            params = new Object[] { ((String) value).toUpperCase() };
        } else {
            sqlB.append(columnName).append(" =? ");
            params = new Object[] { value };
        }
        columns = new String[] { columnName };
    }

    sqlB.append(" Order By AD_Client_ID Desc, ").append(tableName).append("_ID");

    MTable table = MTable.get(Env.getCtx(), tableName);
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sqlB.toString(), trxName);
        pstmt.setInt(1, AD_Client_ID);
        for (int i = 0; i < params.length; i++) {
            Object param = params[i];
            if (param instanceof String) {
                String s = (String) param;
                MColumn column = table.getColumn(columns[i]);
                if (column.getAD_Reference_ID() == DisplayType.Amount
                        || column.getAD_Reference_ID() == DisplayType.Number
                        || column.getAD_Reference_ID() == DisplayType.CostPrice
                        || column.getAD_Reference_ID() == DisplayType.Quantity)
                    pstmt.setBigDecimal(i + 2, new BigDecimal(s));
                else if (column.getAD_Reference_ID() == DisplayType.Date
                        || column.getAD_Reference_ID() == DisplayType.DateTime)
                    pstmt.setTimestamp(i + 2, Timestamp.valueOf(s));
                else if (column.getAD_Reference_ID() == DisplayType.Integer)
                    pstmt.setInt(i + 2, Integer.parseInt(s));
                else
                    pstmt.setString(i + 2, s);
            } else if (param instanceof Integer) {
                pstmt.setInt(i + 2, ((Integer) param).intValue());
            } else {
                pstmt.setObject(i + 2, param);
            }
        }

        rs = pstmt.executeQuery();
        if (rs.next())
            id = rs.getInt(1);
    } catch (Exception e) {
        throw new DatabaseAccessException(e);
    } finally {
        DB.close(rs, pstmt);
    }

    //update cache
    if (id > 0)
        idCache.put(key.toString(), id);

    return id;
}

---

From source file:org.entrystore.rowstore.store.impl.PgDataset.java

/**
 * @see Dataset#setStatus(int)/*from   w  w  w  . j  av  a2s .co m*/
 */
@Override
public void setStatus(int status) {
    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = rowstore.getConnection();
        conn.setAutoCommit(true);
        stmt = conn.prepareStatement("UPDATE " + PgDatasets.TABLE_NAME + " SET status = ? WHERE id = ?");
        stmt.setInt(1, status);
        PGobject uuid = new PGobject();
        uuid.setType("uuid");
        uuid.setValue(id);
        stmt.setObject(2, uuid);
        log.info("Setting status of " + getId() + " to " + EtlStatus.toString(status) + "(" + status + ")");
        log.debug("Executing: " + stmt);
        stmt.executeUpdate();
    } catch (SQLException e) {
        SqlExceptionLogUtil.error(log, e);
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                SqlExceptionLogUtil.error(log, e);
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                SqlExceptionLogUtil.error(log, e);
            }
        }
    }

    this.status = status;
}

---

From source file:com.mmnaseri.dragonfly.fluent.impl.AbstractSelectQueryFinalizer.java

private <H> List<Map<Mapping, Object>> execute(SelectQueryExecution<E, H> selection) {
    final Connection connection = session.getConnection();
    final PreparedStatement preparedStatement;
    try {/*from   w w w. j a v  a 2s  .  c o m*/
        final String sql = selection.getSql() + ";";
        LogFactory.getLog(Statement.class).info("Preparing statement: " + sql);
        preparedStatement = connection.prepareStatement(sql);
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to get a prepared statement from the database", e);
    }
    for (ParameterDescriptor descriptor : selection.getParameters()) {
        try {
            if (descriptor.getValue() == null) {
                preparedStatement.setNull(descriptor.getIndex(), descriptor.getSqlType());
            } else {
                preparedStatement.setObject(descriptor.getIndex(), descriptor.getValue());
            }
        } catch (SQLException e) {
            throw new StatementPreparationException(
                    "Failed to prepare statement for parameter " + descriptor.getIndex(), e);
        }
    }
    final ResultSet resultSet;
    final ResultSetMetaData metaData;
    try {
        resultSet = preparedStatement.executeQuery();
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to retrieve the results from the data source", e);
    }
    try {
        metaData = resultSet.getMetaData();
    } catch (SQLException e) {
        throw new DatabaseNegotiationException("Failed to get result set metadata for query", e);
    }
    final ArrayList<Map<Mapping, Object>> result = new ArrayList<Map<Mapping, Object>>();
    while (true) {
        try {
            if (!resultSet.next()) {
                break;
            }
            final HashMap<Mapping, Object> map = new HashMap<Mapping, Object>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                map.put(new ImmutableMapping(metaData.getTableName(i), metaData.getColumnName(i),
                        metaData.getColumnLabel(i)), resultSet.getObject(i));
            }
            result.add(map);
        } catch (SQLException e) {
            throw new DatabaseNegotiationException("Failed to get the next row", e);
        }

    }
    return result;
}

---

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * select ??  /*from   ww w.ja  v  a  2s .  co m*/
 * 
 * @param reqQuery
 * @exception
 */
private static Object runSQLOther(final UserDBDAO userDB, String strQuery, final List<Object> listParam)
        throws SQLException, Exception {

    // is tajo
    if (DBDefine.TAJO_DEFAULT == userDB.getDBDefine()) {
        logger.error("Not support TAJO.");
    } else {

        java.sql.Connection javaConn = null;
        PreparedStatement prepareStatement = null;
        try {
            SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
            javaConn = client.getDataSource().getConnection();
            prepareStatement = javaConn.prepareStatement(strQuery);

            // TODO mysql?  https://github.com/hangum/TadpoleForDBTools/issues/3  ?   create table ? ?? '(' ?? ? ?? .
            if (userDB.getDBDefine() == DBDefine.MYSQL_DEFAULT
                    || userDB.getDBDefine() == DBDefine.MARIADB_DEFAULT) {
                final String checkSQL = strQuery.trim().toUpperCase();
                if (StringUtils.startsWithIgnoreCase(checkSQL, "CREATE TABLE")) { //$NON-NLS-1$
                    strQuery = StringUtils.replaceOnce(strQuery, "(", " ("); //$NON-NLS-1$ //$NON-NLS-2$
                }
            }

            //            // hive executeUpdate() ? . 13.08.19-hangum
            //            if(userDB.getDBDefine() == DBDefine.HIVE_DEFAULT | 
            //               userDB.getDBDefine() == DBDefine.HIVE2_DEFAULT 
            //            ) { 
            //               return prepareStatement.execute(strQuery);
            //            } else {

            for (int i = 0; i < listParam.size(); i++) {
                Object objParam = listParam.get(i);
                prepareStatement.setObject(i + 1, objParam);
            }

            return prepareStatement.executeUpdate();

        } finally {
            try {
                prepareStatement.close();
            } catch (Exception e) {
            }
        }
    } // end which db

    return false;
}

---

From source file:org.apache.jackrabbit.core.util.db.Oracle10R1ConnectionHelper.java

/**
 * Wraps any input-stream parameters in temporary blobs and frees these again after the statement
 * has been executed./*from  w  ww  .j a v a2 s . com*/
 * 
 * {@inheritDoc}
 */
@Override
protected PreparedStatement execute(PreparedStatement stmt, Object[] params) throws SQLException {
    List<Blob> tmpBlobs = new ArrayList<Blob>();
    try {
        for (int i = 0; params != null && i < params.length; i++) {
            Object p = params[i];
            if (p instanceof StreamWrapper) {
                StreamWrapper wrapper = (StreamWrapper) p;
                Blob tmp = createTemporaryBlob(stmt.getConnection(), wrapper.getStream());
                tmpBlobs.add(tmp);
                stmt.setBlob(i + 1, tmp);
            } else if (p instanceof InputStream) {
                Blob tmp = createTemporaryBlob(stmt.getConnection(), (InputStream) p);
                tmpBlobs.add(tmp);
                stmt.setBlob(i + 1, tmp);
            } else {
                stmt.setObject(i + 1, p);
            }
        }
        stmt.execute();
        return stmt;
    } catch (Exception e) {
        throw new SQLException(e.getMessage());
    } finally {
        for (Blob blob : tmpBlobs) {
            try {
                freeTemporaryBlob(blob);
            } catch (Exception e) {
                log.warn("Could not close temporary blob", e);
            }
        }
    }
}

---

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

private boolean runDelete(Connection connection, String query, Object keys[]) {
    PreparedStatement statement = null;
    try {/*w w  w  . jav  a  2  s  .  c  o  m*/
        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]);
        }
        int rows = statement.executeUpdate();
        if ((ExecutionPathDebugLog.isDebugEnabled) && (log.isDebugEnabled())) {
            log.debug("Delete 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

/**
 * Delete all rows with the keys matching the subset of all keys, returning true if deleted or false if no row found to delete.
 * @param connection db connection/*from   ww  w .j  a  v a2s . c o  m*/
 * @param keys key values
 * @throws StoreExceptionDBRel failed operation
 */
public void deleteValueSubkeyed(Connection connection, Object keys[]) {
    StringBuilder builder = new StringBuilder();
    builder.append("delete from ");
    builder.append(tableName);
    builder.append(" where ");
    String 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 + "' keys '" + print(keys) + "'");
        }
        statement = connection.prepareStatement(query);
        for (int i = 0; i < keys.length; i++) {
            statement.setObject(i + 1, keys[i]);
        }
        int rows = statement.executeUpdate();
        if ((ExecutionPathDebugLog.isDebugEnabled) && (log.isDebugEnabled())) {
            log.debug("Deleted yielded " + rows + " rows");
        }
    } 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 value returning null if not found or the value (which can also be null).
 * @param connection db connection/*w  ww. j a va 2  s.  c o  m*/
 * @param keys to read
 * @return value
 * @throws StoreExceptionDBRel failed operation
 */
public Object[] readValue(Connection connection, Object[] keys) {
    PreparedStatement statement = null;
    try {
        if ((ExecutionPathDebugLog.isDebugEnabled) && (log.isDebugEnabled())) {
            log.debug("Executing query '" + readSQL + "' for keys '" + print(keys) + "'");
        }
        statement = connection.prepareStatement(readSQL);
        for (int i = 0; i < keys.length; i++) {
            statement.setObject(i + 1, keys[i]);
        }

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

        Object[] row = new Object[valueFieldNames.length];
        for (int i = 0; i < valueFieldNames.length; i++) {
            row[i] = DBUtil.getValue(rs, i + 1, valueTypes[i]);
        }
        return row;
    } catch (SQLException ex) {
        String message = "Failed to invoke : " + readSQL + " :" + 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) {
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• »