Utiliser plusieurs DataSources dans Spring MVC

L'objectif de ce document
Préparation de la base de données
Créer le projet de l'application web Maven
Configurer web.xml & pom.xml
Configurer Spring MVC
Model, Interceptor, Routing, Controller ...
Views
Configurer et exécuter l'applicatuon web

See more:
Utiliser plusieurs DataSources avec Spring Boot et RoutingDataSource
Utiliser plusieurs DataSources avec Spring Boot et JPA

1. L'objectif de ce document

Parfois lorsque vous avez besoin de créer une application web afin de vous connecter aux plusieurs bases de données. Vous pouvez donc facilement le faire dan Spring MVC.
Spring vous fournit la classe AbstractRoutingDataSource, vous pouvez écrire une classe étendue de cette classe. Routing Datasource contient une carte (Map) des vraies Datasource. Et elle déterminera quelle DataSource sera utilisée respectivement pour chaque demande de l'utilisateur.

Routing Datasource est également une Datasource, mais elle est une Datasource particulière.

Dans ce document, je stimule un site web qui utilise deux datasource. Chaque datasource s'est connectée à une autre base de données. Chaque base de données stocke des informations pour le système Publisher (Éditeur) et une base de données stocke des informations du système Advertiser (Annonceur).

Dans certaines situations, ous pouvez également construire un nouveau site web de multi-langage, vous pouvez créer plusieur bases de données, chaque base de données stocke le contenu des postes dans une langue précise.

2. Préparation de la base de données

Je créerai deux bases de données, chaque base de données est pour le système PUBLISHER (Éditeur) et une base de données est pour le système ADVERTISER (Annonceur). Toutes les deux bases de données ne sont pas les même. Vous pouvez utiliser Oracle, MySQL ou SQL Server, ...

Database1:

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

Database1 - (ORACLE)

-- Create table
create table PUBLISHERS
(
NAME VARCHAR2(50)
);

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

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

Commit;

Database1 - (MySQL or SQL Server)

-- Create table
create table PUBLISHERS
(
  NAME VARCHAR(50)
);

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

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

Database 2:

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

Database2 - (ORACLE)

-- Create table
create table ADVERTISERS
(
  NAME VARCHAR2(50)
);

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

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

Commit;

Database2 (MySQL or SQL Server)

-- Create table
create table ADVERTISERS
(
  NAME VARCHAR(50)
);

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

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

3. Créer le projet de l'application web Maven

Sur Eclipse, créez un Maven Web App Project vide, nommé SpringMVCMultiDS.

Créer un projet Maven Web App vide dans Eclipse

4. Configurer web.xml & pom.xml

Utilisez WebApp >= 3.

web.xml

<web-app xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
          http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    version="3.0">
 
    <display-name>SpringMVCMultiDS Web Application</display-name>
 
</web-app>

Dans pom.xml j'ai déclaré des bibliothèques JDBC pour toutes les 3 bases de données Oracle, MySQL et SQL Server.

pom.xml

<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/maven-v4_0_0.xsd">
                     
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.o7planning</groupId>
  <artifactId>SpringMVCMultiDS</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>SpringMVCMultiDS Maven Webapp</name>
  <url>http://maven.apache.org</url>


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

    <dependencies>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>

        <!-- Servlet API -->
        <!-- http://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>

        <!-- Jstl for jsp page -->
        <!-- http://mvnrepository.com/artifact/javax.servlet/jstl -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>


        <!-- JSP API -->
        <!-- http://mvnrepository.com/artifact/javax.servlet.jsp/jsp-api -->
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.2</version>
            <scope>provided</scope>
        </dependency>

        <!-- Spring dependencies -->
        <!-- http://mvnrepository.com/artifact/org.springframework/spring-core -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>

        <!-- http://mvnrepository.com/artifact/org.springframework/spring-web -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>

        <!-- http://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>


        <!-- http://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>

        <!-- http://mvnrepository.com/artifact/org.springframework/spring-tx -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>4.3.3.RELEASE</version>
        </dependency>
 

        <!-- Commons DataSources -->
        <!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>


        <!-- MySQL JDBC driver -->
        <!-- http://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.4</version>
        </dependency>

        <!-- Oracle JDBC driver -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.3</version>
        </dependency>

        <!-- SQLServer JDBC driver (JTDS) -->
        <!-- http://mvnrepository.com/artifact/net.sourceforge.jtds/jtds -->
        <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <version>1.3.1</version>
        </dependency>


    </dependencies>


    <build>
        <finalName>SpringMVCMultiDS</finalName>
        <plugins>
       
            <!-- Config: Maven Tomcat Plugin -->
            <!-- http://mvnrepository.com/artifact/org.apache.tomcat.maven/tomcat7-maven-plugin -->
            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <artifactId>tomcat7-maven-plugin</artifactId>
                <version>2.2</version>
                <!-- Config: contextPath and Port (Default - /SpringMVCMultiDS,8080) -->
                <!--
                <configuration>
                    <path>/</path>
                    <port>8899</port>
                </configuration>
                -->   
            </plugin>
        </plugins>
    </build>   

