DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

JDBC 101

05.24.2006
| 4463 views |
  • submit to reddit
        // Note the use of a prepared statement. You can gain a couple of different benefits
// from using a prepared statement versus a conventional statement. One is that the
// database can compile the statement once and simply insert different values into the
// variables each time you call it. For frequently used functions this can result in
// improved performance. The second advantage is where the values to be passed in might
// be changable by hostile users (e.g. a search term taken from a webpage). In those
// cases assembled SQL strings are suseptible to attack by cleverly phrased inputs but
// prepared statements are not because they are _never_ interpreted as actual SQL, only
// as values for variables.
//
// See my other code snippets "Getting A Data Source From Tomcat" and "Data Source 101"
// for more information on how to get the data source you pass into a routine like this.

    /**
     * In this case the Record object is something we are saving to the database.
     */
    public void persistRecord(DataSource dataSource, 
        Record record) throws Exception {

        Connection conn = null;
        PreparedStatement st = null;
        
        int retVal = 0;
        
        try {
            conn = dataSource.getConnection();
            
            st = conn.prepareStatement("insert into test(a, b) values(?, ?)");

            st.setInt(1, record.getA());
            st.setString(2, record.getB());
            
            int records = st.executeUpdate();
        } catch (SQLException se) {
            log.error(se, se);
            throw se;
        } finally {
            UtilityJDBC.closeSQLClasses(conn, st, null);   
        }
    }

    /**
     * NOTE!! if you close a statement the associated ResultSet
     * is closed too so you should copy the contents to some other
     * object. The result set is invalidated also if you recycle a
     * Statement and try to execute some other query before the result
     * set has been completely examined.
     * 
     * @param conn
     * @param st
     * @param rs
     * @throws SQLException
     */
    public static void closeSQLClasses(Connection conn, Statement st,
        ResultSet rs) {

        try {
            if (rs != null) {
                rs.close();
            }

            if (st != null) {
                st.close();
            }

            if (conn != null) {
                conn.close();
            }
        } catch (SQLException se) {
            log.error(se, se);
        }
    }