How to connect Two Database MySQL and MongoDB in the same project? Is it possible?

hibernate, java, mongodb, spring-data, spring-mvc

Solution

I'm not sure whether this is the best solution. But, it worked for me. If you have two relational databases using `Jpa` module, then I would suggest you to create `entity` and `transaction` manager beans to read each datasource config. Refer the below link for the above use case.

springboot always read data from primary datasource

As you wish to have a combination of `SQL` and `NoSQL`, I would create `entity` and `transcation` manager beans for `MySQL` database as it works well with `Jpa`. And leave as-is configuration for `Mongo`(configs read directly from `application.properties`).

`MySQLConfiguration` datasource config class :

@Configuration
@PropertySource("classpath:persistence-multiple-db.properties")
@EnableJpaRepositories(basePackages = "com.springdata.dao.mysql", entityManagerFactoryRef = "mysqlEntityManager", transactionManagerRef = "mysqlTransactionManager")
public class MySQLConfiguration {

    @Autowired
    private Environment env;

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean mysqlEntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(myMySQLDataSource());
        em.setPackagesToScan(new String[] { "com.springdata.models" });

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<String, Object>();
        properties.put("hibernate.dialect", env.getProperty("hibernate.dialect"));
        em.setJpaPropertyMap(properties);

        return em;
    }

    @Bean
    @Primary
    public DataSource myMySQLDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.mysql.jdbc.driverClassName"));
        dataSource.setUrl(env.getProperty("spring.mysql.jdbc.url"));
        dataSource.setUsername(env.getProperty("spring.mysql.user"));
        dataSource.setPassword(env.getProperty("spring.mysql.pass"));

        return dataSource;
    }

    @Bean
    @Primary
    public PlatformTransactionManager mysqlTransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(mysqlEntityManager().getObject());
        return transactionManager;
    }

Above datasource config params are read from `classpath:persistence-multiple-db.properties` file in the classpath.

# mysql jdbc connections
spring.mysql.jdbc.driverClassName=com.mysql.jdbc.Driver
spring.mysql.jdbc.url=jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false
spring.mysql.user=root
spring.mysql.pass=password1

# hibernate.X
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

The above configuration, should be suffice to deal `MySQL` datasource. To have mongo configuration in your project, add the below lines to `application.properties`.

# mongo configuration
spring.data.mongodb.uri=mongodb://localhost
spring.data.mongodb.database=test

Springboot will automatically create the necessary mongo datasource beans and keeps them readily available for spring container to use.

Now, create repository interfaces for both MySQL and Mongo datasources.

`MyMongoRepository` interface:

@Transactional
public interface MyMongoRepository extends MongoRepository<Users, String>{

}

`MySQLRepository` interface:

@Transactional
public interface MySQLRepository extends JpaRepository<Users, String>{

}

`Users` pojo class :

@Entity
@Table(name = "users")
@Document(collection="users")
@Data
public class Users {

    @Id
    @javax.persistence.Id
    private String id;
    private String name;
    private Integer age;

}

Have added below annotations to enable mongorepositoreis and for component scan to springboot main class.

@SpringBootApplication
@ComponentScan(basePackages = { "com.springdata" })
@EnableMongoRepositories(basePackages={"com.springdata.dao.mongo"})
public class SpringbootmysqlmongoApplication {

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

Finally, some code to test.

`MyRepositoryImpl` class:

@Service
public class MyRepositoryImpl {

    @Autowired
    private MyMongoRepository myMongoRepository;

    @Autowired
    private MySQLRepository mySQLRepository;

    @PostConstruct
    public void extractUsers(){
        myMongoRepository.findAll().forEach((user) -> System.out.println("user name from mongo is : "+user.getName()));
        mySQLRepository.findAll().forEach((user) -> System.out.println("User name from mysql is : "+user.getName()));
    }

}

Have created `users` table in `mysql test` database and `users` collection in `mongo test` database.

Lastly, have uploaded my code to git repository.

https://github.com/harshavmb/springbootmysqlmongo

Problem

Original source