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:de.sqlcoach.db.jdbc.DBAppUser.java

/**
 * Gets the all./*from  ww  w.  j  av  a2 s. c  o m*/
 * 
 * @param cn
 *            the cn
 * 
 * @return the all
 */
public static List<AppUser> getAll(Connection cn) {
    if (log.isInfoEnabled())
        log.info("getAll ENTER ");
    final ArrayList<AppUser> col = new ArrayList<AppUser>();
    final String query = "SELECT * FROM " + TABLENAME;
    try (final PreparedStatement pstmt = cn.prepareStatement(query);
            final ResultSet resultset = pstmt.executeQuery()) {
        while (resultset.next()) {
            AppUser model = new AppUser();
            setModel(resultset, model);
            col.add(model);
        }
    } catch (SQLException e) {
        log.error("getAll ", e);
    }
    return col;
}

---

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

/**
 * Delete.//  w w w. j a v  a2 s  .  c o m
 * 
 * @param cn
 *            the cn
 * @param model
 *            the model
 * 
 * @return the int
 */
public static int delete(Connection cn, AppUser model) {
    if (log.isInfoEnabled())
        log.info("delete ENTER model=" + model);
    int result = -1;
    final String query = "DELETE FROM " + TABLENAME + " WHERE id = ?";

    try (PreparedStatement pstmt = cn.prepareStatement(query);) {

        pstmt.setInt(1, model.getId());
        result = pstmt.executeUpdate();

    } catch (SQLException e) {
        log.error("delete ", e);
    }

    return result;
}

---

From source file:edu.jhu.pha.vospace.DbPoolServlet.java

/** Helper function to setup and teardown SQL connection & statement. */
public static <T> T goSql(String context, String sql, SqlWorker<T> goer) {
    //logger.debug(context);
    Connection conn = null;
    PreparedStatement stmt = null;
    try {/*  w w w  .j av a 2 s.  c  o  m*/
        conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:dbPool");
        int tries = 30; // number of repitions when a transaction fails due to a deadlock
        while (true) {
            try {
                if (sql != null)
                    stmt = conn.prepareStatement(sql);
                T result = goer.go(conn, stmt);
                return result;
            } catch (MySQLTransactionRollbackException transactionEx) {
                if (tries > 0)
                    tries--;
                else {
                    logger.error("Exceeded limit of transaction tries.");
                    goer.error(context, transactionEx);
                    throw transactionEx;
                }
            }
        }
    } catch (SQLException e) {
        goer.error(context, e);
        return null;
    } finally {
        close(stmt);
        close(conn);
    }
}

---

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

/**
 * Get./*  w  w  w .ja  v  a  2 s.  c o  m*/
 * 
 * @param cn
 *            the cn
 * @param id
 *            the id
 * 
 * @return the app user
 */
public static AppUser get(final Connection cn, int id) {
    if (log.isInfoEnabled())
        log.info("get ENTER id=" + id);

    final String query = "SELECT * FROM " + TABLENAME + " WHERE id = ?";

    AppUser model = null;
    try (PreparedStatement pstmt = cn.prepareStatement(query);) {
        pstmt.setInt(1, id);
        try (ResultSet resultset = pstmt.executeQuery();) {

            if (resultset.next()) {
                model = new AppUser();
                setModel(resultset, model);
            }
        }
    } catch (SQLException e) {
        log.error("get ", e);
    }
    return model;
}

---

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

/**
 * returns terminal logs for user session for host system
 *
 * @param con       DB connection/* w  w  w.  ja v  a  2 s. c  o  m*/
 * @param sessionId session id
 * @return session output for session
 */
public static List<HostSystem> getHostSystemsForSession(Connection con, Long sessionId) {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select distinct instance_id, system_id from terminal_log where session_id=?");
        stmt.setLong(1, sessionId);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            HostSystem hostSystem = SystemDB.getSystem(con, rs.getLong("system_id"));
            hostSystem.setInstanceId(rs.getInt("instance_id"));
            hostSystemList.add(hostSystem);
        }

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

    } catch (Exception ex) {
        log.error(ex.toString(), ex);
    }
    return hostSystemList;

}

---

From source file:com.wso2telco.mnc.resolver.mncrange.McnRangeDbUtil.java

