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.accada.epcis.repository.query.QueryOperationsBackendSQL.java

private PreparedStatement prepareSimpleEventQuery(final QueryOperationsSession session,
        SimpleEventQueryDTO seQuery) throws SQLException, ImplementationExceptionResponse {

    StringBuilder sqlSelectFrom;// ww  w  .  ja  v a 2  s. com
    StringBuilder sqlWhereClause = new StringBuilder(" WHERE 1");
    List<Object> sqlParams = new ArrayList<Object>();

    String eventType = seQuery.getEventType();
    if (EpcisConstants.AGGREGATION_EVENT.equals(eventType)) {
        sqlSelectFrom = new StringBuilder(SQL_SELECT_FROM_AGGREGATIONEVENT);
    } else if (EpcisConstants.OBJECT_EVENT.equals(eventType)) {
        sqlSelectFrom = new StringBuilder(SQL_SELECT_FROM_OBJECTEVENT);
    } else if (EpcisConstants.QUANTITY_EVENT.equals(eventType)) {
        sqlSelectFrom = new StringBuilder(SQL_SELECT_FROM_QUANTITYEVENT);
    } else if (EpcisConstants.TRANSACTION_EVENT.equals(eventType)) {
        sqlSelectFrom = new StringBuilder(SQL_SELECT_FROM_TRANSACTIONEVENT);
    } else {
        String msg = "Unknown event type: " + eventType;
        LOG.error(msg);
        ImplementationException ie = new ImplementationException();
        ie.setReason(msg);
        throw new ImplementationExceptionResponse(msg, ie);
    }

    boolean joinedEpcs = false;
    boolean joinedExtensions = false;
    boolean joinedBizTransacitions = false;

    // construct the SQL query dynamically
    List<EventQueryParam> eventQueryParams = seQuery.getEventQueryParams();
    for (EventQueryParam queryParam : eventQueryParams) {
        String eventField = queryParam.getEventField();
        Operation op = queryParam.getOp();
        Object value = queryParam.getValue();

        // check if we need to do any JOINs
        if ("epcList".equals(eventField) || "childEPCs".equals(eventField) || "anyEPC".equals(eventField)) {
            // we have a query on EPCs, so we need to join the appropriate
            // "_EPCs" table
            if (!joinedEpcs) {
                sqlSelectFrom.append(" JOIN event_").append(eventType).append("_EPCs AS epc");
                sqlSelectFrom.append(" ON event_").append(eventType).append(".id=epc.event_id");
                joinedEpcs = true;
            }
            // update the event field to search in
            eventField = "epc.epc";
        } else if (eventField.startsWith("extension")) {
            // we have a query on an extension field, so we need to join the
            // appropriate "_extensions" table
            if (!joinedExtensions) {
                sqlSelectFrom.append(" JOIN event_").append(eventType).append("_extensions AS extension");
                sqlSelectFrom.append(" ON event_").append(eventType).append(".id=extension.event_id");
                joinedExtensions = true;
            }
        } else if (eventField.startsWith("bizTrans")) {
            // we have a query on business transactions, so we need to join
            // the
            // appropriate "_bizTrans" and "bizTransList" tables
            if (!joinedBizTransacitions) {
                sqlSelectFrom.append(" JOIN event_").append(eventType).append("_bizTrans AS bizTransList");
                sqlSelectFrom.append(" ON event_").append(eventType).append(".id=bizTransList.event_id");
                sqlSelectFrom.append(" JOIN BizTransaction ON bizTransList.bizTrans_id=BizTransaction.id");
                sqlSelectFrom.append(" JOIN voc_BizTrans AS bizTrans ON BizTransaction.bizTrans=bizTrans.id");
                sqlSelectFrom.append(
                        " JOIN voc_BizTransType AS bizTransType ON BizTransaction.type=bizTransType.id");
                joinedBizTransacitions = true;
            }
            if ("bizTransList.bizTrans".equals(eventField)) {
                eventField = "bizTrans";
            } else if ("bizTransList.type".equals(eventField)) {
                eventField = "bizTransType";
            }
        } else if (eventField.endsWith(".attribute")) {
            String attrTable = attributeTablenameMap.get(eventField);
            if (attrTable != null) {
                String vocAlias = eventField.substring(0, eventField.indexOf("."));
                sqlSelectFrom.append(" JOIN ").append(attrTable);
                sqlSelectFrom.append(" ON ").append(attrTable).append(".id=").append(vocAlias).append(".id");
                eventField = attrTable + ".attribute";
            }
        } else if (eventField.endsWith(".attribute.value")) {
            String attrTable = attributeTablenameMap.get(eventField.substring(0, eventField.length() - 6));
            eventField = attrTable + ".value";
        }
        String vocField = vocabularyTypeMap.get(eventField);
        if (vocField != null) {
            eventField = vocField;
        }

        // now check the provided event field, operation, and value and
        // update the SQL strings accordingly
        if (value == null && op == Operation.EXISTS) {
            if (eventField.startsWith("epc") || eventField.startsWith("bizTransList")) {
                // EXISTS-query already coped with by JOIN - nothing to do
            } else {
                // check if the given event field exists
                sqlWhereClause.append(" AND ?");
                sqlParams.add(eventField);
            }
        } else if (value != null) {
            if (value instanceof List) {
                // we have a multi-value query parameter, e.g. action, EPCs,
                // vocabulary types
                List<?> paramValues = (List<?>) value;
                if (!paramValues.isEmpty()) {
                    if (op == Operation.MATCH || op == Operation.WD) {
                        // this results in a SQL "LIKE" query
                        sqlWhereClause.append(" AND (0");
                        for (Object paramValue : paramValues) {
                            String strValue = (String) paramValue;

                            // MATCH-params might be 'pure identity' EPC
                            // patterns
                            if (op == Operation.MATCH && !eventField.startsWith("epcClass")) {
                                if (strValue.startsWith("urn:epc:idpat:")) {
                                    strValue = strValue.replace("urn:epc:idpat:", "urn:epc:id:");
                                }
                            }
                            strValue = strValue.replaceAll("\\*", "%");

                            sqlWhereClause.append(" OR ").append(eventField).append(" LIKE ?");
                            sqlParams.add(strValue);
                            if (seQuery.isAnyEpc() && "epc.epc".equals(eventField)) {
                                sqlWhereClause.append(" OR parentID LIKE ?");
                                sqlParams.add(strValue);
                            }
                        }
                        sqlWhereClause.append(")");
                    } else {
                        // this results in a SQL "IN" query
                        sqlWhereClause.append(" AND ").append(eventField).append(" IN (?");
                        sqlParams.add(paramValues.get(0));
                        for (int i = 1; i < paramValues.size(); i++) {
                            sqlWhereClause.append(",?");
                            sqlParams.add(paramValues.get(i));
                        }
                        sqlWhereClause.append(")");
                    }
                }
            } else {
                // we have a single-value parameter, e.g. eventTime,
                // recordTime, parentID
                String sqlOp = operationMap.get(op);
                sqlWhereClause.append(" AND ").append(eventField).append(" ").append(sqlOp).append(" ?");
                sqlParams.add(value);
            }
        }
    }

    // construct the final SQL query string
    StringBuilder sql = sqlSelectFrom.append(sqlWhereClause);
    if (seQuery.getOrderBy() != null) {
        sql.append(" ORDER BY ").append(seQuery.getOrderBy());
        if (seQuery.getOrderDirection() != null) {
            sql.append(" ").append(seQuery.getOrderDirection().name());
        }
    }
    if (seQuery.getLimit() != -1) {
        sql.append(" LIMIT ").append(seQuery.getLimit());
    } else if (seQuery.getMaxEventCount() != -1) {
        sql.append(" LIMIT ").append(seQuery.getMaxEventCount() + 1);
    }
    String sqlSelect = sql.toString();

    PreparedStatement selectEventsStmt = session.getConnection().prepareStatement(sqlSelect);
    LOG.debug("SQL: " + sqlSelect);
    for (int i = 0; i < sqlParams.size(); i++) {
        selectEventsStmt.setObject(i + 1, sqlParams.get(i));
        if (LOG.isDebugEnabled()) {
            LOG.debug("     param" + i + " = " + sqlParams.get(i));
        }
    }
    return selectEventsStmt;
}

