spring与mybatis的整合

一、原生spring与mybatis的整合

分两种方式介绍：原始xml方式和基于注解的方式

1.xml方式

参考Spring的基本配置和Spring与Mybatis的整合

2.注解方式

使用的是alibaba druid数据源

1.jdbc配置文件db.properties

mybatis.druid.url=jdbc:mysql://127.0.0.1:3306/consult
mybatis.druid.username=root
mybatis.druid.password=123456
mybatis.druid.driver-class-name=org.gjt.mm.mysql.Driver
mybatis.druid.initialSize=2
mybatis.druid.minIdle=2
mybatis.druid.maxActive=2
## 配置获取连接等待超时的时间
mybatis.druid.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
mybatis.druid.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
mybatis.druid.minEvictableIdleTimeMillis=300000
mybatis.druid.validationQuery=SELECT 1 FROM DUAL
mybatis.druid.testWhileIdle=true
mybatis.druid.testOnBorrow=false
mybatis.druid.testOnReturn=false
mybatis.druid.poolPreparedStatements=true
mybatis.druid.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
mybatis.druid.filters=stat,wall
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
mybatis.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

2.数据源配置类

其中PropertiesReader类是自定义的，作用是读取db.properties的配置

@Configuration
public class DruidConfig {

    private String driverClassName;
    private String username;
    private String jdbcUrl;
    private String password;
    private int maxActive;
    private int minIdle;
    private int initialSize;
    private Long timeBetweenEvictionRunsMillis;
    private Long minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private Integer maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private String connectionProperties;


    @Bean(destroyMethod = "close",initMethod = "init")
    public DataSource dataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(PropertiesReader.get("mybatis.druid.driver-class-name"));
        druidDataSource.setUrl(PropertiesReader.get("mybatis.druid.url"));
        druidDataSource.setUsername(PropertiesReader.get("mybatis.druid.username"));
        druidDataSource.setPassword(PropertiesReader.get("mybatis.druid.password"));
        druidDataSource.setMaxActive(PropertiesReader.getInteger("mybatis.druid.maxActive"));
        druidDataSource.setInitialSize(PropertiesReader.getInteger("mybatis.druid.initialSize"));
        druidDataSource.setMinEvictableIdleTimeMillis(PropertiesReader.getInteger("mybatis.druid.minEvictableIdleTimeMillis"));
        druidDataSource.setValidationQuery(PropertiesReader.get("mybatis.druid.validationQuery"));
        druidDataSource.setTestWhileIdle(PropertiesReader.getBoolean("mybatis.druid.testWhileIdle"));
        druidDataSource.setTestOnBorrow(PropertiesReader.getBoolean("mybatis.druid.testOnBorrow"));
        druidDataSource.setTestOnReturn(PropertiesReader.getBoolean("mybatis.druid.testOnReturn"));
        druidDataSource.setPoolPreparedStatements(PropertiesReader.getBoolean("mybatis.druid.poolPreparedStatements"));
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(PropertiesReader.getInteger("mybatis.druid.maxPoolPreparedStatementPerConnectionSize"));

        try {
            druidDataSource.setFilters(PropertiesReader.get("mybatis.druid.filters"));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return druidDataSource;
    }
}

3.mybatis配置类

@Configuration
//开启事务支持
@EnableTransactionManagement
//指定mapper接口的路径
@MapperScan(basePackages = {"cn.enjoy.dao"})
public class MybatisConfig{

    @Autowired
    private ApplicationContext applicationContext;

