Utiliser plusieurs DataSources avec Spring Boot et RoutingDataSource

Objectifs de cette publication
Préparer la base de données
Create Spring Boot Project
Configurer DataSource & RoutingDataSource
DataSourceInterceptor & Configuration
DAO, Controller
Thymeleaf Template

1. Objectifs de cette publication

Parfois vous avez besoin d'une application Web qui se connecte aux multiple bases de données. Et vous pouvez le faire facilement dans Spring Boot.
Spring vous fournit la classe AbstractRoutingDataSource, vous pouvez écire une classe étendue à partir de cette classe. Routing Datasource contient une carte (Map) de réelles sources de données.

Note: Routing Datasource is also a Datasource but it is a special one.

Et DataSource Interceptor va décider quelle DataSource est utilisée respectivement pour chaque enquête de l'utilisateur.

Dans cet article, je stimule un site Web avec 2 datasource. Chaque datasource se connecte à une base de données différente. Une base de données emmagasine le système Publisher (Editer) et une autre base de données stoke les informations du système Advertiser (Annonceur ).

Dans certains cas, vous pouvez également construire un site web multilingue. Vous pouvez créer plus de base de données. Chaque base de données stocke le contenu des publications dans une langue spécifique.

Dans d'autre cas, si votre application a besoin de plusieurs DataSource, et chaque fonction (chaque page) peut travailler simultanément avec multiple DataSource vous pouvez faire référence aux instructions ci-dessous :
Utiliser plusieurs DataSources avec Spring Boot et JPA

2. Préparer la base de données

Je vais créer deux bases de données, une est pour PUBLISHER (Éditeur) et une est pour le système ADVERTISER (Annoncer). Ces deux bases de données ne peuvent pas être de même type. Vous pouvez utiliser Oracle, MySQL ou SQL Server, ...

Database1:

Le système de base de données PUBLISHER a un tableau Publishers.

Database1 - (MySQL, SQL Server, PostGres)

-- ===========================================
-- DATABASE FOR PUBLISHER SYSTEM
-- ===========================================
create table PUBLISHERS
(
  ID Bigint,
  NAME VARCHAR(255),
  Primary key (ID)
);

insert into publishers (ID, NAME)
values (1, 'publisher 1');

insert into publishers (ID, NAME)
values (2, 'publisher 2');

Database1 - (ORACLE)

-- ===========================================
-- DATABASE FOR PUBLISHER SYSTEM
-- ===========================================
create table PUBLISHERS
(
  ID NUMBER(19),
  NAME VARCHAR(255),
  Primary key (ID)
);

insert into publishers (ID, NAME)
values (1, 'publisher 1');

insert into publishers (ID, NAME)
values (2, 'publisher 2');

Commit;

Database 2:

Le système de la base de données de ADVERTISER a un tableau de Advertisers.

Database2 (MySQL, SQL Server, PostGres)

-- ===========================================
-- DATABASE FOR ADVERTISER SYSTEM
-- ===========================================
create table ADVERTISERS
(
  ID Bigint,
  NAME VARCHAR(255),
  Primary key (ID)
);

insert into advertisers (ID, NAME)
values (1, 'Advertiser 1');

insert into advertisers (ID, NAME)
values (2, 'Advertiser 2');

Database2 - (ORACLE)

-- ===========================================
-- DATABASE FOR ADVERTISER SYSTEM
-- ===========================================
create table ADVERTISERS
(
  ID NUMBER(19),
  NAME VARCHAR(255),
  Primary key (ID)
);

insert into advertisers (ID, NAME)
values (1, 'Advertiser 1');

insert into advertisers (ID, NAME)
values (2, 'Advertiser 2');

Commit;

