解决SpringBoot+Druid+Mybatis Plus 执行MySQL批量插入，更新 报错的问题

最新推荐文章于 2024-04-09 15:39:15 发布
最新推荐文章于 2024-04-09 15:39:15 发布
阅读量6.1k 收藏 2
点赞数
分类专栏： Java 笔记 Spring Boot 文章标签： 批量插入 MySQL Springboot Druid UncategorizedSQLException
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yangxiao_hui/article/details/91375554
版权
笔记 同时被 3 个专栏收录
40 篇文章 2 订阅
订阅专栏
Java
8 篇文章 0 订阅
订阅专栏
Spring Boot
6 篇文章 0 订阅
订阅专栏

 

项目实际开发中，使用到了MySQL批量插入的语法，在MySQL中执行成功后，移植到项目时发生了问题，语句如下：

REPLACE INTO table1(
			id,
			works_fusion_id,
			statistics_date,
			chapter_number,
			new_chapter,
			works_hot,
			works_like_number,
			works_read_number,
			works_comment_number,
			works_collection_number
		)
		SELECT
			(SELECT UPPER(REPLACE(result.id, '-', ''))) id,
			result.works_fusion_id,
			STR_TO_DATE(IFNULL(result.statistics_date,'1990-01-01'),'%Y-%m-%d') statistics_date,
			IFNULL(result.chapter_number,0) chapter_number,
			IFNULL(result.new_chapter,'无数据') new_chapter,
			IFNULL(result.works_hot,0) works_hot,
			IFNULL(result.works_like_number,0) works_like_number,
			IFNULL(result.works_read_number,0) works_read_number,
			IFNULL(result.works_comment_number,0) works_comment_number,
			IFNULL(result.works_collection_number,0) works_collection_number
		FROM(
			SELECT
				UUID() id,
				swf.id as works_fusion_id,
				DATE_FORMAT(sdr.update_time,'%Y-%m-%d') statistics_date,
				COUNT(DISTINCTROW sccount.id) chapter_number,
				sci.title new_chapter,
				sum(DISTINCT sdr.works_hot) works_hot,
                sum(DISTINCT sdr.works_read_number) works_read_number,
                sum(DISTINCT sdr.works_like_number) works_like_number,
                sum(DISTINCT sdr.works_comment_number) works_comment_number,
                sum(DISTINCT sdr.works_collection_number) works_collection_number
			FROM table2 swf
			INNER JOIN table3 sw ON sw.works_fusion_id=swf.id
			GROUP BY swf.id
		)result

项目中执行SQL报错内容：

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@10d97b64] was not registered for synchronization because synchronization is not active
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@3bbdf835] will not be managed by Spring
==>  Preparing: REPLACE INTO sqa_day_data_statistics( id, works_fusion_id, statistics_date, chapter_number, new_chapter, works_hot, works_like_number, works_read_number, works_comment_number, works_collection_number ) SELECT (SELECT UPPER(REPLACE(result.id, '-', ''))) id, result.works_fusion_id, STR_TO_DATE(IFNULL(result.statistics_date,'1990-01-01'),'%Y-%m-%d') statistics_date, IFNULL(result.chapter_number,0) chapter_number, IFNULL(result.new_chapter,'无数据') new_chapter, IFNULL(result.works_hot,0) works_hot, IFNULL(result.works_like_number,0) works_like_number, IFNULL(result.works_read_number,0) works_read_number, IFNULL(result.works_comment_number,0) works_comment_number, IFNULL(result.works_collection_number,0) works_collection_number FROM( SELECT UUID() id, swf.id as works_fusion_id, DATE_FORMAT(sdr.update_time,'%Y-%m-%d') statistics_date, COUNT(DISTINCTROW sccount.id) chapter_number, sci.title new_chapter, sum(DISTINCT sdr.works_hot) works_hot, sum(DISTINCT sdr.works_read_number) works_read_number, sum(DISTINCT sdr.works_like_number) works_like_number, sum(DISTINCT sdr.works_comment_number) works_comment_number, sum(DISTINCT sdr.works_collection_number) works_collection_number FROM sqa_works_fusion swf INNER JOIN sqa_works sw ON sw.works_fusion_id=swf.id INNER JOIN sqa_data_record sdr ON sdr.works_id=sw.id AND sdr.update_time = ( SELECT MAX(update_time) FROM sqa_data_record WHERE works_id = sdr.works_id ) INNER JOIN sqa_chapter_info sci ON sci.works_id=sw.id AND sci.`no`=( SELECT MAX(`no`) FROM sqa_chapter_info WHERE works_id =sci.works_id ) INNER JOIN sqa_chapter_info sccount on sccount.works_id=sw.id GROUP BY swf.id )result 
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@10d97b64]
2019-06-10 14:25:24.732  INFO 2240 --- [           main] o.s.b.f.xml.XmlBeanDefinitionReader      : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2019-06-10 14:25:24.758  INFO 2240 --- [           main] o.s.jdbc.support.SQLErrorCodesFactory    : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]

