浅入浅出MyBatis(10):分页查询

January 14th 2015  | Tags: MyBatis, orm

2015-01-14

该文章是《专题:浅入浅出MyBatis》系列文章的一篇。点击进入该专题目录

工具与环境:Intellij IDEA 14,JDK 1.7,MyBatis 3.2,MySQL 5.5。

本文以浅入浅出MyBatis(01):查询id为1的用户的信息为基础。

什么是分页(pagination)

例如在数据库的某个表里有1000条数据,我们每次只显示100条数据,在第1页显示第0到第99条,在第2页显示第100到199条,依次类推,这就是分页。

分页可以分为逻辑分页物理分页逻辑分页是我们的程序在显示每页的数据时,首先查询得到表中的1000条数据,然后成熟根据当前页的“页码”选出其中的100条数据来显示。

物理分页是程序先判断出该选出这1000条的第几条到第几条,然后数据库根据程序给出的信息查询出程序需要的100条返回给我们的程序。

创建blog表并插入数据

在之前,已经创建了user表,其中的数据有:

mysql> select * from user;
+----+--------+----------------+----------+
| id | name   | email          | password |
+----+--------+----------------+----------+
|  1 | letian | letian@111.com | 123      |
|  2 | xiaosi | xiaosi@111.com | 123      |
+----+--------+----------------+----------+
2 rows in set (0.00 sec)

现在新建一个blog表,

CREATE  TABLE `blog_db`.`blog` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `owner_id` INT NOT NULL ,
  `title` TEXT NOT NULL ,
  `content` TEXT NOT NULL ,
  PRIMARY KEY (`id`) )
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

并插入数据:

INSERT INTO `blog_db`.`blog` (`owner_id`, `title`, `content`) VALUES ('1', '标题1', '文本1');

......

最终如下:

mysql> SELECT * FROM blog_db.blog;
+----+----------+----------+----------+
| id | owner_id | title    | content  |
+----+----------+----------+----------+
|  1 |        1 | 标题1    | 文本1    |
|  2 |        1 | 标题2    | 文本2    |
|  3 |        1 | 标题3    | 文本3    |
|  4 |        1 | 标题4    | 文本4    |
|  5 |        1 | 标题5    | 文本5    |
|  6 |        2 | 标题21   | 文本21   |
+----+----------+----------+----------+
6 rows in set (0.00 sec)

下面,我们看一下如何对这些blog进行分页查询。

在letian.mybatis.bean下添加Blog.java

其内容如下:

package letian.mybatis.bean;

public class Blog {
    private int id;
    private int ownerId;
    private String title;
    private String content;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getOwnerId() {
        return ownerId;
    }

    public void setOwnerId(int ownerId) {
        this.ownerId = ownerId;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "id=" + id +
                ", ownerId=" + ownerId +
                ", title='" + title + '\'' +
                ", content='" + content + '\'' +
                '}' + '\n';
    }
}

在letian.mybatis.dao创建BlogMapper.java

其内容如下:

package letian.mybatis.dao;


import letian.mybatis.bean.Blog;

import java.util.HashMap;
import java.util.List;

public interface BlogMapper {

    List<Blog> findAll();
    List<Blog> findAll2(HashMap<String, Integer> offsetLimit);
}

在letian.mybatis.mapper创建BlogMapper.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="letian.mybatis.dao.BlogMapper">


    <resultMap id="findAllResult" type="letian.mybatis.bean.Blog">
        <result property="ownerId" column="owner_id"/>
    </resultMap>

    <select id="findAll" resultMap="findAllResult" resultType="letian.mybatis.bean.Blog">
        SELECT *
        FROM blog;
    </select>

    <select id="findAll2" parameterType="HashMap" resultMap="findAllResult" resultType="letian.mybatis.bean.Blog">
        SELECT *
        FROM blog LIMIT #{offset}, #{limit};
    </select>

</mapper>

然后将BlogMapper.xml加入mybatis的配置文件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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/blog_db?useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="letian/mybatis/mapper/UserMapper.xml"/>
        <!--加入BlogMapper.xml-->
        <mapper resource="letian/mybatis/mapper/BlogMapper.xml"/> 
    </mappers>

</configuration>

修改Main.java

将Main.java内容修改为:

import java.io.IOException;
import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import letian.mybatis.bean.User;
import letian.mybatis.bean.Blog;
import letian.mybatis.dao.UserMapper;
import letian.mybatis.dao.BlogMapper;


public class Main {

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sessionFactory;
        sessionFactory = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsReader("mybatis-config.xml"));

        SqlSession sqlSession = sessionFactory.openSession();

        List<Blog> blogs;

        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        blogs = blogMapper.findAll();  //!
        System.out.println(blogs+"\n");

        int offset = 0;
        int limit = 2;
        RowBounds rowBounds = new RowBounds(offset, limit);
        blogs = sqlSession.selectList("letian.mybatis.dao.BlogMapper.findAll", new Object(), rowBounds); //!
        System.out.println(blogs+"\n");

        HashMap<String, Integer> offsetLimit = new HashMap<String, Integer>();
        offsetLimit.put("offset", 0);
        offsetLimit.put("limit", 3);
        blogs = blogMapper.findAll2(offsetLimit); //!
        System.out.println(blogs+"\n");

        sqlSession.close();
    }
}

其中:

blogs = blogMapper.findAll();

是获取所有的blog信息。

blogs = sqlSession.selectList("letian.mybatis.dao.BlogMapper.findAll", new Object(), rowBounds);

是逻辑分页,即在获取所有blog信息的基础上得到offset为0、limit为2的2条blog的信息。

blogs = blogMapper.findAll2(offsetLimit);

是物理分页。findAll2方法通过offsetLimit将offset和limit信息传递给下面的sql语句:

SELECT *
FROM blog LIMIT #{offset}, #{limit};

。mysql本身实现分页查询。

执行结果

执行Main.java,结果如下:

[Blog{id=1, ownerId=1, title='标题1', content='文本1'}
, Blog{id=2, ownerId=1, title='标题2', content='文本2'}
, Blog{id=3, ownerId=1, title='标题3', content='文本3'}
, Blog{id=4, ownerId=1, title='标题4', content='文本4'}
, Blog{id=5, ownerId=1, title='标题5', content='文本5'}
, Blog{id=6, ownerId=2, title='标题21', content='文本21'}
]

[Blog{id=1, ownerId=1, title='标题1', content='文本1'}
, Blog{id=2, ownerId=1, title='标题2', content='文本2'}
]

[Blog{id=1, ownerId=1, title='标题1', content='文本1'}
, Blog{id=2, ownerId=1, title='标题2', content='文本2'}
, Blog{id=3, ownerId=1, title='标题3', content='文本3'}
]

补充:通过插件实现Mysql的物理分页查询

MyBatis 物理分页这篇文章中给出了一个基于MyBatis插件的物理分页查询的实现,其思路是捕获sql语句,为sql语句补全limit和offset信息。如果你熟悉MyBatis的插件机制,可以自己实现一个。

(完)