From source file:org.fosstrak.epcis.repository.query.QueryOperationsBackendSQL.java

private PreparedStatement prepareSimpleEventQuery(final QueryOperationsSession session,
        SimpleEventQueryDTO seQuery) throws SQLException, ImplementationExceptionResponse {

    StringBuilder sqlSelectFrom;/*from   w w w . j a v a  2  s.  c  om*/
    StringBuilder sqlWhereClause = new StringBuilder(" WHERE 1");
    List<Object> sqlParams = new ArrayList<Object>();

    String eventType = seQuery.getEventType();
    if (EpcisConstants.AGGREGATION_EVENT.equals(eventType)) {
        sqlSelectFrom = new StringBuilder(SQL_SELECT_FROM_AGGREGATIONEVENT);
    } else if (EpcisConstants.OBJECT_EVENT.equals(eventType)) {
        sqlSelectFrom = new StringBuilder(SQL_SELECT_FROM_OBJECTEVENT);
    } else if (EpcisConstants.QUANTITY_EVENT.equals(eventType)) {
        sqlSelectFrom = new StringBuilder(SQL_SELECT_FROM_QUANTITYEVENT);
    } else if (EpcisConstants.TRANSACTION_EVENT.equals(eventType)) {
        sqlSelectFrom = new StringBuilder(SQL_SELECT_FROM_TRANSACTIONEVENT);
    } else {
        String msg = "Unknown event type: " + eventType;
        LOG.error(msg);
        ImplementationException ie = new ImplementationException();
        ie.setReason(msg);
        throw new ImplementationExceptionResponse(msg, ie);
    }

    boolean joinedEpcs = false;
    boolean joinedBizTransacitions = false;

    // construct the SQL query dynamically
    List<EventQueryParam> eventQueryParams = seQuery.getEventQueryParams();
    int nofEventFieldExtensions = 0;
    for (EventQueryParam queryParam : eventQueryParams) {
        String eventField = queryParam.getEventField();
        Operation op = queryParam.getOp();
        Object value = queryParam.getValue();

        // check if we need to do any JOINs
        if ("epcList".equals(eventField) || "childEPCs".equals(eventField) || "anyEPC".equals(eventField)) {
            // we have a query on EPCs, so we need to join the appropriate
            // "_EPCs" table
            if (!joinedEpcs) {
                sqlSelectFrom.append(" JOIN event_").append(eventType).append("_EPCs AS epc");
                sqlSelectFrom.append(" ON event_").append(eventType).append(".id=epc.event_id");
                joinedEpcs = true;
            }
            // update the event field to search in
            eventField = "epc.epc";
        } else if (eventField.startsWith("extension")) {
            // we have a query on an extension field, so we need to join the
            // appropriate "_extensions" table

            /*
             * For every extension condition there are two EventQueryParams,
             * one for the name of the parameter and another one for the
             * value. Example: extension.intValue extension.fieldname
             * Therefore, the JOINs will be created once from every two
             * extension conditions (the odd ones)
             */
            nofEventFieldExtensions++;
            if (nofEventFieldExtensions % 2 == 1) {
                sqlSelectFrom.append(" JOIN event_").append(eventType).append("_extensions AS extension")
                        .append((nofEventFieldExtensions / 2) + 1);
                sqlSelectFrom.append(" ON event_").append(eventType).append(".id=extension")
                        .append((nofEventFieldExtensions / 2) + 1).append(".event_id");
            }
        } else if (eventField.startsWith("bizTrans")) {
            // we have a query on business transactions, so we need to join
            // the appropriate "_bizTrans" and "bizTransList" tables
            if (!joinedBizTransacitions) {
                sqlSelectFrom.append(" JOIN event_").append(eventType).append("_bizTrans AS bizTransList");
                sqlSelectFrom.append(" ON event_").append(eventType).append(".id=bizTransList.event_id");
                sqlSelectFrom.append(" JOIN BizTransaction ON bizTransList.bizTrans_id=BizTransaction.id");
                sqlSelectFrom.append(" JOIN voc_BizTrans AS bizTrans ON BizTransaction.bizTrans=bizTrans.id");
                sqlSelectFrom.append(
                        " JOIN voc_BizTransType AS bizTransType ON BizTransaction.type=bizTransType.id");
                joinedBizTransacitions = true;
            }
            if ("bizTransList.bizTrans".equals(eventField)) {
                eventField = "bizTrans";
            } else if ("bizTransList.type".equals(eventField)) {
                eventField = "bizTransType";
            }
        } else if (eventField.endsWith(".attribute")) {
            String attrTable = attributeTablenameMap.get(eventField);
            if (attrTable != null) {
                String vocAlias = eventField.substring(0, eventField.indexOf("."));
                sqlSelectFrom.append(" JOIN ").append(attrTable);
                sqlSelectFrom.append(" ON ").append(attrTable).append(".id=").append(vocAlias).append(".id");
                eventField = attrTable + ".attribute";
            }
        } else if (eventField.endsWith(".attribute.value")) {
            String attrTable = attributeTablenameMap.get(eventField.substring(0, eventField.length() - 6));
            eventField = attrTable + ".value";
        }
        String vocField = vocabularyTypeMap.get(eventField);
        if (vocField != null) {
            eventField = vocField;
        }

        // now check the provided event field, operation, and value and
        // update the SQL strings accordingly
        if (value == null && op == Operation.EXISTS) {
            if (eventField.startsWith("epc") || eventField.startsWith("bizTransList")) {
                // EXISTS-query already coped with by JOIN - nothing to do
            } else {
                // check if the given event field exists
                sqlWhereClause.append(" AND ?");
                sqlParams.add(eventField);
            }
        } else if (value != null) {
            if (value instanceof List<?>) {
                // we have a multi-value query parameter, e.g. action, EPCs,
                // vocabulary types
                List<?> paramValues = (List<?>) value;
                if (!paramValues.isEmpty()) {
                    if (op == Operation.MATCH || op == Operation.WD) {
                        // this results in a SQL "LIKE" query
                        sqlWhereClause.append(" AND (0");
                        for (Object paramValue : paramValues) {
                            String strValue = (String) paramValue;

                            // MATCH-params might be 'pure identity' EPC
                            // patterns
                            if (op == Operation.MATCH && !eventField.startsWith("epcClass")) {
                                if (strValue.startsWith("urn:epc:idpat:")) {
                                    strValue = strValue.replace("urn:epc:idpat:", "urn:epc:id:");
                                }
                            }
                            strValue = strValue.replaceAll("\\*", "%");

                            sqlWhereClause.append(" OR ").append(eventField).append(" LIKE ?");
                            sqlParams.add(strValue);
                            if (seQuery.isAnyEpc() && "epc.epc".equals(eventField)) {
                                sqlWhereClause.append(" OR parentID LIKE ?");
                                sqlParams.add(strValue);
                            }
                        }
                        sqlWhereClause.append(")");
                    } else {
                        // this results in a SQL "IN" query
                        sqlWhereClause.append(" AND ").append(eventField).append(" IN (?");
                        sqlParams.add(paramValues.get(0));
                        for (int i = 1; i < paramValues.size(); i++) {
                            sqlWhereClause.append(",?");
                            sqlParams.add(paramValues.get(i));
                        }
                        sqlWhereClause.append(")");
                    }
                }
            } else {
                // we have a single-value parameter, e.g. eventTime,
                // recordTime, parentID
                String sqlOp = operationMap.get(op);
                sqlWhereClause.append(" AND ").append(eventField).append(" ").append(sqlOp).append(" ?");
                sqlParams.add(value);
            }
        }
    }

    // construct the final SQL query string
    StringBuilder sql = sqlSelectFrom.append(sqlWhereClause);
    if (seQuery.getOrderBy() != null) {
        sql.append(" ORDER BY ").append(seQuery.getOrderBy());
        if (seQuery.getOrderDirection() != null) {
            sql.append(" ").append(seQuery.getOrderDirection().name());
        }
    }
    if (seQuery.getLimit() != -1) {
        sql.append(" LIMIT ").append(seQuery.getLimit());
    } else if (seQuery.getMaxEventCount() != -1) {
        sql.append(" LIMIT ").append(seQuery.getMaxEventCount() + 1);
    }
    String sqlSelect = sql.toString();

    PreparedStatement selectEventsStmt = session.getConnection().prepareStatement(sqlSelect);
    LOG.debug("SQL: " + sqlSelect);
    for (int i = 0; i < sqlParams.size(); i++) {
        selectEventsStmt.setObject(i + 1, sqlParams.get(i));
        if (LOG.isDebugEnabled()) {
            LOG.debug("     param" + i + " = " + sqlParams.get(i));
        }
    }
    return selectEventsStmt;
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyFromGBIF.java

/**
 * // ww  w  .  j  av  a 2s  .c  o m
 */
public void processMissingGenusSpecies() {
    String pSQL = "UPDATE raw SET genus=?, species=?, subspecies=? WHERE id = ?";

    String where = " WHERE genus IS NULL AND species IS NULL AND scientific_name IS NOT NULL";
    String gbifSQLBase = "SELECT id, scientific_name FROM raw" + where;

    long totalRecs = BasicSQLUtils.getCount(srcDBConn, "SELECT COUNT(*) FROM raw " + where);
    long procRecs = 0;
    long startTime = System.currentTimeMillis();
    int secsThreshold = 0;

    PrintWriter pw = null;

    final double HRS = 1000.0 * 60.0 * 60.0;

    Statement gStmt = null;
    PreparedStatement pStmt = null;

    try {
        pw = new PrintWriter("gbif.log");

        pStmt = dbConn.prepareStatement(pSQL);

        System.out.println("Total Records: " + totalRecs);
        pw.println("Total Records: " + totalRecs);

        gStmt = srcDBConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        gStmt.setFetchSize(Integer.MIN_VALUE);

        System.out.println(gbifSQLBase);

        ResultSet gRS = gStmt.executeQuery(gbifSQLBase);
        while (gRS.next()) {
            int id = gRS.getInt(1);
            pStmt.setObject(4, id);

            String[] gs = StringUtils.split(gRS.getString(2), ' ');
            switch (gs.length) {
            case 1:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, null);
                pStmt.setString(3, null);
                break;

            case 2:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, gs[1]);
                pStmt.setString(3, null);
                break;

            case 3:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, gs[1]);
                pStmt.setString(3, gs[2]);
                break;

            default:
                continue;
            }
            try {
                pStmt.executeUpdate();

            } catch (Exception ex) {
                System.err.println("For ID[" + gRS.getObject(1) + "][" + gRS.getObject(2) + "]");
                ex.printStackTrace();
                pw.print("For ID[" + gRS.getObject(1) + "] " + ex.getMessage());
                pw.flush();
            }

            procRecs++;
            if (procRecs % 10000 == 0) {
                long endTime = System.currentTimeMillis();
                long elapsedTime = endTime - startTime;

                double avergeTime = (double) elapsedTime / (double) procRecs;

                double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs - procRecs)
                        / HRS;

                int seconds = (int) (elapsedTime / 60000.0);
                if (secsThreshold != seconds) {
                    secsThreshold = seconds;

                    String msg = String.format(
                            "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                            ((double) (elapsedTime)) / HRS, avergeTime,
                            100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                    System.out.println(msg);
                    pw.println(msg);
                    pw.flush();
                }
            }
        }

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            if (gStmt != null) {
                gStmt.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            pw.close();

        } catch (Exception ex) {

        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");
}

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

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

