背景了解:
mybatis框架下SQL查询一周内数据,由于mybatis对于>=和 <=的不识别,会误认为是括号所有导致了以下错误
报错信息:
<!-- 本周告警 -->
<select id="getweekalert" resultType="int">
select IFNULL(SUM(alert_sum),0) as alert_sum from tb_checkresults where
DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
</select>
Caused by: org.apache.ibatis.builder.BuilderException: Error creating document instance. Cause: org.xml.sax.SAXParseException; lineNumber: 77; columnNumber: 43; 元素内容必须由格式正确的字符数据或标记组成。
at org.apache.ibatis.parsing.XPathParser.createDocument(XPathParser.java:259)
at org.apache.ibatis.parsing.XPathParser.<init>(XPathParser.java:125)
at org.apache.ibatis.builder.xml.XMLMapperBuilder.<init>(XMLMapperBuilder.java:78)
at org.mybatis.spring.SqlSessionFactoryBean.buildSqlSessionFactory(SqlSessionFactoryBean.java:520)
... 73 common frames omitted
Caused by: org.xml.sax.SAXParseException: 元素内容必须由格式正确的字符数据或标记组成。
at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.createSAXParseException(ErrorHandlerWrapper.java:203)
at com.sun.org.apache.xerces.internal.util.ErrorHandlerWrapper.fatalError(ErrorHandlerWrapper.java:177)
at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:400)
at com.sun.org.apache.xerces.internal.impl.XMLErrorReporter.reportError(XMLErrorReporter.java:327)
at com.sun.org.apache.xerces.internal.impl.XMLScanner.reportFatalError(XMLScanner.java:1438)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.startOfMarkup(XMLDocumentFragmentScannerImpl.java:2637)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2735)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:606)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:510)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:848)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:777)
at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141)
at com.sun.org.apache.xerces.internal.parsers.DOMParser.parse(DOMParser.java:243)
at com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderImpl.parse(DocumentBuilderImpl.java:339)
at org.apache.ibatis.parsing.XPathParser.createDocument(XPathParser.java:257)
... 76 common frames omitted
解决办法:
(1) <![CDATA[带有>=和 <=的语句括起来]]>
<!-- 本周告警 -->
<select id="getweekalert" resultType="int">
select IFNULL(SUM(alert_sum),0) as alert_sum from tb_checkresults where
<![CDATA[ DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)]]>
</select>
(2)用<;代替小于号,用>;代替大于号
大于: DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)
小于: DATE_SUB(CURDATE(), INTERVAL 7 DAY) >= date(create_time)
找得到,看得懂,明确报错信息很重要
解决问题有多种办法,符合要求的才是最好的