How can I close the database connection in this approach?

Question

So, I was making a JDBC CRUD program using Java Eclipse and to prevent data leak I am not able to determine when and how to close the connection. And in other instance I'd like to ask whether my program opens the connection at every function which makes it less desirable. Following is my code,

_getConnection code establishes the database connection,

package com.Database;

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

public class _getConnection {

    static final String JDBC_Driver = "com.mysql.jdbc.Driver";
    static final String URL = "jdbc:mysql://localhost:3306/rubyrail?useSSL=false";
    static final String Username = "root";
    static final String Password = "root";
    static Connection connection = null;


    public Connection connect()
    {
        //Connection connection = null;

        try {
              Class.forName("com.mysql.jdbc.Driver");              
              connection = DriverManager.getConnection(URL, Username, Password); 
        }
        catch(Exception e)
        {}

        return connection;
    }

    public static Connection close()
    {
        if (connection!=null)
        {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return connection;
    }
}

Database_Connection is the CRUD application,

package com.Database;

import java.sql.*;
import java.sql.Date;
import java.util.*;

public class Database_Connection
{
    static Scanner scanner = new Scanner(System.in);

    public static void Create()
    {
        _getConnection get_connect = new _getConnection();
        Connection conn = get_connect.connect();
        int item_no = 0;
        String item_name = null;
        int item_cost = 0;
        Statement stmt = null;
        System.out.println("\nEnter the following details,");
        System.out.println("\nItem Number: ");
        item_no = scanner.nextInt();
        scanner.nextLine();
        System.out.println("\nItem Name: ");
        item_name = scanner.nextLine();
        System.out.println("\nItem Cost: ");
        item_cost = scanner.nextInt();

        try 
        {
            String sql = "Insert into item (item_no, item_name, item_cost, last_update) values ("+item_no+",'"+item_name+"',"+item_cost+",CURDATE())";
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate(sql);
            //stmt.execute(sql);
        }
        catch(Exception e)
        {System.out.println(e);}


    }

    public static void Read()
    {
        _getConnection get_connect = new _getConnection();
        Connection conn = get_connect.connect();
        Statement stmt=null;

        try 
        {
            String sql = "Select * from item";
            stmt = conn.prepareStatement(sql);
            ResultSet resultset = stmt.executeQuery(sql);
            while (resultset.next())
            {
                int item_no = resultset.getInt("item_no");
                String item_name = resultset.getString("item_name");
                int item_cost = resultset.getInt("item_cost");
                Date last_update = resultset.getDate("last_update");

                System.out.print("Item Number: " + item_no);
                 System.out.print(", Item Name: " + item_name);
                 System.out.print(", Item Cost: " + item_cost);
                 System.out.println(", Last Updated: " + last_update);
            }
        }
        catch(Exception e)
        {System.out.println(e);}
    }

    public static void Update()
    {
        _getConnection get_connect = new _getConnection();
        Connection conn = get_connect.connect();
        Statement stmt=null;
        int item_no = 0;
        String item_name = null;
        int item_cost = 0;
        System.out.println("\nEnter the Item Number to be Updated,");
        System.out.println("\nItem Number: ");
        item_no = scanner.nextInt();
        scanner.nextLine();
        System.out.println("\nEnter the following details,");
        System.out.println("\nItem Name: ");
        item_name = scanner.nextLine();
        System.out.println("\nItem Cost: ");
        item_cost = scanner.nextInt();

        try 
        {
            String sql = "update item set item_name = '"+item_name+"',item_cost ="+item_cost+",last_update = CURDATE() where item_no = "+item_no;
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate(sql);
        }
        catch(Exception e)
        {System.out.println(e);}

    }

    public static void Delete()
    {
        _getConnection get_connect = new _getConnection();
        Connection conn = get_connect.connect();
        Statement stmt=null;
        int item_no = 0;
        System.out.println("\nEnter the Item Number to be Deleted,");
        System.out.println("\nItem Number: ");
        item_no = scanner.nextInt();

        try 
        {
            String sql = "delete from item where item_no = "+item_no;
            stmt = conn.prepareStatement(sql);
            stmt.executeUpdate(sql);
        }
        catch(Exception e)
        {System.out.println(e);}
    }

    public static void Close()
    {
        _getConnection.close();

        System.out.println("Closing Connection..");

        System.out.println("Connection Closed!");
    }

