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.obm.domain.dao.CalendarDaoJdbcImpl.java

@VisibleForTesting
void insertAttendees(AccessToken editor, Event ev, Connection con, List<Attendee> attendees)
        throws SQLException {
    String attQ = "INSERT INTO EventLink (" + ATT_INSERT_FIELDS + ") VALUES (" + "?, " + // event_id
            "?, " + // entity_id
            "?, " + // state
            "?, " + // required
            "?," + // percent
            "?," + // user_create
            "?" + // is_organizer
            ")";// ww  w. j a  v a 2s.  c o  m
    boolean shouldClearOrganizer = false;
    PreparedStatement ps = null;

    try {
        ps = con.prepareStatement(attQ);

        final int eventObmId = ev.getObmId().getObmId();
        final Set<Attendee> listAttendee = removeDuplicateAttendee(attendees);
        Set<EntityId> alreadyAddedAttendees = Sets.newHashSet();

        for (final Attendee at : listAttendee) {
            boolean isOrganizer = Objects.firstNonNull(at.isOrganizer(), false);

            String attendeeEmail = at.getEmail();
            EntityId userEntity = at.getEntityId();

            // There must be only one organizer in a given event
            if (isOrganizer) {
                shouldClearOrganizer = true;
            }

            if (alreadyAddedAttendees.contains(userEntity)) {
                logger.info("Attendee {} with entity ID {} already added, skipping.", attendeeEmail,
                        userEntity);

                continue;
            }

            ps.setInt(1, eventObmId);
            ps.setInt(2, userEntity.getId());
            ps.setObject(3, getJdbcObjectParticipation(at));
            ps.setObject(4, getJdbcObjectParticipationRole(at));
            ps.setInt(5, at.getPercent());
            ps.setInt(6, editor.getObmId());
            ps.setBoolean(7, isOrganizer);
            ps.addBatch();
            logger.info("Adding " + attendeeEmail + (isOrganizer ? " as organizer" : " as attendee"));

            alreadyAddedAttendees.add(userEntity);
        }

        // Clear the previous organizer if needed
        if (shouldClearOrganizer) {
            clearOrganizer(eventObmId, con);
        }

        ps.executeBatch();
    } finally {
        obmHelper.cleanup(null, ps, null);
    }
}

From source file:fr.aliacom.obm.common.calendar.CalendarDaoJdbcImpl.java

@VisibleForTesting
void insertAttendees(AccessToken editor, Event ev, Connection con, List<Attendee> attendees)
        throws SQLException {
    String attQ = "INSERT INTO EventLink (" + ATT_INSERT_FIELDS + ") VALUES (" + "?, " + // event_id
            "?, " + // entity_id
            "?, " + // state
            "?, " + // required
            "?," + // percent
            "?," + // user_create
            "?" + // is_organizer
            ")";/*from w  ww . j av a  2  s .c  om*/
    boolean shouldClearOrganizer = false;
    PreparedStatement ps = null;

    try {
        ps = con.prepareStatement(attQ);

        final int eventObmId = ev.getObmId().getObmId();
        final Set<Attendee> listAttendee = removeDuplicateAttendee(attendees);
        Set<EntityId> alreadyAddedAttendees = Sets.newHashSet();

        for (final Attendee at : listAttendee) {
            boolean isOrganizer = Objects.firstNonNull(at.isOrganizer(), false);

            String attendeeEmail = at.getEmail();
            EntityId userEntity = at.getEntityId();

            // There must be only one organizer in a given event
            if (isOrganizer) {
                shouldClearOrganizer = true;
            }

            if (alreadyAddedAttendees.contains(userEntity)) {
                logger.info("Attendee {} with entity ID {} already added, skipping.", attendeeEmail,
                        userEntity);

                continue;
            }

            ps.setInt(1, eventObmId);
            ps.setInt(2, userEntity.getId());
            ps.setObject(3, getJdbcObjectParticipation(at));
            ps.setObject(4, getJdbcObjectParticipationRole(at));
            ps.setInt(5, at.getPercent());
            ps.setInt(6, editor.getObmId());
            ps.setBoolean(7, isOrganizer);
            ps.addBatch();
            logger.info(LogUtils.prefix(editor) + "Adding " + attendeeEmail
                    + (isOrganizer ? " as organizer" : " as attendee"));

            alreadyAddedAttendees.add(userEntity);
        }

        // Clear the previous organizer if needed
        if (shouldClearOrganizer) {
            clearOrganizer(eventObmId, con);
        }

        ps.executeBatch();
    } finally {
        obmHelper.cleanup(null, ps, null);
    }
}

