Tejas Tejas - 5 months ago 27
SQL Question

Is opening/closing of JDBC connection instead of maintaining it, desirable for my application?

Every 30 seconds my server gets GPS coordinates of every user connected. Every time I receive these coordinates, I have to store/update these in a MySQL table. The clients connect to the server using web sockets, so a new object and a thread are created for every connection. So basically, every client has a JDBC connection open to the database. My question is-


  1. Should I close the JDBC connection every time the server updates the values and open it after 30 seconds when it receives the next values(to save memory space)?

    Or

  2. Should I leave the JDBC connection open(I've heard JDBC open/close are expensive operations)?


Answer

It's depend on the architecture of your server side codes and the way you insert/update your tables:

  • Do you maintain creating transactions or not? Or your connection is AutoCommit=true?
  • Are you doing a number of operations in a batch or doing single operations using different connections?

The most efficient and publicly used way is creating a connection pool with a rational number of initial connections. This helps with the expensive operation of creating connections. So you are doing this operation (creating connection pool) only once when your server is started.

Then in your codes you should refactor creating connections and replace it with getting an already opened and idle connection from the pool. Then after you used the connection for the CRUD operations, you should not close it. Just make sure you are returning it to the pool.

However in small applications with limited number of connections it doesn't matter how you open/close connections. But having a managed connection pool is a very important step in designing and implementing a large enterprise application.

You can implement your own way of connection pooling using these examples:

How to set up a MySQL connection pool in Java

Java Database Connection Pooling

Also if your server application is managed and run by a web server like tomcat you can let the tomcat handle creating and managing the connection pool for you. You should just define a datasource in tomcat. See this tutorial:

Connection Pooling in a Java Web Application with Tomcat

Hope this would be helpful.