Wsrtjtyk

I’m building a Spring Boot application that should support various databases engines (MySQL, PostgreSQL, Oracle, SQL Server).

The user will be prompted to select which database it would like to use when installing the application and all tables will be created after this. I think that I should save the user option somewhere and then use it to select the right engine that my application will use to instantiate the repositories.

I googled for some references or best practices that I should follow to implement this but wasn’t able to find (maybe I’m searching the wrong terms - sorry).

Do you have any resource that points me to the right direction?

Thanks a lot!!!

It's unclear what you mean by installing but if you want your application to start with different databases you can use different Spring profiles with your application.yml Spring boot profiles

---



spring:

    profiles: postgres

  datasource:

    url: 

    username: 

    password:

    driver-class-name: org.postgresql.Driver

---



spring:

  profiles: mysql

  datasource:

    url: 

    username: 

    password:

    driver-class-name: com.mysql.jdbc.Driver

You can then start the application with the respective profile

java -jar -Dspring.profiles.active=mysql yourapp.jar

You can define different connections and get them with specific methods

private DataSource mySqlDataSource;

private DataSource postgresDataSource;



public Connection getMySqlConnection() throws SQLException {

   mySqlDataSource.getConnection();

}

public Connection getPostgresConnection() throws SQLException {

   postgresDataSource.getConnection();

}

You can refer sample. In sample I use hibernate + spring boot for multiple databases.
You can work by this way:

Step 1: you declare in application.properties info connect of database (mysql, oracle, postgresql)

# MySQL-Database

mysql.db.driver: com.mysql.jdbc.Driver

mysql.db.url: jdbc:mysql://localhost:3306/test

mysql.db.username: root

mysql.db.password: root

mysql.hibernate.dialect: org.hibernate.dialect.MySQL5Dialect

mysql.entitymanager.packagesToScan: com.test.mysql





# postgresql-Database

postgresql.db.driver: org.postgresql.Driver

postgresql.db.url: jdbc:postgresql://localhost:5432/postgres_demo

postgresql.db.username: root

postgresql.db.password: 

postgresql.hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect

postgresql.entitymanager.packagesToScan: com.test.postgresql





# Oracle-Database

oracle.db.driver: oracle.jdbc.driver.OracleDriver

oracle.db.url: jdbc:oracle:thin:@localhost:1521:xe

oracle.db.username: root

oracle.db.password: 

oracle.hibernate.dialect: org.hibernate.dialect.Oracle10gDialect

oracle.entitymanager.packagesToScan: com.test.oracle

Step 2: In project spring boot you can refer structure project such as:

Step 3 : Implement data source for mysql, oracle, postgresql. you can refer MysqlDatabaseConfig.java, OracleDatabaseConfig.java, PostgresDatabaseConfig.java

package com.test.mysql;



import java.util.Properties;



import javax.sql.DataSource;



import org.springframework.beans.factory.annotation.Value;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import org.springframework.orm.hibernate4.HibernateTransactionManager;

import org.springframework.orm.hibernate4.LocalSessionFactoryBean;

import org.springframework.transaction.annotation.EnableTransactionManagement;



@Configuration

@EnableTransactionManagement

public class MysqlDatabaseConfig {



  @Value("${mysql.db.driver}")

  private String DB_DRIVER;



  @Value("${mysql.db.password}")

  private String DB_PASSWORD;



  @Value("${mysql.db.url}")

  private String DB_URL;



  @Value("${mysql.db.username}")

  private String DB_USERNAME;



  @Value("${mysql.hibernate.dialect}")

  private String HIBERNATE_DIALECT;



  @Value("${mysql.entitymanager.packagesToScan}")

  private String ENTITYMANAGER_PACKAGES_TO_SCAN;



  @Bean(name="mysqlDataSource")

  @Primary

  public DataSource cmrDataSource() {

    DriverManagerDataSource dataSource = new DriverManagerDataSource();

    dataSource.setDriverClassName(DB_DRIVER);

    dataSource.setUrl(DB_URL);

    dataSource.setUsername(DB_USERNAME);

    dataSource.setPassword(DB_PASSWORD);

    return dataSource;

  }



  @Bean(name="mysqlSessionFactory")

  public LocalSessionFactoryBean crmSessionFactory() {

    LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();

    sessionFactoryBean.setDataSource(cmrDataSource());

    sessionFactoryBean.setPackagesToScan(ENTITYMANAGER_PACKAGES_TO_SCAN);

    Properties hibernateProperties = new Properties();

    hibernateProperties.put("hibernate.dialect", HIBERNATE_DIALECT);

    sessionFactoryBean.setHibernateProperties(hibernateProperties);



    return sessionFactoryBean;

  }



  @Bean(name="mysqlTransactionManager")

  public HibernateTransactionManager transactionManager() {

    HibernateTransactionManager transactionManager =  new HibernateTransactionManager();

    transactionManager.setSessionFactory(crmSessionFactory().getObject());

    return transactionManager;

  }



}

OracleDatabaseConfig.java

package com.test.oracle;



import org.springframework.beans.factory.annotation.Value;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import org.springframework.orm.hibernate4.HibernateTransactionManager;

import org.springframework.orm.hibernate4.LocalSessionFactoryBean;

import org.springframework.transaction.annotation.EnableTransactionManagement;



import javax.sql.DataSource;

