如何用JDBC打印SUM和AVG SQL值

Question

好的，我正在尝试使用JDBC打印SUM和AVG函数的结果。所以基本上，它不起作用。我做错了什么?

   <table border="1">
     <tr><th>Total Homes Sold</th><th>Total Sales Amount</th><th>Averages Price Per Home</th></tr>    
       <% rset = stmt.executeQuery("SELECT COUNT(home_ID) FROM home");%>
       <% rset1 = stmt.executeQuery("SELECT SUM(purchase_Price) FROM home");%>          
       <% rset2 = stmt.executeQuery("SELECT AVG(purchase_Price) FROM home");%>
       <tr>
         <td><%= rset.getString(1) %></td>
         <td><%= rset1.getString(1) %></td>
         <td><%= rset2.getString(1) %></td>
      </tr>
   </table>

Answer 1

主要问题是需要在结果集中(在executeQuery()之后)调用next()来检索结果集的第一行(即使只有一行)。

如果不这样做，则没有行可用，并且调用getString()会导致"ResultSet is closed"错误。

Answer 2

试着做:

   <tr>
   <% rset = stmt.executeQuery("SELECT COUNT(home_ID) FROM home");%>
     <td><%= rset.getString(1) %></td>
   <% rset = stmt.executeQuery("SELECT SUM(purchase_Price) FROM home");%>          
     <td><%= rset.getString(1) %></td>
   <% rset = stmt.executeQuery("SELECT AVG(purchase_Price) FROM home");%>
     <td><%= rset.getString(1) %></td>
  </tr>

或更好:

   <tr>
   <% rset = stmt.executeQuery("SELECT COUNT(home_ID), SUM(purchase_Price), AVG(purchase_Price) FROM home");%>
     <td><%= rset.getString(1) %></td>
     <td><%= rset.getString(2) %></td>
     <td><%= rset.getString(3) %></td>
  </tr>