springboot项目配置双数据源，分别用sqlite和mysql数据库，并且利用slqite存储mysql连接信息

时间：2024-05-31 10:32:11

之前项目中有需求配置简单的双数据源，之前百度好久，给出的结果擦边球信息非常多，也许是我的技术有限吧，我只需要可以用双双数据源就可以了，其它先不考虑，所谓功夫不负有心人吧，最后经过群友 的帮助和我自己的实践，最终实现了效果，当然，我这里的配置双数据源比较适合初学者，很简单的功能，就是单纯的springboot项目可以使用双数据源就行，好了，（看到这里的估计心里已经抱怨：我只不过是来解决问题的，不是听你来说写文章前因后果的），最后说一句，我也是怕忘所以做下记录。。。。。

现在上干货

1.首先引jar包，除了springboot web工程基础jar包外需要如下jar包，其中红色的是配置双数据源最重要的：

<dependency><!--tomcat下运行-->
   <groupId>org.apache.tomcat</groupId>
   <artifactId>tomcat-dbcp</artifactId>
   <version>7.0.75</version>
</dependency>
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-tomcat</artifactId>
</dependency>

<dependency><!--mybatis持久层-->
   <groupId>org.mybatis.spring.boot</groupId>
   <artifactId>mybatis-spring-boot-starter</artifactId>
   <version>1.1.1</version>
</dependency>
<dependency><!--mysql数据库连接-->
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency><!--sqlite数据库连接-->
   <groupId>org.xerial</groupId>
   <artifactId>sqlite-jdbc</artifactId>
   <version>3.21.0.1</version>
</dependency>

<dependency><!--配置双数据源必须要导的jar包-->
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-configuration-processor</artifactId>
   <optional>true</optional>
</dependency>

2.application.properties配置2个数据库属性内容：


# sqlite库
spring.datasource.test.url=jdbc:sqlite:/sqlite/testDB.db
spring.datasource.test.username=
spring.datasource.test.password=
#spring.datasource.test.driver-class-name=org.sqlite.JDBC
#连接池配置
# 省略

# mysql库（在我这里因为mysql属性读取sqlite库汇总内容，所以在这里没有起作用，                                           如果指定不变的双数据库源，这里必须要有）
spring.datasource.test1.url=jdbc:mysql://localhost/work_account?characterEncoding=utf8&useSSL=true
spring.datasource.test1.username=root
spring.datasource.test1.password=root
#spring.datasource.test1.driver-class-name=com.mysql.jdbc.Driver

3.springboot启动类application类中类注释@SpringBootApplication需要加上exclude来阻止工程随tomcat启动默认读取application.properties中的配置，内容如下：

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

4.项目具体目录结构如下图，其中红色框内的是新建内容：

springboot项目配置双数据源，分别用sqlite和mysql数据库，并且利用slqite存储mysql连接信息

config包内是项目随tomcat启动读取配置内容的地方，其中DBSqliteConfig类和DBMysqlConfig是映射application.properties数据库配置属性的类，具体内容如下：

DBSqliteConfig.java

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
//映射application.properties中对应的名字
@ConfigurationProperties(prefix = "spring.datasource.test")
@Component
public class DBSqliteConfig {

   private String url;
   private String username;
   private String password;

   public String getUrl() {
      return url;
   }

   public void setUrl(String url) {
      this.url = url;
   }

   public String getUsername() {
      return username;
   }

   public void setUsername(String username) {
      this.username = username;
   }

   public String getPassword() {
      return password;
   }

   public void setPassword(String password) {
      this.password = password;
   }

}

DBMysqlConfig

import org.springframework.stereotype.Component;

//@ConfigurationProperties(prefix = "spring.datasource.test1")
// 由于MybatisDbMysqlConfig类testDataSource方法中通过查表获取的mysqlurl连接信息，所以这里就这个注//解不需要映射
@Component
public class DBMysqlConfig {

