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:FacultyAdvisement.StudentRepository.java

public static String getPicture(DataSource ds, String key) throws SQLException {

    Blob image = null;/* w w w.  j  ava 2  s  .  c  o  m*/

    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }
    try {
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM USERTABLE WHERE USERNAME = ?");
        ps.setString(1, key);
        ResultSet result = ps.executeQuery();
        while (result.next()) {
            image = result.getBlob("IMAGE");
        }
    } finally {
        conn.close();
    }

    if (image != null) {
        return "ImageServlet?username=" + key;
    } else {
        return "/resources/default-image.png";
    }
}

---

From source file:com.l2jserver.model.template.NPCTemplateConverter.java

private static Skills fillSkillList(final ObjectFactory factory, ResultSet npcRs, int npcId)
        throws SQLException {
    final Connection conn = npcRs.getStatement().getConnection();
    final Skills skills = factory.createNPCTemplateSkills();

    final PreparedStatement st = conn.prepareStatement("SELECT * FROM npcskills WHERE npcid = ?");
    st.setInt(1, npcId);/*from   w w w.ja v  a  2  s.com*/
    st.execute();
    final ResultSet rs = st.getResultSet();
    while (rs.next()) {
        Skills.Skill s = factory.createNPCTemplateSkillsSkill();
        s.setId(new SkillTemplateID(rs.getInt("skillid"), null));
        s.setLevel(rs.getInt("level"));
        skills.getSkill().add(s);
    }
    if (skills.getSkill().size() == 0)
        return null;
    return skills;
}

---

From source file:ca.qc.adinfo.rouge.leaderboard.db.LeaderboardDb.java

