How to handle return ResultSet value after closing coonection in finally(Error Handling )?

Question

Here is my DbOperationProcess code

in this code for manipulating any data in database shown an error

for example for retrieving values to JTable it show error

it cannot return any value Why?

for example when calling this

StudentView.java

      String query="select * from tbl_student";

      rs=db_obj.getData(query);

here is the error

    java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:768)
    at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7008)
    at DbApp.ViewStudents.initTable(ViewStudents.java:49)
    at DbApp.ViewStudents.<init>(ViewStudents.java:23)

please correct me

package DbApp;

import java.sql.*;

/**
 *
 * @author DELL
 */
public class DbOperationProcess {

    ResultSet rs = null;
    int value = 0;
    Statement st = null;
    Connection conn = null;

    public ResultSet getData(String query) {
        try {
            conn = DbOperation.getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(query);
            System.out.println(query);
        } catch (Exception ae) {
            ae.printStackTrace();
        } finally {
            try {
                DbOperation.closeConnection(rs, st, conn);
            } catch (Exception ae) {
            }
        }
        return rs;
    }

    public void setData(String query) {
        try {
            conn = DbOperation.getConnection();
            st = conn.createStatement();
            st.execute(query);
        } catch (Exception ae) {
            ae.printStackTrace();
        } finally {
            try {
                DbOperation.closeConnection(rs, st, conn);
            } catch (Exception ae) {
            }
        }
    }

    public int setUpdate(String query) {
        try {
            conn = DbOperation.getConnection();
            st = conn.createStatement();
            int value = st.executeUpdate(query);
            System.out.println("query");
        } catch (Exception ae) {
            ae.printStackTrace();
        } finally {
            try {
                DbOperation.closeConnection(rs, st, conn);
            } catch (Exception ae) {
            }
        }
        return value;
    }
}

And here is my DbOperation Code

    /*
     * To change this template, choose Tools | Templates
     * and open the template in the editor.
     */

    package DbApp;

    import java.sql.*;
    /**
     *
     * @author DELL
     */
    public class DbOperation 
    {


        public static Connection getConnection()
        {
        Connection conn=null;;
        try
        {
            String driver = "org.gjt.mm.mysql.Driver";
            String databasename = "db_college";
            String url = "jdbc:mysql://localhost:3306/";
            String username = "root";
            String password = "123456";


            Class.forName(driver);

            conn=DriverManager.getConnection(url+databasename,username,password);
        }
        catch (ClassNotFoundException cnfe) 
        {

            System.out.println("JDBC Driver not found" + cnfe);

        } 
        catch (SQLException sqle) 
        {

            System.out.println("JDBC URL Error " + sqle);

        }
        catch(Exception ae)
        {
            ae.printStackTrace();
        }
        return conn;
        }

        public static void closeConnection(ResultSet rs,Statement st,Connection conn)
        {
        try
        {
           if(rs!=null) 
           {
               rs.close();
           }
           if(st!=null) 
           {
               st.close();
           }
           if(conn!=null) 
           {
               conn.close();
           }
        }
        catch(Exception ae)
        {

        }

        }

    }

Answer 1

Your getData(String query) method makes no sense. You should have a different method for each DB query. You should consume the ResultSet immediately after executing the query. You shouldn't return a ResultSet. You should return an Object / Collection of Objects that contain the data returned by the query.

You might think that you are saving some lines of code by using the same ResultSet getData(String query) method for all your queries, but this approach doesn't work :

1. You are passing it a static SQL String, instead of passing it the query parameters and executing a dynamic SQL query with a PreparedStatement, which is much safer.

2. If you don't fetch the data from the ResultSet in the same method that produced it, you can't close the ResultSet and the DB connection until you do.