MyBatis 分頁插件的實現

MyBatis 分頁功能是有所欠缺的，需要手動也分頁SQL實現；當然也不是不可以解決，比如使用插件就可以很好的解決這個

先了解下 Mybatis 的 Mapper 執行過程: 我們通過映射器 Mapper 對資料庫進行增刪改操作時，Mapper 執行的過程是通過 Executor、StatementHandler、ParameterHandler 和ResultHandler 來完成對資料庫的操作和返回結果

Executor 代表執行器，由它來調度StatementHandler、ParameterHandler、ResultHandler等來執行對應的SQL。

StatementHandler 的作用是使用資料庫的Statement(PreparedStatement) 執行操作，是上面提到的四個對象的核心。

ParameterHandler 用於SQL對參數的處理。

ResultHandler是進行最後數據集(ResultSet)的封裝返回處理的。

前提

MyBatis 攔截器知識介紹

public interface Interceptor {

Object intercept(Invocation var1) throws Throwable;

Object plugin(Object var1);

void setProperties(Properties var1);

}

1

2

3

4

5

6

7

intercept方法是插件的核心方法，它有個Invocation類型的參數，通過這個參數可以反射調度原來對象的方法。

plugin方法的作用是給被攔截的對象生成一個代理對象並返回。

setProperties方法允許在plugin元素中配置所需參數。

分頁攔截器

思路：在Mapper 方法中傳入Page分頁對象，在攔截器中根據是否有分頁Page對象來判斷是否進行分頁

Page對象類

