Multiple Datasources in Spring Application

Multiple data source implementations are very crucial in instances where we want to secure the application from vulnerabilities such as a database failure. This will ensure that the application can still function even if part of the database went down. This tutorial will use student and course entities to demonstrate how multiple data sources can be configured to serve a single application. We will make use of Hibernate but you can switch to standard JPA if that is what you prefer.

Prerequisites

JDK 11+
Maven
Intellij (optional)

Execute SQL scripts
Create student and course configuration properties
Add Maven dependencies
Create student Data Source
Create student SessionFactory
Create student TransactionManager
Repeat step 2,3 $ 4 for the course
Create student and course Entity
Create student and course Repository
Create student and course Controller
Create DispatcherServletInitializer
Testing the application

Execute SQL scripts

The following script will create a student database and sample student objects.

mysql> source E:\scripts\SpringMultipleDataSources\database-scripts\student.sql

student.sql

CREATE DATABASE IF NOT EXISTS `student_database`;


USE `student_database`;


DROP TABLE IF EXISTS `student`;

CREATE TABLE `student`(
`id` int NOT NULL AUTO_INCREMENT,
`first_name` varchar (50),
`last_name` varchar (50),
`email` varchar (50),
PRIMARY KEY (`id`)
);

LOCK TABLES `student` WRITE;

INSERT INTO `student` VALUES
(1,"john","doe","john@javadev.com"),
(2,"peter","anderson","peter@javadev.com"),
(3,"lucy","parker","lucy@javadev.com"),
(4,"mercy","williams","mercy@javadev.com");

UNLOCK TABLES;

The following script will create a course database and sample course objects.

mysql> source E:\scripts\SpringMultipleDataSources\database-scripts\course.sql

course.sql

CREATE DATABASE IF NOT EXISTS `course_database`;


USE `course_database`;


DROP TABLE IF EXISTS `course`;

CREATE TABLE `course`(
`id` int NOT NULL AUTO_INCREMENT,
`course_name` varchar (50),
PRIMARY KEY (`id`)
);

LOCK TABLES `course` WRITE;

INSERT INTO `course` VALUES
(1,"Introduction to Java"),
(2,"Multithreading in Action"),
(3,"Concurrency control mechanisms"),
(4,"Data structures and algorithms");

UNLOCK TABLES;

Create student and course configuration properties

Create two files with the names student-datasource.properties and course-datasource.properties respectively under the properties folder.
These files will contain the database, hibernate, and connection pool properties as shown below to be used by the application.

Student details

#
# JDBC connection properties
#
student.jdbc.driver=com.mysql.cj.jdbc.Driver
student.jdbc.url=jdbc:mysql://localhost:3306/student_database?useSSL=false&serverTimezone=UTC
student.jdbc.user=username
student.jdbc.password=password

#
# Connection pool properties
#
student.connection.pool.initialPoolSize=5
student.connection.pool.minPoolSize=5
student.connection.pool.maxPoolSize=20
student.connection.pool.maxIdleTime=3000

#
# Hibernate properties
#
student.hibernate.dialect=org.hibernate.dialect.MySQLDialect
student.hibernate.show_sql=true
student.hibernate.packagesToScan=com.javadev.datasources.entity

Course details

#
# JDBC connection properties
#
course.jdbc.driver=com.mysql.cj.jdbc.Driver
course.jdbc.url=jdbc:mysql://localhost:3306/course_database?useSSL=false&serverTimezone=UTC
course.jdbc.user=username
course.jdbc.password=password

#
# Connection pool properties
#
course.connection.pool.initialPoolSize=5
course.connection.pool.minPoolSize=5
course.connection.pool.maxPoolSize=20
course.connection.pool.maxIdleTime=3000

#
# Hibernate properties
#
course.hibernate.dialect=org.hibernate.dialect.MySQLDialect
course.hibernate.show_sql=true
course.hibernate.packagesToScan=com.javadev.datasources.entity

Add maven dependencies

