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-
It's depend on the architecture of your server side codes and the way you
update your tables:
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:
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:
Hope this would be helpful.