SpringMVC+JDBC:分页示例_MySQL

一 环境:XP3+Oracle10g+MyEclipse6+(Tomcat)+JDK1.5 二 工程相关图片: 三 基本上参照之前示例修改得来,重点关注SimpleJdbcTemplate与JdbcTemplate用法 以下只列出比较重要的类 UserController.java Java代码 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); } } 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;}@Resourcepublic 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代码 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 当首次调用时,分页状态类中没有值为 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); } } 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;}@Resourcepublic 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;}@Resourcepublic 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