Is it better to have one always open connection or is it better to establish a connection each time something is needed?

Question

Currently my program establishes a connection to the server each time it needs to and closes the connection after it grabs what it needs.

Connection con = DriverManager.getConnection(url, user, pass);      
//grab data      
con.close();

Is it better or worse practice and what difference would it make if I just had one global connection running from the start of the program. Something like

public static Connection con = DriverManager.getConnection(url, user, pass);

And just referenced it wherever I needed it with something like

classname.con.createStatement();

Answer 1

This depends on your application. You should code like what you need -- however one session for a big application can introduce problems.

For example thread safety. If multiple users are connected to your application one session / connection is out of the scope.

I would use one connection per request -- with an additional connection pool and maximum amount of open connections.

And because using a connection can throw exceptions put your code inside a try-with-resources block and then your connection is closed automatically.

try (Connection con = DriverManager.getConnection(myConnectionURL);) {
    // grab data
} catch (SQLException e) {
    e.printStackTrace();
}

Answer 2

you should follow singleton design pattern for connecting Database, below is example for Singleton design pattern for DB connection

 /**
     * Singleton for connecting to a database through JDBC
     * 
     *
     **/

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

    public class JDBC {

        private static Connection connection = null;
        private final static String ADRESS   = "";
        private final static String DATABASE = "";
        private final static String USER     = "";
        private final static String PASSWORD = "";
        private final static String PORT     = "";
        private final static String DRIVER   = "";

        /**
         * Method that loads the specified driver
         * 
         * @return void
         **/

        private static void loadDriver() {
            try {
                Class.forName(DRIVER);
            }
            catch (Exception e) {
                errorHandler("Failed to load the driver " + DRIVER, e);
            }
        }

        /**
         * Method that loads the connection into the right property
         * 
         * @return void
         **/

        private static void loadConnection() {
            try {
                connection = DriverManager.getConnection(getFormatedUrl(), USER, PASSWORD);
            }
            catch (SQLException e) {
                errorHandler("Failed to connect to the database " + getFormatedUrl(), e);         
            }
        }

        /**
         * Method that shows the errors thrown by the singleton
         * 
         * @param  {String}    Message
         * @option {Exception} e
         * @return  void
         **/

        private static void errorHandler(String message, Exception e) {
            System.out.println(message);  
            if (e != null) System.out.println(e.getMessage());   
        }

        /**
         * Method that returns the formated URL to connect to the database
         * 
         * @return {String}
         **/

        private static String getFormatedUrl() {
            return ADRESS + ":" + PORT + "/" + DATABASE;
        }

        /**
         * Static method that returns the instance for the singleton
         * 
         * @return {Connection} connection
         **/

        public static Connection getConnection() {
            if (connection == null) {
                loadDriver();
                loadConnection();
            }
            return connection;
        }

        /**
         * Static method that close the connection to the database
         * 
         * @return void
         **/

        public static void closeConnection() {
            if (connection == null) {
                errorHandler("No connection found", null);
            }
            else {
                try {
                    connection.close();
                    connection = null;
                }
                catch (SQLException e) {
                    errorHandler("Failed to close the connection", e);
                }
            }
        }
    }