Static Connection method in Java

Question

I'm confused regarding the use of static Connection to connect database. From what I've read, static connection shouldn't be used. This is my method:

private static Connection conn()
    {
        
        Connection connection = null;
            
        try {
            
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
            

        } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        
        return connection;
        
    }

Then to get results a new method:

public void getResult(ArrayList test)
    {
        
        try
        {
            
            Connection conn = conn();
            
            String sql = "select id from test";
            
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            
            while(rs.next())
            {
                
               test.add(rs.getInt("id"));
                
            }
            
            conn.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }

Now, I don't know if this is safe. The connection is being started new every time method is called(?), and I' am closing it. Certainly connection pooling would be good for performance, but is this thread safe?

Answer 1

Technically it's threadsafe, but prone to connection leakage if an exception is thrown. The conn.close() would not be called in that case. The conn.close() should be in a finally block, or the code modified to use try-with-resources, which will call it implicitly.

The try-with-resources (Java 7+) allows declaration of variables that implement the Autocloseable interface in the try statement. Those variables are then gracefully closed at the end of the try block.

try( Connection conn = conn(); ) 
{
  //.... other statements
} catch(SQLException e) {
  //.... exception code
}