SpringBoot连接Mysql数据库

本文记录Spring Boot连接单mysql数据源和多mysql数据源的一种配置方式。

---

单数据源

1.在pom.xml中添加相关依赖

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>

注：上述配置中的mybatis-spring-boot-starter适用于Spring 2，若使用Spring 3，需要升级到更高版本（如3.0.4），否则会报错Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required。
 

2.在配置文件中配置数据库和mapper.xml文件信息

# 数据库账号密码
spring.datasource.url=jdbc:mysql://localhost:3306/first_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.datasource.username=first
spring.datasource.password=first
# mapper.xml文件路径
mybatis.mapper-locations=classpath:mappers/*.xml

3.使用

3.1.在/resources/mappers添加编写sql语句的xml文件

3.2.将接口类和SQL进行映射

有两种方法：

1. 在对应的Mapper接口类中添加@Mapper注解即可；
2. 在对应的Mapper接口类中添加@Repository注解，并在启动类添加@MapperScan("#{Mapper接口类所在包路径}")注解。

3.3.配置事务

默认开启事务（Spring 3.3.2中测试通过），无需特别配置，直接在代码中使用@Transactional注解即可。
 

---

多数据源

1.在pom.xml中添加相关依赖

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.26</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

2.在配置文件中配置数据库和mapper.xml文件信息

# 第一个数据库配置
spring.datasource.first.url=jdbc:mysql://localhost:3306/first_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.datasource.first.username=first
spring.datasource.first.password=first
spring.datasource.first.mapper-locations=classpath:mappers/first/*.xml
spring.datasource.first.driver-class-name=com.mysql.cj.jdbc.Driver
# 第二个数据库配置
spring.datasource.second.url=jdbc:mysql://localhost:3306/second_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC
spring.datasource.second.username=second
spring.datasource.second.password=second
spring.datasource.second.mapper-locations=classpath:mappers/second/*.xml
spring.datasource.second.driver-class-name=com.mysql.cj.jdbc.Driver

3.Druid配置

第一个数据库的Druid配置

package com.example.dbtest.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.dbtest.dao.first", sqlSessionTemplateRef = "firstSqlSessionTemplate")
public class DruidFirstConfig {
    @Value("${spring.datasource.first.url:}")
    private String url;
    @Value("${spring.datasource.first.username:}")
    private String username;
    @Value("${spring.datasource.first.password:}")
    private String password;
    @Value("${spring.datasource.first.mapper-locations:}")
    private String mapperLocations;
    @Value("${spring.datasource.first.driver-class-name:}")
    private String driverClassName;

    @Bean
    public DataSource firstDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);
        return dataSource;
    }

    @Bean
    public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public DataSourceTransactionManager firstDataSourceTransactionManager(@Qualifier("firstDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

第二个数据库的Druid配置

package com.example.dbtest.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.dbtest.dao.second", sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class DruidSecondConfig {
    @Value("${spring.datasource.second.url:}")
    private String url;
    @Value("${spring.datasource.second.username:}")
    private String username;
    @Value("${spring.datasource.second.password:}")
    private String password;
    @Value("${spring.datasource.second.mapper-locations:}")
    private String mapperLocations;
    @Value("${spring.datasource.second.driver-class-name:}")
    private String driverClassName;

    @Bean
    public DataSource secondDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setDriverClassName(driverClassName);
        return dataSource;
    }

    @Bean
    public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public DataSourceTransactionManager secondDataSourceTransactionManager(@Qualifier("secondDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

3.使用

3.1.在/resources/mappers/first和/resources/mappers/second添加编写sql语句的xml文件

3.2.将接口类和SQL进行映射

在对应的Mapper接口类中添加@Mapper或@Repository注解即可，无需在启动类添加@MapperScan("#{Mapper接口类所在包路径}")注解（已经在对应的配置类添加过该注解了）。

3.3.配置事务

默认开启事务（Spring 3.3.2中测试通过），无需特别配置，直接在代码中使用@Transactional注解即可，注意@Transactional除了指定rollbackFor外，还需要指定事务管理器transactionManager，事务管理器名称为对应配置类的方法名（如firstDataSourceTransactionManager、secondDataSourceTransactionManager）。