站内搜索

SpringMVC+JDBC:分页示例_MySQL

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


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

Java代码

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


BaseController.java

Java代码

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


UserRowMapper.java

Java代码

  1. package com.liuzd.sj.dao;
  2. import java.sql.ResultSet;
  3. import java.sql.SQLException;
  4. import org.springframework.jdbc.core.RowMapper;
  5. import com.liuzd.sj.entity.User;
  6. public class UserRowMapper implements RowMapper<User> {
  7. public UserRowMapper(){}
  8. public User mapRow(ResultSet rs, int index) throws SQLException {
  9. User user = new User(
  10. rs.getString("id"),
  11. rs.getString("name"),
  12. rs.getString("password"),
  13. rs.getString("address"),
  14. rs.getString("sex"),
  15. rs.getInt("age")
  16. );
  17. return user;
  18. }
  19. }


UserDAOImpl.java

Java代码

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


UserDAOImpl2.java

Java代码

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

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