很多时候我们会查询一个列表，然后对列表过滤分组，构建一个key对应特定的几个列表的情况出现，java中要写比较多的代码，其实可以使用mysql直接构建出一对多的json结构，然后将json返回，java中只需要将这个json转成一个对象即可
下面例子都是不创建新对象直接转换成map使用，如果创建新对象那么json转成对象的速度会更快，也会更方便。比如
java List<String> serieData=baseMapper.getCheckTrainTaskNumByTrain(req,robNames);
for (String s : serieData) {
seriesReq r= JSON.parseObject(s,seriesReq.class);
series.add(r);
}
Map<String, Object> result = new HashMap<>();
result.put("xAxis", xAxis);
result.put("series", series);
return result;
}
java//String接受json对象
public Map<String, Object> test(Request req){
List<Map<String,Object>> dataList=new ArrayList<>();
List<String> list = baseMapper.getCheckFaultNum(req);
for (String s: list) {
JSONObject jsonObject=JSON.parseObject(s);
Map<String,Object> map=jsonObject.getInnerMap();
List<Object> listobj=JSON.parseArray((String) map.get("list"),Object.class);
map.put("list", listobj);
dataList.add(map);
}
return Collections.singletonMap("data",dataList);
}
//JSONObject接受json对象
public Map<String, Object> test(Request req){
List<Map<String,Object>> dataList=new ArrayList<>();
List<JSONObject> list = baseMapper.getCheckFaultNum(req);
for (JSONObject jsonObject : list) {
String value=(String)jsonObject.get("result");//这个result就是sql里面json对象的别名
JSONObject o= JSON.parseObject(value);
Map<String, Object> res=o.getInnerMap();
List<Object> listobj=JSON.parseArray((String) map.get("list"),Object.class);
map.put("list", listobj);
dataList.add(res);
}
return Collections.singletonMap("data",dataList);
}
//字符串接受jsonArray
public Map<String, Object> test(Request req){
List<Map<String,Object>> dataList = new ArrayList<>();
String str = baseMapper.getCheckFaultNum(req);
JSONArray jsonArray=JSON.parseArray(str);
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject obj = JSONObject.parseObject((String) jsonArray.get(i));
Map<String, Object> res=o.getInnerMap();
List<Object> listobj=JSON.parseArray((String) map.get("list"),Object.class);
map.put("list", listobj);
dataList.add(res);
}
return Collections.singletonMap("data",dataList);
}
//直接使用阿里巴巴的JSONArray对象不能直接接受数据库返回的jsonArray，只能采用string的方式接受了
xml<select id="getCheckFaultNum" resultType="com.alibaba.fastjson.JSONObject">
SELECT JSON_OBJECT(
'name', '总数',
'list', COALESCE(JSON_ARRAYAGG(
JSON_OBJECT(
'totalFaults', item.totalFaults,
'checkDate', item.checkDate
)
), '[]')
) AS result
FROM ( SELECT
DATE_FORMAT(a.check_time, '%Y-%m-%d') AS checkDate,
COUNT(*) AS totalFaults
FROM
product_abnormal_v a
<where>
a.check_time <![CDATA[>=]]> #{req.startDate,jdbcType=DATE}
and a.check_time <![CDATA[<=]]> #{req.endDate,jdbcType=DATE}
and a.fault_status!=2
and a.task_status=6
and a.check_mode=0
</where>
GROUP BY
DATE_FORMAT(a.check_time, '%Y-%m-%d'))item
<if test="req.trainNo != null and req.trainNo != ''">
union all
SELECT JSON_OBJECT(
'name',#{req.trainNo},
'list', COALESCE(JSON_ARRAYAGG(
JSON_OBJECT(
'totalFaults', item.totalFaults,
'checkDate', item.checkDate
)
), '[]')
) AS result
FROM ( SELECT
DATE_FORMAT(a.check_time, '%Y-%m-%d') AS checkDate,
COUNT(*) AS totalFaults
FROM
product_abnormal_v a
<where>
a.check_time <![CDATA[>=]]> #{req.startDate,jdbcType=DATE}
and a.check_time <![CDATA[<=]]> #{req.endDate,jdbcType=DATE}
and a.fault_status!=2
and a.task_status=6
and a.check_mode=0
and a.vehicle_no=#{req.trainNo}
</where>
GROUP BY
DATE_FORMAT(a.check_time, '%Y-%m-%d'))item
</if>
</select>
xml<select id="getCheckFaultNum" resultType="java.lang.String">
SELECT JSON_OBJECT(
'name', '总数',
'list', COALESCE(JSON_ARRAYAGG(
JSON_OBJECT(
'totalFaults', item.totalFaults,
'checkDate', item.checkDate
)
), '[]')
) AS result
FROM ( SELECT
DATE_FORMAT(a.check_time, '%Y-%m-%d') AS checkDate,
COUNT(*) AS totalFaults
FROM
product_abnormal_v a
<where>
a.check_time <![CDATA[>=]]> #{req.startDate,jdbcType=DATE}
and a.check_time <![CDATA[<=]]> #{req.endDate,jdbcType=DATE}
and a.fault_status!=2
and a.task_status=6
and a.check_mode=0
</where>
GROUP BY
DATE_FORMAT(a.check_time, '%Y-%m-%d'))item
<if test="req.trainNo != null and req.trainNo != ''">
union all
SELECT JSON_OBJECT(
'name',#{req.trainNo},
'list', COALESCE(JSON_ARRAYAGG(
JSON_OBJECT(
'totalFaults', item.totalFaults,
'checkDate', item.checkDate
)
), '[]')
) AS result
FROM ( SELECT
DATE_FORMAT(a.check_time, '%Y-%m-%d') AS checkDate,
COUNT(*) AS totalFaults
FROM
product_abnormal_v a
<where>
a.check_time <![CDATA[>=]]> #{req.startDate,jdbcType=DATE}
and a.check_time <![CDATA[<=]]> #{req.endDate,jdbcType=DATE}
and a.fault_status!=2
and a.task_status=6
and a.check_mode=0
and a.vehicle_no=#{req.trainNo}
</where>
GROUP BY
DATE_FORMAT(a.check_time, '%Y-%m-%d'))item
</if>
</select>
xml<select id="getCheckFaultNum" resultType="java.lang.String">
select JSON_ARRAYAGG(it.result) as array
from (
SELECT JSON_OBJECT(
'name', '总数',
'list', COALESCE(JSON_ARRAYAGG(
JSON_OBJECT(
'totalFaults', item.totalFaults,
'checkDate', item.checkDate
)
), '[]')
) AS result
FROM ( SELECT
DATE_FORMAT(a.check_time, '%Y-%m-%d') AS checkDate,
COUNT(*) AS totalFaults
FROM
product_abnormal_v a
<where>
a.check_time <![CDATA[>=]]> #{req.startDate,jdbcType=DATE}
and a.check_time <![CDATA[<=]]> #{req.endDate,jdbcType=DATE}
and a.fault_status!=2
and a.task_status=6
and a.check_mode=0
</where>
GROUP BY
DATE_FORMAT(a.check_time, '%Y-%m-%d'))item
<if test="req.trainNo != null and req.trainNo != ''">
union all
SELECT JSON_OBJECT(
'name',#{req.trainNo},
'list', COALESCE(JSON_ARRAYAGG(
JSON_OBJECT(
'totalFaults', item.totalFaults,
'checkDate', item.checkDate
)
), '[]')
) AS result
FROM ( SELECT
DATE_FORMAT(a.check_time, '%Y-%m-%d') AS checkDate,
COUNT(*) AS totalFaults
FROM
product_abnormal_v a
<where>
a.check_time <![CDATA[>=]]> #{req.startDate,jdbcType=DATE}
and a.check_time <![CDATA[<=]]> #{req.endDate,jdbcType=DATE}
and a.fault_status!=2
and a.task_status=6
and a.check_mode=0
and a.vehicle_no=#{req.trainNo}
</where>
GROUP BY
DATE_FORMAT(a.check_time, '%Y-%m-%d'))item
</if>
) it
</select>
本文作者:Weee
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
预览: