3

I currently have one database connected and it is working. I would like to connect another (and eventually 2 more) databases. How do I do so? There should be a solution using only annotations and properties files.

I also read this question, How to use 2 or more databases with spring?, but I dont know how it works too well/ if it will apply. I'm not using a controller class and I dont know what that does. I'm also not sure how the config class they mention in the answer actually connects to the specific DO.

This is my application.properties file: (marked out username and password but its there in my file)

hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.default_schema=dbo
hibernate.packagesToScan=src.repositories.LMClientRepository.java

spring.jpa.generate-ddl=true
spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy
spring.datasource.username=***
spring.datasource.password=***
spring.datasource.url=jdbc:sqlserver://schqvsqlaod:1433;database=dbMOBClientTemp;integratedSecurity=false;
spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1
spring.jpa.database=dbMOBClientTemp
spring.jpa.show-sql=true
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

This is my application file:

package testApplication;

import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.orm.jpa.EntityScan;
import org.springframework.cache.annotation.EnableCaching;
import org.springframework.context.annotation.Bean;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import fileRetrieval.InputFileParse;
import lmDataObjects.LMClientDO;
import lmDataObjects.LoadMethodDO;
import repositories.LMClientRepository;
import repositories.LoadMethodRepository;

@SpringBootApplication
@EnableJpaRepositories(basePackageClasses = LoadMethodRepository.class)
@EntityScan(basePackageClasses = LoadMethodDO.class)
@EnableCaching
public class Application {

    private static final Logger log = LoggerFactory.getLogger(Application.class);

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Bean
    public CommandLineRunner demo(LoadMethodRepository lm_repo, LMClientRepository lmc_repo) {
        return (args) -> {
            List<LMClientDO> lmlist = InputFileParse.getMultiGroupfile();

            List<String> uniqueMediaIds = new ArrayList(InputFileParse.getUniqueMediaIds());

            for (int i = 0; i < InputFileParse.getUniqueMediaIds().size(); i ++){
                lm_repo.save(new LoadMethodDO(uniqueMediaIds.get(i)));
            }

            for (int i = 0; i < lmlist.size(); i++){
                lmc_repo.save(new LMClientDO(lmlist.get(i).getClientId(), lmlist.get(i).getMediaId()));
            }
            //Here is where I would like to do stuff with data from the other database that I have not connected yet

        };
    }
}

I also made a new properties file called application-MTS.properties and I put data for the new database in there. Still unsure of what to do with it.

spring.datasource.username=***
spring.datasource.password=***
spring.datasource.url=jdbc:sqlserver://SCHQVSQLCON2\VSPD:1433;database=dbMTS;integratedSecurity=false;
Community
  • 1
  • 1
user3321551
  • 123
  • 2
  • 3
  • 15

2 Answers2

2

Spring Data has understood this is a common use case that people may want so they created a example project on how to do this. I would review the spring-data-examples for multiple-datasources.

The important aspect is to look at the OrderConfig and CustomerConfig classes as they define the two data sources.

Shawn Clark
  • 3,330
  • 2
  • 18
  • 30
  • In the example you gave, it seems there is no properties file. How is this being done without it? I'm not seeing where the name of the database, username, password, or url are set – user3321551 Aug 11 '16 at 16:12
  • You might wanna add a datasource grammatically as `@Bean public DataSource dataSource(){ BasicDataSource dataSource = new BasicDataSource(); dataSource.setDriverClassName("com.mysql.jdbc.Driver"); dataSource.setUrl("jdbc:mysql://localhost:3306/test"); dataSource.setUsername("root"); dataSource.setPassword("secret"); return dataSource; }` – yasaspramoda Jul 05 '17 at 05:25
  • Thanks for this! Yeah this is a really common use case for web applications whether monolithic or not. In-memory L1 cache either implemented with a built-in Spring cache or embedded H2 with a local H2/Postgres instance on-disk L2 cache and a remote Postgres to hold the most up-to-date versions of data with all your redundancy. – patrickjp93 Jul 08 '18 at 06:10
0

For Hibernate you can have mutiple hbm.xml files and get that in a class and use it.

Something like this:

if ("yourCondition".equals(definedCondition)) {
    sf = new Configuration().configure("example.cfg.xml").buildSessionFactory();
} else {
    sf = new Configuration().configure("exampleTwo.cfg.xml").buildSessionFactory();
}

You can have separate database and login info in the xml files.

If you want two separate connections, you can store them in separate methods and call them to create multiple sessions.

Pritam Banerjee
  • 17,953
  • 10
  • 93
  • 108