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

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 {      
    

       
    

       
    

    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;   
    

    }   
    

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



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());   
    

    }   
    

   
    

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



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());   
    

    }   
    

   
    

    public int pageCounts(String querySql) {           
    

        return getSimpleJdbcTemplate().queryForInt("select count(1) from("+querySql+")");   
    

    }   
    

   
    

    public List pageList(String querySql) {        
    

        return getSimpleJdbcTemplate().query(querySql, new BeanPropertyRowMapper(User.class));   
    

    }   
    

   
    

}   
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