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:at.alladin.rmbt.controlServer.StatusResource.java

@Post("json")
public String request(final String entity) {
    addAllowOrigin();//from  w  w  w .  ja  v a  2s .co  m
    JSONObject request = null;

    final ErrorList errorList = new ErrorList();
    final JSONObject answer = new JSONObject();
    String answerString;

    final String clientIpRaw = getIP();
    final InetAddress clientAddress = InetAddresses.forString(clientIpRaw);

    System.out.println(MessageFormat.format(labels.getString("NEW_STATUS_REQ"), clientIpRaw));

    double geolat = 0;
    double geolong = 0;
    float geoaccuracy = 0; // in m
    double geoaltitude = 0;
    float geospeed = 0; // in m/s
    String telephonyNetworkSimOperator = "";
    String telephonyNetworkSimCountry = "";
    boolean homeCountry = true;

    if (entity != null && !entity.isEmpty()) {
        // try parse the string to a JSON object
        try {
            // debug parameters sent
            request = new JSONObject(entity);
            System.out.println(request.toString(4));

            /* sample request
            Status request from 46.206.21.94
            {
             "accuracy": 65,
             "altitude": 233.5172119140625,
             "client": "RMBT",
             "device": "iPhone",
             "language": "de",
             "lat": 48.19530995813387,
             "long": 16.29190354953834,
             "model": "iPhone5,2",
             "name": "RMBT",
             "network_type": 2,
             "os_version": "8.1.2",
             "plattform": "iOS",
             "softwareRevision": "next-858-8491858",
             "softwareVersion": "1.3.7",
             "softwareVersionCode": "1307",
             "speed": 0,
             "telephony_network_sim_country": "at",
             "telephony_network_sim_operator": "232-01",
             "telephony_network_sim_operator_name": "A1",
             "time": 1418241092784,
             "timezone": "Europe/Vienna",
             "type": "MOBILE",
             "uuid": "1cf594f6-0d07-4ff6-acd3-3d78ed9c0274",
             "version": "0.3"
            }
            */

            geolat = request.optDouble("lat", 0);
            geolong = request.optDouble("long", 0);
            geoaccuracy = (float) request.optDouble("accuracy", 0);
            geoaltitude = request.optDouble("altitude", 0);
            geospeed = (float) request.optDouble("speed", 0);
            telephonyNetworkSimOperator = request.optString("telephony_network_sim_operator", "");
            telephonyNetworkSimCountry = request.optString("telephony_network_sim_country", "");

        } catch (final JSONException e) {
            errorList.addError("ERROR_REQUEST_JSON");
            System.out.println("Error parsing JSON Data " + e.toString());
        }
    } else {
        errorList.addErrorString("Expected request is missing.");
    }
    /*
     * 
     * wb_a2 = 'AT' (2 digit country code)
     * ne_50m_admin_0_countries (SRID 900913 Mercator)
     * lat/long (SRID 4326 WGS84)
     * 
     * SELECT st_contains(the_geom, ST_transform(ST_GeomFromText('POINT(56.391944 48.218056)',4326),900913)) home_country
     * FROM  ne_50m_admin_0_countries 
     * WHERE wb_a2='AT';
     * 
     *
     * */
    try {
        PreparedStatement st;
        st = conn.prepareStatement(
                " SELECT st_contains(the_geom, ST_TRANSFORM(ST_GeomFromText( ? ,4326),900913)) home_country "
                        + " FROM  ne_50m_admin_0_countries " + " WHERE wb_a2 = ? ");
        int i = 1;
        final String point = "POINT(" + String.valueOf(geolong) + " " + String.valueOf(geolat) + ")";
        st.setObject(i++, point);
        st.setObject(i++, telephonyNetworkSimCountry.toUpperCase());

        //debug query
        System.out.println(st.toString());
        final ResultSet rs = st.executeQuery();

        if (rs.next()) // result only available if country (wb_a2) is found in ne_50_admmin_0_countries
            homeCountry = rs.getBoolean("home_country");

    } catch (final SQLException e) {
        errorList.addError("ERROR_DB_GENERAL");
        e.printStackTrace();
    }

    try {

        answer.put("home_country", homeCountry);
        answer.putOpt("error", errorList.getList());
    } catch (final JSONException e) {
        System.out.println("Error saving ErrorList: " + e.toString());
    }

    answerString = answer.toString();

    return answerString;
}