From source file:org.globus.workspace.persistence.PersistenceAdapterImpl.java

public void add(InstanceResource resource)

        throws WorkspaceDatabaseException {

    if (resource == null) {
        throw new WorkspaceDatabaseException("resource is null");
    }//from w  w  w.j  ava  2  s . c om

    final int id = resource.getID();

    if (id < 0) {
        throw new WorkspaceDatabaseException("id is less than zero");
    }

    if (this.dbTrace) {
        logger.trace("add(): " + Lager.id(id) + ", WorkspaceResource = " + resource);
    }

    final byte[] faultBytes;

    try {
        faultBytes = ErrorUtil.toByteArray(resource.getStateThrowable());
    } catch (IOException e) {
        throw new WorkspaceDatabaseException(e);
    }

    Connection c = null;
    PreparedStatement pstmt = null;
    PreparedStatement[] pstmts = null;
    try {
        c = getConnection();
        c.setAutoCommit(false);
        pstmt = c.prepareStatement(SQL_INSERT_RESOURCE);

        pstmt.setInt(1, id);
        pstmt.setString(2, resource.getName());
        pstmt.setInt(3, resource.getState());
        pstmt.setInt(4, resource.getTargetState());

        if (resource.getTerminationTime() != null) {
            pstmt.setObject(5, new Long(resource.getTerminationTime().getTimeInMillis()));
        } else {
            pstmt.setInt(5, 0);
        }

        if (resource.isOpsEnabled()) {
            pstmt.setInt(6, 1);
        } else {
            pstmt.setInt(6, 0);
        }

        if (resource.getCreatorID() != null) {
            pstmt.setString(7, resource.getCreatorID());
        } else {
            pstmt.setNull(7, Types.VARCHAR);
        }

        if (resource.getStartTime() != null) {
            pstmt.setObject(8, new Long(resource.getStartTime().getTimeInMillis()));
        } else {
            pstmt.setInt(8, 0);
        }

        if (resource.isVMMaccessOK()) {
            pstmt.setInt(9, 1);
        } else {
            pstmt.setInt(9, 0);
        }

        if (resource.getEnsembleId() != null) {
            pstmt.setString(10, resource.getEnsembleId());
        } else {
            pstmt.setNull(10, Types.VARCHAR);
        }

        if (resource.getGroupId() != null) {
            pstmt.setString(11, resource.getGroupId());
        } else {
            pstmt.setNull(11, Types.VARCHAR);
        }

        pstmt.setInt(12, resource.getGroupSize());

        if (resource.isLastInGroup()) {
            pstmt.setInt(13, 1);
        } else {
            pstmt.setInt(13, 0);
        }

        pstmt.setInt(14, resource.getLaunchIndex());

        if (faultBytes != null) {
            pstmt.setObject(15, faultBytes, Types.BLOB);
        } else {
            pstmt.setNull(15, Types.BLOB);
        }

        pstmt.setString(16, resource.getClientToken());

        pstmt.setDouble(17, resource.getChargeRatio());

        if (this.dbTrace) {
            logger.trace("creating WorkspaceResource db " + "entry for " + Lager.id(id));
        }

        pstmt.executeUpdate();

        if (resource instanceof VMPersistence) {

            pstmts = VirtualMachinePersistenceUtil.getInsertVM(resource, id, c);

            if (this.dbTrace) {
                logger.trace("creating VirtualMachine db " + "entry for " + Lager.id(id) + ": " + pstmts.length
                        + " inserts");
            }

            for (int i = 0; i < pstmts.length; i++) {
                pstmts[i].executeUpdate();
            }
        }

        c.commit();

    } catch (SQLException e) {
        logger.error("", e);
        throw new WorkspaceDatabaseException(e);
    } catch (ManageException e) {
        logger.error("", e);
        throw new WorkspaceDatabaseException(e);
    } finally {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
            if (pstmts != null) {
                for (int i = 0; i < pstmts.length; i++) {
                    pstmts[i].close();
                }
            }
            if (c != null) {
                c.setAutoCommit(true);
                returnConnection(c);
            }
        } catch (SQLException sql) {
            logger.error("SQLException in finally cleanup", sql);
        }
    }
}

