Use Multiple DataSources in Spring MVC

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, "/*");
    }

}

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/*");
    }

}

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

}

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

}

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

}

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

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

}