Access denied for user 'xy'@'localhost' (using password: YES)解决办法

悠悠 2022-07-15 04:22 152阅读 0赞

今天配置sping+spingmvc+mybatis时，在配置到druid数据源的时候报了这个错误，上网查了一下  
总结一下原因：大多数都是 可能是连接数据库的用户名或者密码错误，但是我的用户名，密码，和url都是正确的。因为是用的框架，自己就动手试试能不能获取到值，结果发现了问题：如下

下面是我配置sping-mybatis.xml的文件：

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:c="http://www.springframework.org/schema/c" xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
    
    
        
        <context:property-placeholder location="classpath:jdbc.properties"/>
    
        
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
            
            <property name="url" value="${url}"/>
            <property name="username" value ="${username}"/>
            <property name="password" value="${password}"/>
        </bean>
    
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <constructor-arg index="0" ref="dataSource"/>
        </bean>
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource"/>
            <property name="mapperLocations" value="classpath:cn/cupcat/mapper/*.xml"/> 
        </bean> 
    
         <bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="cn.cupcat.dao"/>
        </bean>
    
        <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" >
            <constructor-arg index="0" ref="sqlSessionFactory"/>
        </bean> 
    
    </beans>

下面是jdbc.properties的文件内容：

driverClassName=com.mysql.jdbc.Driver
    username=root
    password=123456
    initialSize=1
    maxActive=20
    maxIdle=2
    minIdle=1
    url=jdbc\:mysql\://127.0.0.1\:3306/adressbook02?useUnicode\=true&characterEncoding\=UTF-8

思考了一下发现报错信息，用户名配置的是root 怎么报错的时候会出现xy’@’localhost’ (using password: YES)错误呢，于是我就想了一下，是不是使用SpEL没有获取到用户名呢？于是又在spring-mybatis.xml里面配置了一个student的bean 将username,\{password\},$\{url\}赋值给student bean 的属性里面在控制器里面输出，看看能不能取到值：

修改后的sping-mybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:c="http://www.springframework.org/schema/c" xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
    
    
        
        <context:property-placeholder location="classpath:jdbc.properties"/>
    
        
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
            
            <property name="url" value="${url}"/>
            <property name="username" value ="${users}"/>
            <property name="password" value="${password}"/>
        </bean>
        
        <bean id="student" class="cn.cupcat.model.Student">
            <property name="sNo" value="${users}"/>
            <property name="sName" value="${password}"/>
            <property name="qq" value="${url}"/>
        </bean>
            
    
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <constructor-arg index="0" ref="dataSource"/>
        </bean>
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource"/>
            <property name="mapperLocations" value="classpath:cn/cupcat/mapper/*.xml"/> 
        </bean> 
    
         <bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="cn.cupcat.dao"/>
        </bean>
    
        <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" >
            <constructor-arg index="0" ref="sqlSessionFactory"/>
        </bean> 
    
    </beans>

控制器代码： StudentController.java

package cn.cupcat.controller;
    
    import javax.annotation.Resource;
    
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.ModelMap;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import cn.cupcat.model.Student;
    import cn.cupcat.service.StudentService;
    
    @Controller
    public class StudentController {
    
    
        @Resource(name="student")
        private Student student ;
    
        @Resource(name = "studentService")
        private StudentService studentService;
    
        int num = 0;
    
        @RequestMapping("aa")
        public String testUrl(ModelMap modelMap) {
    
            System.out.println("第" + num + "次" +"username:"+ student.getsNo()+"...password:"+student.getsName()+"url:"+student.getQq());
    
            num++;
            System.out.println("第" + num + "次" + "......test springmvc .....");
            Student stu = studentService.selectByPrimaryKey(2);
            modelMap.put("bb", stu);
            return "aa";
        }
    }

结果输出：  
第0次username:null…password:123456url:jdbc:mysql://127.0.0.1:3306/adressbook02?useUnicode=true&characterEncoding=UTF-8  
第1次……test springmvc …..

结果发现没有获取到$\{username\}的值，于是修改了一下jdbc.properties的文件内容，将username 修改问users ，测试一下：

driverClassName=com.mysql.jdbc.Driver
    #修改的地方 
    users=root
    password=123456
    initialSize=1
    maxActive=20
    maxIdle=2
    minIdle=1
    #url=jdbc\:mysql\://122.13.166.136\:3306_jngczx?useUnicode\=true&characterEncoding\=UTF-8
    #url=jdbc\:mysql\://222.194.124.23\:3306/sjjx?useUnicode\=true&characterEncoding\=UTF-8
    url=jdbc\:mysql\://127.0.0.1\:3306/adressbook02?useUnicode\=true&characterEncoding\=UTF-8

修改后的spring-mybatis.xml

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:c="http://www.springframework.org/schema/c" xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
    
    
        
        <context:property-placeholder location="classpath:jdbc.properties"/>
    
        
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
            
            <property name="url" value="${url}"/>
            <property name="username" value ="${users}"/>
            <property name="password" value="${password}"/>
        </bean>
    
        <bean id="student" class="cn.cupcat.model.Student">
            <property name="sNo" value="${users}"/>
            <property name="sName" value="${password}"/>
            <property name="qq" value="${url}"/>
        </bean>
    
    
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <constructor-arg index="0" ref="dataSource"/>
        </bean>
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource"/>
            <property name="mapperLocations" value="classpath:cn/cupcat/mapper/*.xml"/> 
        </bean> 
    
         <bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="cn.cupcat.dao"/>
        </bean>
    
        <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate" >
            <constructor-arg index="0" ref="sqlSessionFactory"/>
        </bean> 
    
    </beans>

结果在控制器里面输出了：  
第0次username:root…password:123456url:jdbc:mysql://127.0.0.1:3306/adressbook02?useUnicode=true&characterEncoding=UTF-8  
第1次……test springmvc …..

可以看到得到了 ￥\{users\}的值，也不报错了，问题解决了。为什么会发生这样的原因现在还没有清楚，还需要查资料，真的是学习永无止境呀！ 学习吧，少年，努力！