Today we will go through a tutorial on how to deal with a situation where we need to get data from multiple datasources.

The ACHO framework provides a unified way to write applications that run across multiple platforms and databases. ACHO allows developers to write business logic and integrate with databases as if they were a single database. The ACHO framework has a very modular approach with an object oriented API. Entities are created in the framework by injecting a Java Bean with ACHO framework’s APIs.

One of the cool things about Spring framework is that you can define multiple datasources for your application. There are a lot of articles in the internet about this, but a lot of them are a little outdated. So, I decided to write this article so that I can keep myself updated with all the changes, and I have included a lot of pictures, to make it easy to understand.

Implementing multiple data sources is very important to protect an application from vulnerabilities such as database failure. This allows the application to continue working even if part of the database fails. This guide uses the student and course entities to demonstrate how to configure multiple data sources to serve a single application. We will use Hibernate, but you can switch to standard JPA if you prefer.

Condition

  • JDK 11+.
  • Maven
  • Intellij (optional)

Table of contents

  1. Executing SQL scripts
  2. Creation of learner and course configuration properties
  3. Adding Maven Dependencies
  4. Create a data source for students
  5. Create an apprentice session factory
  6. Create a student TransactionManager
  7. Repeat steps 2, 3 and 4 for the course.
  8. Creating a student and course entity
  9. Setting up a student and course administration
  10. Creation of a student and course controller
  11. Create DispatcherServletInitializer
  12. Testing of applications

Execute SQL scripts

The following script creates a database of students and sample student objects. mysql> Source E:scriptsSpringMultipleDataSources-database-scriptsstudent.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 `Pupil` WRITE ; INSERT INTO `student` VALUES (1,john,doe,[email protected]), (2,peter,anderson,[email protected]), (3,lucy,parker,[email protected]), (4,mercy,williams,[email protected]) ; UNLOCKING TABLES ; The following script creates the course database and sample course objects. mysql> source E:scriptsSpringMultipleDataSources-database-scriptscourse.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,Control mechanisms for parallelism), (4,Data structures and algorithms) ; UNLOCKING TABLES ;

Create properties for student and course configuration

  • Create two files named student-datasource.properties and course-datasource.properties in the properties folder.
  • These files contain the database, hibernate, and connection pool properties, shown below, that are used by the application.

Information for students

# # 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 # # properties connection pool # 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 # # properties connection pool # 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

Adding Maven Dependencies

5.3.4 5.4.5.Final 8.0.17 0.9.5.4 11 11 org.springframework spring-webmvc ${springframework.version} org.springframework spring-tx ${springframework.version} org.springframework spring-form ${springframework.version} org.hibernate hibernate-core ${hibernate.version} mysql mysql-connector-java ${mysql.connector.version} com.mchange c3p0 ${c3po.version} javax.servlet javax.servlet-api 3.1.0 com.fasterxml.jackson.core jackson-databind 2.9.8

Create a data source for students

@Configuration @EnableWebMvc @EnableTransactionManagement @ComponentScan(basePackages = com.javadev.datasources) @PropertySource({classpath:student-datasource.properties,classpath:student-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 the class declares one or more @Bean methods and can be manipulated by the Spring container to create job definitions and runtime service requests for those beans.
  • @EnableWebMvc – Adding this annotation to the @Configuration class imports the Spring MVC configuration of WebMvcConfigurationSupport.
  • @EnableTransactionManagement- Enable Spring’s transaction management based on annotations.
  • @ComponentScan – Sets the component scan directives to be used with @Configuration classes.
  • @PropertySource is an annotation that provides a convenient and declarative mechanism for adding a PropertySource to the Spring environment.
  • Environment – An interface that displays the environment in which the current application is running.
  • DataSource – Provides a standard method for working with database connections.
  • ComboPooledDataSource is an implementation of DataSource that uses JDBC to receive the connection and release it at the end.

Making a student essay factory

  • SessionFactory must be added to the 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 a common way to configure a generic Hibernate SessionFactory in the context of a Spring application.
  • Properties – the Properties class represents a constant set of properties.
  • SessionFaactory consumes a data source, so we gave it the studentDataSource()* function.

Creating a student TransactionManager

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

  • HibernateTransactionManager – manages transactions between different entities to ensure data integrity.
  • @Qualifier – The transaction manager consumes the SessionFactory, and since we have the SessionFactory student and race, we need to specify it with @Qualifier.

Repeat steps 2, 3 and 4 for course

  • Creating a quote data source
  • Create a courseware factory
  • Create a TransactionManager course

Creating student and course unit

@Entity @Table(name = student) public class Student { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = id) private int id ; @Column(name = first name) private String first name ; @Column(name = lastName) private String lastName ; @Column(name = email) private String email ; //Getter, Setter and toString } @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 ; //Getter, Setter and toString }

Create a repository for students and courses

Student safety

@Repository public class StudentRepository { Private final SessionFactory ; @Autowired public StudentRepository(@Qualifier(studentSessionFactory) SessionFactory) { this.sessionFactory = sessionFactory; } @Transactional(studentTransactionManager) public List findAllStudents(){ Session studentsSession = sessionFactory.getCurrentSession() ; Query studentQuery = studentsSession.createQuery(from Student,Student.class) ; StudentList = studentQuery.getResultList() ; back students; } }

  • The @Repository is a storage, encapsulation, retrieval and query mechanism that emulates a collection of objects.
  • StudentReposition – has a method that returns a list of students from the database.
  • @Transactional – Since we have transactional handlers for students and courses, we need to specify a student transaction with @Transactional.

Memory course

@Repository public class CourseRepository {. Private final SessionFactory ; @Autowired public CourseRepository(@Qualifier(courseSessionFactory) SessionFactory) { this.sessionFactory = sessionFactory; } @Transactional(courseTransactionManager) public List findAllCourses(){ Session courseSession = sessionFactory.getCurrentSession() ; Query courseQuery = courseSession.createQuery(from course,course.class) ; CourseList = courseQuery.getResultList() ; give the quotes back; } }

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

Creating student and course administrators

Student Controller

@RestController @RequestMapping(/student) public class StudentController {. private StudentRepository studentRepository ; @Autowired public StudentController(StudentRepository studentRepository){ this.studentRepository = studentRepository; } @GetMapping(/all) public List findAllStudents(){ return studentRepository.findAllStudents(); } }

  • @RestController – A controller that handles REST requests.
  • @RequestMapping – Annotation for assigning web requests to methods of request processing classes with flexible method signatures.
  • The /student/all query of the findAllStudents method returns a list of students.

Price control

@RestController @RequestMapping(/course) public class CourseController {. private CourseRepository courseRepository ; @Autowired public CourseController(CourseRepository courseRepository){ this.courseRepository = courseRepository; } @GetMapping(/all) public List findAllCourses(){ return courseRepository.findAllCourses(); } }

  • Querying /course/all with the findAllCourses method displays a list of courses in the database.

Create DispatcherServletInitializer

This sets the default mapping for the application and also allows it to be deployed on a remote 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[]{/} ; } }

