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;
	}
}

评论

2 楼 estn_h 2013-03-27  

1 楼 zouhuiqun 2012-04-05