MyBatis笔记

●悲催的Bug
与spring结合时，org.mybatis.spring.mapper.MapperScannerConfigurer不支持 <context:property-placeholder />
(mybatis 3.1.1, mybatis-spring 1.1.1)
●结合Spring的配置
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="typeAliasesPackage" value="xx.xx.xx.entity" />
<property name="plugins">
<list>
<!-- 配置自己实现的分页插件 -->
<bean class="xx.xx.xx.mybatis.PagingPlugin">
<property name="dialect" value="mysql"/>
</bean>
</list>
</property>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="xx.xx.xx.dao" />
</bean>

●传递多个参数
//使用@Param来注解，例如：
List queryXX(@Param("arg1") int arg1, @Param("arg2") String arg2);

●分页插件。 有以下缺点：

• 暂时只实现mysql和oracle
  
• oracle未测试
  
• 未考虑取总数的性能
  
• 未考虑排序查找的结果集未能自动放到分页对象(Page)中
  

Page.java
public class Page{
private int limit = 20; //每页显示条数
private int start = 0;  //起始行号
private long total = -1; //总数
private List result = new ArrayList(); //结果集
//---- 省略get set ----//
}
PagingPlugin.java
/**
* Mybatis的分页查询插件，通过拦截StatementHandler的prepare方法来实现。
* 只有在参数列表中包括Page类型的参数时才进行分页查询。
* 在多参数的情况下，只对第一个Page类型的参数生效。
* 另外，在参数列表中，Page类型的参数无需用@Param来标注
* @author linzongxue 2012-1-16（修改）
*
*/
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class PagingPlugin implements Interceptor {
private String dialect;
@SuppressWarnings("unchecked")
@Override
public Object intercept(Invocation invocation) throws Throwable {
if(!(invocation.getTarget() instanceof RoutingStatementHandler))
return invocation.proceed();
RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget();
BoundSql boundSql = statementHandler.getBoundSql();
//分析是否含有分页参数，如果没有则不是分页查询
//注意：在多参数的情况下，只处理第一个分页参数
Page page = null;
Object paramObj = boundSql.getParameterObject();
if (paramObj instanceof Page){ //只有一个参数的情况
page = (Page)paramObj;
}
else if (paramObj instanceof Map){ //多参数的情况，找到第一个Page的参数
for (Map.Entry<String, Object> e : ((Map<String, Object>)paramObj).entrySet()){
if (e.getValue() instanceof Page){
page = (Page)e.getValue();
break;
}
}
}
if (page == null) return invocation.proceed();
//查找总记录数，并设置Page的相关参数
long total = this.getTotal(invocation);
page.setTotal(total);
//生成分页SQL
String pageSql = generatePageSql(boundSql.getSql(), page);
//强制修改最终要执行的SQL
setFieldValue(boundSql, "sql", pageSql);
return invocation.proceed();
}
/**
* 获取记录总数
*/
@SuppressWarnings("unchecked")
private long getTotal(Invocation invocation) throws Exception{
RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget();
BoundSql boundSql = statementHandler.getBoundSql();
/*
* 为了设置查找总数SQL的参数，必须借助MappedStatement、Configuration等这些类，
* 但statementHandler并没有开放相应的API，所以只好用反射来强行获取。
*/
BaseStatementHandler delegate = (BaseStatementHandler)getFieldValue(statementHandler, "delegate");
MappedStatement mappedStatement = (MappedStatement)getFieldValue(delegate, "mappedStatement");
Configuration configuration = mappedStatement.getConfiguration();
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
Object param = boundSql.getParameterObject();
MetaObject metaObject = configuration.newMetaObject(param);
long total = 0;
String sql = boundSql.getSql();
String countSql = "select count(1) from (" + sql+ ") as t"; //记录统计  (mysql要求必须添加 最后的as t)
try{
Connection conn = (Connection)invocation.getArgs()[0];
PreparedStatement ps = conn.prepareStatement(countSql);
int i = 1;
for (ParameterMapping pm : boundSql.getParameterMappings()) {
Object value = null;
String propertyName = pm.getProperty();
PropertyTokenizer prop = new PropertyTokenizer(propertyName);
if (typeHandlerRegistry.hasTypeHandler(param.getClass())) {
value = param;  
}
else if (boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);  
}
else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX)&& boundSql.hasAdditionalParameter(prop.getName())) {  
value = boundSql.getAdditionalParameter(prop.getName());
if (value != null) {  
value = configuration.newMetaObject(value).getValue(propertyName.substring(prop.getName().length()));  
}
} else {  
value = metaObject.getValue(propertyName);
}
pm.getTypeHandler().setParameter(ps, i++, value, pm.getJdbcType());
}
ResultSet rs = ps.executeQuery();
rs.next();
total = rs.getLong(1);
rs.close();  
ps.close();
}
catch (Exception e){
throw new RuntimeException("分页查询无法获取总记录数", e);
}
return total;
}
/**
* 生成分页SQL
*/
private String generatePageSql(String sql, Page page){
StringBuilder pageSql = new StringBuilder();
if("mysql".equals(dialect)){
pageSql.append(sql);
pageSql.append(" limit ").append(page.getStart()).append(",").append(page.getLimit());  
}
else if("oracle".equals(dialect)){
pageSql.append("select * from (select t.*, ROWNUM num from (")
.append(sql).append(") as t where ROWNUM <= ")
.append(page.getStart() + page.getLimit())
.append(") where num > ").append(page.getStart());
}
else{
throw new RuntimeException("分页插件还不支持数据库类型:" + dialect);
}
return pageSql.toString();
}
/**
* 用反射取对象的属性值
*/
private Object getFieldValue(Object obj, String fieldName) throws Exception{        
for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {
try{
Field field = superClass.getDeclaredField(fieldName);
field.setAccessible(true);
return field.get(obj);
}
catch(Exception e){}
}
return null;
}
/**
* 用反射设置对象的属性值
*/
private void setFieldValue(Object obj, String fieldName, Object fieldValue) throws Exception{
Field field = obj.getClass().getDeclaredField(fieldName);
field.setAccessible(true);
field.set(obj, fieldValue);
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties props) {
}
public void setDialect(String dialect){
this.dialect = dialect.toLowerCase();
}
public String getDialect(){
return this.dialect;
}
}