Multiple DataBase Application SpringBoot , Hibernate and JPA

Question

I need a concept to design a multi database application using Spring boot, Hibernate and JPA.

for now i am thinking to support 4 relational database (Mysql, H2, SQLLite, Oracle).

what i am doing is to select the right database profile using the spring boot profile feature and then loading the related database properties.

## application-h2.properties
## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url=jdbc:h2:file:~/test
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.H2Dialect
# application-mysql.properties
# MySQL-Database
#spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/ntk?autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

and then in application.properties

spring.profiles.active=h2

Here the the application will run and load the property for h2 database .Same way we can select profile as Mysql and it will load the Mysql related properties.

Here, mostly every query is being generated by JPA based on the dialect. Which is compatible with all the databases (because, at a time we are running this application for one database based on the profile selected).But, my concern is if some complex query that need to be written manually how i can make it compatible for other database. Lets say some query designed for Mysql might not be compatible with Oracle.

Can anyone suggest some approach based on the description given by me , please do let me know if some other information is required.

• Raymond Nijland: "But, my concern is if some complex query that need to be written manually how i can make it compatible for other database" Very big problem, that would most likely requires a SQL transpiler
• Rohitesh: @RaymondNijland approach looks good to yout ? Please suggest. Or any other way of doing this which does not require SQL transpiler.
• Raymond Nijland: To big of a problem i think imagine if you write MySQL code SELECT MD5(..) and you need it for SQL Server (MSSQL) it would be SELECT HASHBYTES('md5', ..) this is a easy source-to-source conversion, for harder things you also have to write a interpreter in PHP or SQL to handle the more complex cases which a other RDMS might support..
• Rohitesh: @RaymondNijland it looks very complex to me. How it would be like on dectecting which profile we are generate the query specif to databases which are not compatible using native sql or something similar ??
• Raymond Nijland: "it looks very complex to me." Yes why do you think most frameworks keep it ANSI/ISO SQL standard or support one RDMS system... The problems start when you need to use SQL dialect (specific vendor extensions)

Answer 1

Given that you are already defining different profiles for each different DBs, Why not create a bean to encapsulate all these "more advanced" queries and annotate it with @profile, so that the right one is actually used based on the selected profile?

Create an interface declaring a method with every advanced queries you might need:

public interface IAdvancedQuery {
 void advancedQuery1();
 void advancedQuery2();
 ... 
}

Then, create a bean implementing this interface for each profile and annotate it accordingly:

@Profile("h2")
@Component
public class H2Queries implements IAdvancedQuery {
 @Override
 public void query1() {
 // your h2 specific query goes here
 }
 @Override
 public void query2() {
 ...
 }
 ...
}
@Profile("mysql")
@Component
public class MysqlQueries implements IAdvancedQuery {
 @Override
 public void query1() {
 // your mySQL specific query goes here
 }
 @Override
 public void query2() {
 ...
 }
 ...
}

Finally, and assuming you have a consumer service class, you just use @Autowired to inject the appropriate bean, injected automatically based on the profile you specify in your spring app:

@Service
public class ConsumerService {
 @Autowired
 public IAdvancedQuery advancedQuery;
 ...
 public void someMethod() {
 // do something with the advanced query
 advancedQuery.query1();
 ...
 }
 ...
}

The right "advanced query" method will end up being selected based on the profile and bean instantiated.