×

springboot 多数据源+druid+ MyBatis+分页插件

96
lucode
2017.12.31 01:56 字数 659

第一步:

需要禁止掉springboot自生的DataSourceAutoConfiguration
因为它会默认会读取application.properties文件的spring.datasource.*属性并自动配置单数据源。
启动类上的注解修改为:

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

第二步

修改配置文件,增加多数据源

#  第一数据库
spring.datasource.pay.url=127.0.0.1:3306
spring.datasource.pay.username=lucode
spring.datasource.pay.password=123
spring.datasource.pay.initialSize=5
spring.datasource.pay.maxActive=20
spring.datasource.pay.minIdle=1
spring.datasource.pay.maxWait=30000
spring.datasource.pay.timeBetweenEvictionRunsMillis=60000
spring.datasource.pay.minEvictableIdleTimeMillis=300000
spring.datasource.pay.poolPreparedStatements=true
spring.datasource.pay.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.pay.filters=stat, wall, config

# 第二个数据库
spring.datasource.travel.url=99.99.99.99:3306
spring.datasource.travel.username=lucode
spring.datasource.travel.password=123
spring.datasource.travel.initialSize=5
spring.datasource.travel.maxActive=20
spring.datasource.travel.minIdle=1
spring.datasource.travel.maxWait=30000
spring.datasource.travel.timeBetweenEvictionRunsMillis=60000
spring.datasource.travel.minEvictableIdleTimeMillis=300000
spring.datasource.travel.poolPreparedStatements=true
spring.datasource.travel.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.travel.filters=stat, wall, config

第三步

Druid数据源读取配置文件或者普通数据源配置

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@ConfigurationProperties(prefix = "spring.datasource.pay")
public class DruidConfigMetroPay {
    public String onoff;
    public String url;
    public String username;
    public String password;
    public int initialSize;
    public int maxActive;
    public int minIdle;
    public long maxWait;
    public long timeBetweenEvictionRunsMillis;
    public long minEvictableIdleTimeMillis;
    public boolean poolPreparedStatements;
    public int maxPoolPreparedStatementPerConnectionSize;
    public String filters;
    public String connectionProperties;

    public DruidConfigMetroPay() {
    }

    public DruidConfigMetroPay(String url, String username, String password, int initialSize, int maxActive, int minIdle,
                               long maxWait, long timeBetweenEvictionRunsMillis, long minEvictableIdleTimeMillis,
                               boolean poolPreparedStatements, int maxPoolPreparedStatementPerConnectionSize) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.initialSize = initialSize;
        this.maxActive = maxActive;
        this.minIdle = minIdle;
        this.maxWait = maxWait;
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
        this.poolPreparedStatements = poolPreparedStatements;
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;

    }

    @Bean(name="druidDataSourceMetroPay")
    public DataSource druidDataSourceMetroPay() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxWait(maxWait);
        druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        druidDataSource.setConnectionProperties(connectionProperties);
        try {
            druidDataSource.setFilters("stat,slf4j");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            druidDataSource.setFilters(filters);
            druidDataSource.init();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return druidDataSource;
    }
//  此处省略 get/set 方法
}

注意:

  • druidDataSourceMetroPay() 这个方法的 Bean最好加一下命名。
  • ConfigurationProperties 的prefix 指定自定义配置的前缀。如果不嫌麻烦,可以一个一个注入到成员变量。
    同理另外一个德鲁伊数据源
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@ConfigurationProperties(prefix = "spring.datasource.travel")
public class DruidConfigMetroTravel {

    public String onoff;
    public String url;
    public String username;
    public String password;
    public int initialSize;
    public int maxActive;
    public int minIdle;
    public long maxWait;
    public long timeBetweenEvictionRunsMillis;
    public long minEvictableIdleTimeMillis;
    public boolean poolPreparedStatements;
    public int maxPoolPreparedStatementPerConnectionSize;
    public String filters;
    public String connectionProperties;

    public DruidConfigMetroTravel() {
    }

