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:org.works.integration.storedproc.derby.DerbyStoredProcedures.java

public static void findCoffee(int coffeeId, String[] coffeeDescription) throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;

    try {/*from   w  w  w  . j av  a  2s. co  m*/
        connection = DriverManager.getConnection("jdbc:default:connection");
        String sql = "SELECT * FROM COFFEE_BEVERAGES WHERE ID = ? ";
        statement = connection.prepareStatement(sql);
        statement.setLong(1, coffeeId);

        ResultSet resultset = statement.executeQuery();
        resultset.next();
        coffeeDescription[0] = resultset.getString("COFFEE_DESCRIPTION");

    } finally {
        JdbcUtils.closeStatement(statement);
        JdbcUtils.closeConnection(connection);
    }

}

From source file:com.sql.EmailOutInvites.java

/**
 * Get a list of all of the email invites awaiting to be sent.
 * /*from  ww w.j  av a  2 s. c o m*/
 * @return List EmailOutInvitesModel
 */
public static List<EmailOutInvitesModel> getQueuedEmailInvites() {
    List<EmailOutInvitesModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOutInvites";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutInvitesModel item = new EmailOutInvitesModel();
            item.setId(rs.getInt("id"));
            item.setSection(rs.getString("Section") == null ? "" : rs.getString("Section"));
            item.setToAddress(rs.getString("TOaddress") == null ? "" : rs.getString("TOaddress"));
            item.setCcAddress(rs.getString("CCaddress") == null ? "" : rs.getString("CCaddress"));
            item.setEmailBody(rs.getString("emailBody") == null ? "" : rs.getString("emailBody"));
            item.setCaseNumber(rs.getString("caseNumber") == null ? "" : rs.getString("caseNumber"));
            item.setHearingType(rs.getString("hearingType") == null ? "" : rs.getString("hearingType"));
            item.setHearingRoomAbv(
                    rs.getString("hearingRoomAbv") == null ? "" : rs.getString("hearingRoomAbv"));
            item.setHearingDescription(
                    rs.getString("hearingDescription") == null ? "" : rs.getString("hearingDescription"));
            item.setHearingStartTime(
                    CalendarCalculation.adjustTimeZoneOffset(rs.getTimestamp("hearingStartTime")));
            item.setHearingEndTime(CalendarCalculation.adjustTimeZoneOffset(rs.getTimestamp("hearingEndTime")));
            item.setEmailSubject(rs.getString("emailSubject") == null ? "" : rs.getString("emailSubject"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.sql.EmailOutAttachment.java

/**
 * Gathers a list of attachments for a specific email address.
 * /*from   w w w  .  j  a  v a  2 s  . c  o  m*/
 * @param emailID Integer
 * @return List (EmailOutAttachmentModel) 
 */
public static List<EmailOutAttachmentModel> getAttachmentsByEmail(int emailID) {
    List<EmailOutAttachmentModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOutAttachment WHERE emailOutID = ?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, emailID);
        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutAttachmentModel item = new EmailOutAttachmentModel();
            item.setId(rs.getInt("id"));
            item.setEmailOutID(rs.getInt("emailOutID"));
            item.setFileName(rs.getString("fileName"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.l2jfree.gameserver.instancemanager.RaidPointsManager.java

public static void addPoints(L2Player player, int bossId, int points) {
    final Map<Integer, Integer> pointsByBossId = getList(player.getObjectId());

    points += pointsByBossId.containsKey(bossId) ? pointsByBossId.get(bossId).intValue() : 0;

    pointsByBossId.put(bossId, points);/*www  .ja  va2  s. com*/

    Connection con = null;
    try {
        con = L2DatabaseFactory.getInstance().getConnection();
        PreparedStatement statement;
        statement = con.prepareStatement(
                "REPLACE INTO character_raid_points (`charId`,`boss_id`,`points`) VALUES (?,?,?)");
        statement.setInt(1, player.getObjectId());
        statement.setInt(2, bossId);
        statement.setInt(3, points);
        statement.executeUpdate();
        statement.close();
    } catch (Exception e) {
        _log.fatal("could not update char raid points:", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

From source file:com.sql.CaseType.java

/**
 * Gathers a list of active case types for finding the proper section based 
 * on the case number./*www.j  a  va2s.  c  o  m*/
 * 
 * @return List CaseTypeModel
 */
public static List<CaseTypeModel> getCaseTypes() {
    List<CaseTypeModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM CaseType WHERE active = 1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            CaseTypeModel item = new CaseTypeModel();
            item.setId(rs.getInt("id"));
            item.setActive(rs.getBoolean("active"));
            item.setSection(rs.getString("Section"));
            item.setCaseType(rs.getString("caseType"));
            item.setDescription(rs.getString("Description"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.sql.ServerEmailControl.java

/**
 * Update completion time of current thread.
 * //from www  .  j  a v  a  2s  . c  o  m
 * @param column
 */
public static void updateCompletionTime(String column) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "UPDATE ServerEmailControl SET " + column + " = GETDATE() WHERE "
                + "id = (SELECT TOP 1 id FROM ServerEmailControl)";
        ps = conn.prepareStatement(sql);
        ps.executeUpdate();
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(conn);
    }
}

From source file:net.big_oh.common.jdbc.JdbcProxyExerciser.java

private static void exercisePreparedBatchInsert(Connection con) throws SQLException {
    logger.info(StringUtils.center("exercise prepared batch insert", 100, "-"));

    PreparedStatement preparedStmnt = null;
    try {/*from   w  ww  . ja  v  a  2 s . co  m*/
        preparedStmnt = con.prepareStatement("INSERT INTO TEST_TABLE VALUES ( ? )");
        preparedStmnt.setString(1, "value4");
        preparedStmnt.addBatch();
        preparedStmnt.setString(1, "value5");
        preparedStmnt.addBatch();
        preparedStmnt.executeBatch();
    } finally {
        DbUtils.closeQuietly(preparedStmnt);
    }
}

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

/**
 * Return the list of device identifiers associated with this account.
 * @param userinfo//from   ww w . j a  v a  2s  . c  om
 * @param requestParams
 * @return
 * @throws IOException 
 * @throws SQLException 
 * @throws ClassNotFoundException 
 */
public static ReturnResult processRequest(Login.UserInfo userinfo, JSONObject requestParams)
        throws ClassNotFoundException, SQLException, IOException {
    String username = requestParams.getString("username");

    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = Database.get();

        /*
         *    Get user ID
         */

        ps = c.prepareStatement("SELECT userid " + "FROM Users " + "WHERE username = ?");
        ps.setString(1, username);
        rs = ps.executeQuery();

        int userid = 0;
        if (rs.next()) {
            userid = rs.getInt(1);
        } else {
            return new ReturnResult(Errors.ERROR_UNKNOWNUSER, "Unknown user");
        }
        rs.close();
        rs = null;
        ps.close();
        ps = null;

        /*
         * Get devices
         */
        ps = c.prepareStatement("SELECT Devices.deviceuuid, Devices.publickey " + "FROM Devices, Users "
                + "WHERE Users.userid = Devices.userid " + "AND Users.username = ?");
        ps.setString(1, username);
        rs = ps.executeQuery();

        DeviceReturnResult drr = new DeviceReturnResult(userid);
        while (rs.next()) {
            drr.addDeviceUUID(rs.getString(1), rs.getString(2));
        }
        return drr;
    } finally {
        if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (c != null)
            c.close();
    }
}

From source file:com.l2jfree.sql.L2DatabaseInstaller.java

private static void insertRevision(double revision) {
    System.out.println("Saving revision '" + revision + "'.");

    Connection con = null;
    try {//from  www  . ja va 2 s  .c o m
        con = L2Database.getConnection();

        PreparedStatement ps = con.prepareStatement("INSERT INTO _revision VALUES (?,?)");
        ps.setDouble(1, revision);
        ps.setLong(2, System.currentTimeMillis());
        ps.executeUpdate();
        ps.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        L2Database.close(con);
    }

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

From source file:com.sql.EmailOut.java

/**
 * Gathers current emails waiting to be sent out.
 *
 * @return List (EmailOutModel)//from   w w w.  j  a  v  a2 s. c o  m
 */
public static List<EmailOutModel> getEmailOutQueue() {
    List<EmailOutModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT * FROM EmailOut WHERE okToSend = 1";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            EmailOutModel item = new EmailOutModel();
            item.setId(rs.getInt("id"));
            item.setSection(rs.getString("Section"));
            item.setCaseYear(rs.getString("caseYear"));
            item.setCaseType(rs.getString("caseType"));
            item.setCaseMonth(rs.getString("caseMonth"));
            item.setCaseNumber(rs.getString("caseNumber"));
            item.setTo(rs.getString("to"));
            item.setFrom(rs.getString("from"));
            item.setCc(rs.getString("cc"));
            item.setBcc(rs.getString("bcc"));
            item.setSubject(rs.getString("subject"));
            item.setBody(rs.getString("body"));
            item.setUserID(rs.getInt("UserID"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}