From source file:org.sleuthkit.autopsy.imagegallery.datamodel.DrawableDB.java

public List<Long> getFileIDsInGroup(GroupKey<?> groupKey) throws TskCoreException {

    if (groupKey.getAttribute().isDBColumn) {
        switch (groupKey.getAttribute().attrName) {
        case CATEGORY:
            return groupManager.getFileIDsWithCategory((Category) groupKey.getValue());
        case TAGS:
            return groupManager.getFileIDsWithTag((TagName) groupKey.getValue());
        }/* w ww. ja v  a  2 s . co  m*/
    }
    List<Long> files = new ArrayList<>();
    dbReadLock();
    try {
        PreparedStatement statement = getGroupStatment(groupKey.getAttribute());
        statement.setObject(1, groupKey.getValue());

        try (ResultSet valsResults = statement.executeQuery()) {
            while (valsResults.next()) {
                files.add(valsResults.getLong(OBJ_ID));
            }
        }
    } catch (SQLException ex) {
        LOGGER.log(Level.WARNING,
                "failed to get file for group:" + groupKey.getAttribute() + " == " + groupKey.getValue(), ex);
    } finally {
        dbReadUnlock();
    }

    return files;
}

From source file:com.gs.obevo.db.impl.core.jdbc.JdbcHelper.java