3. Create Spring Boot Project

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
    http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.o7planning</groupId>
    <artifactId>SpringBootRoutingDS</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>SpringBootRoutingDS</name>
    <description>Spring Boot + Multi DataSource + Routing DataSource</description>
 
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        
        <!-- For Thymeleaf VIEW. Remove it if using Jsp VIEW -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        
        <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.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        
                
        <!-- SQL Server - Mssql-Jdbc driver -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>
        
        <!-- SQL Server - JTDS driver -->
        <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <scope>runtime</scope>
        </dependency>
        
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>
        
        
        <!-- For JSP VIEW (Need REMOVE spring-boot-starter-thymeleaf) -->
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
        </dependency>
        
        <!-- For JSP VIEW (Need REMOVE spring-boot-starter-thymeleaf) -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        
    </dependencies>

    <repositories>
    
        <!-- Repository for ORACLE JDBC Driver -->
        <repository>
            <id>codelds</id>
            <url>https://code.lds.org/nexus/content/groups/main-repo</url>
        </repository>
 
    </repositories>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
    
</project>

4. Configurer DataSource & RoutingDataSource

Les informations des bases de données sont configurées dans le fichier datasource-cfg.properties.

datasource-cfg.properties (MySQL + MySQL)

# DataSource (PUBLISHER System).

spring.datasource.driver-class-name.1=com.mysql.jdbc.Driver
spring.datasource.url.1=jdbc:mysql://localhost:3306/publisher
spring.datasource.username.1=root
spring.datasource.password.1=12345



# DataSource (ADVERTISER System).

spring.datasource.driver-class-name.2=com.mysql.jdbc.Driver
spring.datasource.url.2=jdbc:mysql://localhost:3306/advertiser
spring.datasource.username.2=root
spring.datasource.password.2=12345

datasource-cfg.properties (SQL Server + SQL Server) (JTDS Driver)

# DataSource  (PUBLISHER System).

spring.datasource.driver-class-name.1=net.sourceforge.jtds.jdbc.Driver
spring.datasource.url.1=jdbc:jtds:sqlserver://localhost:1433/publisher;instance=SQLEXPRESS
spring.datasource.username.1=sa
spring.datasource.password.1=12345


# DataSource  (ADVERTISER System).

spring.datasource.driver-class-name.2=net.sourceforge.jtds.jdbc.Driver
spring.datasource.url.2=jdbc:jtds:sqlserver://localhost:1433/advertiser;instance=SQLEXPRESS
spring.datasource.username.2=sa
spring.datasource.password.2=12345

datasource-cfg.properties (SQL Server + SQL Server) (Mssql-Jdbc Driver)

# DataSource  (PUBLISHER System).

spring.datasource.driver-class-name.1=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url.1=jdbc:sqlserver://tran-vmware-pc\\SQLEXPRESS:1433;databaseName=publisher
spring.datasource.username.1=sa
spring.datasource.password.1=12345


# DataSource  (ADVERTISER System).

spring.datasource.driver-class-name.2=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url.2=jdbc:sqlserver://tran-vmware-pc\\SQLEXPRESS:1433;databaseName=advertiser
spring.datasource.username.2=sa
spring.datasource.password.2=12345

datasource-cfg.properties (ORACLE + ORACLE)

# DataSource (PUBLISHER System).

spring.datasource.driver-class-name.1=oracle.jdbc.driver.OracleDriver
spring.datasource.url.1=jdbc:oracle:thin:@localhost:1521:db12c
spring.datasource.username.1=publisher
spring.datasource.password.1=12345


# DataSource (ADVERTISER System).

spring.datasource.driver-class-name.2=oracle.jdbc.driver.OracleDriver
spring.datasource.url.2=jdbc:oracle:thin:@localhost:1521:db12c
spring.datasource.username.2=advertiser
spring.datasource.password.2=12345

datasource-cfg.properties (PostGres + PostGres)

# DataSource (PUBLISHER System).

spring.datasource.driver-class-name.1=org.postgresql.Driver
spring.datasource.url.1=jdbc:postgresql://tran-vmware-pc:5432/publisher
spring.datasource.username.1=postgres
spring.datasource.password.1=12345



# DataSource (ADVERTISER System).

spring.datasource.driver-class-name.2=org.postgresql.Driver
spring.datasource.url.2=jdbc:postgresql://tran-vmware-pc:5432/advertiser
spring.datasource.username.2=postgres
spring.datasource.password.2=12345

Par défaut, Spring Boot va automatiquement configurer une DataSource, par conséquent, vous devez le désactiver pour qu'il ne puisse pas configurer votre DataSource. Ces configurations automatiques comprennent :

