Mybatis 的分页查询与主键值回填

一、分页查询

数据准备：

SELECT * FROM dept_bak;

insert into dept_bak values(60,'开发部','深圳');
insert into dept_bak values(70,'产品部','上海');
insert into dept_bak values(80,'运维部','北京');
insert into dept_bak values(90,'实施部','杭州');
insert into dept_bak values(100,'销售部','成都');
insert into dept_bak values(110,'人事部','南京');

SELECT * FROM dept_bak;

1、方式一：使用Mybatis的RowBounds

1.1 DeptMapper.java

public interface DeptMapper {
    // Mybatis中的RowBounds类实现分页查询
    List<Dept> selectByRowBounds(RowBounds rowBounds);
}

1.2 DeptMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
	PUBLIC "-//mybatis.org//DTDMapper3.0//EN"
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--命名空间的值必须与DAO接口的全限定名一致-->
<mapper namespace="com.dyh.dao.DeptMapper">
	<resultMap type="dept" id="resultMapDept">
		<id property="deptno" column="deptno" />
		<result property="dname" column="dname" />
		<result property="city" column="loc" />
	</resultMap>

	<sql id="deptUseColumns">
		deptno,dname,loc as city
	</sql>
	<!-- 采用Mybatis中的RowBouds实现分页查询，注意是一次性查询出整张表的数据然后在内存操作 -->
	<select id="selectByRowBounds" resultType="dept" >
		select <include refid="deptUseColumns" /> from dept_bak
	</select>
</mapper>

1.3 JUnit单元测试TestPage.java

package com.dyh.test;

import com.dyh.dao.DeptMapper;
import com.dyh.pojo.Dept;
import com.dyh.util.MybatisUtil;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.List;

public class TestPage {
    SqlSession connection;
    DeptMapper mapper;
    @Before
    public void  init(){
        connection = MybatisUtil.getConnection();
        mapper = connection.getMapper(DeptMapper.class);
    }

    @Test
    public void testPageByRowBounds(){
        // 每页3条记录，查询第二页  ,注意：此种方法的弊端
        // 他是一次性取出所有数据，在内存中操作，当数据表数据量很大时
        // 不建议使用，IO会很大，内存消耗很大，拖慢系统性能
        // RowBounds rowBounds = new RowBounds(3,3);
        RowBounds rowBounds = new RowBounds(6,3);
        List<Dept> list = mapper.selectByRowBounds(rowBounds);
        list.forEach(System.out::println);
    }

    @After
    public void destroy(){
        MybatisUtil.closConnection();
    }
}

2、方式二：使用SQL语句实现查询

2.1 Oracle中分页查询SQL

-- 每页三条记录，查询第二页 
-- Oracle数据库中有个rownum伪列，不能使用> 

-- 方式一：使用between and结合子查询
select * from (
  SELECT ROWNUM as rn,deptno,dname,loc from dept_bak
) t
where t.rn BETWEEN 7 and 9
-- 4=3×1+1,6=3×2
-- 查询第n页（每页m条记录）
-- between m(n-1)+1 and mn

-- 方式二：使用rownum+子查询
select t.*
from (
	SELECT ROWNUM as rn,deptno,dname,loc from dept_bak
	where rownum<=3*2
) t where rn>3
-- 每页limit : m条记录，查询第pageIndex : n页
select t.*
from (
	SELECT ROWNUM as rn,deptno,dname,loc from dept_bak
	where rownum<=m*n
) t where rn>m*(n-1)

2.2 DeptMapper.java

DeptMapper.java中增加如下代码：

/**
     * @param records       每页的记录条数
     * @param pageIndex     第几页
     * @return              返回集合
     */
    List<Dept> selectBySQL(
            @Param(value="records") int records,
            @Param(value="pageIndex") int pageIndex);

2.3 DeptMapper.xml

DeptMapper.xml增加如下代码：

<!-- 采用SQL语句实现分页查询  Oracle中分页查询实现 -->
	<!-- between m(n-1)+1 and mn-->
	<select id="selectBySQL" resultType="dept" >
		select <include refid="deptUseColumns" /> from (
			SELECT ROWNUM as rn,deptno,dname,loc from dept_bak
		) t
		where t.rn BETWEEN #{records}*(#{pageIndex}-1)+1 and #{records}*#{pageIndex}
	</select>

2.4 JUnit单元测试TestPage.java

在TestPage.java类中增加如下代码：

 @Test
    public void testPageBySQL(){
        SqlSession connection = MybatisUtil.getConnection();
        DeptMapper mapper = connection.getMapper(DeptMapper.class);
        List<Dept> list = mapper.selectBySQL(2,3);
        list.forEach(System.out::println);
    }

2.5 其它数据库分页查询SQL

MySQL中分页查询使用limit 偏移量,每页多少条记录

SQL Server分页查询使用top+子查询，top是查询前多少条记录。

二、主键值回填

应用场景：下单，要往数据库中插入一条订单记录，插入成功后需要拿到订单编号。

Mybatis 中支持两种方法获取主键：

获取自增主键的值。 如：MySQL、 SqlServer

获取非自增主键的值。 如 Oracle

1、获取主键自增值

MYSQL数据库和SQLServer数据库。参考《Mybatis实战.docx》61-62页。

作业3尝试完成。

2、获取非主键自增值

Oracle数据库中的序列

drop sequence seq_dept;
create sequence seq_dept start with 90 INCREMENT by 1;
select seq_dept.nextval from dual

DeptMapper.java中增加如下代码：

 /**
     * 插入如数据并且拿到插入数据的主键
     * @param dept
     */
    void addDeptKeyReturn(Dept dept);

注意：上述方法不能有返回值，若返回整数类型，则返回的是插入成功的记录条数，而不是返回的插入的数据的主键。

DeptMapper.xml中增加如下代码：

<!-- 获取非主键自增值 -->
	<insert id="addDeptKeyReturn"  keyProperty="deptno">
		/*将查询序列获取到的值 赋值给deptno ，然后在插入的时候使用 */
		<selectKey keyProperty="deptno" keyColumn="deptno" resultType="int" order="BEFORE">
			select seq_dept.nextval from dual
		</selectKey>
		insert into dept_bak(deptno, dname, loc) values(#{deptno} , #{dname}, #{city})
	</insert>

注意：resultType="int"表示查询序列返回的结果为int类型。

测试

 @Test
    public void testAddDept(){
        // 插入时，对象没有主键
        Dept dept = new Dept(null, "财务部", "香港");
        mapper.addDeptKeyReturn(dept);  // 此处如果设置返回值，返回的是插入成功的记录条数
        // 插入成功后，对象的主键值，就已经有了，主键回填
        System.out.println(dept.getDeptno());
        session.commit();
    }