SQLSyntaxErrorException

报bug的操作流程

任务：项目的一个接口多了一个新需求，那个接口需要多加一个字段，需要从数据库多select一个字段出来。

一开始想不就加个字段嘛，在源码里找到接口，然后找到mapper.xml里对应的实现sql，如下：

  <select id="listMap" parameterType="Map" resultType="Map">
    select
    a.id as "id",
    a.packet_task_id as "packetTaskId",
    a.packet_define_id as "packetDefineId",
    a.total_packet_count as "totalPacketCount",
    a.packeted_count as "packetdCount",
    a.status as "status",
    a.start_packet_time as "startPacketTime",
    a.end_packet_time as "endPacketTime",
    a.packet_user as "packetUser",
    a.create_user as "createUser",
    a.create_time as "createTime",
    a.last_user as "lastUser",
    a.last_time as "lastTime",
    a.delete_flag as "deleteFlag",
    b.name as "packetName",
    c.audit_user as "auditUser",
    c.audit_time as "auditTime",
    b.serial_number as "serialNumber"
    from
    <include refid="table_name"/>
    a
    inner join t_basic_packet b on a.packet_define_id = b.id
    inner join t_flow_packet_task c on a.packet_task_id = c.id
    where a.delete_flag = 0
    <include refid="condition_sql"/>
    <include refid="listmap_and_countmap_oneday_keyword_condition"/>
    <if test=" _sort !=null and _sort != ''">
      order by ${_sort}
      <if test="_order !=null and _order != ''">
        ${_order}
      </if>
    </if>

  </select>

任务就是改写这段sql就完事了。

我也懒得拼接sql语句，自己拼接也容易拼接错是吧，我就直接从项目的日志文件里拉出sql语句来：

select a.id as "id", 
a.packet_task_id as "packetTaskId", 
a.packet_define_id as "packetDefineId", 
a.total_packet_count as "totalPacketCount", 
a.packeted_count as "packetdCount", 
a.status as "status", 
a.start_packet_time as "startPacketTime", 
a.end_packet_time as "endPacketTime", 
a.packet_user as "packetUser", 
a.create_user as "createUser", 
a.create_time as "createTime", 
a.last_user as "lastUser", 
a.last_time as "lastTime", 
a.delete_flag as "deleteFlag", 
b.name as "packetName", 
c.audit_user as "auditUser", 
c.audit_time as "auditTime", 
b.serial_number as "serialNumber" 
from t_flow_packet_sub_task a 
inner join t_basic_packet b on a.packet_define_id = b.id 
inner join t_flow_packet_task c on a.packet_task_id = c.id 
where a.delete_flag = 0 
and a.status = 4 
order by a.create_time 
desc limit 0,20

然后我就去查a，b，c三张表有没有我需要的那个字段了，如果有的话就好办了，结果查了下发现没有，那没办法，只能join多一张表来拿到那个字段,下边就是inner join多了一张d表的sql（d.packet_instance_id就是我需要的字段）：

select a.id as "id", 
a.packet_task_id as "packetTaskId", 
a.packet_define_id as "packetDefineId", 
a.total_packet_count as "totalPacketCount", 
a.packeted_count as "packetdCount", 
a.status as "status", 
a.start_packet_time as "startPacketTime", 
a.end_packet_time as "endPacketTime", 
a.packet_user as "packetUser", 
a.create_user as "createUser", 
a.create_time as "createTime", 
a.last_user as "lastUser", 
a.last_time as "lastTime", 
a.delete_flag as "deleteFlag", 
b.name as "packetName", 
c.audit_user as "auditUser", 
c.audit_time as "auditTime", 
b.serial_number as "serialNumber" 
d.packet_instance_id as "packetInstanceId"
from t_flow_packet_sub_task a 
inner join t_basic_packet b on a.packet_define_id = b.id 
inner join t_flow_packet_task c on a.packet_task_id = c.id 
inner join t_flow_packet_sub_task_rel d on a.id = d.packet_sub_task_id
where a.delete_flag = 0 
and a.status = 4 
order by a.create_time 
desc limit 0,20

然后在数据库一跑，直接报错

[Error Code: 1064, SQL State: 42000]  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 'd.packet_instance_id as "packetInstanceId"
from t_flow_packet_sub_task a 
inner ' at line 19
Code: 1064 SQL State: 42000 --- 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 'd.packet_instance_id as "packetInstanceId"
from t_flow_packet_sub_task a 
inner ' at line 19

回头一看，发现少了个逗号，加上个逗号就跑通了，然后就是粘贴到mapper.xml上啦

  <select id="listMap" parameterType="Map" resultType="Map">
    select
    a.id as "id",
    a.packet_task_id as "packetTaskId",
    a.packet_define_id as "packetDefineId",
    a.total_packet_count as "totalPacketCount",
    a.packeted_count as "packetdCount",
    a.status as "status",
    a.start_packet_time as "startPacketTime",
    a.end_packet_time as "endPacketTime",
    a.packet_user as "packetUser",
    a.create_user as "createUser",
    a.create_time as "createTime",
    a.last_user as "lastUser",
    a.last_time as "lastTime",
    a.delete_flag as "deleteFlag",
    b.name as "packetName",
    c.audit_user as "auditUser",
    c.audit_time as "auditTime",
    b.serial_number as "serialNumber"
    d.packet_instance_id as "packetInstanceId"
    from
    <include refid="table_name"/>
    a
    inner join t_basic_packet b on a.packet_define_id = b.id
    inner join t_flow_packet_task c on a.packet_task_id = c.id
    inner join t_flow_packet_sub_task_rel d on a.id = d.packet_sub_task_id
    where a.delete_flag = 0
    <include refid="condition_sql"/>
    <include refid="listmap_and_countmap_oneday_keyword_condition"/>
    <if test=" _sort !=null and _sort != ''">
      order by ${_sort}
      <if test="_order !=null and _order != ''">
        ${_order}
      </if>
    </if>

  </select>