Application test

To test the source of the student data, execute the following GET request to postman. http://localhost:8080/SpringMultipleDataSources_war/student/all word-image-597 To test the quote data source, execute the following GET request to postman. http://localhost:8080/SpringMultipleDataSources_war/course/all word-image-598

Supplement

In this tutorial, you learned how to configure a Spring application to use multiple data sources. The same configurations can be applied to Spring Boot with minor modifications. Spring Boot automatically configures the data source, and each data source must be accompanied by a session factory and a transaction manager. Have fun coding!Spring Framework is a time-proven solution for building Java-based enterprise applications, supporting many types of application servers. Some of the most common Spring application use cases include:. Read more about spring boot jpa multiple databases and let us know what you think.

Frequently Asked Questions

How do I configure multiple datasources in spring boot application?

If you have a need to access more than one datasources application in Spring boot, you can use the Spring Data JPA annotation @Dependent. If you have a need to use more than one datasource, please follow this article to configure and setup multiple datasources in spring boot application. Spring Boot gives a developer the ability to create Spring based apps that are decoupled from the backend. Spring Boot provides a plethora of features that helps in creating, testing and deploying our applications. Following are few of the features that Spring Boot provides.

Can we use multiple DataSource in spring boot?

Spring Boot is a very popular Java framework for writing more efficient and testable applications. With Spring Boot, you can write your applications in a fraction of the time it would take using other frameworks. It’s quick, easy to use, and the code is more readable and maintainable too. The concept of DataSources is one of the most used features in developing Enterprise Applications in Spring Boot. Spring Boot makes it much easier to setup and consume multiple different DataSources in your application. In this article, we will learn how to create and consume multiple datasources in a Spring Boot application.

How do I run multiple databases in spring boot?

The core concept of Spring Boot is to provide an entry point for new Java projects without any XML files and JAR files. This is useful if you want to package the application together along with all its dependencies in one single JAR file. If you are wondering how to run multiple Databases in a Spring Boot application, here is how you can do it. Web applications are often designed to support multiple databases, however these databases are typically piped together and do not run in the same context. This means you cannot interact with the databases, such as adding or updating data, within the same Java class. You could run separate processes to handle each database, but this would require you to have a separate application for each database.

configure multiple datasources in springspring boot 2.4 multiple data sourcesspring boot jpa multiple databasesspring boot multiple data sources jdbctemplatehow to configure multiple datasources databases with spring bootconfigure multiple datasources in spring mvc,People also search for,Feedback,Privacy settings,How Search works,configure multiple datasources in spring,how to configure multiple datasources databases with spring boot,spring boot 2.4 multiple data sources,spring boot jpa multiple databases,configure multiple datasources in spring mvc,how to connect multiple databases using hibernate spring example,spring hibernate multiple data sources example,spring boot multiple data sources jdbctemplate

You May Also Like

6 best VPNs for Internet Explorer to improve your security

Error: The file name is not specified. Milan Stanojevic – deputy editor-in-chief…

36 Best Free Windows 10 Anime Theme 2021

36 Best anime theme Windows 10 free 2021 – Want to change…

[ Fixed ] Can’t Download from Google Drive | 2021 [ Problem Solved ]

Google is one of the most important and well known technology industries…

How to Make a One Page Landscape on Microsoft Word

RECOMMENDED: Click here to fix Windows errors and optimize system performance. There…