public class Pagination<T> {

// 開始頁(當前頁)

private int startPage;

// 每頁的記錄數

private int pageSize;

// 總記錄數

private int totalRecord;

// 總頁數

private int totalPage;

// 每頁的數據集合

private List<T> content;

public Pagination() {

super();

}

public Pagination(int startPage, int pageSize) {

super();

this.startPage = startPage;

this.pageSize = pageSize;

}

public Pagination(int startPage, int pageSize, int totalRecord, List<T> content) {

super();

this.startPage = startPage;

this.pageSize = pageSize;

this.totalRecord = totalRecord;

this.content = content;

this.totalPage = this.totalRecord > 0 && this.pageSize > 0 ? (this.totalRecord % this.pageSize == 0 ? this.totalRecord / this.pageSize: this.totalRecord / this.pageSize + 1) : 0;

}

public int getStartPage() {

return startPage;

}

public int getPageSize() {

return pageSize;

}

public void setPageSize(int pageSize) {

this.pageSize = pageSize;

}

public int getTotalRecord() {

return totalRecord;

}

public void setTotalRecord(int totalRecord) {

this.totalPage = this.totalRecord > 0 && this.pageSize > 0 ? (this.totalRecord % this.pageSize == 0 ? this.totalRecord / this.pageSize: this.totalRecord / this.pageSize + 1) : 0;

this.totalRecord = totalRecord;

}

public int getTotalPage() {

return totalPage;

}

public List<T> getContent() {

return content;

}

public void setContent(List<T> content) {

this.content = content;

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

Dialect 方言類

不同的資料庫分頁的SQL語句是不一樣的,這裡使用方言的形式來支持不同的資料庫,只需要在配置攔截器的時候配置一個dialect參數即可

Dialect介面

public interface Dialect {

enum DB{

MYSQL

}

// 構建Count的SQL語句

public String buildCountSQL(String originalSQL);

// 構建分頁的SQL語句

public String buildPageSQL(String originalSQL, Integer startPage, Integer pageSize);

}

1

2

3

4

5

6

7

8

9

10

11

MySQL方言的實現

public class MySQLDialect implements Dialect {

@Override

public String buildCountSQL(String originalSQL) {

StringBuilder sb = new StringBuilder();

sb.append("SELECT COUNT(1) AS TOTAL FROM ( ").append(originalSQL).append(" ) AS T ");

return sb.toString();

}

@Override

public String buildPageSQL(String originalSQL, Integer startPage, Integer pageSize) {

StringBuilder sb = new StringBuilder();

sb.append(originalSQL).append(" LIMIT ").append(startPage).append(" , ").append(pageSize);

return sb.toString();

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

Dialect工廠類

用於根據不同的參數獲取不同的方言

public class DialectFactory {

private static Map<String, Dialect> DIALECTS = new HashMap<String, Dialect>();

public static Dialect buildDialect(String type) {

if(type == null || type.length() == 0) {

return null;

}

if(type.equals(Dialect.DB.MYSQL.name())) {

Dialect dialect = DIALECTS.get(type);

if(dialect == null) {

dialect = new MySQLDialect();

DIALECTS.put(type, dialect);

}

return dialect;

}

return null;

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

分頁攔截器的實現

// 註解這裡攔截 StatementHandler

@Intercepts({

@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})

})

public class PaginationInterceptor implements Interceptor {

private Logger logger = LoggerFactory.getLogger(getClass());

private String dialect;

@SuppressWarnings("rawtypes")

@Override

public Object intercept(Invocation invocation) throws Throwable {

Object target = invocation.getTarget();

// 如果方言不存在就給出警告

if(this.dialect == null || this.dialect.length() == 0) {

logger.warn("Dialect Not Found, Please set it at Configurtion");

return invocation.proceed();

}

Dialect dialect = DialectFactory.buildDialect(this.dialect);

if(dialect == null) {

logger.warn("Dialect {} is Not Support yet, MayBe {} is ok", this.dialect, Dialect.DB.MYSQL.toString());

return invocation.proceed();

}

if(StatementHandler.class.isAssignableFrom(target.getClass())) {

StatementHandler statementHandler = (StatementHandler) target;

MetaObject metaObject = SystemMetaObject.forObject(statementHandler);

// 分離代理對象鏈(由於目標類可能被多個攔截器攔截，從而形成多次代理，通過下面的兩次循環

// 可以分離出最原始的的目標類)

while(metaObject.hasGetter("h")) {

metaObject = SystemMetaObject.forObject(metaObject.getValue("h"));

}

// 分離最後一個代理對象的目標類

while(metaObject.hasGetter("target")) {

metaObject = SystemMetaObject.forObject(metaObject.getValue("target"));

}

//獲取查詢介面映射的相關信息

MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");

// 不是查詢語句

if(!mappedStatement.getSqlCommandType().equals(SqlCommandType.SELECT)) {

return invocation.proceed();

}

BoundSql boundSql = statementHandler.getBoundSql();

// 獲取分頁參數

Object paramObj = boundSql.getParameterObject();

Pagination<?> pagination = null;

if(Pagination.class.isAssignableFrom(paramObj.getClass())) {

pagination = (Pagination<?>) paramObj;

}else if(Map.class.isAssignableFrom(paramObj.getClass())) {

for(Object arg: ((Map)paramObj).values()) {

if(Pagination.class.isAssignableFrom(arg.getClass())) {

pagination = (Pagination<?>) arg;

break;

}

}

}

if(pagination == null || pagination.getPageSize() <= 0) {

return invocation.proceed();

}

String originalSQL = boundSql.getSql();

// 獲取總記錄數

Connection connection = (Connection) invocation.getArgs()[0];

String countSQL = dialect.buildCountSQL(originalSQL);

this.queryTotal(countSQL, mappedStatement, paramObj, boundSql, connection, pagination);

// 構建分頁SQL

String pageSQL = dialect.buildPageSQL(originalSQL, pagination.getStartPage(), pagination.getPageSize());

logger.debug("Build Pagination SQL => {}", pageSQL);

/*

* <p> 禁用內存分頁 </p>

* <p> 內存分頁會查詢所有結果出來處理（這個很嚇人的），如果結果變化頻繁這個數據還會不準。</p>

*/

metaObject.setValue("delegate.boundSql.sql", pageSQL);

metaObject.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET);

metaObject.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT);

return invocation.proceed();

}

return invocation.proceed();

}

/**

* 獲取總記錄數

*/

private void queryTotal(String countSQL, MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql, Connection connection, Pagination<?> pagination) {

try (PreparedStatement preparedStatement = connection.prepareStatement(countSQL)){

DefaultParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);

parameterHandler.setParameters(preparedStatement);

int totalRecord = 0;

try(ResultSet resultSet = preparedStatement.executeQuery()){

if(resultSet.next()) {

totalRecord = resultSet.getInt(1);

}

}

pagination.setTotalRecord(totalRecord);

} catch (SQLException e) {

e.printStackTrace();

}

}

@Override

public Object plugin(Object target) {

if(StatementHandler.class.isAssignableFrom(target.getClass())) {

return Plugin.wrap(target, this);

}

return target;

}

@Override

public void setProperties(Properties properties) {

// 獲取方言的參數

this.dialect = properties.getProperty("dialect");

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

註冊攔截器

Spring Boot 方式

*在有 @Configuration 註解類中添加分頁插件的 Bean *

@Bean

public Interceptor paginationInterceptor() {

PaginationInterceptor interceptor = new PaginationInterceptor();

Properties properties = new Properties();

properties.setProperty("dialect", Dialect.DB.MYSQL.name());

interceptor.setProperties(properties);

return interceptor;

}

1

2

3

4

5

6

7

8

9

XML 的方式（兩種方式）

1.在 mybatis-config.xml 中添加插件

<configuration>

<plugins>

<plugin interceptor="com.mybatis.interceptors.PaginationInterceptor">

<property name="dialect" value="mysql" />

</plugin>

</plugins>

</configuration>

1

2

3

4

5

6

7

2.在spring配置文件中添加插件

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

...

<property name="plugins">

<list>

<bean class="com.mybatis.interceptors.PaginationInterceptor">

<property name="dialect" value="mysql"/>

</bean>

</list>

</property>

...

</bean>

1

2

3

4

5

6

7

8

9

10

11

使用

User對象

public class User {

private Long id;

private String userName;

private String email;

private String name;

...

}

1

2

3

4

5

6

7

8

9

10

11

12

13

UserMapper

public interface UserMapper {

// 返回的List

public List<User> findByPage(Pagination<User> page);

}

1

2

3

4

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"

"//ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="com.mybatis.mapper.UserMapper">

<select id="findByPage" resultType="com.mybatis.model.User">

SELECT * FROM USER

</select>

</mapper>

1

2

3

4

5

6

7

8

UserService 略

UserServiceImpl

@Service

public class UserServiceImpl implements UserService {

@Autowired

private UserMapper mapper;

@Override

public Pagination<User> findByPage(int startPage, int pageSize) {

Pagination<User> pagination = new Pagination<User>(startPage - 1, pageSize);

// 返回的是List

List<User> content = mapper.findByPage(pagination);

pagination.setContent(content);

return pagination;

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

至此分頁完成，基本能夠實現自動分頁

思考：

能否在Mapper中直接返回Page對象，正常的思路是不行的，為什麼呢？我們看下 Executor 就知道了。

public interface Executor {

ResultHandler NO_RESULT_HANDLER = null;

int update(MappedStatement ms, Object parameter) throws SQLException;

<E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, CacheKey cacheKey, BoundSql boundSql) throws SQLException;

<E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException;

<E> Cursor<E> queryCursor(MappedStatement ms, Object parameter, RowBounds rowBounds) throws SQLException;

List<BatchResult> flushStatements() throws SQLException;

void commit(boolean required) throws SQLException;

void rollback(boolean required) throws SQLException;

CacheKey createCacheKey(MappedStatement ms, Object parameterObject, RowBounds rowBounds, BoundSql boundSql);

boolean isCached(MappedStatement ms, CacheKey key);

void clearLocalCache();

void deferLoad(MappedStatement ms, MetaObject resultObject, String property, CacheKey key, Class<?> targetType);

Transaction getTransaction();

void close(boolean forceRollback);

boolean isClosed();

void setExecutorWrapper(Executor executor);

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

什麼是 Executor ？在上面也介紹過，執行器，貫穿整個SQL執行（整個插件），就是說從開始到拿到結果都是由 Executor 負責的,但它不負責將結果映射到 Mapper 所以你想在 Mapper 自定義一個返回值,就必須遵循 Executor 中的返回值;所以用插件的方法在 Mapper 中返回 Page 對象是不可能實現;

那怎麼解決呢 ？ 目前沒有好的解決辦法。

有一種變態的方法，就是重構 SqlSessionFactoryBean 利用反射覆蓋 Configuration 中的 MapperRegistry ,簡明的表達就是重構MapperRegistry、MapperProxy、MapperMethod 這個你就可以為所欲為了，哈哈哈

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！