Example usage for java.sql Connection prepareStatement

List of usage examples for java.sql Connection prepareStatement

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareStatement

Introduction

In this page you can find the example usage for java.sql Connection prepareStatement.

Prototype

PreparedStatement prepareStatement(String sql) throws SQLException;

Source Link

Document

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

Usage

From source file:io.kazuki.v0.internal.helper.TestHelper.java

public static void dropSchema(DataSource database) {
    Connection conn = null;
    try {/*from w w w  .  jav a2  s .  c o m*/
        conn = database.getConnection();
        conn.prepareStatement("DROP ALL OBJECTS").execute();
        conn.commit();
    } catch (Exception e) {
        throw Throwables.propagate(e);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                throw Throwables.propagate(e);
            }
        }
    }

}

From source file:com.chaosinmotion.securechat.server.commands.RemoveDevice.java

public static boolean processRequest(Login.UserInfo userinfo, JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    String deviceid = requestParams.optString("deviceid");

    /*//w  w w  .ja va2  s .c om
     * Delete device. We only delete if it is also ours.
     */
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = Database.get();
        ps = c.prepareStatement("DELETE FROM Devices " + "WHERE userid = ? AND deviceuuid = ?");
        ps.setInt(1, userinfo.getUserID());
        ps.setString(2, deviceid);
        ps.execute();

        return true;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:com.l2jfree.gameserver.model.GMAudit.java

public static void auditGMAction(String gm_name, String target, String type, String action, String param) {
    if (Config.GM_AUDIT) {
        Connection con = null;
        try {//from   www .  j  a  va 2s.  co m
            con = L2DatabaseFactory.getInstance().getConnection(con);
            PreparedStatement statement = con.prepareStatement(
                    "INSERT INTO gm_audit(gm_name, target, type, action, param, date) VALUES(?,?,?,?,?,now())");

            statement.setString(1, gm_name);
            statement.setString(2, target);
            statement.setString(3, type);
            statement.setString(4, action);
            statement.setString(5, param);

            statement.executeUpdate();
        } catch (Exception e) {
            _log.fatal("", e);
        } finally {
            L2DatabaseFactory.close(con);
        }
    }
}

From source file:Main.java

public static String getCLOB(int id) throws Exception {
    Connection conn = null;
    ResultSet rs = null;/*from  w w w.  j av  a  2 s. c om*/
    PreparedStatement pstmt = null;
    String query = "SELECT clobData FROM tableName WHERE id = ?";

    try {
        conn = getConnection();
        pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, id);
        rs = pstmt.executeQuery();
        rs.next();
        Clob clob = rs.getClob(1);
        // materialize CLOB onto client
        String wholeClob = clob.getSubString(1, (int) clob.length());
        return wholeClob;
    } finally {
        rs.close();
        pstmt.close();
        conn.close();
    }
}

From source file:Main.java

/**
 * <p>//from  w w w  . ja va  2  s.  co m
 * Execute a DDL statement
 * </p>
 */
public static void executeDDL(String ddl) throws SQLException {
    Connection conn = getLocalConnection();

    // now register the function
    print(ddl);
    try {
        PreparedStatement createStatement = conn.prepareStatement(ddl);

        createStatement.execute();
        createStatement.close();
    } catch (SQLException t) {
        SQLException s = new SQLException("Could not execute DDL:\n" + ddl);

        s.setNextException(t);

        throw s;
    }
}

From source file:ImageStringToBlob.java

private static int writeBlobToDb(Connection conn, Long id, Blob dataBlob) throws Exception {
    String sql = "update client_image set contents = ? where image_id = ?";
    PreparedStatement pst = conn.prepareStatement(sql);
    pst.setBlob(1, dataBlob);/*from  ww  w . j  av  a  2s .  c  o  m*/
    pst.setLong(2, id);

    return pst.executeUpdate();
}

From source file:au.org.ala.sds.GeneraliseOccurrenceLocations.java

