Over a million developers have joined DZone.

JDBC 101

·
// 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);
        }
    }
Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}