org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: sql injection violation, syntax error: syntax error, expect RPAREN, actual IDENTIFIER pos 949, line 29, column 23, token IDENTIFIER sccount : REPLACE INTO sqa_day_data_statistics(
			id,
			works_fusion_id,
			statistics_date,
			chapter_number,
			new_chapter,
			works_hot,
			works_like_number,
			works_read_number,
			works_comment_number,
			works_collection_number
		)
		SELECT
			(SELECT UPPER(REPLACE(result.id, '-', ''))) id,
			result.works_fusion_id,
			STR_TO_DATE(IFNULL(result.statistics_date,'1990-01-01'),'%Y-%m-%d') statistics_date,
			IFNULL(result.chapter_number,0) chapter_number,
			IFNULL(result.new_chapter,'无数据') new_chapter,
			IFNULL(result.works_hot,0) works_hot,
			IFNULL(result.works_like_number,0) works_like_number,
			IFNULL(result.works_read_number,0) works_read_number,
			IFNULL(result.works_comment_number,0) works_comment_number,
			IFNULL(result.works_collection_number,0) works_collection_number
		FROM(
			SELECT
				UUID() id,
				swf.id as works_fusion_id,
				DATE_FORMAT(sdr.update_time,'%Y-%m-%d') statistics_date,
				COUNT(DISTINCTROW sccount.id) chapter_number,
				sci.title new_chapter,
				sum(DISTINCT sdr.works_hot) works_hot,
                sum(DISTINCT sdr.works_read_number) works_read_number,
                sum(DISTINCT sdr.works_like_number) works_like_number,
                sum(DISTINCT sdr.works_comment_number) works_comment_number,
                sum(DISTINCT sdr.works_collection_number) works_collection_number
			FROM sqa_works_fusion swf
			INNER JOIN sqa_works sw ON sw.works_fusion_id=swf.id
			INNER JOIN sqa_data_record sdr ON sdr.works_id=sw.id AND sdr.update_time = (
				SELECT MAX(update_time) FROM sqa_data_record WHERE works_id = sdr.works_id
			)
			INNER JOIN sqa_chapter_info sci ON sci.works_id=sw.id AND sci.`no`=(
				SELECT MAX(`no`) FROM sqa_chapter_info WHERE works_id =sci.works_id
			)
			INNER JOIN sqa_chapter_info sccount on sccount.works_id=sw.id
			GROUP BY swf.id
		)result
