springMVC+JDBC:分页示例

文章来源：http://liuzidong.iteye.com/blog/1067492
一 环境:XP3+Oracle10g+MyEclipse6+(Tomcat)+JDK1.5
二 工程相关图片:


三 基本上参照之前示例修改得来,重点关注SimpleJdbcTemplate与JdbcTemplate用法
以下只列出比较重要的类
UserController.java
Java代码

    package com.liuzd.sj.web;  
     
    import java.util.List;  
     
    import javax.annotation.Resource;  
    import javax.servlet.http.HttpServletRequest;  
     
    import org.springframework.stereotype.Controller;  
    import org.springframework.web.bind.annotation.PathVariable;  
    import org.springframework.web.bind.annotation.RequestMapping;  
    import org.springframework.web.bind.annotation.SessionAttributes;  
    import org.springframework.web.servlet.ModelAndView;  
     
    import com.liuzd.page.Page;  
    import com.liuzd.sj.entity.User;  
    import com.liuzd.sj.service.UserService;  
     
    @Controller 
    @RequestMapping("/user")  
    @SessionAttributes("userList")  
    public class UserController extends BaseController{  
          
        private UserService userService;  
          
     
        public UserService getUserService() {  
            return userService;  
        }  
          
        @Resource 
        public void setUserService(UserService userService) {  
            this.userService = userService;  
        }     
     
          
        @RequestMapping("/userList")  
        public ModelAndView userList(HttpServletRequest request){  
            StringBuilder querySql = new StringBuilder();  
            querySql.append("select * from users where 1=1 ");  
              
            String oracleQuerySql = querySql.toString();  
            //获取总条数  
            Long totalCount = new Long(this.getUserService().pageCounts(oracleQuerySql));  
            //设置分页对象  
            Page page = executePage(request,oracleQuerySql,totalCount," id desc ");       
              
            ModelAndView mv = new ModelAndView();         
            //查询集合        
            List<User> users = this.getUserService().pageList(page.getQuerySql());  
            mv.addObject("userList",users);               
            mv.setViewName("userList");           
            return mv;  
        }  
          
          
          
        @RequestMapping("/addUser")  
        public ModelAndView addUser(HttpServletRequest request,User user){  
            System.out.println("ADD USER: "+ user);   
            this.userService.addUser(user);       
            return userList(request);  
        }  
          
        @RequestMapping("/toAddUser")  
        public String toAddUser(){        
            return "addUser";  
        }  
          
        @RequestMapping("/delUser/{id}")  
        public ModelAndView delUser(@PathVariable("id") String id,HttpServletRequest request){  
            this.userService.delUser(new User().setId(id));  
            return userList(request);  
        }  
          
        @RequestMapping("/getUser/{id}")  
        public ModelAndView getUser(@PathVariable("id") String id){  
            User user = this.userService.getUserById(new User().setId(id));  
            ModelAndView mv = new ModelAndView("updateUser");  
            mv.addObject("user",user);  
            return mv;  
        }     
          
        @RequestMapping("/updateUser")  
        public ModelAndView editUser(User user,HttpServletRequest request){   
            System.out.println("编辑: "+user);  
            this.userService.editUser(user);  
            return userList(request);  
        }         
          
    } 

