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.PostgreSqlEngine.java

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

    int i = 1;/*from   w ww .java2s.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.setClob(i, sr);
                    // postrgresql driver des not have setClob implemented
                    ps.setString(i, (String) val);
                } 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:org.openbravo.client.kernel.reference.UIDefinition.java

/**
 * Computes properties to initialize and set the field in a Smartclient form. This can be the
 * default value or the sets of values in the valuemap.
 * //from   ww w  . j av  a  2 s .c  o m
 * @param field
 *          the field for which the information should be computed.
 * @param getValueFromSession
 * @return a JSONObject string which is used to initialize the formitem.
 */
public String getFieldProperties(Field field, boolean getValueFromSession) {
    String columnValue = "";
    RequestContext rq = RequestContext.get();
    if (getValueFromSession) {
        String inpColumnName = null;
        if (field.getProperty() != null && !field.getProperty().isEmpty()) {
            inpColumnName = "inp" + "_propertyField_"
                    + Sqlc.TransformaNombreColumna(field.getName()).replace(" ", "") + "_"
                    + field.getColumn().getDBColumnName();
        } else {
            inpColumnName = "inp" + Sqlc.TransformaNombreColumna(field.getColumn().getDBColumnName());
        }
        columnValue = rq.getRequestParameter(inpColumnName);
    } else {
        if (field.getColumn().getDBColumnName().equalsIgnoreCase("documentno")
                || (field.getColumn().isUseAutomaticSequence()
                        && field.getColumn().getDBColumnName().equals("Value"))) {
            String docTypeTarget = rq
                    .getRequestParameter("inp" + Sqlc.TransformaNombreColumna("C_DocTypeTarget_ID"));
            if (docTypeTarget == null)
                docTypeTarget = "";
            String docType = rq.getRequestParameter("inp" + Sqlc.TransformaNombreColumna("C_DocType_ID"));
            if (docType == null)
                docType = "";
            columnValue = "<" + Utility.getDocumentNo(new DalConnectionProvider(false),
                    rq.getVariablesSecureApp(), field.getTab().getWindow().getId(),
                    field.getColumn().getTable().getDBTableName(), docTypeTarget, docType, false, false) + ">";
        } else {
            String defaultS = field.getColumn().getDefaultValue();
            if (defaultS == null || defaultS.equals("\"\"")) {
                defaultS = "";
            }
            if (defaultS.equalsIgnoreCase("@#Date@")) {
                return setNOWDefault();
            } else if (!defaultS.startsWith("@SQL=")) {
                columnValue = Utility.getDefault(new DalConnectionProvider(false), rq.getVariablesSecureApp(),
                        field.getColumn().getDBColumnName(), defaultS, field.getTab().getWindow().getId(), "");
            } else {
                ArrayList<String> params = new ArrayList<String>();
                String sql = parseSQL(defaultS, params);
                int indP = 1;
                PreparedStatement ps = null;
                try {
                    ps = OBDal.getInstance().getConnection(false).prepareStatement(sql);
                    for (String parameter : params) {
                        String value = "";
                        if (parameter.substring(0, 1).equals("#")) {
                            value = Utility.getContext(new DalConnectionProvider(false),
                                    RequestContext.get().getVariablesSecureApp(), parameter,
                                    field.getTab().getWindow().getId());
                        } else {
                            String fieldId = "inp" + Sqlc.TransformaNombreColumna(parameter);
                            if (RequestContext.get().getParameterMap().containsKey(fieldId)) {
                                value = RequestContext.get().getRequestParameter(fieldId);
                            }
                            if (value == null || value.equals("")) {
                                value = Utility.getContext(new DalConnectionProvider(false),
                                        RequestContext.get().getVariablesSecureApp(), parameter,
                                        field.getTab().getWindow().getId());
                            }
                        }
                        ps.setObject(indP++, value);
                    }
                    ResultSet rs = ps.executeQuery();
                    if (rs.next()) {
                        columnValue = getValueFromSQLDefault(rs);
                    }
                } catch (Exception e) {
                    log.error("Error computing default value for field " + field.getName() + " of tab "
                            + field.getTab().getName(), e);
                } finally {
                    try {
                        ps.close();
                    } catch (SQLException e) {
                        // won't happen
                    }
                }
            }
        }
    }
    if (columnValue == null || columnValue.equals("null")) {
        columnValue = "";
    }
    JSONObject jsnobject = new JSONObject();
    try {
        jsnobject.put("value", createFromClassicString(columnValue));
        jsnobject.put("classicValue", columnValue);
    } catch (JSONException e) {
        log.error("Couldn't get field property value for column " + field.getColumn().getDBColumnName());
    }
    return jsnobject.toString();
}

From source file:edu.umd.cs.marmoset.modelClasses.TestOutcome.java