From source file:org.ramadda.repository.database.DatabaseManager.java

/**
 * _more_/*w ww  . ja va 2  s .  c  o  m*/
 *
 * @param statement _more_
 * @param values _more_
 * @param startIdx _more_
 *
 * @throws Exception _more_
 */
public void setValues(PreparedStatement statement, Object[] values, int startIdx) throws Exception {
    for (int i = 0; i < values.length; i++) {
        if (values[i] == null) {
            statement.setNull(i + startIdx, java.sql.Types.VARCHAR);
        } else if (values[i] instanceof Date) {
            setDate(statement, i + startIdx, (Date) values[i]);
        } else if (values[i] instanceof Boolean) {
            boolean b = ((Boolean) values[i]).booleanValue();
            statement.setInt(i + startIdx, (b ? 1 : 0));
        } else if (values[i] instanceof Double) {
            double d = ((Double) values[i]).doubleValue();
            //Special check for nans on derby
            if (d == Double.POSITIVE_INFINITY) {
                d = Double.NaN;
            } else if (d == Double.NEGATIVE_INFINITY) {
                d = Double.NaN;
            }
            if (d != d) {
                if (isDatabaseDerby()) {
                    d = -99999999.999;
                }
                //
            }
            try {
                statement.setDouble(i + startIdx, d);
            } catch (Exception exc) {
                System.err.println("d:" + d);

                throw exc;
            }
        } else {
            statement.setObject(i + startIdx, values[i]);
        }
    }
}

From source file:gemlite.core.internal.db.AsyncEventHelper.java

/**
 * Set column value at given index in a prepared statement. The implementation
 * tries using the matching underlying type to minimize any data type
 * conversions, and avoid creating wrapper Java objects (e.g. {@link Integer}
 * for primitive int).//from  w  w  w  . ja va 2  s.c om
 * 
 * @param type
 *          the SQL type of the column as specified by JDBC {@link Types}
 *          class
 * @param ps
 *          the prepared statement where the column value has to be set
 * @param row
 *          the source row as a {@link ResultSet} from where the value has to
 *          be extracted
 * @param rowPosition
 *          the 1-based position of the column in the provided
 *          <code>row</code>
 * @param paramIndex
 *          the 1-based position of the column in the target prepared
 *          statement (provided <code>ps</code> argument)
 * @param sync
 *          the {@link DBSynchronizer} object, if any; it is used to store
 *          whether the current driver is JDBC4 compliant to enable performing
 *          BLOB/CLOB operations {@link PreparedStatement#setBinaryStream},
 *          {@link PreparedStatement#setCharacterStream}
 * 
 * @throws SQLException
 *           in case of an exception in setting parameters
 */
