1. [代码]Mybatis全局配置文件
|
1
2
3
|
<plugins>< plugin interceptor = "com.has.core.page.PaginationInterceptor" /></plugins> |
2. [文件] PaginationInterceptor.java
|
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
|
@Intercepts ({ @Signature (type = StatementHandler. class , method = "prepare" , args = { Connection. class }) })public class PaginationInterceptor implements Interceptor {@Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler statementHandler = (StatementHandler) invocation.getTarget();BoundSql boundSql = statementHandler.getBoundSql();MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue( "delegate.rowBounds" );if (rowBounds == null || rowBounds == RowBounds.DEFAULT) {return invocation.proceed();}Configuration configuration = (Configuration) metaStatementHandler.getValue( "delegate.configuration" );Dialect.Type databaseType = null ;try {databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty( "dialect" ).toUpperCase());} catch (Exception e) {}if (databaseType == null ) {throw new RuntimeException( "the value of the dialect property in configuration.xml is not defined : "+ configuration.getVariables().getProperty( "dialect" ));}Dialect dialect = null ;switch (databaseType) {case MYSQL:dialect = new MySql5Dialect();break ;case ORACLE:dialect = new OracleDialect();break ;}String originalSql = (String) metaStatementHandler.getValue( "delegate.boundSql.sql" );metaStatementHandler.setValue( "delegate.boundSql.sql" ,dialect.getLimitString(originalSql, rowBounds.getOffset(), rowBounds.getLimit()));metaStatementHandler.setValue( "delegate.rowBounds.offset" , RowBounds.NO_ROW_OFFSET);metaStatementHandler.setValue( "delegate.rowBounds.limit" , RowBounds.NO_ROW_LIMIT);return invocation.proceed();}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this );}@Overridepublic void setProperties(Properties properties) {}} |
3. [文件] Dialect.java
|
1
2
3
4
5
6
7
8
9
10
|
/*** 数据库方言定义**/public abstract class Dialect {public static enum Type {MYSQL, ORACLE}public abstract String getLimitString(String sql, int skipResults, int maxResults);} |
4. [文件] MySql5Dialect.java
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
/*** mysql方言分页类*/public class MySql5Dialect extends Dialect {protected static final String SQL_END_DELIMITER = ";" ;public String getLimitString(String sql, boolean hasOffset) {return MySql5PageHepler.getLimitString(sql, - 1 , - 1 );}public String getLimitString(String sql, int offset, int limit) {return MySql5PageHepler.getLimitString(sql, offset, limit);}public boolean supportsLimit() {return true ;}} |
5. [文件] OracleDialect.java
|
1
2
3
4
5
6
7
8
9
10
11
|
package com.chyjr.has.core.page.dialect;/*** oracel方言分页* */public class OracleDialect extends Dialect {public String getLimitString(String sql, int offset, int limit) {// TODO 未实现return "";}} |
6. [文件] MySql5PageHepler.java
|
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
|
import java.util.regex.Matcher;import java.util.regex.Pattern;/*** mysql分页工具类*/public class MySql5PageHepler {/*** 得到查询总数的sql*/public static String getCountString(String querySelect) {querySelect = getLineSql(querySelect);int orderIndex = getLastOrderInsertPoint(querySelect);int formIndex = getAfterFormInsertPoint(querySelect);String select = querySelect.substring( 0 , formIndex);// 如果SELECT 中包含 DISTINCT 只能在外层包含COUNTif (select.toLowerCase().indexOf( "select distinct" ) != - 1|| querySelect.toLowerCase().indexOf( "group by" ) != - 1 ) {return new StringBuffer(querySelect.length()).append( "select count(1) count from (" ).append(querySelect.substring( 0 , orderIndex)).append( " ) t" ).toString();} else {return new StringBuffer(querySelect.length()).append( "select count(1) count " ).append(querySelect.substring(formIndex, orderIndex)).toString();}}/*** 得到最后一个Order By的插入点位置** @return 返回最后一个Order By插入点的位置*/private static int getLastOrderInsertPoint(String querySelect) {int orderIndex = querySelect.toLowerCase().lastIndexOf( "order by" );if (orderIndex == - 1 || !isBracketCanPartnership(querySelect.substring(orderIndex, querySelect.length()))) {throw new RuntimeException( "My SQL 分页必须要有Order by 语句!" );}return orderIndex;}/*** 得到分页的SQL** @param offset* 偏移量* @param limit* 位置* @return 分页SQL*/public static String getLimitString(String querySelect, int offset, int limit) {querySelect = getLineSql(querySelect);// String sql = querySelect.replaceAll("[^\\s,]+\\.", "") + " limit " +// offset + " ," + limit;String sql = querySelect + " limit " + offset + " ," + limit;return sql;}/*** 将SQL语句变成一条语句，并且每个单词的间隔都是1个空格** @param sql* SQL语句* @return 如果sql是NULL返回空，否则返回转化后的SQL*/private static String getLineSql(String sql) {return sql.replaceAll( "[\r\n]" , " " ).replaceAll( "\\s{2,}" , " " );}/*** 得到SQL第一个正确的FROM的的插入点*/private static int getAfterFormInsertPoint(String querySelect) {String regex = "\\s+FROM\\s+" ;Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);Matcher matcher = pattern.matcher(querySelect);while (matcher.find()) {int fromStartIndex = matcher.start( 0 );String text = querySelect.substring( 0 , fromStartIndex);if (isBracketCanPartnership(text)) {return fromStartIndex;}}return 0 ;}/*** 判断括号"()"是否匹配,并不会判断排列顺序是否正确** @param text* 要判断的文本* @return 如果匹配返回TRUE,否则返回FALSE*/private static boolean isBracketCanPartnership(String text) {if (text == null || (getIndexOfCount(text, '(' ) != getIndexOfCount(text, ')' ))) {return false ;}return true ;}/*** 得到一个字符在另一个字符串中出现的次数** @param text* 文本* @param ch* 字符*/private static int getIndexOfCount(String text, char ch) {int count = 0 ;for ( int i = 0 ; i < text.length(); i++) {count = (text.charAt(i) == ch) ? count + 1 : count;}return count;}} |
2. [图片] Mybatis.jpg
