今天在实现签到功能的时候,测试用了没有签到过的用户id,所以在查询总积分的过程中出现了积分为空值的异常,进过修改,将其签到积分的空值改为0值然后再存入Model中程序正常运行。
sql求和出现null时赋值为0:
语句为:
SELECT
CASE WHEN sumScore IS NULL THEN 0 ELSE sumScore
END AS sumScore
FROM(SELECT SUM(poi_score)AS sumScore FROM points WHERE u_id = 9 AND poi_del = 0)
AS a
public SumModel sumScores(UserModel user){sql = "SELECT CASE WHEN sumScore IS NULL THEN 0 ELSE sumScore END AS sumScore FROM(SELECT SUM(poi_score)AS sumScore FROM points WHERE u_id = ? AND poi_del = ?) AS a";data = db.query(sql, user.getU_id(),0);return mapToSumScoreModel(data).get(0);}
泛型转换:
public List<SumModel> mapToSumScoreModel(List<Map<String,Object>> data){List<SumModel> list = new ArrayList<>();SumModel sumModel = null;for (Map<String,Object> map : data) {sumModel = new SumModel(Integer.parseInt(map.getOrDefault("sumScore", "").toString()));list.add(sumModel);}return list;}
本文链接:https://my.lmcjl.com/post/15002.html
展开阅读全文
4 评论