public final void setColumnInPrepStatement(String type, Object val, PreparedStatement ps,
        final DBSynchronizer sync, int paramIndex) throws SQLException {
    switch (type) {
    case JavaTypes.STRING:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.VARCHAR);
        else {
            final String realVal = (String) val;
            ps.setString(paramIndex, realVal);
        }
        break;
    case JavaTypes.INT1:
    case JavaTypes.INT2:
    case JavaTypes.INT3:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.INTEGER);
        else {
            final int realVal = (int) val;
            ps.setInt(paramIndex, realVal);
        }
        break;
    case JavaTypes.DOUBLE1:
    case JavaTypes.DOUBLE2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DOUBLE);
        else {
            final double realVal = (double) val;
            ps.setDouble(paramIndex, realVal);
        }
        break;
    case JavaTypes.FLOAT1:
    case JavaTypes.FLOAT2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.FLOAT);
        else {
            final float realVal = (float) val;
            ps.setDouble(paramIndex, realVal);
        }
        break;
    case JavaTypes.BOOLEAN1:
    case JavaTypes.BOOLEAN2:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.BOOLEAN);
        else {
            final boolean realVal = (boolean) val;
            ps.setBoolean(paramIndex, realVal);
        }
        break;
    case JavaTypes.DATE_SQL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DATE);
        else {
            final Date realVal = (Date) val;
            ps.setDate(paramIndex, realVal);
        }
        break;
    case JavaTypes.DATE_UTIL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DATE);
        else {
            final java.util.Date realVal = (java.util.Date) val;
            ps.setDate(paramIndex, new Date(realVal.getTime()));
        }
        break;
    case JavaTypes.BIGDECIMAL:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.DECIMAL);
        else {
            final BigDecimal realVal = (BigDecimal) val;
            ps.setBigDecimal(paramIndex, realVal);
        }
        break;
    case JavaTypes.TIME:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.TIME);
        else {
            final Time realVal = (Time) val;
            ps.setTime(paramIndex, realVal);
        }
        break;
    case JavaTypes.TIMESTAMP:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.TIMESTAMP);
        else {
            final Timestamp realVal = (Timestamp) val;
            ps.setTimestamp(paramIndex, realVal);
        }
        break;
    case JavaTypes.OBJECT:
        if (val == null || StringUtils.isEmpty(val.toString()))
            ps.setNull(paramIndex, Types.JAVA_OBJECT);
        else {
            final Object realVal = (Object) val;
            ps.setObject(paramIndex, realVal);
        }
        break;
    default:
        throw new UnsupportedOperationException("java.sql.Type = " + type + " not supported");
    }
}

From source file:com.belle.yitiansystem.merchant.service.impl.MerchantsService.java

/**
 * sql?/*from w w w .  ja  va 2s .  c o  m*/
 * 
 * @author wang.m
 * @throws SQLException
 */
private List<Map<String, Object>> getMapBysql(String sql, Object[] param) throws SQLException {
    List<Map<String, Object>> maps = null;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(sql);
        if (null != param && param.length > 0) {
            for (int i = 0; i < param.length; i++) {
                pstmt.setObject(i + 1, param[i]);
            }
        }
        rs = pstmt.executeQuery();
        Map<String, Object> map = null;
        maps = new ArrayList<Map<String, Object>>();
        while (rs.next()) {
            map = new HashMap<String, Object>();
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 0; i < rsmd.getColumnCount();) {
                ++i;
                String key = rsmd.getColumnLabel(i).toLowerCase();
                if (map.containsKey(key)) {
                    throw new IllegalArgumentException("?key  " + key);
                }
                map.put(key, rs.getObject(i));
            }
            maps.add(map);
        }
    } catch (Exception e) {
        // TODO: handle exception
        logger.error("sql?!", e);
    } finally {
        close(conn, pstmt, rs);
    }
    return maps;
}

From source file:com.belle.yitiansystem.merchant.service.impl.MerchantsService.java

/**
 * ?sql?//from  www  .ja v  a 2  s. co m
 * 
 * @throws SQLException
 * 
 **/
private String getResouseBySql(String sql, List<Object> params) throws SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String str = "";
    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(sql);
        if (params != null && params.size() > 0) {
            for (int j = 0; j < params.size(); j++) {
                pstmt.setObject(j + 1, params.get(j));
            }
        }
        rs = pstmt.executeQuery();
        while (rs.next()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 0; i < rsmd.getColumnCount();) {
                ++i;
                str = (String) rs.getObject(i);
            }
        }
    } catch (Exception e) {
        // TODO: handle exception
        logger.error("sql?!" + sql, e);
    } finally {
        close(conn, pstmt, rs);
    }
    return str;
}

From source file:it.fub.jardin.server.DbUtils.java