<properties>
        <springframework.version>5.3.4</springframework.version>
        <hibernate.version>5.4.5.Final</hibernate.version>
        <mysql.connector.version>8.0.17</mysql.connector.version>
        <c3po.version>0.9.5.4</c3po.version>

        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>
    <dependencies>

        <!-- Spring MVC support -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${springframework.version}</version>
        </dependency>

        <!-- Spring Transactions -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${springframework.version}</version>
        </dependency>

        <!-- Spring ORM -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${springframework.version}</version>
        </dependency>

        <!-- Hibernate Core -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>

        <!-- Add MySQL and C3P0 support -->

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.connector.version}</version>
        </dependency>

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>${c3po.version}</version>
        </dependency>


        <!-- Servlet-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.9.8</version>
        </dependency>

    </dependencies>

Create student data source

@Configuration
@EnableWebMvc
@EnableTransactionManagement
@ComponentScan(basePackages = "com.javadev.datasources")
@PropertySource({"classpath:student-datasource.properties","classpath:course-datasource.properties"})
public class DataSourceAppConfig {

    private Environment environment;

    @Autowired
    public DataSourceAppConfig(Environment environment){
        this.environment = environment;
    }

    @Bean
    public DataSource studentDataSource(){
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

        try {
            comboPooledDataSource.setDriverClass(environment.getProperty("student.jdbc.driver"));
        }catch (PropertyVetoException e){
            throw new RuntimeException(e);
        }

        comboPooledDataSource.setJdbcUrl(environment.getProperty("student.jdbc.url"));
        comboPooledDataSource.setUser(environment.getProperty("student.jdbc.user"));
        comboPooledDataSource.setPassword(environment.getProperty("student.jdbc.password"));

        comboPooledDataSource.setInitialPoolSize(Integer.parseInt(Objects.requireNonNull(environment.getProperty("student.connection.pool.initialPoolSize"))));
        comboPooledDataSource.setMinPoolSize(Integer.parseInt(Objects.requireNonNull(environment.getProperty("student.connection.pool.minPoolSize"))));
        comboPooledDataSource.setMaxPoolSize(Integer.parseInt(Objects.requireNonNull(environment.getProperty("student.connection.pool.maxPoolSize"))));
        comboPooledDataSource.setMaxIdleTime(Integer.parseInt(Objects.requireNonNull(environment.getProperty("student.connection.pool.maxIdleTime"))));

        return comboPooledDataSource;
    }
}

@Configuration -Indicates that a class declares one or more @Bean methods and may be processed by the Spring container to generate bean definitions and service requests for those beans at runtime.
@EnableWebMvc – Adding this annotation to an @Configuration class imports the Spring MVC configuration from WebMvcConfigurationSupport.
@EnableTransactionManagement– Enables Spring’s annotation-driven transaction management capability.
@ComponentScan – Configures component scanning directives for use with @Configuration classes.
@PropertySource – Annotation providing a convenient and declarative mechanism for adding a PropertySource to Spring’s Environment.
Environment – Interface representing the environment in which the current application is running.
DataSource – Provides a standard method of working with database connections.
ComboPooledDataSource – An implementation of DataSource which uses JDBC to get a connection and releases it at the end.

Create a student SessionFactory

The SessionFactory should be added to DataSourceAppConfig class.

@Bean
   public LocalSessionFactoryBean studentSessionFactory(){
       LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();

       sessionFactoryBean.setDataSource(studentDataSource());
       sessionFactoryBean.setPackagesToScan(environment.getProperty("student.hibernate.packagesToScan"));
       Properties properties = new Properties();
       properties.setProperty("student.hibernate.dialect",environment.getProperty("student.hibernate.dialect"));
       properties.setProperty("student.hibernate.show_sql",environment.getProperty("student.hibernate.show_sql"));
       sessionFactoryBean.setHibernateProperties(properties);

       return sessionFactoryBean;
   }

LocalSessionFactoryBean – This is the usual way to set up a shared Hibernate SessionFactory in a Spring application context
Properties – The Properties the class represents a persistent set of properties.
SessionFaactory consumes a data source thus we passed studentDataSource()to it.

Create student TransactionManager

@Bean
   @Autowired
   public HibernateTransactionManager studentTransactionManager(
           @Qualifier("studentSessionFactory")SessionFactory sessionFactory
           ){
       HibernateTransactionManager hibernateTransactionManager = new HibernateTransactionManager();
       hibernateTransactionManager.setSessionFactory(sessionFactory);

       return hibernateTransactionManager;
   }

