SpringMVC+JDBC:分页示例

一 环境:XP3+Oracle10g+MyEclipse6+(Tomcat)+JDK1.5
二 工程相关图片:


三 基本上参照之前示例修改得来,重点关注SimpleJdbcTemplate与JdbcTemplate用法
以下只列出比较重要的类
UserController.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>

另外page.jsp与page.jar可在附件中下载

评论

7 楼 dragon888 2016-05-01  

伙计lib下所的有jar包故意删除了吗？

6 楼 windersehen 2015-08-17  

pager.jar的page中好像没executePage，调用报错。
工程检出页面全部报错，无法使用。
综上，没法用。

5 楼 ironfanprince 2014-05-22  

动手试了一下，除了有个别的小问题外，完全可用。大家应该表示感谢才对！谢谢！

4 楼 shiguanghui 2014-05-09  

这种垃圾文章，不转也罢。

写这样的东西，纯粹是浪费大家时间。真烂！

3 楼 Sawyer36 2012-06-04  

项目导入到eclipse中，所有的jar包都已经加上，但是所有的jsp界面都有错误，这是什么情况，求指教？

2 楼 liuzidong 2012-06-02  

1 你可以用jd.exe反编译jar就可得到源码
2 这个分布是针对oracle作的分页，你修改下sql语句就行了，在mysql中分页是：limit 0,10这样！你试下吧！

1 楼 fusm1008 2012-06-01  

求解！我把这个应用移植到mysql数据库，报错：
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT TTT.* FROM(SELECT TT.*,ROWNUM RN FROM( USERS WHERE 1=1 ORDER BY  ID DESC  )TT)TTT  WHERE RN>0 AND RN<=10]; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE 1=1 ORDER BY  ID DESC  )TT)TTT  WHERE RN>0 AND RN<=10' at line 1
**************
可以传给我个page.jar的java源码吗？