Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > body text

SpringMVC+JDBC:分页示例_MySQL

WBOY
Release： 2016-05-31 08:46:40
Original
1670 people have browsed it

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


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

Java代码  

  1. package com.liuzd.sj.web;  
  2.   
  3. import java.util.List;  
  4.   
  5. import javax.annotation.Resource;  
  6. import javax.servlet.http.HttpServletRequest;  
  7.   
  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;  
  13.   
  14. import com.liuzd.page.Page;  
  15. import com.liuzd.sj.entity.User;  
  16. import com.liuzd.sj.service.UserService;  
  17.   
  18. @Controller  
  19. @RequestMapping("/user")  
  20. @SessionAttributes("userList")  
  21. public class UserController extends BaseController{  
  22.       
  23.     private UserService userService;  
  24.       
  25.   
  26.     public UserService getUserService() {  
  27.         return userService;  
  28.     }  
  29.       
  30.     @Resource  
  31.     public void setUserService(UserService userService) {  
  32.         this.userService = userService;  
  33.     }     
  34.   
  35.       
  36.     @RequestMapping("/userList")  
  37.     public ModelAndView userList(HttpServletRequest request){  
  38.         StringBuilder querySql = new StringBuilder();  
  39.         querySql.append("select * from users where 1=1 ");  
  40.           
  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 ");       
  46.           
  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.     }  
  54.       
  55.       
  56.       
  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.     }  
  63.       
  64.     @RequestMapping("/toAddUser")  
  65.     public String toAddUser(){        
  66.         return "addUser";  
  67.     }  
  68.       
  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.     }  
  74.       
  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.     }     
  82.       
  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.     }         
  89.       
  90. }  
Copy after login

BaseController.java

Java代码  

  1. package com.liuzd.sj.web;  
  2.   
  3. import javax.servlet.http.HttpServletRequest;  
  4.   
  5. import com.liuzd.page.Page;  
  6. import com.liuzd.page.PageState;  
  7. import com.liuzd.page.PageUtil;  
  8.   
  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 {  
  21.       
  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");  
  37.           
  38.         /**获取下标判断分页状态*/  
  39.         int index = PageState.getOrdinal(pageAction);                 
  40.           
  41.         Page page = null;         
  42.         /** 
  43.          * index  
  44.          * 1 当首次调用时,分页状态类中没有值为 NULL 返回 -1 
  45.          * 2 当页面设置每页显示多少条: index=0,当每页显示多少条时,分页类要重新计算 
  46.          * */  
  47.         Page sessionPage = getPage(request);  
  48.           
  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.     }     
  57.       
  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.     }     
  65.       
  66.     private void setSession(HttpServletRequest request,Page page) {  
  67.         request.getSession().setAttribute(PageUtil.SESSION_PAGE_KEY,page);        
  68.     }     
  69.   
  70. }  
Copy after login

UserDAOImpl.java

Java代码  

  1. package com.liuzd.sj.dao.impl;  
  2.   
  3. import java.sql.PreparedStatement;  
  4. import java.sql.SQLException;  
  5. import java.util.List;  
  6.   
  7. import javax.annotation.Resource;  
  8.   
  9. import org.springframework.jdbc.core.BeanPropertyRowMapper;  
  10. import org.springframework.jdbc.core.PreparedStatementSetter;  
  11. import org.springframework.stereotype.Repository;  
  12.   
  13. import com.liuzd.sj.dao.UserDAO;  
  14. import com.liuzd.sj.dao.UserRowMapper;  
  15. import com.liuzd.sj.entity.User;  
  16.   
  17. @Repository("userDao")  
  18. public class UserDAOImpl implements UserDAO  
  19. {  
  20.       
  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=?";  
  27.       
  28.     private org.springframework.jdbc.core.JdbcTemplate jdbcTemplate;      
  29.   
  30.     public org.springframework.jdbc.core.JdbcTemplate getJdbcTemplate() {  
  31.         return jdbcTemplate;  
  32.     }  
  33.   
  34.     @Resource  
  35.     public void setJdbcTemplate(  
  36.             org.springframework.jdbc.core.JdbcTemplate jdbcTemplate) {  
  37.         this.jdbcTemplate = jdbcTemplate;  
  38.     }     
  39.       
  40.   
  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.         });  
  54.           
  55.           
  56.     }  
  57.   
  58.     public int checkUserExits(User user) {                
  59.         return getJdbcTemplate().queryForInt(CHECK, user.getName(),user.getPassword());  
  60.     }  
  61.   
  62.     public void delUser(User user) {  
  63.         getJdbcTemplate().update(DEL, user.getId());  
  64.     }  
  65.   
  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.     }  
  80.   
  81.     public List getAllUser() {        
  82.         return getJdbcTemplate().query(SELECT, new BeanPropertyRowMapper(User.class));  
  83.     }  
  84.   
  85.     public User getUserById(User user) {          
  86.         return getJdbcTemplate().queryForObject(GET, new UserRowMapper(),user.getId());  
  87.     }  
  88.   
  89.     public int pageCounts(String querySql) {          
  90.         return getJdbcTemplate().queryForInt("select count(1) from("+querySql+")");  
  91.     }  
  92.   
  93.     public List pageList(String querySql) {       
  94.         return getJdbcTemplate().query(querySql, new UserRowMapper());  
  95.     }  
  96.   
  97. }  
