MyBatis3.1.1+Spring3.1.1的增、删、查、改以及分页和事务管理

jopen 8年前

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    


来自: http://my.oschina.net/jlu100/blog/603899