MyBatis3.1.1+Spring3.1.1的增、删、查、改以及分页和事务管理
jopen
9年前
1. [代码]Mybatis全局配置文件
<plugins>
< plugin interceptor = "com.has.core.page.PaginationInterceptor" /> </plugins>
2. [文件] PaginationInterceptor.java
@Intercepts ({ @Signature (type = StatementHandler. class , method = "prepare" , args = { Connection. class }) }) public class PaginationInterceptor implements Interceptor { @Override public 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(); } @Override public Object plugin(Object target) { return Plugin.wrap(target, this ); } @Override public void setProperties(Properties properties) { } }
3. [文件] Dialect.java
/** * 数据库方言定义 * */ public abstract class Dialect { public static enum Type { MYSQL, ORACLE } public abstract String getLimitString(String sql, int skipResults, int maxResults); } 4. [文件] MySql5Dialect.java
/** * 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
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
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 只能在外层包含COUNT if (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
