SpringMVC+JDBC:分页示例_MySQL

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


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

Java代码

1. package com.liuzd.sj.web;
3. import java.util.List;
5. import javax.annotation.Resource;
6. import javax.servlet.http.HttpServletRequest;
8. import org.springframework.stereotype.Controller;
9. import org.springframework.web.bind.annotation.PathVariable;
10. import org.springframework.web.bind.annotation.RequestMapping;
11. import org.springframework.web.bind.annotation.SessionAttributes;
12. import org.springframework.web.servlet.ModelAndView;
14. import com.liuzd.page.Page;
15. import com.liuzd.sj.entity.User;
16. import com.liuzd.sj.service.UserService;
18. @Controller
19. @RequestMapping("/user")
20. @SessionAttributes("userList")
21. public class UserController extends BaseController{
23. private UserService userService;
26. public UserService getUserService() {
27. return userService;
28. }
30. @Resource
31. public void setUserService(UserService userService) {
32. this.userService = userService;
33. }
36. @RequestMapping("/userList")
37. public ModelAndView userList(HttpServletRequest request){
38. StringBuilder querySql = new StringBuilder();
39. querySql.append("select * from users where 1=1 ");
41. String oracleQuerySql = querySql.toString();
42. //获取总条数
43. Long totalCount = new Long(this.getUserService().pageCounts(oracleQuerySql));
44. //设置分页对象
45. Page page = executePage(request,oracleQuerySql,totalCount," id desc ");
47. ModelAndView mv = new ModelAndView();
48. //查询集合
49. List users = this.getUserService().pageList(page.getQuerySql());
50. mv.addObject("userList",users);
51. mv.setViewName("userList");
52. return mv;
53. }
57. @RequestMapping("/addUser")
58. public ModelAndView addUser(HttpServletRequest request,User user){
59. System.out.println("ADD USER: "+ user);
60. this.userService.addUser(user);
61. return userList(request);
62. }
64. @RequestMapping("/toAddUser")
65. public String toAddUser(){
66. return "addUser";
67. }
69. @RequestMapping("/delUser/{id}")
70. public ModelAndView delUser(@PathVariable("id") String id,HttpServletRequest request){
71. this.userService.delUser(new User().setId(id));
72. return userList(request);
73. }
75. @RequestMapping("/getUser/{id}")
76. public ModelAndView getUser(@PathVariable("id") String id){
77. User user = this.userService.getUserById(new User().setId(id));
78. ModelAndView mv = new ModelAndView("updateUser");
79. mv.addObject("user",user);
80. return mv;
81. }
83. @RequestMapping("/updateUser")
84. public ModelAndView editUser(User user,HttpServletRequest request){
85. System.out.println("编辑: "+user);
86. this.userService.editUser(user);
87. return userList(request);
88. }
90. }

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 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代码

1. package com.liuzd.sj.web;
3. import javax.servlet.http.HttpServletRequest;
5. import com.liuzd.page.Page;
6. import com.liuzd.page.PageState;
7. import com.liuzd.page.PageUtil;
9. /**
10. *Title:
11. *Description:
12. *Copyright: Copyright (c) 2011
13. *Company:http://liuzidong.iteye.com/
14. *Makedate:2011-5-23 下午03:31:03
15. * @author liuzidong
16. * @version 1.0
17. * @since 1.0
18. *
19. */
20. public class BaseController {
22. /**
23. * oracel的三层分页语句
24. * 子类在展现数据前,进行分页计算!
25. * @param querySql 查询的SQL语句,未进行分页
26. * @param totalCount 根据查询SQL获取的总条数
27. * @param columnNameDescOrAsc 列名+排序方式 : ID DESC or ASC
28. */
29. protected Page executePage(HttpServletRequest request,String querySql,Long totalCount,String columnNameDescOrAsc){
30. String oracleSql = PageUtil.createQuerySql(querySql,columnNameDescOrAsc);
31. if(null == totalCount){
32. totalCount = 0L;
33. }
34. /**页面状态,这个状态是分页自带的,与业务无关*/
35. String pageAction = request.getParameter("pageAction");
36. String value = request.getParameter("pageKey");
38. /**获取下标判断分页状态*/
39. int index = PageState.getOrdinal(pageAction);
41. Page page = null;
42. /**
43. * index <1 只有二种状态
44. * 1 当首次调用时,分页状态类中没有值为 NULL 返回 -1
45. * 2 当页面设置每页显示多少条: index=0,当每页显示多少条时,分页类要重新计算
46. * */
47. Page sessiOnPage= getPage(request);
49. if(index <1){
50. page = PageUtil.inintPage(oracleSql,totalCount,index,value,sessionPage);
51. }else{
52. page = PageUtil.execPage(index,value,sessionPage);
53. }
54. setSession(request,page);
55. return page;
56. }
58. private Page getPage(HttpServletRequest request) {
59. Page page = (Page)request.getSession().getAttribute(PageUtil.SESSION_PAGE_KEY);
60. if(page == null){
61. page = new Page();
62. }
63. return page;
64. }
66. private void setSession(HttpServletRequest request,Page page) {
67. request.getSession().setAttribute(PageUtil.SESSION_PAGE_KEY,page);
68. }
70. }

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代码

