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:herddb.cli.HerdDBCLI.java

@SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
private static void executeStatementInSqlFile(String query, final Statement statement,
        SqlFileStatus sqlFileStatus) throws SQLException, ScriptException {
    query = query.trim();//www.j  a va2 s.c  om

    if (query.isEmpty() || query.startsWith("--")) {
        return;
    }
    String formattedQuery = query.toLowerCase();
    if (formattedQuery.endsWith(";")) {
        // mysqldump
        formattedQuery = formattedQuery.substring(0, formattedQuery.length() - 1);
    }
    if (formattedQuery.equals("exit") || formattedQuery.equals("quit")) {
        throw new SQLException("explicit END of script with '" + formattedQuery + "'");
    }
    if (sqlFileStatus.frommysqldump
            && (formattedQuery.startsWith("lock tables") || formattedQuery.startsWith("unlock tables"))) {
        // mysqldump
        return;
    }
    Boolean setAutoCommit = null;
    if (formattedQuery.startsWith("autocommit=")) {
        String value = "";
        if (formattedQuery.split("=").length > 1) {
            value = formattedQuery.split("=")[1];
        }
        switch (value) {
        case "true":
            setAutoCommit = true;
            break;
        case "false":
            setAutoCommit = false;
            break;
        default:
            System.out.println("No valid value for autocommit. Only true and false allowed.");
            return;
        }
    }
    if (sqlFileStatus.verbose) {
        System.out.println("Executing query:" + query);
    }
    try {
        if (setAutoCommit != null) {
            statement.getConnection().setAutoCommit(setAutoCommit);
            System.out.println("Set autocommit=" + setAutoCommit + " executed.");
            return;
        }
        if (formattedQuery.equals("commit")) {
            sqlFileStatus.flushAndCommit(statement.getConnection());

            System.out.println("Commit executed.");
            return;
        }
        if (formattedQuery.equals("rollback")) {
            sqlFileStatus.rollback(statement.getConnection());
            statement.getConnection().rollback();
            System.out.println("Rollback executed.");
            return;
        }

        QueryWithParameters rewritten = null;
        if (sqlFileStatus.rewritestatements) {
            rewritten = rewriteQuery(query, sqlFileStatus.tableSpaceMapper, sqlFileStatus.frommysqldump);
        }
        if (rewritten != null) {
            if (rewritten.schema != null) {
                HerdDBConnection connection = statement.getConnection().unwrap(HerdDBConnection.class);
                if (connection != null && !connection.getSchema().equalsIgnoreCase(rewritten.schema)) {
                    sqlFileStatus.flushAndCommit(connection);
                    commitAndChangeSchema(connection, rewritten.schema);
                }
            }
            PreparedStatement ps = sqlFileStatus.prepareStatement(statement.getConnection(), rewritten.query);
            int i = 1;
            for (Object o : rewritten.jdbcParameters) {
                ps.setObject(i++, o);
            }
            ps.addBatch();
        } else {
            PreparedStatement ps = sqlFileStatus.prepareStatement(statement.getConnection(), query);
            ps.addBatch();
        }
        sqlFileStatus.countPendingOp();
    } catch (SQLException err) {
        if (sqlFileStatus.ignoreerrors) {
            println("ERROR:" + err);
            return;
        } else {
            throw err;
        }
    }
}

From source file:Classes.Database.java

private void EscapeSQL(PreparedStatement preparedStatement, Object... arguments) throws SQLException {
    int i = 0;/* w  ww .  j a  va2s  . co m*/
    for (Object obj : arguments) {
        i++;

        if (obj != null && obj.getClass() == Date.class) // when the argument is a date then convert to a timestamp
        {
            preparedStatement.setTimestamp(i, DateToTimestamp((Date) obj));
        } else if (obj != null && obj.getClass() == String.class) // when the argument is a string then escap all html4 stuff
        {
            preparedStatement.setObject(i, escapeHtml4((String) obj));
        } else {
            preparedStatement.setObject(i, obj);
        }
    }
}