    public DruidConfigMetroTravel(String url, String username, String password, int initialSize, int maxActive, int minIdle,
                                  long maxWait, long timeBetweenEvictionRunsMillis, long minEvictableIdleTimeMillis,
                                  boolean poolPreparedStatements, int maxPoolPreparedStatementPerConnectionSize) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.initialSize = initialSize;
        this.maxActive = maxActive;
        this.minIdle = minIdle;
        this.maxWait = maxWait;
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
        this.poolPreparedStatements = poolPreparedStatements;
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;

    }

    @Bean(name="druidDataSourceMetroTravel")
    public DataSource druidDataSourceMetroPay() {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        druidDataSource.setInitialSize(initialSize);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxWait(maxWait);
        druidDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        druidDataSource.setConnectionProperties(connectionProperties);
        try {
            druidDataSource.setFilters("stat,slf4j");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            druidDataSource.setFilters(filters);
            druidDataSource.init();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return druidDataSource;
    }

这样就定义了两个德鲁伊数据源了.
我们分别命名为druidDataSourceMetroPaydruidDataSourceMetroTravel
两个数据源对应着配置上的两个数据库。

第四步

下面简单整合一下德鲁伊的基础配置,只是简单做一个用户名密码的的配置,详细配置.....不说了。
值得注意的是这个配置必须优先,所以必须加上@Order这个配置。

import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
/**
 * @author yunfeng.lu
 * @create 2017/12/28.
 */
@Configuration
public class DruidServletConfig {
    @Bean
    @Order
    public ServletRegistrationBean statViewServlet() {
        StatViewServlet servlet = new StatViewServlet();
        ServletRegistrationBean bean = new ServletRegistrationBean(servlet, "/druid/*");
        bean.addInitParameter("loginUsername", "lucode");
        bean.addInitParameter("loginPassword", "lucode123");
        return bean;
    }
}

第五步

注册MyBatis分页插件PageHelper

@Configuration
public class MybatisPageHelperConfig {
    @Bean
    public PageInterceptor pageHelper() {
        PageInterceptor pageHelper = new PageInterceptor();
        Properties p = new Properties();
        p.setProperty("offsetAsPageNum", "true");
        p.setProperty("rowBoundsWithCount", "true");
        p.setProperty("reasonable", "true");
        pageHelper.setProperties(p);
        return pageHelper;
    }
}

第六步

最后一步,也是将Druid数据源整合到 MyBatis 上以及pageHelper的整合。

import com.github.pagehelper.PageInterceptor;
import org.apache.ibatis.plugin.Interceptor;
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.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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;
/**
 * @author yunfeng.lu
 * @create 2017/12/28.
 */
@MapperScan(basePackages = "com.lucode.**.dao.pay",
        sqlSessionFactoryRef = "sqlSessionFactoryMetroPay",
        sqlSessionTemplateRef = "sqlSessionTemplateMetroPay")
@Configuration
public class MyBatisConfigMetroPay {
//    如果不集成  德鲁伊数据库连接池,需要使用普通的数据源,也在这里配置
//    @Autowired
//    @Qualifier("metroPay")
//    private DataSource dataSourceMetroPay;

    /**
     * 德鲁伊数据源
     */
    private final DataSource druidDataSourceMetroPay;

    private final PageInterceptor pageHelper;

    @Autowired
    public MyBatisConfigMetroPay(@Qualifier("druidDataSourceMetroPay") DataSource druidDataSourceMetroPay, PageInterceptor pageHelper) {
        this.druidDataSourceMetroPay = druidDataSourceMetroPay;
        this.pageHelper = pageHelper;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactoryMetroPay() throws Exception {

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(druidDataSourceMetroPay);
        sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageHelper});
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(
                resolver.getResources("classpath*:com/lucode/**/dao/pay/*.xml"));

        SqlSessionFactory sqlSessionFactory = null;
        try {
            sqlSessionFactory = sqlSessionFactoryBean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            System.exit(0);
        }
        org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean("metroPayTransaction")
    public DataSourceTransactionManager transactionManager() throws Exception {
        return new DataSourceTransactionManager(druidDataSourceMetroPay);
    }
    @Bean
    public SqlSessionTemplate sqlSessionTemplateMetroPay() throws Exception {
        return new SqlSessionTemplate(sqlSessionFactoryMetroPay());
    }
}

解释一下

  • MyBatisConfigMetroPay 的构造方法,完成了DataSource,PageInterceptor的注入,注意的是 DataSource的注入需要标注一下注入 bean 的名字,也就是刚刚上面配置的 Bean 的名字druidDataSourceMetroPay。

  • MapperScan 这个扫描注解,需要配置扫描的路径,这里多说一下,多数据源的 dao 层,两个数据库的 dao,需要分开放,比如我们这里分了 travel 和 pay 两个包放 xxxmapper.xml和xxxmapper.java


    image.png

    至于 pojo 随便你,放在一起也没事。

  • sqlSessionFactoryMetroPay和sqlSessionTemplateMetroPay其实配置一也就可以,但是你配置两个系统会忽略一个。

  • transactionManager()这个方法需要特别注意的,这个事务相关的,如果不配置这个,开启事务的时候,就会出问题。
    配置也比较简单,标注一下 Bean 的明细,将数据源赋值进去即可。
    那我们在业务层使用声明式事务注解如何使用呢?

@Transactional(propagation = Propagation.REQUIRED, 
rollbackFor = Exception.class,
transactionManager = "metroPayTransaction")// 就是在这里指定一下事务
    public void transferCancel(InsideTransferConfirmModel model) {
      //忽略方法具体内容
}

同理另外一个数据库的就在配置一个
最后文件是这样的


image.png

DruidConfigMetroPay和DruidConfigMetroTravel 完成德鲁伊数据源的配置

DruidServletConfig和MybatisPageHelperConfig分别完成德鲁伊和分页插件的配置

MyBatisConfigMetroTravel和MyBatisConfigMetroPay最后完成他们的整合

至于DataSourceConfig其实是不适用德鲁伊数据库连接池数据源的配置

@Configuration
public class DataSourceConfig {
    @Bean(name = "metroPay")
    @ConfigurationProperties(prefix = "spring.datasource.pay")
    public DataSource dataSourceMetroPay() {
        return DataSourceBuilder.create().build();
    }


    @Bean(name = "metroTravel")
    @ConfigurationProperties(prefix = "spring.datasource.travel")
    public DataSource dataSourceMetroTravel() {
        return DataSourceBuilder.create().build();
    }

}
java
Web note ad 1