从数据库取出数据在jsp页面上以表格形式呈现，并对表格数据进行分页打印操作。

这几天在做一个物料出入库的编码设计，需要从数据库拿到数据，并以表格形式呈现在网页上，后期还需要对数据进行分页，实现打印操作。

基本思路如下：
1.连接数据库；
2.从数据库取出数据展示在jsp页面上；
3.对数据进行分页操作；
4.实现打印。
5.加一条根据当前数据生成特定二维码。
下面开始贴代码：

package com.newcomer.model;

import java.util.Date;

public class Materials {  
    private String ordNum;  
    private String purOrdNo;  
    private String matNum;  
    private float num;  
    private String matName;
    private String position;
    private Date date;

    public String getOrdNum() {
        return ordNum;
    }
    public void setOrdNum(String ordNum) {
        this.ordNum = ordNum;
    }
    public String getPurOrdNo() {
        return purOrdNo;
    }
    public void setPurOrdNo(String purOrdNo) {
        this.purOrdNo = purOrdNo;
    }
    public String getMatNum() {
        return matNum;
    }
    public void setMatNum(String matNum) {
        this.matNum = matNum;
    }
    public double getNum() {
        return num;
    }
    public void setNum(float num) {
        this.num = num;
    }
    public String getMatName() {
        return matName;
    }
    public void setMatName(String matName) {
        this.matName = matName;
    }
    public String getPosition() {
        return position;
    }
    public void setPosition(String position) {
        this.position = position;
    }
    public Date getDate() {
        return date;
    }
    public void setDate(Date date) {
        this.date = date;
    }


}  

数据获取：

package com.newcomer.servlet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;

import com.newcomer.model.Materials;
import com.newcomer.utils.DBConnection;