From source file:org.efaps.admin.user.Person.java

/**
 * @param _sql      SQL Statment to be execuetd
 * @param _criteria filter criteria/*from   www. j a va2  s .c  om*/
 * @return true if successful
 * @throws EFapsException on error
 */
@SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
private static Person getPersonFromDB(final String _sql, final Object _criteria) throws EFapsException {
    Person ret = null;
    ConnectionResource con = null;
    try {
        con = Context.getThreadContext().getConnectionResource();
        PreparedStatement stmt = null;
        try {
            stmt = con.getConnection().prepareStatement(_sql);
            stmt.setObject(1, _criteria);
            final ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                final long id = rs.getLong(1);
                final String uuid = rs.getString(2);
                final String name = rs.getString(3);
                final boolean status = rs.getBoolean(4);
                ret = new Person(id, uuid, name.trim(), status);
                Person.cachePerson(ret);
                Person.LOG.debug("read from DB Person:{} ", ret);
            }
            rs.close();
        } catch (final SQLException e) {
            Person.LOG.error("search for person with SQL statement '" + _sql + "' is not possible", e);
            throw new EFapsException(Person.class, "getFromDB.SQLException", e, _sql);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (final SQLException e) {
                Person.LOG.error("Catched error on closing statement", e);
            }
            if (con != null) {
                con.commit();
            }
        }
    } finally {
        if (con != null && con.isOpened()) {
            con.abort();
        }
    }
    if (ret != null) {
        ret.readFromDB();
    }
    return ret;
}

From source file:com.mirth.connect.server.util.DatabaseConnection.java

public CachedRowSet executeCachedQuery(String expression, List<Object> parameters) throws SQLException {
    PreparedStatement statement = null;

    try {/*w ww. ja  va  2s .  c o m*/
        statement = connection.prepareStatement(expression);
        logger.debug("executing prepared statement:\n" + expression);

        ListIterator<Object> iterator = parameters.listIterator();

        while (iterator.hasNext()) {
            int index = iterator.nextIndex() + 1;
            Object value = iterator.next();
            logger.debug("adding parameter: index=" + index + ", value=" + value);
            statement.setObject(index, value);
        }

        ResultSet result = statement.executeQuery();
        CachedRowSetImpl crs = new CachedRowSetImpl();
        crs.populate(result);
        DbUtils.closeQuietly(result);
        return crs;
    } catch (SQLException e) {
        throw e;
    } finally {
        DbUtils.closeQuietly(statement);
    }
}

From source file:com.geodetix.geo.dao.PostGisGeometryDAOImpl.java

/**
 * PostGIS implementation of the entity bean's lifecicle method 
 * <code>ejbStore()</code>./*from   w ww.j av  a 2  s.  c om*/
 * 
 * @param ejb the ejb whose data must be stored.
 * @throws javax.ejb.EJBException launched if a generic EJB error is encountered.
 */
public void store(com.geodetix.geo.ejb.GeometryBean ejb) throws javax.ejb.EJBException {

    PreparedStatement pstm = null;
    Connection con = null;

    try {

        con = this.dataSource.getConnection();

        pstm = con.prepareStatement(PostGisGeometryDAO.EJB_STORE_STATEMENT);

        pstm.setObject(1, new PGgeometry(ejb.getGeometry()));
        pstm.setString(2, ejb.getDescription());
        pstm.setInt(3, ejb.getId().intValue());

        if (pstm.executeUpdate() != 1) {
            throw new EJBException("ejbStore unable to update EJB.");
        }

    } catch (SQLException se) {

        throw new EJBException(se);

    } finally {

        try {
            if (pstm != null) {
                pstm.close();
            }
        } catch (Exception e) {
        }

        try {
            if (con != null) {
                con.close();
            }

        } catch (Exception e) {
        }
    }
}

From source file:nl.b3p.catalog.arcgis.ArcSDE10JDBCHelper.java

