mybatis 查询无结果，SQL查询有结果

[噗哩噼啦]

pojo类有set/get方法
public class GzltjCount implements Serializable {
private static final long serialVersionUID = 1L;

private String CId;

private String CName;

private String CDeptId;

private String CDeptName;

private String CCountCun;

private String CCountQu;
/**
* 创建扫描任务的扫描任务
*/
private String CCountCreateScan;

private String CCountDoScan;

private String CCountDoHeyan;

private String CCountUpCl;

/**
* 平均取件天数
*/
private String CAvgQuDay;

/**
* 文件滞留天数
*/
private String CRetentionFileDay;
}
xml
<resultMap id="BaseResultMap" type="com.thunisoft.ssclsz.platform.pojo.GzltjCount">
<!-- WARNING - @mbggenerated This element is automatically generated by
MyBatis Generator, do not modify. -->
<id column="cid" jdbcType="VARCHAR" property="CId" />
<result column="deptName" jdbcType="VARCHAR" property="CDeptName" />
<result column="deptId" jdbcType="VARCHAR" property="CDeptId" />
<result column="countCun" jdbcType="VARCHAR" property="CCountCun" />
<result column="countQu" jdbcType="VARCHAR" property="CCountQu" />
<result column="countCreateScan" jdbcType="VARCHAR" property="CCountCreateScan" />
<result column="countDoScan" jdbcType="VARCHAR" property="CCountDoScan" />
<result column="countDoHy" jdbcType="VARCHAR" property="CCountDoHeyan" />
<result column="countUpCl" jdbcType="VARCHAR" property="CCountUpCl" />
<result column="countAvg" jdbcType="VARCHAR" property="CAvgQuDay" />
<result column="countRetention" jdbcType="VARCHAR" property="CRetentionFileDay" />
</resultMap>
<select id="selectZlForDept" resultMap="BaseResultMap" parameterType="java.util.Map">
SELECT
foo.c_id AS "cid",
SUM (FIRST) AS "countCun",
SUM (SECOND) AS "countQu",
SUM (third) AS "countAvg",
SUM (forth) AS "countRetention"
FROM
(
SELECT
t_court_user.c_id,
COUNT (1) AS "first",
0 AS "second",
0 AS "third",
0 AS "forth"
FROM
t_szxx_history
LEFT JOIN t_court_user ON c_input_user_id = t_court_user.c_id
WHERE
c_input_user_id IS NOT NULL
AND c_input_user_id != ''
AND d_cfsj BETWEEN '2017-04-20'
AND '2019-04-20'
AND c_js_dept_id = '157286457'
GROUP BY
t_court_user.c_id
UNION ALL
SELECT
t_court_user.c_id,
0,
COUNT (1),
0,
0
FROM
t_szxx_history
LEFT JOIN t_court_user ON c_output_user_id = t_court_user.c_id
WHERE
c_output_user_id IS NOT NULL
AND c_output_user_id != ''
AND d_cfsj BETWEEN '2017-04-20'
AND '2019-04-20'
AND c_js_dept_id = '157286457'
GROUP BY
t_court_user.c_id
UNION ALL
SELECT
t_court_user.c_id,
0,
0,
SUM (
date_part(
'day',
t_szxx_history.d_cfsj :: TIMESTAMP - t_szxx_history.d_update :: TIMESTAMP
) + 1
) / COUNT (1),
MAX (
date_part(
'day',
t_szxx_history.d_cfsj :: TIMESTAMP - t_szxx_history.d_update :: TIMESTAMP
) + 1
)
FROM
t_szxx_history
LEFT JOIN t_court_user ON c_input_user_id = t_court_user.c_id
WHERE
t_szxx_history.d_cfsj BETWEEN '2017-04-20'
AND '2019-04-20'
AND c_js_dept_id = '157286457'
AND t_szxx_history.n_zt = 4
AND t_szxx_history.c_input_user_id IS NOT NULL
AND t_szxx_history.c_input_user_id != ''
GROUP BY
t_court_user.c_id
) AS foo
GROUP BY c_id
</select>
实现层
public List<GzltjCount> selectZlForDept(Timestamp alarmStartTime,
Timestamp alarmEndTime, String column, String sort) {
Map<String, Object> selectInfo = new HashMap<String, Object>();
selectInfo.put("alarmStartTime", alarmStartTime);
selectInfo.put("alarmEndTime", alarmEndTime);
selectInfo.put("column", column);
selectInfo.put("sort", sort);
List<GzltjCount> list = szxxCountMapper.selectZlForDept(selectInfo);
return list;
}

[噗哩噼啦]

引用 1 楼 QWERT4745 的回复:

控制台打印的sql执行没问题？
那就只能是映射哪个地方不对了

SQL执行不报错，日志打印出来的到数据库有结果

[QWERT4745]

有没有可能是sum()出来的结果跟你设置的varchar不匹配？

[噗哩噼啦]

引用 3 楼 QWERT4745 的回复:

有没有可能是sum()出来的结果跟你设置的varchar不匹配？

没有，现在问题变了，查询结果里没有第一列，其他列都有值了

[hbhbhbhbhb1021]

数据库里CID是什么类型

[lifewell1]

应该是映射的问题，映射错了才会没值

[QWERT4745]

控制台打印的sql执行没问题？ 那就只能是映射哪个地方不对了

[有鲲]

数据库字段名和实体类变量名是不一样的 <result column="deptName" jdbcType="VARCHAR" property="CDeptName" />的column是数据库的字段名

[噗哩噼啦]

问题已经解决！ 连表查询时 ，使用 {db.t_court_user} 可以解决查询结果不出现的情况，具体原因不知道，或者AB表相连时，A左连B，查询A表字段也可以查出

[Codeing Gap]

我现在出现同样的问题。请问下你使用{db.t_court_user}查询解决了这个问题。这个db是代指什么？数据库名称吗？
我现在在mybatis中，出现了查询条件如果不采用<include refid="xxx"/>标签进行复用，并不判空，直接赋值的方式。可以查询到数据。但是如果将查询的条件采用sql片段提取作为公用，采用<include>标签来复用它，放入<select>xxx<select/>语句中去，就无法查询到结果了。

[jicaowuliu]

在SqlMapConfig.xml配置文件中把
<property name="url" value="jdbc:mysql://localhost:3306/mybatistest" />
改为:
<property name="url" value="jdbc:mysql://localhost:3306/mybatistest?characterEncoding=utf8" />
指定一个字符的编码格式，问题解决。