问题描述及解决方案
在写一个MyBatis从数据库中读取数据的示例程序时,总是出现如下报错信息:
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
### The error may exist in org/example/dao/StudentDao.xml
### The error may involve org.example.dao.StudentDao.selectStudents
### The error occurred while executing a query
### Cause: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:153)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:135)
at org.example.MyApp.main(MyApp.java:29)
Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3996)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2137)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:776)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:208)
at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:224)
at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:219)
at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.getConnection(UnpooledDataSource.java:95)
at org.apache.ibatis.datasource.pooled.PooledDataSource.popConnection(PooledDataSource.java:432)
at org.apache.ibatis.datasource.pooled.PooledDataSource.getConnection(PooledDataSource.java:89)
at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:139)
at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:61)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:337)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
... 4 more
Process finished with exit code 1
此时数据库的主配置文件中,dataSource 标签的内容如下:
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3307/mybatis_test"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</dataSource>
Access denied for user 'root'@'localhost'
,也就是拒绝访问,但我在配置文件中写的username和password都是正确的,花了一两的小时试图找出问题所在,但无果。
我访问的数据库是MariaDB,而且我的电脑上装了两个数据库,一个是MySQL,在3306端口上,另一个则是MariaDB,在3307端口上。MySQL数据库的账号密码分别是root
和password
(没错,就是密码的英文),MariaDB的账号密码分别为root
和123
。
之前学JDBC时,写com.mysql.jdbc.Driver
和jdbc:mysql://localhost:3307/数据库名
是可以正常访问MariaDB中的数据的。因为之前知道MariaDB的底层其实和MySQL很相似,于是就以为MariaDB和MySQL的访问没什么区别。
但是现在这套用在MyBatis上却不行了。后来想到会不会是MariaDB和MySQL所要用的依赖不同?后来一查还真是,于是把依赖改成MariaDB的,如下:
<!--mariadb依赖-->
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>2.7.3</version>
</dependency>
并且将dataSource标签中的driver标签和url标签改成MariaDB的:
<dataSource type="POOLED">
<property name="driver" value="org.mariadb.jdbc.Driver"/>
<property name="url" value="jdbc:mariadb://localhost:3307/mybatis_test"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</dataSource>
于是就可以正常访问MariaDB数据库了。
总结
所以为什么会报Access denied的错误呢?
是因为MyBatis以为我要访问的是MySQL数据库,但是MySQL的账号密码不对,于是出现拒绝访问的错误。于是修改配置,指定要访问MariaDB数据库,问题就解决了。