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数据库请参考以下博客。

JPA 执行单条和多条Insert插入语句（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);
    }