Copy after login

UserDAOImpl2.java

Java代码  

  1. package com.liuzd.sj.dao.impl;  
  2.   
  3. import java.util.List;  
  4. import java.util.Map;  
  5.   
  6. import javax.annotation.Resource;  
  7.   
  8. import org.springframework.jdbc.core.BeanPropertyRowMapper;  
  9. import org.springframework.stereotype.Repository;  
  10.   
  11. import com.liuzd.sj.dao.UserDAO;  
  12. import com.liuzd.sj.entity.User;  
  13. import com.liuzd.util.BeanToMapUtil;  
  14.   
  15. @Repository("userDao2")  
  16. public class UserDAOImpl2 implements UserDAO  
  17. {  
  18.       
  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=?";  
  25.       
  26.     private org.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate;  
  27.       
  28.     public org.springframework.jdbc.core.simple.SimpleJdbcTemplate getSimpleJdbcTemplate() {  
  29.         return simpleJdbcTemplate;  
  30.     }  
  31.   
  32.     @Resource  
  33.     public void setSimpleJdbcTemplate(  
  34.             org.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate) {  
  35.         this.simpleJdbcTemplate = simpleJdbcTemplate;  
  36.     }     
  37.       
  38.   
  39.     public void addUser(final User user) {  
  40.         Map userMap = BeanToMapUtil.beanToMap(user);         
  41.         getSimpleJdbcTemplate().update(INSERT, userMap);          
  42.     }  
  43.   
  44.     public int checkUserExits(User user) {            
  45.         return getSimpleJdbcTemplate().queryForInt(CHECK, user.getName(),user.getPassword());  
  46.     }  
  47.   
  48.     public void delUser(User user) {  
  49.         getSimpleJdbcTemplate().update(DEL, user.getId());  
  50.     }  
  51.   
  52.     public void editUser(final User user) {               
  53.         Map userMap = BeanToMapUtil.beanToMap(user);         
  54.         getSimpleJdbcTemplate().update(UPDATE, userMap);  
  55.     }  
  56.   
  57.     public List getAllUser() {        
  58.         return getSimpleJdbcTemplate().query(SELECT, new BeanPropertyRowMapper(User.class));  
  59.     }  
  60.   
  61.     public User getUserById(User user) {          
  62.         return getSimpleJdbcTemplate().queryForObject(GET, new BeanPropertyRowMapper(User.class),user.getId());  
  63.     }  
  64.   
  65.     public int pageCounts(String querySql) {          
  66.         return getSimpleJdbcTemplate().queryForInt("select count(1) from("+querySql+")");  
  67.     }  
  68.   
  69.     public List pageList(String querySql) {       
  70.         return getSimpleJdbcTemplate().query(querySql, new BeanPropertyRowMapper(User.class));  
  71.     }  
  72.   
  73. }  
Copy after login
source：php.cn
Previous article：MySQL Fabric部署_MySQL Next article：MySQl 子查询，左右连接，多表连接学习笔记_MySQL
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
function_exists() cannot determine the custom function Function test () {return true;} if (function_exists ('test')) {echo "test is function...
From 2024-04-29 11:01:01
0
2
1972
How to display the mobile version of Google Chrome Hello teacher, how can I change Google Chrome into a mobile version?
From 2024-04-23 00:22:19
0
10
2132
The child window operates the parent window, but the output does not respond. The first two sentences are executable, but the last sentence cannot be implemented.
From 2024-04-19 15:37:47
0
1
1800
There is no output in the parent window document.onclick = function(){ window.opener.document.write('I am the output of the child ...
From 2024-04-18 23:52:34
0
1
1698
Where is the courseware about CSS mind mapping? Courseware
From 2024-04-16 10:10:18
0
0
1706
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template