preparedstatementpooltests.java

Java Database connection pool

package poolman.tests.basic;

import com.codestudio.sql.PoolMan;
import com.codestudio.sql.PoolManConnection;
import com.codestudio.sql.PoolManConnectionHandle;
import com.codestudio.util.JDBCPool;
import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PreparedStatementPoolTests extends TestCase {

    public PreparedStatementPoolTests(String name) {
        super(name);
    }

    public static Test suite() {
        return new TestSuite(PreparedStatementPoolTests.class);
    }

    public void testPreparedStatementPool() {

        Connection con = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;

        String sql = "update neville set name = ? where id = 1";

        try {

            con = getConnection();

            ps1 = con.prepareStatement(sql);
            ps1.setString(1, "prepstatetest1");
            ps1.execute();
            ps1.close();

            ps2 = con.prepareStatement(sql);
            ps2.setString(1, "prepstatetest2");
            ps2.execute();
            ps2.close();

            PoolManConnection pcon = ((PoolManConnectionHandle) con).getPoolManConnection();
            JDBCPool jpool = (JDBCPool) pcon.getPool();
            int numPooledStatements = jpool.numPooledStatements(sql);

            ps2 = con.prepareStatement("select name from neville where id > ?");
            ps2.setInt(1, 1);
            ResultSet res = ps2.executeQuery();
            while (res.next()) {
                System.out.println(res.getString(1));
            }
            // don't close res or statement (testing auto closing)...

            assert(numPooledStatements == 1);

        } catch (Exception e) {
            System.out.println("TEST: prepared statement error: " + e.getMessage());
            e.printStackTrace();
            fail();
        }
        finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        try {
            con = getConnection();
            PoolManConnection pcon = ((PoolManConnectionHandle) con).getPoolManConnection();
            JDBCPool jpool = (JDBCPool) pcon.getPool();
            int numPooledStatements = jpool.numPooledStatements(sql);
        } catch (Exception e) { e.printStackTrace(); }
    }


    private Connection getConnection()
            throws SQLException {

        return getDefaultDataSource().getConnection();
    }

    private DataSource getDefaultDataSource()
            throws SQLException {

        DataSource defaultDS = PoolMan.getDataSource();
        return defaultDS;
    }
}