背景介绍
这个月的任务有点重,一直在忙着项目的开发,导致之前都没有更新,月底了怎么着也得更一篇啊。下面进入正题
之前同事在部署系统时,无法启动,一直报这个错误,但是实在找不到原因,大概的方向就是肯定是sql文件出问题了,
因为这部分文件我有参与,所以一块看了一下,最终发现是是Mybatis注解SQL的一个坑,特此记录一下。
友情建议:一般稍微复杂一点的SQL语句还是使用xml方式比较好,便于维护、灵活度也比较高,简单的SQL可以使用注解方式,简洁明了,维护起来也不难。
问题原因
SQL中的代码存在格式问题,一般都是sql中的特殊符号转译问题,检查一下大于小于这类符号的转义写法,注意,如果是使用的注解方式书写SQL,也要检查Mapper文件,虽然日志的报错文件指向了xml文件。本次遇到的就是这个问题。
踩坑过程
原始代码(可正常启动运行)
@Select("select merchant_id as merchantId,\n" +
" (select name from tb_union_merchant where id = merchant_id) as merchantName,\n" +
" id as couponId,\n" +
" discount_price as couponPrice,\n" +
" discount_name as couponName,\n" +
" discount_start as couponStart,\n" +
" discount_rest as couponNum,\n" +
" DATE_FORMAT(activity_end_date, '%Y-%m-%d') as endDate,\n" +
" case\n" +
" when (discount_rest <= 0 and DATE_FORMAT(update_date,'%Y-%m-%d') >= date_sub(curdate(),interval 2 day)) then '3'\n" +
" when discount_rest >0 then '1'\n" +
" else '' end as status\n"+
" from tb_discount\n" +
"where del_flag = '0'\n" +
" and putaway_flag = '1'\n" +
" and vouchers_create = '3'\n" +
" limit #{offset},#{limit};")
List<Map> getDiscountListByPage(@Param("offset")int offset,@Param("limit")int limit);
以上代码为分页查询语句,中间包含了子查询和条件判断,应该是属于稍微复杂的SQL语句了。很明显,里面包含了好多转译符,可以推断出,里面注解SQL是从其他地方粘过来的(和同事核对过,这是数据库软件中写的脚本,运行成功之后才粘贴过来的)。注意一下里面的小于等于、大于等于符号,直接是使用的符号,如下图:
但是直接在注解中使用符号,项目没有报错,运行正常,说明这种脚本方式,符号是可以转译的,小于等于对应<=;大于等于对应>=;
错误代码(导致项目无法正常启动)
@Select({"<script>",
"select merchant_id as merchantId,\n" +
" (select name from tb_union_merchant where id = merchant_id) as merchantName,\n" +
" id as couponId,\n" +
" discount_price as couponPrice,\n" +
" discount_name as couponName,\n" +
" discount_start as couponStart,\n" +
" discount_rest as couponNum,\n" +
" DATE_FORMAT(activity_end_date, '%Y-%m-%d') as endDate,\n" +
" case\n" +
" when (discount_rest <= 0 and DATE_FORMAT(update_date,'%Y-%m-%d') >= date_sub(curdate(),interval 2 day)) then '3'\n" +
" when discount_rest >0 then '1'\n" +
" else '' end as status\n"+
" from tb_discount\n" +
"where del_flag = '0'\n" +
" and putaway_flag = '1'\n" +
" and vouchers_create = '3'\n" +
" <if test=\"limit != null and limit>0\" >\n" +
" LIMIT #{limit} OFFSET #{offset}\n" +
"</if>\n" +
" <if test=\"merchantId != null and merchantId!=''\" >\n" +
" merchant_id = #{merchantId}\n" +
"</if>\n"
, "</script>"})
List<Map> getDiscountListByPage(@Param("merchantId") String merchantId, @Param("offset") int offset, @Param("limit") int limit);
这版代码是我改的,我在原始代码加上了merchantId和limit字段的动态筛选判断,因为用到了动态SQL语句,所以遵照原始脚本语句,我就加上了脚本标识
<script>动态SQL语句</script>
tips:<script>后使用逗号或者加号连接都可以
然后项目启动时,就开始报错,而且声明了报错文件是DiscountMapper.xml,而不是对应的Mapper.java文件,导致好几个同事都在xml文件中去找问题,而忽略了Mapper.java文件。
这个就是个坑点了,所以需要更改注解文件的转译写法,但是还记得上一版运行正常的SQL脚本,里面同样没有对特殊符号进行转译,但是加上script脚本之后,这样的自动转译就失效了,这是第二个坑点。
修改后的正确代码(第一版)
@Select({"<script>" +
"select merchant_id as merchantId,\n" +
" (select name from tb_union_merchant where id = merchant_id) as merchantName,\n" +
" id as couponId,\n" +
" discount_price as couponPrice,\n" +
" discount_name as couponName,\n" +
" discount_start as couponStart,\n" +
" discount_rest as couponNum,\n" +
" DATE_FORMAT(activity_end_date, '%Y-%m-%d') as endDate,\n" +
" case\n" +
" when (discount_rest <= 0 and DATE_FORMAT(update_date,'%Y-%m-%d') >= date_sub(curdate(),interval 2 day)) then '3'\n" +
" when discount_rest >0 then '1'\n" +
" else '' end as status\n"+
" from tb_discount\n" +
"where del_flag = '0'\n" +
" and putaway_flag = '1'\n" +
" and vouchers_create = '3'\n" +
" <if test=\"limit != null and limit > 0\" >\n" +
" LIMIT #{limit} OFFSET #{offset}\n" +
"</if>\n" +
" <if test=\"merchantId != null and merchantId!=''\" >\n" +
" merchant_id = #{merchantId}\n" +
"</if>\n"
+ "</script>"})
List<Map> getDiscountListByPage(@Param("distinguish")String distinguish,@Param("merchantId") String merchantId, @Param("offset") int offset, @Param("limit") int limit);
这一版主要是将特殊符号换成转译写法
项目可以成功启动,问题解决。
但是对于注解带来的这些坑点,实在是有点可恨,我同事最终还是将这段注解方式的SQL语句换成了xml文件形式的,毕竟因为这个问题耽误了他那么久时间找原因。
修改后的正确代码(第二版)
- Mapper文件
/**
* 代金券列表查询
* @param merchantId
* @param offset
* @param limit
* @return
*/
List<Map> getDiscountListByPage(@Param("distinguish")String distinguish,@Param("merchantId") String merchantId, @Param("offset") int offset, @Param("limit") int limit);
- xml文件
<!-- Start 代金券列表查询 -->
<select id="getDiscountListByPage" resultType="Map">
select merchant_id as merchantId,
(select name from tb_union_merchant where id = dis.merchant_id) as merchantName,
id as couponId,
discount_price as couponPrice,
discount_name as couponName,
discount_start as couponStart,
discount_rest as couponNum,
DATE_FORMAT(activity_end_date, '%Y-%m-%d') as endDate,
case
when (discount_rest <= 0 and DATE_FORMAT(update_date,'%Y-%m-%d') >= date_sub(curdate(),interval 2 day)) then '3'
when discount_rest > 0 then '1'
else '' end
as status
from tb_discount dis
<where>
del_flag = '0'
and putaway_flag = '1'
and vouchers_create = '3'
<if test=" distinguish != null and distinguish != '' " >
and merchant_id in ((select t1.id
from tb_union_merchant t1
where t1.distinguish = #{distinguish}) )
</if>
<if test=" merchantId != null and merchantId != '' " >
and merchant_id = #{merchantId}
</if>
</where>
order by dis.create_date desc
<if test="limit != null and limit > 0 " >
LIMIT #{limit} OFFSET #{offset}
</if>
</select>
<!-- 代金券列表查询 End -->
以上是最终版,将注解方式改成了xml文件格式。
总结反思
- 坑点
- 1、包含script脚本的注解SQL语句,不支持自动对特殊符号转译,如大于小于等(建议无论注解还是xml,都采用标准的转译写法比较保险)
- 2、注解sql如果有报错,会指向对应的xml问价,误导报错文件位置(从这里也能看出,Mybatis底层在处理注解SQL文件时,还是会将它放到xml文件中处理)
- 总结
对于Mybatis的注解写法和xml写法,网上各有争议,当然也各有优势,不能在注解这儿踩到坑就一棍子打死,毕竟注解是xml的升级版,的确带来了很多便捷之处。但是还是建议复杂的SQL语句还是可以借助xml方式书写,因为代码维护也是个隐形工作量,哈哈,写代码也要有公德心呐。