/**
* Populated a prepared statement starting at a given index with all of the fields
* of this model class.//from  www .  j a va2  s . co  m
* @param stmt the PreparedStatement
* @param index the starting index
* @return the index of the next open slot in the prepared statement
* @throws SQLException
*/
int putValues(PreparedStatement stmt, int index) throws SQLException {
    limitSizes();
    stmt.setInt(index++, getTestRunPK());
    stmt.setString(index++, getTestType().name());
    stmt.setString(index++, getTestNumber());
    stmt.setString(index++, getOutcome());
    stmt.setInt(index++, getPointValue());
    stmt.setString(index++, getTestName());
    stmt.setString(index++, getShortTestResult());
    stmt.setString(index++, longTestResult);
    stmt.setString(index++, getExceptionClassName());
    stmt.setString(index++, (getCoarsestCoverageLevel() != null) ? getCoarsestCoverageLevel().toString()
            : CoverageLevel.NONE.toString());
    stmt.setBoolean(index++, getExceptionSourceCoveredElsewhere());
    stmt.setObject(index++, getDetails());
    stmt.setInt(index++, getExecutionTimeMillis());
    return index;
}

From source file:com.prosnav.oms.dao.CustDao.java

public int insertAndGetKey(final String sql, final Object[] o, String id) {
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jt.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {

            // String sql_sms = "insert into
            // sms(title,content,date_s,form,sffs,by1,by2,by3) values
            // (?,?,'"+dates+"',?,?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < o.length; i++) {
                ps.setObject(i + 1, o[i]);
            }/*from w  w w.  j av  a2s . co  m*/

            return ps;
        }
    }, keyHolder);
    Map<String, Object> generatedId = keyHolder.getKeyList().get(0);
    Integer com_id = (Integer) generatedId.get(id);
    // Long generatedId = keyHolder.getKey().longValue();
    // int a =Integer.parseInt(o_id);
    return com_id;
}

From source file:org.hxzon.util.db.springjdbc.StatementCreatorUtils.java

/**
 * Set the specified PreparedStatement parameter to null,
 * respecting database-specific peculiarities.
 *///from  w w w . j  a  v a 2 s . co  m
private static void setNull(PreparedStatement ps, int paramIndex, int sqlType, String typeName)
        throws SQLException {
    if (sqlType == SqlTypeValue.TYPE_UNKNOWN) {
        boolean useSetObject = false;
        Integer sqlTypeToUse = null;
        DatabaseMetaData dbmd = null;
        String jdbcDriverName = null;
        boolean checkGetParameterType = !shouldIgnoreGetParameterType;
        if (checkGetParameterType && !driversWithNoSupportForGetParameterType.isEmpty()) {
            try {
                dbmd = ps.getConnection().getMetaData();
                jdbcDriverName = dbmd.getDriverName();
                checkGetParameterType = !driversWithNoSupportForGetParameterType.contains(jdbcDriverName);
            } catch (Throwable ex) {
                logger.debug("Could not check connection metadata", ex);
            }
        }
        if (checkGetParameterType) {
            try {
                sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex);
            } catch (Throwable ex) {
                if (logger.isDebugEnabled()) {
                    logger.debug(
                            "JDBC 3.0 getParameterType call not supported - using fallback method instead: "
                                    + ex);
                }
            }
        }
        if (sqlTypeToUse == null) {
            // JDBC driver not compliant with JDBC 3.0 -> proceed with database-specific checks
            sqlTypeToUse = Types.NULL;
            try {
                if (dbmd == null) {
                    dbmd = ps.getConnection().getMetaData();
                }
                if (jdbcDriverName == null) {
                    jdbcDriverName = dbmd.getDriverName();
                }
                if (checkGetParameterType) {
                    driversWithNoSupportForGetParameterType.add(jdbcDriverName);
                }
                String databaseProductName = dbmd.getDatabaseProductName();
                if (databaseProductName.startsWith("Informix")
                        || jdbcDriverName.startsWith("Microsoft SQL Server")) {
                    useSetObject = true;
                } else if (databaseProductName.startsWith("DB2") || jdbcDriverName.startsWith("jConnect")
                        || jdbcDriverName.startsWith("SQLServer")
                        || jdbcDriverName.startsWith("Apache Derby")) {
                    sqlTypeToUse = Types.VARCHAR;
                }
            } catch (Throwable ex) {
                logger.debug("Could not check connection metadata", ex);
            }
        }
        if (useSetObject) {
            ps.setObject(paramIndex, null);
        } else {
            ps.setNull(paramIndex, sqlTypeToUse);
        }
    } else if (typeName != null) {
        ps.setNull(paramIndex, sqlType, typeName);
    } else {
        ps.setNull(paramIndex, sqlType);
    }
}

From source file:edu.ku.brc.specify.config.init.secwiz.UserPanel.java

/**
 * //from w  ww. j av a  2s  . co  m
 */
