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

// You can setup a data source in tomcat using a context file
// or you can set one up using the Administration web pages
// as well. Either way you do it, here is the simple code to
// get the data source from Tomcat so you can start pulling
// out database connections.

// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");

// Look up our data source by the name we gave it when we created it. 
// In this case that's "jdbc/EmployeeDB".
DataSource ds = (DataSource) envCtx.lookup("jdbc/EmployeeDB");

// Way too many people hard code their JDBC connections to
// the database server(s). Use data sources to connect to
// your databases. They can be setup easily in any major
// Java application server as well as JSP/servlet engines
// like Tomcat and Spring.
//
// If you aren't running inside a container that provides
// you with easy access to a data source though, you can
// include the Jakarta Commons DBCP library and create a
// data source as shown in the code below. The result is
// a data source complete with pooling, caching, etc. that
// you can pass to other code that just expects to receive
// a data source.

BasicDataSource dataSource = new BasicDataSource();

dataSource.setDriverClassName(System.getProperty("driverClassName"));
dataSource.setUsername(System.getProperty("username"));
dataSource.setPassword(System.getProperty("password"));
dataSource.setUrl(System.getProperty("url"));