how to make a one database connection for many methods in java?

Objects of class ExampleDAOImpl are not thread-safe. So if more than one thread use the same ExampleDAOImpl object and call methods at the same time, it could happen that one closes the connection then the other thread tries to use it.

Possible solutions:

• Ensure that the ExampleDAOImpl Objects are never used in multi-threaded context. This will be still error-prone.
• Better suggestion: instead of using a single Connection Object, use a ConnectionPool to get a connection at the beginning of each method and free it after you finish from it.

What the point of making a global connection when you opening connection in each method. This is likely waht happening when you call method2 from method1 it closes connection that is used by method1

Generally the good practice is to create connection once in application life and close the connection on application close/exit, as there is cost of opening and closing connection.

your codes are not thread-safe, because jdbc connection can not be used concurrently! Please Change Connection con to a ThreadLocal, and add two methods to connect and disconnection.

The codes will be like:

public class ConnectionHelper
{
private static ThreadLocal<Connection> con = new ThreadLocal<Connection>();

protected Connection getConnection()
{
if(con.get() ==null)
{
con.set(Database.connect());
}
return con.get();
}

protected Connection closeConnection()
{
if(con.get() !=null)
{
con.get().close;
}
}
}

in your DAO: public void method1 () { try { Connection con = ConnectionHelper.getConnection(); } finally { ConnectionHelper.closeConnection(); } }

create two function one for

  connection()

another for

  disconnection()

call those methods in every method

  function method()
 {
  //call 

  connection();

  //code;

  //call 

     disconnection();
  } 

If you are using a Connection Pool implementation like BoneCP, it is perfectly fine to open new connections for each method, just ensure that the method does not reference the instance variable, but rather a local variable.

On the other hand if you have no connection pool available and working with only one connection, you will have to find a suitable place to close that connection, since getting new ones without a pool is quite expensive.

If what you want is to DRY your code, then use abstract classes and lambdas

class ExampleDAOImpl implements ExampleDAO{

    // With lambdas
    public void method1 () {
       withConnection( (Connection con) -> {
           // do whatever you want with the connection
           // no need to open or close it
       })
    }

    // Old style abstract class
    public void method2 () {
       withConnection(new TaskExecutor(){
          void doStuff(Connection con){
             // do whatever you want with the connection
             // no need to open or close it
          }
       })
    }

    // This is the magic you have to add.
    private void withConnection(TaskExecutor executer){
        // Instantiate it to avoid race conditions
        // good thing this is the only place
        // you have to worry about opening and closing it
        Connection con = Database.connect();
        executer.doStuff(con);
        con.close();
    }

    private abstract class TaskExecutor{
        abstract void doStuff(Connection con);
    }

}

I solved my own problem with creating one connection for all methods :

 public void generateTestData() throws SQLException, ClassNotFoundException, IOException {
    ConnectionToDatabase connectionToDatabase = new ConnectionToDatabase();
    try (Connection connection = connectionToDatabase.connectToDatabase()) {
        {
            generateGroups(connection);
            generateCourses(connection);
            assignCoursesToStudents(connection);
        }
    }
}

Maybe for smb it will be useful :)