How to use 2 or more databases with spring?

Question

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.

@duffymo: first of all, thank your for taking your time to answer. I believe it's easy but I failed to find a doc / sample that do this. Could you please share some docs? Regards. — Plicatibu, May 21 '15 at 00:28
@MarcioAndreyOliveira take a look in [here](http://www.codingpedia.org/ama/how-to-setup-multiple-data-sources-with-spring-and-jpa/) or in [this thread at coderanch](http://www.coderanch.com/t/558790/Spring/Spring-JPA-Hibernate-multiple-Database). I think this might give you a help. — Diogo Calazans, May 21 '15 at 01:33
The fact that you mention application.properties makes me suspect that you're asking about Spring Boot - please update tags if it is the case, it changes the question quite a lot. — kryger, May 21 '15 at 09:36
@MarcioAndreyOliveira if my answer is correct please click on grey check mark left to my answer so I can get points, thanks — jimagic, May 21 '15 at 13:34
@calazans, thank your for the links. But as I wrote in the question, I don't want to use XML files. Regards. — Plicatibu, May 22 '15 at 22:30

score 80 · Accepted Answer · answered May 23 '15 at 01:40

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(); 
    } 
}

Is it possible to use configuration by convention instead of @Qualifier annotation? I mean, name configuration methods and injected properties with same name? I tried it, but it didn't work. — Raffael Bechara Rameh, Feb 01 '17 at 19:31
Is it possible to change Qualifier inside UserRepository according to the value of the parameter of getUser ? — jesicadev18, Aug 01 '17 at 14:15
Just a note that above configuration only works with MySQL and not mongoDB. — piepi, Mar 28 '18 at 12:38
What is the correct maven dependency for TomcatDataSourceConfiguration? — Jay, Aug 01 '18 at 13:17
Can we have two different databases, for example, MySQL and Postgres in one Spring application? — Rishi Agrawal, Nov 14 '19 at 19:25

score 26 · Answer 2 · answered May 21 '15 at 01:33

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" />

Jiten, thank you for taking your time to help me. I don't wnat to use XML files. Just annotations or properties on application.properties file. Regards. — Plicatibu, May 22 '15 at 22:27
i used your answer, but i get javax.persistence.TransactionRequiredException: no transaction is in progress, can you please take a look at https://stackoverflow.com/questions/47430321/inject-2-data-sources-into-springhibernate-application-always-throw-no-transact for more details — Melad Basilius, Nov 22 '17 at 08:56
Hi, jimagic. your answer is very helpful to me. how these data sources will choose? — SHIVA, Feb 06 '20 at 07:11

score 1 · Answer 3 · edited May 23 '17 at 12:26

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

score 1 · Answer 4 · answered Apr 15 '20 at 15:10

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

}

How to use 2 or more databases with spring?

4 Answers4

Linked