Spring Boot + Spring JPA + multiple data sources

Suppose you faced with the challenge of having two data sources in your Spring Boot application. Official documentation tells you how to configure them. But how to use them for your JPA layer is not that clear. Here is my quick guide about it.

Preface

You can imagine our two databases as two parking lots — one for cars and one for trucks. Trucks don`t fit in on cars parking lot. Cars can fit in into trucks parking lot, but from an economic perspective, it does not make sense to reserve truck parking space for a car. I`ll use this case in my example project.

In real-world scenarios, it can be that we want one data source responsible for only writes cause it is master DB and the other one is just replica for reading operations only. Or it can be that second DB serves as some reports cause we generate them on a nightly basis. Of course, I`ll agree that in the microservice world you can have separate application two work with the second database. But sometimes it is not an option or not needed.

So lets` have a look at how the database layer can be quickly configured and then used.

Configuration

Lets` start with the configuration:

Easy to encounter that we have app.cars.datasource and app.trucks.datasource reflecting two databases that we`re going to use.

Application configuration for data sources

Now lets` proceed with actual data sources in application. As you can see they use a different set of properties which we configured before.

One for cars:

And one for trucks:

JPA repository

Here are entities and JPA repositories of the project.

JPA repositories:

Entity managers configuration

Now, to be able to use different data sources for different repositories we need to define entity manager configuration for cars and trucks. I made each in different configuration classes.

Also, notice EnableJpaRepositorie annotation which is important to enabling JPA repositories and EnableTransactionManagemen annotation to enable Spring transaction management.

EnableJpaRepository has basePackages property which specifies which package to scan for repository classes. Without it, repositories won`t be found. In our case repositories are located in different packages for the usage simplicity. You can come with any naming, but important to be able to quickly recognize which repository uses which database.

Hibernate naming strategy

Common SQL convention to use “_” delimiter in field names. For example, plate_name. In this case, the expectation will be that Car.plateNumber and Truck.plateNumber properties will mapped to plate_number column of respectful tables. It would automatically happen if the data source and entity manager was configured Spring JPA directly. Not the case anymore since we created these beans by ourselves instead. Hence you`ll get an exception:

org.postgresql.util.PSQLException: ERROR: column truck0_.platenumber does not exist

And it is totally true because the column is named plate_number, but property called platenumber. This means Hibernate tries to map the name of the property to name of the column as is. To fix this we need to set an appropriate naming strategy to our entity manager. You can check entity manager factory configuration again and encounter this line:

.properties(jpaProperties)

It corresponds to bean with name commonJpaProperties:

And add hibernate strategy properties to configuration like this:

Conclusion

By implementing this all I realize how many things Spring JPA preconfigure for you which allows you quickly start business logic implementation. I appreciate the fact that still, you have the flexibility to add more data sources and use JPA repositories which is very handy.

You can check the whole project on Github. Thanks, hope you find it useful!