Java Database minimize connection creation

Question

I am a very beginner to Java and for one of my assignments. I have to do database operations. I have this Java code for inserting data to a database and it works fine.

public class JavaDBWork
{

  public static void main(String[] args)
  {
    try
    {
      // create a mysql database connection
      String myDriver = "org.gjt.mm.mysql.Driver";
      String myUrl = "jdbc:mysql://localhost/test";
      Class.forName(myDriver);
      Connection conn = DriverManager.getConnection(myUrl, "root", "");

      // create a sql date object so we can use it in our INSERT statement
      Calendar calendar = Calendar.getInstance();
      java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

      // the mysql insert statement
      String query = " insert into users (first_name, last_name, date_created, is_admin, num_points)"
        + " values (?, ?, ?, ?, ?)";

    //Other Code
}

My question is every time the code runs, my code has to create a database connection and prepared statement objects and as I understand it is a costly operation.

Is there any way that I can keep a single connection to database and use that to perform my database work?

The cost of creation of connection is relative, and it is really not that important to consider for 'toy' projects like student assignments. Totally different subject: loading `org.gjt.mm.mysql.Driver` is really 15+ years out of date, the MySQL driver class has been called `com.mysql.jdbc.Driver` for years now (the `org.gjt.mm.mysql.Driver` is just preserved for backwards compatibility). — Mark Rotteveel, Aug 26 '17 at 08:21
Related (although maybe a little dated): https://stackoverflow.com/questions/2835090/how-to-establish-a-connection-pool-in-jdbc — Mark Rotteveel, Aug 26 '17 at 08:25
such optimisation in command line one shot program ("main") totally has not sense. If You want different type algorithm, show idea. — Jacek Cz, Aug 26 '17 at 10:03
@MarkRotteveel Yes I agree it is not important in school assignments but I asked it because my assignment is somewhat considerable web project and this is some test code.Thanks for pointing out the outdated driver as well. :) — Ran_Macavity, Aug 26 '17 at 14:17
@JacekCz Yes code optimization for this program does not make much sense. My complete assignment is a student management system web application and required to put it in a server like Jboss. When such thing happens, I thought of optimizing my code further.Thanks for your answer :) — Ran_Macavity, Aug 26 '17 at 14:17
servers JBos, Tomcat and others, have CONNECTION POOL ready to go, and is well documented, almost on first page of documentation. — Jacek Cz, Aug 26 '17 at 14:23
Pool on the server is 10 lines of configuration (may be specific on server) and 3 lines of Java code — Jacek Cz, Aug 26 '17 at 14:29
When using Tomcat or JBoss, you would use connection pooling https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-jboss.html — Jaydee, Aug 28 '17 at 12:01
Thanks Jacek Cz and Jaydee. When I am deploying the application to an application server, I will follow your guidelines :) — Ran_Macavity, Aug 28 '17 at 18:54
There's no need to call `Class.forName(myDriver);` (and therefore no need to have the variable `myDriver`). — DodgyCodeException, Jan 29 '18 at 13:02

score 3 · Accepted Answer · answered Aug 28 '17 at 12:04

You can use the design pattern Singleton Connection like this :

1- create a class SingletonConnection.java look like that :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.EmptyStackException;

public class SingletonConnection   {


    private static Connection connection = null ;
    static 
    {
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection
                    ("jdbc:mysql://localhost:3306/gestion_comptes","root","");
        }catch(Exception ex)
        {

        }

    }
    public static Connection getConnection() throws Exception {
        return connection;
    }


}

And in your other class call it like that :

 public  class DaoImpl{

        public Connection connection = SingletonConnection.getConnection();

        public DaoImpl() throws Exception 
        {
            if(connection==null)
                throw new Exception("impossible de se connecter à la base de données");
        }
}

Thank you for your answer.I am quite new to java so have to learn design pattern as well. After some search, it seems this approach solves my problem. Thanks again :) — Ran_Macavity, Aug 28 '17 at 18:52
@MarkRotteveel If so, can you please let me know your suggestion? I do not need codes,just an overview of how to do it.Even an external source would suffice :) — Ran_Macavity, Aug 29 '17 at 18:48
@Ran_Macavity I am glad to hear that.thanks this approach is using in java application and web. this is a design pattern — Med Elgarnaoui, Aug 29 '17 at 21:08

user6266369 · Answer 2 · 2017-08-29T11:08:14.823

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Calendar;

public class JavaDBWork {

    private final String DATBASE = "myDatabasename";
    private final String URL = "jdbc:mysql://localhost:3306/" + DATBASE + "?useSSL=false";
    private final String USERNAME = "myUsername";
    private final String PASSWORD = "myPassword";

    private Connection databaseConnection;

    // Main Method to init
    public static void main(String[] args) {
        // build an javaDBWork with init connection one time
        JavaDBWork javaDBWork = new JavaDBWork();
        // call methods in javaDBWork you like
        javaDBWork.doSomething();
    }

    public JavaDBWork() {
        // create a mysql database connection at init
        this.databaseConnection = databaseConnection();
    }

    private Connection databaseConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception exception) {
            exception.printStackTrace();
        }
        return connection;
    }

    public void doSomething() {
        try {
            // create a sql date object so we can use it in our INSERT statement
            Calendar calendar = Calendar.getInstance();
            java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

            // the mysql insert statement       
            String sql = " insert into users (first_name, last_name, date_created, is_admin, num_points)" + " values (?, ?, ?, ?, ?)";
            PreparedStatement preparedStatement = null;

            preparedStatement = databaseConnection.prepareStatement(sql);
            preparedStatement.setString(1, "Lucky");
            preparedStatement.setString(2, "Luke");
            ...

            preparedStatement.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
        }
    }

You can do something like this.

Init the database connection one time and hold the connection in a object which you call when you need.

EDIT: Example when you need connection in another class:

public class MyMainClass {

    private JavaDBWork javaDBWork;

    // Main Method to init
    public static void main(String[] args) {
        new MyMainClass();
    }

    public MyMainClass() {
        // build an javaDBWork with init connection one time
        javaDBWork = new JavaDBWork();
        // call methods in javaDBWork you like
        javaDBWork.doSomething();
    }

}


public class JavaDBWork {

    private final String DATBASE = "myDatabasename";
    private final String URL = "jdbc:mysql://localhost:3306/" + DATBASE + "?useSSL=false";
    private final String USERNAME = "myUsername";
    private final String PASSWORD = "myPassword";

    private Connection databaseConnection;

    public JavaDBWork() {
        // create a mysql database connection at init
        this.databaseConnection = databaseConnection();
    }

    private Connection databaseConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception exception) {
            exception.printStackTrace();
        }
        return connection;
    }

    public void doSomething() {
        try {
            // create a sql date object so we can use it in our INSERT statement
            Calendar calendar = Calendar.getInstance();
            java.sql.Date startDate = new java.sql.Date(calendar.getTime().getTime());

            // the mysql insert statement
            String sql = " insert into users (first_name, last_name, date_created, is_admin, num_points)" + " values (?, ?, ?, ?, ?)";
            PreparedStatement preparedStatement = null;

            preparedStatement = databaseConnection.prepareStatement(sql);
            preparedStatement.setString(1, "Lucky");
            preparedStatement.setString(2, "Luke");

            preparedStatement.executeUpdate();
        } catch (Exception e) {
            // TODO: handle exception
        }
    }
}

Thank you for your answer :) It helps if we code within the class. What if we want a connection outside the class? Any ideas on that? — Ran_Macavity, Aug 28 '17 at 18:50
Just put the main() method into the class you need and use as shown yesterday. Maybe you need a global variable. See my edit in the answer. :) — user6266369, Aug 29 '17 at 11:08

Java Database minimize connection creation

2 Answers2