在mybatis 的@Select注解中使用＜script＞导致的报错问题

原代码如下如下@Select({"<script>SELECT " +"IFNULL( sum( CASE WHEN dispose_type = 1 THEN 1 ELSE 0 END ), 0 ) AS misinformation, " +"IFNULL( sum( CASE WHEN dispose_type = 2 THEN 1 ELSE 0 END ), 0 ) AS fa

我是三良

7047人浏览 · 2021-10-19 14:09:06

我是三良 · 2021-10-19 14:09:06 发布

原代码如下如下

  @Select({"<script>SELECT " +
      "IFNULL( sum( CASE WHEN dispose_type = 1 THEN 1 ELSE 0 END ), 0 ) AS misinformation, " +
      "IFNULL( sum( CASE WHEN dispose_type = 2 THEN 1 ELSE 0 END ), 0 ) AS fault, " +
      "IFNULL( sum( CASE WHEN dispose_type = 3 THEN 1 ELSE 0 END ), 0 ) AS alarm, " +
      "IFNULL( sum( CASE WHEN dispose_type = 4 THEN 1 ELSE 0 END ), 0 ) AS others  " +
      "FROM device_event " +
      "WHERE   event_dispose_status = 1   " +
      "and DATE_SUB(CURDATE(), INTERVAL ${period} DAY) <= date(ts) " +
      "  and device_id in (select id from device where org = #{org}  " +
      "<if test = 'subSystem!=null'> and device_system_type=#{subSystem} </if>); </script>"})
  EventDisposeTypeStatisticsVM getDevcieHisEventStatBySubSystemAndPeriod(@Param("subSystem") Long subSystem,@Param("period") Long period,@Param("org") Long org);

使用的是mybatis 的@Select注解执行sql。代码执行时报以下错误。

因为要增加一个subSystem的子判断条件，所以在原sql基础上增加了<script> </script>标志。

结果一执行，代码就报错，程序启动不了，各种百度也找不到原因，因为整个语句看起来没有错。在其他地方也使用了相同写法，并没有报错出现。

主要报错信息如下。

ringframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'deviceEventService': Unsatisfied dependency expressed through field 'baseMapper'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'deviceEventMapper' defined in file [D:\code\zhxf012\build\classes\java\main\com\rydit\business\event\mapper\DeviceEventMapper.class]: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: org.apache.ibatis.builder.BuilderException: Error creating document instance.  Cause: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 428; 元素内容必须由格式正确的字符数据或标记组成。
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:643) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:130) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]
	at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:399) ~[spring-beans-5.2.8.RELEASE.jar:5.2.8.RELEASE]

后来发现<script>的作用是将整个sql语句转换为mybatis 中 xml中的写法，sql中不能存在大于< 或者小于>符号，这种符号会被认为是中括号的一部分。从而导致sql解析错误。而我的sql语句中有一个<= 判断条件。

将源代码中的 < 改为 <即可。

      "and DATE_SUB(CURDATE(), INTERVAL ${period} DAY) <= date(ts) "

主要修改的代码如下。

      "and DATE_SUB(CURDATE(), INTERVAL ${period} DAY) &lt;= date(ts) "

最终正确执行SQL代码如下：

@Select({"<script>SELECT " +
      "IFNULL( sum( CASE WHEN dispose_type = 1 THEN 1 ELSE 0 END ), 0 ) AS misinformation, " +
      "IFNULL( sum( CASE WHEN dispose_type = 2 THEN 1 ELSE 0 END ), 0 ) AS fault, " +
      "IFNULL( sum( CASE WHEN dispose_type = 3 THEN 1 ELSE 0 END ), 0 ) AS alarm, " +
      "IFNULL( sum( CASE WHEN dispose_type = 4 THEN 1 ELSE 0 END ), 0 ) AS others  " +
      "FROM device_event " +
      "WHERE   event_dispose_status = 1   " +
      "and DATE_SUB(CURDATE(), INTERVAL ${period} DAY) &lt;= date(ts) " +
      "  and device_id in (select id from device where org = #{org}  " +
      "<if test = 'subSystem!=null'> and device_system_type=#{subSystem} </if>); </script>"})
  EventDisposeTypeStatisticsVM getDevcieHisEventStatBySubSystemAndPeriod(@Param("subSystem") Long subSystem,@Param("period") Long period,@Param("org") Long org);

参考资料：Error creating document instance. Cause: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber - jiduoduo - 博客园在写mysql的sql语句时，会用到>=或者<= 那么<=这个会被解析为xml的语法 (org.xml.sax.SAXParseException) 这样就会报错。解决： &https://www.cnblogs.com/jiduoduo/p/14729050.html