Java SQLSyntaxErrorException: You have an error in your SQL syntax问题4种情况解决(SQL、JPA、MyBatis)
1 问题描述
Caused by: java.sql.SQLSyntaxErrorException: 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 'insert into t_salary_2021_01( DETAILID) values('52dd5ce4a5794573b9b202a2f00788e4' at line 2
2 问题分析
2.1 SQL
1、SQL语句中出现了子查询,但是这个子查询里面存在子查询外面的表字段。
select t1.*
from sys_dept t1
left join (
select distinct t4.group_id
from sys_user_role t3
left join sys_role t4 on t4.role_id=t3.role_id and status='1' and t4.group_id=t1.group_id
where t3.user_id='1'
) t5 on t5.group_id=t1.group_id
解决办法:子查询里面不存在子查询外面的表字段。
select t1.*
from sys_dept t1
where t1.dept_id in (
select distinct t4.dept_id
from sys_dept t1
left join sys_user_role t3 on t3.user_id = t2.user_id and user_id='1'
left join sys_role t4 on t4.role_id=t3.role_id and status='1' and t4.group_id=t1.group_id
)
2、MySQL数据库使用了SQL Server数据库才能使用的stuff for xml path语句。
select stuff((
select * from ','+ area_id
FROM dbo.T_duty_city T1
WHERE T1.id = T0.id FOR XML PATH ('')
),1,1,'') area_id
from t_st T0
解决办法:使用group_concat()实现把多条记录的某个字段拼成一条记录的字段值,并且用,号隔开。
select (
select GROUP_CONCAT(area_id)
from t_duty_city T1
where T1.id =T0.id
) area_id
from t_st T0
3、MySQL不支持cast(字段名 as varchar(50)),只有SQL Server 支持cast(字段名 as varchar(50))支持。
select cast(username as varchar(50)) username from t_user;
解决办法: 将cast(字段名 as varchar(50))改为cast(字段名 as char)。
select cast(username as char) username from t_user;
4、表字段名为mysql关键字,比如describe、maxValue。
解决办法:
(1)describe前面加表名。
select t1.describe
from t_users t1
(2) describe两边添加`。
select `describe`
from t_users
2.2 JPA
1、使用Jpa的executeUpdate()方法执行插入语句时,sql语句有多行,但是executeUpdate()方法只支持执行一行。
解决办法:将insert into tablename values(...);格式改为insert into tablename values(...),(...);,将多行insert into语句变为一行insert into 语句。
该方法只支持MySQL数据库,Oracle数据库请参考以下博客。
2、使用Jpa的executeUpdate()方法执行更新语句语句时,sql语句有多行,但是executeUpdate()方法只支持执行一行。
解决办法:循环使用executeUpdate()方法执行单条更新语句。
2.3 Mybatis
1、使用Mybatis执行插入语句时,插入语句有多条,但是Mybatis只能执行一条语句,多条不能执行,会报错。
解决办法:
(1)将insert into tablename values(...);格式改为insert into tablename values(...),(...);,将多行insert into语句变为一行insert into 语句。
<insert id="create">
INSERT INTO t_user(username, password)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.username}, #{item.password})
</foreach>
</insert>
(2)Mybits默认只能执行一条语句,jdbc配置后面添加&allowMultiQueries=true,允许执行多条语句。
jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&allowMultiQueries=true
注:每条语句要以;号结尾。
2、 使用Mybatis执行更新语句时,更新语句有多条,但是Mybatis只能执行一条语句,多条不能执行,会报错。
解决办法:Mybits默认只能执行一条语句,jdbc配置后面添加&allowMultiQueries=true,允许执行多条语句。
jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&allowMultiQueries=true
注:每条语句要以;号结尾。
3、Mybatis里面的SQL语句使用了--注释,但是MySQL不支持。
解决办法:Mybatis里面的SQL语句使用一下注释即可。
#注释内容
-- 注释内容,注意看着--后面有个空格
/*注释内容*/
2.4 MyBatis-Plus
1、select()方法里面的sql出现了select,导致报错。
/**
* 使用条件构造器的having()方法
*
* @return
*/
@GetMapping("/getListByHaving")
public List<Map<String, Object>> getListByHaving() {
QueryWrapper<UserEntity> queryWrapper = new QueryWrapper();
queryWrapper.select("select password,count(*) as sexCount")
.groupBy("password")
.having("count(*)>1");
return userService.listMaps(queryWrapper);
}
解决办法:
select()方法里面的Sql只能出现查询的字段名。
/**
* 使用条件构造器的having()方法
*
* @return
*/
@GetMapping("/getListByHaving")
public List<Map<String, Object>> getListByHaving() {
QueryWrapper<UserEntity> queryWrapper = new QueryWrapper();
queryWrapper.select("password,count(*) as sexCount")
.groupBy("password")
.having("count(*)>1");
return userService.listMaps(queryWrapper);
}