spring-mybatis

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.example.mapper.AccountMapper.selectAccount

maven在配置的时候会自动忽略xml文件

        <!--maven默认打包是不会认为资源文件需要打入包内，所以需要设置-->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                    <include>**/*.properties</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>

### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (Access denied for user 'GF'@'localhost' (using password: YES))

写的都是对的但是报错了，这是个坑

解决方法

db.propertise

applicationContext.xml

username写成两段

运行结果

单元测试@Test加@Transactional注解自动回滚

去掉以后不会回滚

数据库会更新

基础文件

Account

package com.example.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Account {
    private Long id;
    private String accountno;
    private String password;
    private Double balance;
}

AccountMapper

package com.example.mapper;

import com.example.entity.Account;

public interface AccountMapper {

    //查询账户
    Account selectAccount (String accountno);

    //更新账户
    void updateAccount(Account account);
}

AccountMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.AccountMapper">
    <select id="selectAccount" parameterType="string" resultType="account">
        select * from spring_account where accountno=#{value}
    </select>

    <update id="updateAccount" parameterType="account">
        update spring_account set accountno=#{accountno},password=#{password},balance=#{balance} where id=#{id}
    </update>
</mapper>

AccountService

package com.example.service;

public interface AccountService {

    //转账
    void transfer(String fromAccountNo,String toAccountNo,double money);
}

AccountServiceImpl

package com.example.service;


import com.example.entity.Account;
import com.example.mapper.AccountMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
//@Transactional
public class AccountServiceImpl implements AccountService {

    @Autowired
    AccountMapper accountMapper;
    @Override
    public void transfer(String fromAccountNo, String toAccountNo, double money) {

        Account fromAccount=accountMapper.selectAccount(fromAccountNo);
        fromAccount.setBalance(fromAccount.getBalance()-money);
        Account toAccount=accountMapper.selectAccount(toAccountNo);
        toAccount.setBalance(toAccount.getBalance()+money);
        accountMapper.updateAccount(fromAccount);
        accountMapper.updateAccount(toAccount);
    }
}

 

xml+注解

applicationContext.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:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        https://www.springframework.org/schema/context/spring-context-2.5.xsd
        http://www.springframework.org/schema/aop
        https://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

    <!--开启注解扫描包-->
    <context:component-scan base-package="com.example"/>

    <!--开启AOP-->
    <aop:aspectj-autoproxy/>

    <!--开启声明式事务-->
    <tx:annotation-driven/>

    <!--导入外部数据库配置文件-->
    <context:property-placeholder location="db.properties"/>
    <!--配置数据源-->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="initialSize" value="${jdbc.initialSize}"/>
        <property name="maxTotal" value="${jdbc.maxTotal}"/>
        <property name="maxIdle" value="${jdbc.maxIdle}"/>
        <property name="maxWaitMillis" value="${jdbc.maxWaitMillis}"/>
    </bean>
    <!--配置sqlSessionfactory-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--整合数据源-->
        <property name="dataSource" ref="dataSource"/>
        <!--配置Mybatis -->
        <property name="configLocation" value="SqlMapConfig.xml"/>
        <!--配置mapper映射文件-->
        <property name="mapperLocations" value="com/example/mapper/*.xml"/>
    </bean>

    <!--mapper接口扫描器-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.example.mapper"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

    <!--配置事务管理器-->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>


</beans>

db.properties

# 数据库配置文件
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring?serverTimezone=Asia/Shanghai
jdbc.username=root
jdbc.password=123456
jdbc.initialSize=10
jdbc.maxTotal=5
jdbc.maxIdle=5
jdbc.maxWaitMillis=5000

SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <!--开启延迟加载的支持-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>

        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <!--    别名设置，全路径名改成类名-->
    <typeAliases>
        <!--        该包下面实体类全部采用别名形式-->
        <package name="com.example.entity"/>
    </typeAliases>

</configuration>

log4j.properties

### 设置###
log4j.rootLogger = debug,stdout,D,E

### 输出信息到控制抬 ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n

### 输出DEBUG 级别以上的日志到=D://Projects/java/Mybatis/logs/log.log  ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = D://Projects/java/Mybatis/logs/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG 
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

### 输出ERROR 级别以上的日志到=D://Projects/java/Mybatis/logs/log.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =D://Projects/java/Mybatis/logs/log.log
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR 
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

全注解

全注解版本就不要xml文件了，新增AppConfig

package com.example;

import org.apache.commons.dbcp2.BasicDataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.context.annotation.*;
import org.springframework.core.env.Environment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;


@Configuration
@ComponentScan
@EnableTransactionManagement
@EnableAspectJAutoProxy
@PropertySource("classpath:db.properties")
//mapper扫描器
@MapperScan(basePackages = "com.example.mapper")
public class AppConfig {

    @Autowired
    Environment env;

    //数据源
    @Bean
    public DataSource dataSource() {
        BasicDataSource bds = new BasicDataSource();
        bds.setDriverClassName(env.getProperty("jdbc.driver"));
        bds.setUrl(env.getProperty("jdbc.url"));
        bds.setUsername(env.getProperty("jdbc.username"));
        bds.setPassword(env.getProperty("jdbc.password"));
        bds.setInitialSize(Integer.parseInt(env.getProperty("jdbc.initialSize")));
        bds.setMaxTotal(Integer.parseInt(env.getProperty("jdbc.maxTotal")));
        bds.setMaxIdle(Integer.parseInt(env.getProperty("jdbc.maxIdle")));
        bds.setMaxWaitMillis(Long.parseLong(env.getProperty("jdbc.maxWaitMillis")));
        return bds;
    }

    //SqlSessionFactory
    @Bean
    public SqlSessionFactoryBean sqlSessionFactoryBean() {
        SqlSessionFactoryBean sqlSessionFactoryBean = null;
        sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        //spring整合mybatis配置文件
        //别名
        sqlSessionFactoryBean.setTypeAliasesPackage("com.example.entity");
        //mybatis框架其他配置


        return sqlSessionFactoryBean;
    }


    //事务管理器
    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager() {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource());
        return dataSourceTransactionManager;
    }

}

测试类

AccountMapperTest

package com.example.mapper;

import com.example.BaseTest;
import com.example.entity.Account;
import junit.framework.TestCase;
import org.junit.Test;

public class AccountMapperTest extends BaseTest {

    @Test
    public void testSelectAccount() {
        Account account=accountMapper.selectAccount("10001");
        System.out.println(account);
    }

    @Test
    public void testUpdateAccount() {
    }
}

AccountServiceTest

package com.example.service;

import com.example.BaseTest;
import org.junit.Test;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;


//@Transactional(isolation = Isolation.READ_COMMITTED, propagation = Propagation.REQUIRED)
public class AccountServiceTest extends BaseTest {

    @Test
    public void testTransfer() {
        accountService.transfer("10001", "10002", 1000);
    }


}

BaseTest

package com.example;

import com.example.mapper.AccountMapper;
import com.example.service.AccountService;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@ContextConfiguration(locations = "classpath:applicationContext.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class BaseTest {

    @Autowired
    public AccountMapper accountMapper;

    @Autowired
    public AccountService accountService;
}

运行结果