From source file:org.biokoframework.system.repository.sql.SqlRepository.java

@Override
public DE retrieve(String anEntityKey) {
    ArrayList<DE> entities = new ArrayList<DE>();
    Connection connection = null;
    PreparedStatement retrieveStatement = null;
    try {//from  w w w . jav  a  2  s  . co  m
        connection = fDbConnector.getConnection();
        retrieveStatement = SqlStatementsHelper.preparedRetrieveByIdStatement(fEntityClass, fTableName,
                connection);
        retrieveStatement.setObject(1, anEntityKey);
        retrieveStatement.execute();

        entities = SqlStatementsHelper.retrieveEntities(retrieveStatement.getResultSet(), fEntityClass,
                fTranslator, fEntityBuilderService);
    } catch (SQLException exception) {
        exception.printStackTrace();
    } finally {
        closeDumbSql(connection, retrieveStatement, null);
    }
    if (entities.isEmpty()) {
        return null;
    } else {
        return entities.get(0);
    }
}

From source file:com.dsf.dbxtract.cdc.journal.JournalExecutor.java

/**
 * Removes imported references from journal table.
 * /*from www .ja  v  a 2  s .  co m*/
 * @param conn
 * @param rows
 * @throws SQLException
 */
private void deleteFromJournal(Connection conn, List<Map<String, Object>> rows) throws SQLException {

    if (rows.isEmpty()) {
        if (logger.isDebugEnabled())
            logger.debug(logPrefix + "nothing to clean");
        return;
    }

    if (logger.isDebugEnabled())
        logger.debug(logPrefix + "cleaning journal " + handler.getJournalTable());

    StringBuilder sb = new StringBuilder("delete from " + handler.getJournalTable() + " where ");
    for (int i = 0; i < journalColumns.size(); i++) {
        sb.append(i > 0 ? " and " : "").append(journalColumns.get(i)).append("=?");
    }
    PreparedStatement ps = null;
    try {
        ps = conn.prepareStatement(sb.toString());
        for (Map<String, Object> keys : rows) {
            for (int i = 0; i < journalColumns.size(); i++) {
                ps.setObject(i + 1, keys.get(journalColumns.get(i)));
            }
            ps.addBatch();
        }
        ps.executeBatch();
        logger.info(logPrefix + rows.size() + " rows removed (" + handler.getJournalTable() + ")");

    } finally {
        DBUtils.close(ps);
    }
}

From source file:com.runwaysdk.system.metadata.ontology.PostgresOntolgoyDatabase.java

/**
 * Executes the given SQL and manages the connection. This takes in a variable number of prepared statement arguments that are assigned in order: <code>
 * preparedStatement.setObject(1, args[0]);
 * preparedStatement.setObject(2, args[1]);
 * </code> ... and so on./* w  ww . j ava  2s.c om*/
 * 
 * @param sql
 * @param args
 * @return The number of rows updated
 */
private int execute(String sql, Object... args) {
    Connection conn = Database.getConnection();

    PreparedStatement prepared = null;

    try {
        prepared = conn.prepareStatement(sql);

        // prepared statements start counting at 1, not 0.
        int queryIndex = 1;
        for (Object arg : args) {
            prepared.setObject(queryIndex++, arg);
        }

        return prepared.executeUpdate();
    } catch (SQLException e) {
        throw new DatabaseException(e);
    } finally {
        if (prepared != null) {
            try {
                prepared.close();
            } catch (SQLException e) {
                throw new DatabaseException(e);
            }
        }
    }
}

