利用js日期控件重构WEB功能
开发需求：网页中的日期部门（注册页面和查询条件）都用js日期控件重写
页面一：更新员工页面
empUpdate.jsp 中增加 onfocus 事件
入职日期：<input id="hiredate" type="text" name="hiredateTxt" value="${requestScope.empBean.hiredate}" onfocus="setday(this,'yyyy-MM-dd','2010-01-01','2020-12-30',1)" readonly="readonly"><br>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <!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>员工更新</title> <link href="/web01//css/main.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="/web01/js/DatePicker.js"></script> </head> <body> <%@ include file="top.jsp" %> <form action="/web01/empController" method="get"> 员工编号：<input type="text" disabled="disabled" value="${requestScope.empBean.empno}"><br> 员工姓名：<input type="text" name="enameTxt" value="${requestScope.empBean.ename}"><br> 职位：<input type="text" name="jobTxt" value="${requestScope.empBean.job}"><br> 领导：<input type="text" name="mgrTxt" value="${requestScope.empBean.mgr}"><br> 入职日期：<input id="hiredate" type="text" name="hiredateTxt" value="${requestScope.empBean.hiredate}" onfocus="setday(this,'yyyy-MM-dd','2010-01-01','2020-12-30',1)" readonly="readonly"><br> 工资：<input type="text" name="salTxt" value="${requestScope.empBean.sal}"><br> 奖金：<input type="text" name="commTxt" value="${requestScope.empBean.comm}"><br> 部门：<input type="text" name="deptnoTxt" value="${requestScope.empBean.deptno}"><br> <input type="submit" value="Save"> <input type="hidden" name="callTp" value="empSave"> <input type="hidden" name="empno" value="${requestScope.empBean.empno}"> <br/> </form> <%@ include file="bottom.jsp" %> </body> </html>
前台传到后台的日期格式是 yyyy-mm-dd，在java端进行格式化去掉“-”后变成 yyyymmdd格式的字符串，然后保存到数据库。
所以增加了一个处理String的类StringUtil.java。
package com.test.common.util; public class StringUtil { public static String formatString(String dateStringWithLine){ String dateString = null; if (dateStringWithLine != null) { dateString = dateStringWithLine.replace("-", ""); } return dateString; } }
在service层调用SQL之前处理日期字符串 StringUtil.formatString(empBean.getHiredate())
// 更新emp信息 public int empSave(EmpBean empBean) { int updateResulInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("UPDATE EMP SET ENAME = ? \n"); sqlBf.append(" , JOB = ? \n"); sqlBf.append(" , MGR = ? \n"); sqlBf.append(" , HIREDATE = TO_DATE(?, 'YYYYMMDD') \n"); sqlBf.append(" , SAL = ? \n"); sqlBf.append(" , COMM = ? \n"); sqlBf.append(" , DEPTNO = ? \n"); sqlBf.append("WHERE EMPNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setString(idx++, empBean.getEname()); pstmt.setString(idx++, empBean.getJob()); pstmt.setInt(idx++, empBean.getMgr()); pstmt.setString(idx++, StringUtil.formatString(empBean.getHiredate())); pstmt.setDouble(idx++, empBean.getSal()); pstmt.setDouble(idx++, empBean.getComm()); pstmt.setInt(idx++, empBean.getDeptno()); pstmt.setInt(idx++, empBean.getEmpno()); updateResulInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(null, pstmt, conn); } return updateResulInt; }
页面效果是
页面二：添加员工
empAdd.jsp 中也跟上面相同方式处理
入职日期：<input id="hiredate" type="text" name="hiredateTxt" value="" onfocus="setday(this,'yyyy-MM-dd','2010-01-01','2020-12-30',1)" readonly="readonly"><br>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <!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>添加员工</title> <link href="/web01/css/main.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="/web01/js/DatePicker.js"></script> </head> <body> <%@ include file="top.jsp" %> <form action="/web01/empController" method="get"> 员工姓名：<input type="text" name="enameTxt" value="" maxlength="10"><br> 职位：<input type="text" name="jobTxt" value="" maxlength="9"><br> 领导号：<input type="text" name="mgrTxt" value="" maxlength="4"><br> 入职日期：<input id="hiredate" type="text" name="hiredateTxt" value="" onfocus="setday(this,'yyyy-MM-dd','2010-01-01','2020-12-30',1)" readonly="readonly"><br> 工资：<input type="text" name="salTxt" value="" maxlength="7"><br> 奖金：<input type="text" name="commTxt" value="" maxlength="7"><br> 部门编号：<input type="text" name="deptnoTxt" value="" maxlength="2"><br> <input type="submit" value="Add"> <input type="hidden" name="callTp" value="empAdd"> <br/> </form> <%@ include file="bottom.jsp" %> </body> </html>
添加员工的service层调用SQL之前处理字符串 StringUtil.formatString(empBean.getHiredate())
// 添加新的员工 public int empAdd(EmpBean emp) { int insertInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } int nextEmpno = this.getNextEmpno(); StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) \n"); sqlBf.append(" VALUES(? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , TO_DATE(?, 'YYYYMMDD') \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ?) \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, nextEmpno); pstmt.setString(idx++, emp.getEname()); pstmt.setString(idx++, emp.getJob()); pstmt.setInt(idx++, emp.getMgr()); pstmt.setString(idx++, StringUtil.formatString(emp.getHiredate())); pstmt.setDouble(idx++, emp.getSal()); pstmt.setDouble(idx++, emp.getComm()); pstmt.setInt(idx++, emp.getDeptno()); insertInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return insertInt; }
员工的service的全部代码如下：
package com.test.biz.service; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import com.test.biz.bean.EmpBean; import com.test.common.dao.BaseDao; import com.test.common.util.StringUtil; public class EmpService { private int idx = 1; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; public ArrayList<EmpBean> getEmpList(EmpBean eb){ ArrayList<EmpBean> empList = new ArrayList<EmpBean>(); BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } // 3. 执行SQL语句 StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT EMPNO \n"); sqlBf.append(" , ENAME \n"); sqlBf.append(" , JOB \n"); sqlBf.append(" , MGR \n"); sqlBf.append(" , TO_CHAR(HIREDATE, 'YYYYMMDD') HIREDATE \n"); sqlBf.append(" , SAL \n"); sqlBf.append(" , COMM \n"); sqlBf.append(" , DEPTNO \n"); sqlBf.append("FROM EMP \n"); sqlBf.append("WHERE ENAME LIKE UPPER(?) || '%' \n"); sqlBf.append("ORDER BY EMPNO \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setString(idx++, eb.getEname()); // 4. 获取结果集 rs = pstmt.executeQuery(); while (rs.next()) { EmpBean emp = new EmpBean(); emp.setEmpno(rs.getInt("empno")); emp.setEname(rs.getString("ename")); emp.setJob(rs.getString("job")); emp.setMgr(rs.getInt("mgr")); emp.setHiredate(rs.getString("hiredate")); emp.setSal(rs.getDouble("sal")); emp.setComm(rs.getDouble("comm")); emp.setDeptno(rs.getInt("deptno")); empList.add(emp); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return empList; } // 利用empno查询单条员工信息 public EmpBean empById(int empno) { EmpBean emp = new EmpBean(); BaseDao baseBao = new BaseDao(); try { conn = baseBao.dbConnection(); } catch (SQLException e) { e.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT EMPNO \n"); sqlBf.append(" , ENAME \n"); sqlBf.append(" , JOB \n"); sqlBf.append(" , MGR \n"); sqlBf.append(" , TO_CHAR(HIREDATE, 'YYYY-MM-DD') HIREDATE \n"); sqlBf.append(" , SAL \n"); sqlBf.append(" , COMM \n"); sqlBf.append(" , DEPTNO \n"); sqlBf.append("FROM EMP \n"); sqlBf.append("WHERE EMPNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, empno); rs = pstmt.executeQuery(); if (rs.next()) { emp.setEmpno(rs.getInt("EMPNO")); emp.setEname(rs.getString("ENAME")); emp.setJob(rs.getString("JOB")); emp.setMgr(rs.getInt("MGR")); emp.setHiredate(rs.getString("HIREDATE")); emp.setSal(rs.getDouble("SAL")); emp.setComm(rs.getDouble("COMM")); emp.setDeptno(rs.getInt("DEPTNO")); } } catch (SQLException e) { e.printStackTrace(); } finally { baseBao.dbDisconnection(rs, pstmt, conn); } return emp; } // 更新emp信息 public int empSave(EmpBean empBean) { int updateResulInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("UPDATE EMP SET ENAME = ? \n"); sqlBf.append(" , JOB = ? \n"); sqlBf.append(" , MGR = ? \n"); sqlBf.append(" , HIREDATE = TO_DATE(?, 'YYYYMMDD') \n"); sqlBf.append(" , SAL = ? \n"); sqlBf.append(" , COMM = ? \n"); sqlBf.append(" , DEPTNO = ? \n"); sqlBf.append("WHERE EMPNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setString(idx++, empBean.getEname()); pstmt.setString(idx++, empBean.getJob()); pstmt.setInt(idx++, empBean.getMgr()); pstmt.setString(idx++, StringUtil.formatString(empBean.getHiredate())); pstmt.setDouble(idx++, empBean.getSal()); pstmt.setDouble(idx++, empBean.getComm()); pstmt.setInt(idx++, empBean.getDeptno()); pstmt.setInt(idx++, empBean.getEmpno()); updateResulInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(null, pstmt, conn); } return updateResulInt; } // 获取下一个员工号 public int getNextEmpno() { int nextEmpno = 0; StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT MAX(EMPNO) + 1 AS EMPNO \n"); sqlBf.append("FROM EMP \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); rs = pstmt.executeQuery(); if (rs.next()) { nextEmpno = rs.getInt("EMPNO"); } } catch (SQLException e) { e.printStackTrace(); } return nextEmpno; } // 添加新的员工 public int empAdd(EmpBean emp) { int insertInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } int nextEmpno = this.getNextEmpno(); StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("INSERT INTO EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) \n"); sqlBf.append(" VALUES(? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , TO_DATE(?, 'YYYYMMDD') \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ?) \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, nextEmpno); pstmt.setString(idx++, emp.getEname()); pstmt.setString(idx++, emp.getJob()); pstmt.setInt(idx++, emp.getMgr()); pstmt.setString(idx++, StringUtil.formatString(emp.getHiredate())); pstmt.setDouble(idx++, emp.getSal()); pstmt.setDouble(idx++, emp.getComm()); pstmt.setInt(idx++, emp.getDeptno()); insertInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return insertInt; } // 删除一名员工 public int empDelete(int empno) { int deleteResulInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("DELETE FROM EMP \n"); sqlBf.append("WHERE EMPNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, empno); deleteResulInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(null, pstmt, conn); } return deleteResulInt; } }
页面三：访问日志查询页面
requestLogList.jsp
代码说明：
页面加载时设置初始日期值，默认值是当天日期。
<body onload="setInitDate();">
function setInitDate(){ var myDate = new Date(); var mytime = myDate.getFullYear() + '-' + (myDate.getMonth() < 10 ?'0' + (myDate.getMonth() + 1) : myDate.getMonth() + 1) + '-' + myDate.getDate(); if(document.getElementById('starttime').value == ""){ document.getElementById('starttime').value = mytime; } }
添加查询条件
访问日期：<input id="starttime" type="text" name="starttime" value="${requestScope.starttime }" onfocus="setday(this,'yyyy-MM-dd','2010-01-01','2020-12-30',1)" readonly="readonly"><br>
点击查询时进行判断已经选择了日期
<input type="submit" value="Search" onclick="verDate()">
function verDate(){ dateStr = document.getElementById('starttime').value; if(dateStr.length == 0){ alert("请选择日期！"); return false; } }
每个超链接中添加日期 starttime=${requestScope.starttime }
<a href="/web01/requestInfoController?callTp=requestInfoPageList&starttime=${requestScope.starttime }&now_page_num=1">首页 | </a>
完整代码：
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <!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>访问日志查看</title> <link href="/web01//css/main.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="/web01/js/DatePicker.js"></script> <script type="text/javascript"> function setInitDate(){ var myDate = new Date(); var mytime = myDate.getFullYear() + '-' + (myDate.getMonth() < 10 ?'0' + (myDate.getMonth() + 1) : myDate.getMonth() + 1) + '-' + myDate.getDate(); if(document.getElementById('starttime').value == ""){ document.getElementById('starttime').value = mytime; } } function verDate(){ dateStr = document.getElementById('starttime').value; if(dateStr.length == 0){ alert("请选择日期！"); return false; } } </script> </head> <body onload="setInitDate();"> <%@ include file="top.jsp" %> <h2>访问日志查询</h2> <form action="/web01/requestInfoController" method="get"> 访问日期：<input id="starttime" type="text" name="starttime" value="${requestScope.starttime }" onfocus="setday(this,'yyyy-MM-dd','2010-01-01','2020-12-30',1)" readonly="readonly"><br> <input type="submit" value="Search" onclick="verDate()"> <input type="hidden" name="callTp" value="requestInfoList"> <br/> <table> <tr> <th>NO</th> <th>contextPath</th> <th>localAddr</th> <th>localName</th> <th>localPort</th> <th>method</th> <th>remoteAddr</th> <th>remoteHost</th> <th>remotePort</th> <th>requestURI</th> <th>requestURL</th> <th>requestedSessionId</th> <th>locale</th> <th>regiDt</th> </tr> <c:forEach items="${requestScope.requestInfoList}" var="requestInfo"> <tr> <td><c:out value="${requestInfo.rowSeq }" default=" "/></td> <td><c:out value="${requestInfo.contextPath }" default=" "/></td> <td><c:out value="${requestInfo.localAddr }" default=" "/></td> <td><c:out value="${requestInfo.localName }" default=" "/></td> <td><c:out value="${requestInfo.localPort }" default=" "/></td> <td><c:out value="${requestInfo.method }" default=" "/></td> <td><c:out value="${requestInfo.remoteAddr }" default=" "/></td> <td><c:out value="${requestInfo.remoteHost }" default=" "/></td> <td><c:out value="${requestInfo.remotePort }" default=" "/></td> <td><c:out value="${requestInfo.requestURI }" default=" "/></td> <td><c:out value="${requestInfo.requestURL }" default=" "/></td> <td><c:out value="${requestInfo.requestedSessionId }" default=" "/></td> <td><c:out value="${requestInfo.locale }" default=" "/></td> <td><c:out value="${requestInfo.regiDt }" default=" "/></td> </tr> </c:forEach> </table> </form> 总个数：<b>${sessionScope.ttlCnt}</b> 页 <br> 总页数：<b>${sessionScope.ttlPage}</b> 页 <br> <a href="/web01/requestInfoController?callTp=requestInfoPageList&starttime=${requestScope.starttime }&now_page_num=1">首页 | </a> <c:choose> <c:when test="${sessionScope.now_page_num==1}"> 上一页 </c:when> <c:otherwise> <a href="/web01/requestInfoController?callTp=requestInfoPageList&starttime=${requestScope.starttime }&now_page_num=${sessionScope.now_page_num - 1}">上一页 | </a> </c:otherwise> </c:choose> <c:choose> <c:when test="${sessionScope.now_page_num==sessionScope.ttlPage}"> 下一页 </c:when> <c:otherwise> <a href="/web01/requestInfoController?callTp=requestInfoPageList&starttime=${requestScope.starttime }&now_page_num=${sessionScope.now_page_num + 1}">下一页 | </a> </c:otherwise> </c:choose> <a href="/web01/requestInfoController?callTp=requestInfoPageList&starttime=${requestScope.starttime }&now_page_num=${sessionScope.ttlPage}"> 尾页</a> <script type="text/javascript"> function pageNum_Change(){ var now_page_num = document.getElementById("now_page_num").value; window.open("/web01/requestInfoController?callTp=requestInfoPageList&starttime=${requestScope.starttime }&now_page_num="+now_page_num, "_self"); } </script> 直接访问:<input id="now_page_num" value="${sessionScope.now_page_num}"><input type="button" value="go" onclick="pageNum_Change()"> <%@ include file="bottom.jsp" %> </body> </html>
控制器代码的处理，需要把request中的日期（查询日期）重新赋值到request中。
request.setAttribute("starttime", request.getParameter("starttime"));
控制器完成代码：
package com.test.system.controller; import java.io.IOException; import java.util.ArrayList; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import com.test.system.bean.RequestInfoBean; import com.test.system.service.RequestInfoService; /** * Servlet implementation class RequestInfoController */ @WebServlet("/RequestInfoController") public class RequestInfoController extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public RequestInfoController() { super(); } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String callTp = request.getParameter("callTp"); if (callTp.equals("requestInfoList")) { int now_page_num = 1; RequestInfoService ris = new RequestInfoService(); ArrayList<RequestInfoBean> requestInfoList = ris.getRequestInfoList("", now_page_num, request.getParameter("starttime")); HttpSession session = request.getSession(); // 当前页面（第一次查询时设置成第一页） session.setAttribute("now_page_num", now_page_num); // 总页数 int ttlPage = ris.getTtlPage(request.getParameter("starttime")); session.setAttribute("ttlPage", ttlPage); // 获取总数 int ttlCnt = ris.getTtlCount(request.getParameter("starttime")); session.setAttribute("ttlCnt", ttlCnt); request.setAttribute("starttime", request.getParameter("starttime")); request.setAttribute("requestInfoList", requestInfoList); request.getRequestDispatcher("/view/requestLogList.jsp").forward(request, response); } else if (callTp.equals("requestInfoPageList")) { RequestInfoService ris = new RequestInfoService(); ArrayList<RequestInfoBean> requestInfoList = ris.getRequestInfoList("", Integer.parseInt(request.getParameter("now_page_num")), request.getParameter("starttime")); HttpSession session = request.getSession(); session.setAttribute("now_page_num", request.getParameter("now_page_num")); // 总页数 int ttlPage = ris.getTtlPage(request.getParameter("starttime")); session.setAttribute("ttlPage", ttlPage); // 获取总数 int ttlCnt = ris.getTtlCount(request.getParameter("starttime")); session.setAttribute("ttlCnt", ttlCnt); request.setAttribute("starttime", request.getParameter("starttime")); request.setAttribute("requestInfoList", requestInfoList); request.getRequestDispatcher("/view/requestLogList.jsp").forward(request, response); } } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doGet(request, response); } }
日期处理的service层代码。在原有代码的基础上添加REGI_DT范围处理。
sqlBf.append("WHERE REGI_DT >= TO_DATE(?, 'YYYYMMDD') \n");
sqlBf.append("AND REGI_DT< (TO_DATE(?, 'YYYYMMDD') + 1) \n");
完成代码：
package com.test.system.service; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import javax.servlet.http.HttpServletRequest; import com.test.common.Constant; import com.test.common.dao.BaseDao; import com.test.common.util.StringUtil; import com.test.system.bean.RequestInfoBean; public class RequestInfoService { private int idx = 1; private Connection conn = null; private PreparedStatement pstmt = null; private ResultSet rs = null; // 保存request信息 public void saveRequestInfo(HttpServletRequest request){ BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("INSERT INTO REQUEST_INFO (REQUEST_INFO_SEQ \n"); sqlBf.append(" , CHARACTER_ENCODING \n"); sqlBf.append(" , CONTENT_TYPE \n"); sqlBf.append(" , CONTEXT_PATH \n"); sqlBf.append(" , LOCAL_ADDR \n"); sqlBf.append(" , LOCAL_NAME \n"); sqlBf.append(" , LOCAL_PORT \n"); sqlBf.append(" , METHOD \n"); sqlBf.append(" , REMOTE_ADDR \n"); sqlBf.append(" , REMOTE_HOST \n"); sqlBf.append(" , REMOTE_PORT \n"); sqlBf.append(" , REMOTE_USER \n"); sqlBf.append(" , REQUEST_URI \n"); sqlBf.append(" , REQUEST_URL \n"); sqlBf.append(" , REQUESTED_SESSION_ID \n"); sqlBf.append(" , LOCALE \n"); sqlBf.append(" , REGI_DT) \n"); sqlBf.append("VALUES(SEQ_REQUEST_INFO.NEXTVAL \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , SYSDATE) \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setString(idx++, request.getCharacterEncoding()); pstmt.setString(idx++, request.getContentType()); pstmt.setString(idx++, request.getContextPath()); pstmt.setString(idx++, request.getLocalAddr()); pstmt.setString(idx++, request.getLocalName()); pstmt.setInt(idx++, request.getLocalPort()); pstmt.setString(idx++, request.getMethod()); pstmt.setString(idx++, request.getRemoteAddr()); pstmt.setString(idx++, request.getRemoteHost()); pstmt.setInt(idx++, request.getRemotePort()); pstmt.setString(idx++, request.getRemoteUser()); pstmt.setString(idx++, request.getRequestURI()); pstmt.setString(idx++, request.getRequestURL().toString() + "?callTp=" + request.getParameter("callTp")); pstmt.setString(idx++, request.getRequestedSessionId()); pstmt.setString(idx++, request.getLocale().toString()); int i = pstmt.executeUpdate(); if (i == 1) { System.out.println("##### save request success \n"); } else { System.out.println("##### save request fail \n"); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(null, pstmt, conn); } } // 查询List public ArrayList<RequestInfoBean> getRequestInfoList(String str, int now_page_num, String startTime){ ArrayList<RequestInfoBean> requestInfoList = new ArrayList<RequestInfoBean>(); BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT A.* \n"); sqlBf.append("FROM (SELECT T1.* \n"); sqlBf.append(" , ROWNUM AS ROW_SEQ \n"); sqlBf.append(" FROM (SELECT CHARACTER_ENCODING \n"); sqlBf.append(" , CONTENT_TYPE \n"); sqlBf.append(" , CONTEXT_PATH \n"); sqlBf.append(" , LOCAL_ADDR \n"); sqlBf.append(" , LOCAL_NAME \n"); sqlBf.append(" , LOCAL_PORT \n"); sqlBf.append(" , METHOD \n"); sqlBf.append(" , REMOTE_ADDR \n"); sqlBf.append(" , REMOTE_HOST \n"); sqlBf.append(" , REMOTE_PORT \n"); sqlBf.append(" , REMOTE_USER \n"); sqlBf.append(" , REQUEST_URI \n"); sqlBf.append(" , REQUEST_URL \n"); sqlBf.append(" , REQUESTED_SESSION_ID \n"); sqlBf.append(" , LOCALE \n"); sqlBf.append(" , TO_CHAR(REGI_DT, 'YYYY/MM/DD HH24:MI:SS') REGI_DT \n"); sqlBf.append(" FROM REQUEST_INFO \n"); sqlBf.append(" WHERE REGI_DT >= TO_DATE(?, 'YYYYMMDD') \n"); sqlBf.append(" AND REGI_DT< (TO_DATE(?, 'YYYYMMDD') + 1) \n"); sqlBf.append(" ORDER BY REQUEST_INFO_SEQ DESC \n"); sqlBf.append(" ) T1 \n"); sqlBf.append(" WHERE ROWNUM < (? * ?) + 1 \n"); sqlBf.append(" ) A \n"); sqlBf.append("WHERE A.ROW_SEQ > (? * (? - 1)) \n"); sqlBf.append("ORDER BY A.ROW_SEQ \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); pstmt.setString(1, StringUtil.formatString(startTime)); pstmt.setString(2, StringUtil.formatString(startTime)); pstmt.setInt(3, Constant.UNIT_CNT); pstmt.setInt(4, now_page_num); pstmt.setInt(5, Constant.UNIT_CNT); pstmt.setInt(6, now_page_num); rs = pstmt.executeQuery(); while (rs.next()) { RequestInfoBean rib = new RequestInfoBean(); rib.setCharacterEncoding(rs.getString("CHARACTER_ENCODING")); rib.setContentType(rs.getString("CONTENT_TYPE")); rib.setContextPath(rs.getString("CONTEXT_PATH")); rib.setLocalAddr(rs.getString("LOCAL_ADDR")); rib.setLocalName(rs.getString("LOCAL_NAME")); rib.setLocalPort(rs.getInt("LOCAL_PORT")); rib.setMethod(rs.getString("METHOD")); rib.setRemoteAddr(rs.getString("REMOTE_ADDR")); rib.setRemoteHost(rs.getString("REMOTE_HOST")); rib.setRemotePort(rs.getInt("REMOTE_PORT")); rib.setRemoteUser(rs.getString("REMOTE_USER")); rib.setRequestURI(rs.getString("REQUEST_URI")); rib.setRequestURL(rs.getString("REQUEST_URL")); rib.setRequestedSessionId(rs.getString("REQUESTED_SESSION_ID")); rib.setLocale(rs.getString("LOCALE")); rib.setRegiDt(rs.getString("REGI_DT")); rib.setRowSeq(rs.getInt("ROW_SEQ")); requestInfoList.add(rib); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return requestInfoList; } // 获取记录总数 public int getTtlCount(String startTime){ int ttlCnt = 0; // Total Count BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT COUNT(1) TTL_CNT \n"); sqlBf.append("FROM REQUEST_INFO \n"); sqlBf.append("WHERE REGI_DT >= TO_DATE(?, 'YYYYMMDD') \n"); sqlBf.append("AND REGI_DT< (TO_DATE(?, 'YYYYMMDD') + 1) \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); pstmt.setString(1, StringUtil.formatString(startTime)); pstmt.setString(2, StringUtil.formatString(startTime)); rs = pstmt.executeQuery(); if (rs.next()) { ttlCnt = rs.getInt("TTL_CNT"); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return ttlCnt; } // 获取页数 public int getTtlPage(String startTime){ int ttlPage = 0; // Total Count BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT COUNT(1) TTL_CNT \n"); sqlBf.append("FROM REQUEST_INFO \n"); sqlBf.append("WHERE REGI_DT >= TO_DATE(?, 'YYYYMMDD') \n"); sqlBf.append("AND REGI_DT< (TO_DATE(?, 'YYYYMMDD') + 1) \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); pstmt.setString(1, StringUtil.formatString(startTime)); pstmt.setString(2, StringUtil.formatString(startTime)); rs = pstmt.executeQuery(); if (rs.next()) { ttlPage = rs.getInt("TTL_CNT") % Constant.UNIT_CNT == 0 ? rs.getInt("TTL_CNT") / Constant.UNIT_CNT : rs.getInt("TTL_CNT") / Constant.UNIT_CNT + 1; } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return ttlPage; } }