        public static void main (String args[])
        {
            _getConnection get_connect = new _getConnection();
            Connection conn = get_connect.connect();
            int choice= 0;
            try {
                  if(conn!=null)
                  while (choice < 6)
                  {
                      System.out.println("\n1. Create");
                      System.out.println("\n2. Read");
                      System.out.println("\n3. Update");
                      System.out.println("\n4. Delete");
                      System.out.println("\n5. Close");

                  choice = scanner.nextInt();
                  switch(choice)
                  {
                  case 1: Create();
                  break;

                  case 2: Read();
                  break;

                  case 3: Update();
                  break;

                  case 4: Delete();
                  break;

                  case 5: Close();
                  break;
                  }
                } 
            }
            catch (Exception e) {}
        }

}

I would like my solution to have the closing in the Close() function of the program.

Please learn about Java naming conventions, your code is harder to read than necessary because you use non-standard naming conventions. — Mark Rotteveel, May 02 '19 at 13:31
Also, your use of prepared statements is wrong, please learn about parameterized statements, and **do not** concatenate values into a query string. It is unsafe as it makes your code vulnerable to SQL injection. Furthermore using any of the `execute` methods that accept a String on a `PreparedStatement` is wrong (it should throw an exception, but MySQL is lenient here), you should use the `execute` method without a string parameter. — Mark Rotteveel, May 02 '19 at 13:36

score 0 · Answer 1 · answered May 02 '19 at 12:14

0

It's always a best practice to close the Database connection. You can add a finally block to close the database connection. The best practice is to close the connections in the below order ResultSet, Statement, and Connection in a finally block at the end of your program.

Please follow this link for more details [Accepted answer] https://stackoverflow.com/a/2225275/7719903

answered May 02 '19 at 12:14

Barefooter

61
6

Hi, but I wanted to know the solution which is more integrated to my program. I mean how can I put the syntax in the Close() funtion to make it work ? if the above code is correct it refreshes the code in the main function which works only when the connection is open. – devilboy477 May 02 '19 at 12:30
@devilboy477 You don't even need you `_connection` class at all, just use try-with-resources and open the connection for the shortest scope necessary. – Mark Rotteveel May 02 '19 at 13:32

score 0 · Accepted Answer · answered May 02 '19 at 13:45

You can change your code to something like:

public class DatabaseConnection {

    static final String URL = "jdbc:mysql://localhost:3306/rubyrail?useSSL=false";
    static final String Username = "root";
    static final String Password = "root";

    private static Connection createConnection() {
        return DriverManager.getConnection(URL, Username, Password);
    }

    public static void create() {
        try (Connection connection = createConnection()) {
            // do something with the connection
        } catch (SQLException e) {
            e.printStackTrace();
            // or something else to handle the error
        }
    }

    // same for the rest of your methods

    public static void main (String args[]) {
        int choice= 0;
        while (choice < 6) {
            System.out.println("\n1. Create");
            System.out.println("\n2. Read");
            System.out.println("\n3. Update");
            System.out.println("\n4. Delete");
            System.out.println("\n5. Close");

            choice = scanner.nextInt();
            switch(choice) {
            case 1: 
                create();
                break;
            // other cases
            }
        }
    }
}

This will create a connection for each method invocation, which may be less efficient, but will simplify resource management. If performance is of real importance, you should consider using a data source that provides connection pooling (eg HikariCP, Apache DBCP, etc). Using a connection pool will allow reuse of connections without your code having to worry about it beyond setting up the data source configuration.

Alternatively, create the connection once in your main, and pass it to each method you want to call:

public static void create(Connection connection) {
    try {
        // do something with connection
    } catch (SQLException e) {
        e.printStackTrace();
        // or something else to handle the error
    }
}
public static void main (String args[]) {
    try (Connection connection = createConnection()) {
        int choice= 0;
        while (choice < 6) {
            System.out.println("\n1. Create");
            System.out.println("\n2. Read");
            System.out.println("\n3. Update");
            System.out.println("\n4. Delete");
            System.out.println("\n5. Close");

            choice = scanner.nextInt();
            switch(choice) {
            case 1: 
                create(connection);
                break;
            // other cases
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
        // or something else to handle the error
    }
}

Hi, is it safe to open a connection in every method ? Doesn't it crowds with unused connections or it handles only one connection? I hope you got my concern. — devilboy477, May 02 '19 at 15:26
@devilboy477 The try-with-resources statement will automatically close the connection at the end of the try-block. — Mark Rotteveel, May 02 '19 at 16:22
Ah, I see. Thank you so much. You have been a great help. :) — devilboy477, May 03 '19 at 04:58

How can I close the database connection in this approach?

2 Answers2