How to run several select queries with same statement and result set?

Question

I am trying to write simple Java web application to get data from a database. I need to run several select queries on different database tables.

String queryOne = "select firstname from employees where empid = id";
String queryOne = "select title from books where bookid = bid";
String queryOne = "select auther from books where bookid = bid";

And I tried to do it like this:

Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet rs1 = statement.executeQuery(queryOne);

while (rs1.nest()) {
String firstName = rs1.getString(1);
}
statement.close();
connection.close();

I can only run one query with the same statement. How can I execute several queries with the same statement?

Solution 1

You could perhaps store the queries you want in an array and iterate through it like:

Connection conn = dataSource.getConnection();
try {
  Statement stmt = conn.createStatement();
  try {
    for (String q : queries) {  //queries is an array containing the 3 queries
      ResultSet rset = statement.executeQuery(q);
      try {
        rset.getString(1);
      } finally {
        rset.close();
      }
    }
  } finally {
    stmt.close();
  }
} finally {
  conn.close();
}

P.S. It is a good idea to enclose your Connection, ResultSet and Statement objects in a try...finally block in order for you to ensure that you are able to close() them everytime.

Solution 2

why can't you join tables and do 1 query to get all results? Your queries seems to be very unoptimised. As an example:

select title from books where bookid = bid
select auther from books where bookid = bid

can be done easily in one query:

select title, author from books where bookid = bid

Comments

[Alexis C.]

May this help : stackoverflow.com/questions/10797794/…

[Sean Kilb]

I get datasource like this: DataSource dataSource = (DataSource) context.lookup("jdbc/DatabaseName"); How do I add allowMultipleQueries flag to that string? Thank you.

[JB Nizet]

Why do you care? Why would using several statements be a bad thing?

[Montre]

Use a connection.prepareStatement() and the PreparedStatement you get is "reusable". You should do this always to get parameter substitution too.

[Sean Kilb]

If I use PreparedStatement how do I get several resultsets?

[Montre]

@SeanKilb Oh, I misread your question. You can only get multiple resultsets with JDBC when calling a stored procedure. If you want to minimise DB roundtrips, you'll have to make a single UNION query and add a discriminator column.

[Montre]

As of Java 7, you can use try-with-resources

[S1LENT WARRIOR]

@ EmanuelSaringan don't you think there will be any performance overhead with this approach?