private int updateInternal(Connection conn, int retryCount, String sql, Object... args) {
    this.jdbcHandler.preUpdate(conn, this);

    Statement statement = null;//from w  ww  . j a v  a 2s  .c  om
    PreparedStatement ps = null;
    try {
        if (LOG.isDebugEnabled()) {
            LOG.debug("Executing update on connection {}: {} with args: {}", displayConnection(conn), sql,
                    args);
        }
        if (args.length == 0) {
            // For args length == 0, we use regular Statements and not PreparedStatements
            // This is because of http://www.selikoff.net/2008/08/04/question-mark-%E2%80%98%E2%80%99-characters-as-text-in-jdbc/
            // In short - question marks are naively interpreted by PreparedStatements as parameters, even if in
            // strings or comments
            // This can affect legacy DDL files that may have such comments sprinkled in. So we go w/ Statements,
            // which is what spring-jdbc did (this product had used spring-jdbc in an early incarnation, which was
            // when we discovered this issue)
            statement = conn.createStatement();
            return statement.executeUpdate(sql);
        } else {
            ps = conn.prepareStatement(sql);
            for (int j = 0; j < args.length; j++) {
                if (!parameterTypeEnabled || args[j] != null) {
                    ps.setObject(j + 1, args[j]);
                } else {
                    ps.setNull(j + 1, ps.getParameterMetaData().getParameterType(j + 1));
                }
            }

            return ps.executeUpdate();
        }
    } catch (SQLException e) {
        DataAccessException dataAccessException = new DataAccessException(e);
        boolean retry = this.jdbcHandler.handleException(this, conn, retryCount, dataAccessException);
        if (retry) {
            return this.updateInternal(conn, retryCount + 1, sql, args);
        } else {
            throw dataAccessException;
        }
    } finally {
        DbUtils.closeQuietly(statement);
        DbUtils.closeQuietly(ps);
    }
}