public Integer removeObjects(final Integer resultsetId, final List<BaseModelData> records, String username)
        throws HiddenException, VisibleException {

    int resCode = 0;

    Connection connection = this.dbConnectionHandler.getConn();

    String query = new String(""), appChiavePrimaria = "";
    PreparedStatement ps = null;
    try {//from   w w  w.  j a  va 2s  .c  om

        ResultSetMetaData metadata = this.dbProperties.getResultsetMetadata(connection, resultsetId);
        String tableName = metadata.getTableName(1);
        // Ciclo per gestire pi cancellazioni nella stessa invocazione
        List<BaseModelData> primaryKeyList = this.dbProperties.getPrimaryKeys(tableName);
        if (primaryKeyList.size() <= 0) {
            throw new HiddenException("La tabella non contiene chiavi primarie: impossibile operare!");
        }
        for (BaseModelData record : records) {
            query = "";
            // Preparazione della query
            query = query.concat("DELETE FROM `" + tableName + "` WHERE `");

            String separator = "AND `";
            for (BaseModelData pk : primaryKeyList) {
                appChiavePrimaria = pk.get("PK_NAME");
                query = query.concat(appChiavePrimaria);
                if (record.get(appChiavePrimaria) == null) {
                    query = query.concat("` IS NULL ");
                } else {
                    query = query.concat("` = ? ");
                }

                query = query.concat(separator);
            }

            query = query.substring(0, query.length() - separator.length());

            ps = (PreparedStatement) connection.prepareStatement(query);
            int i = 1;
            // for (String property : record.getPropertyNames()) {
            for (BaseModelData pk : primaryKeyList) {
                ps.setObject(i, record.get((String) pk.get("PK_NAME")));
                i++;
            }

            // Log.debug("Query DELETE: " + ps);
            int num = ps.executeUpdate();
            if (num > 0) {
                // this.log("DELETE (" + ps.toString() + ")");
                JardinLogger.debug(username, "DELETE query (" + ps.toString() + ")");
            }
            resCode += num;
        }
    } catch (MySQLIntegrityConstraintViolationException ecv) {
        ecv.printStackTrace();

        throw new HiddenException("Errore durante l'eliminazione dei record: " + ecv.getLocalizedMessage());
    } catch (Exception e) {
        // Log.warn("Errore SQL", e);
        e.printStackTrace();
        throw new HiddenException("Errore durante l'eliminazione dei record: " + e.getLocalizedMessage());
    } finally {
        // JardinLogger.info("Objects removed");
        this.dbConnectionHandler.closeConn(connection);
    }

    return (new Integer(resCode));
}

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

/**
 * //from  w  w  w . ja v  a  2 s  .co  m
 */