public static boolean submitScore(DBManager dbManager, String key, long userId, long score) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*from  w w w. j  a  va 2 s.c o  m*/
    String sql = null;

    sql = "INSERT INTO rouge_leaderboard_score (`leaderboard_key`, `user_id`, `score`) "
            + "VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE score = GREATEST(?, score);";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        stmt.setString(1, key);
        stmt.setLong(2, userId);
        stmt.setLong(3, score);
        stmt.setLong(4, score);

        int ret = stmt.executeUpdate();

        return (ret > 0);

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

    } finally {

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

---

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

/**
 * checks to see if user is an admin based on auth token
 *
 * @param userId    user id//from w w w .j a  va 2s.c o m
 * @param authToken auth token string
 * @return user type if authorized, null if not authorized
 */
public static String isAuthorized(Long userId, String authToken) {

    String authorized = null;

    Connection con = null;
    if (authToken != null && !authToken.trim().equals("")) {

        try {
            con = DBUtils.getConn();
            PreparedStatement stmt = con
                    .prepareStatement("select * from users where enabled=true and id=? and auth_token=?");
            stmt.setLong(1, userId);
            stmt.setString(2, authToken);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                authorized = rs.getString("user_type");

            }
            DBUtils.closeRs(rs);

            DBUtils.closeStmt(stmt);

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

}

---

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

/**
 * updates password for admin using auth token
 *///from   ww w.ja va 2s. c  o m
public static boolean updatePassword(Auth auth) {
    boolean success = false;

    Connection con = null;
    try {
        con = DBUtils.getConn();

        String prevSalt = getSaltByAuthToken(con, auth.getAuthToken());
        PreparedStatement stmt = con
                .prepareStatement("select * from users where auth_token like ? and password like ?");
        stmt.setString(1, auth.getAuthToken());
        stmt.setString(2, EncryptionUtil.hash(auth.getPrevPassword() + prevSalt));
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {

            String salt = EncryptionUtil.generateSalt();
            stmt = con.prepareStatement("update users set password=?, salt=? where auth_token like ?");
            stmt.setString(1, EncryptionUtil.hash(auth.getPassword() + salt));
            stmt.setString(2, salt);
            stmt.setString(3, auth.getAuthToken());
            stmt.execute();
            success = true;
        }

        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

---

From source file:com.l2jserver.model.template.NPCTemplateConverter.java

private static Droplist fillDropList(final ObjectFactory factory, ResultSet npcRs, int npcId)
        throws SQLException {
    final Connection conn = npcRs.getStatement().getConnection();
    final Droplist drops = factory.createNPCTemplateDroplist();

    final PreparedStatement st = conn.prepareStatement("SELECT * FROM droplist WHERE mobId = ?");
    st.setInt(1, npcId);//from w  w w . ja v  a  2 s  .  co m
    st.execute();
    final ResultSet rs = st.getResultSet();
    while (rs.next()) {
        final Droplist.Item item = factory.createNPCTemplateDroplistItem();
        item.setId(new ItemTemplateID(rs.getInt("itemId"), null));
        item.setMin(rs.getInt("min"));
        item.setMax(rs.getInt("max"));
        item.setChance(rs.getInt("chance"));
        item.setCategory(getCategory(rs.getInt("category")));
        drops.getItem().add(item);
    }
    if (drops.getItem().size() == 0)
        return null;
    return drops;
}

---

From source file:com.wso2telco.dao.TransactionDAO.java

/**
 * Insert transaction log./*from ww  w .  j  a  v a2  s .c  om*/
 *
 * @param transaction the transaction
 * @param contextId   the context id
 * @param statusCode  the status code
 * @throws Exception the exception
 */
public static void insertTransactionLog(Transaction transaction, String contextId, int statusCode)
        throws Exception {

    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DbUtil.getConnectDBConnection();
        String query = "INSERT INTO mcx_cross_operator_transaction_log (tx_id, tx_status, batch_id, api_id, "
                + "client_id," + " application_state, sub_op_mcc, sub_op_mnc, timestamp_start, timestamp_end, "
                + "exchange_response_code)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        ps = conn.prepareStatement(query);
        ps.setString(1, transaction.getTx_id());
        ps.setString(2, transaction.getTx_status());
        ps.setString(3, contextId);
        ps.setString(4, transaction.getApi().getId());
        ps.setString(5, transaction.getClient_id());
        ps.setString(6, transaction.getApplication_state());
        ps.setString(7, transaction.getSubscriber_operator().getMcc());
        ps.setString(8, transaction.getSubscriber_operator().getMnc());
        ps.setString(9, transaction.getTimestamp().getStart());
        ps.setString(10, transaction.getTimestamp().getEnd());
        ps.setInt(11, statusCode);
        ps.execute();

    } catch (SQLException e) {
        handleException("Error in inserting transaction log record : " + e.getMessage(), e);
    } finally {
        DbUtil.closeAllConnections(ps, conn, null);
    }
}

---

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

/**
 * returns terminal logs for user session for host system
 *
 * @param sessionId     session id//  w  ww  .  ja  v  a  2  s  .  c  om
 * @param instanceId    instance id for terminal session
 * @return session output for session
 */
public static List<SessionOutput> getTerminalLogsForSession(Connection con, Long sessionId,
        Integer instanceId) {

    List<SessionOutput> outputList = new LinkedList<>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from terminal_log where instance_id=? and session_id=? order by log_tm asc");
        stmt.setLong(1, instanceId);
        stmt.setLong(2, sessionId);
        ResultSet rs = stmt.executeQuery();
        StringBuilder outputBuilder = new StringBuilder("");
        while (rs.next()) {
            outputBuilder.append(rs.getString("output"));
        }

        String output = outputBuilder.toString();
        output = output.replaceAll("\\u0007|\u001B\\[K|\\]0;|\\[\\d\\d;\\d\\dm|\\[\\dm", "");
        while (output.contains("\b")) {
            output = output.replaceFirst(".\b", "");
        }
        DBUtils.closeRs(rs);

        SessionOutput sessionOutput = new SessionOutput();
        sessionOutput.setSessionId(sessionId);
        sessionOutput.setInstanceId(instanceId);
        sessionOutput.getOutput().append(output);

        outputList.add(sessionOutput);

        DBUtils.closeRs(rs);

        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return outputList;

}

---

From source file:FacultyAdvisement.StudentRepository.java

public static void delete(DataSource ds, Student student) throws SQLException {
    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }//  w  w  w  .  ja v a  2 s.co  m
    try {

        PreparedStatement ps;
        ps = conn.prepareStatement("Delete from STUDENT where EMAIL=?");
        ps.setString(1, student.getUsername());
        ps.executeUpdate();
        ps = conn.prepareStatement("Delete from USERTABLE where USERNAME=?");
        ps.setString(1, student.getUsername());
        ps.executeUpdate();
        ps = conn.prepareStatement("Delete from GROUPTABLE where USERNAME=?");
        ps.setString(1, student.getUsername());
        ps.executeUpdate();
    } finally {
        conn.close();
    }

    //students = (HashMap<String, StudentPOJO>) readAll(); // reload the updated info
}

---

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

public static List<Event> loadAfter(Connection connection, Instant time, long id)
        throws SQLException, IOException {
    String statement = "SELECT \"id\",\"data\",\"removed\" FROM \"events\" "
            + "WHERE \"time\" > ? OR (\"time\" = ? AND \"id\" > ?) ORDER BY \"time\" ASC, \"id\" ASC";
    List<Event> events = new ArrayList<>();
    try (PreparedStatement stmt = connection.prepareStatement(statement)) {
        stmt.setTimestamp(1, Timestamp.from(time));
        stmt.setTimestamp(2, Timestamp.from(time));
        stmt.setLong(3, id);//from  w w  w  .  j  av a2s . c  om
        LOG.debug("Executing query: {}", stmt);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            String evString = rs.getString("data");
            Event event = Serialization.getJsonMapper().readValue(evString, Event.class);
            event.id = rs.getLong("id");
            event.removed = rs.getBoolean("removed");
            events.add(event);
        }
    }
    LOG.debug("Loaded {} events", events.size());
    return events;
}

---

• «
• 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
• »