import java.util.Properties;



@Configuration

@EnableTransactionManagement

public class OracleDatabaseConfig {



  @Value("${oracle.db.driver}")

  private String DB_DRIVER;



  @Value("${oracle.db.password}")

  private String DB_PASSWORD;



  @Value("${oracle.db.url}")

  private String DB_URL;



  @Value("${oracle.db.username}")

  private String DB_USERNAME;



  @Value("${oracle.hibernate.dialect}")

  private String HIBERNATE_DIALECT;



  @Value("${oracle.entitymanager.packagesToScan}")

  private String ENTITYMANAGER_PACKAGES_TO_SCAN;



  @Bean(name="oracleDataSource")

  @Primary

  public DataSource cmrDataSource() {

    DriverManagerDataSource dataSource = new DriverManagerDataSource();

    dataSource.setDriverClassName(DB_DRIVER);

    dataSource.setUrl(DB_URL);

    dataSource.setUsername(DB_USERNAME);

    dataSource.setPassword(DB_PASSWORD);

    return dataSource;

  }



  @Bean(name="oracleSessionFactory")

  public LocalSessionFactoryBean crmSessionFactory() {

    LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();

    sessionFactoryBean.setDataSource(cmrDataSource());

    sessionFactoryBean.setPackagesToScan(ENTITYMANAGER_PACKAGES_TO_SCAN);

    Properties hibernateProperties = new Properties();

    hibernateProperties.put("hibernate.dialect", HIBERNATE_DIALECT);

    sessionFactoryBean.setHibernateProperties(hibernateProperties);



    return sessionFactoryBean;

  }



  @Bean(name="oracleTransactionManager")

  public HibernateTransactionManager transactionManager() {

    HibernateTransactionManager transactionManager =  new HibernateTransactionManager();

    transactionManager.setSessionFactory(crmSessionFactory().getObject());

    return transactionManager;

  }



}

PostgresDatabaseConfig.java

package com.test.postgresql;



import org.springframework.beans.factory.annotation.Value;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import org.springframework.orm.hibernate4.HibernateTransactionManager;

import org.springframework.orm.hibernate4.LocalSessionFactoryBean;

import org.springframework.transaction.annotation.EnableTransactionManagement;



import javax.sql.DataSource;

import java.util.Properties;



@Configuration

@EnableTransactionManagement

public class PostgresDatabaseConfig {



  @Value("${postgresql.db.driver}")

  private String DB_DRIVER;



  @Value("${postgresql.db.password}")

  private String DB_PASSWORD;



  @Value("${postgresql.db.url}")

  private String DB_URL;



  @Value("${postgresql.db.username}")

  private String DB_USERNAME;



  @Value("${postgresql.hibernate.dialect}")

  private String HIBERNATE_DIALECT;



  @Value("${postgresql.entitymanager.packagesToScan}")

  private String ENTITYMANAGER_PACKAGES_TO_SCAN;



  @Bean(name="postgresqlDataSource")

  @Primary

  public DataSource cmrDataSource() {

    DriverManagerDataSource dataSource = new DriverManagerDataSource();

    dataSource.setDriverClassName(DB_DRIVER);

    dataSource.setUrl(DB_URL);

    dataSource.setUsername(DB_USERNAME);

    dataSource.setPassword(DB_PASSWORD);

    return dataSource;

  }



  @Bean(name="postgresqlSessionFactory")

  public LocalSessionFactoryBean crmSessionFactory() {

    LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();

    sessionFactoryBean.setDataSource(cmrDataSource());

    sessionFactoryBean.setPackagesToScan(ENTITYMANAGER_PACKAGES_TO_SCAN);

    Properties hibernateProperties = new Properties();

    hibernateProperties.put("hibernate.dialect", HIBERNATE_DIALECT);

    sessionFactoryBean.setHibernateProperties(hibernateProperties);



    return sessionFactoryBean;

  }



  @Bean(name="postgresqlTransactionManager")

  public HibernateTransactionManager transactionManager() {

    HibernateTransactionManager transactionManager =  new HibernateTransactionManager();

    transactionManager.setSessionFactory(crmSessionFactory().getObject());

    return transactionManager;

  }



}

Step 4: call to use:
in case mysql

package com.test.mysql.dao;



import org.hibernate.SessionFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.stereotype.Repository;

import org.springframework.transaction.annotation.Transactional;



@Repository

@Transactional("mysqlTransactionManager")

public class TestMysqlDao {

    @Autowired

    @Qualifier("mysqlSessionFactory")

    private SessionFactory sessionFactory;

}

In case oracle

package com.test.oracle.dao;



import org.hibernate.SessionFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.stereotype.Repository;

import org.springframework.transaction.annotation.Transactional;



@Repository

@Transactional("oracleTransactionManager")

public class TestOracleDao {

    @Autowired

    @Qualifier("oracleSessionFactory")

    private SessionFactory sessionFactory;

}

In case postgresql

package com.test.postgresql.dao;



import org.hibernate.SessionFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.beans.factory.annotation.Qualifier;

import org.springframework.stereotype.Repository;

import org.springframework.transaction.annotation.Transactional;



@Repository

@Transactional("postgresqlTransactionManager")

public class TestPostgresDao {

    @Autowired

    @Qualifier("postgresqlSessionFactory")

    private SessionFactory sessionFactory;

}