@Select注解里添加动态sql语句
在@Select注解中使用<script>
、<if>
、<where>
等标签实现动态sql
注:为什么要使用这种方式,因为正常的SQL只能传参数,而不能直接拼接SQL语句,会报错。
@Select("<script>" +
"SELECT pr.*,lr.scxh,lr.bzxh,lr.wpmc,lr.gg,lr.djp,lr.dyph,lr.bb,lr.bc,lr.jz from package_record pr LEFT JOIN line_record lr ON pr.line_id = lr.id" +
"<where>"+
"<if test='ph != null and ph != \"\"'> and pr.ph = #{ph}</if>" +
"<if test='scxh != null and scxh != \"\"'> and lr.scxh = #{scxh}</if>" +
"<if test='bb != null and bb != \"\"'> and lr.bb = #{bb}</if>" +
"<if test='djp != null and djp != \"\"'> and lr.djp = #{djp}</if>" +
"<if test='xcpd02dxbzdhStatus != null'> and pr.xcpd02dxbzdh_status = #{xcpd02dxbzdhStatus}</if>" +
"<if test='bzjlhStatus != null'> and pr.bzjlh_status = #{bzjlhStatus}</if>" +
"<if test='dateRangeStart != null and dateRangeStart != \"\"'> and pr.create_time >= #{dateRangeStart}</if>" + // 大于>
"<if test='dateRangeEnd != null and dateRangeEnd != \"\"'> and pr.create_time <= #{dateRangeEnd}</if>" + // 小于<
"</where>"+
"limit #{offset},#{limit}" +
"</script>")
List<PackageRecordVo> getList(long offset, long limit,
String ph, String scxh, String bb, String djp, Integer xcpd02dxbzdhStatus, Integer bzjlhStatus, String dateRangeStart, String dateRangeEnd);
几个坑:
<if>
标签中的and不能大写;- if后面的
test
,不是自己的字段名 >
、<
等要使用转义字符,>
、<
对照如下:
原符号 : < <= > >= & ' "
替换符号: < <= > >= & ' "