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

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

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

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

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

<?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

<%@ 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>