How to manage connection to database SQL

Question

I have a SQL database and a machine running a java code to fetch information from it.
The machine is listening on a port all the time.
At first I closed the connection after every query, but the increased latency was high.
So I switched to never closing the connection, and now the connection is closed after some time with no use (the latency was low though).
So obviously the solution is somewhere between never closing and always closing.
What is the recommended heuristics to handle connection to SQL database?
Now I am in testing of the server and the amount of requests is low. When the server will run, users will use the server more than it is used now. I can't predict how much more.

Answer 1

Use any connection pool for your use case.If you are using app server you can use app server connection pool or use opensource dbcp connection pool mechanism.

<!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.2.2</version>
</dependency>

dbcp configuration link

import org.apache.commons.dbcp2.BasicDataSource;


public class DataBaseUtility
{
    private static BasicDataSource dataSource;

    private static BasicDataSource getDataSource()
    {

        if (dataSource == null)
        {
            BasicDataSource ds = new BasicDataSource();
            ds.setUrl("jdbc:mysql://localhost/test");
            ds.setUsername("root");
            ds.setPassword("password");


            ds.setMinIdle(5);
            ds.setMaxIdle(10);
            ds.setMaxOpenPreparedStatements(100);

            dataSource = ds;
        }
        return dataSource;
    }

    public static void main(String[] args) throws SQLException
    {

        try (BasicDataSource dataSource = DataBaseUtility.getDataSource(); 
                Connection connection = dataSource.getConnection();
                PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM account");)
        {
System.out.println("The Connection Object is of Class: "+connection.getClass());
            try (ResultSet resultSet = pstmt.executeQuery();)
            {
                while (resultSet.next())
                {
                    System.out.println(resultSet.getString(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
                }
            }
            catch (Exception e)
            {
                connection.rollback();
                e.printStackTrace();
            }
        }
    }

}