/**
 * Gets the mcc number ranges.//ww w .  j  av  a  2s  .co m
 *
 * @param mcc the mcc
 * @return the mcc number ranges
 * @throws MobileNtException the mobile nt exception
 */
public static List<NumberRange> getMccNumberRanges(String mcc) throws MobileNtException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "SELECT mnccode,brand,rangefrom,rangeto " + "FROM mcc_number_ranges " + "WHERE mcccode = ?";

    List<NumberRange> lstranges = new ArrayList();

    try {
        conn = getAxiataDBConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        rs = ps.executeQuery();
        while (rs.next()) {
            lstranges.add(new NumberRange(rs.getLong("rangefrom"), rs.getLong("rangeto"),
                    rs.getString("mnccode"), rs.getString("brand")));
        }
    } catch (SQLException e) {
        handleException("Error occured while getting Number ranges for mcc: " + mcc + " from the database", e);
    } finally {
        McnRangeDbUtil.closeAllConnections(ps, conn, rs);
    }
    return lstranges;
}

---

From source file:com.concursive.connect.web.webdav.WebdavManager.java

/**
 * Gets the webdavPassword attribute of the WebdavManager object
 *
 * @param db       Description of the Parameter
 * @param username Description of the Parameter
 * @return The webdavPassword value/*from  ww  w  .  j  a  va  2 s .c o m*/
 * @throws SQLException Description of the Exception
 */
public static String getWebdavPassword(Connection db, String username) throws SQLException {
    String password = "";
    PreparedStatement pst = db.prepareStatement(
            "SELECT webdav_password " + "FROM users " + "WHERE username = ? " + "AND enabled = ? ");
    pst.setString(1, username);
    pst.setBoolean(2, true);
    ResultSet rs = pst.executeQuery();
    if (rs.next()) {
        password = rs.getString("webdav_password");
    }
    rs.close();
    pst.close();
    return password;
}

---

From source file:com.wso2telco.dep.reportingservice.dao.ApiManagerDAO.java

/**
 * Gets the consumer key by application.
 *
 * @param applicationId the application id
 * @return the consumer key by application
 * @throws APIMgtUsageQueryServiceClientException the API mgt usage query service client exception
 * @throws SQLException the SQL exception
 *///  w  ww . j a va  2s .c  o m
public static String getConsumerKeyByApplication(int applicationId)
        throws APIMgtUsageQueryServiceClientException, SQLException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT CONSUMER_KEY FROM " + ReportingTable.AM_APPLICATION_KEY_MAPPING
            + " WHERE KEY_TYPE = 'PRODUCTION' AND APPLICATION_ID=?";
    String consumerKey = null;

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_DB);
        ps = conn.prepareStatement(sql);
        ps.setInt(1, applicationId);
        log.debug("getConsumerKeyByApplication");
        results = ps.executeQuery();
        while (results.next()) {
            consumerKey = results.getString("CONSUMER_KEY");
        }
    } catch (Exception e) {
        log.error("Error occured while getting consumer key from the database" + e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, results);
    }

    return consumerKey;
}

---

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

/**
 * Truncate table.//from w  ww.  j  a  v  a  2s  .c  o  m
 *
 * @param table table to truncate
 */
public static void truncateTable(String table) {
    //        LOGGER.log(Level.INFO, "Started inserting trip into DB for trip = " +
    //                trip.toString() + " from thread = " + Thread.currentThread());
    PreparedStatement truncateTable = 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
        truncateTable = conn.prepareStatement("truncate " + table);
        truncateTable.execute();
    } catch (SQLException e) {
        LOGGER.log(Level.SEVERE,
                "Problem when truncating table = " + table + " from thread = " + Thread.currentThread());
    } finally {
        try {
            if (truncateTable != null)
                truncateTable.close();
        } catch (Exception e) {
            LOGGER.log(Level.SEVERE, "Problem with closing prepared statement for truncating table = " + table
                    + " 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.tethrnet.manage.db.UserDB.java

/**
 * updates existing user//from   ww w . j  a  v  a  2s  .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  email=?, username=?, user_type=?, password=?, salt=? where id=?");
        stmt.setString(1, user.getEmail());
        stmt.setString(2, user.getUsername());
        stmt.setString(3, user.getUserType());
        stmt.setString(4, EncryptionUtil.hash(user.getPassword() + salt));
        stmt.setString(5, salt);
        stmt.setLong(6, 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);

}

---

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