实现简单的门禁查询功能
目前对SSH框架、ajax等技术不熟，现在用jsp+servlet+javaBean实现简单的门禁查询功能，数据库用SQLserver2005 数据库名：XQMJ
文件：DoorEvent.java(javabean);
doorAction.java(servlet);
DoorConnDB;
doorManager.jsp;
doorQuery.jsp
需要注意几点：
1、在查询条件选择时，select中选项直接从数据库中取得rs.next, 可以用ajax优化
2、查询数据库时，同时用到2张表，用union连接；注意查询条件的判断
3、数据不是很多，实现了一个假分页，直接把查询的结果放在ArrayList中，然后对其分页
4、页面使用局部刷新，用到了iframe,注意子窗口获取父窗口的数据
5、注意js向servlet传值和jsp向servlet传值时字符编码问题（中文乱码）
DoorActionDoorEventDoorConnDBdoorManger.jspdoorQuery.jsp
之二（用jstl标签+pager-taglib分页+导出Excel表格(POI控件)）
文件包括；
DoorEvent
package com.sino.ibms.model; import java.util.Date; public class DoorEvent { private Date EA_InsertDateTime; private String EA_Co_Name; private String EA_Pe_Code; private String EA_Pe_FirstName; private String EA_Pe_De_Name; private String EA_eventName; public Date getEA_InsertDateTime() { return EA_InsertDateTime; } public void setEA_InsertDateTime(Date eA_InsertDateTime) { EA_InsertDateTime = eA_InsertDateTime; } public String getEA_Co_Name() { return EA_Co_Name; } public void setEA_Co_Name(String eA_Co_Name) { EA_Co_Name = eA_Co_Name; } public String getEA_Pe_FirstName() { return EA_Pe_FirstName; } public void setEA_Pe_FirstName(String eA_Pe_FirstName) { EA_Pe_FirstName = eA_Pe_FirstName; } public String getEA_Pe_De_Name() { return EA_Pe_De_Name; } public void setEA_Pe_De_Name(String eA_Pe_De_Name) { EA_Pe_De_Name = eA_Pe_De_Name; } public String getEA_eventName() { return EA_eventName; } public void setEA_eventName(String eA_eventName) { EA_eventName = eA_eventName; } public String getEA_Pe_Code() { return EA_Pe_Code; } public void setEA_Pe_Code(String eA_Pe_Code) { EA_Pe_Code = eA_Pe_Code; } }
DoorAction
package com.sino.ibms.action; import java.io.IOException; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import javax.servlet.RequestDispatcher; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import com.sino.ibms.action.DoorConnDB; import com.sino.ibms.model.DoorEvent; @SuppressWarnings("serial") public class DoorAction extends HttpServlet { /** * Constructor of the object. */ public DoorAction() { super(); } /** * Destruction of the servlet. <br> */ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } @SuppressWarnings({ "static-access", "deprecation" }) public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); ArrayList<DoorEvent> list = new ArrayList<DoorEvent>(); String cname = new String(request.getParameter("cname").getBytes( "ISO-8859-1"), "utf-8"); String pename = new String(request.getParameter("pename").getBytes( "ISO-8859-1"), "utf-8"); String eventname = new String(request.getParameter("eventname") .getBytes("ISO-8859-1"), "utf-8"); String btime = request.getParameter("btime"); String etime = request.getParameter("etime"); String flag = request.getParameter("flag"); String sql = "select EA_InsertDateTime,EA_Co_Name,EA_eventName,EA_Pe_FirstName,EA_Pe_Code,EA_Pe_DE_Name from dbo.AIC_Access_EventMessageDataAccess where 1=1 "; if (cname != null && !"ALL".equals(cname)) { sql += " and EA_Co_Name=N'" + cname + "'"; } if (!"".equals(pename) && !"ALL".equals(pename)) { sql += " and EA_Pe_FirstName=N'" + pename + "'"; } if (pename.equals("")) { sql += " and EA_Pe_FirstName is null"; } if (eventname != null && !"ALL".equals(eventname)) { sql += " and EA_eventName=N'" + eventname + "'"; } if ((btime != null) && (!"".equals(btime))) { sql += " and convert(datetime,'" + btime + "') <= convert(datetime,EA_InsertDateTime)"; } if ((etime != null) && (!"".equals(etime))) { sql += " and convert(datetime,'" + etime + "') >= convert(datetime,EA_InsertDateTime)"; } sql += " order by EA_InsertDateTime desc"; DoorConnDB db = new DoorConnDB(); ResultSet rs = db.executestatement(sql); if (flag.equals("toQuery")) { try { while (rs.next()) { DoorEvent de = new DoorEvent(); de.setEA_InsertDateTime(rs.getTimestamp(1)); de.setEA_Co_Name(rs.getString(2)); de.setEA_eventName(rs.getString(3)); de.setEA_Pe_FirstName(rs.getString(4)); de.setEA_Pe_Code(rs.getString(5)); de.setEA_Pe_De_Name(rs.getString(6)); list.add(de); } // request.getSession().setAttribute("list", list); request.setAttribute("list", list); request.setAttribute("cname", cname); request.setAttribute("pename", pename); request.setAttribute("eventname", eventname); request.setAttribute("btime", btime); request.setAttribute("etime", etime); RequestDispatcher rd = request .getRequestDispatcher("/page/door/doorQuery.jsp"); rd.forward(request, response); } catch (Exception e) { } } else { try { // 获取总列数 int CountColumnNum = rs.getMetaData().getColumnCount(); int i = 1; // 创建Excel文档 HSSFWorkbook wb = new HSSFWorkbook(); // sheet 对应一个工作页 HSSFSheet sheet = wb.createSheet("巡更表中的数据"); HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始 HSSFCell[] firstcell = new HSSFCell[CountColumnNum]; String[] names = new String[CountColumnNum]; names[0] = "钮号"; names[1] = "人员"; names[2] = "地点"; names[3] = "巡检时间"; names[4] = "棒号"; names[5] = "事件"; for (int j = 0; j < CountColumnNum; j++) { firstcell[j] = firstrow.createCell((short) j); firstcell[j].setCellValue(new HSSFRichTextString(names[j])); } while (rs.next()) { // 创建电子表格的一行 HSSFRow row = sheet.createRow(i); // 下标为1的行开始 for (int j = 0; j < CountColumnNum; j++) { // 在一行内循环 HSSFCell cell = row.createCell((short) j); // 设置表格的编码集，使支持中文 // // 先判断数据库中的数据类型 // 将结果集里的值放入电子表格中 cell.setCellValue(new HSSFRichTextString(rs .getString(j + 1))); } i++; } response.setHeader("Content-Disposition", "attachment;filename=xungeng.xls"); response.setHeader("Content-Type", "application/vnd.ms-excel"); wb.write(response.getOutputStream()); System.out.println("数据库导出成功"); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } } public void init() throws ServletException { // Put your code here } }
DoorlistAction
package com.sino.ibms.action; import java.io.IOException; import java.sql.ResultSet; import java.util.ArrayList; import javax.servlet.RequestDispatcher; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.sino.ibms.model.DoorEvent; public class DoorListAction extends HttpServlet { /** * */ private static final long serialVersionUID = 1L; /** * Constructor of the object. */ public DoorListAction() { super(); } public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } @SuppressWarnings("static-access") public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ArrayList<DoorEvent> cNamelist = new ArrayList<DoorEvent>(); ArrayList<DoorEvent> pelist = new ArrayList<DoorEvent>(); ArrayList<DoorEvent> eventlist = new ArrayList<DoorEvent>(); DoorConnDB db = new DoorConnDB(); String sql1 = "select distinct EA_Co_Name from AIC_Access_EventMessageDataAccess"; String sql2 = "select distinct EA_Pe_FirstName from AIC_Access_EventMessageDataAccess"; String sql3 = "select distinct EA_eventName from AIC_Access_EventMessageDataAccess"; ResultSet rs1 = db.executestatement(sql1); ResultSet rs2 = db.executestatement(sql2); ResultSet rs3 = db.executestatement(sql3); try { while (rs1.next()) { DoorEvent de1 = new DoorEvent(); de1.setEA_Co_Name(rs1.getString(1)); cNamelist.add(de1); } while (rs2.next()) { DoorEvent de2 = new DoorEvent(); de2.setEA_Pe_FirstName(rs2.getString(1)); pelist.add(de2); } while (rs3.next()) { DoorEvent de3 = new DoorEvent(); de3.setEA_eventName(rs3.getString(1)); eventlist.add(de3); } request.setAttribute("cNamelist", cNamelist); request.setAttribute("pelist", pelist); request.setAttribute("eventlist", eventlist); RequestDispatcher rd = request .getRequestDispatcher("/page/door/doorManager.jsp"); rd.forward(request, response); } catch (Exception e) { System.out.println("网络连接出现故障"); RequestDispatcher rd = request .getRequestDispatcher("/error.jsp"); rd.forward(request, response); } finally { db.dbclose(); } } public void init() throws ServletException { // Put your code here } }
DoorConnDB
package com.sino.ibms.action; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; public class DoorConnDB { public static Connection con=null; public static ResultSet rs=null; public static Connection getConn() throws SQLException{ try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url="jdbc:sqlserver://127.0.0.1:1433;databaseName=NewAICDatabase"; con=DriverManager.getConnection(url,"sa","123456"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } return con; } public static ResultSet executestatement(String sql){ try { con=getConn(); rs=con.prepareStatement(sql).executeQuery(); } catch (SQLException e) { System.out.print("数据库查询处出现问题"); e.printStackTrace(); } return rs; } public static void dbclose() { try { if(rs!=null){ rs.close(); } if(con!=null){ con.close(); } } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } } /* example @SuppressWarnings("deprecation") public static void main(String []args){ String sql="select top 20 EA_InsertDateTime,EA_Co_Name,EA_eventName,EA_Pe_FirstName,EA_Pe_Code,EA_Pe_DE_Name from dbo.AIC_Access_EventMessageDataAccess where EA_Pe_Code is not null and EA_Pe_DE_Name is not null"; ResultSet r1=executestatement(sql); try { while(r1.next()) { // System.out.print(r1.getTimestamp(1).toLocaleString()+" "+r1.getDate(1)+" "+r1.getTime(1)+" "+r1.getString(2)+" "+r1.getString(3)+" "+r1.getString(4)+" "+r1.getString(5)+"教育发展大厦"+"\n"); System.out.println(r1.getTimestamp(1).toLocaleString()+" "+r1.getString(2)+" "+r1.getString(3)+" "+r1.getString(4)+" "+r1.getString(5)+" "+r1.getString(6)); } String now=new Date().toLocaleString(); System.out.println(now); } catch (SQLException e) { // TODO 自动生成 catch 块 e.printStackTrace(); } finally{ dbclose(); } } */ }
doorManager.jsp
<%@ page language="java" import="java.util.*,com.sino.ibms.model.*" 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>门禁查询</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"> <SCRIPT type="text/javascript" src="/namidoor/js/My97DatePicker/WdatePicker.js"></SCRIPT> </head> <body> <form method="post" target="door" action="" name="dbform"> <table width="100%"> <tr> <td> <table align="center"> <tr class="f2_tr"> <td>起始时间：</td> <td><input type="text" class="Wdate" name="btime" id="btime" onclick="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" readonly="readonly" /> </td> <td>结束时间：</td> <td><input type="text" class="Wdate" name="etime" id="etime" onclick="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm:ss'})" readonly="readonly" /></td> </tr> </table> </td> </tr> <tr> <td> <table align="center"> <tr class="f2_tr" align="left"> <td>控制器名：</td> <td> <select name="cname" id="cname"> <option value="ALL">全部</option> <c:forEach items="${cNamelist}" var="cl"> <option value="${cl.EA_Co_Name}">${cl.EA_Co_Name}</option> </c:forEach> </select> </td> <td>人员姓名：</td> <td> <select name="pename" id="pename"> <option value="ALL">全部</option> <c:forEach items="${pelist}" var="pl"> <option value="${pl.EA_Pe_FirstName}">${pl.EA_Pe_FirstName}</option> </c:forEach> </select> </td> <td>事件:</td> <td> <select name="eventname" id="eventname"> <option value="ALL">全部</option> <c:forEach items="${eventlist}" var="el"> <option value="${el.EA_eventName}">${el.EA_eventName}</option> </c:forEach> </select> </td> <td><input type="submit" value="查询" class="button" onclick="checkaction(0)"/> <input type="submit" value="导出报表" class="button" onclick="checkaction(1)"/> </td> </tr> </table> </td> </tr> </table> <table width="100%"> <tr> <td height="500px"><iframe src="<%=request.getContextPath()%>/page/door/doorQuery.jsp" name="door" width="100%" frameborder="0" marginheight="0" marginwidth="0" height="500px"></iframe></td> </tr> </table> </form> </body> <script type="text/javascript"> function checkaction(v){ if(v==0){ document.dbform.action="DoorAction?flag=toQuery"; }else{ document.dbform.action="DoorAction?flag=toExcel"; } dbform.submit(); } </script> </html>
doorQuery
<%@ page language="java" import="java.util.*,com.sino.ibms.model.*" pageEncoding="utf-8"%> <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@ taglib uri="http://jsptags.com/tags/navigation/pager" prefix="pg"%> <%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%> <%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %> <% String path = request.getContextPath(); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/"; request.setCharacterEncoding("utf-8"); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'doorQuery.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"> </head> <body> <div align="center"> <pg:pager url="DoorAction" index="center" maxPageItems="10" maxIndexPages="10" isOffset="<%=false%>" export="pageOffset,currentPageNumber=pageNumber" scope="request"> <pg:param name="cname" value="${cname}"/> <pg:param name="pename" value="${pename}"/> <pg:param name="eventname" value="${eventname}"/> <pg:param name="btime" value="${btime}"/> <pg:param name="etime" value="${etime}"/> <pg:param name="flag" value="toQuery"/> <table class="mainTable" border="1px" width="800px" align="center" style="border-collapse:collapse"> <tr> <th>时间</th> <th>控制器名称</th> <th>事件名称</th> <th>人员</th> <th>人员编号</th> <th>部门</th> </tr> <c:if test="${empty list}"> <tr> <td colspan="6" align="center"><font color="red">无相关数据</font> </td> </tr> </c:if> <c:if test="${!empty list}"> <c:forEach items="${list}" var="list"> <pg:item> <tr> <td><fmt:formatDate value="${list.EA_InsertDateTime}" pattern="yyyy-MM-dd hh:mm:ss"/> </td> <td>${list.EA_Co_Name}</td> <td>${list.EA_eventName}</td> <td>${list.EA_Pe_FirstName}</td> <td>${list.EA_Pe_Code}</td> <td>${list.EA_Pe_De_Name}</td> </tr> </pg:item> </c:forEach> </c:if> </table> <pg:index> <pg:first> <a href="<%=pageUrl%>">首页</a> </pg:first> <pg:prev> <a href="<%=pageUrl%>">上一页</a> </pg:prev> <pg:pages> <c:choose> <c:when test="${pageNumber eq currentPageNumber}"> <font color="red">[<%=pageNumber%>]</font> </c:when> <c:otherwise> <a href="<%=pageUrl%>"><%=pageNumber%></a> </c:otherwise> </c:choose> </pg:pages> <pg:next> <a href="<%=pageUrl%>">下一页</a> </pg:next> <pg:last> <a href="<%=pageUrl%>">尾页</a> </pg:last> 共${fn:length(list)}条记录 </pg:index> </pg:pager> </div> </body> </html>
error.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <% 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 'error.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"> --> </head> <body> <h1>网络连接故障</h1> <br> </body> </html>
index.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <% 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 'index.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"> --> </head> <body> <form action="DoorListAction" method="post"> <input type="submit" value="提交" class="button" /> </form> </body> </html>
web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"> <display-name></display-name> <servlet> <description>This is the description of my J2EE component</description> <display-name>This is the display name of my J2EE component</display-name> <servlet-name>DoorAction</servlet-name> <servlet-class>com.sino.ibms.action.DoorAction</servlet-class> </servlet> <servlet> <description>This is the description of my J2EE component</description> <display-name>This is the display name of my J2EE component</display-name> <servlet-name>DoorListAction</servlet-name> <servlet-class>com.sino.ibms.action.DoorListAction</servlet-class> </servlet> <servlet-mapping> <servlet-name>DoorAction</servlet-name> <url-pattern>/DoorAction</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>DoorListAction</servlet-name> <url-pattern>/DoorListAction</url-pattern> </servlet-mapping> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> </web-app>
