如何使用相同的语句和结果集运行多个选择查询

Question

我正试图编写简单的Java web应用程序从数据库中获取数据。我需要在不同的数据库表上运行几个选择查询。

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";

我试着这样做:

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();

同一个语句只能运行一个查询。如何使用同一语句执行多个查询?

Answer 1

您可以将您想要的查询存储在数组中，并像这样遍历它:

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();
}

注:这是一个好主意，包括您的连接，ResultSet和语句对象在一个尝试…最后阻塞，以确保每次都能够关闭()它们。

Answer 2

为什么不能连接表并进行1次查询以获得所有结果?你的问题似乎很不合理。例如:

select title from books where bookid = bid
查询bookkid = bid

可以在一个查询中轻松完成:

查询图书的书名和作者