From source file:mil.army.usace.data.dataquery.rdbms.RdbmsDataQuery.java

public void setParams(PreparedStatement st, Object[] params) throws SQLException {
    if (params != null) {
        for (int i = 0; i < params.length; i++) {
            if (params[i] instanceof java.util.Date) {
                st.setDate(i + 1, new java.sql.Date(((java.util.Date) params[i]).getTime()));
            } else {
                st.setObject(i + 1, (Object) params[i]);
            }//from   www  .  jav  a2 s. com
        }
    }
}

From source file:dbutils.DbUtilsTemplate.java

public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {

    // check the parameter count, if we can
    ParameterMetaData pmd = null;
    if (!pmdKnownBroken) {
        pmd = stmt.getParameterMetaData();
        int stmtCount = pmd.getParameterCount();
        int paramsCount = params == null ? 0 : params.length;

        if (stmtCount != paramsCount) {
            throw new SQLException(
                    "Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount);
        }/*from ww w. ja  v  a2s . co m*/
    }

    // nothing to do here
    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.
            int sqlType = Types.VARCHAR;
            if (!pmdKnownBroken) {
                try {
                    /*
                     * It's not possible for pmdKnownBroken to change from
                     * true to false, (once true, always true) so pmd cannot
                     * be null here.
                     */
                    sqlType = pmd.getParameterType(i + 1);
                } catch (SQLException e) {
                    pmdKnownBroken = true;
                }
            }
            stmt.setNull(i + 1, sqlType);
        }
    }
}

