博主最近入职啦,在试用期做了一个练手项目,工作重点集中在MyBatis查询上,做一些报表展示。
在过程中接触到一些mybatis 动态sql,还有一些小坑,专门总结一下。
-
choose, when, otherwise
<select id="find" resultType="java.lang.Long">
select id from tablename group by id having
<choose>
<when test="type==0">
max(days) between 1 and 60
</when>
<when test="type==1">
max(days) > 60
</when>
</choose>
</select>
- case when end
- 多参数的时候,各种版本的mybatis写法不同,比如#{0},#{arg0}
select id,
IFNULL(sum(interest_part) ,0) as interest,
sum(case when status=1 then column1
when status=2 then column2
else 0
end) as repayInterest
from table
group by loan_id having max(day) =0
limit #{arg0},#{arg1}
- if
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
- 对in的使用,一定要先判断list是否为空,否则list为空时会出错。
- foreach
- 使用1=0返回null
<select id="sumRepayInterest" parameterType="java.util.List" resultType="java.math.BigDecimal">
select IFNULL(sum(money),0) from table
<where>
status=1
<choose>
<when test="list!=null and list.size>0">
and loan_id in
<foreach item="item" collection="list" separator="," open="(" close=")" index="">#{item, jdbcType=BIGINT}
</foreach>
</when>
<otherwise>
and 1=0
</otherwise>
</choose>
</where>
</select>
- 要显示数值的时候,如果查询为空,会显示null,要做判断。
IFNULL(sum(money),0)
其他动态SQL之后再总结啦~
最后,最最最重要的是,博主是个小菜鸡,刚开始学,如有大神指出错误,感激不尽。欢迎大家讨论。