MySQL版本是8.0.3,数据库连接字符串都检查无误,驱动类名加不加cj都试过了,URL是否加时区、是否加useSSL都试过了,都无法正常登录进去!
我在运行library这个web程序登录时(我的MySQL版本是8.0.3,数据库连接字符串都检查无误,驱动类名加不加cj都试过了,URL是否加时区、是否加useSSL都试过了,都无法正常登录进去!),提示错误分如下两种情况:
1、当我使用您工程默认的mysql-connector-java(5.1.6)时,用admin/123456登录提示错误:
### Error querying database. Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
### The error may exist in com/yanzhen/dao/AdminMapper.xml
### The error may involve com.yanzhen.dao.AdminMapper.queryUserByNameAndPassword
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Connections could not be acquired from the underlying database!
2、当我使用mysql-connector-java(8.0.9-rc)时,用admin/123456登录提示错误:
### 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 'admin where username = 'admin'
and password = '123456'' at line 1
### The error may exist in com/yanzhen/dao/AdminMapper.xml
### The error may involve com.yanzhen.dao.AdminMapper.queryUserByNameAndPassword-Inline
### The error occurred while setting parameters
### SQL: select * from admin where username = ? and password = ?
### 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 'admin where username = 'admin' and password = '123456'' at line 1
问题分析:
数据中的表名为admin,而admin在MySQL8.0.3中已经变成关键字了,二者冲突了。Mysql5.7没限制。其它被Mysql8占用的关键字还有:member,order,admin,leave。如果表名写成member,order,admin,leave等就会报错,暂时发现这几个...好坑呀...
解决办法:
方法(1),改掉表名,不使用关键字。
方法(2),给表名加上反单引号``符号(是tab键上方那个键)。