How to use 2 or more databases with spring?

SpringSpring BootMultiple Databases

Spring Problem Overview

I have an application that runs Spring MVC.

I need it to access 2 different databases in my app (one is a PostgreSQL and the other one is a MySQL database).

How do I configure this using just annotations or application.properties file?

Regards.

Spring Solutions

Solution 1 - Spring

Here is the example code for having multiple Database/datasource on Spring-Boot I hope it helps!

application.properties

spring.ds_items.driverClassName=org.postgresql.Driver 
spring.ds_items.url=jdbc:postgresql://srv0/test 
spring.ds_items.username=test0 
spring.ds_items.password=test0 
 
 
spring.ds_users.driverClassName=org.postgresql.Driver 
spring.ds_users.url=jdbc:postgresql://srv1/test 
spring.ds_users.username=test1 
spring.ds_users.password=test1

DatabaseItemsConfig.java

package sb; 
 
import org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration; 
import org.springframework.boot.context.properties.ConfigurationProperties; 
import org.springframework.context.annotation.Bean; 
import org.springframework.context.annotation.Configuration; 
import org.springframework.jdbc.core.JdbcTemplate; 
 
import javax.sql.DataSource; 
 
@Configuration 
@ConfigurationProperties(name = "spring.ds_items") 
public class DatabaseItemsConfig extends TomcatDataSourceConfiguration { 
 
    @Bean(name = "dsItems") 
    public DataSource dataSource() { 
        return super.dataSource(); 
    } 
 
    @Bean(name = "jdbcItems") 
    public JdbcTemplate jdbcTemplate(DataSource dsItems) { 
        return new JdbcTemplate(dsItems); 
    } 
}

DatabaseUsersConfig.java

package sb; 
 
import org.springframework.boot.autoconfigure.jdbc.TomcatDataSourceConfiguration; 
import org.springframework.boot.context.properties.ConfigurationProperties; 
import org.springframework.context.annotation.Bean; 
import org.springframework.context.annotation.Configuration; 
import org.springframework.jdbc.core.JdbcTemplate; 
 
import javax.sql.DataSource; 
 
@Configuration 
@ConfigurationProperties(name = "spring.ds_users") 
public class DatabaseUsersConfig extends TomcatDataSourceConfiguration { 
 
    @Bean(name = "dsUsers") 
    public DataSource dataSource() { 
        return super.dataSource(); 
    } 
 
    @Bean(name = "jdbcUsers") 
    public JdbcTemplate jdbcTemplate(DataSource dsUsers) { 
        return new JdbcTemplate(dsUsers); 
    } 
 
}

ItemRepository.java

package sb; 
 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.beans.factory.annotation.Qualifier; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.RowMapper; 
import org.springframework.stereotype.Repository; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
@Repository 
public class ItemRepository { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 
 
    @Autowired 
    @Qualifier("jdbcItems") 
    protected JdbcTemplate jdbc; 
 
    public Item getItem(long id) { 
        return jdbc.queryForObject("SELECT * FROM sb_item WHERE id=?", itemMapper, id); 
    } 
 
    private static final RowMapper<Item> itemMapper = new RowMapper<Item>() {
        public Item mapRow(ResultSet rs, int rowNum) throws SQLException { 
            Item item = new Item(rs.getLong("id"), rs.getString("title")); 
            item.price = rs.getDouble("id"); 
            return item; 
        } 
    }; 
}

UserRepository.java

package sb; 
 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.beans.factory.annotation.Qualifier; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.RowMapper; 
import org.springframework.stereotype.Repository; 
 
import java.sql.ResultSet; 
import java.sql.SQLException; 
 
@Repository 
public class UserRepository { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 
 
    @Autowired 
    @Qualifier("jdbcUsers") 
    protected JdbcTemplate jdbc; 
 
    public User getUser(long id) { 
        return jdbc.queryForObject("SELECT * FROM sb_user WHERE id=?", userMapper, id); 
    } 
 
    private static final RowMapper<User> userMapper = new RowMapper<User>() {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException { 
            User user = new User(rs.getLong("id"), rs.getString("name")); 
            user.alias = rs.getString("alias"); 
            return user; 
        } 
    }; 
}