</project>

5. Configurer Spring MVC

SpringWebAppInitializer.java

package org.o7planning.springmvcmultids.config;

import javax.servlet.FilterRegistration;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRegistration;

import org.springframework.web.WebApplicationInitializer;
import org.springframework.web.context.ContextLoaderListener;
import org.springframework.web.context.support.AnnotationConfigWebApplicationContext;
import org.springframework.web.filter.CharacterEncodingFilter;
import org.springframework.web.servlet.DispatcherServlet;

public class SpringWebAppInitializer implements WebApplicationInitializer {

    @Override
    public void onStartup(ServletContext servletContext) throws ServletException {
        AnnotationConfigWebApplicationContext appContext = new AnnotationConfigWebApplicationContext();
        appContext.register(ApplicationContextConfig.class);

        ServletRegistration.Dynamic dispatcher = servletContext.addServlet("SpringDispatcher",
                new DispatcherServlet(appContext));
        dispatcher.setLoadOnStartup(1);
        dispatcher.addMapping("/");       
        // 
        dispatcher.setInitParameter("contextClass", appContext.getClass().getName());

        servletContext.addListener(new ContextLoaderListener(appContext));

        // UTF8 Charactor Filter.
        FilterRegistration.Dynamic fr = servletContext.addFilter("encodingFilter", CharacterEncodingFilter.class);

        fr.setInitParameter("encoding", "UTF-8");
        fr.setInitParameter("forceEncoding", "true");
        fr.addMappingForUrlPatterns(null, true, "/*");
    }

}

Dans WebMvcConfig vous devez vous inscrire DataSourceInterceptor, cet intercepteur analyse le URL de demande afin de déterminer quelle Datasource sera utilisée.

WebMvcConfig.java

package org.o7planning.springmvcmultids.config;

import org.o7planning.springmvcmultids.interceptor.DataSourceIntercetor;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

@Configuration
@EnableWebMvc
public class WebMvcConfig extends WebMvcConfigurerAdapter {

    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {

        // Default..
    }

    @Override
    public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
        configurer.enable();
    }

    //
    @Override
    public void addInterceptors(InterceptorRegistry registry) {

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

}

ApplicationContextConfig.java

package org.o7planning.springmvcmultids.config;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.o7planning.springmvcmultids.routing.MyRoutingDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
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.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.servlet.view.InternalResourceViewResolver;

@Configuration
@ComponentScan("org.o7planning.springmvcmultids.*")

@EnableTransactionManagement

// Load to Environment.
@PropertySources({ @PropertySource("classpath:ds/datasource-cfg.properties") })

public class ApplicationContextConfig {

    // The Environment class serves as the property holder
    // and stores all the properties loaded by the @PropertySource
    @Autowired
    private Environment env;

    @Bean(name = "viewResolver")
    public InternalResourceViewResolver getViewResolver() {
        InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();

        viewResolver.setPrefix("/WEB-INF/pages/");
        viewResolver.setSuffix(".jsp");

        return viewResolver;
    }

    // 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();

        Map<Object, Object> dsMap = new HashMap<Object, Object>();
        dsMap.put("PUBLISHER_DS", dataSource1);
        dsMap.put("ADVERTISER_DS", dataSource2);

        dataSource.setTargetDataSources(dsMap);