private void saveUserData() {
    boolean hasErrors = false;
    DBMSUserMgr mgr = DBMSUserMgr.getInstance();

    PreparedStatement pStmtSp = null;
    PreparedStatement pStmtAg = null;
    try {
        String dbUserName = properties.getProperty("dbUserName");
        String dbPassword = properties.getProperty("dbPassword");
        String hostName = properties.getProperty("hostName");

        if (mgr.connect(dbUserName, dbPassword, hostName, databaseName)) {
            pStmtSp = mgr.getConnection()
                    .prepareStatement("UPDATE specifyuser SET EMail=?, Password=? WHERE SpecifyUserID = ?");
            pStmtAg = mgr.getConnection().prepareStatement("UPDATE agent SET Email=? WHERE AgentID = ?");

            for (UserData ud : userModel.getUserData()) {
                if (ud.isChanged()) {
                    if (StringUtils.isNotEmpty(ud.getEmail())) {
                        pStmtSp.setString(1, ud.getEmail());
                    } else {
                        pStmtSp.setObject(1, null);
                    }
                    pStmtSp.setString(2, ud.getPassword());
                    pStmtSp.setInt(3, ud.getId());
                    int rv = pStmtSp.executeUpdate();
                    if (rv == 1) {
                        ud.setChanged(false);
                    } else {
                        System.err.println("Error " + pStmtSp.getWarnings());
                    }

                    if (StringUtils.isNotEmpty(ud.getEmail())) {
                        String sql = String.format(
                                "SELECT AgentID FROM agent WHERE SpecifyUserID = %d AND (Email IS NULL OR Email <> '%s')",
                                ud.getId(), ud.getEmail());
                        Vector<Integer> agentIds = BasicSQLUtils.queryForInts(mgr.getConnection(), sql);
                        for (Integer agentId : agentIds) {
                            pStmtAg.setString(1, ud.getEmail());
                            pStmtAg.setInt(2, agentId);
                        }

                        if (pStmtSp.executeUpdate() != 1) {
                            // error
                            hasErrors = true;
                        }
                    }
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (pStmtSp != null)
                pStmtSp.close();
            if (pStmtAg != null)
                pStmtAg.close();
            mgr.close();
        } catch (SQLException e) {
        }
    }

    if (!hasErrors) {
        userModel.setPwdChanged(false);
        userModel.setChanged(false);
        saveBtn.setEnabled(false);
        mkKeysBtn.setEnabled(false);

        if (changedEMail) {
            changedEMail = false;
            SwingUtilities.invokeLater(new Runnable() {
                @Override
                public void run() {
                    UIRegistry.loadAndPushResourceBundle("specifydbsetupwiz");
                    UIRegistry.showLocalizedMsg(JOptionPane.INFORMATION_MESSAGE, "SEC_EML_TITLE",
                            "SEC_EML_MSG");
                    UIRegistry.popResourceBundle();
                }
            });
        }
    }
}

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

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

    int i = 1;//from www.java  2s  .  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()) {
            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.setClob(i, sr);
                } else {
                    throw new DatabaseEngineException("Cannot convert " + val.getClass().getSimpleName()
                            + " to String. CLOB columns only accept Strings.");
                }
                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.nuxeo.ecm.core.storage.sql.Mapper.java

/**
 * Deletes a fragment, and returns {@code true} if there really were rows
 * deleted.//from  w w w .  jav a  2  s .  c  om
 */
protected boolean deleteFragment(String tableName, Serializable id) throws SQLException {
    String sql = sqlInfo.getDeleteSql(tableName);
    if (isLogEnabled()) {
        logSQL(sql, Collections.singletonList(id));
    }
    PreparedStatement ps = connection.prepareStatement(sql);
    try {
        ps.setObject(1, id);
        int count = ps.executeUpdate();
        logCount(count);
        return count > 0;
    } finally {
        closePreparedStatement(ps);
    }
}

From source file:com.feedzai.commons.sql.abstraction.engine.impl.MySqlEngine.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 a  va2  s . co 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()) {
            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.setClob(i, sr);
                } else {
                    throw new DatabaseEngineException("Cannot convert " + val.getClass().getSimpleName()
                            + " to String. CLOB columns only accept Strings.");
                }
                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 variable s to database", ex);
        }

        i++;
    }

    return i - 1;
}

From source file:org.nuxeo.ecm.core.storage.sql.Mapper.java

/**
 * Gets the root id for a given repository, if registered.
 *
 * @param repositoryId the repository id, usually 0
 * @return the root id, or null if not found
 *///from ww w .j a  v  a 2 s . co m
protected Serializable getRootId(Serializable repositoryId) throws StorageException {
    String sql = sqlInfo.getSelectRootIdSql();
    try {
        if (isLogEnabled()) {
            logSQL(sql, Collections.singletonList(repositoryId));
        }
        PreparedStatement ps = connection.prepareStatement(sql);
        try {
            ps.setObject(1, repositoryId);
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                if (isLogEnabled()) {
                    log("  -> (none)");
                }
                return null;
            }
            Column column = sqlInfo.getSelectRootIdWhatColumn();
            Serializable id = column.getFromResultSet(rs, 1);
            if (isLogEnabled()) {
                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 {
            closePreparedStatement(ps);
        }
    } catch (SQLException e) {
        checkConnectionReset(e);
        throw new StorageException("Could not select: " + sql, e);
    }
}