2

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?

Luiggi Mendoza
  • 85,076
  • 16
  • 154
  • 332
Sean Kilb
  • 959
  • 5
  • 16
  • 27
  • 1
    May this help : http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement – Alexis C. May 26 '13 at 14:33
  • I get datasource like this: DataSource dataSource = (DataSource) context.lookup("jdbc/DatabaseName"); How do I add allowMultipleQueries flag to that string? Thank you. – Sean Kilb May 26 '13 at 14:39
  • 2
    Why do you care? Why would using several statements be a bad thing? – JB Nizet May 26 '13 at 14:40
  • Use a `connection.prepareStatement()` and the `PreparedStatement` you get is "reusable". You should do this always to get parameter substitution too. – millimoose May 26 '13 at 14:40
  • If I use PreparedStatement how do I get several resultsets? – Sean Kilb May 26 '13 at 14:41
  • @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. – millimoose May 26 '13 at 14:44

2 Answers2

5

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.

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

rnglbd
  • 471
  • 3
  • 6