Spring与Mybatis框架整合——数据分页

1.创建结构如下图的Maven项目

2.引入所需jar包的依赖坐标

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>   <!-- 工程模型版本 -->

    <!-- 项目工程的坐标 : 确定不同项目的说明 -->
    <groupId>com.yc</groupId>
    <artifactId>spring-02-spring-mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  <!-- 设置项目的编码集 -->
        <junit.version>4.12</junit.version>
        <log4j.version>2.5</log4j.version>
        <spring.version>4.3.2.RELEASE</spring.version>
        <spring-jdbc.version>4.3.2.RELEASE</spring-jdbc.version>
        <spring-test.version>4.3.2.RELEASE</spring-test.version>
        <mybatis.version>3.4.1</mybatis.version>
        <oracle.version>12.1.0.1</oracle.version>
        <mybatis-spring.version>1.3.0</mybatis-spring.version>
        <commons-dbcp2.version>2.1.1</commons-dbcp2.version>
    </properties>

    <!--依赖 引入第三方的操作 -->
    <dependencies>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
            <scope>runtime</scope>
        </dependency>


        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring-jdbc.version}</version>
        </dependency>

        <!-- 第三方的数据库连接池框架 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>${commons-dbcp2.version}</version>
        </dependency>

        <!-- mybatis与spring整合包 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>${mybatis-spring.version}</version>
            <scope>runtime</scope>
        </dependency>

        <!-- mybatis框架包 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>${mybatis.version}</version>
            <scope>runtime</scope>
        </dependency>

        <!-- orcal数据库驱动包 -->
        <dependency>
            <groupId>com.hynnet</groupId>
            <artifactId>oracle-driver-ojdbc6</artifactId>
            <version>${oracle.version}</version>
            <scope>runtime</scope>
            <exclusions>
                <exclusion>
                    <artifactId>slf4j-api</artifactId>
                    <groupId>org.slf4j</groupId>
                </exclusion>
            </exclusions>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>${log4j.version}</version>
        </dependency>

        <!-- 引入spring 框架 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>




    </dependencies>

    <!-- 指定上传的位置 -->
    <distributionManagement>
        <repository>
            <id>nexus-releases</id>
            <name>Nexus Releases Repositories</name>
            <url>http://localhost:8081/nexus/content/repositories/releases</url>
        </repository>

        <snapshotRepository>
            <id>nexus-snapshots</id>
            <name>Nexus Snapshots Repositories</name>
            <url>http://localhost:8081/nexus/content/repositories/snapshots</url>
        </snapshotRepository>
    </distributionManagement>

</project>

3.配置文件如下
数据库连接:db.properties

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.username=mybatis26(用户名)
jdbc.password=a（密码）
jdbc.maxActive =5
jdbc.initialSize =10
jdbc.maxTotal=100
jdbc.minIdle=5
jdbc.validationQuery =select 1 from dual

日志记录:log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<Configuration>
    <Appenders>
        <Console name="STDOUT" target="SYSTEM_OUT">
            <PatternLayout pattern="%d %-5p %C{1} (%F:%L) - %m%n"/>
        </Console>
    </Appenders>
    <Loggers>
        <Logger name="com.yc.spring" level="debug"/>
        <Logger name="org.apache.ibatis.logging.jdbc" level="debug"/>
        <Root level="error">
            <AppenderRef ref="STDOUT"/>
        </Root>
    </Loggers>
</Configuration>