### The error may exist in file [/Users/yangxiaohui/HmProject/hm-spss/target/classes/mapper/spss/SqaDayDataStatisticsMapper.xml]
### The error may involve com.hm.spss.mapper.spss.SqaDayDataStatisticsMapper.insertWorksDayDataList
### The error occurred while executing an update
### SQL: REPLACE INTO sqa_day_data_statistics(    id,    works_fusion_id,    statistics_date,    chapter_number,    new_chapter,    works_hot,    works_like_number,    works_read_number,    works_comment_number,    works_collection_number   )   SELECT    (SELECT UPPER(REPLACE(result.id, '-', ''))) id,    result.works_fusion_id,    STR_TO_DATE(IFNULL(result.statistics_date,'1990-01-01'),'%Y-%m-%d') statistics_date,    IFNULL(result.chapter_number,0) chapter_number,    IFNULL(result.new_chapter,'无数据') new_chapter,    IFNULL(result.works_hot,0) works_hot,    IFNULL(result.works_like_number,0) works_like_number,    IFNULL(result.works_read_number,0) works_read_number,    IFNULL(result.works_comment_number,0) works_comment_number,    IFNULL(result.works_collection_number,0) works_collection_number   FROM(    SELECT     UUID() id,     swf.id as works_fusion_id,     DATE_FORMAT(sdr.update_time,'%Y-%m-%d') statistics_date,     COUNT(DISTINCTROW sccount.id) chapter_number,     sci.title new_chapter,     sum(DISTINCT sdr.works_hot) works_hot,                 sum(DISTINCT sdr.works_read_number) works_read_number,                 sum(DISTINCT sdr.works_like_number) works_like_number,                 sum(DISTINCT sdr.works_comment_number) works_comment_number,                 sum(DISTINCT sdr.works_collection_number) works_collection_number    FROM sqa_works_fusion swf    INNER JOIN sqa_works sw ON sw.works_fusion_id=swf.id    INNER JOIN sqa_data_record sdr ON sdr.works_id=sw.id AND sdr.update_time = (     SELECT MAX(update_time) FROM sqa_data_record WHERE works_id = sdr.works_id    )    INNER JOIN sqa_chapter_info sci ON sci.works_id=sw.id AND sci.`no`=(     SELECT MAX(`no`) FROM sqa_chapter_info WHERE works_id =sci.works_id    )    INNER JOIN sqa_chapter_info sccount on sccount.works_id=sw.id    GROUP BY swf.id   )result
### Cause: java.sql.SQLException: sql injection violation, syntax error: syntax error, expect RPAREN, actual IDENTIFIER pos 949, line 29, column 23, token IDENTIFIER sccount : REPLACE INTO sqa_day_data_statistics(
			id,
			works_fusion_id,
			statistics_date,
			chapter_number,
			new_chapter,
			works_hot,
			works_like_number,
			works_read_number,
			works_comment_number,
			works_collection_number
		)
		SELECT
			(SELECT UPPER(REPLACE(result.id, '-', ''))) id,
			result.works_fusion_id,
			STR_TO_DATE(IFNULL(result.statistics_date,'1990-01-01'),'%Y-%m-%d') statistics_date,
			IFNULL(result.chapter_number,0) chapter_number,
			IFNULL(result.new_chapter,'无数据') new_chapter,
			IFNULL(result.works_hot,0) works_hot,
			IFNULL(result.works_like_number,0) works_like_number,
			IFNULL(result.works_read_number,0) works_read_number,
			IFNULL(result.works_comment_number,0) works_comment_number,
			IFNULL(result.works_collection_number,0) works_collection_number
		FROM(
			SELECT
				UUID() id,
				swf.id as works_fusion_id,
				DATE_FORMAT(sdr.update_time,'%Y-%m-%d') statistics_date,
				COUNT(DISTINCTROW sccount.id) chapter_number,
				sci.title new_chapter,
				sum(DISTINCT sdr.works_hot) works_hot,
                sum(DISTINCT sdr.works_read_number) works_read_number,
                sum(DISTINCT sdr.works_like_number) works_like_number,
                sum(DISTINCT sdr.works_comment_number) works_comment_number,
                sum(DISTINCT sdr.works_collection_number) works_collection_number
			FROM sqa_works_fusion swf
			INNER JOIN sqa_works sw ON sw.works_fusion_id=swf.id
			INNER JOIN sqa_data_record sdr ON sdr.works_id=sw.id AND sdr.update_time = (
				SELECT MAX(update_time) FROM sqa_data_record WHERE works_id = sdr.works_id
			)
			INNER JOIN sqa_chapter_info sci ON sci.works_id=sw.id AND sci.`no`=(
				SELECT MAX(`no`) FROM sqa_chapter_info WHERE works_id =sci.works_id
			)
			INNER JOIN sqa_chapter_info sccount on sccount.works_id=sw.id
			GROUP BY swf.id
		)result
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; sql injection violation, syntax error: syntax error, expect RPAREN, actual IDENTIFIER pos 949, line 29, column 23, token IDENTIFIER sccount : REPLACE INTO sqa_day_data_statistics(
			id,
			works_fusion_id,
			statistics_date,
			chapter_number,
			new_chapter,
			works_hot,
			works_like_number,
			works_read_number,
			works_comment_number,
			works_collection_number
		)
		SELECT
			(SELECT UPPER(REPLACE(result.id, '-', ''))) id,
			result.works_fusion_id,
			STR_TO_DATE(IFNULL(result.statistics_date,'1990-01-01'),'%Y-%m-%d') statistics_date,
			IFNULL(result.chapter_number,0) chapter_number,
			IFNULL(result.new_chapter,'无数据') new_chapter,
			IFNULL(result.works_hot,0) works_hot,
			IFNULL(result.works_like_number,0) works_like_number,
			IFNULL(result.works_read_number,0) works_read_number,
			IFNULL(result.works_comment_number,0) works_comment_number,
			IFNULL(result.works_collection_number,0) works_collection_number
		FROM(
			SELECT
				UUID() id,
				swf.id as works_fusion_id,
				DATE_FORMAT(sdr.update_time,'%Y-%m-%d') statistics_date,
				COUNT(DISTINCTROW sccount.id) chapter_number,
				sci.title new_chapter,
				sum(DISTINCT sdr.works_hot) works_hot,
                sum(DISTINCT sdr.works_read_number) works_read_number,
                sum(DISTINCT sdr.works_like_number) works_like_number,
                sum(DISTINCT sdr.works_comment_number) works_comment_number,
                sum(DISTINCT sdr.works_collection_number) works_collection_number
			FROM sqa_works_fusion swf
			INNER JOIN sqa_works sw ON sw.works_fusion_id=swf.id
			INNER JOIN sqa_data_record sdr ON sdr.works_id=sw.id AND sdr.update_time = (
				SELECT MAX(update_time) FROM sqa_data_record WHERE works_id = sdr.works_id
			)
			INNER JOIN sqa_chapter_info sci ON sci.works_id=sw.id AND sci.`no`=(
				SELECT MAX(`no`) FROM sqa_chapter_info WHERE works_id =sci.works_id
			)
			INNER JOIN sqa_chapter_info sccount on sccount.works_id=sw.id
			GROUP BY swf.id
		)result; nested exception is java.sql.SQLException: sql injection violation, syntax error: syntax error, expect RPAREN, actual IDENTIFIER pos 949, line 29, column 23, token IDENTIFIER sccount : REPLACE INTO sqa_day_data_statistics(
			id,
			works_fusion_id,
			statistics_date,
			chapter_number,
			new_chapter,
			works_hot,
			works_like_number,
			works_read_number,
			works_comment_number,
			works_collection_number
		)
		SELECT
			(SELECT UPPER(REPLACE(result.id, '-', ''))) id,
			result.works_fusion_id,
			STR_TO_DATE(IFNULL(result.statistics_date,'1990-01-01'),'%Y-%m-%d') statistics_date,
			IFNULL(result.chapter_number,0) chapter_number,
			IFNULL(result.new_chapter,'无数据') new_chapter,
			IFNULL(result.works_hot,0) works_hot,
			IFNULL(result.works_like_number,0) works_like_number,
			IFNULL(result.works_read_number,0) works_read_number,
			IFNULL(result.works_comment_number,0) works_comment_number,
			IFNULL(result.works_collection_number,0) works_collection_number
		FROM(
			SELECT
				UUID() id,
				swf.id as works_fusion_id,
				DATE_FORMAT(sdr.update_time,'%Y-%m-%d') statistics_date,
				COUNT(DISTINCTROW sccount.id) chapter_number,
				sci.title new_chapter,
				sum(DISTINCT sdr.works_hot) works_hot,
                sum(DISTINCT sdr.works_read_number) works_read_number,
                sum(DISTINCT sdr.works_like_number) works_like_number,
                sum(DISTINCT sdr.works_comment_number) works_comment_number,
                sum(DISTINCT sdr.works_collection_number) works_collection_number
			FROM sqa_works_fusion swf
			INNER JOIN sqa_works sw ON sw.works_fusion_id=swf.id
			INNER JOIN sqa_data_record sdr ON sdr.works_id=sw.id AND sdr.update_time = (
				SELECT MAX(update_time) FROM sqa_data_record WHERE works_id = sdr.works_id
			)
			INNER JOIN sqa_chapter_info sci ON sci.works_id=sw.id AND sci.`no`=(
				SELECT MAX(`no`) FROM sqa_chapter_info WHERE works_id =sci.works_id
			)
			INNER JOIN sqa_chapter_info sccount on sccount.works_id=sw.id
			GROUP BY swf.id
		)result

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:90)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy92.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:64)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61)
	at com.sun.proxy.$Proxy109.insertWorksDayDataList(Unknown Source)
	at com.hm.spss.RlTemplateApplicationTests.testsql(RlTemplateApplicationTests.java:70)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.sql.SQLException: sql injection violation, syntax error: syntax error, expect RPAREN, actual IDENTIFIER pos 949, line 29, column 23, token IDENTIFIER sccount : REPLACE INTO sqa_day_data_statistics(
			id,
			works_fusion_id,
			statistics_date,
			chapter_number,
			new_chapter,
			works_hot,
			works_like_number,
			works_read_number,
			works_comment_number,
			works_collection_number
		)
		SELECT
			(SELECT UPPER(REPLACE(result.id, '-', ''))) id,
			result.works_fusion_id,
			STR_TO_DATE(IFNULL(result.statistics_date,'1990-01-01'),'%Y-%m-%d') statistics_date,
			IFNULL(result.chapter_number,0) chapter_number,
			IFNULL(result.new_chapter,'无数据') new_chapter,
			IFNULL(result.works_hot,0) works_hot,
			IFNULL(result.works_like_number,0) works_like_number,
			IFNULL(result.works_read_number,0) works_read_number,
			IFNULL(result.works_comment_number,0) works_comment_number,
			IFNULL(result.works_collection_number,0) works_collection_number
		FROM(
			SELECT
				UUID() id,
				swf.id as works_fusion_id,
				DATE_FORMAT(sdr.update_time,'%Y-%m-%d') statistics_date,
				COUNT(DISTINCTROW sccount.id) chapter_number,
				sci.title new_chapter,
				sum(DISTINCT sdr.works_hot) works_hot,
                sum(DISTINCT sdr.works_read_number) works_read_number,
                sum(DISTINCT sdr.works_like_number) works_like_number,
                sum(DISTINCT sdr.works_comment_number) works_comment_number,
                sum(DISTINCT sdr.works_collection_number) works_collection_number
			FROM sqa_works_fusion swf
			INNER JOIN sqa_works sw ON sw.works_fusion_id=swf.id
			INNER JOIN sqa_data_record sdr ON sdr.works_id=sw.id AND sdr.update_time = (
				SELECT MAX(update_time) FROM sqa_data_record WHERE works_id = sdr.works_id
			)
			INNER JOIN sqa_chapter_info sci ON sci.works_id=sw.id AND sci.`no`=(
				SELECT MAX(`no`) FROM sqa_chapter_info WHERE works_id =sci.works_id
			)
			INNER JOIN sqa_chapter_info sccount on sccount.works_id=sw.id
			GROUP BY swf.id
		)result
	at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:806)
	at com.alibaba.druid.wall.WallFilter.connection_prepareStatement(WallFilter.java:259)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
	at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:930)
	at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:122)
	at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:568)
	at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:341)
	at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:349)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
	at com.sun.proxy.$Proxy137.prepareStatement(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
	at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy136.prepare(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:132)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy136.prepare(Unknown Source)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 34 more
Caused by: com.alibaba.druid.sql.parser.ParserException: syntax error, expect RPAREN, actual IDENTIFIER pos 949, line 29, column 23, token IDENTIFIER sccount
	at com.alibaba.druid.sql.parser.SQLExprParser.accept(SQLExprParser.java:2742)
	at com.alibaba.druid.sql.parser.SQLExprParser.parseAggregateExpr(SQLExprParser.java:1520)
	at com.alibaba.druid.sql.parser.SQLExprParser.methodRest(SQLExprParser.java:1124)
	at com.alibaba.druid.sql.parser.SQLExprParser.parseSelectItem(SQLExprParser.java:3339)
	at com.alibaba.druid.sql.parser.SQLSelectParser.parseSelectList(SQLSelectParser.java:675)
	at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:181)
	at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:236)
	at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:88)
	at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.parseTableSource(MySqlSelectParser.java:246)
	at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.parseFrom(MySqlSelectParser.java:89)
	at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:193)
	at com.alibaba.druid.sql.parser.SQLSelectParser.query(SQLSelectParser.java:236)
	at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:88)
	at com.alibaba.druid.sql.parser.SQLExprParser.primary(SQLExprParser.java:655)
	at com.alibaba.druid.sql.dialect.mysql.parser.MySqlExprParser.primary(MySqlExprParser.java:198)
	at com.alibaba.druid.sql.parser.SQLExprParser.expr(SQLExprParser.java:154)
	at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseReplace(MySqlStatementParser.java:2332)
	at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseStatementListDialect(MySqlStatementParser.java:900)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:517)
	at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:182)
	at com.alibaba.druid.wall.WallProvider.checkInternal(WallProvider.java:624)
	at com.alibaba.druid.wall.WallProvider.check(WallProvider.java:578)
	at com.alibaba.druid.wall.WallFilter.checkInternal(WallFilter.java:793)
	... 75 more

 

