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?

Jaydee · Answer 1 · 2012-05-15T10:36:48.463

5

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.

edited May 15 '12 at 10:36

answered May 15 '12 at 10:14

Jaydee

4,138
1
19
20

Can this be done - create a result set as SCROLL_SENSITIVE and CONCUR_UPDATABLE in the init() method of the servlet. There will be no deleteion done in the servlet. Just it will be checked if the a particular entry is present in the table or not. SCROLL_SENSITIVE will ensure that any updates made will be reflected in the result set also. What is wrong with this idea? – Ashwin May 15 '12 at 10:22
@Jaydee : The init() method will be called only once. But a result set object created in init – Ashwin May 15 '12 at 10:47
You could do it and then save the value to a variable. – Jaydee May 15 '12 at 10:57
@Jaydee : I did not get you. Are you saying that I create result set in init with SCROLL_SENSITIVE and CONCUR_UPDATABLE properties. and execute the query in init itself. And then assign the init result set to a local result set object and use it? – Ashwin May 16 '12 at 05:00
You have to remember that any access to the result set will need to allow for the multithreaded nature of the servlets. So if the result set is being updated in a thread, you need a seperate instance per thread. If it is not being updated in the thread, you may as well execute it in init() and assign to an object acessible through out your system. – Jaydee May 16 '12 at 12:10
@Jaydee : My result set gets updated in only one servlet. But it is used in many servlets. So what can be the problems in such a case? – Ashwin May 17 '12 at 05:22
Is the result set only updated once when the application is initialised? – Jaydee May 17 '12 at 08:14
@Jaydee : No it can get updated even after the application is initialized. But it gets updated in only one servlet. other servlets simply fetch values from the result set. – Ashwin May 18 '12 at 04:36
The problem is the servlet that does the update can be processing many (multithreaded) requests at the same time, so what is the state of the result set at any given time? – Jaydee May 18 '12 at 08:30
Also, what happens when one servlet is reading from the result set when it is updated. If you can guarantee that these things won't happen or you are willing to take the performance hit of syncronising all the accesses, you'll be fine. – Jaydee May 18 '12 at 11:18

score 5 · Answer 2 · edited Jun 20 '20 at 09:12

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.

very good answer! I did not know that these could be done. Can you give me a link where I can learn more about what all can be put into web.xml file. one more question - pls see my comment to Jaydee's answer — Ashwin, May 15 '12 at 10:28
http://shop.oreilly.com/product/9780596516680.do this is a good book that you can learn about servlets and jsps. If you want to learn about web.xml tags read http://docs.oracle.com/cd/E13222_01/wls/docs81/webapp/web_xml.html — Krishan, May 15 '12 at 10:40
that depends on the size of your application. If you have a lot of database operations then you will have to use database connection pooling as @Jaydee has explained. — Krishan, May 15 '12 at 10:48
Yeah, that can be done. But you don't want to put your credentials in the web.xml Instead you can use a realm(JDBC/memory). Anyhow first you should have an idea about how authentication and authorization works in servlets and jsps. As a comment I'm unable to post how to do that. If you really want to know you can start a new question, if there are no any questions related already — Krishan, May 16 '12 at 03:20
Since you asked for a seperate question I have posted it at http://stackoverflow.com/questions/10612231/authentication-and-authorization-in-servlets . Two people have already given the answer. But not explanation. — Ashwin, May 16 '12 at 05:18
In your answer you have given the db user and password in the web.xml file. But in jboss deploy/postgres-ds.xml file I have already given that information. Why is it required that I give it again? — Ashwin, May 16 '12 at 05:31

initializing a database connection object once and reusing it - jboss

2 Answers2