        return dataSource;
    }

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

        // See: datasouce-cfg.properties
        dataSource.setDriverClassName(env.getProperty("ds.database-driver1"));
        dataSource.setUrl(env.getProperty("ds.url1"));
        dataSource.setUsername(env.getProperty("ds.username1"));
        dataSource.setPassword(env.getProperty("ds.password1"));

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

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

        // See: datasouce-cfg.properties
        dataSource.setDriverClassName(env.getProperty("ds.database-driver2"));
        dataSource.setUrl(env.getProperty("ds.url2"));
        dataSource.setUsername(env.getProperty("ds.username2"));
        dataSource.setPassword(env.getProperty("ds.password2"));

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

        return dataSource;
    }

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

        txManager.setDataSource(dataSource);

        return txManager;
    }

}

Les informations de la base de données sont configurées au fichier ds/datasource-cfg.xml, et sont lues par la classe ApplicationContextConfig.

ds/datasource-cfg.properties (ORACLE + ORACLE)

# DataSource (PUBLISHER System).

ds.database-driver1=oracle.jdbc.driver.OracleDriver
ds.url1=jdbc:oracle:thin:@localhost:1521:db12c
ds.username1=publisher
ds.password1=12345


# DataSource (ADVERTISER System).

ds.database-driver2=oracle.jdbc.driver.OracleDriver
ds.url2=jdbc:oracle:thin:@localhost:1521:db12c
ds.username2=advertiser
ds.password2=12345

ds/datasource-cfg.properties (MySQL + MySQL)

# DataSource (PUBLISHER System).

ds.database-driver1=com.mysql.jdbc.Driver
ds.url1=jdbc:mysql://localhost:3306/publisher
ds.username1=root
ds.password1=12345



# DataSource (ADVERTISER System).

ds.database-driver2=com.mysql.jdbc.Driver
ds.url2=jdbc:mysql://localhost:3306/advertiser
ds.username2=root
ds.password2=12345

ds/datasource-cfg.properties (SQL Server + SQL Server)

# DataSource  (PUBLISHER System).

ds.database-driver1=net.sourceforge.jtds.jdbc.Driver
ds.url1=jdbc:jtds:sqlserver://localhost:1433/simplehr;instance=SQLEXPRESS
ds.username1=publisher
ds.password1=12345


# DataSource  (ADVERTISER System).

ds.database-driver2=net.sourceforge.jtds.jdbc.Driver
ds.url2=jdbc:jtds:sqlserver://localhost:1433/simplehr;instance=SQLEXPRESS
ds.username2=advertiser
ds.password2=12345

6. Model, Interceptor, Routing, Controller ...

DataSourceIntercetor est un intercepteur, il est basé sur le URL de la demande (request) pour déterminer quelle Datasource sera utilisée.

DataSourceIntercetor.java

package org.o7planning.springmvcmultids.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();

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

        // /SpringMVCMultiDS/publisher/dashboard
        // /SpringMVCMultiDS/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;
    }

}

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

MyRoutingDataSource.java

package org.o7planning.springmvcmultids.routing;

import javax.servlet.http.HttpServletRequest;

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;
    }

}

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

DataDAO.java

package org.o7planning.springmvcmultids.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;

@Repository
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.springmvcmultids.controller;

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

import org.o7planning.springmvcmultids.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 = { "/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. Views

/WEB-INF/pages/advertiser.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Advertiser</title>
</head>
<body>
<div style="border:1px solid #ccc;text-align:right;padding:5px;">
   <a href="${pageContext.request.contextPath}/publisher/list">Publisher List</a>
   &nbsp;&nbsp;
   <a href="${pageContext.request.contextPath}/advertiser/list">Advertiser List</a>
</div>

<h1>Advertiser Page</h1>

<c:forEach var="item" items="${advertisers}" varStatus="status">

  ${item}  <br/>
 
</c:forEach>


</body>
</html>

/WEB-INF/pages/publisher.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Advertiser</title>
</head>
<body>
<div style="border:1px solid #ccc;text-align:right;padding:5px;">
   <a href="${pageContext.request.contextPath}/publisher/list">Publisher List</a>
   &nbsp;&nbsp;
   <a href="${pageContext.request.contextPath}/advertiser/list">Advertiser List</a>
</div>

<h1>Publisher Page</h1>

<c:forEach var="item" items="${publishers}" varStatus="status">

  ${item}  <br/>
 
</c:forEach>

</body>
</html>