首页 新闻 赞助 找找看

关于在一个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)

 

我是新手,请不要笑我错误的低级,请给出详细的具体的解决代码!!

 

谢谢了

horse_leo的主页 horse_leo | 初学一级 | 园豆:17
提问于:2013-07-16 11:04
< >
分享
最佳答案
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
Beyond-bit | 老鸟四级 |园豆:2885 | 2013-07-16 11:36

45行:

                b.setIssue(rs.getInt("issue"));

horse_leo | 园豆:17 (初学一级) | 2013-07-16 11:41

45行:

                b.setIssue(rs.getInt("issue"));

 

我已经知道问题错在哪里了

应该是 b.setIssue(rs1.getInt("issue"));
rs1,不应该是rs

horse_leo | 园豆:17 (初学一级) | 2013-07-16 11:46

@horse_leo: 

恭喜,你的这种做法很容易出现这种小问题的!建议稍微封装一下!方便调用!

Beyond-bit | 园豆:2885 (老鸟四级) | 2013-07-16 11:48
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册