BaseController.java
Java代码

    package com.liuzd.sj.web;  
     
    import javax.servlet.http.HttpServletRequest;  
     
    import com.liuzd.page.Page;  
    import com.liuzd.page.PageState;  
    import com.liuzd.page.PageUtil;  
     
    /**    
     *Title:      
     *Description:      
     *Copyright: Copyright (c) 2011    
     *Company:http://liuzidong.iteye.com/     
     *Makedate:2011-5-23 下午03:31:03    
     * @author liuzidong    
     * @version 1.0    
     * @since 1.0     
     *    
     */ 
    public class BaseController {  
          
        /** 
         * oracel的三层分页语句     
         * 子类在展现数据前,进行分页计算! 
         * @param querySql  查询的SQL语句,未进行分页 
         * @param totalCount 根据查询SQL获取的总条数 
         * @param columnNameDescOrAsc 列名+排序方式 : ID DESC or ASC 
         */ 
        protected Page executePage(HttpServletRequest request,String querySql,Long totalCount,String columnNameDescOrAsc){  
            String oracleSql = PageUtil.createQuerySql(querySql,columnNameDescOrAsc);  
            if(null == totalCount){  
                totalCount = 0L;  
            }  
            /**页面状态,这个状态是分页自带的,与业务无关*/ 
            String pageAction = request.getParameter("pageAction");  
            String value = request.getParameter("pageKey");  
              
            /**获取下标判断分页状态*/ 
            int index = PageState.getOrdinal(pageAction);                 
              
            Page page = null;         
            /** 
             * index < 1 只有二种状态 
             * 1 当首次调用时,分页状态类中没有值为 NULL 返回 -1 
             * 2 当页面设置每页显示多少条: index=0,当每页显示多少条时,分页类要重新计算 
             * */ 
            Page sessionPage = getPage(request);  
              
            if(index < 1){             
                page = PageUtil.inintPage(oracleSql,totalCount,index,value,sessionPage);  
            }else{                
                page = PageUtil.execPage(index,value,sessionPage);  
            }         
            setSession(request,page);     
            return page;  
        }     
          
        private Page getPage(HttpServletRequest request) {  
            Page page = (Page)request.getSession().getAttribute(PageUtil.SESSION_PAGE_KEY);  
            if(page == null){  
                page = new Page();  
            }  
            return page;          
        }     
          
        private void setSession(HttpServletRequest request,Page page) {  
            request.getSession().setAttribute(PageUtil.SESSION_PAGE_KEY,page);        
        }     
     
    } 

UserRowMapper.java
Java代码

    package com.liuzd.sj.dao;  
     
    import java.sql.ResultSet;  
    import java.sql.SQLException;  
     
    import org.springframework.jdbc.core.RowMapper;  
     
    import com.liuzd.sj.entity.User;  
     
    public class UserRowMapper implements RowMapper<User> {     
          
          
        public UserRowMapper(){}      
          
        public User mapRow(ResultSet rs, int index) throws SQLException {                 
            User user = new User(  
                    rs.getString("id"),  
                    rs.getString("name"),  
                    rs.getString("password"),  
                    rs.getString("address"),  
                    rs.getString("sex"),  
                    rs.getInt("age")  
            );        
            return user;  
        }  
    } 

UserDAOImpl.java
Java代码

    package com.liuzd.sj.dao.impl;  
     
    import java.sql.PreparedStatement;  
    import java.sql.SQLException;  
    import java.util.List;  
     
    import javax.annotation.Resource;  
     
    import org.springframework.jdbc.core.BeanPropertyRowMapper;  
    import org.springframework.jdbc.core.PreparedStatementSetter;  
    import org.springframework.stereotype.Repository;  
     
    import com.liuzd.sj.dao.UserDAO;  
    import com.liuzd.sj.dao.UserRowMapper;  
    import com.liuzd.sj.entity.User;  
     
    @Repository("userDao")  
    public class UserDAOImpl implements UserDAO  
    {  
          
        private static final String INSERT = "insert into users(id,name,age,sex,address,password)VALUES(?,?,?,?,?,?)";  
        private static final String UPDATE = "update users set name=?,age=?,sex=?,address=?,password=? where id=?";  
        private static final String GET = "select * from users where id=?";  
        private static final String CHECK = "select count(1) from users where name=? and password=?";  
        private static final String SELECT = "select * from users";  
        private static final String DEL = "delete users where id=?";  
          
        private org.springframework.jdbc.core.JdbcTemplate jdbcTemplate;      
     
        public org.springframework.jdbc.core.JdbcTemplate getJdbcTemplate() {  
            return jdbcTemplate;  
        }  
     
        @Resource 
        public void setJdbcTemplate(  
                org.springframework.jdbc.core.JdbcTemplate jdbcTemplate) {  
            this.jdbcTemplate = jdbcTemplate;  
        }     
          
     
        public void addUser(final User user) {  
            getJdbcTemplate().update(INSERT, new PreparedStatementSetter(){  
                public void setValues(PreparedStatement ps)  
                        throws SQLException {  
                    int i = 0;        
                    ps.setString(++i, user.getId());  
                    ps.setString(++i, user.getName());  
                    ps.setInt(++i, user.getAge());  
                    ps.setString(++i,user.getSex());  
                    ps.setString(++i,user.getAddress());  
                    ps.setString(++i,user.getPassword());             
                }             
            });  
              
              
        }  
     
        public int checkUserExits(User user) {                
            return getJdbcTemplate().queryForInt(CHECK, user.getName(),user.getPassword());  
        }  
     
        public void delUser(User user) {  
            getJdbcTemplate().update(DEL, user.getId());  
        }  
     
        public void editUser(final User user) {       
            getJdbcTemplate().update(UPDATE, new PreparedStatementSetter(){  
                public void setValues(PreparedStatement ps)  
                        throws SQLException {  
                    int i = 0;                        
                    ps.setString(++i, user.getName());  
                    ps.setInt(++i, user.getAge());  
                    ps.setString(++i,user.getSex());  
                    ps.setString(++i,user.getAddress());  
                    ps.setString(++i,user.getPassword());     
                    ps.setString(++i, user.getId());  
                }             
            });  
        }  
     
        public List<User> getAllUser() {        
            return getJdbcTemplate().query(SELECT, new BeanPropertyRowMapper<User>(User.class));  
        }  
     
        public User getUserById(User user) {          
            return getJdbcTemplate().queryForObject(GET, new UserRowMapper(),user.getId());  
        }  
     
        public int pageCounts(String querySql) {          
            return getJdbcTemplate().queryForInt("select count(1) from("+querySql+")");  
        }  
     
        public List<User> pageList(String querySql) {       
            return getJdbcTemplate().query(querySql, new UserRowMapper());  
        }  
     
    } 