Controller.java

package sb; 
 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.web.bind.annotation.RequestMapping; 
import org.springframework.web.bind.annotation.RequestParam; 
import org.springframework.web.bind.annotation.RestController; 
 
@RestController 
public class Controller { 
    protected final Logger log = LoggerFactory.getLogger(getClass()); 
 
    @Autowired 
    private UserRepository users; 
 
    @Autowired 
    private ItemRepository items; 
 
    @RequestMapping("test") 
    public String test() { 
        log.info("Test"); 
        return "OK"; 
    } 
 
    @RequestMapping("user") 
    public User getUser(@RequestParam("id") long id) { 
        log.info("Get user"); 
        return users.getUser(id); 
    } 
 
    @RequestMapping("item") 
    public Item getItem(@RequestParam("id") long id) { 
        log.info("Get item"); 
        return items.getItem(id); 
    } 
 
}

Application.java

package sb; 
 
import org.springframework.boot.SpringApplication; 
import org.springframework.boot.autoconfigure.EnableAutoConfiguration; 
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; 
import org.springframework.context.annotation.ComponentScan; 
import org.springframework.context.annotation.Configuration; 
 
@EnableAutoConfiguration(exclude = DataSourceAutoConfiguration.class) 
@Configuration 
@ComponentScan(basePackages = "sb") 
public class Application { 
 
    public static void main(String[] args) throws Throwable { 
        SpringApplication app = new SpringApplication(Application.class); 
        app.run(); 
    } 
}

Solution 2 - Spring

this is how you set up multiple data sources on spring xml file, here is mine for example, hope it helps

<bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url"
              value="jdbc:mysql://localhost:3306/gl?characterEncoding=UTF-8" />
    <property name="username" value="root" />
    <property name="password" value="2238295" />
</bean>



<bean id="mainDataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url"
              value="jdbc:mysql://localhost:3306/gl_main?characterEncoding=UTF-8" />
    <property name="username" value="root" />
    <property name="password" value="2238295" />
</bean>

<!-- Hibernate 4 SessionFactory Bean definition -->
<bean id="sfAccounting"
      class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="com.gl.domain.accounting" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.connection.useUnicode">true</prop>
            <prop key="hibernate.connection.characterEncoding">UTF-8</prop>
            <prop key="hibernate.connection.charSet">UTF-8</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect
            </prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.show_sql">false</prop>
        </props>
    </property>
</bean>



<!-- Hibernate 4 SessionFactory Bean definition -->
<bean id="sfCommon"
      class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="mainDataSource" />
    <property name="packagesToScan" value="com.gl.domain.common" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.connection.useUnicode">true</prop>
            <prop key="hibernate.connection.characterEncoding">UTF-8</prop>
            <prop key="hibernate.connection.charSet">UTF-8</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect
            </prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.show_sql">false</prop>
        </props>
    </property>
</bean>



<tx:annotation-driven transaction-manager="txnManagerAccounting"/>
<tx:annotation-driven transaction-manager="txnManagerCommon"/>

<bean id="txnManagerAccounting"
      class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sfAccounting" />

</bean>


<bean id="txnManagerCommon"
      class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sfCommon" />

</bean>

<bean id="persistenceExceptionTranslationPostProcessor"
      class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />

Solution 3 - Spring

You can also try to define multiple datasources & assign one of them as primary.

Here is the demo code.

The Primary Datasource:

@MapperScan(basePackages = "com.demo.mysqldao",
sqlSessionFactoryRef = "mysqlSqlSessionFactory")
@Configuration
public class MysqlDatabaseConfig {

  @Value("${mysql.datasource.url}")
  String jdbcUrl;

  @Value("${mysql.datasource.username}")
  String jdbcUser;

  @Value("${mysql.datasource.password}")
  String jdbcPass;

  @Value("${mysql.datasource.driverClassName}")
  String jdbcProvider;

  BasicDataSource src = null;

  Logger log = LoggerFactory.getLogger(MysqlDatabaseConfig.class);

