Caused by: java.sql.SQLException: Access denied for user ‘æ�¨æ˜Š‘@‘localhost‘数据源读取配置问题

淡淡的烟草味﹌ 2022-10-12 04:17 8阅读 0赞

### properties对数据源配置的的读取问题 ###

*  问题描述
 *   *  环境
     *  错误代码：
 *  原因
 *  解决方案如下：
 *   *  一：修改用户名的key
     *  二，修改system-properties-mode="FALLBACK"

# 问题描述 #

读取db.properties中的数据信息然后导入mybatis的数据源配置中出现的错误。

## 环境 ##

db.properties内容：

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
    username=root
    jdbc.password=123456

ssm中。 mybatis读取数据源时的配置：

<context:property-placeholder location="classpath:db.properties" system-properties-mode="ENVIRONMENT" />
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
            <property name="driverClassName" value="${driver}"></property>
            <property name="url" value="${url}"></property>
            <property name="username" value="${username}"></property>
            <property name="password" value="${password}"></property>
    
        </bean>

## 错误代码： ##

Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'æ�¨æ˜Š'@'localhost' (using password: YES)
    	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
    	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.base/java.lang.reflect.Method.invoke(Method.java:564)
    	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
    	... 49 more
    Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'æ�¨æ˜Š'@'localhost' (using password: YES)
    	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)
    	at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:80)
    	at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:67)
    	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:147)
    	... 55 more
    Caused by: java.sql.SQLException: Access denied for user 'æ�¨æ˜Š'@'localhost' (using password: YES)
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)
    	at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1714)
    	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1224)
    	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2199)
    	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2230)
    	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2025)
    	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:778)
    	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488)
    	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
    	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
    	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:678)
    	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:190)
    	at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:155)
    	at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:146)
    	at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:205)
    	at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:169)
    	at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158)
    	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116)
    	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
    	... 65 more

# 原因 #

在通过读取db.properties文件中的数据源配置信息时，会报以上错误，然后很多时候我就没有用这个方式，而是直接写的对应值到xml中，以为是会出现乱码导致的。

直到我一个个的试验，发现只有如果用户名的key为username，则无法通过$\{username\}提取出来，得出结论，用户名的key不同用username提取，在进一步百度之后，发现 <context:property-placeholder location=“classpath:db.properties” system-properties-mode=“ENVIRONMENT” />这条命令是默认有个属性system-properties-mode=“ENVIRONMENT”，意思是会读取系统用户名，所以肯定就不对了，于是这里要么修改properties中用户名的key，让其不为username，要么，修改system-properties-mode=“FALLBACK”，让它不从系统环境中读取。

# 解决方案如下： #

## 一：修改用户名的key ##

db.properties内容：

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://localhost:3306/mybatis001?characterEncoding=utf-8
    jdbc.username=root
    jdbc.password=123

xml中配置内容：

运行成功。

## 二，修改system-properties-mode=“FALLBACK” ##

db.properties内容：和报错前一样，不变

jdbc.driver=com.mysql.jdbc.Driver
    jdbc.url=jdbc:mysql://127.0.0.1:3306/smbms?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
    username=root
    jdbc.password=123456

xml中配置：

<context:property-placeholder location="classpath:db.properties" system-properties-mode="FALLBACK" />
       <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
            <property name="driverClassName" value="${driver}"></property>
            <property name="url" value="${url}"></property>
            <property name="username" value="${username}"></property>
            <property name="password" value="${password}"></property>
    
        </bean>