spring框架配置文件spring.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:p="http://www.springframework.org/schema/p"
    xmlns:util="http://www.springframework.org/schema/util" xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/util 
        http://www.springframework.org/schema/util/spring-util.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop 
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd">

    <!-- 扫描bean包 -->
    <!-- bean就由spring容器创建好的对象 -->
    <!-- 指定可以做为spring容器管理的对象的包 -->
    <context:component-scan base-package="com.yc.spring" />

    <!-- 引入外部属性文件 连接数据库 -->
    <context:property-placeholder location="classpath:db.properties" />



    <!-- 数据源 -->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
        <property name="initialSize" value="${jdbc.initialSize}" />
        <property name="maxTotal" value="${jdbc.maxTotal}" />
        <property name="minIdle" value="${jdbc.minIdle}" />
        <property name="validationQuery" value="${jdbc.validationQuery}" />
    </bean>

    <!-- 配制mybatis的会话工厂对象 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" /><!-- 注入数据源 对象 -->
        <property name="typeAliasesPackage" value="com.yc.spring.entity" /><!-- 
            注入别名定义所在的包 -->
            <!-- 也能链接mybatis.xml文件,像别名这样的设置可以放在mybatis中 -->
        <property name="mapperLocations" value="classpath:mapper/*Mapper.xml"></property><!-- 注入注册映射文件 -->
    </bean>

    <!-- 配制映射接口的代理实现类的bean,bean的名称为映射接口名首字母小写 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.yc.spring.mapper"></property>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>

</beans>

4.创建实体类
User.java

package com.yc.spring.entity;

import java.io.Serializable;

public class User  implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 2561775154643522215L;
    private Integer id;
    private String name;
    private String birthday;
    private String gender;
    private String career;
    private String address;
    private String mobile;
    private String picPath;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getBirthday() {
        return birthday;
    }
    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }
    public String getGender() {
        return gender;
    }
    public void setGender(String gender) {
        this.gender = gender;
    }
    public String getCareer() {
        return career;
    }
    public void setCareer(String career) {
        this.career = career;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getMobile() {
        return mobile;
    }
    public void setMobile(String mobile) {
        this.mobile = mobile;
    }
    public String getPicPath() {
        return picPath;
    }
    public void setPicPath(String picPath) {
        this.picPath = picPath;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", birthday=" + birthday + ", gender=" + gender + ", career="
                + career + ", address=" + address + ", mobile=" + mobile + ", picPath=" + picPath + "]";
    }
    public User( String name, String birthday, String gender, String career, String address, String mobile,
            String picPath) {

        this.name = name;
        this.birthday = birthday;
        this.gender = gender;
        this.career = career;
        this.address = address;
        this.mobile = mobile;
        this.picPath = picPath;
    }
    public User() {
    }   
}

PaginationBean.java

package com.yc.spring.entity;

import java.util.List;

/**
 * 通用分页bean
 * @author 7G-5HI7
 *
 * @param <T>
 */
public class PaginationBean<T> {
    // 请求参数(有默认值)
    private Integer currPage =1 ;// 当前页
    private Integer pageSize=10;// 每页的数据条数

    // 响应数据
    private Integer total;// 数据的总条数
    private List<T> rows;// 当前分页的数据
    /* 以上参数不能变,ui内置参数 */

    private Integer totalPage;// 总页数

    public Integer getCurrPage() {
        return currPage;
    }

    public void setCurrPage(Integer currPage) {
        this.currPage = currPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotal() {
        return total;
    }

    public void setTotal(Integer total) {
        this.total = total;
    }

    public List<T> getRows() {
        return rows;
    }

    public void setRows(List<T> rows) {
        this.rows = rows;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }

    @Override
    public String toString() {
        return "PaginationBean [currPage=" + currPage + ", pageSize=" + pageSize + ", total=" + total + ", \nrows=" + rows
                + ", totalPage=" + totalPage + "]";
    }

    public PaginationBean(Integer currPage, Integer pageSize, Integer total, List<T> rows, Integer totalPage) {
        this.currPage = currPage;
        this.pageSize = pageSize;
        this.total = total;
        this.rows = rows;
        this.totalPage = totalPage;
    }

    public PaginationBean() {
    }
}

UsersBean.java

package com.yc.spring.entity;

import java.io.Serializable;
import java.util.List;

public class UsersBean  implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = -541475228302438374L;
    private int currPage ;//当前页面

    private int totalPage ;//页面总数
    private  List<User> rows;
    private int total;//总记录数