    @Bean
    public SqlSessionFactoryBean sqlSessionFactoryBean(DataSource dataSource) throws IOException {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        //设置日志类
        configuration.setLogImpl(Log4jImpl.class);
        sqlSessionFactoryBean.setConfiguration(configuration);
//        sqlSessionFactoryBean.setConfigLocation(applicationContext.getResource("classpath:mybatis-config.xml"));    
		//设置mapper.xml路径        
		sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:xml/*Mapper.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("cn.enjoy.pojo");
        return sqlSessionFactoryBean;
    }

	//配置事务管理器	
    @Bean
    public PlatformTransactionManager annotationDrivenTransactionManager(DataSource dataSource) {
        DataSourceTransactionManager dtm = new DataSourceTransactionManager();
        dtm.setDataSource(dataSource);
        return dtm;
    }

    @Bean
    public TransactionTemplate transactionTemplate(PlatformTransactionManager platformTransactionManager) {
        TransactionTemplate transactionTemplate = new TransactionTemplate();
        transactionTemplate.setTransactionManager(platformTransactionManager);
        return transactionTemplate;
    }
}

另外一种设置mapper.xml的方式是

PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResources("classpath:xml/*Mapper.xml"));

二、springboot与mybatis的整合

下面以mybatis-plus为例，介绍整合步骤。

1.引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.0</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>

2.application.yml配置

spring:
    datasource:
      name: cgj
      url: jdbc:mysql://你的数据库IP地址:3306/cgj?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      # 使用druid数据源
      druid:
        max-active: 20
        initial-size: 1
        max-wait: 60000
        min-idle: 1
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 300000
        validation-query: SELECT 'x' FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: false
        max-open-prepared-statements: 20
        # encrypt password
        #filters: config,stat,wall,log4j
        use-global-data-source-stat: true
        connection-init-sqls:
          - set names utf8mb4
        
        
mybatis-plus:
  mapper-locations: classpath*:xml/*Mapper.xml
  -- 加控制台打印日志配置
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl     

3.启动类加注解

加如下注解

@MapperScan("cn.enjoy.dao")

三、springboot多数据源整合

上面介绍的方式，都是在一个数据源的情况下。当有多个数据源时，我们可以使用如下步骤整合。

1.application.yml配置

包括两个数据源配置：master和slave1

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://localhost:3306/master?serverTimezone=GMT%2b8&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&autocommit=false
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave1:
      jdbc-url: jdbc:mysql://localhost:3306/slave1?serverTimezone=GMT%2b8&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&autocommit=false
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver

2.master主数据源配置

@Configuration
//master数据源的mapper接口路径为cn.enjoy.rwseparation.dao.master
@MapperScan(
        basePackages = "cn.enjoy.rwseparation.dao.master",
        annotationClass = Repository.class, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterConfig {

	//master数据源对象
    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return create().build();
    }


    @Bean
    public SqlSessionFactory masterSqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean
                = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(masterDataSource());
         PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
        sessionFactory.setMapperLocations(resourcePatternResolver.getResources("classpath:/mapper/human/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate masterSqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(masterSqlSessionFactory());
    }

    @Bean
    public PlatformTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }
}

3.slave1从数据源配置

@Configuration
@MapperScan(
        basePackages = "cn.enjoy.rwseparation.dao.slave1",
        annotationClass = Repository.class, sqlSessionFactoryRef = "slave1SqlSessionFactory")
public class SlaveConfig {

	//slave1数据源对象
    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return create().build();
    }


    @Bean
    public SqlSessionFactory slave1SqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean
                = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(slave1DataSource());
        PathMatchingResourcePatternResolver resourcePatternResolver =new PathMatchingResourcePatternResolver();
        sessionFactory.setMapperLocations(resourcePatternResolver.getResources("classpath:/mapper/woman/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate slave1SqlSessionTemplate() throws Exception {
        return new SqlSessionTemplate(slave1SqlSessionFactory());
    }

    @Bean
    public PlatformTransactionManager slave1TransactionManager() {
        return new DataSourceTransactionManager(slave1DataSource());
    }
}

4.使用方式

需要指定PlatformTransactionManager的bean id

4.1 读主数据源

@Service
public class HumanService {

    @Autowired
    private HumanMapper humanMapper;

    @Transactional("masterTransactionManager")
    public void select(){
        humanMapper.select();
    }
}

4.2 读从数据源

@Service
public class WomanService {
    @Autowired
    private WomanMapper womanMapper;

    @Transactional("slave1TransactionManager")
    public void select(){
        womanMapper.select();
    }
}

注意：
因为MybatisAutoConfiguration是当在不存在SqlSessionFactory对象的时候，才会去从yml或者properties中读取xml路径配置。

而多数据源情况下，由于通过@Bean手动配置了SqlSessionFactory，所以已经存在了SqlSessionFactory，此时就不会去读取yml或者properties中的xml路径配置。
所以此时需要我们自己去set路径。

PathMatchingResourcePatternResolver resourcePatternResolver = PathMatchingResourcePatternResolver();
sessionFactory.setMapperLocations(resourcePatternResolver.getResources("......"));