public void process() throws SQLException {
    buildTags = new BuildTags();
    buildTags.setDbConn(dbConn);
    buildTags.setDbConn2(dbConn);
    buildTags.initialPrepareStatements();

    BasicSQLUtils.setDBConnection(dbConn);

    IdMapperMgr idMapperMgr = IdMapperMgr.getInstance();
    idMapperMgr.setDBs(srcDBConn2, dbConn);

    geoStmt1 = dbConn.prepareStatement(
            "SELECT GeographyID FROM geography WHERE RankID = ? AND ParentID = ? AND LOWER(Abbrev) = ?");
    geoStmt2 = dbConn
            .prepareStatement("SELECT GeographyID FROM geography WHERE RankID = ? AND LOWER(Abbrev) = ?");
    agentStmt = dbConn
            .prepareStatement("SELECT AgentID FROM agent WHERE LOWER(FirstName) = ? AND LOWER(LastName) = ?");
    tagStmt = dbConn.prepareStatement(
            "SELECT CollectionObjectID FROM collectionobject WHERE CollectionID = 4 AND LOWER(FieldNumber) = ?");

    BasicSQLUtils.update(srcDBConn, "UPDATE recovery SET r_date = null WHERE r_date = '0000-00-00'");

    boolean doTags = true;
    if (doTags) {
        int divId = 2;
        int dspId = 3;
        int colId = 32768;

        String sql = "SELECT tagid, " + "r_city, r_state, r_zip, r_country, r_date, r_lat, r_long, "
                + "reporter_first, reporter_last, reporter_city, reporter_state, reporter_country, reporter_zip, "
                + "dir, dist, gender, "
                + "t_first, t_middle, t_last, t_city, t_state, t_country, t_postalcode, t_org, t_lat, t_long, t_date FROM recovery ORDER BY recovid ASC";

        Statement stmt = srcDBConn.createStatement();
        stmt.setFetchSize(Integer.MIN_VALUE);

        log.debug("Querying for Tags...");
        ResultSet rs = stmt.executeQuery(sql);
        int cnt = 0;
        log.debug("Done querying for Tags...");

        Calendar cal = Calendar.getInstance();
        Timestamp ts = new Timestamp(cal.getTime().getTime());

        String common = "TimestampCreated, Version, CreatedByAgentID";
        String coStr = String.format(
                "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Text2, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?)",
                common);
        String ceStr = String.format(
                "INSERT INTO collectingevent (StartDate, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?)",
                common);
        String lcStr = String.format(
                "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)",
                common);
        String clStr = String.format(
                "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)",
                common);
        String rlStr = String.format(
                "INSERT INTO collectionrelationship (collectionRelTypeID, LeftSideCollectionID, RightSideCollectionID, %s) VALUES(?,?,?,?,?,?)",
                common);
        String agStr = String
                .format("INSERT INTO agent (AgentType, FirstName, LastName, %s) VALUES(?,?,?,?,?,?)", common);
        String adStr = String.format(
                "INSERT INTO address (City, State, PostalCode, Country, AgentID, %s) VALUES(?,?,?,?, ?,?,?,?)",
                common);

        String lcUpdateStr = "UPDATE locality SET Latitude1=?, Longitude1=?, SrcLatLongUnit=?, Lat1text=?, Long1text=?, LatLongType=? WHERE LocalityID = ?";
        String lcStr2 = "SELECT LocalityID FROM locality WHERE LocalityName LIKE ? AND LocalityName LIKE ?";

        PreparedStatement coStmt = dbConn.prepareStatement(coStr);
        PreparedStatement ceStmt = dbConn.prepareStatement(ceStr);
        PreparedStatement lcStmt = dbConn.prepareStatement(lcStr);
        PreparedStatement clStmt = dbConn.prepareStatement(clStr);
        PreparedStatement rlStmt = dbConn.prepareStatement(rlStr);
        PreparedStatement agStmt = dbConn.prepareStatement(agStr);
        PreparedStatement adStmt = dbConn.prepareStatement(adStr);
        PreparedStatement lcUpStmt = dbConn.prepareStatement(lcUpdateStr);
        PreparedStatement lcStmt2 = dbConn.prepareStatement(lcStr2);

        int recNum = 1;
        while (rs.next()) {
            String tag = rs.getString(1);

            String city = rs.getString(2);
            String state = rs.getString(3);
            String zip = rs.getString(4);
            String country = rs.getString(5);
            Date date = rs.getDate(6);

            double lat = rs.getDouble(7);
            boolean isLatNull = rs.wasNull();

            double lon = rs.getDouble(8);
            boolean isLonNull = rs.wasNull();

            String dir = rs.getString(9);
            String dist = rs.getString(10);
            String gender = rs.getString(11);

            String rep_first = rs.getString(12);
            String rep_last = rs.getString(13);
            String rep_city = rs.getString(14);
            String rep_state = rs.getString(15);
            String rep_country = rs.getString(16);
            String rep_zip = rs.getString(17);

            String t_first = rs.getString(18);
            //String t_middle    = rs.getString(19);
            String t_last = rs.getString(20);
            String t_city = rs.getString(21);
            String t_state = rs.getString(22);
            String t_country = rs.getString(23);
            String t_zip = rs.getString(24);
            //String t_org       = rs.getString(25);

            double t_lat = rs.getDouble(26);
            boolean isTLatNull = rs.wasNull();

            double t_lon = rs.getDouble(27);
            boolean isTLonNull = rs.wasNull();

            //String oldState = state;

            city = condense(rep_city, t_city, city);
            state = condense(rep_state, state, t_state);
            country = condense(rep_country, country, t_country);
            zip = condense(rep_zip, zip, t_zip);
            rep_first = condense(rep_first, t_first);
            rep_last = condense(rep_last, t_last);

            /*boolean debug = ((rep_state != null && rep_state.equals("IA")) || 
            (t_state != null && t_state.equals("IA")) || 
            (oldState != null && oldState.equals("IA")));
                    
            if (debug && (state == null || !state.equals("IA")))
            {
            System.out.println("ouch");
            }*/

            if (rep_first != null && rep_first.length() > 50) {
                rep_first = rep_first.substring(0, 50);
            }

            lat = isLatNull && !isTLatNull ? t_lat : lat;
            lon = isLonNull && !isTLonNull ? t_lon : lon;

            try {
                // (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID
                Integer geoId = buildTags.getGeography(country, state, null);

                // Latitude varies between -90 and 90, and Longitude between -180 and 180.
                if (lat < -90.0 || lat > 90.0) {
                    lcStmt.setObject(1, null);
                    lcStmt.setObject(4, null);
                } else {
                    lcStmt.setDouble(1, lat);
                    lcStmt.setString(4, Double.toString(lat));

                    lcUpStmt.setDouble(1, lat);
                    lcUpStmt.setString(4, Double.toString(lat));
                }

                if (lon < -180.0 || lon > 180.0) {
                    lcStmt.setObject(2, null);
                    lcStmt.setObject(5, null);
                } else {
                    lcStmt.setDouble(2, lon);
                    lcStmt.setString(5, Double.toString(lon));

                    lcUpStmt.setDouble(2, lon);
                    lcUpStmt.setString(5, Double.toString(lon));
                }

                String locName = null;
                String fullName = null;

                Integer locId = null;
                geoId = buildTags.getGeography(country, state, null);
                if (geoId != null) {
                    fullName = geoFullNameHash.get(geoId);
                    if (fullName == null) {
                        fullName = BasicSQLUtils
                                .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId);
                        geoFullNameHash.put(geoId, fullName);
                    }

                    if (StringUtils.isNotEmpty(city)) {
                        locName = city + ", " + fullName;
                    } else {
                        locName = fullName;
                    }
                    locId = localityHash.get(locName);
                    if (locId == null) {
                        lcStmt2.setString(1, "%" + city);
                        lcStmt2.setString(2, country + "%");
                        ResultSet lcRS = lcStmt2.executeQuery();
                        if (lcRS.next()) {
                            locId = lcRS.getInt(1);
                            if (!lcRS.wasNull()) {
                                localityHash.put(locName, locId);
                            }
                        }
                        lcRS.close();
                    }

                } else {
                    //unknown++;
                    fullName = "Unknown";
                    locName = buildTags.buildLocalityName(city, fullName);
                    geoId = 27507; // Unknown
                    locId = localityHash.get(locName);
                    //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]");
                }

                if (locId == null) {
                    lcStmt.setByte(3, (byte) 0);
                    lcStmt.setString(6, "Point");
                    lcStmt.setInt(7, dspId);
                    lcStmt.setString(8, getLocalityName(country, state, null, city));
                    lcStmt.setObject(9, geoId);
                    lcStmt.setTimestamp(10, ts);
                    lcStmt.setInt(11, 1);
                    lcStmt.setInt(12, 1);
                    lcStmt.executeUpdate();
                    locId = BasicSQLUtils.getInsertedId(lcStmt);

                } else if (!isLatNull && !isLonNull) {
                    int count = BasicSQLUtils.getCountAsInt(
                            "SELECT COUNT(*) FROM locality WHERE Latitude1 IS NULL AND Longitude1 IS NULL AND LocalityID = "
                                    + locId);
                    if (count == 1) {
                        lcUpStmt.setByte(3, (byte) 0);
                        lcUpStmt.setString(6, "Point");
                        lcUpStmt.setInt(7, locId);
                        lcUpStmt.executeUpdate();
                    }
                }

                // (StartDate, Method, DisciplineID, LocalityID
                ceStmt.setDate(1, date);
                ceStmt.setInt(2, dspId);
                ceStmt.setInt(3, locId);
                ceStmt.setTimestamp(4, ts);
                ceStmt.setInt(5, 1);
                ceStmt.setInt(6, 1);
                ceStmt.executeUpdate();
                Integer ceId = BasicSQLUtils.getInsertedId(ceStmt);

                //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId
                coStmt.setString(1, String.format("%09d", recNum++));
                coStmt.setString(2, tag);
                coStmt.setString(3, gender);
                coStmt.setString(4, dir);
                coStmt.setString(5, dist);
                coStmt.setInt(6, colId);
                coStmt.setInt(7, colId);
                coStmt.setInt(8, ceId);
                coStmt.setTimestamp(9, ts);
                coStmt.setInt(10, 1);
                coStmt.setInt(11, 1);
                coStmt.executeUpdate();
                //Integer coId = BasicSQLUtils.getInsertedId(coStmt);

                Integer agentId = getAgentId(agentStmt, rep_first, rep_last);
                if (agentId == null) {
                    agStmt.setInt(1, 0);
                    agStmt.setString(2, rep_first);
                    agStmt.setString(3, rep_last);
                    agStmt.setTimestamp(4, ts);
                    agStmt.setInt(5, 1);
                    agStmt.setInt(6, 1);
                    agStmt.executeUpdate();
                    agentId = BasicSQLUtils.getInsertedId(agStmt);

                    if (agentId != null) {
                        adStmt.setString(1, rep_city);
                        adStmt.setString(2, rep_state);
                        adStmt.setString(3, rep_zip);
                        adStmt.setString(4, rep_country);
                        adStmt.setInt(5, agentId);
                        adStmt.setTimestamp(6, ts);
                        adStmt.setInt(7, 1);
                        adStmt.setInt(8, 1);
                        adStmt.executeUpdate();
                    } else {
                        log.error("agentId is null after being created: " + rep_first + ", " + rep_last);
                    }
                }

                // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID
                clStmt.setInt(1, 0);
                clStmt.setBoolean(2, true);
                clStmt.setInt(3, ceId);
                clStmt.setInt(4, divId);
                clStmt.setInt(5, agentId);
                clStmt.setTimestamp(6, ts);
                clStmt.setInt(7, 1);
                clStmt.setInt(8, 1);
                clStmt.executeUpdate();

            } catch (Exception ex) {
                log.debug(recNum + " tag[" + tag + "]");
                ex.printStackTrace();
            }

            cnt++;
            if (cnt % 100 == 0) {
                System.out.println("Col Obj: " + cnt);
            }
        }

        coStmt.close();
        ceStmt.close();
        lcStmt.close();
        clStmt.close();
        rlStmt.close();
        agStmt.close();
        adStmt.close();
        lcUpStmt.close();

        buildTags.cleanup();
    }
}