    public int getTotal() {
        return total;
    }

    public UsersBean(int currPage, int totalPage, List<User> rows, int total) {
        this.currPage = currPage;
        this.totalPage = totalPage;
        this.rows = rows;
        this.total = total;
    }
    public void setTotal(int total) {
        this.total = total;
    }


    public UsersBean() {
        super();
    }


    public int getTotalPage() {
        return totalPage;
    }
    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
    @Override
    public String toString() {
        return "NewsBean [currPage=" + currPage + ", totalPage=" + totalPage + ", news=" + rows + "]";
    }
    public int getCurrPage() {
        return currPage;
    }
    public void setCurrPage(int currPage) {
        this.currPage = currPage;
    }
    public int gettotalPage() {
        return totalPage;
    }
    public void settotalPage(int totalPage) {
        this.totalPage = totalPage;
    }
    public List<User> getRows() {
        return rows;
    }
    public void setRows(List<User> rows) {
        this.rows = rows;
    }   
}

5.创建映射接口类
UserMapper.java

package com.yc.spring.mapper;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.yc.spring.entity.PaginationBean;
import com.yc.spring.entity.User;

public interface UserMapper {

    User findUser(User user); 

    List<User> getAllUser();

    /*这是MyBatis提供了一个使用注解来参入多个参数的方式。这种方式需要在接口的参数上添加@Param注解*/
    List<User> getPartNews(@Param("pageSize") int pageSize,@Param("currPage") int currPage);

    int getTotalPage(int pageSize);

    PaginationBean<User> getUsersByPagination(PaginationBean<User> userBean);

    int updateUser(User user);

    User getUserById(int id);
}

6.创建xml映射文件
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.yc.spring.mapper.UserMapper">

    <!-- 定义sql语句  变量 -->
    <sql id="selectProfile">
        select * from profile
    </sql>

    <select id="findUser" parameterType="User" resultType="User">
        <include refid="selectProfile"/>  where id=#{id} and name=#{name}
    </select>

    <select id="getUserById" parameterType="int" resultType="User" useCache="false">
        <include refid="selectProfile"/>   where id=#{id} 
    </select>


    <select id="getAllUser" resultType="User">
        <include refid="selectProfile"/>  
    </select>

    <select id="getTotalPage" resultType="int" parameterType="int">
        select ceil(count(1)/#{pageSize}) from profile
    </select>

    <select id="getPartNews" resultType="User" parameterType="int">
        select * from
        (select rownum rn,n.* from
        (<include refid="selectProfile"/>   order by 1 )n where ${pageSize}*${currPage} >= rownum)
        where rn>(${currPage}-1)*${pageSize}
    </select>


    <!-- 嵌套查询 -->
    <resultMap type="PaginationBean" id="PaginationBeanMap">

        <collection property="rows" column="{pageSize=pageSize,currPage=currPage}" select="getUsers"></collection>
    </resultMap>

    <!-- #是占位符(就是?) $是直接取到值 -->
    <select id="getUsersByPagination" parameterType="PaginationBean"
        resultMap="PaginationBeanMap">
        select count(1) total, ceil(count(1)/#{pageSize}) totalPage ,
        ${pageSize} pageSize,${currPage} currPage from profile
    </select>

    <select id="getUsers" resultType="User">
        select * from
        (select rownum rn,n.* from
        (<include refid="selectProfile"/>   order by 1 desc )n where ${pageSize}*${currPage} >=
        rownum)
        where rn>(${currPage}-1)*${pageSize}
    </select>

    <!-- 只能有传入参数,不能写传出参数 -->
    <update id="updateUser" parameterType="User" flushCache="false"><!-- 不清除缓存 -->
        update profile set
        name=#{name} ,birthday=#{birthday},career=#{career},address=#{address} ,mobile=#{mobile}
        <if test="picPath != null">,picPath=#{picPath}</if>
        where id=#{id}
    </update>
</mapper>