Spring utilizing Mongo implementation over JPA

Question

I am fairly new to the Spring Framework and have been having some trouble setting up the project I am currently working on. I need to be able to connect to two different databases one being MongoDB and the other being MSSQL. I am using JPA to connect to the MSSQL.

The problem that I am encountering is that it appears to be trying to make calls to the Mongo database when I want it to make calls to the MSSQL and I'm not really sure how to tell it what to read from. I have seen the posts advising to use the @Qualifier annotation to direct it to the correct implementation, but I don't think that that will work for my case.

@RestController
@RequestMapping("/software")
public class SoftwareEndpoint {



    @Autowired
    SoftwareRepository repo;    


    /**********************************************************************************
    ********************************MSSQL calls****************************************
    ***********************************************************************************/
    @RequestMapping(value="/all",method=RequestMethod.GET,produces=MediaType.APPLICATION_JSON)
    String getAllSoftware(){

        System.out.println("Here1");
        List<Software> allSoftware = (List<Software>) repo.findAll();
        System.out.println("Here2");
        //rest of method and class

Above shows a snippet of my controller class that has an instance of my SoftwareRepository. I also print to the out stream before and after the db call.

The out stream only shows "Here1", goes on to print out this line:

2016-10-04 07:35:39.810  INFO 4236 --- [nio-8080-exec-2] org.mongodb.driver.cluster               : No server chosen by ReadPreferenceServerSelector{readPreference=primary} from cluster description ClusterDescription{type=UNKNOWN, connectionMode=SINGLE, all=[ServerDescription{address=localhost:27017, type=UNKNOWN, state=CONNECTING, exception={com.mongodb.MongoSocketOpenException: Exception opening socket}, caused by {java.net.ConnectException: Connection refused: connect}}]}. Waiting for 30000 ms before timing out

and then throws an exception on timeout.

I do not have a mongo instance running locally, however there will be on where the application is being deployed, but I don't believe that this is the problem because on hitting that endpoint, it shouldn't be making a call to the Mongo database, it should be trying to reach out to MSSQL.

TLDR: How do I specify which database implementation for Spring to use for a specific repository or database call?

Answer 1

You can connect to different databases in spring based on the configuration in context.

The below code is for connecting to MySql and Mongo DB. You can substitute MySql with MSSQL provided you have the JDBC for it. Check http://jdbforms.sourceforge.net/UsersGuide/html/ch20s02.html for what the properties for JDBC connection mean.

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="mySqldataSource" /> <!-- Change the datasource to MSSQL-->
    </bean>

    <bean id="mySqldataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="removeAbandoned">
            <value>true</value>
        </property>
        <property name="removeAbandonedTimeout">
            <value>30</value>
        </property>
        <property name="driverClassName">
            <value>MSSQL_DRIVER_CLASS_NAME</value>
        </property>
        <property name="url">
            <value>MSSQL_DATABASE_URL</value>
        </property>
        <property name="username">
            <value>MSSQL_DB_USER_NAME</value>
        </property>
        <property name="password">
            <value>MSSQL_DB_PASSWORD</value>
        </property>
        <property name="maxIdle"> 
            <value>10</value>
        </property>
        <property name="maxActive"> 
            <value>10</value>
        </property>
        <property name="maxWait">
            <value>100000</value>
        </property>
        <property name="testOnBorrow">
            <value>false</value>
        </property>
        <property name="testWhileIdle">
            <value>false</value>
        </property>
        <property name="timeBetweenEvictionRunsMillis">
            <value>60000</value>
        </property>
        <property name="minEvictableIdleTimeMillis">
            <value>60000</value>
        </property>
        <property name="numTestsPerEvictionRun">
            <value>1</value>
        </property>
        <property name="defaultTransactionIsolation" value="1" />
        <property name="poolPreparedStatements" value="true" />
        <property name="maxOpenPreparedStatements" value="1" />
    </bean>

    <bean class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"></bean>

Below is for connecting to mongodb

    <mongo:db-factory dbname="mongoDbName" host="mongoServer" port="mongoPort"/>


    <bean id="mongoTemplate" class="org.springframework.data.mongodb.core.MongoTemplate">
        <constructor-arg name="mongoDbFactory" ref="mongoDbFactory" />
    </bean>
    <mongo:repositories base-package="com.test.repoPackage"/> <!-- Package containing the mongo repository interfaces -->

Now you can use the repositories provided by spring.

EDIT 1: Suppose name of config is springConfig.properties. In the above example for the properties dbname, host and port in mongo:db-factory, you would want the values to be configured in springConfig.properties. So lets name them below:

mongoServer = xxx.xx.xxx.xxx
mongoPort = 27017
mongoDb = testDb

Now the context file needs to be modified to import the springConfig.properties. this is done as below in the context file:

<bean id="propertyConfigurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer" >
        <property name="locations" >
            <list>              
                <value>classpath:/log4j.properties</value>
                <value>classpath:/springConfig.properties</value>
            </list>
        </property>
    </bean>

The bean mongo:db-factory would now look like:

<mongo:db-factory dbname="${mongoDb}" host="${mongoServer}" port="${mongoPort}"/>

Notice that the "keys" from config (dbname, host and port) are represented insde ${}. This will replace with values in config for the keys.

Answer 2

You need to have two separated config for JPA. Don't forget to disable JPA auto configuration.

@SpringBootApplication(
        exclude={
           DataSourceAutoConfiguration.class,
           DataSourceTransactionManagerAutoConfiguration.class,
           HibernateJpaAutoConfiguration.class
        }
)

Below is example for two different sql database. Could be easily adapted for your needs (when second datasource is mongo).

First one:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "sellitEntityManagerFactory",
        transactionManagerRef = "sellitTransactionManager",
        basePackages = { "co.sellit.core.api.repository.sellit" }
)
public class JpaSellitConfig {

