Close SQL connection Java Webservice

Question

Hello,

I am programming a WebService in Java that create and call this class :

    public class Manager{

        private Connection aConnection;


        public CacheManager(){
            //We get a connection
                aConnection = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/mydb?user=root&password=";


        }
// Insert a datalist into a table
    public void insertIntoDB(List listData, String tableName, StringData previousData)
    {

       // Some code using database

        }

The main problem is that the connection is not closed right after the call of the webservice. It means that 100 calls to the webservice create 100 connections to the database.It create the MySQL error "too many users connected" If I wait 2 minutes, objects are destroyed by the garbage collector and the webservice can work again.

Does somebody has an idea about how to bypass this problem ?

Thanks!!

Answer 1

I think you should maintain a database connection pool. For example, you can refer C3P0. In that way, when you initialize the class, in the constructor, you can setup the database connection pool (say 10 connections to the database). In the insertIntoDB method, first you get a connection from the connection pool, perform your database CRUD operations and finally give back the connection to the pool. In this way, you will never put pressure on the database by creating multiple of connections. Creating connection per call will make it slower as well, threr is an antipattern on this called Connection Thrashing.

Answer 2

I had to create a connection pool on glassfish, then each time I need the connection in my program, I 'get' a connection and release it right after. I do not get the connection in the constructor anymore, just inside my functions.