From source file:org.biokoframework.system.repository.sql.SqlRepository.java

@Override
public ArrayList<DE> getEntitiesByForeignKey(String foreignKeyName, Object foreignKeyValue) {
    ArrayList<DE> entities = new ArrayList<DE>();
    Connection connection = null;
    PreparedStatement retrieveStatement = null;
    try {//from  w w w .  j  a  va  2  s.c om
        connection = fDbConnector.getConnection();
        retrieveStatement = SqlStatementsHelper.prepareRetrieveByForeignKey(fEntityClass, fTableName,
                connection, foreignKeyName);

        retrieveStatement.setObject(1, foreignKeyValue);
        retrieveStatement.execute();

        entities = SqlStatementsHelper.retrieveEntities(retrieveStatement.getResultSet(), fEntityClass,
                fTranslator, fEntityBuilderService);
    } catch (SQLException exception) {
        exception.printStackTrace();
    } finally {
        closeDumbSql(connection, retrieveStatement, null);
    }
    return entities;
}

From source file:org.apache.hadoop.sqoop.manager.SqlManager.java

/**
 * executes an arbitrary SQL statement//from w  ww. j  a  v  a 2 s.  c  o  m
 * @param stmt The SQL statement to execute
 * @return A ResultSet encapsulating the results or null on error
 */