    @Bean
    @ConfigurationProperties(prefix="spring.datasource.sellit")
    public DataSourceProperties sellitDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties(prefix="spring.hikaricp.sellit")
    public HikariConfig sellitHikariConfig() {
        HikariConfig hikariConfig = new HikariConfig();
        return hikariConfig;
    }

    @Bean
    public DataSource sellitDataSource(
            @Qualifier("sellitHikariConfig") HikariConfig sellitHikariConfig,
            @Qualifier("sellitDataSourceProperties") DataSourceProperties sellitDataSourceProperties) {

        sellitHikariConfig.setDriverClassName(sellitDataSourceProperties.getDriverClassName());
        sellitHikariConfig.setJdbcUrl(sellitDataSourceProperties.getUrl());
        sellitHikariConfig.setUsername(sellitDataSourceProperties.getUsername());
        sellitHikariConfig.setPassword(sellitDataSourceProperties.getPassword());
        sellitHikariConfig.setConnectionTestQuery("SELECT 1");

        HikariDataSource hikariDataSource = new HikariDataSource(sellitHikariConfig);
        return hikariDataSource;
    }

    @Bean
    @ConfigurationProperties(prefix="spring.jpa.sellit")
    public JpaVendorAdapter sellitJpaVendorAdapter() {
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        return jpaVendorAdapter;
    }

    @Bean
    @Autowired
    public EntityManagerFactory sellitEntityManagerFactory(
            @Qualifier("sellitDataSource") DataSource sellitDataSource,
            @Qualifier("sellitJpaVendorAdapter") JpaVendorAdapter sellitJpaVendorAdapter) {

        LocalContainerEntityManagerFactoryBean lemf = new LocalContainerEntityManagerFactoryBean();
        lemf.setDataSource(sellitDataSource);
        lemf.setJpaVendorAdapter(sellitJpaVendorAdapter);
        lemf.setPackagesToScan("co.sellit.core.api.entity.sellit");
        lemf.setPersistenceUnitName("sellitPersistenceUnit");
        lemf.afterPropertiesSet();
        return lemf.getObject();
    }

    @Bean
    @Autowired
    public EntityManager sellitDataSourceEntityManager(
            @Qualifier("sellitEntityManagerFactory") EntityManagerFactory sellitEntityManagerFactory) {

        return sellitEntityManagerFactory.createEntityManager();
    }

