首页 > 代码库 > MyBatis笔记----mybatis分页
MyBatis笔记----mybatis分页
mybatis版本3.4以下
结构
spring-mvc.xml
<?xml version="1.0" encoding="UTF-8"?><beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd"><!-- 自动扫描加载注解的包 --><context:component-scan base-package="com.ij34.bean"/><bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="prefix" value="/WEB-INF/view/"></property><property name="suffix" value=".jsp" ></property></bean></beans>
com.ij34.mybatis
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd" default-autowire="byName" default-lazy-init="false"> <!-- Showcase‘s CustomFreemarkerManager example --> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://localhost:3306/mybatis"></property> <property name="username" value="root"></property> <property name="password" value="123456"></property> </bean> <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"></property> <property name="configLocation" value="classpath:com/ij34/mybatis/mybatis-config.xml"></property> <property name="mapperLocations" value="classpath:com/ij34/mybatis/UserMapper.xml"></property> </bean><bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.ij34.model"></property> <property name="sqlSessionFactory" ref="sqlSessionFactory" /> </bean></beans>
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration><typeAliases><typeAlias type="com.ij34.model.Article" alias="Article"/><typeAlias type="com.ij34.model.User" alias="User"/><typeAlias type="com.ij34.pages.PageInfo" alias="PageInfo"/></typeAliases> <plugins> <plugin interceptor="com.ij34.pages.PagePlugin"> <property name="dialect" value="mysql" /> <property name="pageSqlId" value=".*ListPage.*" /> </plugin> </plugins></configuration>
UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.ij34.model.UserMapper"><resultMap type="Article" id="resultAticleList"> <id property="id" column="aid"/> <result property="title" column="title"/> <result property="content" column="content"/> <association property="user" javaType="User"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="age" column="age"/> </association> </resultMap> <select id="selectarticle" parameterType="int" resultMap="resultAticleList"> select users.id,users.name,users.age,article.id aid,article.title,article.content from users,article where users.id=article.userid and users.id=#{id} </select> <select id="ListPage" resultMap="resultAticleList"> select users.id,users.name,users.age,article.id aid,article.title,article.content from users,article where users.id=article.userid and users.id=#{userid} </select> </mapper>
com.ij34.model
User.java
package com.ij34.model;public class User { private int id; private String name; private int age; public int getId() { return id;}public void setId(int id) { this.id = id;}public String getName() { return name;}public void setName(String name) { this.name = name;}public int getAge() { return age;}public void setAge(int age) { this.age = age;}public String toString() { return "User [id=" + id + ", name=" + name + ", age=" + age + "]";}}
Article.java
package com.ij34.model;public class Article { private int id; private User user; private String title; private String content; public String getContent() { return content;}public void setContent(String content) { this.content = content;}public int getId() { return id;}public void setId(int id) { this.id = id;}public User getUser() { return user;}public void setUser(User user) { this.user = user;}public String getTitle() { return title;}public void setTitle(String title) { this.title = title;} }
UserMapper.java
package com.ij34.model;import java.util.List;import org.apache.ibatis.annotations.Param;import com.ij34.pages.PageInfo;public interface UserMapper { public List<Article> selectarticle(int id); public List<Article> ListPage(@Param("page") PageInfo page,@Param("userid") int userid);}
com.ij34.pages
参考网上的分页插件
PageInfo.java
package com.ij34.pages;import java.io.Serializable;public class PageInfo implements Serializable { private static final long serialVersionUID = 587754556498974978L; //pagesize ，每一页显示多少 private int showCount = 9; //总页数 private int totalPage; //总记录数 private int totalResult; //当前页数 private int currentPage; //当前显示到的ID, 在mysql limit 中就是第一个参数. private int currentResult; private String sortField; private String order; public int getShowCount() { return showCount; } public void setShowCount(int showCount) { this.showCount = showCount; } public int getTotalPage() { return totalPage; } public void setTotalPage(int totalPage) { this.totalPage = totalPage; } public int getTotalResult() { return totalResult; } public void setTotalResult(int totalResult) { this.totalResult = totalResult; } public int getCurrentPage() { return currentPage; } public void setCurrentPage(int currentPage) { this.currentPage = currentPage; } public int getCurrentResult() { return currentResult; } public void setCurrentResult(int currentResult) { this.currentResult = currentResult; } public String getSortField() { return sortField; } public void setSortField(String sortField) { this.sortField = sortField; } public String getOrder() { return order; } public void setOrder(String order) { this.order = order; } }
PagePlugin.java
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
要用3.4以下版本，可以参考官方
package com.ij34.pages;import java.lang.reflect.Field;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Map;import java.util.Properties;import javax.xml.bind.PropertyException;import org.apache.ibatis.executor.ErrorContext;import org.apache.ibatis.executor.ExecutorException;import org.apache.ibatis.executor.statement.BaseStatementHandler;import org.apache.ibatis.executor.statement.RoutingStatementHandler;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.mapping.ParameterMapping;import org.apache.ibatis.mapping.ParameterMode;import org.apache.ibatis.plugin.Interceptor;import org.apache.ibatis.plugin.Intercepts;import org.apache.ibatis.plugin.Invocation;import org.apache.ibatis.plugin.Plugin;import org.apache.ibatis.plugin.Signature;import org.apache.ibatis.reflection.MetaObject;import org.apache.ibatis.reflection.property.PropertyTokenizer;import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.type.TypeHandler;import org.apache.ibatis.type.TypeHandlerRegistry;@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })public class PagePlugin implements Interceptor { private static String dialect = ""; private static String pageSqlId = ""; @SuppressWarnings("unchecked") public Object intercept(Invocation ivk) throws Throwable { if (ivk.getTarget() instanceof RoutingStatementHandler) { RoutingStatementHandler statementHandler = (RoutingStatementHandler) ivk .getTarget(); BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper .getValueByFieldName(statementHandler, "delegate"); MappedStatement mappedStatement = (MappedStatement) ReflectHelper .getValueByFieldName(delegate, "mappedStatement"); if (mappedStatement.getId().matches(pageSqlId)) { BoundSql boundSql = delegate.getBoundSql(); Object parameterObject = boundSql.getParameterObject(); if (parameterObject == null) { throw new NullPointerException("parameterObject error"); } else { Connection connection = (Connection) ivk.getArgs()[0]; String sql = boundSql.getSql(); String countSql = "select count(0) from (" + sql + ") myCount"; System.out.println("总数sql 语句:"+countSql); PreparedStatement countStmt = connection .prepareStatement(countSql); BoundSql countBS = new BoundSql( mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), parameterObject); setParameters(countStmt, mappedStatement, countBS, parameterObject); ResultSet rs = countStmt.executeQuery(); int count = 0; if (rs.next()) { count = rs.getInt(1); } rs.close(); countStmt.close(); PageInfo page = null; if (parameterObject instanceof PageInfo) { page = (PageInfo) parameterObject; page.setTotalResult(count); } else if(parameterObject instanceof Map){ Map<String, Object> map = (Map<String, Object>)parameterObject; page = (PageInfo)map.get("page"); if(page == null) page = new PageInfo(); page.setTotalResult(count); }else { Field pageField = ReflectHelper.getFieldByFieldName( parameterObject, "page"); if (pageField != null) { page = (PageInfo) ReflectHelper.getValueByFieldName( parameterObject, "page"); if (page == null) page = new PageInfo(); page.setTotalResult(count); ReflectHelper.setValueByFieldName(parameterObject, "page", page); } else { throw new NoSuchFieldException(parameterObject .getClass().getName()); } } String pageSql = generatePageSql(sql, page); System.out.println("page sql:"+pageSql); ReflectHelper.setValueByFieldName(boundSql, "sql", pageSql); } } } return ivk.proceed(); } private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException { ErrorContext.instance().activity("setting parameters") .object(mappedStatement.getParameterMap().getId()); List<ParameterMapping> parameterMappings = boundSql .getParameterMappings(); if (parameterMappings != null) { Configuration configuration = mappedStatement.getConfiguration(); TypeHandlerRegistry typeHandlerRegistry = configuration .getTypeHandlerRegistry(); MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject); for (int i = 0; i < parameterMappings.size(); i++) { ParameterMapping parameterMapping = parameterMappings.get(i); if (parameterMapping.getMode() != ParameterMode.OUT) { Object value; String propertyName = parameterMapping.getProperty(); PropertyTokenizer prop = new PropertyTokenizer(propertyName); if (parameterObject == null) { value = null; } else if (typeHandlerRegistry .hasTypeHandler(parameterObject.getClass())) { value = parameterObject; } else if (boundSql.hasAdditionalParameter(propertyName)) { value = boundSql.getAdditionalParameter(propertyName); } else if (propertyName .startsWith(ForEachSqlNode.ITEM_PREFIX) && boundSql.hasAdditionalParameter(prop.getName())) { value = boundSql.getAdditionalParameter(prop.getName()); if (value != null) { value = configuration.newMetaObject(value) .getValue( propertyName.substring(prop .getName().length())); } } else { value = metaObject == null ? null : metaObject .getValue(propertyName); } TypeHandler typeHandler = parameterMapping.getTypeHandler(); if (typeHandler == null) { throw new ExecutorException( "There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId()); } typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType()); } } } } private String generatePageSql(String sql, PageInfo page) { if (page != null && (dialect !=null || !dialect.equals(""))) { StringBuffer pageSql = new StringBuffer(); if ("mysql".equals(dialect)) { pageSql.append(sql); pageSql.append(" limit " + page.getCurrentResult() + "," + page.getShowCount()); } else if ("oracle".equals(dialect)) { pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from ("); pageSql.append(sql); pageSql.append(") tmp_tb where ROWNUM<="); pageSql.append(page.getCurrentResult() + page.getShowCount()); pageSql.append(") where row_id>"); pageSql.append(page.getCurrentResult()); } return pageSql.toString(); } else { return sql; } } public Object plugin(Object arg0) { // TODO Auto-generated method stub return Plugin.wrap(arg0, this); } public void setProperties(Properties p) { dialect = p.getProperty("dialect"); if (dialect ==null || dialect.equals("")) { try { throw new PropertyException("dialect property is not found!"); } catch (PropertyException e) { // TODO Auto-generated catch block e.printStackTrace(); } } pageSqlId = p.getProperty("pageSqlId"); if (dialect ==null || dialect.equals("")) { try { throw new PropertyException("pageSqlId property is not found!"); } catch (PropertyException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }}
ReflectHelper.java
package com.ij34.pages;import java.lang.reflect.Field;public class ReflectHelper { public static Field getFieldByFieldName(Object obj, String fieldName) { for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass .getSuperclass()) { try { return superClass.getDeclaredField(fieldName); } catch (NoSuchFieldException e) { } } return null; } public static Object getValueByFieldName(Object obj, String fieldName) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException { Field field = getFieldByFieldName(obj, fieldName); Object value = null; if(field!=null){ if (field.isAccessible()) { value = field.get(obj); } else { field.setAccessible(true); value = field.get(obj); field.setAccessible(false); } } return value; } public static void setValueByFieldName(Object obj, String fieldName, Object value) throws SecurityException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException { Field field = obj.getClass().getDeclaredField(fieldName); if (field.isAccessible()) { field.set(obj, value); } else { field.setAccessible(true); field.set(obj, value); field.setAccessible(false); } } }
com.ij34.bean
package com.ij34.bean;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.servlet.ModelAndView;import com.ij34.model.*;import com.ij34.pages.PageInfo;@Controller@RequestMapping("/article")public class Test { @Autowired UserMapper mapper; @RequestMapping("/list") public ModelAndView listall(HttpServletRequest request,HttpServletResponse response){ List<Article> articles=mapper.selectarticle(1); //制定视图，也就是list.jsp ModelAndView mav=new ModelAndView("list"); mav.addObject("articles",articles); return mav; } @RequestMapping("/pagelist") public ModelAndView pageList(HttpServletRequest request,HttpServletResponse response){ int currentPage = request.getParameter("page")==null?1:Integer.parseInt(request.getParameter("page")); int pageSize = 9; if (currentPage<=1){ currentPage =1; } int currentResult = (currentPage-1) * pageSize; System.out.println(request.getRequestURI()); System.out.println(request.getQueryString()); PageInfo page = new PageInfo(); page.setShowCount(pageSize); page.setCurrentResult(currentResult); List<Article> articles=mapper.ListPage(page,1); System.out.println(page); int totalCount = page.getTotalResult(); int lastPage=0; if (totalCount % pageSize==0){ lastPage = totalCount % pageSize; } else{ lastPage =1+ totalCount / pageSize; } if (currentPage>=lastPage){ currentPage =lastPage; } String pageStr = ""; pageStr=String.format("<a href=http://www.mamicode.com/"%s\">上一页</a> <a href=http://www.mamicode.com/"%s\">下一页</a>", request.getRequestURI()+"?page="+(currentPage-1),request.getRequestURI()+"?page="+(currentPage+1) ); //制定视图，也就是list.jsp ModelAndView mav=new ModelAndView("list"); mav.addObject("articles",articles); mav.addObject("pageStr",pageStr); return mav; }/* public ModelAndView show(){//@RequestParam 请求参数 List<Article> articles=mapper.selectarticle(1); ModelAndView mav=new ModelAndView("list"); mav.addObject("articles", articles); return mav; }*/}
WebContent
web.xml
<?xml version="1.0" encoding="UTF-8"?><web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5"> <display-name>mybatis_springmvc</display-name> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:com/ij34/mybatis/applicationContext.xml</param-value> </context-param> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <listener> <listener-class> org.springframework.web.context.ContextCleanupListener</listener-class> </listener> <servlet> <servlet-name>springDispatcherServlet</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:spring-mvc.xml</param-value> </init-param> </servlet> <servlet-mapping> <servlet-name>springDispatcherServlet</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping> <filter> <filter-name>characterEncodingfilter</filter-name> <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class> <init-param> <param-name>encoding</param-name> <param-value>utf8</param-value> </init-param> <init-param> <param-name>forceEncoding</param-name> <param-value>true</param-value> </init-param> </filter> <filter-mapping> <filter-name>characterEncodingfilter</filter-name> <url-pattern>*</url-pattern> </filter-mapping></web-app>
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body> <a href="article/list">不分页测试</a><br /><p /> <a href="article/pagelist">分页测试</a></body></html>
list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body><table><c:forEach var="article" items="${articles}"><tr><td>${article.id} |</td><td> ${article.title}|</td><td> ${article.content}|</td><td>${article.user}</td> </tr> </c:forEach></table><h4>${pageStr}</h4></body></html>
结果
MyBatis笔记----mybatis分页
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。