initializing a database connection object once and reusing it - jboss

Question

i am using jboss server and postgresql as the database. Right now I am connecting to the database each time in the servlet like this:

public void doPost(HttpServletRequest req, HttpServletResponse resp)
{
Connection conn =null; // Create connection object
             String database = "jbossdb"; // Name of database
             String user = "qwerty"; //
             String password = "qwerty";
             String url = "jdbc:postgresql://localhost:5432/" + database;
             ResultSet rs = null;
             ResultSetMetaData rsm = null;
             ObjectInputStream in=new ObjectInputStream(req.getInputStream());
             String input=(String) in.readObject();
             String[] s_input=input.split(",");

              try{
                    Class.forName("org.postgresql.Driver").newInstance();
                    //.newInstance()
                 } catch(Exception e) 
                    {
                    System.err.println(e);
                    }

            try{
                conn = DriverManager.getConnection(url, user, password);

                }catch(SQLException se) 
                 {
                        System.err.println(se);
                 }

This code is present in every servet of mine. For each request a new connection object is made. Will this affect performance? Is there any way in jboss where the connection can be initialized only once(possibly on startup) and then passed on to the servlets when required? Should I put it in the init() method of servlet?

Answer 1

A much better way of doing this is using a connection pool. You also need to ensure that you close the connections properly (is isn't clear from your code if this is happening, with a pool it still needs to be done). As for storing the connection, that is not really a good idea as your servlet is multithreaded (I learned this the hard way) and you would need to sychronize all accesses which would be a bit of a disaster .

http://geekexplains.blogspot.co.uk/2008/06/what-is-connection-pooling-why-do-we.html

http://confluence.atlassian.com/display/DOC/Configuring+a+PostgreSQL+Datasource+in+Apache+Tomcat

http://www.devdaily.com/blog/post/java/how-configure-tomcat-dbcp-connection-pool-pooling-postgres

--EDIT-- Why not do it in "init()"?

Learn about the "life cycle of a servlet".

init() is only called once when the servlet is set up by the container.

Each request is using the same servlet not a new instance for each request. So init() will not be called for a request.

As each request is processed by the same servlet instance, your code needs to be multithreaded.

Answer 2

It is probably not a good solution that you are having here. When you are using servlets in order to do something your application loads, you can have listeners. What you have to do is, first create a class implementing ServletContextListener.

public class ContextListener implements ServletContextListener {

    public void contextInitialized(ServletContextEvent sce) {
        ServletContext context=sce.getServletContext();
        String dburl=context.getInitParameter("dbUrl");
        String dbusername=context.getInitParameter("dbUserName");
        String dbpassword=context.getInitParameter("dbPassword");
        
        DBConnector.createConnection(dburl, dbusername, dbpassword);
        System.out.println("Connection Establised.........");
    }

    public void contextDestroyed(ServletContextEvent sce) {
        DBConnector.closeConnection();
    }
    
}

After that you can create another class to create the connection when application loads.

public class DBConnector {
    
    private static Connection con;
    
    public static void createConnection(String dbUrl,String dbusername,String dbPassword){
        try {
            Class.forName("org.postgresql.Driver");
            con=DriverManager.getConnection(dbUrl, dbusername, dbPassword);     
        } catch (Exception ex) {
            ex.printStackTrace();
        }    
    }
    
    public static Connection getConnection(){
        return con;
    }
    
    public static void closeConnection(){
        if(con!=null){
            try {
                con.close();
            } catch (SQLException ex) {
                 ex.printStackTrace();
            }
        }
    
    }
    
    
    
}

Then when ever you want to get the db connection you can call the static getConnection method and get it. In order to call your listener class you have to add the listener tags inside the web.xml.

<context-param>
    <param-name>dbUrl</param-name>
    <param-value>jdbc:postgresql://localhost:5432/jbossdb</param-value>
</context-param>
<context-param>
    <param-name>dbUserName</param-name>
    <param-value>qwerty</param-value>
</context-param>
<context-param>
    <param-name>dbPassword</param-name>
    <param-value>qwerty</param-value>
</context-param>
<listener>
    <listener-class>com.icbt.bookstore.listener.ContextListener</listener-class>
</listener>

It is not good practice to hardcode your db usernames and passwords. Instead you can use servlet init parameters in the web.xml as shown.

Hope, this answers your question.