CODEWITHSUNDEEP
javaspringhibernatespring-bootspring-data-jpa
Rohitesh
Rohitesh

Reputation: 1624

Multiple DataBase Application SpringBoot , Hibernate and JPA

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.

Upvotes: 1

Views: 735

Answers (1)

Ignacio Perez
Ignacio Perez

Reputation: 21

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.

Upvotes: 2

Related Questions