完整Demo：springboot实现多数据源配置

背景

公司有一套人脸识别动态布控系统，该系统有两个子系统组成，识别算法采用C++编写，后台管理系统采用Java编写，C程序提供HTTP接口供Java程序调用，两个程序都是本地化部署。现在有个问题，C程序是南理工学生写的，需求响应不及时，接口不能立马提供，所以考虑由Java程序配置多数据源直接读C程序的数据库。

整体代码结构

config:两个数据库的配置

entity：实体类

test1/test2：Dao层，操作数据库，要在config中分别配置dao层包路径：basePackages = "com.example.demo.test1"

web：测试用的，通过浏览器或postman调接口，分别往两个数据库中插数据

项目源码下载地址：点击跳转

第一步：创建项目

使用Spring官网提供的在线工具创建springboot项目，最简单的即可。

工具地址：https://start.spring.io/

第二步：引入依赖jar包

<dependencies>

    <dependency>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-web</artifactId>

    </dependency>

    <dependency>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-test</artifactId>

        <scope>test</scope>

    </dependency>

    <!--freemarker支持-->

    <dependency>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-freemarker</artifactId>

    </dependency>

    <!-- mysql驱动 -->

    <dependency>

        <groupId>mysql</groupId>

        <artifactId>mysql-connector-java</artifactId>

    </dependency>

    <!--整合mybatis-->

    <dependency>

        <groupId>org.mybatis.spring.boot</groupId>

        <artifactId>mybatis-spring-boot-starter</artifactId>

        <version>1.1.1</version>

    </dependency>

</dependencies>

第三步：添加系统配置文件application.properties

server.port=8080

server.servlet.context-path=/demo

## test1 数据源配置

spring.datasource.test1.driverClassName=com.mysql.jdbc.Driver

spring.datasource.test1.jdbc-url=jdbc:mysql://192.168.1.12:3306/test?useUnicode=true&characterEncoding=utf8

spring.datasource.test1.username=root

spring.datasource.test1.password=123456

## test2 数据源配置

spring.datasource.test2.driverClassName=com.mysql.jdbc.Driver

spring.datasource.test2.jdbc-url=jdbc:mysql://192.168.1.12:3306/test2?useUnicode=true&characterEncoding=utf8

spring.datasource.test2.username=root

spring.datasource.test2.password=123456

第四步：添加数据源配置

数据库test配置DB1Config

/**

 * @author ：xy.hero@qq.com

 * @date ：Created in 2019-07-26 15:44

 * @description：www.jiagou1216.com

 */

@Configuration

@MapperScan(basePackages = "com.example.demo.test1", sqlSessionFactoryRef = "test1SqlSessionFactory")

public class DB1Config {

    @Bean(name = "test1DataSource")

    @ConfigurationProperties(prefix = "spring.datasource.test1")

    @Primary

    public DataSource testDataSource() {

        return DataSourceBuilder.create().build();

    }

    @Bean(name = "test1SqlSessionFactory")

    @Primary

    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {

        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

        bean.setDataSource(dataSource);

        return bean.getObject();

    }

    @Bean(name = "test1TransactionManager")

    @Primary

    public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {

        return new DataSourceTransactionManager(dataSource);

    }

    @Bean(name = "test1SqlSessionTemplate")

    @Primary

    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

        return new SqlSessionTemplate(sqlSessionFactory);

    }

}

数据库test2配置DB2Config

/**

 * @author ：xy.hero@qq.com

 * @date ：Created in 2019-07-26 15:50

 * @description：www.jiagou1216.com

 */

@Configuration

@MapperScan(basePackages = "com.example.demo.test2", sqlSessionFactoryRef = "test2SqlSessionFactory")

public class DB2Config {

    @Bean(name = "test2DataSource")

    @ConfigurationProperties(prefix = "spring.datasource.test2")

    public DataSource testDataSource() {

        return DataSourceBuilder.create().build();

    }

    @Bean(name = "test2SqlSessionFactory")

    public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {

        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

        bean.setDataSource(dataSource);

        return bean.getObject();

    }

    @Bean(name = "test2TransactionManager")

    public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) {

        return new DataSourceTransactionManager(dataSource);

    }

    @Bean(name = "test2SqlSessionTemplate")

    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {

        return new SqlSessionTemplate(sqlSessionFactory);

    }

}

第五步：其他类，数据库建表

CREATE TABLE `book` (

  `bookid` int(11) NOT NULL AUTO_INCREMENT,

  `bookname` varchar(255) DEFAULT NULL,

  `bookprice` decimal(10,2) DEFAULT NULL,

  PRIMARY KEY (`bookid`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Book

/**

 * @author ：xy.hero@qq.com

 * @date ：Created in 2019-07-26 15:50

 * @description：www.jiagou1216.com

 */

public class Book {

    private Integer bookid;

    private String bookname;

    private Integer bookprice;

    get/set略...

}

Book1Dao

@Service

public interface Book1Dao {

    @Select("select * from book where bookname=#{bookname}")

    public Book findByName(@Param("bookname") String bookname);

    @Insert("insert into book(bookname,bookprice) values (#{bookname},#{bookprice})")

    public int insertBook(@Param("bookname") String bookname, @Param("bookprice") Double bookprice);

    @Select("select * from book")

    public Book findBook(@Param("Book") Book book);

}

Book2Dao

@Service

public interface Book2Dao {

    @Select("select * from book where bookname=#{bookname}")

    public Book findByName(@Param("bookname") String bookname);

    @Insert("insert into book(bookname,bookprice) values (#{bookname},#{bookprice})")

    public int insertBook(@Param("bookname") String bookname, @Param("bookprice") Double bookprice);

}

BookController

/**

 * @author ：xy.hero@qq.com

 * @date ：Created in 2019-07-26 15:53

 * @description：www.jiagou1216.com

 */

@RestController

public class BookController {

    @Autowired

    private Book1Dao book1Dao;

    @Autowired

    private Book2Dao book2Dao;

@RequestMapping("insert1")

    public void insert1() {

        book1Dao.insertBook("金瓶梅", 99.9);

        System.out.println("success");

    }

    @RequestMapping("insert2")

    public void insert2() {

        book2Dao.insertBook("帝国时代", 22.2);

        System.out.println("success");

    }

}

第六步：启动项目测试

浏览器访问，查看库中是否已添加数据：

http://localhost:8080/demo/insert1

http://localhost:8080/demo/insert2

如果项目启动报如下错误，请在application.properties配置文件中给每个数据源url后加：&serverTimezone=GMT

即：spring.datasource.test1.jdbc-url=jdbc:mysql://192.168.1.12:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT

异常信息：

java.sql.SQLException: The server time zone value '�й���׼ʱ��' is unrecognized or represents more than one time zone.

You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

http://cdn.jiagou1216.com/20190727003228.png?imageView2/0/q/100|watermark/2/text/aHR0cHM6Ly93d3cuamlhZ291MTIxNi5jb20=/font/5a6L5L2T/fontsize/400/fill/IzAwMDAwMA==/dissolve/100/gravity/SouthEast/dx/10/dy/10