Returning a ResultSet to another function

Question

I'm working on creating a program to manage a MySQL server. I have a working UI and code that can add new entries to the database as it should. Pictured below is the proto GUI that I'm using, the part that's important for the question is the table box that will show the entries of the database when it's working.

Text

The code that I have for reading the contents of the database works as it should. The program is structured so that I have separate classes for the interface, scene controller and SQL commands. The issue that I'm dealing with is that I can pull the needed data from the database but getting to the scene controller class to write it to the database simply isn't working.

The relevant pieces of code are included below is included below.

SQL functions:

public ResultSet readDataBase() throws Exception{
    try {
        // Establish connection to server
        Class.forName("com.mysql.cj.jdbc.Driver");
        connect=DriverManager.getConnection("jdbc:mysql://localhost/library?"+"user=tempUser&password=12345");
        statement=connect.createStatement();

        // Execute query and write the results
        resultSet=statement.executeQuery("select * from library.books");
        return resultSet;

        // writeResultSet(resultSet);

    } catch (Exception e){
        throw e;
    } finally {
        close();
    }
}

Scene controller code:

public void fillTable() throws SQLException{
    ResultSet resultSet=null;
    try {
        resultSet=commands.readDataBase();

        while(resultSet.next()) {
            String title = resultSet.getString("title");
            String author = resultSet.getString("author");
            String genre = resultSet.getString("genre");
            String format = resultSet.getString("format");
            String isbn = resultSet.getString("isbn");

            System.out.println("Title: " + title);
            System.out.println("Author: " + author);
            System.out.println("Genre: " + genre);
            System.out.println("Format: " + format);
            System.out.println("ISBN: " + isbn);
            System.out.println();
        }
    }catch (Exception e){
        e.printStackTrace();
    }
}

The purpose of the code in the scene controller block is simply to test for and read the result set for now. The code for writing to write the data from the result to their respective tables will be added later. This code was selected because I originally had it in the SQL functions class and it worked there, so I knew the code was good and did its job.

Whenever I run the code however, I get this error result.

java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.result.ResultSetImpl.checkClosed(ResultSetImpl.java:445)
at com.mysql.cj.jdbc.result.ResultSetImpl.next(ResultSetImpl.java:1726)
at library.test.windows.interfaceSceneController.fillTable(interfaceSceneController.java:108)
at library.test.windows.winInterface.main(winInterface.java:33)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at javafx.graphics/com.sun.javafx.application.LauncherImpl.launchApplicationWithArgs(LauncherImpl.java:464)
at javafx.graphics/com.sun.javafx.application.LauncherImpl.launchApplication(LauncherImpl.java:363)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:564)
at java.base/sun.launcher.LauncherHelper$FXHelper.main(LauncherHelper.java:1051)

I've done some reading into this prior but the only really relevant source of info I could find was in this post from over seven years ago. The answer for that question made reference to using JavaBeans as a place to put the information as an in-between but then used a class called a 'Biler' in their sample code. I can't find any reference to a Biler anywhere except his post and my IDE (IntelliJ if that's relevant) did not recognize it at all. I've been doing some experimentation with JavaBeans but I'm not sure it solving my problem.

In summary, my question is this. What do I need to do in order to properly pass a ResultSet from the function accessing the SQL server to the class containing the code for writing it to the table in my interface? I know this must be possible somehow but I can't seem to figure it out.

The `Biler` class is not a general class, but a pojo/JavaBean specific to that question (which btw is a duplicate of your question). The author of that answer used it because the OP of the question was selecting from a table called `Biler`. The point is to create a class an populate it with data from each row, and then pass a list of those objects instead of the result set. — Mark Rotteveel, Jul 28 '20 at 14:19

score 2 · Answer 1 · answered Jul 27 '20 at 16:46

2

Well, I assume you are closing the conneciton in your close() method. So that's why it is closed. The finally is executed after the method body but before the result is passed to the calling funtion.

try {

    return resultSet;

} finally {
    close();
}

IMHO it is a bad pratice to pass back a resultset anyway because the resultset is not really typesafe. If you change the query you will break the fillTable() function without noticing. And, of course, you would need to find a way to close the resultset and the underlying DB connection at some point. This might be challenging.

answered Jul 27 '20 at 16:46

fhossfel

2,041
16
24

2

This is what I've found out the hard way. I really underestimated how ResultSets worked and simply assumed that they worked like a typical variable such as an array where you can pass them around between functions and classes with no issues. My assumption was that closing the connection would do just that, close the connection but leave me with a table I could use for my interface safely cached in the memory. That was a potent learning curve. @fhossfel – TeaDrinker Jul 27 '20 at 20:48

Basil Bourque · Accepted Answer · 2020-07-28T16:45:12.733

2

Your database access code should be kept entirely separate from your user-interface code. The UI should not deal with an active ResultSet.

You need to copy the data out of the result set, or use a utility to do so for you.

`CachedRowSet`

A RowSet may be the solution. This interface extends ResultSet. See the Java Tutorials by Oracle for explanations.

You could use the CachedRowSet interface that keeps a copy of the result set data in memory, detached from the database. Oracle provides an implementation, as might other vendors such as your JDBC driver.

A CachedRowSet implementation is disconnected from the database. In contrast, a ResultSet maintains a database connection (the root of your problem). To quote the Javadoc:

A CachedRowSet object is a container for rows of data that caches its rows in memory, which makes it possible to operate without always being connected to its data source. Further, it is a JavaBeans™ component and is scrollable, updatable, and serializable.

That interface is extended by a few more interfaces.

POJOs

Plain old Java objects is another option, copying every field of every row from your ResultSet into properties of a Java object.

You can simply loop the result set while instantiating records. Or you can use any of a variety of frameworks to assist.

Records

Defining a class for such POJOs is much simpler when using the new records feature arriving in Java 16, now previewed in Java 15. The constructor, getters, toString, and equals & hashCode are all synthesized by the compiler. You simply declare the properties.

A record can be declared as a stand-alone class, or as a nested class, or even locally within a method.

edited Jul 28 '20 at 16:45

answered Jul 27 '20 at 16:58

Basil Bourque

303,325
100
852
1,154

This seems highly promising! I was under the impression that I was keeping the access code separate from the interface code through the use of the ResultSet. My mistake was not fully understanding how a ResultSet works. I thought of it as something akin to any standard variable like an Int, String or Array where it was more permanent and not dependent on the connection still being active. The cached Row set seems perfect for my issue and I'll test it as soon as I get back to my computer. – TeaDrinker Jul 27 '20 at 17:23
Okay so it took far longer than I are to think to get the cachedRowSet to work properly (the URL simply refused to work and I honestly don't know why) but I finally got it working. The cachedRowSet did everything I needed it to! Thank you so much for the help! – TeaDrinker Jul 27 '20 at 20:42
1

I would recommend not using `CachedRowSet`, but instead populate a list of pojos. The `javax.sql.RowSet` reference implementation included with Java is pretty buggy (or at least, every time I touch it, I come across issues with incorrect behaviour and wrong interpretations of the JDBC requirement). – Mark Rotteveel Jul 28 '20 at 14:21
@MarkRotteveel When making such a criticism, I suggest linking to the specific implementation that troubled you. A particular implementation having a certain problem does not invalidate the concept of row sets. – Basil Bourque Jul 28 '20 at 16:27
I mention the specific implementation: the reference implementation included with Java, which is what most people will be using. – Mark Rotteveel Jul 28 '20 at 17:05

Returning a ResultSet to another function

2 Answers2

CachedRowSet

POJOs

Records

`CachedRowSet`