从零搭建自己的SpringBoot后台框架(十一)

不念不忘少年蓝@ 2023-08-17 15:32 94阅读 0赞

### 一：配置数据源 ###

修改application.properties

spring.datasource.db1.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.db1.url=jdbc:mysql://localhost:3333/demo?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&failOverReadOnly=false
    spring.datasource.db1.username=root
    spring.datasource.db1.password=123456
    spring.datasource.db1.initialSize=5
    spring.datasource.db1.minIdle=5
    spring.datasource.db1.maxActive=20
    #连接等待超时时间
    spring.datasource.db1.maxWait=60000
    #配置隔多久进行一次检测(检测可以关闭的空闲连接)
    spring.datasource.db1.timeBetweenEvictionRunsMillis=60000
    #配置连接在池中的最小生存时间
    spring.datasource.db1.minEvictableIdleTimeMillis=300000
    spring.datasource.db1.validationQuery=SELECT 1 FROM DUAL
    spring.datasource.db1.testWhileIdle=true
    spring.datasource.db1.testOnBorrow=false
    spring.datasource.db1.testOnReturn=false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    spring.datasource.db1.poolPreparedStatements=true
    spring.datasource.db1.maxPoolPreparedStatementPerConnectionSize=20
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    spring.datasource.db1.filters=stat,wall,slf4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    spring.datasource.db1.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    
    
    spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
    spring.datasource.db2.url=jdbc:mysql://localhost:3306/chuchen?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&autoReconnect=true&failOverReadOnly=false
    spring.datasource.db2.username=root
    spring.datasource.db2.password=123456
    spring.datasource.db2.initialSize=5
    spring.datasource.db2.minIdle=5
    spring.datasource.db2.maxActive=20
    #连接等待超时时间
    spring.datasource.db2.maxWait=60000
    #配置隔多久进行一次检测(检测可以关闭的空闲连接)
    spring.datasource.db2.timeBetweenEvictionRunsMillis=60000
    #配置连接在池中的最小生存时间
    spring.datasource.db2.minEvictableIdleTimeMillis=300000
    spring.datasource.db2.validationQuery=SELECT 1 FROM DUAL
    spring.datasource.db2.testWhileIdle=true
    spring.datasource.db2.testOnBorrow=false
    spring.datasource.db2.testOnReturn=false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    spring.datasource.db2.poolPreparedStatements=true
    spring.datasource.db2.maxPoolPreparedStatementPerConnectionSize=20
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    spring.datasource.db2.filters=stat,wall,slf4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    spring.datasource.db2.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

注意修改为自己的数据库连接

### 二：创建数据源配置 ###

***MysqlDataSource1Config***

package com.example.demo.core.configurer.dataSource;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.io.Resource;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    import tk.mybatis.spring.annotation.MapperScan;
    
    import javax.sql.DataSource;
    
    @Configuration
    @EnableTransactionManagement
    @MapperScan(basePackages = {"com.example.demo.dao.db1"})
    public class MysqlDataSource1Config {
    
        @Bean(name = "primaryDataSource")
        //需设置主数据源
        @Primary
        @ConfigurationProperties(prefix="spring.datasource.db1")
        public DataSource dataSource(){
            //跟之前不一样了
            return new DruidDataSource();
        }
    
        @Bean(name = "primaryTransactionManager")
        @Primary
        public DataSourceTransactionManager masterTransactionManager() {
            return new DataSourceTransactionManager(dataSource());
    
        }
    
        @Bean(name="primarySqlSessionFactory")
        @Primary
        public SqlSessionFactory sqlSessionFactory(@Qualifier("primaryDataSource")DataSource primaryDataSource) throws Exception {
            SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(primaryDataSource);
            Resource[] mapperLocations = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db1/*.xml");
            sessionFactory.setMapperLocations(mapperLocations);
            return sessionFactory.getObject();
        }
    }

***MysqlDataSource2Config***

package com.example.demo.core.configurer.dataSource;
    
    
    import com.alibaba.druid.pool.DruidDataSource;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.Resource;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import tk.mybatis.spring.annotation.MapperScan;
    
    import javax.sql.DataSource;
    
    @Configuration
    @MapperScan(basePackages = {"com.example.demo.dao.db2"}, sqlSessionFactoryRef = "secondSqlSessionFactory")
    public class MysqlDataSource2Config {
    
        @Bean(name = "secondDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.db2")
        public DataSource dataSource() {
            return new DruidDataSource();
        }
    
        @Bean(name = "secondSqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory() throws Exception {
            SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(dataSource());
            Resource[] mapperLocations = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/db2/*.xml");
            sessionFactory.setMapperLocations(mapperLocations);
            return sessionFactory.getObject();
        }
    }

### 三：修改目录结构 ###

目录结构如下图，db1为主数据源，db2为从数据源，这样根据访问dao层不同，会自动进行数据源切换

![format_png][]

### 四：功能测试 ###

输入`http://localhost:8080/userInfo/selectAll`

结果：

{
        "code": 200,
        "data": {
            ....省略
            "list": [
                {
                    "id": "1",
                    "userName": "Mr_初晨"
                },
                {
                    "id": "2",
                    "userName": "Mr_初晨"
                }
            ],
            .....省略
        },
        "msg": "success"
    }

输入`http://localhost:8080/userLabel/list`

结果：

{
        "code": 200,
        "data": {
            ....省略
            "list": [
                 {
                    "createTime": 1523283547000,
                    "id": "24084baed3c6409a8619a635bf4428a1",
                    "labelId": "4",
                    "userId": "1"
                }
            ],
            .....省略
        },
        "msg": "success"
    }

输入`http://localhost:8080/druid/datasource.html`

也可以看见有两个数据源

[format_png]: /images/20230810/1e1a2a37dcab4481b62fc3382d0ac0e0.png