@Override
public void saveMetadata(ArcSDEJDBCDataset dataset, String metadata) throws Exception {
    Connection c = getConnection();
    PreparedStatement ps = null;
    try {/*from   w w  w. java2  s.  c  o m*/

        // Sloop encoding uit XML declaratie, anders geeft MSSQL error 
        // "unable to switch the encoding" op column type xml

        Document doc = DocumentHelper.getMetadataDocument(metadata);
        metadata = new XMLOutputter(Format.getPrettyFormat().setOmitEncoding(true)).outputString(doc);

        String sql = "update " + getTableName(TABLE_ITEMS) + " set documentation = ? where objectid = ?";
        ps = c.prepareStatement(sql);
        ps.setCharacterStream(1, new StringReader(metadata), metadata.length());
        ps.setObject(2, dataset.getObjectID());
        int rowsAffected = ps.executeUpdate();
        if (rowsAffected != 1) {
            throw new Exception("Updating metadata should affect maximum one row; got rows affected count of "
                    + rowsAffected);
        }
    } finally {
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(c);
    }
}

From source file:org.efaps.admin.user.Person.java

/**
 * Returns for given parameter <i>_jaasKey</i> the instance of class
 * {@link Person}. The parameter <i>_jaasKey</i> is the name of the person
 * used in the given JAAS system for the person.
 *
 * @param _jaasSystem JAAS system for which the JAAS key is named
 * @param _jaasKey key in the foreign JAAS system for which the person is
 *            searched/*from  ww  w  .  ja  v a  2  s .c o  m*/
 * @throws EFapsException on error
 * @return instance of class {@link Person}, or <code>null</code> if person
 *         is not found
 * @see #get(long)
 */
public static Person getWithJAASKey(final JAASSystem _jaasSystem, final String _jaasKey) throws EFapsException {
    long personId = 0;
    ConnectionResource rsrc = null;
    try {
        rsrc = Context.getThreadContext().getConnectionResource();
        PreparedStatement stmt = null;
        try {
            stmt = rsrc.getConnection().prepareStatement(Person.SQL_JAASKEY);
            stmt.setObject(1, _jaasKey);
            stmt.setObject(2, _jaasSystem.getId());
            final ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                personId = rs.getLong(1);
            }
            rs.close();
        } catch (final SQLException e) {
            Person.LOG.error("search for person for JAAS system '" + _jaasSystem.getName() + "' with key '"
                    + _jaasKey + "' is not possible", e);
            throw new EFapsException(Person.class, "getWithJAASKey.SQLException", e, _jaasSystem.getName(),
                    _jaasKey);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (final SQLException e) {
                throw new EFapsException(Person.class, "getWithJAASKey.SQLException", e, _jaasSystem.getName(),
                        _jaasKey);
            }
        }
        rsrc.commit();
    } finally {
        if (rsrc != null && rsrc.isOpened()) {
            rsrc.abort();
        }
    }
    return Person.get(personId);
}

From source file:org.finra.dm.dao.Log4jOverridableConfigurerTest.java

/**
 * Executes a SQL prepared statement with the specified arguments.
 *
 * @param sql the SQL statement./*  ww  w.  j a  v a2s .  c  om*/
 * @param arguments the arguments.
 *
 * @throws SQLException if any SQL errors were encountered.
 */
