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

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