  @Bean(name = "mysqlDataSource")
  @Primary
  @PostConstruct
  public DataSource mysqlDataSource() {
    if (jdbcUrl == null) {
      throw new RuntimeException("initialization datasource error with null jdbcUrl");
    }
    log.info("Using JDBC ------------> " + jdbcUrl);
    if (src == null) {
      BasicDataSource dataSource = new BasicDataSource();
      dataSource.setDriverClassName(jdbcProvider);
      dataSource.setUrl(jdbcUrl);
      dataSource.setUsername(jdbcUser);
      dataSource.setPassword(jdbcPass);
      dataSource.setMaxActive(100);
      dataSource.setMinIdle(3);
      dataSource.setMaxIdle(10);
      dataSource.setMinEvictableIdleTimeMillis(60 * 1000);
      dataSource.setNumTestsPerEvictionRun(100);
      dataSource.setRemoveAbandoned(true);
      dataSource.setRemoveAbandonedTimeout(60 * 1000);
      dataSource.setTestOnBorrow(true);
      dataSource.setTestOnReturn(true);
      dataSource.setTestWhileIdle(true);
      dataSource.setTimeBetweenEvictionRunsMillis(30 * 60 * 1000);
      src = dataSource;
    }
    return src;
  }

  @Autowired
  @Qualifier(value = "mysqlDataSource")
  DataSource mysqlDataSource;

  @Bean("mysqlTransactionManager")
  @Primary
  public DataSourceTransactionManager mysqlTransactionManager() {
    return new DataSourceTransactionManager(mysqlDataSource);
  }

  @Bean("mysqlSqlSessionFactory")
  @Primary
  public SqlSessionFactory mysqlSqlSessionFactory() throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(mysqlDataSource);
    return sessionFactory.getObject();
  }