   private String url;
   private String username;
   private String password;
   private String ip;
   private String dataname;
   private String port;

   public String getUrl() {
      return url;
   }

   public void setUrl(String url) {
      this.url = url;
   }

   public String getUsername() {
      return username;
   }

   public void setUsername(String username) {
      this.username = username;
   }

   public String getPassword() {
      return password;
   }

   public void setPassword(String password) {
      this.password = password;
   }

   public String getIp() {
      return ip;
   }

   public void setIp(String ip) {
      this.ip = ip;
   }

   public String getDataname() {
      return dataname;
   }

   public void setDataname(String dataname) {
      this.dataname = dataname;
   }

   public String getPort() {
      return port;
   }

   public void setPort(String port) {
      this.port = port;
   }
}

sqlite包内是操作sqlite数据库的所有操作，MybatisDbSqliteConfig类映射sqlite包内的操作；mysql包内是操作mysql数据库的所有操作，MybatisDbMysqlConfig类映射mysql包内的操作，具体如下：

MybatisDbSqliteConfig.java

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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.sqlite.SQLiteDataSource;

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


@Configuration//配置的注解
@MapperScan(basePackages = {"com.jhsec.demosqlite.sqlite"}, sqlSessionTemplateRef = "testSqlSessionTemplate")
public class MybatisDbSqliteConfig {

    // 配置数据源,Primary是主数据源，就是tomcat启动先走的数据库
     @Primary
    @Bean(name = "testDataSource")
    public DataSource testDataSource(DBSqliteConfig testConfig) throws SQLException {

        SQLiteDataSource sqLiteDataSource=new SQLiteDataSource();
        sqLiteDataSource.setUrl(testConfig.getUrl());
        sqLiteDataSource.setDatabaseName("testDataSource");



/*        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource();
        xaDataSource.setUniqueResourceName("testDataSource");*/

        return sqLiteDataSource;
    }

    @Bean(name = "testSqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("testDataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "testSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("testSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

MybatisDbMysqlConfig.java

import com.jhsec.demosqlite.sqlite.service.FilmService;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
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 javax.sql.DataSource;
import java.sql.SQLException;

@Configuration//配置的注解
@MapperScan(basePackages = {"com.jhsec.demosqlite.mysql"}, sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class MybatisDbMysqlConfig {


    @Autowired
    private FilmService filmService;


    // 配置数据源
    @Bean(name = "test2DataSource")
    public DataSource testDataSource(DBMysqlConfig testConfig) throws SQLException {
       /* MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
        mysqlXaDataSource.setUrl(testConfig.getUrl());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
        mysqlXaDataSource.setPassword(testConfig.getPassword());
        mysqlXaDataSource.setUser(testConfig.getUsername());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);

        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(mysqlXaDataSource);
        xaDataSource.setUniqueResourceName("test2DataSource");*/
        //这里是通过查sqlite数据库中表的数据，得到mysql的连接信息
        testConfig= filmService.getMysqlInfo();

        MysqlDataSource mysqlDataSource=new MysqlDataSource();
        mysqlDataSource.setUrl("jdbc:mysql://"+testConfig.getIp()+":"+testConfig.getPort()+"/"+testConfig.getDataname()+"?characterEncoding=utf8&useSSL=true");
        mysqlDataSource.setUser(testConfig.getUsername());
        mysqlDataSource.setPassword(testConfig.getPassword());
        mysqlDataSource.setDatabaseName("test2DataSource");

        return mysqlDataSource;
    }

    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    @Bean(name = "test2SqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}
5.最后如何是指定不变的2个数据源的话就可以把DBMysqlConfig类名上的注释去掉和MybatisDbMysqlConfig类中testDataSource方法testConfig= filmService.getMysqlInfo();这一行注释掉就可以。这里不知道如何上传源码，如还有不懂的可以留言鄙人，欢迎评论。



相关文章