springjdbc分页 mysql_SpringMVC+JDBC:分页示例_MySQL

一 环境:XP3+Oracle10g+MyEclipse6+(Tomcat)+JDK1.5

二 工程相关图片:

三 基本上参照之前示例修改得来,重点关注SimpleJdbcTemplate与JdbcTemplate用法

以下只列出比较重要的类

UserController.java

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{

privateUserService userService;

publicUserService getUserService() {

returnuserService;

}

@Resource

publicvoidsetUserService(UserService userService) {

this.userService = userService;

}

@RequestMapping("/userList")

publicModelAndView userList(HttpServletRequest request){

StringBuilder querySql = newStringBuilder();

querySql.append("select * from users where 1=1 ");

String oracleQuerySql = querySql.toString();

//获取总条数

Long totalCount = newLong(this.getUserService().pageCounts(oracleQuerySql));

//设置分页对象

Page page = executePage(request,oracleQuerySql,totalCount," id desc ");

ModelAndView mv = newModelAndView();

//查询集合

Listusers = this.getUserService().pageList(page.getQuerySql());

mv.addObject("userList",users);

mv.setViewName("userList");

returnmv;

}

@RequestMapping("/addUser")

publicModelAndView addUser(HttpServletRequest request,User user){

System.out.println("ADD USER: "+ user);

this.userService.addUser(user);

returnuserList(request);

}

@RequestMapping("/toAddUser")

publicString toAddUser(){

return"addUser";

}

@RequestMapping("/delUser/{id}")

publicModelAndView delUser(@PathVariable("id") String id,HttpServletRequest request){

this.userService.delUser(newUser().setId(id));

returnuserList(request);

}

@RequestMapping("/getUser/{id}")

publicModelAndView getUser(@PathVariable("id") String id){

User user = this.userService.getUserById(newUser().setId(id));

ModelAndView mv = newModelAndView("updateUser");

mv.addObject("user",user);

returnmv;

}

@RequestMapping("/updateUser")

publicModelAndView editUser(User user,HttpServletRequest request){

System.out.println("编辑: "+user);

this.userService.editUser(user);

returnuserList(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;

}

@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();

//查询集合

Listusers = 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代码

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:http://liuzidong.iteye.com/

*Makedate:2011-5-23 下午03:31:03

* @author liuzidong

* @version 1.0

* @since 1.0

*

*/

publicclassBaseController {

/**

* oracel的三层分页语句

* 子类在展现数据前,进行分页计算!

* @param querySql 查询的SQL语句,未进行分页

* @param totalCount 根据查询SQL获取的总条数

* @param columnNameDescOrAsc 列名+排序方式 : ID DESC or ASC

*/

protectedPage 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");

/**获取下标判断分页状态*/

intindex = 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,sessionPage);

}else{

page = PageUtil.execPage(index,value,sessionPage);

}

setSession(request,page);

returnpage;

}

privatePage getPage(HttpServletRequest request) {

Page page = (Page)request.getSession().getAttribute(PageUtil.SESSION_PAGE_KEY);

if(page ==null){

page = newPage();

}

returnpage;

}

privatevoidsetSession(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 < 1 只有二种状态

* 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);

}

}

UserRowMapper.java

Java代码

packagecom.liuzd.sj.dao;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importorg.springframework.jdbc.core.RowMapper;

importcom.liuzd.sj.entity.User;

publicclassUserRowMapperimplementsRowMapper{

publicUserRowMapper(){}

publicUser mapRow(ResultSet rs,intindex)throwsSQLException {

User user = newUser(

rs.getString("id"),

rs.getString("name"),

rs.getString("password"),

rs.getString("address"),

rs.getString("sex"),

rs.getInt("age")

);

returnuser;

}

}

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代码

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