public class ShowMatDao {
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    private Connection conn;
    public void matDelete(String sql) {
        try {
            new DBConnection().getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            int rows = pstmt.executeUpdate(sql);
            if (rows >= 1) {
                System.out.println("--成功删除--");
            } else {
                System.out.println("--删除失败--");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }

    public ArrayList<Materials> getMaterialsList(String sql) {
        ArrayList<Materials> list = new ArrayList<Materials>();
        try {
            conn = new DBConnection().getConnection();//加上conn
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String ordNum = rs.getString(1);
                String purOrdNo = rs.getString(2);
                String matNum = rs.getString(3);
                Float num = rs.getFloat(4);
                String matName = rs.getString(5);
                String position = rs.getString(6);
                Date date = rs.getDate(7);

                Materials materials = new Materials();
                materials.setOrdNum(ordNum);
                materials.setPurOrdNo(purOrdNo); 
                materials.setMatNum(matNum);
                materials.setNum(num);
                materials.setMatName(matName);
                materials.setPosition(position);
                materials.setDate(date);
                list.add(materials);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}

连接数据库：

package com.newcomer.utils;  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnection {
    private static final String DBDRIVER = "com.mysql.jdbc.Driver";
    private static final String DBURL = "jdbc:mysql://localhost:3306/test?useSSL=false";
    private static final String DBUSER = "登录名";
    private static final String DBPASSWORD = "你的数据库密码";
    private Connection conn = null;

    public DBConnection() {
        try {
            Class.forName(DBDRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            this.conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        return this.conn;
    }

    public static void close(Connection conn){
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(PreparedStatement pstmt) {
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

servlet操作：

package com.newcomer.servlet;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.newcomer.model.Materials;


public class MatServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        this.doPost(request, response);
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        ShowMatDao showMatDao = new ShowMatDao();
        String sql = "select * from bar_code";
        ArrayList<Materials> list = showMatDao.getMaterialsList(sql);
        request.setAttribute("list", list);
        request.getRequestDispatcher("/index.jsp").forward(request, response);
    }
}

JSP页面：

<%@page import="com.newcomer.model.Materials"%>
<%@ page language="java" import="java.util.*" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%
    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>list</title>
<meta http-equiv="pragma" content="no-cache" />
<meta http-equiv="cache-control" content="no-cache" />
<meta http-equiv="expires" content="0" />
<link rel="stylesheet" type="text/css" href="./files/print.css" />
<script type="text/javascript" src="./files/jquery-1.4.2.min.js"></script>
<script type="text/javascript" src="./files/msswms_print.js"></script>
<script language="javascript">
function printWithAlert() {         
    document.all.WebBrowser.ExecWB(6,1);      
} 
function printSetup() {         
     document.all.WebBrowser.ExecWB(8,1);       
}      
function printPrieview() {         
     document.all.WebBrowser.ExecWB(7,1);       
}       

</script>
</head>
<%
    request.setCharacterEncoding("GBK");
%>
<body>

  <object  id=WebBrowser  classid=CLSID:8856F961-340A-11D0-A96B-00C04FD705A2 style="display:none"> 
  </object>  

<div id="print-content">

     <!-- 页面顶部信息 -->
     <div class="headInfo" id="headerInfo">     
     </div>

     <!-- 页面记录信息 -->
        <table class="tableTopBorder_3" id="tabContent"  style="align:center;">
        <c:forEach items="${list}" var="mat">
              <tbody>
                  <tr><td id="td_ordNum">订单编号：</td><td><c:out value="${mat.matNum}" /></td><td id="td_purOrdNo">采购单号：</td><td><c:out value="${mat.ordNum }" /></td></tr>
                  <tr><td id="td_matNum">物料编号：</td><td><c:out value="${mat.purOrdNo}" /></td><td id="td_num">数量：</td><td><c:out value="${mat.num}" /></td></tr>
                  <tr><td id="td_matName">物料名称：</td><td><c:out value="${mat.matName}" /></td><td rowspan="3" colspan="2"><img width="150px" height="150px" alt="二维码" src="${pageContext.request.contextPath}/qrCode?keycode=location.href"/></td></tr>
                  <!-- keycode的值就是扫描二维码之后显示的值 -->
                  <tr><td id="td_position">仓位：</td><td><c:out value="${mat.position}" /></td></tr>
                  <tr><td id="td_date">日期：</td><td><c:out value="${mat.date}" /></td></tr>

               </c:forEach>
           </tbody>
       </table> 

 <!-- 页码信息 -->
     <div class="signatureArea" id="footerInfo">
        <span class="floatRight pageNum">1/1</span>
     </div>

<div id="action-buttons" class="noPrint" >
      <br>
         <center>
        <input onclick="javasricpt:window.print()" type="button" value="&nbsp;&nbsp;打印&nbsp;&nbsp;" class="btn" id="showPrintButton"/>
          <input type=button value="打印设置" onClick="printSetup()" />  
          <button onclick ='printPrieview()' title='打印预览...' >打印预览</button></td>  
        </center>
    </div>
</div>

</body>
</html>

生成二维码的Servlet方法

package com.newcomer.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.zxing.client.j2se.MatrixToImageWriter;
import com.google.zxing.BarcodeFormat;
import com.google.zxing.WriterException;
import com.google.zxing.common.BitMatrix;
import com.google.zxing.qrcode.QRCodeWriter;

/** 
 * @Description: 生成二维码 （QR格式）
 * @author lwei
 */
public class BarCodeDServlet extends HttpServlet {

    /**   
     * @Fields serialVersionUID : serialVersionUID 
     */ 

    private static final long serialVersionUID = 1L;

    private static final String KEY = "keycode";
    private static final String SIZE = "msize";
    private static final String IMAGETYPE = "JPEG";

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        String keycode = req.getParameter(KEY);

        if (keycode != null && !"".equals(keycode)) {
            ServletOutputStream stream = null;
            try {
                int size=129;
                String msize = req.getParameter(SIZE);
                if (msize != null && !"".equals(msize.trim())) {
                    try{
                        size=Integer.valueOf(msize);
                    } catch (NumberFormatException e) {
                        //TODO output to log
                    }
                }
                stream = resp.getOutputStream();
                QRCodeWriter writer = new QRCodeWriter();
                BitMatrix m = writer.encode(keycode, BarcodeFormat.QR_CODE, size, size);
                MatrixToImageWriter.writeToStream(m, IMAGETYPE, stream);
            } catch (WriterException e) {
                e.printStackTrace();
            } finally {
                if (stream != null) {
                    stream.flush();
                    stream.close();
                }
            }
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        this.doGet(req, resp);
    }

}

至此，大功告成。其中需要用到的引用文件路径在此下载：http://download.csdn.net/download/weixin_39547589/9981381

分页显示在网页上的预览界面地址为：http://localhost:8080/项目名称/matServlet

重点：后缀名一定是Servlet.

效果图：