UserDAOImpl2.java
Java代码

    package com.liuzd.sj.dao.impl;  
     
    import java.util.List;  
    import java.util.Map;  
     
    import javax.annotation.Resource;  
     
    import org.springframework.jdbc.core.BeanPropertyRowMapper;  
    import org.springframework.stereotype.Repository;  
     
    import com.liuzd.sj.dao.UserDAO;  
    import com.liuzd.sj.entity.User;  
    import com.liuzd.util.BeanToMapUtil;  
     
    @Repository("userDao2")  
    public class UserDAOImpl2 implements UserDAO  
    {  
          
        private static final String INSERT = "insert into users(id,name,age,sex,address,password)VALUES(:id,:name,:age,:sex,:address,:password)";  
        private static final String UPDATE = "update users set name=:name,age=:age,sex=:sex,address=:address,password=:password where id=:id";  
        private static final String GET = "select * from users where id=?";  
        private static final String CHECK = "select count(1) from users where name=? and password=?";  
        private static final String SELECT = "select * from users";  
        private static final String DEL = "delete users where id=?";  
          
        private org.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate;  
          
        public org.springframework.jdbc.core.simple.SimpleJdbcTemplate getSimpleJdbcTemplate() {  
            return simpleJdbcTemplate;  
        }  
     
        @Resource 
        public void setSimpleJdbcTemplate(  
                org.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate) {  
            this.simpleJdbcTemplate = simpleJdbcTemplate;  
        }     
          
     
        public void addUser(final User user) {  
            Map<String,Object> userMap = BeanToMapUtil.beanToMap(user);         
            getSimpleJdbcTemplate().update(INSERT, userMap);          
        }  
     
        public int checkUserExits(User user) {            
            return getSimpleJdbcTemplate().queryForInt(CHECK, user.getName(),user.getPassword());  
        }  
     
        public void delUser(User user) {  
            getSimpleJdbcTemplate().update(DEL, user.getId());  
        }  
     
        public void editUser(final User user) {               
            Map<String,Object> userMap = BeanToMapUtil.beanToMap(user);         
            getSimpleJdbcTemplate().update(UPDATE, userMap);  
        }  
     
        public List<User> getAllUser() {        
            return getSimpleJdbcTemplate().query(SELECT, new BeanPropertyRowMapper<User>(User.class));  
        }  
     
        public User getUserById(User user) {          
            return getSimpleJdbcTemplate().queryForObject(GET, new BeanPropertyRowMapper<User>(User.class),user.getId());  
        }  
     
        public int pageCounts(String querySql) {          
            return getSimpleJdbcTemplate().queryForInt("select count(1) from("+querySql+")");  
        }  
     
        public List<User> pageList(String querySql) {       
            return getSimpleJdbcTemplate().query(querySql, new BeanPropertyRowMapper<User>(User.class));  
        }  
     
    } 