protected ResultSet execute(String stmt, Object... args) {
    if (null == stmt) {
        LOG.error("Null statement sent to SqlManager.execute()");
        return null;
    }

    PreparedStatement statement = null;
    try {
        statement = this.getConnection().prepareStatement(stmt, ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        if (null != args) {
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
        }

        LOG.info("Executing SQL statement: " + stmt);
        return statement.executeQuery();
    } catch (SQLException sqlException) {
        LOG.error("Error returned by SQL database: " + sqlException.toString());
        return null;
    }

    // TODO(aaron): Is calling ResultSet.close() sufficient?
    // Or must statement.close() be called too?
}

From source file:och.comp.db.base.universal.UniversalQueries.java

private List<Object> trySelect(SelectRows<?> select) throws Exception {

    lastQuery.remove();//from   w  w  w .  java2s.com

    Class<?>[] fieldTypes = select.selectFields.fieldTypes;
    WhereCondition condition = select.whereCondition;

    if (isEmpty(fieldTypes))
        return emptyList();

    StringBuilder sb = new StringBuilder();
    sb.append("SELECT");
    {
        boolean isFirst = true;
        for (Class<?> type : fieldTypes) {
            if (!isFirst)
                sb.append(',');
            else
                isFirst = false;
            sb.append(' ').append(fieldName(type));
        }
    }
    sb.append("\n FROM ").append(select.table);
    appendWhereCondition(sb, condition);
    if (select.sortCondition != null)
        appendSortCondition(sb, select.sortCondition);
    if (select.limit != null)
        sb.append("\n LIMIT ").append(select.limit);
    if (select.offset != null)
        sb.append("\n OFFSET ").append(select.offset);
    sb.append(';');

    try (Connection conn = getSingleOrNewConnection(ds)) {

        String q = sb.toString();
        lastQuery.set(q);

        PreparedStatement ps = conn.prepareStatement(q);
        if (!isEmpty(condition)) {
            RowField<?>[] values = condition.values();
            for (int i = 0; i < values.length; i++) {
                ps.setObject(i + 1, values[i].value);
            }
        }

        long startTime = System.currentTimeMillis();
        logQueryStart(q);

        ResultSet rs = ps.executeQuery();

        logQueryEnd(startTime);

        ArrayList<Object> out = new ArrayList<>();
        while (rs.next()) {
            Object entity = mapper.createEntity(rs, select.resultType, fieldTypes);
            out.add(entity);
        }

        ps.close();

        return out;
    }

}

From source file:org.biokoframework.system.repository.sql.SqlRepository.java

@Override
public DE retrieveByForeignKey(String foreignKeyName, Object foreignKeyValue) {
    ArrayList<DE> entities = new ArrayList<DE>();
    Connection connection = null;
    PreparedStatement retrieveStatement = null;
    try {/*www .ja va2  s . co m*/
        connection = fDbConnector.getConnection();
        retrieveStatement = SqlStatementsHelper.prepareRetrieveOneByForeignKey(fEntityClass, fTableName,
                connection, foreignKeyName);
        retrieveStatement.setObject(1, foreignKeyValue);
        retrieveStatement.execute();

        entities = SqlStatementsHelper.retrieveEntities(retrieveStatement.getResultSet(), fEntityClass,
                fTranslator, fEntityBuilderService);
        retrieveStatement.close();
        connection.close();
    } catch (SQLException exception) {
        LOGGER.error("Retrieve:", exception);
        exception.printStackTrace();
    } finally {
        closeDumbSql(connection, retrieveStatement, null);
    }
    if (entities.isEmpty()) {
        return null;
    } else {
        return entities.get(0);
    }
}

From source file:org.efaps.admin.AbstractAdminObject.java

/**
 * The instance method reads the properties for this administration object.
 * Each found property is set with instance method {@link #setProperty}.
 *
 * @throws CacheReloadException on error
 *
 * @see #setProperty/*w w w  . j  a  va2 s .  c o m*/
 */
protected void readFromDB4Properties() throws CacheReloadException {
    ConnectionResource con = null;
    try {
        con = Context.getThreadContext().getConnectionResource();
        final PreparedStatement stmt = con.getConnection().prepareStatement(AbstractAdminObject.SELECT);
        stmt.setObject(1, getId());
        final ResultSet rs = stmt.executeQuery();
        AbstractAdminObject.LOG.debug("Reading Properties for '{}'", getName());
        while (rs.next()) {
            final String nameStr = rs.getString(1).trim();
            final String value = rs.getString(2).trim();
            setProperty(nameStr, value);
            AbstractAdminObject.LOG.debug("    Name: '{}' - Value: '{}'", new Object[] { nameStr, value });
        }
        rs.close();
        stmt.close();
        if (con.isOpened()) {
            con.commit();
        }
    } catch (final SQLException e) {
        throw new CacheReloadException("could not read properties for " + "'" + getName() + "'", e);
    } catch (final EFapsException e) {
        throw new CacheReloadException("could not read properties for " + "'" + getName() + "'", e);
    } finally {
        if (con != null && con.isOpened()) {
            try {
                con.abort();
            } catch (final EFapsException e) {
                throw new CacheReloadException("could not read properties for " + "'" + getName() + "'", e);
            }
        }
    }
}

From source file:com.yahoo.flowetl.commons.db.actions.UpdateAction.java

@Override
public Integer applyAction(Connection db) {
    PreparedStatement st = null;
    try {//from  www .  j  ava  2 s  .  co  m
        String sql = getSql();
        try {
            st = db.prepareStatement(sql);
        } catch (SQLException e) {
            throw new CoreException("Unable to prepare update action for query " + sql, e);
        }
        List<Object> binds = getBindings();
        if (binds != null && binds.isEmpty() == false) {
            for (int i = 0; i < binds.size(); i++) {
                Object o = binds.get(i);
                try {
                    st.setObject((i + 1), o);
                } catch (SQLException e) {
                    throw new CoreException(
                            "Unable to bind param " + (i + 1) + " for sql " + sql + " with param " + o, e);
                }
            }
        }
        try {
            logger.log(Level.DEBUG, "Executing %s with bound params %s", sql, binds);
            int res = st.executeUpdate();
            logger.log(Level.DEBUG, "Executing %s affected %s rows", sql, res);
            return res;
        } catch (SQLException e) {
            throw new CoreException(
                    "Unable to execute update for sql " + sql + " with params " + StringUtils.join(binds, ","),
                    e);
        }
    } finally {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                logger.log(Level.WARN, "Error closing update action prepared statement", e);
            }
        }
    }
}