jdbc - two requests in one connection

Question

Java

Hello!
I started learning Java and ran into a problem with the database - I can’t execute the second sql query in one connection, having the first one open. The JDBC documentation says that another instance of the PreparedStatement object should be used for this, but this does not help me - when the second request is executed, the unread ResultSet of the first one is closed.

                conn = DriverManager.getConnection(
                                strURL + "?lc_ctype=WIN1251",properties);
                PreparedStatement q1 = conn.prepareStatement("select * from params");
                ResultSet r1 = q1.executeQuery();
                PreparedStatement q2 = conn.prepareStatement("select * from clients");
                ResultSet r2 = q2.executeQuery();
                r1.next();//Тут программа вываливается с ошибкой org.firebirdsql.jdbc.FBSQLException: The result set is closed
                r1.getObject(1); 
                r2.next();
                r2.getObject(1);

Full text at http://pastebin.com/
Do you really need to open a new connection for every request? Or am I doing something wrong?
Thanks in advance!

Answer 1

No, you don't need to open a new connection for every request. You need to close existing statements before calling new ones. It’s just that in this case, you have two different queries open at the same time, one of which pulls data from one table in batch mode, and the second pulls completely different data from the second table. It cannot be at the same time. In this case, you really need two connections (use a connection pool for more efficient work). Or you can pull data first from one query, close PrepareStatement and ResultSet, and only then pull data from the second table.

Answer 2

You will most likely need to get another connection for the second request. But I wouldn't do that.
If you need to simultaneously read from two places in the same database, then the problem is in the design. It's probably worth doing one of the following:
1) write one JOIN for two tables and return it
2) Read the first result into memory and work with this data while iterating over the second
3) Use a stored procedure in the database that will do all the logic and return the result .

Answer 3

The documentation says that Statement

By default, only one ResultSet object per Statement object can be opened at the same time.
Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects .
All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists

Answer 4

It is not clear how the comment to my own answer fell so deep ...
Try: Statement.getResultSet()instead ofStatement.executeQuery()

Answer 5

First of all, the used DBMS should support this possibility. Well, the driver, respectively. For example, it appeared in MSSQL not so long ago, since the 2005 version. As for your Firebird, I definitely can’t say, dig the docks.

Answer 6

This is highly dependent on the type of database and JDBC driver, but usually such tricks are not allowed. Well, think about it - transmitting two different data sets at the same time over one TCP connection at the same time is, although it is possible in principle, but usually the authors do not bother with this.

Answer 7

use a connection pool and fetch connections from the pool for each request. Then put back.