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

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?

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
– Sean Kilb, Commented May 26, 2013 at 14:39
Why do you care? Why would using several statements be a bad thing? — JB Nizet
– JB Nizet, Commented May 26, 2013 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
– millimoose, Commented May 26, 2013 at 14:40

user2422457 · Accepted Answer · 2013-05-26 14:40:14Z

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.

answered May 26, 2013 at 14:40

user2422457

Sign up to request clarification or add additional context in comments.

3 Comments

millimoose Over a year ago

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

S1LENT WARRIOR Over a year ago

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

Sudhir Gaurav Over a year ago

Any reply how to overcome performance overhead ? . I have similar requirement but performance is main concern

rnglbd · Accepted Answer · 2013-05-27 13:25:17Z

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

edited May 27, 2013 at 13:25

answered May 26, 2013 at 14:42

rnglbd

4713 silver badges6 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related