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

Question

Currently I'm using Hibernate(MySQL) with Spring, the configuration is running fine for me, but once I configured another configuration mongo-config.xml file and trying to run a test case with mongodb it's showing Error creating bean with name .... from first configuration.

Below is my mongo-config.xml

<context:annotation-config />
<context:component-scan base-package="com.test.mongo" />
<context:property-placeholder location="classpath:mongo-dao.properties" />

<bean id="mongoTemplate" class="org.springframework.data.mongodb.core.MongoTemplate">
    <constructor-arg name="mongoDbFactory" ref="mongoDbFactory" />

</bean>
<bean id="mongoDbFactory" class="org.springframework.data.mongodb.core.MongoFactoryBean">
    <property name="driverClassName" value="${spring.datasource.driverClassName}" />
    <property name="host" value="${spring.data.mongodb.host}" />
    <property name="port" value="${spring.data.mongodb.port}" />
    <property name="databaseName" value="${spring.data.mongodb.database}" />

and my first configuration for hibernate is looks like something

<context:component-scan base-package="com.hb.dao" />
<tx:annotation-driven transaction-manager="transactionManager" />

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${db.jdbc.driverClassName}" />
    <property name="url" value="${db.jdbc.url}" />
    <property name="username" value="${db.jdbc.username}" />
    <property name="password" value="${db.jdbc.password}" />
</bean>

<bean id="sessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan">
        <list>
            <value>com.hb..dao.domain.entity</value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.show_sql">${hibernate.show_sql:false}</prop>
            <prop key="hibernate.format_sql">${hibernate.format_sql:false}</prop>
        </props>
    </property>
</bean>

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

And the stack trace is

java.lang.IllegalStateException: Failed to load ApplicationContext
at org.springframework.test.context.CacheAwareContextLoaderDelegate.loadContext(CacheAwareContextLoaderDelegate.java:99)
at org.springframework.test.context.DefaultTestContext.getApplicationContext(DefaultTestContext.java:101)
at org.springframework.test.context.support.DependencyInjectionTestExecutionListener.injectDependencies(DependencyInjectionTestExecutionListener.java:109)
at org.springframework.test.context.support.DependencyInjectionTestExecutionListener.prepareTestInstance(DependencyInjectionTestExecutionListener.java:75)
at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:331)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:213)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:290)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'accessProfileDaoImpl': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private org.hibernate.SessionFactory com.soe.dao.AbstractDao.sessionFactory; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [org.hibernate.SessionFactory] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations:

Here is my test Class-

    public class MongoQuestionsTest extends BaseDaoMongoTest{   
        private static final Logger logger = LogManager.getLogger(MongoQuestionsTest.class);

        @Autowired
        private MongoTestDao mongoTestDaoImpl;
        @Test
        public void saveQuestions(){
            MongoQuestions mongoQuestions = new MongoQuestions();
            mongoQuestions.setUsername("Hi");
            mongoQuestions.setPassword("Hello");
            mongoTestDaoImpl.save(mongoQuestions);
            logger.debug("Mongo User Set with id " + mongoQuestions.getId());
        }
    and **BaseDaoMongoTest**---

    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations={"classpath:/mongo-config-test.xml"})
    public class BaseDaoMongoTest {
    }

And in MongoTestDaoImpl class I just Auto-wired MongoTemplate and calling save() method that's it.

Answer 1

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