分页查询SpringMVC+JDBC.docx

1、分页查询SpringMVC+JDBC本文通过一个实例,详细地说明了如何用SpringMVC进行数据库查询并且分页显示开发环境:操作系统：windows XP sp3数据库：Oracle10gIDE：MyEclipse6Web容器：Tomcat5.xJDK版本：JDK1.6工程切图如下基本上参照之前示例修改得来,重点关注SimpleJdbcTemplate与JdbcTemplate用法 以下只列出比较重要的类 UserController.java package com.liuzd.sj.web;import java.util.List;import javax.annotation.Res

2、ource;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.sp

3、ringframework.web.servlet.ModelAndView;import com.liuzd.page.Page;import com.liuzd.sj.entity.User;import com.liuzd.sj.service.UserService;ControllerRequestMapping(/user)SessionAttributes(userList)public class UserController extends BaseController private UserService userService; public UserService g

4、etUserService() 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

5、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().pageLis

6、t(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(/toAddUse

7、r) 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

8、) 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.userServi

9、ce.editUser(user); return userList(request); BaseController.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: *Makedate

10、: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(HttpServletRequ

11、est 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

12、(pageKey); /*获取下标判断分页状态*/ int index = PageState.getOrdinal(pageAction); Page page = null; /* * index 1 只有二种状态 * 1 当首次调用时,分页状态类中没有值为 NULL 返回 -1 * 2 当页面设置每页显示多少条: index=0,当每页显示多少条时,分页类要重新计算 * */ Page sessionPage = getPage(request); if(index 1) page = PageUtil.inintPage(oracleSql,totalCount,index,value

13、,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

14、setSession(HttpServletRequest request,Page page) request.getSession().setAttribute(PageUtil.SESSION_PAGE_KEY,page); UserRowMapper.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

15、 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.ja

16、va 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.st

17、ereotype.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(?,?,?,?,?,?); privat

18、e 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 = selec

19、t * 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.Jdb

20、cTemplate 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.

21、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().upda

22、te(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.setStr

23、ing(+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(),u

24、ser.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 package com.liuzd.sj.dao.impl;import java.util.List;import

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

26、 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 sta

27、tic 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.cor

28、e.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