将ResultSet返回给另一个函数

huangapple go评论47阅读模式
英文:

Returning a ResultSet to another function

问题

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.

将ResultSet返回给另一个函数

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
...

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.

英文:

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.

将ResultSet返回给另一个函数

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.

答案1

得分: 2

我假设你在你的close()方法中关闭了连接。所以它被关闭了。finally 在方法体执行之后但在结果传递给调用函数之前执行。

try {
    return resultSet;
} finally {
    close();
}

在我看来,将结果集返回是一种不好的做法,因为结果集并不是真正类型安全的。如果你改变了查询,你可能会在不知不觉中破坏fillTable()函数。当然,你需要找到一种方法来在某个时刻关闭结果集和底层的数据库连接。这可能会有挑战性。

英文:

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.

答案2

得分: 2

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

你的数据库访问代码应该完全与用户界面代码分开。用户界面不应处理活动的 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.

一个 RowSet 可能是解决方案。这个接口扩展了 ResultSet。请参阅 Oracle 的 Java 教程 进行解释。

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.

您可以使用 CachedRowSet 接口,它将结果集数据的副本存储在内存中,与数据库分离。Oracle 提供了一个实现,其他供应商,如您的 JDBC 驱动程序,也可能提供。

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:

CachedRowSet 的实现与数据库断开连接。相比之下,ResultSet 保持着与数据库的连接(这是问题的根本)。引用一下 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.

> CachedRowSet 对象是一个用于缓存数据行的容器,它将数据行缓存在内存中,从而可以在不始终连接到其数据源的情况下进行操作。此外,它是一个 JavaBeans™ 组件,支持滚动、更新和序列化。

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.

POJO(普通的 Java 对象)是另一种选择,将每一行的每个字段从您的 ResultSet 复制到 Java 对象的属性中。

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.

当使用 Java 16 中引入的新 records 特性时,为这种 POJO 定义类要简单得多,现在已在 Java 15 中预览。构造函数、getter、toStringequalshashCode 都由编译器合成。您只需声明属性。

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

可以将记录声明为独立的类、嵌套类,甚至在方法内部。

英文:

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.

将ResultSet返回给另一个函数

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.

huangapple
  • 本文由 发表于 2020年7月28日 00:40:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/63119694.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定