    @Bean
    @Autowired
    @Qualifier("sellitTransactionManager")
    public PlatformTransactionManager sellitTransactionManager(
            @Qualifier("sellitEntityManagerFactory") EntityManagerFactory sellitEntityManagerFactory) {

        return new JpaTransactionManager(sellitEntityManagerFactory);
    }
}

Second one:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
       entityManagerFactoryRef = "ofEntityManagerFactory",
       transactionManagerRef = "ofTransactionManager",
       basePackages = { "co.sellit.core.api.repository.openfire" }
)
public class JpaOpenfireConfig {

   @Bean
   @ConfigurationProperties(prefix="spring.datasource.openfire")
   public DataSourceProperties ofDataSourceProperties() {
       return new DataSourceProperties();
   }

   @Bean
   @ConfigurationProperties(prefix="spring.hikaricp.openfire")
   public HikariConfig ofHikariConfig() {
       HikariConfig hikariConfig = new HikariConfig();
       return hikariConfig;
   }

   @Bean
   public DataSource ofDataSource(
           @Qualifier("ofHikariConfig") HikariConfig ofHikariConfig,
           @Qualifier("ofDataSourceProperties") DataSourceProperties ofDataSourceProperties) {

       ofHikariConfig.setDriverClassName(ofDataSourceProperties.getDriverClassName());
       ofHikariConfig.setJdbcUrl(ofDataSourceProperties.getUrl());
       ofHikariConfig.setUsername(ofDataSourceProperties.getUsername());
       ofHikariConfig.setPassword(ofDataSourceProperties.getPassword());
       ofHikariConfig.setConnectionTestQuery("SELECT 1");

       HikariDataSource hikariDataSource = new HikariDataSource(ofHikariConfig);
       return hikariDataSource;
   }

   @Bean
   @ConfigurationProperties(prefix="spring.jpa.openfire")
   public JpaVendorAdapter ofJpaVendorAdapter() {
       HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
       return jpaVendorAdapter;
   }

   @Bean
   @Autowired
   public EntityManagerFactory ofEntityManagerFactory(
           @Qualifier("ofDataSource") DataSource ofDataSource,
           @Qualifier("ofJpaVendorAdapter") JpaVendorAdapter ofJpaVendorAdapter) {

       LocalContainerEntityManagerFactoryBean lemf = new LocalContainerEntityManagerFactoryBean();
       lemf.setDataSource(ofDataSource);
       lemf.setJpaVendorAdapter(ofJpaVendorAdapter);
       lemf.setPackagesToScan("co.sellit.core.api.entity.openfire");
       lemf.setPersistenceUnitName("ofPersistenceUnit");
       lemf.afterPropertiesSet();
       return lemf.getObject();
   }

   @Bean
   @Autowired
   public EntityManager ofDataSourceEntityManager(
           @Qualifier("ofEntityManagerFactory") EntityManagerFactory ofEntityManagerFactory) {

       return ofEntityManagerFactory.createEntityManager();
   }

   @Bean
   @Autowired
   @Qualifier("ofTransactionManager")
   public PlatformTransactionManager ofTransactionManager(
           @Qualifier("ofEntityManagerFactory") EntityManagerFactory ofEntityManagerFactory) {

       return new JpaTransactionManager(ofEntityManagerFactory);
   }


}

So repositories from package co.sellit.core.api.repository.sellit will use sellit db

But repositories from package co.sellit.core.api.repository.openfire will use openfire database.

UPDATE (xml config version)

<bean id="openfireDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/openfire?characterEncoding=UTF-8" />
</bean>

<bean id="sellitDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/sellit?characterEncoding=UTF-8" />
</bean>

<bean id="openfireSessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="openfireDataSource" />
    <property name="packagesToScan" value="co.sellit.core.api.repository.openfire" />
    <property name="hibernateProperties">
        <props>
           ...
        </props>
    </property>
</bean>

<bean id="sellitSessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="sellitDataSource" />
    <property name="packagesToScan" value="co.sellit.core.api.repository.sellit" />
    <property name="hibernateProperties">
        <props>
            ...
        </props>
    </property>
</bean>

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

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

<tx:annotation-driven transaction-manager="openfireTxnManager" />
<tx:annotation-driven transaction-manager="sellitTxnManager" />