How to use 2 or more databases with spring?

Asked 21/5, 2015 at 0:24 Answered 15/4, 2020 at 15:10

Solved spring spring-boot multiple-databases

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.

Toshiatoshiko answered 21/5, 2015 at 0:24 Comment(5)

@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. – Toshiatoshiko 21/5, 2015 at 0:28

@MarcioAndreyOliveira take a look in here or in this thread at coderanch. I think this might give you a help. – Outwards 21/5, 2015 at 1: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. – Inadequate 21/5, 2015 at 9:36

@MarcioAndreyOliveira if my answer is correct please click on grey check mark left to my answer so I can get points, thanks – Maureenmaureene 21/5, 2015 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. – Toshiatoshiko 22/5, 2015 at 22:30

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

Maureenmaureene answered 23/5, 2015 at 1:40 Comment(6)

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. – Moslemism 1/2, 2017 at 19:31

Is it also possible to monitor these datasources via jmx? – Ceresin 12/7, 2017 at 14:11

Is it possible to change Qualifier inside UserRepository according to the value of the parameter of getUser ? – Nuke 1/8, 2017 at 14:15

Just a note that above configuration only works with MySQL and not mongoDB. – Sabbatarian 28/3, 2018 at 12:38

What is the correct maven dependency for TomcatDataSourceConfiguration? – Tiruchirapalli 1/8, 2018 at 13:17

Can we have two different databases, for example, MySQL and Postgres in one Spring application? – Bethea 14/11, 2019 at 19:25

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

Maureenmaureene answered 21/5, 2015 at 1:33 Comment(3)

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. – Toshiatoshiko 22/5, 2015 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 #47430821 for more details – Recrimination 22/11, 2017 at 8:56

Hi, jimagic. your answer is very helpful to me. how these data sources will choose? – Lussier 6/2, 2020 at 7:11

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://mcmap.net/q/272940/-spring-boot-multiple-datasource

Acetometer answered 16/12, 2016 at 3:50 Comment(0)

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

}

Phelia answered 15/4, 2020 at 15:10 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags