mysql insert语句值包含单引号特殊字符解决方法

问题描述：前文章记录saveAll()批量新增执行自定义sql衍生问题：saveAll()批量新增执行自定义sql使用entityManager执行批量新增语句，执行sql语句时报错，报错信息如下：Caused by: org.hibernate.exception.SQLGrammarException: could not execute statementat org.hibernate.e

满船清梦压星河~

6293人浏览 · 2022-01-07 10:38:43

满船清梦压星河~ · 2022-01-07 10:38:43 发布

问题描述：

前文章记录saveAll()批量新增执行自定义sql衍生问题：
saveAll()批量新增执行自定义sql

使用entityManager执行批量新增语句，执行sql语句时报错，报错信息如下：

Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
	at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:204)
	at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1575)
	at org.hibernate.query.internal.NativeQueryImpl.doExecuteUpdate(NativeQueryImpl.java:274)
	at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1504)
	... 109 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '疫':少儿防护科普','0','1','2022-01-06T15:33','2022-01-06T16:00','111' at line 1

在这里插入图片描述
名称中有单引号（防’疫’:少儿防护科普）

原因分析：

某个字段中包含单引号等特殊符号，导致sql语句执行报错。

解决方案：

对字段特殊符号值进行处理，单引号 ’ 替换为两个单引号 ‘’

    public static String parseString(String name){
    	if(name.indexOf("'") > 0 ){
    		name = name.replaceAll("'", "''");
    	}
    	return name;
    }