I have a problem to implement custom handler for Map through MyBatis in my Spring Boot example.
Here is my request object shown below
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class QueryRequest implements Serializable {
private List<String> years;
private List<String> months;
private List<String> region;
private List<String> office;
}
Here is the response object shown below
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class TaskCountResponse implements Serializable {
private String region;
private String office;
private Map<String, Integer> monthlyCounts;
}
Here is the dao shown below
@Repository
public interface QueryTaskDao {
List<TaskCountResponse> getTaskStatusCounts(QueryRequest queryRequest);
}
Here is the custom handler for Map<String,Integer> shown below
@Slf4j
public class MapTypeHandler extends BaseTypeHandler<Map<String, Integer>> {
private static final ObjectMapper objectMapper = new ObjectMapper();
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Map<String, Integer> parameter, JdbcType jdbcType) throws SQLException {
try {
String json = objectMapper.writeValueAsString(parameter);
log.info("MapTypeHandler | setNonNullParameter | json : " + json);
ps.setString(i, json);
} catch (IOException e) {
throw new SQLException("Error converting Map to JSON", e);
}
}
@Override
public Map<String, Integer> getNullableResult(ResultSet rs, String columnName) throws SQLException {
try {
String json = rs.getString(columnName);
log.info("MapTypeHandler | getNullableResult(ResultSet rs, String columnName) | json : " + json);
if (json != null) {
return objectMapper.readValue(json, new TypeReference<Map<String, Integer>>() {});
}
return null;
} catch (IOException e) {
throw new SQLException("Error converting JSON to Map", e);
}
}
@Override
public Map<String, Integer> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
try {
String json = rs.getString(columnIndex);
log.info("MapTypeHandler | getNullableResult(ResultSet rs, int columnIndex) | json : " + json);
if (json != null) {
return objectMapper.readValue(json, new TypeReference<Map<String, Integer>>() {});
}
return null;
} catch (IOException e) {
throw new SQLException("Error converting JSON to Map", e);
}
}
@Override
public Map<String, Integer> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
try {
String json = cs.getString(columnIndex);
log.info("MapTypeHandler | getNullableResult(CallableStatement cs, int columnIndex) | json : " + json);
if (json != null) {
return objectMapper.readValue(json, new TypeReference<Map<String, Integer>>() {});
}
return null;
} catch (IOException e) {
throw new SQLException("Error converting JSON to Map", e);
}
}
}
Here is the XML part of mybatis
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.dao.QueryTaskDao">
<cache/>
<!-- Result Map to map query results to TaskStatusCountResponse -->
<resultMap id="taskStatusCountResultMap" type="com.example.model.TaskStatusCountResponse">
<result property="region" column="region_name"/>
<result property="office" column="office_name"/>
<!-- Handling dynamic columns for monthlyCounts -->
<result property="monthlyCounts" column="monthlyCounts" javaType="java.util.Map" typeHandler="com.example.utils.MapTypeHandler"/>
</resultMap>
<!-- SQL fragment for dynamic filtering -->
<sql id="queryTaskStatusCondition">
<where>
<!-- Filter by years -->
<if test="years != null and !years.isEmpty()">
AND SUBSTRING(tt.task_finish_time, 1, 4) IN
<foreach collection="years" item="year" open="(" close=")" separator=",">
#{year}
</foreach>
</if>
<!-- Filter by months -->
<if test="months != null and !months.isEmpty()">
AND SUBSTRING(tt.task_finish_time, 6, 2) IN
<foreach collection="months" item="month" open="(" close=")" separator=",">
#{month}
</foreach>
</if>
<!-- Filter by region -->
<if test="region != null and !region.isEmpty()">
AND tt.region_name IN
<foreach collection="region" item="region" open="(" close=")" separator=",">
#{region}
</foreach>
</if>
<!-- Filter by office -->
<if test="office != null and !office.isEmpty()">
AND tt.office_name IN
<foreach collection="office" item="office" open="(" close=")" separator=",">
#{office}
</foreach>
</if>
</where>
</sql>
<!-- Main SQL query to get Task Status Counts -->
<select id="getTaskStatusCounts" resultMap="taskStatusCountResultMap" parameterType="com.example.model.QueryRequest">
SELECT
tt.region_name,
tt.office_name,
<!-- Dynamically create counts for each year-month combination -->
<trim prefix="" suffix="" suffixOverrides=",">
<foreach collection="years" item="year" separator=",">
<foreach collection="months" item="month" separator=",">
COUNT(CASE WHEN SUBSTRING(tt.task_finish_time, 1, 7) = CONCAT(#{year}, '-', #{month}) THEN 1 END) AS month_${year}_${month}
</foreach>
</foreach>
</trim>
FROM
task_list tt
<include refid="queryTaskStatusCondition"/>
GROUP BY
tt.region_name,
tt.office_name
ORDER BY
tt.region_name_en ASC;
</select>
</mapper>
When I send a request to http://localhost:8048/statistic/getTaskCountsbyTime
{
"years": ["2022", "2023"],
"months": ["01", "02", "03"],
"region": ["A Region", "B Region"],
"office": ["A Region Office", "B Region Office"]
}
I get this response
[
{
"region": "A Region",
"office": "A Region Office",
"monthlyCounts": null
},
{
"region": "B Region",
"office": "B Region Office",
"monthlyCounts": null
}
]
The response should look something like this (with actual counts in place of null):
[
{
"region": "A Region",
"office": "A Region Office",
"monthlyCounts": {
"month_2022_01": 10,
"month_2022_02": 15,
"month_2022_03": 12,
"month_2023_01": 5,
"month_2023_02": 7,
"month_2023_03": 6
}
},
{
"region": "B Region",
"office": "B Region Office",
"monthlyCounts": {
"month_2022_01": 8,
"month_2022_02": 14,
"month_2022_03": 13,
"month_2023_01": 4,
"month_2023_02": 6,
"month_2023_03": 9
}
}
]
I get null of monthlyCounts .Where is the problem in xml mapper or mapper type handler? Can you revise it to fix ?
First try
<select id="getTaskStatusCounts" resultMap="taskStatusCountResultMap" parameterType="com.example.model.QueryRequest">
SELECT
tt.region_name AS region,
tt.office_name AS office,
CONCAT(
'{',
<foreach collection="years" item="year" separator=",">
<foreach collection="months" item="month" separator=",">
CONCAT('"month_${year}_${month}":',
COUNT(CASE WHEN SUBSTRING(tt.task_finish_time, 1, 7) = CONCAT(#{year}, '-', #{month}) THEN 1 END))
</foreach>
</foreach>,
'}'
) AS monthlyCounts
FROM
task_list tt
<include refid="queryTaskStatusCondition"/>
GROUP BY
tt.region_name,
tt.office_name
ORDER BY
tt.region_name_en ASC;
</select>
I also revised it but I get this error message
Cause: java.sql.SQLException: Error converting JSON to Map
; uncategorized SQLException; SQL state [null]; error code [0]; Error converting JSON to Map; nested exception is java.sql.SQLException: Error converting JSON to Map] with root cause
com.fasterxml.jackson.core.JsonParseException: Unexpected character ('"' (code 34)): was expecting comma to separate Object entries
at [Source: (String)"{"month_2022_01":0"month_2022_02":0"month_2022_03":0"month_2023_01":3"month_2023_02":4"month_2023_03":8}"; line: 1, column: 20]
Type handler deals with a single column, so it is not suitable for your usage.
I can think of a few solutions, but the one using ResultHandler may be the cleanest.
The mapper method would now look as follows.
void getTaskStatusCounts(QueryRequest queryRequest,
ResultHandler<Map<String, String>> resultHandler);
Here is the new query which is much simpler.
<select id="getTaskStatusCounts" resultType="map">
SELECT
region_name,
office_name,
CONCAT(SUBSTRING(task_finish_time, 1, 4), '_', SUBSTRING(task_finish_time, 6, 2)) year_month
FROM task_list
<include refid="queryTaskStatusCondition" />
</select>
In general, you should avoid a query that has dynamic column count because it is harder to deal with.
And here is the custom result handler.
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.Map.Entry;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
public class TaskCountResponseResultHandler implements ResultHandler<Map<String, String>> {
private final Map<Entry<String, String>, TaskCountResponse> map = new TreeMap<>(
(e1, e2) -> {
int x = e1.getKey().compareTo(e2.getKey());
return x != 0 ? x : e1.getValue().compareTo(e2.getValue());
});
private final List<String> years;
private final List<String> months;
public TaskCountResponseResultHandler(List<String> years, List<String> months) {
super();
this.years = years;
this.months = months;
}
@Override
public void handleResult(ResultContext<? extends Map<String, String>> resultContext) {
// This method is called for each row and
// this map contains [column label] vs. [column value]
Map<String, String> row = resultContext.getResultObject();
Entry<String, String> regionOfficeKey = Map.entry(row.get("REGION_NAME"), row.get("OFFICE_NAME"));
String monthKey = "month_" + row.get("YEAR_MONTH");
map.computeIfAbsent(regionOfficeKey, k -> {
TaskCountResponse v = new TaskCountResponse();
v.setRegion(k.getKey());
v.setOffice(k.getValue());
v.setMonthlyCounts(new TreeMap<>());
return v;
}).getMonthlyCounts().merge(monthKey, 1, Integer::sum);
}
public List<TaskCountResponse> getResult() {
return map.values().stream().map(response -> {
// Puts zero for months with no data
Map<String, Integer> counts = response.getMonthlyCounts();
for (String year : years) {
for (String month : months) {
counts.putIfAbsent("month_" + year + "_" + month, 0);
}
}
return response;
}).toList();
}
}
To execute the query and get the list, your code would look as follows, basically.
List<String> years = List.of("2022", "2023");
List<String> months = List.of("01", "02");
// Prepare parameter
QueryRequest param = new QueryRequest();
param.setYears(years);
param.setMonths(months);
// Create a result handler.
TaskCountResponseResultHandler resultHandler = new TaskCountResponseResultHandler(years, months);
// Execute query.
mapper. getTaskStatusCounts(param, resultHandler);
// Get the list from the result handler.
List<TaskCountResponse> result = resultHandler.getResult();
[EDIT in response to OP's update]
I would not use JSON.
Here is the tricky part of the query.
CONCAT(
<foreach collection="years" item="year" separator=",">
<foreach collection="months" item="month" separator=",">
CONCAT('month_',#{year},'_',#{month},'=',
COUNT(CASE WHEN SUBSTRING(tt.task_finish_time, 1, 7) = CONCAT(#{year}, '-', #{month}) THEN 1 END),
',')
</foreach>
</foreach>
) AS monthlyCounts
MySQL casts the number (i.e. the result of COUNT) implicitly, but some other DBs might need explicit conversion like TO_CHAR.
Unless you are 100% sure there is no malicious String in years or months, you should not use ${} as it could be vulnerable to SQL injection.
The value of monthlyCounts may look like month_2022_01=2,month_2022_02=1,...,.
There is an extra comma at the end, but String#split() in the type handler makes it irrelevant.
And here is the type handler.
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;
import java.util.TreeMap;
import java.util.stream.Collectors;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
public class MapTypeHandler implements TypeHandler<Map<String, Integer>> {
@Override
public void setParameter(PreparedStatement ps, int i, Map<String, Integer> parameter, JdbcType jdbcType)
throws SQLException {
// not used
}
@Override
public Map<String, Integer> getResult(ResultSet rs, String columnName) throws SQLException {
return parse(rs.getString(columnName));
}
@Override
public Map<String, Integer> getResult(ResultSet rs, int columnIndex) throws SQLException {
return parse(rs.getString(columnIndex));
}
@Override
public Map<String, Integer> getResult(CallableStatement cs, int columnIndex) throws SQLException {
return parse(cs.getString(columnIndex));
}
private Map<String, Integer> parse(String str) {
return Arrays.stream(str.split(",")).map(s -> s.split("="))
.collect(Collectors.toMap(arr -> arr[0], arr -> Integer.valueOf(arr[1]), (v1, v2) -> v2, TreeMap::new));
}
}
The demo includes both solutions (it uses HSQLDB).
https://github.com/harawata/mybatis-issues/tree/master/so-79260900