1. package com.liuzd.sj.dao;
3. import java.sql.ResultSet;
4. import java.sql.SQLException;
6. import org.springframework.jdbc.core.RowMapper;
8. import com.liuzd.sj.entity.User;
10. public class UserRowMapper implements RowMapper {
13. public UserRowMapper(){}
15. public User mapRow(ResultSet rs, int index) throws SQLException {
16. User user = new User(
17. rs.getString("id"),
18. rs.getString("name"),
19. rs.getString("password"),
20. rs.getString("address"),
21. rs.getString("sex"),
22. rs.getInt("age")
23. );
24. return user;
25. }
26. }

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 {	
	
	
	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代码

1. package com.liuzd.sj.dao.impl;
3. import java.sql.PreparedStatement;
4. import java.sql.SQLException;
5. import java.util.List;
7. import javax.annotation.Resource;
9. import org.springframework.jdbc.core.BeanPropertyRowMapper;
10. import org.springframework.jdbc.core.PreparedStatementSetter;
11. import org.springframework.stereotype.Repository;
13. import com.liuzd.sj.dao.UserDAO;
14. import com.liuzd.sj.dao.UserRowMapper;
15. import com.liuzd.sj.entity.User;
17. @Repository("userDao")
18. public class UserDAOImpl implements UserDAO
19. {
21. private static final String INSERT = "insert into users(id,name,age,sex,address,password)VALUES(?,?,?,?,?,?)";
22. private static final String UPDATE = "update users set name=?,age=?,sex=?,address=?,password=? where id=?";
23. private static final String GET = "select * from users where id=?";
24. private static final String CHECK = "select count(1) from users where name=? and password=?";
25. private static final String SELECT = "select * from users";
26. private static final String DEL = "delete users where id=?";
28. private org.springframework.jdbc.core.JdbcTemplate jdbcTemplate;
30. public org.springframework.jdbc.core.JdbcTemplate getJdbcTemplate() {
31. return jdbcTemplate;
32. }
34. @Resource
35. public void setJdbcTemplate(
36. org.springframework.jdbc.core.JdbcTemplate jdbcTemplate) {
37. this.jdbcTemplate = jdbcTemplate;
38. }
41. public void addUser(final User user) {
42. getJdbcTemplate().update(INSERT, new PreparedStatementSetter(){
43. public void setValues(PreparedStatement ps)
44. throws SQLException {
45. int i = 0;
46. ps.setString(++i, user.getId());
47. ps.setString(++i, user.getName());
48. ps.setInt(++i, user.getAge());
49. ps.setString(++i,user.getSex());
50. ps.setString(++i,user.getAddress());
51. ps.setString(++i,user.getPassword());
52. }
53. });
56. }
58. public int checkUserExits(User user) {
59. return getJdbcTemplate().queryForInt(CHECK, user.getName(),user.getPassword());
60. }
62. public void delUser(User user) {
63. getJdbcTemplate().update(DEL, user.getId());
64. }
66. public void editUser(final User user) {
67. getJdbcTemplate().update(UPDATE, new PreparedStatementSetter(){
68. public void setValues(PreparedStatement ps)
69. throws SQLException {
70. int i = 0;
71. ps.setString(++i, user.getName());
72. ps.setInt(++i, user.getAge());
73. ps.setString(++i,user.getSex());
74. ps.setString(++i,user.getAddress());
75. ps.setString(++i,user.getPassword());
76. ps.setString(++i, user.getId());
77. }
78. });
79. }
81. public List getAllUser() {
82. return getJdbcTemplate().query(SELECT, new BeanPropertyRowMapper(User.class));
83. }
85. public User getUserById(User user) {
86. return getJdbcTemplate().queryForObject(GET, new UserRowMapper(),user.getId());
87. }
89. public int pageCounts(String querySql) {
90. return getJdbcTemplate().queryForInt("select count(1) from("+querySql+")");
91. }
93. public List pageList(String querySql) {
94. return getJdbcTemplate().query(querySql, new UserRowMapper());
95. }
97. }

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 getAllUser() {		
		return getJdbcTemplate().query(SELECT, new BeanPropertyRowMapper(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 pageList(String querySql) {		
		return getJdbcTemplate().query(querySql, new UserRowMapper());
	}

}

UserDAOImpl2.java

Java代码

1. package com.liuzd.sj.dao.impl;
3. import java.util.List;
4. import java.util.Map;
6. import javax.annotation.Resource;
8. import org.springframework.jdbc.core.BeanPropertyRowMapper;
9. import org.springframework.stereotype.Repository;
11. import com.liuzd.sj.dao.UserDAO;
12. import com.liuzd.sj.entity.User;
13. import com.liuzd.util.BeanToMapUtil;
15. @Repository("userDao2")
16. public class UserDAOImpl2 implements UserDAO
17. {
19. private static final String INSERT = "insert into users(id,name,age,sex,address,password)VALUES(:id,:name,:age,:sex,:address,:password)";
20. private static final String UPDATE = "update users set name=:name,age=:age,sex=:sex,address=:address,password=:password where id=:id";
21. private static final String GET = "select * from users where id=?";
22. private static final String CHECK = "select count(1) from users where name=? and password=?";
23. private static final String SELECT = "select * from users";
24. private static final String DEL = "delete users where id=?";
26. private org.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate;
28. public org.springframework.jdbc.core.simple.SimpleJdbcTemplate getSimpleJdbcTemplate() {
29. return simpleJdbcTemplate;
30. }
32. @Resource
33. public void setSimpleJdbcTemplate(
34. org.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate) {
35. this.simpleJdbcTemplate = simpleJdbcTemplate;
36. }
39. public void addUser(final User user) {
40. Map userMap = BeanToMapUtil.beanToMap(user);
41. getSimpleJdbcTemplate().update(INSERT, userMap);
42. }
44. public int checkUserExits(User user) {
45. return getSimpleJdbcTemplate().queryForInt(CHECK, user.getName(),user.getPassword());
46. }
48. public void delUser(User user) {
49. getSimpleJdbcTemplate().update(DEL, user.getId());
50. }
52. public void editUser(final User user) {
53. Map userMap = BeanToMapUtil.beanToMap(user);
54. getSimpleJdbcTemplate().update(UPDATE, userMap);
55. }
57. public List getAllUser() {
58. return getSimpleJdbcTemplate().query(SELECT, new BeanPropertyRowMapper(User.class));
59. }
61. public User getUserById(User user) {
62. return getSimpleJdbcTemplate().queryForObject(GET, new BeanPropertyRowMapper(User.class),user.getId());
63. }
65. public int pageCounts(String querySql) {
66. return getSimpleJdbcTemplate().queryForInt("select count(1) from("+querySql+")");
67. }
69. public List pageList(String querySql) {
70. return getSimpleJdbcTemplate().query(querySql, new BeanPropertyRowMapper(User.class));
71. }
73. }

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 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 userMap = BeanToMapUtil.beanToMap(user);		
		getSimpleJdbcTemplate().update(UPDATE, userMap);
	}

	public List getAllUser() {		
		return getSimpleJdbcTemplate().query(SELECT, new BeanPropertyRowMapper(User.class));
	}

	public User getUserById(User user) {		
		return getSimpleJdbcTemplate().queryForObject(GET, new BeanPropertyRowMapper(User.class),user.getId());
	}

	publ