一、需求
在开发中,我们有时候需要将结果集映射成Map类型,不是List<Map>,如下:
sql查询得到的结果是:
而我们需要的数据格式是:
对应bean的属性类型为:
二、解决方案:
1、在service层实现
这种方法会让service层显得比较臃肿,如果系统中用的比较多,会重复写很多这样的代码
2、编写mybatis拦截器实现
根据多种尝试,最终决定写mybatis拦截器进行实现。
2.1、先写一个拦截器
package com.renyiwei.mybatis.interceptor; import java.lang.reflect.Field; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Properties; import org.apache.ibatis.executor.resultset.ResultSetHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; @Intercepts({ @Signature(method = "handleResultSets", type = ResultSetHandler.class, args = { Statement.class }) }) public class MapInterceptor implements Interceptor { @SuppressWarnings("unused") private Properties properties; @Override public Object intercept(Invocation invocation) throws Throwable { ResultSetHandler resultSetHandler = (ResultSetHandler) invocation.getTarget(); Class<? extends ResultSetHandler> clazz = resultSetHandler.getClass(); // 反射读取boundSql 获得sql语句 Field field = clazz.getDeclaredField("boundSql"); field.setAccessible(true); BoundSql boundsql = (BoundSql) field.get(resultSetHandler); String sql = boundsql.getSql(); // 如果sql中包含 MAP_KEY 和 MAP_VALUE 则被认为需要该将结果集转为Map类型 if (sql.contains("MAP_KEY") && sql.contains("MAP_VALUE")) { try { List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); Map<String, Object> map = new HashMap<String, Object>(); Statement statement = (Statement) invocation.getArgs()[0]; // 取得方法的参数Statement ResultSet rs = statement.getResultSet(); // 取得结果集 while (rs.next()) { String mapKey = rs.getString("MAP_KEY"); Object mapValue = rs.getObject("MAP_VALUE"); map.put(mapKey, mapValue); // 取得结果集后K、V关联后放到MAP当中 } list.add(map); // 这里返回list,而不是直接返回map return list; } catch (Exception e) { // 如果出现异常,可能是冲突,则... return invocation.proceed(); } } // 如果没有进行拦截处理,则执行默认逻辑 return invocation.proceed(); } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { this.properties = properties; } }
2.2、配置拦截器
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="plugins"> <list> <bean class="com.github.miemiedev.mybatis.paginator.OffsetLimitInterceptor"> <property name="dialectClass" value="com.github.miemiedev.mybatis.paginator.dialect.MySQLDialect"></property> </bean> <bean class="com.renyiwei.mybatis.interceptor.MapInterceptor"></bean> </list> </property> </bean>
2.3、写sql语句
SELECT name AS MAP_KEY,value AS MAP_VALUE FROM table