From source file:org.apache.hive.jdbc.TestJdbcDriver2.java

@Test
public void testPrepareStatement() {

    String sql = "from (select count(1) from " + tableName
            + " where   'not?param?not?param' <> 'not_param??not_param' and ?=? "
            + " and 1=? and 2=? and 3.0=? and 4.0=? and 'test\\'string\"'=? and 5=? and ?=? "
            + " and date '2012-01-01' = date ?"
            + " ) t  select '2011-03-25' ddate,'China',true bv, 10 num limit 10";

    ///////////////////////////////////////////////
    //////////////////// correct testcase
    //////////////////// executed twice: once with the typed ps setters, once with the generic setObject
    //////////////////////////////////////////////
    try {/*from w w  w .j  a  v a  2s .  c  o  m*/
        PreparedStatement ps = createPreapredStatementUsingSetXXX(sql);
        ResultSet res = ps.executeQuery();
        assertPreparedStatementResultAsExpected(res);
        ps.close();

        ps = createPreapredStatementUsingSetObject(sql);
        res = ps.executeQuery();
        assertPreparedStatementResultAsExpected(res);
        ps.close();

    } catch (Exception e) {
        e.printStackTrace();
        fail(e.toString());
    }

    ///////////////////////////////////////////////
    //////////////////// other failure testcases
    //////////////////////////////////////////////
    // set nothing for prepared sql
    Exception expectedException = null;
    try {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.executeQuery();
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the un-setted sql statement should throw exception", expectedException);

    // set some of parameters for prepared sql, not all of them.
    expectedException = null;
    try {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setBoolean(1, true);
        ps.setBoolean(2, true);
        ps.executeQuery();
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the invalid setted sql statement should throw exception", expectedException);

    // set the wrong type parameters for prepared sql.
    expectedException = null;
    try {
        PreparedStatement ps = con.prepareStatement(sql);

        // wrong type here
        ps.setString(1, "wrong");

        assertTrue(true);
        ResultSet res = ps.executeQuery();
        if (!res.next()) {
            throw new Exception("there must be a empty result set");
        }
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Execute the invalid setted sql statement should throw exception", expectedException);

    // setObject to the yet unknown type java.util.Date
    expectedException = null;
    try {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setObject(1, new Date());
        ps.executeQuery();
    } catch (Exception e) {
        expectedException = e;
    }
    assertNotNull("Setting to an unknown type should throw an exception", expectedException);

}