Use Multiple DataSources in Spring MVC

The objective of the document
Preparing the Database
Create Maven Web App Project
Configure web.xml & pom.xml
Configure Spring MVC
Model, Interceptor, Routing, Controller ...
Views
Configure and run webapp

See more:
Use Multiple DataSources with Spring Boot and RoutingDataSource
Use Multiple DataSources with Spring Boot and JPA

1. The objective of the document

Sometimes you need to create a Web application connecting to multiple databases. And you can do it easily in Spring MVC.
Spring provides you with class AbstractRoutingDataSource, you can write an extended class from the class. Routing Datasource contains a Map of real Datasources. And it will determine what DataSource is used for each request from the user respectively.

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

In the post, I simulate a Website with 2 datasources. Each datasource is connected to a different database. A database stores information for Publisher system and the other stores information for Advertiser system.

In some situations, you also can build a multi-language news website, you can create more Databases, each database stores content of the posts in a specific language.

2. Preparing the Database

I will create 2 databases, one is for PUBLISHER system and the other is for ADVERTISER system. Both may not be the same type. You can use Oracle, MySQL or SQL Server,...

Database1:

PUBLISHER database system has a table of 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:

ADVERTISER database system has a table of 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. Create Maven Web App Project

In Eclipse create an empty Maven Web App Project named SpringMVCMultiDS.

Create an Empty Maven Web App project in Eclipse

4. Configure web.xml & pom.xml

Using 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>

In pom.xml, I have declared JDBC libraries for all 3 types of databases including Oracle, MySQL and 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. Configure 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, "/*");
    }

}

In WebMvcConfig you need to register DataSourceInterceptor, the interceptor analyzes the URL of request to determine what Datasource is used.

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

}

Information of Databases is figured at file ds/datasource-cfg.xml and is read by class 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 is an interceptor based on the URL of request to determine what Datasource will be used.

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 is a DataSource containing a Map between keys and real Datasources.

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 is an utility class to query data from the database.

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>