分页查询SpringMVC+JDBC.docx
《分页查询SpringMVC+JDBC.docx》由会员分享,可在线阅读,更多相关《分页查询SpringMVC+JDBC.docx(16页珍藏版)》请在冰豆网上搜索。
![分页查询SpringMVC+JDBC.docx](https://file1.bdocx.com/fileroot1/2022-12/15/73bd4398-417e-46d1-b51c-608c13958fbe/73bd4398-417e-46d1-b51c-608c13958fbe1.gif)
分页查询SpringMVC+JDBC
本文通过一个实例,详细地说明了如何用SpringMVC进行数据库查询并且分页显示
开发环境:
操作系统:
windowsXPsp3
数据库:
Oracle10g
IDE:
MyEclipse6
Web容器:
Tomcat5.x
JDK版本:
JDK1.6
工程切图如下
基本上参照之前示例修改得来,重点关注SimpleJdbcTemplate与JdbcTemplate用法
以下只列出比较重要的类
UserController.java
packagecom.liuzd.sj.web;
importjava.util.List;
importjavax.annotation.Resource;
importjavax.servlet.http.HttpServletRequest;
importorg.springframework.stereotype.Controller;
importorg.springframework.web.bind.annotation.PathVariable;
importorg.springframework.web.bind.annotation.RequestMapping;
importorg.springframework.web.bind.annotation.SessionAttributes;
importorg.springframework.web.servlet.ModelAndView;
importcom.liuzd.page.Page;
importcom.liuzd.sj.entity.User;
importcom.liuzd.sj.service.UserService;
@Controller
@RequestMapping("/user")
@SessionAttributes("userList")
publicclassUserControllerextendsBaseController{
privateUserServiceuserService;
publicUserServicegetUserService(){
returnuserService;
}
@Resource
publicvoidsetUserService(UserServiceuserService){
this.userService=userService;
}
@RequestMapping("/userList")
publicModelAndViewuserList(HttpServletRequestrequest){
StringBuilderquerySql=newStringBuilder();
querySql.append("select*fromuserswhere1=1");
StringoracleQuerySql=querySql.toString();
//获取总条数
LongtotalCount=newLong(this.getUserService().pageCounts(oracleQuerySql));
//设置分页对象
Pagepage=executePage(request,oracleQuerySql,totalCount,"iddesc");
ModelAndViewmv=newModelAndView();
//查询集合
Listusers=this.getUserService().pageList(page.getQuerySql());
mv.addObject("userList",users);
mv.setViewName("userList");
returnmv;
}
@RequestMapping("/addUser")
publicModelAndViewaddUser(HttpServletRequestrequest,Useruser){
System.out.println("ADDUSER:
"+user);
this.userService.addUser(user);
returnuserList(request);
}
@RequestMapping("/toAddUser")
publicStringtoAddUser(){
return"addUser";
}
@RequestMapping("/delUser/{id}")
publicModelAndViewdelUser(@PathVariable("id")Stringid,HttpServletRequestrequest){
this.userService.delUser(newUser().setId(id));
returnuserList(request);
}
@RequestMapping("/getUser/{id}")
publicModelAndViewgetUser(@PathVariable("id")Stringid){
Useruser=this.userService.getUserById(newUser().setId(id));
ModelAndViewmv=newModelAndView("updateUser");
mv.addObject("user",user);
returnmv;
}
@RequestMapping("/updateUser")
publicModelAndVieweditUser(Useruser,HttpServletRequestrequest){
System.out.println("编辑:
"+user);
this.userService.editUser(user);
returnuserList(request);
}
}
BaseController.java
packagecom.liuzd.sj.web;
importjavax.servlet.http.HttpServletRequest;
importcom.liuzd.page.Page;
importcom.liuzd.page.PageState;
importcom.liuzd.page.PageUtil;
/**
*Title:
*Description:
*Copyright:
Copyright(c)2011
*Company:
*Makedate:
2011-5-23下午03:
31:
03
*@authorliuzidong
*@version1.0
*@since1.0
*
*/
publicclassBaseController{
/**
*oracel的三层分页语句
*子类在展现数据前,进行分页计算!
*@paramquerySql查询的SQL语句,未进行分页
*@paramtotalCount根据查询SQL获取的总条数
*@paramcolumnNameDescOrAsc列名+排序方式:
IDDESCorASC
*/
protectedPageexecutePage(HttpServletRequestrequest,StringquerySql,LongtotalCount,StringcolumnNameDescOrAsc){
StringoracleSql=PageUtil.createQuerySql(querySql,columnNameDescOrAsc);
if(null==totalCount){
totalCount=0L;
}
/**页面状态,这个状态是分页自带的,与业务无关*/
StringpageAction=request.getParameter("pageAction");
Stringvalue=request.getParameter("pageKey");
/**获取下标判断分页状态*/
intindex=PageState.getOrdinal(pageAction);
Pagepage=null;
/**
*index<1只有二种状态
*1当首次调用时,分页状态类中没有值为NULL返回-1
*2当页面设置每页显示多少条:
index=0,当每页显示多少条时,分页类要重新计算
**/
PagesessionPage=getPage(request);
if(index<1){
page=PageUtil.inintPage(oracleSql,totalCount,index,value,sessionPage);
}else{
page=PageUtil.execPage(index,value,sessionPage);
}
setSession(request,page);
returnpage;
}
privatePagegetPage(HttpServletRequestrequest){
Pagepage=(Page)request.getSession().getAttribute(PageUtil.SESSION_PAGE_KEY);
if(page==null){
page=newPage();
}
returnpage;
}
privatevoidsetSession(HttpServletRequestrequest,Pagepage){
request.getSession().setAttribute(PageUtil.SESSION_PAGE_KEY,page);
}
}
UserRowMapper.java
packagecom.liuzd.sj.dao;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importorg.springframework.jdbc.core.RowMapper;
importcom.liuzd.sj.entity.User;
publicclassUserRowMapperimplementsRowMapper{
publicUserRowMapper(){}
publicUsermapRow(ResultSetrs,intindex)throwsSQLException{
Useruser=newUser(
rs.getString("id"),
rs.getString("name"),
rs.getString("password"),
rs.getString("address"),
rs.getString("sex"),
rs.getInt("age")
);
returnuser;
}
}
UserDAOImpl.java
packagecom.liuzd.sj.dao.impl;
importjava.sql.PreparedStatement;
importjava.sql.SQLException;
importjava.util.List;
importjavax.annotation.Resource;
importorg.springframework.jdbc.core.BeanPropertyRowMapper;
importorg.springframework.jdbc.core.PreparedStatementSetter;
importorg.springframework.stereotype.Repository;
importcom.liuzd.sj.dao.UserDAO;
importcom.liuzd.sj.dao.UserRowMapper;
importcom.liuzd.sj.entity.User;
@Repository("userDao")
publicclassUserDAOImplimplementsUserDAO
{
privatestaticfinalStringINSERT="insertintousers(id,name,age,sex,address,password)VALUES(?
?
?
?
?
?
)";
privatestaticfinalStringUPDATE="updateuserssetname=?
age=?
sex=?
address=?
password=?
whereid=?
";
privatestaticfinalStringGET="select*fromuserswhereid=?
";
privatestaticfinalStringCHECK="selectcount
(1)fromuserswherename=?
andpassword=?
";
privatestaticfinalStringSELECT="select*fromusers";
privatestaticfinalStringDEL="deleteuserswhereid=?
";
privateorg.springframework.jdbc.core.JdbcTemplatejdbcTemplate;
publicorg.springframework.jdbc.core.JdbcTemplategetJdbcTemplate(){
returnjdbcTemplate;
}
@Resource
publicvoidsetJdbcTemplate(
org.springframework.jdbc.core.JdbcTemplatejdbcTemplate){
this.jdbcTemplate=jdbcTemplate;
}
publicvoidaddUser(finalUseruser){
getJdbcTemplate().update(INSERT,newPreparedStatementSetter(){
publicvoidsetValues(PreparedStatementps)
throwsSQLException{
inti=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());
}
});
}
publicintcheckUserExits(Useruser){
returngetJdbcTemplate().queryForInt(CHECK,user.getName(),user.getPassword());
}
publicvoiddelUser(Useruser){
getJdbcTemplate().update(DEL,user.getId());
}
publicvoideditUser(finalUseruser){
getJdbcTemplate().update(UPDATE,newPreparedStatementSetter(){
publicvoidsetValues(PreparedStatementps)
throwsSQLException{
inti=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());
}
});
}
publicListgetAllUser(){
returngetJdbcTemplate().query(SELECT,newBeanPropertyRowMapper(User.class));
}
publicUsergetUserById(Useruser){
returngetJdbcTemplate().queryForObject(GET,newUserRowMapper(),user.getId());
}
publicintpageCounts(StringquerySql){
returngetJdbcTemplate().queryForInt("selectcount
(1)from("+querySql+")");
}
publicListpageList(StringquerySql){
returngetJdbcTemplate().query(querySql,newUserRowMapper());
}
}
UserDAOImpl2.java
packagecom.liuzd.sj.dao.impl;
importjava.util.List;
importjava.util.Map;
importjavax.annotation.Resource;
importorg.springframework.jdbc.core.BeanPropertyRowMapper;
importorg.springframework.stereotype.Repository;
importcom.liuzd.sj.dao.UserDAO;
importcom.liuzd.sj.entity.User;
importcom.liuzd.util.BeanToMapUtil;
@Repository("userDao2")
publicclassUserDAOImpl2implementsUserDAO
{
privatestaticfinalStringINSERT="insertintousers(id,name,age,sex,address,password)VALUES(:
id,:
name,:
age,:
sex,:
address,:
password)";
privatestaticfinalStringUPDATE="updateuserssetname=:
name,age=:
age,sex=:
sex,address=:
address,password=:
passwordwhereid=:
id";
privatestaticfinalStringGET="select*fromuserswhereid=?
";
privatestaticfinalStringCHECK="selectcount
(1)fromuserswherename=?
andpassword=?
";
privatestaticfinalStringSELECT="select*fromusers";
privatestaticfinalStringDEL="deleteuserswhereid=?
";
privateorg.springframework.jdbc.core.simple.SimpleJdbcTemplatesimpleJdbcTemplate;
publicorg.springframework.jdbc.core.simple.SimpleJdbcTemplategetSimpleJdbcTemplate(){
returnsimpleJdbcTemplate;
}
@Resource
publicvoidsetSimpleJdbcTemplate(
org.springframework.jdbc.core.simple.SimpleJdbcTemplatesimpleJdbcTemplate){
this.simpleJdbcTemplate=simpleJdbcTemplate;
}
publicvoidaddUser(finalUseruser){
MapuserMap=BeanToMapUtil.beanToMap(user);
getSimpleJdbcTemplate().update(INSERT,userMap);
}
publicintcheckUserExits(Useruser){
returngetSimpleJdbcTemplate().queryForInt(CHECK,user.getName(),user.getPassword());
}
public