HibernateTransactionManager – Manages transactions between the different entities to ensure data integrity.
@Qualifier – Transaction Manager consumes a SessionFactory and since we have a student and course SessionFactory we have to specify using @Qualifier.

Repeat step 2,3 & 4 for course

Create a course DataSource
Create a course SessionFactory
Create a course TransactionManager

Create a student and course entity

student entity

@Entity
@Table(name = "student")
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "email")
    private String email;

    //Getters, Setters and toString

}

course entity

@Entity
@Table(name = "course")
public class Course {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "course_name")
    private String courseName;

    //Getters, Setters, and toString 
}

Create student and Course repository

Student repository

@Repository
public class StudentRepository {


    private final SessionFactory sessionFactory;

    @Autowired
    public StudentRepository(@Qualifier("studentSessionFactory") SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }


    @Transactional("studentTransactionManager")
    public List<Student> findAllStudents(){
        Session studentsSession = sessionFactory.getCurrentSession();

        Query<Student> studentsQuery = studentsSession.createQuery("from Student",Student.class);

        List<Student> students = studentsQuery.getResultList();

        return students;
    }
}

@Repository – a mechanism for encapsulating storage, retrieval, and search behavior that emulates a collection of objects.
StudentReposiry– has a method that returns a list of students from the database.
@Transactional – Since we have student and course transaction managers we have to specify students transaction using @Transactional

Course repository

@Repository
public class CourseRepository {

    private final SessionFactory sessionFactory;

    @Autowired
    public CourseRepository(@Qualifier("courseSessionFactory") SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    @Transactional("courseTransactionManager")
    public List<Course> findAllCourses(){
        Session courseSession = sessionFactory.getCurrentSession();

        Query<Course> courseQuery = courseSession.createQuery("from Course",Course.class);

        List<Course> courses = courseQuery.getResultList();

        return courses;
    }
}

CourseRepository – has a method that returns a list of courses from the database.

Create student and course controllers:

Student controller

@RestController
@RequestMapping("/student")
public class StudentController {

    private StudentRepository studentRepository;

    @Autowired
    public StudentController(StudentRepository studentRepository){
        this.studentRepository = studentRepository;
    }

    @GetMapping("/all")
    public List<Student> findAllStudents(){
        return studentRepository.findAllStudents();
    }
}

@RestController – A controller that handles REST requests.
@RequestMapping – Annotation for mapping web requests onto methods in request-handling classes with flexible method signatures.
/student/all request on findAllStudents method will return a list of students.

Course controller

@RestController
@RequestMapping("/course")
public class CourseController {

    private CourseRepository courseRepository;

    @Autowired
    public CourseController(CourseRepository courseRepository){
        this.courseRepository = courseRepository;
    }

    @GetMapping("/all")
    public List<Course> findAllCourses(){
        return courseRepository.findAllCourses();
    }
}

/course/all request on findAllCoursesthe method will return a list of courses from the database.

Create DispatcherServletInitializer

This will enable to set up of the default mapping for the application and also enable deployment to an external Tomcat server.

public class MultipleDataSourcesAppDispatcherServletInitializer extends AbstractAnnotationConfigDispatcherServletInitializer{

    @Override
    protected Class<?>[] getRootConfigClasses() {
        return null;
    }

    @Override
    protected Class<?>[] getServletConfigClasses() {
        return new Class[]{DataSourceAppConfig.class};
    }

    @Override
    protected String[] getServletMappings() {
        return new String[]{"/"};
    }
}

Testing the application

To test the student data source, issue the following GET request on the postman.

`http://localhost:8080/SpringMultipleDataSources_war/student/all`

To test the course data source, issue the following GET request on the postman.

`http://localhost:8080/SpringMultipleDataSources_war/course/all`

Conclusion

In this tutorial, you have learned how to configure a Spring application to use multiple multiple Data Sources. The same configurations can be applied to Spring Boot with a few changes. Spring Boot will auto-configure a data source and each data source must be accompanied by a session factory and a transaction manager.

Happy coding!