关于在一个jsp中调用一个数据库中两张表的数据
1
悬赏园豆：20
[已解决问题]
解决于 2013-07-16 11:46
SearchDdwhoneServlet.java
package com.chk.doubleball.setvlet; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.chk.doubleball.model.Bonus; import com.chk.doubleball.model.DdwhOne; import com.chk.doubleball.util.DBUtil; public class SearchDdwhoneServlet extends HttpServlet { private static final long serialVersionUID = 1L; public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // 1、查询数据 List<DdwhOne> ddwhoneList = new ArrayList<>(); List<Bonus> bonusList = new ArrayList<>(); String sql1 = "select * from t_bonus where _id > (select count(*) from t_bonus) - 100;"; String sql = "select * from t_ddwh01 where _id > (select count(*) from t_ddwh01) - 100;"; Connection conn = DBUtil.getConn(); PreparedStatement pstmt = null; PreparedStatement pstmt1 = null; ResultSet rs = null; ResultSet rs1 = null; try { pstmt = conn.prepareStatement(sql); pstmt1 = conn.prepareStatement(sql1); rs = pstmt.executeQuery(); rs1 = pstmt1.executeQuery(); while(rs1.next()){ Bonus b = new Bonus(); b.setIssue(rs.getInt("issue")); b.setOrder_red_one(rs.getString("order_red_one")); b.setOrder_red_two(rs.getString("order_red_two")); b.setOrder_red_three(rs.getString("order_red_three")); b.setOrder_red_four(rs.getString("order_red_four")); b.setOrder_red_five(rs.getString("order_red_five")); b.setOrder_red_six(rs.getString("order_red_six")); b.setBlue(rs.getString("blue")); bonusList.add(b); } while (rs.next()) { DdwhOne d = new DdwhOne(); /*d.set_id(rs.getInt("_id")); d.setIssue(rs.getInt("issue")); d.setOrder_red_one(rs.getString("order_red_one")); d.setOrder_red_two(rs.getString("order_red_two")); d.setOrder_red_three(rs.getString("order_red_three")); d.setOrder_red_four(rs.getString("order_red_four")); d.setOrder_red_five(rs.getString("order_red_five")); d.setOrder_red_six(rs.getString("order_red_six")); d.setBlue(rs.getString("blue"));*/ d.setW_one(rs.getString("w_one")); d.setW_two(rs.getString("w_two")); d.setW_three(rs.getString("w_three")); d.setW_four(rs.getString("w_four")); d.setW_five(rs.getString("w_five")); d.setW_six(rs.getString("w_six")); d.setW_seven(rs.getString("w_seven")); d.setW_eight(rs.getString("w_eight")); d.setW_nine(rs.getString("w_nine")); d.setW_ten(rs.getString("w_ten")); d.setW_eleven(rs.getString("w_eleven")); d.setW_twelve(rs.getString("w_twelve")); d.setW_thirteen(rs.getString("w_thirteen")); d.setW_fourteen(rs.getString("w_fourteen")); d.setW_fifteen(rs.getString("w_fifteen")); d.setW_sixteen(rs.getString("w_sixteen")); d.setW_seventeen(rs.getString("w_seventeen")); d.setW_eighteen(rs.getString("w_eighteen")); d.setW_nineteen(rs.getString("w_nineteen")); d.setW_twenty(rs.getString("w_twenty")); d.setW_twentyone(rs.getString("w_twentyone")); d.setW_twentytwo(rs.getString("w_twentytwo")); d.setW_twentythree(rs.getString("w_twentythree")); d.setW_twentyfour(rs.getString("w_twentyfour")); d.setW_twentyfive(rs.getString("w_twentyfive")); d.setW_twentysix(rs.getString("w_twentysix")); d.setW_twentyseven(rs.getString("w_twentyseven")); d.setW_twentyeight(rs.getString("w_twentyeight")); d.setW_twentynine(rs.getString("w_twentynine")); d.setW_thirty(rs.getString("w_thirty")); d.setW_thirtyone(rs.getString("w_thirtyone")); d.setW_thirtytwo(rs.getString("w_thirtytwo")); d.setW_thirtythree(rs.getString("w_thirtythree")); d.setStatistics(rs.getInt("statistics")); ddwhoneList.add(d); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DBUtil.close(rs); DBUtil.close(pstmt); DBUtil.close(conn); } request.setAttribute("ddwhoneList", ddwhoneList); request.setAttribute("bonusList", bonusList); // 2、跳转 request.getRequestDispatcher("/test.jsp").forward(request, response); } public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request, response); } }
跳转的页面
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <% String path = request.getContextPath(); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'aaa.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> <style type="text/css"> body table { text-align: center; } </style> </head> <body> <br> <table align="center" border="0" cellspacing="0" cellpadding="0"> <tr> <td> <table border="1" cellspacing="0" cellpadding="0"> <tr> <td rowspan="2" height="18">期号</td> <td colspan="6" height="18">红球</td> <td rowspan="2" width="18" height="18">蓝球</td> </tr> <tr> <td width="18" height="18">一</td> <td width="18" height="18">二</td> <td width="18" height="18">三</td> <td width="18" height="18">四</td> <td width="18" height="18">五</td> <td width="18" height="18">六</td> </tr> <c:if test="${not empty bonusList }"> <c:forEach items="${bonusList }" var="a"> <tr> <td width="18" height="18">${a.issue }</td> <td width="18" height="18">${a.order_red_one }</td> <td width="18" height="18">${a.order_red_two }</td> <td width="18" height="18">${a.order_red_three }</td> <td width="18" height="18">${a.order_red_four }</td> <td width="18" height="18">${a.order_red_five }</td> <td width="18" height="18">${a.order_red_six }</td> <td width="18" height="18">${a.blue }</td> </tr> </c:forEach> </c:if> </table> </td> <td> <table border="1" cellspacing="0" cellpadding="0"> <tr> <td colspan="33" height="18">围红</td> <td rowspan="2" width="18" height="18">验证</td> </tr> <tr> <td width="18" height="18">01</td> <td width="18" height="18">02</td> <td width="18" height="18">03</td> <td width="18" height="18">04</td> <td width="18" height="18">05</td> <td width="18" height="18">06</td> <td width="18" height="18">07</td> <td width="18" height="18">08</td> <td width="18" height="18">09</td> <td width="18" height="18">10</td> <td width="18" height="18">11</td> <td width="18" height="18">12</td> <td width="18" height="18">13</td> <td width="18" height="18">14</td> <td width="18" height="18">15</td> <td width="18" height="18">16</td> <td width="18" height="18">17</td> <td width="18" height="18">18</td> <td width="18" height="18">19</td> <td width="18" height="18">20</td> <td width="18" height="18">21</td> <td width="18" height="18">22</td> <td width="18" height="18">23</td> <td width="18" height="18">24</td> <td width="18" height="18">25</td> <td width="18" height="18">26</td> <td width="18" height="18">27</td> <td width="18" height="18">28</td> <td width="18" height="18">29</td> <td width="18" height="18">30</td> <td width="18" height="18">31</td> <td width="18" height="18">32</td> <td width="18" height="18">33</td> </tr> <c:if test="${not empty ddwhoneList }"> <c:forEach items="${ddwhoneList }" var="a"> <tr> <td class="red_one">${a.w_one }</td> <td width="18" height="18">${a.w_two }</td> <td width="18" height="18">${a.w_three }</td> <td width="18" height="18">${a.w_four}</td> <td width="18" height="18">${a.w_five}</td> <td width="18" height="18">${a.w_six}</td> <td width="18" height="18">${a.w_seven}</td> <td width="18" height="18">${a.w_eight}</td> <td width="18" height="18">${a.w_nine}</td> <td width="18" height="18">${a.w_ten}</td> <td width="18" height="18">${a.w_eleven}</td> <td width="18" height="18">${a.w_twelve}</td> <td width="18" height="18">${a.w_thirteen}</td> <td width="18" height="18">${a.w_fourteen}</td> <td width="18" height="18">${a.w_fifteen}</td> <td width="18" height="18">${a.w_sixteen}</td> <td width="18" height="18">${a.w_seventeen}</td> <td width="18" height="18">${a.w_eighteen}</td> <td width="18" height="18">${a.w_nineteen}</td> <td width="18" height="18">${a.w_twenty}</td> <td width="18" height="18">${a.w_twentyone}</td> <td width="18" height="18">${a.w_twentytwo}</td> <td width="18" height="18">${a.w_twentythree}</td> <td width="18" height="18">${a.w_twentyfour}</td> <td width="18" height="18">${a.w_twentyfive}</td> <td width="18" height="18">${a.w_twentysix}</td> <td width="18" height="18">${a.w_twentyseven}</td> <td width="18" height="18">${a.w_twentyeight}</td> <td width="18" height="18">${a.w_twentynine}</td> <td width="18" height="18">${a.w_thirty}</td> <td width="18" height="18">${a.w_thirtyone}</td> <td width="18" height="18">${a.w_thirtytwo}</td> <td width="18" height="18">${a.w_thirtythree}</td> <td width="18" height="18">${a.statistics}</td> </tr> </c:forEach> </c:if> </table> </td> </tr> </table> <br> </body> </html>
控制台出现的错误提示：
java.sql.SQLException: Before start of result set at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:855) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2710) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2851) at com.chk.doubleball.setvlet.SearchDdwhoneServlet.doGet(SearchDdwhoneServlet.java:45)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1008) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722)
我是新手，请不要笑我错误的低级，请给出详细的具体的解决代码！！
谢谢了
最佳答案
0
1.泛型定义
List<DdwhOne> ddwhoneList = new ArrayList<>(); List<Bonus> bonusList = new ArrayList<>();
还能这样定义泛型的？
2.
DBUtil.close(rs);
DBUtil.close(pstmt);
DBUtil.close(conn);
安照你上面的代码写法，你这里只关闭了一个sql的connection
还需要关闭rs1
3.还有，你的错误在
com.chk.doubleball.setvlet.SearchDdwhoneServlet.doGet(SearchDdwhoneServlet.java:45)
45行你是神马？
收获园豆：20
45行：
b.setIssue(rs.getInt("issue"));
45行：
b.setIssue(rs.getInt("issue"));
我已经知道问题错在哪里了
应该是 b.setIssue(rs1.getInt("issue"));
rs1，不应该是rs
@horse_leo:
恭喜，你的这种做法很容易出现这种小问题的！建议稍微封装一下！方便调用！