Another Datasource (In my demo, it's sqlserver)

@MapperScan(basePackages = "com.demo.sqlserverdao",
sqlSessionFactoryRef = "sqlserverSqlSessionFactory")
@Configuration
public class SQLServerDatabaseConfig {

  @Value("${sqlserver.datasource.url}")
  String jdbcUrl;

  @Value("${sqlserver.datasource.username}")
  String jdbcUser;

  @Value("${sqlserver.datasource.password}")
  String jdbcPass;

  @Value("${sqlserver.datasource.driverClassName}")
  String jdbcProvider;


  BasicDataSource src = null;

  @Bean(name = "sqlServerDataSource")
  @PostConstruct
  public DataSource sqlServerDataSource() {

    if (jdbcUrl == null) {
      throw new RuntimeException("initialization sqlserver datasource error with null jdbcUrl");
    }
    if (src == null) {
      BasicDataSource dataSource = new BasicDataSource();
      dataSource.setDriverClassName(jdbcProvider);
      dataSource.setUrl(jdbcUrl);
      dataSource.setUsername(jdbcUser);
      dataSource.setPassword(jdbcPass);
      dataSource.setMaxActive(100);
      dataSource.setMinIdle(3);
      dataSource.setMaxIdle(10);
      dataSource.setMinEvictableIdleTimeMillis(60 * 1000);
      dataSource.setNumTestsPerEvictionRun(100);
      dataSource.setRemoveAbandoned(true);
      dataSource.setRemoveAbandonedTimeout(60 * 1000);
      dataSource.setTestOnBorrow(true);
      dataSource.setTestOnReturn(true);
      dataSource.setTestWhileIdle(true);
      dataSource.setTimeBetweenEvictionRunsMillis(30 * 60 * 1000);
      src = dataSource;
    }
    return src;
  }

  @Autowired
  @Qualifier(value = "sqlServerDataSource")
  DataSource sqlServerDataSource;

  @Bean("sqlserverTransactionManager")
  public DataSourceTransactionManager sqlserverTransactionManager() {
    return new DataSourceTransactionManager(sqlServerDataSource);
  }

  @Bean("sqlserverSqlSessionFactory")
  public SqlSessionFactory sqlserverSqlSessionFactory() throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(sqlServerDataSource);
    return sessionFactory.getObject();
  }

Ref:https://stackoverflow.com/a/27679997/6037575

Solution 4 - Spring

I offer another version which works only with Java classes and annotations. I have to connect to a postgresql-database and a mysql-database.

Here is my RootConfiguration class where I've defined my datasources and my SessionFactories

@Configuration
@ComponentScan(basePackages="com.netzwerge.nzadmin")
@EnableTransactionManagement
public class RootConfiguration {

  @Autowired
  ServletContext context;
	
  @Bean
  @Qualifier(value="postgre")
  public HibernateTransactionManager txManagerPostgre() {
	  HibernateTransactionManager txManager = new HibernateTransactionManager();
	  txManager.setSessionFactory(sessionFactoryPostgre().getObject());
	
	  return txManager;
  }

  @Bean
  @Qualifier(value="postgre")
  public LocalSessionFactoryBean sessionFactoryProvab() {
	  LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();

	  Properties hibernateProperties = new Properties();
	  hibernateProperties.setProperty("hibernate.show_sql", "false");
	  hibernateProperties.setProperty("hibernate.format_sql", "true");
	  hibernateProperties.setProperty("hibernate.current_session_context_class", "org.springframework.orm.hibernate5.SpringSessionContext");
	  hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQL95Dialect");
	  hibernateProperties.setProperty("hibernate.connection.datasource", "java:comp/env/jdbc/postgres");
	  sessionFactory.setHibernateProperties(hibernateProperties);
	
	  sessionFactory.setPackagesToScan("com.programname.model");
	  sessionFactory.setDataSource(dataSourcePostgre());
	
	  return sessionFactory;
  }

  @Bean
  public DataSource dataSourcePostgre() {
	  JndiObjectFactoryBean dataSource = new JndiObjectFactoryBean();
	  dataSource.setJndiName("java:comp/env/jdbc/postgres");
	  dataSource.setResourceRef(true);
	
	  return (DataSource) dataSource.getObject();
  }



  @Bean
  @Qualifier(value="mysql")
  public HibernateTransactionManager txManagerMySql() {
	  HibernateTransactionManager txManager = new HibernateTransactionManager();
	  txManager.setSessionFactory(sessionFactoryMysql().getObject());
	
	  return txManager;
  }

  @Bean
  @Qualifier(value="mysql")
  public LocalSessionFactoryBean sessionFactorySystemdaten() {
	  LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();

	  Properties hibernateProperties = new Properties();
	  hibernateProperties.setProperty("hibernate.show_sql", "false");
	  hibernateProperties.setProperty("hibernate.format_sql", "true");
	  hibernateProperties.setProperty("hibernate.current_session_context_class", "org.springframework.orm.hibernate5.SpringSessionContext");
	  hibernateProperties.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
	  hibernateProperties.setProperty("hibernate.connection.datasource", "java:comp/env/jdbc/mysql");
	  sessionFactory.setHibernateProperties(hibernateProperties);
	
	  sessionFactory.setPackagesToScan("com.programname.model");
	  sessionFactory.setDataSource(dataSourceSystemdaten());
	
	  return sessionFactory;
  }
	
  @Bean
  public DataSource dataSourceMysql() {
	  JndiObjectFactoryBean dataSource = new JndiObjectFactoryBean();
	  dataSource.setJndiName("java:comp/env/jdbc/mysql");
	  dataSource.setResourceRef(true);
	
	  return (DataSource) dataSource.getObject();
  }

  // Other definitions etc.
}

And here is how you access the different databases in your DAOs. Choose the qualifier you need to access the correct database and also choose the correct transaction manager. In my case I use hibernate in both cases but you are free to use whatever seems appropriate to you.

@Repository
@Transactional("mysql")
public class CustomerDaoImplMySql implements CustomerDao {


  @Autowired
  @Qualifier("mysql")
  private SessionFactory sessionFactory;

  // The Routines to access the data like CRUD go here

}
Categories
Recommended Spring Solutions
Recommended Spring Boot Solutions

Previous Solution:

classifiers in scikit-learn that handle nan/null

PythonPandasMachine LearningScikit LearnNan

Next Solution:

Maintaining good scroll performance when using AVPlayer

IosVideoAvfoundationAvplayer

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionPlicatibuView Question on Stackoverflow
Solution 1 - SpringjimagicView Answer on Stackoverflow
Solution 2 - SpringjimagicView Answer on Stackoverflow
Solution 3 - SpringDai KaixianView Answer on Stackoverflow
Solution 4 - SpringM46View Answer on Stackoverflow