Spring Boot与JDBC多数据源

介绍

在Spring Boot中，如果你需要使用多个数据源（例如连接到不同的数据库），你可以通过配置多个JdbcTemplate实例来实现。

下面是Spring Boot与JdbcTemplate多数据源的整合步骤：

添加依赖：在你的Spring Boot项目的pom.xml文件中添加JdbcTemplate的依赖，如之前所述。

配置数据源：在application.properties（或application.yml）文件中配置多个数据源的连接信息。你可以为每个数据源指定不同的前缀，例如：

# 第一个数据源
spring.datasource.datasource1.url=jdbc:mysql://localhost:3306/database1
spring.datasource.datasource1.username=username1
spring.datasource.datasource1.password=password1
spring.datasource.datasource1.driver-class-name=com.mysql.jdbc.Driver

# 第二个数据源
spring.datasource.datasource2.url=jdbc:mysql://localhost:3306/database2
spring.datasource.datasource2.username=username2
spring.datasource.datasource2.password=password2
spring.datasource.datasource2.driver-class-name=com.mysql.jdbc.Driver

创建数据源和JdbcTemplate Bean：在Spring Boot的配置类中创建多个数据源和对应的JdbcTemplate Bean。你可以使用@Configuration注解和@Bean注解分别创建数据源和JdbcTemplate Bean，并分别给它们命名。

@Configuration
public class DatabaseConfig {

    @Primary
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.datasource1")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "jdbcTemplate1")
    public JdbcTemplate jdbcTemplate1(@Qualifier("dataSource1") DataSource dataSource1) {
        return new JdbcTemplate(dataSource1);
    }

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

    @Bean(name = "jdbcTemplate2")
    public JdbcTemplate jdbcTemplate2(@Qualifier("dataSource2") DataSource dataSource2) {
        return new JdbcTemplate(dataSource2);
    }
}

在上面的示例中，我们创建了两个数据源（dataSource1和dataSource2）以及对应的JdbcTemplate实例（jdbcTemplate1和jdbcTemplate2）。@Primary注解用于指定默认的数据源。

使用JdbcTemplate：在需要使用JdbcTemplate的地方注入对应的JdbcTemplate实例，并使用其提供的方法执行数据库操作。

@Autowired
@Qualifier("jdbcTemplate1")
private JdbcTemplate jdbcTemplate1;

@Autowired
@Qualifier("jdbcTemplate2")
private JdbcTemplate jdbcTemplate2;

public void fetchUsers() {
    List<Map<String, Object>> userList1 = jdbcTemplate1.queryForList("SELECT * FROM users");
    for (Map<String, Object> user : userList1) {
        System.out.println("User from dataSource1: " + user.get("username"));
    }

    List<Map<String, Object>> userList2 = jdbcTemplate2.queryForList("SELECT * FROM users");
    for (Map<String, Object> user : userList2) {
        System.out.println("User from dataSource2: " + user.get("username"));
    }
}

Demo

项目结构

jdbc

项目源码

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class B01SpringBootMultiJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(B01SpringBootMultiJdbcApplication.class, args);
    }

}

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
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 javax.sql.DataSource;

/**
 * CodeCoderCoding
 */
@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.one")
    @Primary
    DataSource dsOne() {
        return DruidDataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties("spring.datasource.two")
    DataSource dsTwo() {
        return DruidDataSourceBuilder.create().build();
    }
}

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

/**
 * @author CodeCoderCoding
 */
