1

I am selecting data from two table with two different queries. I am using one connection and one resultset. Both tables are populated but the resultset.next() of the second query is returning false, although it has to be true.

I also tried to use two differend PreparedStatements and Connections but none of this worked out for me.

DataSource ds = null;
Connection c = null;
PreparedStatement ps = null;
String sql = "SELECT * FROM TABLE1"
String sql2 = "SELECT * FROM TABLE2"

ds = // My datasource
c = ds.getConnection();

ps = c.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();

while (resultSet.next()) {
    // do smth
    // works
}

ps.close();


ps = c.prepareStatement(sql2);
resultSet = ps.executeQuery();

while (resultSet.next()) {
    // do somth
    // does not work although TABLE2 is populated
}

ps.close();

So the program should jump into the second while-loop as there is data returing from the query sql2. Do you have any advise? Thanks!

nikoffm
  • 15
  • 4
  • 1
    Are you sure `table2` actually does have *committed* data? I would speculate that you created and populated both tables in an open session in another client, without any explicit commit; and the `table1` only appears because the DDL to create `table2` implicitly committed. – Alex Poole Jun 26 '19 at 10:10
  • Yes, 100% sure.. :( The tables are only selected by me and not created. Same query in SQL-Developer is returning data... – nikoffm Jun 26 '19 at 10:11
  • 1
    Explicitly closing the result set is good practice but shouldn't cause this effect; and I can't replicate using your code. Another - trivial but surprisingly common - mistake to rule out is that you aren't actually connecting as the same user or even to the same database as you are from SQL Developer, and so are looking at data from different tables than you expect. Particularly if you see the same issue with separate statements and connections. – Alex Poole Jun 26 '19 at 10:23
  • 1
    Your title seems unrelated to your question, as you're not using multiple statements in one result set. The fact you're reusing `variables` doesn't mean you're reusing objects. In any case, you'll need to provide a [mre]. – Mark Rotteveel Jun 26 '19 at 17:55

1 Answers1

0

Try closing the resultset before closing the preparedstatement.

Also, it is very good practice to use try / catch in order to clean things up if you get an exception. See Must JDBC Resultsets and Statements be closed separately although the Connection is closed afterwards?

Stew Ashton
  • 1,499
  • 9
  • 6