13. 從零開始學(xué)springboot-jdbc-多數(shù)據(jù)源

前言

上一節(jié)實現(xiàn)了springboot jpa多數(shù)據(jù)源案例居灯，本節(jié)將實現(xiàn)springboot jdbc多數(shù)據(jù)源案例

創(chuàng)建項目

IDEA創(chuàng)建一個springboot空項目即可勉耀，過程略

添加依賴

pom.xml:

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

1.png

添加配置

application.yml:

spring:
  datasource:
    master:
      username: root
      password: 123456
      jdbc-url: jdbc:mysql://192.168.145.131:3306/test
#      url: jdbc:mysql://192.168.145.131:3306/test
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave:
      username: root
      password: 123456
      jdbc-url: jdbc:mysql://192.168.145.131:3306/test2
#      url: jdbc:mysql://192.168.145.131:3306/test2
      driver-class-name: com.mysql.cj.jdbc.Driver
  jpa:
    properties:
      hibernate:
        hbm2ddl:
          auto: update

建庫

我們新增test/test2數(shù)據(jù)庫
test新建表

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `grade` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', '1', '1', '1');

test2新建表：

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL,
  `age` varchar(255) DEFAULT NULL,
  `course` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `teacher` VALUES ('1', '1', '1', '1');

完善

目錄結(jié)構(gòu)

2.png

config/DataSource:

package com.mrcoder.sbjdbcmultidb.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    //方案一
    @Primary
    @Bean(name = "masterDataSource")
    @Qualifier("masterDataSource")
    @ConfigurationProperties(prefix="spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "slaveDataSource")
    @Qualifier("slaveDataSource")
    @ConfigurationProperties(prefix="spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "masterJdbcTemplate")
    @Qualifier("masterJdbcTemplate")
    public JdbcTemplate masterJdbcTemplate(@Qualifier("masterDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "slaveJdbcTemplate")
    @Qualifier("slaveJdbcTemplate")
    public JdbcTemplate slaveJdbcTemplate(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }




    //方案二(請將方案一注釋贫贝，application.yml修改jdbc-url為url)

//    //master庫
//    @Primary
//    @Bean(name = "masterDataSourceProperties")
//    @Qualifier("masterDataSourceProperties")
//    @ConfigurationProperties(prefix = "spring.datasource.master")
//    public DataSourceProperties masterDataSourceProperties() {
//        return new DataSourceProperties();
//    }
//
//    @Primary
//    @Bean(name = "masterDataSource")
//    @Qualifier("masterDataSource")
//    @ConfigurationProperties(prefix = "spring.datasource.master")
//    public DataSource masterDataSource(@Qualifier("masterDataSourceProperties") DataSourceProperties dataSourceProperties) {
//        return dataSourceProperties.initializeDataSourceBuilder().build();
//    }
//
//    //slave庫
//    @Bean(name = "slaveDataSourceProperties")
//    @Qualifier("slaveDataSourceProperties")
//    @ConfigurationProperties(prefix = "spring.datasource.slave")
//    public DataSourceProperties slaveDataSourceProperties() {
//        return new DataSourceProperties();
//    }
//
//    @Bean(name = "slaveDataSource")
//    @Qualifier("slaveDataSource")
//    @ConfigurationProperties(prefix = "spring.datasource.slave")
//    public DataSource slaveDataSource(@Qualifier("slaveDataSourceProperties") DataSourceProperties dataSourceProperties) {
//        return dataSourceProperties.initializeDataSourceBuilder().build();
//    }

}

entity/Student:

package com.mrcoder.sbjdbcmultidb.entity;

public class Student {
    private int id;

    private String name;

    private int age;

    private int grade;

    public Student() {
    }

    public Student(String name, int age, int grade) {
        this.name = name;
        this.age = age;
        this.grade = grade;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", grade=" + grade +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }
}

entity/Teacher:

package com.mrcoder.sbjdbcmultidb.entity;

public class Teacher {

    private int id;
    private String name;
    private String age;
    private String course;

    public Teacher() {
    }

    public Teacher(String name, String age, String course) {
        this.name = name;
        this.age = age;
        this.course = course;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age='" + age + '\'' +
                ", course='" + course + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }
}

controller/JdbcMultidbController:

package com.mrcoder.sbjdbcmultidb.controller;

import com.mrcoder.sbjdbcmultidb.entity.Student;
import com.mrcoder.sbjdbcmultidb.entity.Teacher;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class JdbcMultidbController {


    //master
    @Autowired
    @Qualifier("masterJdbcTemplate")
    protected JdbcTemplate masterTempleate;

    //slave
    @Autowired
    @Qualifier("slaveJdbcTemplate")
    protected JdbcTemplate slaveTempleate;

    @RequestMapping(value = "/list")
    public void list(){
        String studentSql = "select * from student";
        RowMapper<Student> studentRowMapper = new BeanPropertyRowMapper<>(Student.class);
        List<Student> studentList = masterTempleate.query(studentSql, studentRowMapper);

        String teacherSql = "select * from teacher";
        RowMapper<Teacher> teacherRowMapper = new BeanPropertyRowMapper<>(Teacher.class);
        List<Teacher> teacherList = slaveTempleate.query(teacherSql, teacherRowMapper);

        System.out.println(studentList);
        System.out.println(teacherList);

    }
}

運行

3.png

項目地址

https://github.com/MrCoderStack/SpringBootDemo/tree/master/sb-jdbc-multidb

https://gitee.com/MrCoderStack/SpringBootDemo/tree/master/sb-jdbc-multidb

Tip

關(guān)于數(shù)據(jù)源配置文件中的注釋部分請查看我的另一篇博文

13.1.從零開始學(xué)springboot-jdbcUrl報錯問題

請關(guān)注我的訂閱號

訂閱號.png