From source file:org.guzz.orm.sql.BindedCompiledSQL.java

/**
 * ???setPreparedStatement/*from  www .  j a  v a  2s.c  o m*/
 * 
 * @param dialect dialect
 * @param pstm PreparedStatement
 */
public void prepareNamedParams(Dialect dialect, PreparedStatement pstm) throws SQLException {
    NormalCompiledSQL cs = getCompiledSQLToRun();

    String[] orderParams = cs.getOrderedParams();

    for (int i = 0; i < orderParams.length; i++) {
        String orderParam = orderParams[i];
        Object value = bindedParams.get(orderParam);

        if (value == null) {
            throw new DaoException("missing parameter:[" + orderParam + "] in sql:" + getSQLToRun());
        }

        //NEW Implemention to fix
        if (value instanceof NullValue) {
            value = null;
        }

        SQLDataType type = cs.getSQLDataTypeForParam(cs, orderParam);

        if (type != null) {
            type.setSQLValue(pstm, i + bindStartIndex, value);
        } else { //jdbc?
            if (log.isInfoEnabled()) {
                log.info(
                        "bind named params without SQLDataType found, try CompiledSQL#addParamPropMapping(,) for better binding. bind param is:["
                                + orderParam + "], value is :[" + value + "]. sql is:" + getSQLToRun());
            }

            pstm.setObject(i + bindStartIndex, value);
        }
    }

    //      //The code belowed warning: This IS a BUG!! null object is not supported!!! fix it to use ObjectMapping's SQLDataType for all cases.
    //      
    //      if(value instanceof NullValue){
    //         //this method only works for pojo's insert/update/delete methods
    //         SQLDataType type = compiledSQL.getMapping().getSQLDataTypeOfColumn(compiledSQL.getMapping().getColNameByPropName(orderParam)) ;
    //         if(type != null){
    //            type.setSQLValue(pstm, i + 1, null) ;
    //         }else{
    //            pstm.setObject(i + 1, null) ;
    //         }
    //      }else{
    //         //this method cann't handle null value. So, we change to detect the ObjectMapping's type
    //         SQLDataType type = dialect.getDataType(value.getClass().getName()) ;
    //         type.setSQLValue(pstm, i + 1, value) ;
    //      }
}

From source file:com.netspective.axiom.schema.table.BasicTable.java

public long getCount(ConnectionContext cc, String whereCond, Object[] bindValues)
        throws NamingException, SQLException {
    StringBuffer sql = new StringBuffer("select count(*) from ");
    sql.append(cc.getDatabasePolicy().resolveTableName(this));
    if (whereCond != null) {
        sql.append(" ");
        if (!whereCond.startsWith("where"))
            sql.append("where");
        sql.append(" ");
        sql.append(whereCond);//from  w  w  w .ja  va 2  s  .c o  m
    }

    PreparedStatement stmt = cc.getConnection().prepareStatement(sql.toString());
    try {
        if (bindValues != null) {
            for (int i = 0; i < bindValues.length; i++)
                stmt.setObject(i + 1, bindValues[i]);
        }
        ResultSet rs = stmt.executeQuery();
        try {
            if (rs.next())
                return rs.getLong(1);
            else
                return 0;
        } finally {
            rs.close();
        }
    } finally {
        stmt.close();
    }
}