private static void run(String startAt) throws SQLException, SearchResultException {
    Connection conn = occurrenceDataSource.getConnection();
    PreparedStatement pst = conn.prepareStatement(
            "SELECT id, scientific_name, latitude, longitude, generalised_metres, raw_latitude, raw_longitude FROM raw_occurrence_record LIMIT ?,?");
    int offset = startAt == null ? 0 : Integer.parseInt(startAt);
    int stride = 10000;
    int recCount = 0;
    pst.setInt(2, stride);/* w  w  w.ja va 2s  .co  m*/
    ResultSet rs;

    for (pst.setInt(1, offset); true; offset += stride, pst.setInt(1, offset)) {
        rs = pst.executeQuery();
        if (!rs.isBeforeFirst()) {
            break;
        }
        while (rs.next()) {
            recCount++;

            String rawScientificName = (rs.getString("scientific_name"));
            int id = rs.getInt("id");
            String latitude = rs.getString("latitude");
            String longitude = rs.getString("longitude");
            String generalised_metres = rs.getString("generalised_metres");
            String raw_latitude = rs.getString("raw_latitude");
            String raw_longitude = rs.getString("raw_longitude");

            if (StringUtils.isEmpty(rawScientificName))
                continue;
            if (StringUtils.isEmpty(latitude) || StringUtils.isEmpty(longitude))
                continue;

            // See if it's sensitive
            SensitiveTaxon ss = sensitiveSpeciesFinder.findSensitiveSpecies(rawScientificName);
            if (ss != null) {
                Map<String, String> facts = new HashMap<String, String>();
                facts.put(FactCollection.DECIMAL_LATITUDE_KEY, latitude);
                facts.put(FactCollection.DECIMAL_LONGITUDE_KEY, longitude);

                ValidationService service = ServiceFactory.createValidationService(ss);
                ValidationOutcome outcome = service.validate(facts);
                Map<String, Object> result = outcome.getResult();

                String speciesName = ss.getTaxonName();
                if (StringUtils.isNotEmpty(ss.getCommonName())) {
                    speciesName += " [" + ss.getCommonName() + "]";
                }

                if (!result.get("decimalLatitude").equals(facts.get("decimalLatitude"))
                        || !result.get("decimalLongitude").equals(facts.get("decimalLongitude"))) {
                    if (StringUtils.isEmpty(generalised_metres)) {
                        logger.info("Generalising location for " + id + " '" + rawScientificName
                                + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                                + ", Long=" + result.get("decimalLongitude"));
                        //rawOccurrenceDao.updateLocation(id, result.get("decimalLatitude"), result.get("decimalLongitude"), result.getGeneralisationInMetres(), latitude, longitude);
                    } else {
                        if (generalised_metres != result.get("generalisationInMetres")) {
                            logger.info("Re-generalising location for " + id + " '" + rawScientificName
                                    + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                                    + ", Long=" + result.get("decimalLongitude"));
                            //rawOccurrenceDao.updateLocation(id, result.get("decimalLatitude"), result.get("decimalLongitude"), result.getGeneralisationInMetres());
                        }
                    }
                } else {
                    logger.info("Not generalising location for " + id + " '" + rawScientificName
                            + "' using Name='" + speciesName + "', Lat=" + result.get("decimalLatitude")
                            + ", Long=" + result.get("decimalLongitude") + " - "
                            + result.get("dataGeneralizations"));
                }
            } else {
                // See if was sensitive but not now
                if (StringUtils.isNotEmpty(generalised_metres)) {
                    logger.info("De-generalising location for " + id + " '" + rawScientificName + "', Lat="
                            + raw_latitude + ", Long=" + raw_longitude);
                    //rawOccurrenceDao.updateLocation(id, raw_latitude, raw_longitude, null, null, null);
                }
            }
        }
        rs.close();
        logger.info("Processed " + recCount + " occurrence records.");
    }

    rs.close();
    pst.close();
    conn.close();
}

From source file:com.concursive.connect.web.modules.activity.utils.ProjectHistoryUtils.java

public static int findNextPosition(Connection db, int projectHistoryId) throws SQLException {
    int position = 0;
    PreparedStatement pst = db.prepareStatement("SELECT max(position) AS position " + "FROM project_history "
            + "WHERE history_id = ? OR top_id = ? ");
    pst.setInt(1, projectHistoryId);/*w ww.j  ava2  s  .  c  om*/
    pst.setInt(2, projectHistoryId);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        position = rs.getInt("position");
    }
    rs.close();
    pst.close();
    return (position + 1);
}

From source file:com.alibaba.druid.pool.bonecp.TestPSCache.java

public static void f(DataSource ds, int count) throws Exception {
    Connection conn = ds.getConnection();

    for (int i = 0; i < count; ++i) {
        PreparedStatement stmt = conn.prepareStatement("SELECT 1");
        System.out.println(System.identityHashCode(unwrap(stmt)));
        stmt.close();/*from  ww  w. ja  v  a 2  s . com*/
    }

    conn.close();
}

From source file:at.molindo.dbcopy.util.Utils.java

public static <T> T executePrepared(Connection c, String query, ResultSetHandler<T> handler, Object... params)
        throws SQLException {
    PreparedStatement stmt = c.prepareStatement(query);
    try {/* w w w.  j a v  a2  s  .co  m*/
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
        ResultSet rs = stmt.executeQuery();
        return handle(rs, handler);
    } finally {
        stmt.close();
    }
}