The best way to deal with pooled JDBC-Connections in JSP

[Mario Schwaiger]

Hi!

I am working on a JSP-Project and sometimes I have to fetch or update the Database with JDBC. (I thought about using Hibernate but it would make more afford to use Hibernate for just some easy updates...).
So I bind my basic connection as a bean to the session:

<jsp:useBean class="base.Baseconnection" id="basis" scope="session"/>

Still I have some doubts whether I am doing everything in the right way (using Pooled Connections, Sessions...). Everything works fine so far, but I'm still unsure.
Thank you!

    private Connection connection = null;
    private BoneCP connectionPool = null;

    public Baseconnection() {
    }

    public Connection getConnection() throws SQLException {
        if (connection == null) {
            String password = "secr3t";
            if (Info.isPool()) {
                System.out.println("Gepoolte Verbindung");

                try {
                    // load the database driver (make sure this is in your classpath!)
                    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                } catch (java.lang.ClassNotFoundException e) {
                    e.printStackTrace();
                    return null;
                }

                try {
                    // setup the connection pool
                    BoneCPConfig config = new BoneCPConfig();
                    config.setJdbcUrl(Info.getConnectionString()); //Get the String from a static class - works
                    config.setUsername("mario");
                    config.setPassword(password);
                    config.setMinConnectionsPerPartition(5);
                    config.setMaxConnectionsPerPartition(10);
                    config.setPartitionCount(1);
                    connectionPool = new BoneCP(config); // setup the connection pool

                    connection = connectionPool.getConnection(); // fetch a connection

                    if (connection != null) {
                        System.out.println("Connection successful!");

                    } else {
                        throw new java.lang.NullPointerException("connectionPool.getConnection() war nicht erfolgreich");
                        //System.out.println("Connection failed");
                    }

                    return connection;
                    //connectionPool.shutdown(); // shutdown connection pool.
                } catch (SQLException e) {
                    e.printStackTrace();
                    if (connection != null) {
                        try {
                            connection.close();
                            throw new java.lang.NullPointerException("SQL-Exception, Verbindung geschlossen");
                        } catch (SQLException se) {
                            e.printStackTrace();
                            throw new java.lang.NullPointerException("SQL-Exception, nicht einmal Verbindung kann geschlossen werden");
                        }
                    }
                    throw new java.lang.NullPointerException("Ist aus irgendeinem Grund bis zum Ende Durchgelaufen");
                }

            } else {
//            Connection connection = null;
                try {
                    Class.forName(datenbank.Info.getTreiber());
                    try {
                        // create a database connection
                        connection = DriverManager.getConnection(Info.getConnectionString(), "qohelet", password);

                    } catch (SQLException se) {
                        System.out.println("Error... Versuche erneute Verbindung");
                        {
                            // if the error message is "out of memory", 
                            // it probably means no database file is found
                            System.err.println("----> Basisverbindung <----");
                            se.printStackTrace();
                            System.err.println(se.getMessage());
                            System.err.println(se.getErrorCode());
                            System.err.println(se.getCause());

                        }
                    }
                } catch (ClassNotFoundException cnfe) {
                    System.err.println("Basisverbindung: Class not found");

                }
                return connection;
            }
        } else {
            try {
                if (connection.isClosed()) {
                    return connectionPool.getConnection();
                } else {
                    return connection;
                }
            } catch (SQLException sqle) {
                System.err.println("Error: " + sqle.getLocalizedMessage());
                System.err.println("Code: " + sqle.getSQLState());
                return connection;
            }
        }
    }

public static void disconnect(ResultSet resultSet, PreparedStatement ps, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
    }

In the JSP-Files I use this connection in that way. Is there a possibility to check if the connection is really pooled?

<%
    Connection c = null;
    PreparedStatement s = null;
    ResultSet rs = null;
    String sql = "UPDATE User SET Sess = ? WHERE User = ? AND pw = ?";

    try {
        c = basis.getConnection();
        c.setAutoCommit(false);
        
            s = c.prepareStatement(sql);
            s.setString(1, session.getId());
            s.setString(2, request.getParameter("User"));
            s.setString(3, request.getParameter("pw"));

            s.executeUpdate();
            c.commit();
            

    } catch (SQLException exsql) {
        System.err.println(exsql.toString());
    } finally {
        Baseconnection.disconnect(rs, s, c);
    }

%>

[Ulf Dittmer]

In a web app you should generally use pooled connections, and if possible the pool that is built into the servlet container (if there is one). For Tomcat this is described in http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html

[Mario Schwaiger]

Ulf Dittmer wrote:In a web app you should generally use pooled connections, and if possible the pool that is built into the servlet container (if there is one).

Ok, I implemented it. Thank you for the information. So now, I should be able to use pooled connections?

Everything works as it should, just those doubts always...

By the way: Is it possible to implement this connection pool for Hibernate as well?

<?xml version="1.0" encoding="UTF-8"?> <Context antiJARLocking="true" path="/Proj"> <Resource name="jdbc/sqlserv" auth="Container" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" type="javax.sql.DataSource" maxActive="50" maxIdle="10" maxWait="15000" username="mario" password="secr3t" url="jdbc:sqlserver://127.1.1.1\DB" removeAbandoned="true" removeAbandonedTimeout="30" logAbandoned="true" /> </Context>

<resource-ref> <res-ref-name>jdbc/sqlserv</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope> </resource-ref>

        <%
         InitialContext ic = new InitialContext();
  DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/sqlserv");
  Connection c = ds.getConnection();
  
  Statement s = c.createStatement();
  
  ResultSet rs = s.executeQuery("SELECT * FROM "+Info.getB_BM());
  
  out.write(ausgabe.Ausgabe.resultSetToHTML(rs));
  
  c.close();
        %>

[Ulf Dittmer]

Yes, Hibernate can use that, although I can't point you in the right direction. But some directed searching for "hibernate tomcat connection pool" or some such phrase should lead you there.

[Mario Schwaiger]

Works perfectly!

I checked out StackOverflow here:

http://stackoverflow.com/questions/5303671/configure-hibernate-to-connect-to-database-via-jndi-datasource

Even though, jpkrohling says it is necessary to use a persistence.xml, I'd say, it's not. For those, who have the same struggles as I had here's my new hibernate.cfg.xml. Everything works fine. Many thanks to Ulf for sharing his knowledge.

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="hibernate.connection.datasource">java:comp/env/jdbc/sqlserv</property> <property name="current_session_context_class">thread</property> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <property name="hbm2ddl.auto">update</property> <property name="show_sql">true</property> <!- Your mapped classes...---> </session-factory> </hibernate-configuration>