derby database open and close connections

Question

We have a Utility Class that opens and disconnects the Derby DB Connection
The database is stored in a folder on C drive of a Windows 7 computer
The application is being written with JavaFX 8
We are NOT using transactions
Before using the Utility Class we would Open and Disconnect the connection with each CRUD function rs.close() con.close()
Our question has two parts
1. Do we really need to open and close the connection with each CRUD function?
2. Why is the Utility Class not closing the rs and stmnt?
The conn.close() fires when
We will post the code for the Utility Class and the Delete function
We are also using the code below in the Main Class to shutdown the Derby DB when the application is closed

    private void handle(WindowEvent e) throws SQLException {
    //Proper CLOSE of connection to DB keeps proper incrementing by 1 as set when the table is created
    JDBCUtil.closeConnection(conn);
    String conURL = "jdbc:derby:;shutdown=true";    
    try{
        DriverManager.getConnection(conURL);
    }catch (SQLException se){
    if(!(se.getErrorCode() == 50000) && (se.getSQLState().equals("XJ015")))
        System.err.println(se);
    }
    System.exit(0);
    Platform.exit(); 
}

Utility Class
public class JDBCUtil {

    public static Connection conn;
    public static Connection getConnection() throws SQLException {
    // The URL is specific to the JDBC driver and the database you want to connect
    String dbName="InfoDB";
    String dbURL = "jdbc:derby:C:/A_DerbyDataBase/DBName/" + dbName + ";create=true";
    //String dbURL = "jdbc:derby:DATABASE_NAME;create=true";
    // Set the user id and password
    //String userId = "app";
    //String password = "app";
    // Get a connection

    conn = DriverManager.getConnection(dbURL);
    // Set the auto-commit to false ONLY if you use Transactions
    /*conn.setAutoCommit(true);*/
    System.out.println("111111111111111111111111111 Get Connection ");
    return conn;
    }

    public static void closeConnection(Connection conn) throws SQLException {

        if (conn != null) {
            System.out.println("222222222222222222222 conn.close ");
            conn.close();
        }
    }
    public static void closeStatement(Statement stmnt) throws SQLException{

        if (stmnt != null) {
            System.out.println("3333333333333333333333 stmnt.close ");
            stmnt.close();
        }
    }
    public static void closeResultSet(ResultSet rs) throws SQLException {

        if (rs != null) {
            System.out.println("44444444444444444444444 rs.close ");
            rs.close();
        }
    }

    /*public static void commit(Connection conn) throws SQLException {

    if (conn != null) {
    conn.commit();
    }
    }
    public static void rollback(Connection conn) throws SQLException {

    if (conn != null) {
    conn.rollback();
    }
    }*/
    public static void main(String[] args) throws SQLException {
    //conn = JDBCUtil.getConnection();
    JDBCUtil.closeConnection(conn);
    }

And the Delete Function

    @FXML
private void onDelete(ActionEvent e) throws SQLException, IOException{
    conn = JDBCUtil.getConnection();
    String sql = "DELETE FROM infodata WHERE ID = ?";
    pstmt = conn.prepareStatement(sql); 
    int ID = Integer.valueOf(txfID.getText());
    pstmt.setInt(1, ID);
    pstmt.executeUpdate();
    pstmt.close();

    JDBCUtil.closeConnection(conn);
    ReadFromDB();

    btnEdit.setVisible(false);
    btnDelete.setVisible(false);
    btnCancel.setVisible(false);
    btnAdd.setVisible(true);
    txfInfo.setText("Record Deleted");
}

Answer 1

Database Access with a Connection Pool

Rather than writing your own connection logic, I advise using a connection pool, for example see the Baeldung Hikari Tutorial. You can use it like this:

1. Initialize the connection pool in your application's init method.
2. When you want to use a connection, use the Java try-with-resources construct, which will auto-close the connection (returning it to the connection pool) when you are done with it.
3. In your application's stop method, close the connection pool.

Sample wrapper class for DataSource connections (copied from Baeldung Hikari link above):

public class DataSource {

    private static HikariConfig config = new HikariConfig();
    private static HikariDataSource ds;

    static {
        config.setJdbcUrl( "jdbc_url" );
        config.setUsername( "database_username" );
        config.setPassword( "database_password" );
        config.addDataSourceProperty( "cachePrepStmts" , "true" );
        config.addDataSourceProperty( "prepStmtCacheSize" , "250" );
        config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );
        ds = new HikariDataSource( config );
    }

    private DataSource() {}

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

Sample database call using the Hikari connection pool with autoclose of connections using Java's try with resources construct (copied from Baeldung Hikari link above):

public static List<Employee> fetchData() throws SQLException {
    String SQL_QUERY = "select * from emp";
    List<Employee> employees = null;
    try (Connection con = DataSource.getConnection();
        PreparedStatement pst = con.prepareStatement( SQL_QUERY );
        ResultSet rs = pst.executeQuery();) {
            employees = new ArrayList<>();
            Employee employee;
            while ( rs.next() ) {
                employee = new Employee();
                employee.setEmpNo( rs.getInt( "empno" ) );
                employee.setEname( rs.getString( "ename" ) );
                employee.setJob( rs.getString( "job" ) );
                employee.setMgr( rs.getInt( "mgr" ) );
                employee.setHiredate( rs.getDate( "hiredate" ) );
                employee.setSal( rs.getInt( "sal" ) );
                employee.setComm( rs.getInt( "comm" ) );
                employee.setDeptno( rs.getInt( "deptno" ) );
                employees.add( employee );
            }
    } 
    return employees;
} 

For more info read the Baeldung tutorial and the Hikari site documentation:

• Link to HikariCP project.

Database Access without a Connection Pool

Now, you don't need to use a connection pool to do this, you can open and close a connection for each database call, however I would recommend using a connection pool for performance reasons and so that you don't end up trying to re-invent the wheel and ending up with something square rather than round.

I didn't try debugging or examining the connection utility class you have in your question, but, if you are going to replace it with a connection pool anyway, there is no reason to do that.

Sample code for accessing a database from JavaFX without a connection pool:

• JavaFX MySQL connection example please
• Non-Task based database access.
• JavaFX - Background Thread for SQL Query which uses a Task based sample for db access.

Some of the sample code is written to be minimal to demonstrate particular purposes, such as access to a database and feedback of data from a given table to a UI, and not as a general purpose database utility. For a robust implementation for an application that uses many database queries, a connection pool or a dedicated database connection manager class (such as you have in your question), is preferred.