如何使用Java Servlet和Jsp将数据库查询显示到表中？

问题描述：

我对网络开发很新，所以忍受我=）

I'm very new to web development, so bear with me =)

这里是我的技术堆栈我正在使用
SQL Server 2008 R2，
Tomcat Server 6.0，
Java 1.6，
jQuery，

Ok here is my tech stack I'm working with SQL Server 2008 R2, Tomcat Server 6.0, Java 1.6, jQuery,

这是我的问题。

我需要运行一堆报告类型查询，我需要将这些结果显示为JSP页面上的一个表供用户查看。我遇到的问题是如何执行此操作的方法。

I have a bunch of report type queries I need to run, and I need to display those results as a table on a JSP page for the user to see. The problem I am running into is the method of how to do this.

大多数在线示例显示将查询结果存储在ResultSet中，并将其放入ArrayList和JavaBeans setter / getters，然后在JSP中调用那个Arraylist。

Most online examples show to store the results of the query in a ResultSet, and putting it into an ArrayList with JavaBeans setters/getters, and then calling that Arraylist in the JSP.

另一个问题是，所有这些查询都有不同的列标题，这些例子没有显示如何动态创建列标题（大多数示例都是硬编码的）。

Another problem is, all these queries have different column headers, and these examples do not show how to dynamically create the column headings (most examples are hardcoded).

这是我到目前为止生成报告的示例：

Here is an example of what I can do so far to generate a report:

    //inside the servlet and sets stuff inside a JavaBean
public List<ClaimInfoBean> getClaimInfo() throws SQLException {
    List<ClaimInfoBean> claimList = new ArrayList<ClaimInfoBean>();
    while (results.next()) {
        ClaimInfoBean claim = new ClaimInfoBean();
        claim.setClaimNum(results.getString(1));
                    //set more stuff for the bean
        }

        claimList.add(claim);

    }

在JSP页面上

        //scriptlet (I know this isnt the best way, but only relevant example I can find)
        List<ClaimInfoBean> claimList = claimData.getClaimInfo();
        Iterator<ClaimInfoBean> claimListIterator = claimList.iterator();
        ClaimInfoBean claim;
        while ( claimListIterator.hasNext() ) {
           claim = ( ClaimInfoBean ) claimListIterator.next();
        //call getXXX to plug into a table

对于这个servlet，我只关心正在运行x查询，以在用户单击链接时显示y表。这个servlet应该接受任何查询并显示任何类型的表，所以我上面显示的方式是非常严格的。

For this servlet, all I care about is running x query, to display y table, when a user clicks a link. This servlet should take any query and display any kind of table, so the way I shown above is very restrictive.

任何正确方向的帮助将不胜感激！

Any help in the right direction would be appreciated!

答

这是一个简单的Servlet的内容。 getConnection返回与数据库的连接，而buildDBRequest返回一个设置为执行SQL查询的PreparedStatement。两者都留作练习供读者实施。

Here is the guts of a simple Servlet. getConnection returns a connection to your database, and buildDBRequest returns a PreparedStatement that is set up to execute an SQL query. Both are left as exercises for the reader to implement.

这是JSTL的关键驱动因素。在JSP中，JSTL用于迭代和呈现数据。 JSTL中的一个接口是javax.servlet.jsp.jstl.sql.Result接口。它通常由JSTL SQL标签创建，没有人使用（它们非常有趣且非常灵活 - 用于快速和脏的东西，它们可以很好地工作）。

The key driver for this is the JSTL. In the JSP, JSTL is used to iterate and present the data. One of the interfaces in JSTL is the javax.servlet.jsp.jstl.sql.Result interface. It is normally created by the JSTL SQL tags, which nobody uses (they they're quite interesting and really flexible -- for quick and dirty stuff they work pretty well).

JSTL还提供了一个ResultSupport类，可以方便地将JDBC ResultSet转换为兼容的JSTL Result对象。因此，在这种情况下，所有Servlet真正需要做的是将结果集链接到JSTL Result，然后转发到JSP。注意，当它创建Result对象时，它将加载查询的所有行。 100行没什么大不了的，1M行的潜在问题。

JSTL also provides a ResultSupport class that conveniently converts a JDBC ResultSet in to a compliant JSTL Result object. So, all the Servlet really needs to do in this case is link up a result set to a JSTL Result, and then forwards to the JSP. Note, when it creates the Result object, it will load in all of the rows of the query. No big deal with 100 rows, potential issue with 1M rows.

您可以看到它显示Result的columnNames集合中的列标题，然后迭代结果中的每一行以及每行的每一列。

You can see it display the column headers from the columnNames collection of the Result, and then it iterates over each of the rows in the Result and across each column of each row.

ReportServlet：

ReportServlet:

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
    Connection c = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = getConnection();
        ps = buildDBRequest(request);
        rs = ps.executeQuery();
        Result result = ResultSupport.toResult(rs);
        request.setAttribute("result", result);
        RequestDispatcher rd = request.getRequestDispatcher("/showResult.jsp");
        rd.forward(request, response);
    } catch(SQLException ex) {
        throw new ServletException(ex);
    } finally {
        close(c, ps, rs);
    }
}

showResult.jsp

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Report Results</title>
    </head>
    <body>
        <h1>Report Results</h1>
        <table border="1">
            <!-- column headers -->
            <tr>
                <c:forEach var="columnName" items="${result.columnNames}">
                    <th><c:out value="${columnName}"/></th>
                </c:forEach>
            </tr>
            <!-- column data -->
            <c:forEach var="row" items="${result.rowsByIndex}">
                <tr>
                    <c:forEach var="column" items="${row}">
                        <td><c:out value="${column}"/></td>
                    </c:forEach>
                </tr>
            </c:forEach>
        </table>
    </body>
</html>

如何使用Java Servlet和Jsp将数据库查询显示到表中？

相关推荐