Spring & Multiple SQLs: How to easily connect your Spring application to multiple SQL databases

This post will show you how to connect your Spring application to multiple SQL Databases at the same time. But first, let’s see when multiple DB connections are useful.

spring and multiple sql databases

For example you have an application and you have that application set up in different environments (let’s call it “prod app”), and you want to write another app, to read its DB. Let’s call this application the “view app”. You want to protect the view app with user authentication for sure, because your prod app’s DB contains sensitive data (like the code to detonate printers (tongue) ), so you don’t want anyone to read it. On the other hand you don’t want your view app’s users to be stored in the prod app’s DB. What can you do? Your view application must connect to multiple (in this case SQL) Databases. This tutorial will show you how to do it.

First let’s setup the connection to both databases in the application.yml file

I made two example databases.

One is for the view app, and one is for the prod app. The view DB contains a user table with an e-mail address. I authenticate the user by checking the e-mail given in the header, you can use JWT or any other authentication method, but authentication is out of scope right now, so I implemented this simple and stupid method. If it’s stupid but it works it ain’t stupid right? (big grin)

The other is for the prod DB, it contains a table (detonation_code) with some very sensitive data!

The next step is to tell Spring how to configure the dataSource, entityManagerFactory and transactionManager for the two connections.

I use the same thing for the two configs, the only difference is in the public static final String variables, they will tell Spring where to inject which provided bean.

As you see the two files are almost exactly the same. Let’s talk about the differences.

First, there is a SPECIFIER variable, with which the other String constants will give you the specific bean names for the specific configuration. If you want to make a third DB connection don’t forget to override that (smile)

Second, there are some functions annotated with the @Primary keyword. This will tell Spring which bean to prefer from the two instances with the same type (like the two dataSource, or entityManagerFactory, etc.). Our primary beans are the view beans, so that’s why those are annotated with @Primary.

IMPORTANT! 

In Spring 1.5.7 you MUST separate your repositories into packeges (like I did in the example), and those folders should not contain each other. For example if I moved the repositories from .repository.view package to .repository package, they would use the wrong dataSource, entityManagerFactory etc.

By separating the repositories into packages you have nothing to do with them, but creating it like you do with only one DB connection. Spring will know which implementations to inject into the repositories by the EnableJpaRepositories annotation on the config (smile)

Here is an example for the DetonationCodeRepository

You can use the repository interface like you would use it with only one DB connection, add your specific queries, add Specification, etc.

That’s all, I told you it’s easy (smile)

You can take a look at the full source code here!

Thanks for reading my post, and have a nice day!

PS.: Stay up-to-date! Follow us on Facebook or LLLLinkedin 😉

Alex Sükein

Alex Sükein

- Süxy, can you tell me about robust software development Nasa uses?
- Yes. If we have a final exam tomorrow.