@Configuration
public class JdbcTemplateConfig {
    @Bean
    JdbcTemplate jdbcTemplateOne(@Qualifier("dsOne") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
    @Bean
    JdbcTemplate jdbcTemplateTwo(@Qualifier("dsTwo") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @author CodeCoderCoding
 */
@RestController
public class WebsiteController {
    @Autowired
    WebsiteService websiteService;
    @GetMapping("/add-website")
    public void addWebsite(){
        Website website=new Website();
        website.setId(1);
        website.setName("supremepole");
        website.setUrl("https://cs.supremepole.com");
        websiteService.addWebsite(website);
        Website website2=new Website();
        website2.setId(2);
        website2.setName("supremepole interview");
        website2.setUrl("https://interview.supremepole.com");
        websiteService.addWebsite(website2);
    }
    @GetMapping("/get-website")
    public Website getWebsite(){
        return websiteService.getWebsiteById(1);
    }
    @GetMapping("/update-website")
    public void updateWebsite(){
        Website website=new Website();
        website.setId(1);
        website.setName("supremepole algorithm");
        website.setUrl("https://algorithm.supremepole.com");
        websiteService.updateWebsite(website);
    }
    @GetMapping("/delete-website")
    public void deleteWebsite(){
        websiteService.deleteWebsiteById(2);
    }
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author CodeCoderCoding
 */
@Service
public class WebsiteService {
    @Autowired
    WebsiteDao websiteDao;
    public int addWebsite(Website website) {
        return websiteDao.addWebsite(website);
    }
    public int updateWebsite(Website book) {
        return websiteDao.updateWebsite(book);
    }
    public int deleteWebsiteById(Integer id) {
        return websiteDao.deleteWebsiteById(id);
    }
    public Website getWebsiteById(Integer id) {
        return websiteDao.getWebsiteById(id);
    }
    public List<Website> getAllWebsites() {
        return websiteDao.getAllWebsites();
    }
}

package com.supremepole.b01springbootmultijdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author CodeCoderCoding
 */
@Repository
public class WebsiteDao {
    @Resource(name="jdbcTemplateOne")
    JdbcTemplate jdbcTemplateOne;
    @Resource(name="jdbcTemplateTwo")
    JdbcTemplate jdbcTemplateTwo;
    public int addWebsite(Website website) {
        return jdbcTemplateTwo.update("INSERT INTO website(id,name,url) VALUES (?,?,?)",
                website.getId(), website.getName(), website.getUrl());
    }
    public int updateWebsite(Website website) {
        return jdbcTemplateTwo.update("UPDATE website SET name=?,url=? WHERE id=?",
                website.getName(), website.getUrl(), website.getId());
    }
    public int deleteWebsiteById(Integer id) {
        return jdbcTemplateTwo.update("DELETE FROM website WHERE id=?", id);
    }
    public Website getWebsiteById(Integer id) {
        return jdbcTemplateOne.queryForObject("select * from website where id=?",
                new BeanPropertyRowMapper<>(Website.class), id);
    }
    public List<Website> getAllWebsites() {
        return jdbcTemplateOne.query("select * from website",
                new BeanPropertyRowMapper<>(Website.class));
    }
}

package com.supremepole.b01springbootmultijdbc;

/**
 * @author CodeCoderCoding
 */
public class Website {
    private int id;
    private String url;
    private String name;

    public int getId() {
        return id;
    }

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

    public String getUrl() {
        return url;
    }

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

    public String getName() {
        return name;
    }

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

    @Override
    public String toString(){
        return "id="+id+" ,name"+name+" ,url"+url;
    }
}

server.port=8081
# 数据源1
spring.datasource.one.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.one.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.one.url=jdbc:mysql://localhost:3306/spring-demo?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false
spring.datasource.one.username=root
spring.datasource.one.password=123456
# 数据源2
spring.datasource.two.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.two.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.two.url=jdbc:mysql://localhost:3306/spring-demo2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false
spring.datasource.two.username=root
spring.datasource.two.password=123456

spring.jpa.properties.database=mysql
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.properties.show-sql= true

Spring Boot与JDBC多数据源

# Spring Boot与JDBC多数据源

# 介绍

# Demo

# 项目结构

# 项目源码

Spring Boot与JDBC多数据源

介绍

Demo

项目结构

项目源码