解决Springboot + MyBatis框架“Cause: org.springframework.jdbc.CannotGetJdbcConnectionException”问题

目录

1 出错类型一（连接MySQL失败）

2 出错类型二（MySQL密码错误）

3 出错类型三（XXXMapper.xml中SQL语句语法问题）

前言：

最近用 SpringBoot 写项目的时候，写 Dao 层测试利用 MyBatis 访问数据库遇到了 bug ，困扰了一两天，因此记录一下。

1 出错类型一（连接MySQL失败）

### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user ‘root’@’127.0.0.1’ to database ‘db_onlineexam’

这个问题巨坑，笔者检查了很多次 XXXMapper.xml 的 sql 语句以及电脑主机的 mysql 密码，确认无误，但一直都报这个错误，在网上查了很多资料，都没有成功解决这个bug。最后笔者重新安装 MySQL ，结果问题解决。。。

MySQL安装传送门：https://blog.csdn.net/liangllhahaha/article/details/89508826

2 出错类型二（MySQL密码错误）

### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user ‘root’@’localhost’ (using password: YES)

 Bug详情：

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
### The error may exist in file [D:\IdeaProjects\onlineExam\target\classes\mapper\UserMapper.xml]
### The error may involve com.nenusoftware.onlineexam.mapper.UserMapper.selectByName
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

如图所示，报错提示用户 ‘root’@’localhost’ 的密码有误，于是去检查项目中关于数据库密码的配置，发现 password 不是我的主机上 mysql 的密码：

将密码修改为正确密码即可：

3 出错类型三（XXXMapper.xml中SQL语句语法问题）

### Cause: 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 ”t_user’

Bug详情：

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: 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 ''t_user'
        WHERE t_user.username = '45'' at line 2
### The error may exist in file [D:\IdeaProjects\onlineExam\target\classes\mapper\UserMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT *         FROM 't_user'         WHERE t_user.username = ?
### Cause: 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 ''t_user'
        WHERE t_user.username = '45'' at line 2
; bad SQL grammar []; nested exception is 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 ''t_user'
        WHERE t_user.username = '45'' at line 2

如图所示，报错提示 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 ”t_user’ WHERE t_user.username = ’45” at line 2 ，这是一个语法错误。

于是去检查相应的查询语句，发现在 UserMapper.xml 中关于查询 t_user 这个表，我多写了一对引号：

去掉这对引号即可，就不会报错：

<select id="selectByName" resultMap="User">
    SELECT *
    FROM t_user
    WHERE t_user.username = #{username}
</select>

测试代码显示查询成功。

 结论：出现语法问题超级坑，需要你仔细地检查 XXXMapper.xml 中相关 sql 语句的语法问题。