然后发布到测试服务器，一测试直接报500系统异常。

然后我就去日志文件里看，发现也是报的SQLSyntaxErrorException，但是日志里边很多/n等符号，我是看的一面蒙蔽不知道它到底哪里错了,就比如下面这样，有很多斜杠看得我人都傻了：

{
    "code": "500",
    "msg": "系统异常",
    "data": {
        "busiCode": "0",
        "msg": "\r\n### Error querying database.  Cause: 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 'd.packet_instance_id as \"packetInstanceId\"\n    from\n     t_flow_packet_sub_task ' at line 20\r\n### The error may exist in file [D:\\workplace\\sterilization_supplement_department\\target\\classes\\mapper\\mysql\\TFlowPacketSubTaskMapper.xml]\r\n### The error may involve cn.wisefly.ssd.flow.mapper.TFlowPacketSubTaskMapper.listMap-Inline\r\n### The error occurred while setting parameters\r\n### SQL: select     a.id as \"id\",     a.packet_task_id as \"packetTaskId\",     a.packet_define_id as \"packetDefineId\",     a.total_packet_count as \"totalPacketCount\",     a.packeted_count as \"packetdCount\",     a.status as \"status\",     a.start_packet_time as \"startPacketTime\",     a.end_packet_time as \"endPacketTime\",     a.packet_user as \"packetUser\",     a.create_user as \"createUser\",     a.create_time as \"createTime\",     a.last_user as \"lastUser\",     a.last_time as \"lastTime\",     a.delete_flag as \"deleteFlag\",     b.name as \"packetName\",     c.audit_user as \"auditUser\",     c.audit_time as \"auditTime\",     b.serial_number as \"serialNumber\"     d.packet_instance_id as \"packetInstanceId\"     from      t_flow_packet_sub_task      a     inner join t_basic_packet b on a.packet_define_id = b.id     inner join t_flow_packet_task c on a.packet_task_id = c.id     inner join t_flow_packet_sub_task_rel d on a.id = d.packet_sub_task_id     where a.delete_flag = 0                                                  and a.status = ?                                                                                                   order by a.create_time                 desc limit 0,20\r\n### Cause: 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 'd.packet_instance_id as \"packetInstanceId\"\n    from\n     t_flow_packet_sub_task ' at line 20\n; bad SQL grammar []; nested exception is 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 'd.packet_instance_id as \"packetInstanceId\"\n    from\n     t_flow_packet_sub_task ' at line 20"
    }
}

没办法，只能在本地环境debug了，打开idea的源码debug起来，用postman模拟了一个请求发到本地环境

### Error querying database.  Cause: 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 'd.packet_instance_id as "packetInstanceId"
    from
     t_flow_packet_sub_task ' at line 20
### The error may exist in file [D:\workplace\sterilization_supplement_department\target\classes\mapper\mysql\TFlowPacketSubTaskMapper.xml]
### The error may involve cn.wisefly.ssd.flow.mapper.TFlowPacketSubTaskMapper.listMap-Inline
### The error occurred while setting parameters
### SQL: select     a.id as "id",     a.packet_task_id as "packetTaskId",     a.packet_define_id as "packetDefineId",     a.total_packet_count as "totalPacketCount",     a.packeted_count as "packetdCount",     a.status as "status",     a.start_packet_time as "startPacketTime",     a.end_packet_time as "endPacketTime",     a.packet_user as "packetUser",     a.create_user as "createUser",     a.create_time as "createTime",     a.last_user as "lastUser",     a.last_time as "lastTime",     a.delete_flag as "deleteFlag",     b.name as "packetName",     c.audit_user as "auditUser",     c.audit_time as "auditTime",     b.serial_number as "serialNumber"     d.packet_instance_id as "packetInstanceId"     from      t_flow_packet_sub_task      a     inner join t_basic_packet b on a.packet_define_id = b.id     inner join t_flow_packet_task c on a.packet_task_id = c.id     inner join t_flow_packet_sub_task_rel d on a.id = d.packet_sub_task_id     where a.delete_flag = 0                                                  and a.status = ?                                                                                                   order by a.create_time                 desc limit 0,20
### Cause: 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 'd.packet_instance_id as "packetInstanceId"
    from
     t_flow_packet_sub_task ' at line 20
; bad SQL grammar []; nested exception is 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 'd.packet_instance_id as "packetInstanceId"
    from
     t_flow_packet_sub_task ' at line 20

可以看出在idea的console里边就没有/n等转义符，这点确实比在日志看好。

看报错可以看出又是 d.packet_instance_id as “packetInstanceId” 的问题，去mapper.xml一看发现又是没加逗号，怪不得在数据库跑的通而在测试环境报错。

回头看发现自己真的逗…

总结

1. 别贪方便只在日志里看报错信息，固然日志里的报错信息非常全面，有时候日志里看的不明白的话，去idea里debug是个很好的选择。
2. 报SQLSyntaxErrorException错的话多半是逗号的错（执念）