springboot 数据源配置

Posted on Edited on In Views:

1、环境及依赖

  • Amazon Corretto 17
  • Spring Boot 3.2.0
  • Gradle 7.4.2
  • postgresql 15.5-1
  • mariadb 10.11.6
1
2
3
4
5
6
7
8
9
dependencies {
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:3.0.3'
    compileOnly 'org.projectlombok:lombok'
    runtimeOnly 'org.mariadb.jdbc:mariadb-java-client'
    runtimeOnly 'org.postgresql:postgresql'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testImplementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter-test:3.0.3'
}

2、application.yml

1
2
3
4
5
6
7
8
9
10
11
12
spring:
  datasource:
    main:
      driverClassName: org.mariadb.jdbc.Driver
      jdbc-url: jdbc:mariadb://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
      username: root
      password: root
    secondary:
      driverClassName: org.postgresql.Driver
      url: jdbc:postgresql://192.168.1.5:5432/postgres?currentSchema=demo
      username: postgres
      password: postgres

配置要点

  • 每个数据库要分别指定名称，必须有一个主数据库。本例中主数据库为 main，次数据库为 secondary
  • 本例使用 Hikari 作为连接池。多数据库的地址字段为应显示指定为 jdbc-url 而非 url。单个数据库时，可配置为 url。主数据库使用 @ConfigurationProperties 注解指定相关配置项。为便于对比，次数据库使用另一种等效方式：从配置文件指定的位置取值，此时不受 jdbc-url 或 url 的限制。
  • pg 数据库有「模式（Schema）」的概念，配置文件可指定模式，如果不指定，则相应 sql 语句需要带上模式名称，否则默认指向 public，如：insert into demo.test_table(name, age) VALUES ('haha',123);

3、配置数据源

MainDataSourceConfig.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
@Configuration
@MapperScan(basePackages = "com.example.dao.main", sqlSessionTemplateRef = "mainSqlSessionTemplate")
public class MainDataSourceConfig {

    @Bean(name = "mainDataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.main")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mainSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("mainDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/main/*.xml"));
        sessionFactory.setTypeAliasesPackage("com.example.entity");

        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setCallSettersOnNulls(true);
        configuration.setLogImpl(StdOutImpl.class);
        sessionFactory.setConfiguration(configuration);

        return sessionFactory.getObject();
    }

    @Bean(name = "mainTransactionManager")
    @Primary
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("mainDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "mainSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("mainSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

SecondaryDataSourceConfig.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
@Configuration
@MapperScan(basePackages = "com.example.dao.secondary", sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class SecondaryDataSourceConfig {

    @Value("${spring.datasource.driverClassName}")
    private String driverClassName;

    @Value("${spring.datasource.secondary.url}")
    private String url;

    @Value("${spring.datasource.secondary.username}")
    private String user;

    @Value("${spring.datasource.secondary.password}")
    private String password;

    @Bean(name = "secondaryDataSource")
    public DataSource dataSource() {
        DataSourceBuilder<?> dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName(driverClassName);
        dataSourceBuilder.url(url);
        dataSourceBuilder.password(password);
        dataSourceBuilder.username(user);
        return dataSourceBuilder.build();

        // 或者使用 DriverManagerDataSource
        // DriverManagerDataSource dataSource = new DriverManagerDataSource();
        // dataSource.setDriverClassName(driverClassName);
        // dataSource.setUrl(url);
        // dataSource.setUsername(user);
        // dataSource.setPassword(password);
        //
        // return dataSource;
    }

    @Bean(name = "secondarySqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/secondary/*.xml"));
        sessionFactory.setTypeAliasesPackage("com.example.entity");

        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setCallSettersOnNulls(true);
        configuration.setLogImpl(StdOutImpl.class);
        sessionFactory.setConfiguration(configuration);

        return sessionFactory.getObject();
    }

    @Bean(name = "secondaryTransactionManager")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "secondarySqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

配置要点

  • 如果使用注解，可不配置 classpath*:mapper/secondary/*.xml

4、数据表及 dao 接口

1）测试表定义

mariadb

1
2
3
4
5
6
7
create table test_table
(
    id   bigint auto_increment
        primary key,
    name char(5) not null,
    age  int     not null
);

pg

1
2
3
4
5
6
7
8
9
10
11
create table test_table
(
    id   bigserial
        constraint test_table_pk
            primary key,
    name char(5) not null,
    age  integer not null
);

alter table test_table
    owner to postgres;

2）dao

IMain

1
2
3
4
5
6
7
@Repository
@Mapper
public interface IMain {

    @Insert("insert into test_table(name, age) VALUES (#{name},23);")
    void insertData(String name);
}

ISecondary

1
2
3
4
5
6
7
@Repository
@Mapper
public interface ISecondary {

    @Insert("insert into test_table(name, age) VALUES (#{name},23);")
    void insertData(String name);
}

配置要点

  • pg 数据库如果在配置文件不指定模式，则相应 sql 语句需要带上模式名称，如：insert into demo.test_table(name, age)，否则默认指向 public

5、测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@SpringBootTest
class MutiDatabaseApplicationTests {

    @Resource
    private ISecondary iSecondary;

    @Resource
    private IMain iMain;

    @Test
    void contextLoads() {
        iSecondary.insertData("jack");
        iMain.insertData("mark");
    }

}