最近用spring的时候在xml里写了个逻辑,只查询这个月的所有数据。上网搜了半天,也很少有人能说清楚这个语法到底怎么写。
所以这里最后解决了,给大家科普一下:
首先在service里的写法,把要查询的条件放进map:
@Override
public float getSumMonthMoney(Integer userType, Integer billtype,String timeMonth) {
Map<String,Object> value = new HashMap<String,Object>();
value.put("userType",userType);
value.put("billType",billtype);
value.put("timeMonth",timeMonth);
return userBillMapper.selectReportMonthMoneyInfo(value);
}
然后是在dao层里的定义:
float selectReportMonthMoneyInfo(Map<String, Object> param);
最后在mapper里,即xml文件中:
<!--当月查询-->
<sql id="ReportMonthConditionSql">
<if test="timeMonth != null and timeMonth != ''">
DATE_FORMAT(create_time,'%Y%M') = DATE_FORMAT(#{timeMonth},'%Y%M')
</if>
<if test="timeMonth == null or timeMonth == ''">
DATE_FORMAT(create_time,'%Y%M') = DATE_FORMAT(CURDATE(),'%Y%M')
</if>
</sql>
<!--用户当月查询-注意传递timeNonth=null-->
<select id="getUserMonthIncome" resultType="java.lang.Float">
select
COALESCE(SUM(money),0)
from t_user_bill
<where>
((bill_type = 1) or (bill_type = 8)) and
user_type = #{userType,jdbcType=INTEGER}
and user_id = #{userId,jdbcType=INTEGER}
and <include refid="ReportMonthConditionSql"/>
</where>
</select>
成功地把这个月的资金总和求出来了。