http://zxlaiye.iteye.com/blog/1344703
悲催的Bug
与spring结合时,org.mybatis.spring.mapper.MapperScannerConfigurer不支持 <context:property-placeholder />
(mybatis 3.1.1, mybatis-spring 1.1.1)
●结合Spring的配置
●传递多个参数
●分页插件。 有以下缺点:
Page.java
PagingPlugin.java
![](https://i-blog.csdnimg.cn/blog_migrate/9bbd5f85fefde0a6ff4e9778c624f159.gif)
(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;
- }
- }