{

privatestaticfinalString INSERT ="insert into users(id,name,age,sex,address,password)VALUES(?,?,?,?,?,?)";

privatestaticfinalString UPDATE ="update users set name=?,age=?,sex=?,address=?,password=? where id=?";

privatestaticfinalString GET ="select * from users where id=?";

privatestaticfinalString CHECK ="select count(1) from users where name=? and password=?";

privatestaticfinalString SELECT ="select * from users";

privatestaticfinalString DEL ="delete users where id=?";

privateorg.springframework.jdbc.core.JdbcTemplate jdbcTemplate;

publicorg.springframework.jdbc.core.JdbcTemplate getJdbcTemplate() {

returnjdbcTemplate;

}

@Resource

publicvoidsetJdbcTemplate(

org.springframework.jdbc.core.JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

publicvoidaddUser(finalUser user) {

getJdbcTemplate().update(INSERT, newPreparedStatementSetter(){

publicvoidsetValues(PreparedStatement ps)

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(User user) {

returngetJdbcTemplate().queryForInt(CHECK, user.getName(),user.getPassword());

}

publicvoiddelUser(User user) {

getJdbcTemplate().update(DEL, user.getId());

}

publicvoideditUser(finalUser user) {

getJdbcTemplate().update(UPDATE, newPreparedStatementSetter(){

publicvoidsetValues(PreparedStatement ps)

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));

}

publicUser getUserById(User user) {

returngetJdbcTemplate().queryForObject(GET,newUserRowMapper(),user.getId());

}

publicintpageCounts(String querySql) {

returngetJdbcTemplate().queryForInt("select count(1) from("+querySql+")");

}

publicListpageList(String querySql) {

returngetJdbcTemplate().query(querySql,newUserRowMapper());

}

}

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 ListgetAllUser() {

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 ListpageList(String querySql) {

return getJdbcTemplate().query(querySql, new UserRowMapper());

}

}

UserDAOImpl2.java

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

{

privatestaticfinalString INSERT ="insert into users(id,name,age,sex,address,password)VALUES(:id,:name,:age,:sex,:address,:password)";

privatestaticfinalString UPDATE ="update users set name=:name,age=:age,sex=:sex,address=:address,password=:password where id=:id";

privatestaticfinalString GET ="select * from users where id=?";

privatestaticfinalString CHECK ="select count(1) from users where name=? and password=?";

privatestaticfinalString SELECT ="select * from users";

privatestaticfinalString DEL ="delete users where id=?";

privateorg.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate;

publicorg.springframework.jdbc.core.simple.SimpleJdbcTemplate getSimpleJdbcTemplate() {

returnsimpleJdbcTemplate;

}

@Resource

publicvoidsetSimpleJdbcTemplate(

org.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate) {

this.simpleJdbcTemplate = simpleJdbcTemplate;

}

publicvoidaddUser(finalUser user) {

MapuserMap = BeanToMapUtil.beanToMap(user);

getSimpleJdbcTemplate().update(INSERT, userMap);

}

publicintcheckUserExits(User user) {

returngetSimpleJdbcTemplate().queryForInt(CHECK, user.getName(),user.getPassword());

}

publicvoiddelUser(User user) {

getSimpleJdbcTemplate().update(DEL, user.getId());

}

publicvoideditUser(finalUser user) {

MapuserMap = BeanToMapUtil.beanToMap(user);

getSimpleJdbcTemplate().update(UPDATE, userMap);

}

publicListgetAllUser() {

returngetSimpleJdbcTemplate().query(SELECT,newBeanPropertyRowMapper(User.class));

}

publicUser getUserById(User user) {

returngetSimpleJdbcTemplate().queryForObject(GET,newBeanPropertyRowMapper(User.class),user.getId());

}

publicintpageCounts(String querySql) {

returngetSimpleJdbcTemplate().queryForInt("select count(1) from("+querySql+")");

}

publicListpageList(String querySql) {

returngetSimpleJdbcTemplate().query(querySql,newBeanPropertyRowMapper(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;

}

@Resource

public void setSimpleJdbcTemplate(

org.springframework.jdbc.core.simple.SimpleJdbcTemplate simpleJdbcTemplate) {

this.simpleJdbcTemplate = simpleJdbcTemplate;

}

public void addUser(final User user) {

MapuserMap = 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) {

MapuserMap = BeanToMapUtil.beanToMap(user);

getSimpleJdbcTemplate().update(UPDATE, userMap);

}

public ListgetAllUser() {

return getSimpleJdbcTemplate().query(SELECT, new BeanPropertyRowMapper(User.class));

}

public User getUserById(User user) {

return getSimpleJdbcTemplate().queryForObject(GET, new BeanPropertyRowMapper(User.class),user.getId());

}

publ

相关标签：

本文原创发布php中文网，转载请注明出处，感谢您的尊重！