Example usage for java.sql Connection 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:ips1ap101.lib.core.db.util.DB.java

public static PreparedStatement prepareStatement(Connection connection, String sql, Object[] args) {
    PreparedStatement preparedStatement;
    if (connection != null && sql != null) {
        try {//from  w w  w  .  j  a  v  a  2 s.  com
            preparedStatement = connection.prepareStatement(sql);
            int n = args == null ? 0 : args.length;
            if (n > 0) {
                for (int i = 0; i < n; i++) {
                    if (args[i] == null) {
                        //                          callableStatement.setNull(i + 1, java.sql.Types.OTHER);
                        preparedStatement.setNull(i + 1, java.sql.Types.NULL);
                    } else if (args[i] instanceof EnumTipoDatoSQL) {
                        EnumTipoDatoSQL tipoDatoSQL = (EnumTipoDatoSQL) args[i];
                        preparedStatement.setNull(i + 1, tipoDatoSQL.intValue());
                    } else {
                        preparedStatement.setObject(i + 1, args[i]);
                    }
                }
            }
            return preparedStatement;
        } catch (SQLException ex) {
            Bitacora.logFatal(ex);
        }
    }
    return null;
}

---

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

public static void removeFromDb(int itemId) {
    Connection con = null;
    try {//w w w  .  j a  v  a 2s.  c o  m
        con = L2DatabaseFactory.getInstance().getConnection(con);

        // Delete datas
        PreparedStatement statement = con.prepareStatement("DELETE FROM cursed_weapons WHERE itemId = ?");
        statement.setInt(1, itemId);
        statement.executeUpdate();

        statement.close();
    } catch (SQLException e) {
        _log.fatal("CursedWeaponsManager: Failed to remove data: ", e);
    } finally {
        L2DatabaseFactory.close(con);
    }
}

---

From source file:ca.qc.adinfo.rouge.achievement.db.AchievementDb.java

public static HashMap<String, Achievement> getAchievementList(DBManager dbManager) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/* ww w  .j a  v  a 2  s . c o  m*/
    HashMap<String, Achievement> returnValue = new HashMap<String, Achievement>();

    String sql = "SELECT `key`, `name`, `point_value`, `total` FROM rouge_achievements";
    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        rs = stmt.executeQuery();

        while (rs.next()) {

            String key = rs.getString("key");

            Achievement achievement = new Achievement(key, rs.getString("name"), rs.getInt("point_value"),
                    rs.getDouble("total"), 0);

            returnValue.put(key, achievement);
        }

        return returnValue;

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return null;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

---

From source file:org.ulyssis.ipp.snapshot.Snapshot.java

public static Optional<Snapshot> loadBefore(Connection connection, Instant time)
        throws SQLException, IOException {
    String statement = "SELECT \"id\", \"data\", \"event\" FROM \"snapshots\" "
            + "WHERE \"time\" < ? ORDER BY \"time\" DESC, \"event\" DESC FETCH FIRST ROW ONLY";
    try (PreparedStatement stmt = connection.prepareStatement(statement)) {
        stmt.setTimestamp(1, Timestamp.from(time));
        LOG.debug("Executing query: {}", stmt);
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            String data = rs.getString("data");
            Snapshot result = Serialization.getJsonMapper().readValue(data, Snapshot.class);
            result.id = rs.getLong("id");
            result.eventId = rs.getLong("event");
            return Optional.of(result);
        } else {/*from   w  w w .  j a  v  a 2s .  c  om*/
            return Optional.empty();
        }
    }
}

---

From source file:com.concursive.connect.web.modules.wiki.utils.WikiUtils.java

public static HashMap<String, ImageInfo> buildImageInfo(Connection db, int projectId) throws SQLException {
    HashMap<String, ImageInfo> images = new HashMap<String, ImageInfo>();
    // Full size image
    PreparedStatement pst = db
            .prepareStatement("SELECT client_filename, filename, image_width, image_height, version "
                    + "FROM project_files " + "WHERE link_module_id = ? " + "AND link_item_id = ? ");
    pst.setInt(1, Constants.PROJECT_WIKI_FILES);
    pst.setInt(2, projectId);//from   www.  j av a2  s . c  om
    ResultSet rs = pst.executeQuery();
    while (rs.next()) {
        ImageInfo image = new ImageInfo(rs);
        images.put(image.getFilename(), image);
    }
    rs.close();
    pst.close();
    return images;
}

---

From source file:FacultyAdvisement.StudentRepository.java

public static void updateAllToNotAdvised(DataSource ds) throws SQLException {
    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }/* w w  w. j a  v a  2  s .  com*/
    try {
        PreparedStatement ps = conn.prepareStatement("update student set advised = \'false\'");

        ps.execute();

    } finally {
        conn.close();
    }
}

---

From source file:com.keybox.manage.db.UserDB.java

/**
 * updates existing user//from   w w w  . j av  a 2 s .c o m
 * @param user user object
 */
public static void updateUserCredentials(User user) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        String salt = EncryptionUtil.generateSalt();
        PreparedStatement stmt = con.prepareStatement(
                "update users set first_nm=?, last_nm=?, email=?, username=?, user_type=?, password=?, salt=? where id=?");
        stmt.setString(1, user.getFirstNm());
        stmt.setString(2, user.getLastNm());
        stmt.setString(3, user.getEmail());
        stmt.setString(4, user.getUsername());
        stmt.setString(5, user.getUserType());
        stmt.setString(6, EncryptionUtil.hash(user.getPassword() + salt));
        stmt.setString(7, salt);
        stmt.setLong(8, user.getId());
        stmt.execute();
        DBUtils.closeStmt(stmt);
        if (User.ADMINISTRATOR.equals(user.getUserType())) {
            PublicKeyDB.deleteUnassignedKeysByUser(con, user.getId());
        }

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

}

