The sql query return value encapsulates multiple key and value instances using map
- 2021-11-01 03:33:42
- OfStack
Directly on the code, the code is tested
1. Rewrite ResultHandler
public class MapResultHandler implements ResultHandler {
private final Map mappedResults = new HashMap();
@Override
public void handleResult(ResultContext context) {
@SuppressWarnings("rawtypes")
Map map = (Map) context.getResultObject();
mappedResults.put(map.get("key"), map.get("value"));
}
public Map getMappedResults() {
return mappedResults;
}
}
2. Encapsulated in mapper
<resultMap id="retMap" type="java.util.HashMap">
<result column="keyName" property="key" javaType="java.lang.String"/>
<result column="val" property="value" javaType="java.math.BigDecimal"/>
</resultMap> Examples:
SELECT F_NAME keyName,nvl(sum (F_METADATA_VALUE),0) val from Table name
GROUP BY F_CODE, F_NAME3. service implementation
@Autowired
private SqlSessionFactory sqlSessionFactory;
private static final String mapperPath = "mapper Path of .";
Map<String, Object> parameter = new HashMap<>();
// Setting parameters
parameter.put("query", query);
//mapper Method name of
String methodName = "selectMedicineSurvey";
// Query data usage Map Encapsulation
Map<String, BigDecimal> medicineSurveyMap = getStringBigDecimalMap(mapperPath, parameter, methodName);
// Query data usage Map Encapsulation
private Map<String, BigDecimal> getStringBigDecimalMap(String mapperPath, Map<String, Object> parameter, String methodName) {
SqlSession sqlSession = sqlSessionFactory.openSession(true);
MapResultHandler handler = new MapResultHandler();
sqlSession.select(mapperPath + methodName, parameter, handler);
// Off-current
sqlSession.close();
// Get results
return (Map<String, BigDecimal>) handler.getMappedResults();
}
sqlSession.close();1 must remember that the database is closed, otherwise the number of connections will jam the database
MyBatis queries two fields and returns Map, with one field as key and one field as the implementation of value
1. Description of the problemWhen using MyBatis, we often encounter this situation: two fields of SELECT need to return one Map, where the first field is key and the second field is value. Although MapKey of MyBatis is very practical, it cannot solve this scenario. Here, we will introduce a solution to solve this problem by using interceptors.
2. SolutionsSee source code: spring-mybatis-test
2.1 Notes
package com.adu.spring_test.mybatis.annotations;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Map query results to map The annotation of, in which the 1 Fields are key , No. 2 Fields are value.
* <p>
* Note: The return type must be {@link java.util.Map Map<K, V>} . K/V The type of is passed through the MyBatis Adj. TypeHander Perform type conversion and customize if necessary TypeHander .
*
* @author yunjie.du
* @date 2016/12/22 18:44
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.METHOD })
public @interface MapF2F {
/**
* Is it allowed key Repeat. If it is not allowed, and the actual result is duplicated, it will be thrown org.springframework.dao.DuplicateKeyException .
*
* @return
*/
boolean isAllowKeyRepeat() default true;
/**
* For the same key , whether it is allowed value Different ( In allowing key On the premise of repetition ) . If allowed, according to the query results, the latter overwrites the former; If it is not allowed, a org.springframework.dao.DuplicateKeyException .
*
* @return
*/
boolean isAllowValueDifferentWithSameKey() default false;
}2.2 Interceptor
package com.adu.spring_test.mybatis.interceptor;
import java.lang.reflect.Method;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.mapping.MappedStatement;
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;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DuplicateKeyException;
import com.adu.spring_test.mybatis.annotations.MapF2F;
import com.adu.spring_test.mybatis.util.ReflectUtil;
import javafx.util.Pair;
/**
* MapF2F Interceptor of
*
* @author yunjie.du
* @date 2016/12/22 18:44
*/
@Intercepts(@Signature(method = "handleResultSets", type = ResultSetHandler.class, args = { Statement.class }))
public class MapF2FInterceptor implements Interceptor {
private Logger logger = LoggerFactory.getLogger(MapF2FInterceptor.class);
@Override
public Object intercept(Invocation invocation) throws Throwable {
MetaObject metaStatementHandler = ReflectUtil.getRealTarget(invocation);
MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("mappedStatement");
String className = StringUtils.substringBeforeLast(mappedStatement.getId(), ".");// Current class
String currentMethodName = StringUtils.substringAfterLast(mappedStatement.getId(), ".");// Current method
Method currentMethod = findMethod(className, currentMethodName);// Gets the current Method
if (currentMethod == null || currentMethod.getAnnotation(MapF2F.class) == null) {// If the current Method No annotations MapF2F
return invocation.proceed();
}
// If there is MapF2F Annotation, the results are intercepted and transformed here
MapF2F mapF2FAnnotation = currentMethod.getAnnotation(MapF2F.class);
Statement statement = (Statement) invocation.getArgs()[0];
Pair<Class<?>, Class<?>> kvTypePair = getKVTypeOfReturnMap(currentMethod);// Gets returns Map Li key-value Type of
TypeHandlerRegistry typeHandlerRegistry = mappedStatement.getConfiguration().getTypeHandlerRegistry();// Get various TypeHander Registrar of
return result2Map(statement, typeHandlerRegistry, kvTypePair, mapF2FAnnotation);
}
@Override
public Object plugin(Object obj) {
return Plugin.wrap(obj, this);
}
@Override
public void setProperties(Properties properties) {
}
/**
* Object that matches the specified function name Method .
*
* @param className
* @param targetMethodName
* @return
* @throws Throwable
*/
private Method findMethod(String className, String targetMethodName) throws Throwable {
Method[] methods = Class.forName(className).getDeclaredMethods();// All declared methods of the class
if (methods == null) {
return null;
}
for (Method method : methods) {
if (StringUtils.equals(method.getName(), targetMethodName)) {
return method;
}
}
return null;
}
/**
* Get function returns Map Medium key-value Type of
*
* @param mapF2FMethod
* @return left For key The type of, right For value Type of
*/
private Pair<Class<?>, Class<?>> getKVTypeOfReturnMap(Method mapF2FMethod) {
Type returnType = mapF2FMethod.getGenericReturnType();
if (returnType instanceof ParameterizedType) {
ParameterizedType parameterizedType = (ParameterizedType) returnType;
if (!Map.class.equals(parameterizedType.getRawType())) {
throw new RuntimeException(
"[ERROR-MapF2F-return-map-type] Use MapF2F, The return type must be java.util.Map Type! ! ! method=" + mapF2FMethod);
}
return new Pair<>((Class<?>) parameterizedType.getActualTypeArguments()[0],
(Class<?>) parameterizedType.getActualTypeArguments()[1]);
}
return new Pair<>(null, null);
}
/**
* Map query results to Map Of which 1 Fields as key , No. 2 Fields as value.
*
* @param statement
* @param typeHandlerRegistry MyBatis Li typeHandler The registrar of, which is convenient to convert to the result type specified by the user
* @param kvTypePair Function specifies the return of the Map key-value Type of
* @param mapF2FAnnotation
* @return
* @throws Throwable
*/
private Object result2Map(Statement statement, TypeHandlerRegistry typeHandlerRegistry,
Pair<Class<?>, Class<?>> kvTypePair, MapF2F mapF2FAnnotation) throws Throwable {
ResultSet resultSet = statement.getResultSet();
List<Object> res = new ArrayList();
Map<Object, Object> map = new HashMap();
while (resultSet.next()) {
Object key = this.getObject(resultSet, 1, typeHandlerRegistry, kvTypePair.getKey());
Object value = this.getObject(resultSet, 2, typeHandlerRegistry, kvTypePair.getValue());
if (map.containsKey(key)) {// The key Already exists
if (!mapF2FAnnotation.isAllowKeyRepeat()) {// Judge whether it is allowed or not key Repetition
throw new DuplicateKeyException("MapF2F duplicated key!key=" + key);
}
Object preValue = map.get(key);
if (!mapF2FAnnotation.isAllowValueDifferentWithSameKey() && !Objects.equals(value, preValue)) {// Judge whether it is allowed or not value Different
throw new DuplicateKeyException("MapF2F different value with same key!key=" + key + ",value1="
+ preValue + ",value2=" + value);
}
}
map.put(key, value);// No. 1 1 Column as a key, No. 1 2 Column as a value .
}
res.add(map);
return res;
}
/**
* Result type conversion.
* <p>
* Borrowing and registering here MyBatis Adj. typeHander (including custom ones) to facilitate type conversion.
*
* @param resultSet
* @param columnIndex Field subscript, from 1 Begin
* @param typeHandlerRegistry MyBatis Li typeHandler The registrar of, which is convenient to convert to the result type specified by the user
* @param javaType To convert Java Type
* @return
* @throws SQLException
*/
private Object getObject(ResultSet resultSet, int columnIndex, TypeHandlerRegistry typeHandlerRegistry,
Class<?> javaType) throws SQLException {
final TypeHandler<?> typeHandler = typeHandlerRegistry.hasTypeHandler(javaType)
? typeHandlerRegistry.getTypeHandler(javaType) : typeHandlerRegistry.getUnknownTypeHandler();
return typeHandler.getResult(resultSet, columnIndex);
}
}2.3 ReflectUtil
package com.adu.spring_test.mybatis.util;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Reflection tool class
*/
public class ReflectUtil {
private static final Logger logger = LoggerFactory.getLogger(ReflectUtil.class);
/**
* Separate last 1 Target Object for Proxies
*
* @param invocation
* @return
*/
public static MetaObject getRealTarget(Invocation invocation) {
MetaObject metaStatementHandler = SystemMetaObject.forObject(invocation.getTarget());
while (metaStatementHandler.hasGetter("h")) {
Object object = metaStatementHandler.getValue("h");
metaStatementHandler = SystemMetaObject.forObject(object);
}
while (metaStatementHandler.hasGetter("target")) {
Object object = metaStatementHandler.getValue("target");
metaStatementHandler = SystemMetaObject.forObject(object);
}
return metaStatementHandler;
}
}2.4 MyBatis Datasource Configuration Interceptor
<!-- session factory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:mybatis/mybatis-data-config.xml" />
<property name="mapperLocations" value="classpath:mapper/**/*.xml" />
<property name="plugins">
<array>
<bean class="com.adu.spring_test.mybatis.interceptor.MapF2FInterceptor"/>
</array>
</property>
</bean>2.5 Simple examples
/**
* Obtain user names in batches
*
* @param ids
* @return key For ID , value For username
*/
@MapF2F()
Map<Long, String> queryUserNamesByIds(@Param("ids") List<Long> ids);
<resultMap id="retMap" type="java.util.HashMap">
<result column="keyName" property="key" javaType="java.lang.String"/>
<result column="val" property="value" javaType="java.math.BigDecimal"/>
</resultMap>