站内搜索

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<User> 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. }


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


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<User> {
  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. }


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


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<String,Object> 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<String,Object> userMap = BeanToMapUtil.beanToMap(user);
  54. getSimpleJdbcTemplate().update(UPDATE, userMap);
  55. }
  57. public List<User> getAllUser() {
  58. return getSimpleJdbcTemplate().query(SELECT, new BeanPropertyRowMapper<User>(User.class));
  59. }
  61. public User getUserById(User user) {
  62. return getSimpleJdbcTemplate().queryForObject(GET, new BeanPropertyRowMapper<User>(User.class),user.getId());
  63. }
  65. public int pageCounts(String querySql) {
  66. return getSimpleJdbcTemplate().queryForInt("select count(1) from("+querySql+")");
  67. }
  69. public List<User> pageList(String querySql) {
  70. return getSimpleJdbcTemplate().query(querySql, new BeanPropertyRowMapper<User>(User.class));
  71. }
  73. }

  • 上一篇：MySQL Fabric部署_MySQL
  • 下一篇：MySQl 子查询，左右连接，多表连接学习笔记_MySQL