---

From source file:de.sqlcoach.db.jdbc.DBAppUser.java

/**
 * Get./*w ww. j a  v a  2s .  c om*/
 * 
 * @param cn
 *            the cn
 * @param nickname
 *            the nickname
 * 
 * @return the app user
 */
public static AppUser get(Connection cn, String nickname) {
    if (log.isInfoEnabled())
        log.info("get ENTER nickname=" + nickname);

    AppUser model = null;

    final String query = "SELECT * FROM " + TABLENAME + " WHERE nickname like ?";
    try (final PreparedStatement pstmt = cn.prepareStatement(query);) {

        pstmt.setString(1, nickname);
        try (ResultSet resultset = pstmt.executeQuery();) {
            if (resultset.next()) {
                model = new AppUser();
                setModel(resultset, model);
            }
        } // try
    } catch (SQLException e) {
        log.error("get query=" + query + " nickname=" + nickname, e);
    }
    return model;
}

---

From source file:com.jernejerin.traffic.helper.TripOperations.java

/**
 * Insert a trip into database.//w  w  w . j  a v  a 2  s  . c om
 *
 * @param trip trip to insert.
 * @param table table into which we need to insert trip
 */
public static void insertTrip(Trip trip, String table) {
    //        LOGGER.log(Level.INFO, "Started inserting trip into DB for trip = " +
    //                trip.toString() + " from thread = " + Thread.currentThread());
    PreparedStatement insertTrip = null;
    Connection conn = null;
    try {
        // first we need to get connection from connection pool
        conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:taxi");

        // setting up prepared statement
        insertTrip = conn.prepareStatement("insert into " + table
                + " (eventId, medallion, hack_license, pickup_datetime, "
                + "dropoff_datetime, trip_time, trip_distance, pickup_longitude, pickup_latitude, dropoff_longitude, "
                + "dropoff_latitude, payment_type, fare_amount, surcharge, mta_tax, tip_amount, tolls_amount, "
                + "total_amount, timestampReceived) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        insertTrip.setInt(1, trip.getId());
        insertTrip.setString(2, trip.getMedallion());
        insertTrip.setString(3, trip.getHackLicense());
        insertTrip.setTimestamp(4,
                new Timestamp(trip.getPickupDatetime().toEpochSecond(ZoneOffset.UTC) * 1000));
        insertTrip.setTimestamp(5,
                new Timestamp(trip.getDropOffDatetime().toEpochSecond(ZoneOffset.UTC) * 1000));
        insertTrip.setInt(6, trip.getTripTime());
        insertTrip.setDouble(7, trip.getTripDistance());
        insertTrip.setDouble(8, trip.getPickupLongitude());
        insertTrip.setDouble(9, trip.getPickupLatitude());
        insertTrip.setDouble(10, trip.getDropOffLongitude());
        insertTrip.setDouble(11, trip.getDropOffLatitude());
        insertTrip.setString(12, trip.getPaymentType() != null ? trip.getPaymentType().name() : null);
        insertTrip.setDouble(13, trip.getFareAmount());
        insertTrip.setDouble(14, trip.getSurcharge());
        insertTrip.setDouble(15, trip.getMtaTax());
        insertTrip.setDouble(16, trip.getTipAmount());
        insertTrip.setDouble(17, trip.getTollsAmount());
        insertTrip.setDouble(18, trip.getTotalAmount());
        insertTrip.setLong(19, trip.getTimestampReceived());

        insertTrip.execute();
    } catch (SQLException e) {
        LOGGER.log(Level.SEVERE, "Problem when inserting ticket into DB for ticket = " + trip
                + " from thread = " + Thread.currentThread());
    } finally {
        try {
            if (insertTrip != null)
                insertTrip.close();
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, "Problem with closing prepared statement for ticket = " + trip
                    + " from thread = " + Thread.currentThread());
        }
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, "Problem with closing connection from thread = " + Thread.currentThread());
        }
        //            LOGGER.log(Level.INFO, "Finished inserting ticket into DB for for ticket = " +
        //                    trip + " from thread = " + Thread.currentThread());
    }
}

---

From source file:com.silverpeas.notation.model.RatingDAO.java

public static void createRaterRating(Connection con, RaterRatingPK pk, int note) throws SQLException {
    int newId = 0;
    try {//from   ww w. ja  v a2 s  .c o m
        newId = DBUtil.getNextId(TABLE_NAME, COLUMN_ID);
    } catch (Exception e) {
        SilverTrace.warn("notation", "RatingDAO.createRaterRating", "root.EX_PK_GENERATION_FAILED", e);
    }

    PreparedStatement prepStmt = con.prepareStatement(QUERY_CREATE_RATER_RATING);
    try {
        prepStmt.setInt(1, newId);
        prepStmt.setString(2, pk.getInstanceId());
        prepStmt.setString(3, pk.getContributionId());
        prepStmt.setString(4, pk.getContributionType());
        prepStmt.setString(5, pk.getRater().getId());
        prepStmt.setInt(6, note);
        prepStmt.executeUpdate();
    } finally {
        DBUtil.close(prepStmt);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• »