private void executePreparedStatement(String sql, Object... arguments) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        DataSource dataSource = DaoSpringModuleConfig.getDmDataSource();
        connection = dataSource.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < arguments.length; i++) {
            preparedStatement.setObject(i + 1, arguments[i]);
        }
        preparedStatement.execute();
    } finally {
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

From source file:at.alladin.rmbt.controlServer.IpResource.java

@Post("json")
public String request(final String entity) {
    addAllowOrigin();//  w  w w .j a va 2 s  . c o 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_IP_REQ"), clientIpRaw));

    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 data
            {
                "api_level": "21",
                "device": "hammerhead",
                "language": "en",
                "model": "Nexus 5",
                "os_version": "5.0(1570415)",
                "plattform": "Android",
                "product": "hammerhead",
                "softwareRevision": "master_initial-2413-gf89049d",
                "softwareVersionCode": 20046,
                "softwareVersionName": "2.0.46",
                "timezone": "Europe/Vienna",
                "type": "MOBILE",
                "uuid": "........(uuid)........"
                "location": {
            "accuracy": 20,
            "age": 7740,
            "lat": 51.1053539,
            "long": 17.4921002,
            "provider": "network"
             },
            }
            */
            UUID uuid = null;
            final String uuidString = request.optString("uuid", "");
            if (uuidString.length() != 0)
                uuid = UUID.fromString(uuidString);

            final String clientPlattform = request.getString("plattform");
            final String clientModel = request.getString("model");
            final String clientProduct = request.getString("product");
            final String clientDevice = request.getString("device");
            final String clientSoftwareVersionCode = request.getString("softwareVersionCode");
            final String clientApiLevel = request.getString("api_level");

            final JSONObject location = request.optJSONObject("location");

            long geoage = 0; // age in ms
            double geolat = 0;
            double geolong = 0;
            float geoaccuracy = 0; // in m
            double geoaltitude = 0;
            float geospeed = 0; // in m/s
            String geoprovider = "";

            if (!request.isNull("location")) {
                geoage = location.optLong("age", 0);
                geolat = location.optDouble("lat", 0);
                geolong = location.optDouble("long", 0);
                geoaccuracy = (float) location.optDouble("accuracy", 0);
                geoaltitude = location.optDouble("altitude", 0);
                geospeed = (float) location.optDouble("speed", 0);
                geoprovider = location.optString("provider", "");
            }

            if (errorList.getLength() == 0)
                try {
                    PreparedStatement st;
                    st = conn.prepareStatement(
                            "INSERT INTO status(client_uuid,time,plattform,model,product,device,software_version_code,api_level,ip,"
                                    + "age,lat,long,accuracy,altitude,speed,provider)"
                                    + "VALUES(?, NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                            Statement.RETURN_GENERATED_KEYS);
                    int i = 1;
                    st.setObject(i++, uuid);
                    st.setObject(i++, clientPlattform);
                    st.setObject(i++, clientModel);
                    st.setObject(i++, clientProduct);
                    st.setObject(i++, clientDevice);
                    st.setObject(i++, clientSoftwareVersionCode);
                    st.setObject(i++, clientApiLevel);
                    st.setObject(i++, clientIpRaw);
                    // location information
                    st.setObject(i++, geoage);
                    st.setObject(i++, geolat);
                    st.setObject(i++, geolong);
                    st.setObject(i++, geoaccuracy);
                    st.setObject(i++, geoaltitude);
                    st.setObject(i++, geospeed);
                    st.setObject(i++, geoprovider);

                    final int affectedRows = st.executeUpdate();
                    if (affectedRows == 0)
                        errorList.addError("ERROR_DB_STORE_STATUS");
                } catch (final SQLException e) {
                    errorList.addError("ERROR_DB_STORE_GENERAL");
                    e.printStackTrace();
                }

            answer.put("ip", clientIpRaw);
            if (clientAddress instanceof Inet4Address) {
                answer.put("v", "4");
            } else if (clientAddress instanceof Inet6Address) {
                answer.put("v", "6");
            } else {
                answer.put("v", "0");
            }
        } 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.");
    }

    try {
        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:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testUUID() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("create table test_uuid(id uuid primary key)");
    UUID uuid = new UUID(-2, -1);
    PreparedStatement prep = conn.prepareStatement("insert into test_uuid values(?)");
    prep.setObject(1, uuid);
    prep.execute();/* ww w .  jav  a 2 s  .  c  o m*/
    ResultSet rs = stat.executeQuery("select * from test_uuid");
    rs.next();
    assertEquals("ffffffff-ffff-fffe-ffff-ffffffffffff", rs.getString(1));
    Object o = rs.getObject(1);
    assertEquals("java.util.UUID", o.getClass().getName());
    stat.execute("drop table test_uuid");
}