CODEWITHSUNDEEP
springhibernatespring-mvc
Vaibhav Sharma
Vaibhav Sharma

Reputation: 1742

Executing an SQL query in multiple databases through Spring - Hibernate

I have a below problem and I am looking for solution in Spring - MVC and Hibernate only.

Problem Statement: There is UI which is having a text area and Submit button. In text area user can write a SQL query and after submitting, this SQL query has to be executed at multiple databases.

Note: Assume that multiple databases are already configured.

Please tell me the best approach for implementing it.

Upvotes: 0

Views: 241

Answers (2)

Kristoffer
Kristoffer

Reputation: 251

I will assume that you have multiple datasources wired in you application context (?). If so and assuming that they have different qualifiers, you can inject them into another component, typically a service which is invoked from your controller or directly into your controller.

@Autowired List<DataSource> datasources;

Should do the trick, alternatively through constructor injection. With the latter, you can create a JdbcTemplate per DataSource. Then you loop through the list and execute the same sql query on all datasources.

Upvotes: 1

romainbsl
romainbsl

Reputation: 544

You might consider 2 options:

  1. AbstractRoutingDataSource

With this solution you are able to switch to the right database before executing queries. So you could write something like 

  // GOLD database
  CustomerContextHolder.setCustomerType(CustomerType.GOLD);
  List<Item> goldItems = catalog.getItems();
  assertEquals(3, goldItems.size());
  System.out.println("gold items: " + goldItems);

  // SILVER database
  CustomerContextHolder.setCustomerType(CustomerType.SILVER);
  List<Item> silverItems = catalog.getItems();
  assertEquals(2, silverItems.size());
  System.out.println("silver items: " + silverItems);

  // DEFAULT database
  CustomerContextHolder.clearCustomerType();
  List<Item> bronzeItems = catalog.getItems();
  assertEquals(1, bronzeItems.size());
  System.out.println("bronze items: " + bronzeItems);

Here are some docs:

https://spring.io/blog/2007/01/23/dynamic-datasource-routing/

https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/datasource/lookup/AbstractRoutingDataSource.html

http://fedulov.website/2015/10/14/dynamic-datasource-routing-with-spring/

  1. Using multiple transaction manager (one by database)

This involve that you know in advance what Transaction Manager you will need for a specific Service.

https://docs.spring.io/spring/docs/3.0.x/spring-framework-reference/html/transaction.html#tx-multiple-tx-mgrs-with-attransactional

Upvotes: 1

Related Questions