后来查阅Druid的文档以及网上的一些资料发现，Druid对于这种语法，会识别为两个语句  即：REPLACE INTO xxx 为一句  后面的 SELECT ... 也是一句独立的SQL，而Druid默认的过滤器 `WallFilter` 里默认会拦截多条SQL一起执行的情况，并抛出异常。

解决方案：配置Druid拦截器，开启 是否允许一次执行多条语句的配置：

 

package com.hm.spss.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description:
 */
@Configuration
public class DruidDBConfig {
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        //reg.addInitParameter("allow", "127.0.0.1"); //白名单
        reg.addInitParameter("resetEnable","false");
        return reg;
    }
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        Map<String, String> initParams = new HashMap<String, String>();
        //设置忽略请求

        initParams.put("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.setInitParameters(initParams);
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
        filterRegistrationBean.addInitParameter("principalSessionName","");
        filterRegistrationBean.addInitParameter("aopPatterns","com.example.demo.service");
        filterRegistrationBean.addUrlPatterns("/*");
        return filterRegistrationBean;
    }

    // 配置事物管理器
    @Bean
    public DataSourceTransactionManager transactionManager(){
        return new DataSourceTransactionManager(new DruidDataSource());
    }

    @Bean
    public WallFilter wallFilter(){
        WallFilter wallFilter = new WallFilter();
        wallFilter.setConfig(wallConfig());
        return wallFilter;
    }

    @Bean
    public WallConfig wallConfig(){
        WallConfig wallConfig = new WallConfig();
        wallConfig.setMultiStatementAllow(true);//允许一次执行多条语句
        wallConfig.setNoneBaseStatementAllow(true);//是否允许非以上基本语句的其他语句
        wallConfig.setStrictSyntaxCheck(false);//是否进行严格的语法检测
        return wallConfig;
    }



}

 

清晨先生
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
SpringBoot + Apache Shiro + Mybatis Plus + Thymeleaf 实现的内容管理系统
04-28
采用SpringBoot + Apache Shiro + Mybatis Plus + Thymeleaf 实现的内容管理系统(附带权限管理)，是搭建博客、网站的不二之选。 技术栈：Spring Boot、Apache Shiro、MyBatis-Plus、Alibaba Druid、Redis、MySQL、...
雪花id一直为空指针异常 MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.
m0_49191104的博客
11-08 852
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error updating database. Cause: java.lang.NullPointerException ### The error may exist in edu/znzz/easypoint/dao/DishesTasteMapper.java
spring thread 更新数据库调用updateById报错
wangooo的博客
07-27 4781
启动一个线程，每隔一段时间，调用外部接口后，将数据更新到数据库。 public class RefAnalysisResultThread implements Runnable { private volatile boolean someCondition = true; private IAnalysisService analysisService; public RefAnalysisResultThread() { this.analysisSer
mysql 使用Mybatis框架，批量更新数据时报错
qqgg0216的博客
04-09 221
其中，collection="list" 表示传入的参数是列表类型，item="user" 其中的 user 是自定义的名称(你也可以指定item="item" 这样的形式)，表示单个更新的形参对象(Java 对象 User 有很多属性和表的字段对应)，separator=";如果是批量插入，分隔符参数则为, ，这也是固定的格式。在反复确认 SQL 语句没有写错的情况下，经过一番折腾才知道：如果使用 Mybatis 框架对 MySQL 数据库进行批量更新或者插入操作，需要在连接数据库的 URL 加上。
MyBatis+MySql Insert操作失败解决办法
jinhuawu的专栏
05-21 1346
初次接触MyBatis，遇到数据库使用MySql时Insert失败的问题（select时正常）。在日志中复制SQL语句是可以执行的，不知道什么原因，怀疑是事务问题。 google后看到一哥们也遇到了此问题，不过他找到了一个解决办法，下面是他原话(他的症状和我遇到的一样，不过我不知道哪里有问题，他知道是数据库引擎的问题~): The Engine in MySql is innoDB. Whic
Error updating database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException:
spirit_captive的博客
12-01 2349
但这里我很明确我错误和上面导致没有任何关系，这里我review了一下我的代码，最后找出了问题所在，原因是我修改时，用的lambdaQueryWrapper没有指定eq,具体要修改的行数。这个错误通常表示你尝试更新一个不允许更新的表。翻译是：不允许你对这张表进行修改。把具体修改的行数加上就好了。可以由于以下几个原因导致。
MyBatis-Plus调用插入Insert()方法报错
weixin_41803476的博客
08-23 5544
调用Insert方法竟然报错了，整得一脸懵逼，报错信息如下： Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: ### Error updating database. Ca...
Mybatis Plus 更新问题
编码日常
04-24 1264
mapper.update() 当数据表里没有相应记录时，会自动增加记录。因此要用此方法更新数据库数据需要先进行数据是否存在查询 /** * <p> * 根据 whereEntity 条件，更新记录 * </p> * * @param entity 实体对象(更新的数据， name='Abc') * @param...
SpringBoot + mybatis-plus + druid 实现mySql与Orcl双数据源
01-12
SpringBoot + mybatis-plus + druid 实现mySql与Orcl双数据源
CRM客户管理系统（Springboot+MySQL+mybatis-plus+druid+swagger2+layui
最新发布
05-17
该项目利用了基于springboot + vue + mysql的开发模式框架实现的课设系统，包括了项目的源码资源、sql文件、相关指引文档等等。 【项目资源】：包含前端、后端、移动开发、操作系统、人工智能、物联网、信息化管理...
springboot+mybatis3+druid+postgresql
05-08
springboot+druid+mybatis+postgresql框架搭建，使用mybatis-generator自动生成pojo+mapper+dao
Spirng Boot+Shiro+Druid+Mybatis Plus+Mysql搭建基础框架实现登录用户认证
07-31
Spirng Boot+Shiro+Druid+Mybatis Plus+Mysql搭建基础框架实现登录用户认证 可直接下载运行
解决报错：SQLException: Field 'sort_order' doesn't have a default value；使用MybatisPlus自动插入相应的字段时应该注意，
weixin_44797327的博客
11-29 855
使用MybatisPlus自动插入相应的字段时应该注意 报错信息： Error updating database. Cause: java.sql.SQLException: Field ‘sort_order’ doesn’t have a default value The error may involve com.ultrapower.life.mapper.WxMainDetailM...
MYbatisPlus save或Update CastException 整整一个半小时的BUG,居然在这儿发现了！！
weixin_52445706的博客
02-24 631
MYbatisPlussave或UpdateCastException SecurityContextHolder
使用mybatis向插入mysql数据库插不进数据且不报错
qq_28927539的博客
05-14 5060
原因：session没有进行commit();
mysqlplus 批量插入_解决SpringBoot+Druid+Mybatis Plus 执行MySQL批量插入，更新 报错的问题...
weixin_39727934的博客
12-20 730
项目实际开发中，使用到了MySQL批量插入的语法，在MySQL中执行成功后，移植到项目时发生了问题，语句如下：REPLACE INTO table1(id,works_fusion_id,statistics_date,chapter_number,new_chapter,works_hot,works_like_number,works_read_number,works_comment_num...
mybatis遇到的问题
qq_38275941的博客
03-13 3507
使用mybatis在连接数据库遇到一个异常， `Exception in thread “main” org.apache.ibatis.exceptions.PersistenceException: Error updating database. Cause: java.sql.SQLException: The server time zone value ‘Öйú±ê׼ʱ¼ä’ ...
使用mybatis-plus时候报错Error updating database.subject_parent_id‘ doesn‘t have a default value
weixin_42054567的博客
03-13 625
使用mybatis-plus时候报错Error updating database. Cause: java.sql.SQLException: Field ‘subject_parent_id’ doesn’t have a default value 误代码如图: org.springframework.dao.DataIntegrityViolationException: Error updating database. Cause: java.sql.SQLException: Field ‘
mybatisplus实现xml中批量插入出现java.lang.NullPointerException
wltsysterm的博客
07-05 5333
mybatis-plus框架，是很多后端开发工程师的首选，今天这边小记一个经验问题。 因为业务需要哈，需要一段比较复杂的sql语句，但是大致实现的逻辑就是insert into table1 select col1,col2 from table2这样。 因为语法比较复杂，没办法直接使用java代码来拼凑，就把sql语句直接写到了mapper.xml里头。一切都觉得那么完美。 但是运行起来，...
druid 多数据源_Springboot+mybatis(plus)+druid多数据源
05-14
在Spring Boot项目中使用MyBatis Plus和Druid多数据源的步骤如下： 1. 添加依赖 在`pom.xml`文件中添加以下依赖： ```xml <!-- MyBatis Plus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.3.1</version> </dependency> <!-- Druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.6</version> </dependency> ``` 2. 配置Druid数据源 在`application.yml`中添加Druid数据源的配置： ```yaml spring: datasource: # 主数据源 druid: url: jdbc:mysql://localhost:3306/main_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8 username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver # Druid配置 initialSize: 5 minIdle: 5 maxActive: 20 testOnBorrow: false testOnReturn: false testWhileIdle: true timeBetweenEvictionRunsMillis: 60000 validationQuery: SELECT 1 FROM DUAL # 从数据源 druid2: url: jdbc:mysql://localhost:3306/sub_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8 username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver # Druid配置 initialSize: 5 minIdle: 5 maxActive: 20 testOnBorrow: false testOnReturn: false testWhileIdle: true timeBetweenEvictionRunsMillis: 60000 validationQuery: SELECT 1 FROM DUAL ``` 3. 配置MyBatis Plus 在`application.yml`中添加MyBatis Plus的配置： ```yaml mybatis-plus: # 主数据源配置 mapper-locations: classpath:mapper/main/*.xml type-aliases-package: com.example.main.entity global-config: db-config: id-type: auto field-strategy: not_empty logic-delete-value: 1 logic-not-delete-value: 0 configuration: map-underscore-to-camel-case: true log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 从数据源配置 multi-datasource: main: mapper-locations: classpath:mapper/main/*.xml type-aliases-package: com.example.main.entity sub: mapper-locations: classpath:mapper/sub/*.xml type-aliases-package: com.example.sub.entity ``` 4. 配置数据源路由 在`com.example.config`包下创建`DynamicDataSourceConfig`类，用于配置数据源路由： ```java @Configuration public class DynamicDataSourceConfig { @Bean @ConfigurationProperties("spring.datasource.druid") public DataSource mainDataSource() { return DruidDataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.druid2") public DataSource subDataSource() { return DruidDataSourceBuilder.create().build(); } @Bean public DataSource dynamicDataSource() { DynamicDataSource dynamicDataSource = new DynamicDataSource(); Map<Object, Object> dataSourceMap = new HashMap<>(2); dataSourceMap.put("main", mainDataSource()); dataSourceMap.put("sub", subDataSource()); // 将主数据源作为默认数据源 dynamicDataSource.setDefaultTargetDataSource(mainDataSource()); dynamicDataSource.setTargetDataSources(dataSourceMap); return dynamicDataSource; } @Bean public SqlSessionFactory sqlSessionFactory() throws Exception { MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(dynamicDataSource()); sqlSessionFactoryBean.setTypeAliasesPackage("com.example.main.entity"); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/main/*.xml")); return sqlSessionFactoryBean.getObject(); } @Bean public SqlSessionTemplate sqlSessionTemplate() throws Exception { return new SqlSessionTemplate(sqlSessionFactory()); } } ``` 5. 配置数据源切换 在`com.example.config`包下创建`DynamicDataSource`类，用于实现数据源切换： ```java public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DataSourceContextHolder.getDataSource(); } } ``` 在`com.example.config`包下创建`DataSourceContextHolder`类，用于存储当前数据源： ```java public class DataSourceContextHolder { private static final ThreadLocal<String> DATASOURCE_CONTEXT_HOLDER = new ThreadLocal<>(); public static void setDataSource(String dataSource) { DATASOURCE_CONTEXT_HOLDER.set(dataSource); } public static String getDataSource() { return DATASOURCE_CONTEXT_HOLDER.get(); } public static void clearDataSource() { DATASOURCE_CONTEXT_HOLDER.remove(); } } ``` 在`com.example.aop`包下创建`DataSourceAspect`类，用于切换数据源： ```java @Aspect @Component public class DataSourceAspect { @Pointcut("@annotation(com.example.annotation.DataSource)") public void dataSourcePointCut() { } @Before("dataSourcePointCut()") public void before(JoinPoint joinPoint) { MethodSignature signature = (MethodSignature) joinPoint.getSignature(); DataSource dataSource = signature.getMethod().getAnnotation(DataSource.class); if (dataSource != null) { String value = dataSource.value(); DataSourceContextHolder.setDataSource(value); } } @After("dataSourcePointCut()") public void after(JoinPoint joinPoint) { DataSourceContextHolder.clearDataSource(); } } ``` 6. 使用多数据源 在需要使用从数据源的方法上加上`@DataSource("sub")`注解，如： ```java @Service public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public List<User> listUsers() { DataSourceContextHolder.setDataSource("sub"); List<User> users = userMapper.selectList(null); DataSourceContextHolder.clearDataSource(); return users; } } ``` 这样就完成了Spring Boot项目中使用MyBatis Plus和Druid多数据源的配置。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

清晨先生

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值