利用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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<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;
    }    
}

爱程序网

Tags：[db:关键字]