springmvc.xml
Java代码

    <?xml version="1.0" encoding="UTF-8" ?>  
    <beans xmlns="http://www.springframework.org/schema/beans"   
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:p="http://www.springframework.org/schema/p"   
        xmlns:context="http://www.springframework.org/schema/context" 
        xmlns:mvc="http://www.springframework.org/schema/mvc"     
        xsi:schemaLocation="  
            http://www.springframework.org/schema/beans   
            http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  
            http://www.springframework.org/schema/context   
            http://www.springframework.org/schema/context/spring-context-3.0.xsd  
            http://www.springframework.org/schema/mvc      
            http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd">  
        <!--   
             自动搜索@Controller标注的类  
             用于指明系统从哪个路径下寻找controller，然后提前初始化这些对象。  
         -->  
        <context:component-scan base-package="com.liuzd.sj.web" />      
          
         <!--  ③：对模型视图名称的解析，即在模型视图名称添加前后缀 -->  
        <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver" p:prefix="/WEB-INF/jsp/" p:suffix=".jsp"/>  
          
     </beans> 

userList.jsp
Java代码

    <%@ page language="java" pageEncoding="UTF-8" contentType="text/html; charset=UTF-8"%>  
    <%@ taglib uri="/WEB-INF/c.tld" prefix="c"%>  
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  
    <html>  
      <head>  
        <%@ include file="/common/meta.jsp"%>  
      </head>    
      <body>  
          <table width="60%" border="1" cellpadding="0" align="center">  
                <thead>  
                    <tr>  
                        <th style="cursor: hand;" title="按姓名进行排序" onclick="sortPage('name')" valign="top">  
                            姓名<font color='red'>${page.sortName eq "name" ? page.sortInfo : page.defaultInfo}</font>  
                        </th>  
                        <th style="cursor: hand;" title="按年龄进行排序" onclick="sortPage('age')" valign="top">  
                            年龄<font color='red'>${page.sortName eq "age" ? page.sortInfo : page.defaultInfo}</font>  
                        </th>  
                        <th style="cursor: hand;" title="按性别进行排序" onclick="sortPage('sex')" valign="top">  
                            性别<font color='red'>${page.sortName eq "sex" ? page.sortInfo : page.defaultInfo}</font>  
                        </th>  
                        <th style="cursor: hand;" title="按地址进行排序" onclick="sortPage('address')" valign="top">  
                            地址<font color='red'>${page.sortName eq "address" ? page.sortInfo : page.defaultInfo}</font>  
                        </th>  
                        <th style="cursor: hand;" >  
                            操作  
                        </th>  
                    </tr>  
                </thead>  
                <tbody>             
     
                    <c:forEach items="${userList}" var="user">  
                        <tr align="center">  
                            <td>  
                                ${user.name}  
                            </td>  
                            <td>  
                                ${user.age}  
                            </td>  
                            <td>  
                                ${user.sex eq 1 ? "男" : user.sex eq 2 ? "女" : "未知"}  
                            </td>  
                            <td>  
                                ${user.address}  
                            </td>  
                            <td>  
                                <a  
                                    href="${pageContext.request.contextPath}/user/toAddUser.do">添加</a>  
                                |  
                                <a  
                                    href="${pageContext.request.contextPath}/user/getUser/${user.id}.do">编辑</a>  
                                |  
                                <a  
                                    href="${pageContext.request.contextPath}/user/delUser/${user.id}.do">删除</a>  
                            </td>  
                        </tr>  
                    </c:forEach>  
                    <jsp:include page="/page/page.jsp">  
                        <jsp:param name="url" value="user/userList.do" />                   
                    </jsp:include>  
                      
                </tbody>  
            </table>  
            <br>  
            <a href="${pageContext.request.contextPath}/index.jsp">返回</a><br>       
      </body>  
    </html>