DataSourceAutoConfiguration
DataSourceTransactionManagerAutoConfiguration

SpringBootRoutingDsApplication.java

package org.o7planning.sbroutingds;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.o7planning.sbroutingds.routing.MyRoutingDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.annotation.PropertySources;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@SpringBootApplication

// Disable Auto Config DataSource & DataSourceTransactionManager
@EnableAutoConfiguration(exclude = { //
		DataSourceAutoConfiguration.class, //
		DataSourceTransactionManagerAutoConfiguration.class })

// Load to Environment
// (@see resources/datasource-cfg.properties).
@PropertySources({ @PropertySource("classpath:datasource-cfg.properties") })
public class SpringBootRoutingDsApplication {

	// Stores all the properties loaded by the @PropertySource
	@Autowired
	private Environment env;

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

	// Returns Routing DataSource (MyRoutingDataSource)
	@Autowired
	@Bean(name = "dataSource")
	public DataSource getDataSource(DataSource dataSource1, DataSource dataSource2) {

		System.out.println("## Create DataSource from dataSource1 & dataSource2");

		MyRoutingDataSource dataSource = new MyRoutingDataSource();

		dataSource.initDataSources(dataSource1, dataSource2);

		return dataSource;
	}

	@Bean(name = "dataSource1")
	public DataSource getDataSource1() throws SQLException {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();

		// See: datasouce-cfg.properties
		dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name.1"));
		dataSource.setUrl(env.getProperty("spring.datasource.url.1"));
		dataSource.setUsername(env.getProperty("spring.datasource.username.1"));
		dataSource.setPassword(env.getProperty("spring.datasource.password.1"));

		System.out.println("## DataSource1: " + dataSource);
		return dataSource;
	}

	@Bean(name = "dataSource2")
	public DataSource getDataSource2() throws SQLException {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();

		// See: datasouce-cfg.properties
		dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name.2"));
		dataSource.setUrl(env.getProperty("spring.datasource.url.2"));
		dataSource.setUsername(env.getProperty("spring.datasource.username.2"));
		dataSource.setPassword(env.getProperty("spring.datasource.password.2"));

		System.out.println("## DataSource2: " + dataSource);

		return dataSource;
	}

	@Autowired
	@Bean(name = "transactionManager")
	public DataSourceTransactionManager getTransactionManager(DataSource dataSource) {
		DataSourceTransactionManager txManager = new DataSourceTransactionManager();

		txManager.setDataSource(dataSource);

		return txManager;
	}

}

MyRoutingDataSource est une DataSource, elle contient une carte (Map) entre des clés et des réelles Datasource.

MyRoutingDataSource.java

package org.o7planning.sbroutingds.routing;

import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

// This is a DataSource.
public class MyRoutingDataSource extends AbstractRoutingDataSource {

   @Override
   protected Object determineCurrentLookupKey() {

      HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes())
            .getRequest();

      // See more: DataSourceInterceptor
      String keyDS = (String) request.getAttribute("keyDS");

      System.out.println("KeyDS=" + keyDS);

      if (keyDS == null) {
         keyDS = "PUBLISHER_DS";
      }

      return keyDS;
   }

   public void initDataSources(DataSource dataSource1, DataSource dataSource2) {
      Map<Object, Object> dsMap = new HashMap<Object, Object>();
      dsMap.put("PUBLISHER_DS", dataSource1);
      dsMap.put("ADVERTISER_DS", dataSource2);

      this.setTargetDataSources(dsMap);
   }

}

5. DataSourceInterceptor & Configuration

DataSourceInterceptor va analyser URL de requête, et décider quelle Datasource sera utilisée.

DataSourceIntercetor.java

package org.o7planning.sbroutingds.interceptor;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.handler.HandlerInterceptorAdapter;

public class DataSourceIntercetor extends HandlerInterceptorAdapter {

    // Request:

    // /publisher/list
    // /advertiser/list
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {

        String contextPath = request.getServletContext().getContextPath();

        // /SomeContextPath/publisher
        String prefixPublisher = contextPath + "/publisher";
        
        // /SomeContextPath/advertiser
        String prefixAdvertiser = contextPath + "/advertiser";

        // /SomeContextPath/publisher/dashboard
        // /SomeContextPath/advertiser/dashboard
        
        String uri = request.getRequestURI();
        System.out.println("URI:"+ uri);
        
        if(uri.startsWith(prefixPublisher)) {
            request.setAttribute("keyDS", "PUBLISHER_DS");
        }
        
        else if(uri.startsWith(prefixAdvertiser)) {
            request.setAttribute("keyDS", "ADVERTISER_DS");
        }

        return true;
    }

}

Dans WebMvcConfig, vous devez enregistrer DataSourceInterceptor.

WebMvcConfig.java

package org.o7planning.sbroutingds.config;

import org.o7planning.sbroutingds.interceptor.DataSourceIntercetor;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {

    @Override
    public void addInterceptors(InterceptorRegistry registry) {

        registry.addInterceptor(new DataSourceIntercetor())//
                .addPathPatterns("/publisher/*", "/advertiser/*");
    }

}

6. DAO, Controller

DataDAO est une classe utilitaire pour enquêter des données à partir de la base de données.

DataDAO.java

package org.o7planning.sbroutingds.dao;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class DataDAO extends JdbcDaoSupport {

    @Autowired
    public DataDAO(DataSource dataSource) {
        this.setDataSource(dataSource);
    }

    public List<String> queryPublishers() {
        String sql = "Select name from Publishers";

        List<String> list = this.getJdbcTemplate().queryForList(sql, String.class);
        return list;
    }

    public List<String> queryAdvertisers() {
        String sql = "Select name from Advertisers";

        List<String> list = this.getJdbcTemplate().queryForList(sql, String.class);
        return list;
    }
    
}

MainController.java

package org.o7planning.sbroutingds.controller;

import java.sql.SQLException;
import java.util.List;

import org.o7planning.sbroutingds.dao.DataDAO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
public class MainController {

    @Autowired
    private DataDAO dataDAO;
    
    
    @RequestMapping(value = { "/" }, method = RequestMethod.GET)
    public String home(Model model) throws SQLException {
 
        return "home";
    }

    @RequestMapping(value = { "/advertiser/list" }, method = RequestMethod.GET)
    public String advertiser(Model model) throws SQLException {

        List<String> list = dataDAO.queryAdvertisers();
        model.addAttribute("advertisers", list);

        return "advertiser";
    }

    @RequestMapping(value = { "/publisher/list" }, method = RequestMethod.GET)
    public String publisher(Model model) throws SQLException {

        List<String> list = dataDAO.queryPublishers();
        model.addAttribute("publishers", list);

        return "publisher";
    }

}

7. Thymeleaf Template

home.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
   <head>
      <meta charset="UTF-8"/>
      <title>Home</title>
   </head>
   <body>
   
      <h2>Multi DataSource with RoutingDataSource</h2>
      
      <h3>/publisher/* ==> Using Publisher-DB</h3>
      <ul>
         <li><a th:href="@{/publisher/list}">Show Publisher List</a></li>
      </ul>
      
      <h3>/advertiser/* ==> Using Advertiser-DB</h3>
      <ul>
         <li> <a th:href="@{/advertiser/list}">Show Advertiser Lists</a>   </li>
      </ul>
      
   </body>
   
</html>

publisher.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
   <head>
      <meta charset="UTF-8"/>
      <title>Publisher System</title>
   </head>
  
   <body>
      <a th:href="@{/}">Home</a>
      
      <h2>Using Publisher-DB</h2>
      
      <ul>
         <li th:each="publisher : ${publishers}" th:utext="${publisher}"></li>
      </ul>
   </body>
  
</html>

advertiser.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
   <head>
      <meta charset="UTF-8"/>
      <title>Advertiser System</title>
   </head>
   <body>
      <a th:href="@{/}">Home</a>
      
      <h2>Using Advertiser-DB</h2>
      
      <ul>
         <li th:each="advertiser : ${advertisers}" th:utext="${advertiser}"></li>
      </ul>
      
   </body>
   
</html>