hibernate Configuration class with multiple DataSource

Question

Our application need to deal with multiple databases. We tried to configure multiple data sources through Hibernate configuration and added two configurations one for database 1 and second for database 2. This configuration fails with following Exception

WARNING: Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'mainController': Unsatisfied dependency expressed through field 'dataDAO'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'dataDAO': Unsatisfied dependency expressed through field 'sessionFactory'; nested exception is org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'org.hibernate.SessionFactory' available: expected single matching bean but found 2: sessionFactory1,sessionFactory2
Nov 29, 2019 5:08:11 PM org.springframework.web.context.ContextLoader initWebApplicationContext
SEVERE: Context initialization failed
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'mainController': Unsatisfied dependency expressed through field 'dataDAO'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'dataDAO': Unsatisfied dependency expressed through field 'sessionFactory'; nested exception is org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'org.hibernate.SessionFactory' available: expected single matching bean but found 2: sessionFactory1,sessionFactory2
    at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:596)

This is my Configuration class :

package com.pack1.config;

import java.beans.PropertyVetoException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.annotation.PropertySources;
import org.springframework.core.env.Environment;
import org.springframework.orm.hibernate5.HibernateTransactionManager;
import org.springframework.orm.hibernate5.LocalSessionFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import org.springframework.web.servlet.view.InternalResourceViewResolver;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import com.pack1.routing.MyRoutingDataSource;

@Configuration
@ComponentScan("com.pack1")
@EnableWebMvc
@EnableTransactionManagement

// Load to Environment
@PropertySources({@PropertySource("classpath:ds/datasource-cfg.properties")})

public class ApplicationContextConfig implements WebMvcConfigurer
{


    // The Environment class serves as the property holder
    // and stores all the properties loaded by the @PropertySource
    @Autowired
    private Environment env;

    private Logger logger = Logger.getLogger(getClass().getName());

    @Bean(name = "viewResolver")
    public InternalResourceViewResolver getViewResolver() {
        InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();

        viewResolver.setPrefix("/WEB-INF/pages/");
        viewResolver.setSuffix(".jsp");

        return viewResolver;
    }

    // Returns Routing DataSource (MyRoutingDataSource)
    @Autowired
    @Bean(name = "dataSource")
    public DataSource getDataSource(DataSource dataSource1, DataSource dataSource2) {

        System.out.println("## Create DataSource from dataSource1 & dataSource2");

        MyRoutingDataSource dataSource = new MyRoutingDataSource();

        Map<Object, Object> dsMap = new HashMap<Object, Object>();
        dsMap.put("PUBLISHER_DS", dataSource1);
        dsMap.put("ADVERTISER_DS", dataSource2);

        dataSource.setTargetDataSources(dsMap);

        return dataSource;
    }

    @Bean(name = "dataSource1")
    public DataSource getDataSource1() throws SQLException, PropertyVetoException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();

        // See: datasouce-cfg.properties
        dataSource.setDriverClass(env.getProperty("ds.database-driver1"));
        dataSource.setJdbcUrl(env.getProperty("ds.url1"));
        dataSource.setUser(env.getProperty("ds.username1"));
        dataSource.setPassword(env.getProperty("ds.password1"));

        System.out.println("## getDataSource1: " + dataSource);

        return dataSource;
    }

    private Properties getHibernateProperties1() 
    {
        // set hibernate properties
        Properties props = new Properties();

        props.setProperty("ds.hibernate.dialect1", env.getProperty("ds.hibernate.dialect1"));
        props.setProperty("ds.hibernate.show_sql1", env.getProperty("ds.hibernate.show_sql1"));

        return props;
    }

 // need a helper method 
    // read environment property and convert to int

    private int getIntProperty1(String propName) {

        String propVal = env.getProperty(propName);

        // now convert to int
        int intPropVal = Integer.parseInt(propVal);

        return intPropVal;
    }

    @Bean
    public LocalSessionFactoryBean sessionFactory1() throws SQLException, PropertyVetoException{

        // create session factorys
        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();

        // set the properties
        sessionFactory.setDataSource(getDataSource1());
        sessionFactory.setPackagesToScan(env.getProperty("ds.hibernate.packagesToScan1"));
        sessionFactory.setHibernateProperties(getHibernateProperties1());

        return sessionFactory;
    }

    @Autowired
    @Bean(name = "transactionManager")
    public HibernateTransactionManager getTransactionManager1(SessionFactory sessionFactory1) {

        // setup transaction manager based on session factory 
        HibernateTransactionManager txManager = new HibernateTransactionManager();

        txManager.setSessionFactory(sessionFactory1);

        return txManager;
    }


    @Bean(name = "dataSource2")
    public DataSource getDataSource2() throws SQLException, PropertyVetoException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();

        // See: datasouce-cfg.properties
        dataSource.setDriverClass(env.getProperty("ds.database-driver2"));
        dataSource.setJdbcUrl(env.getProperty("ds.url2"));
        dataSource.setUser(env.getProperty("ds.username2"));
        dataSource.setPassword(env.getProperty("ds.password2"));

        System.out.println("## getDataSource2: " + dataSource);

        return dataSource;
    }

    private Properties getHibernateProperties2() 
    {
        // set hibernate properties
        Properties props = new Properties();

        props.setProperty("ds.hibernate.dialect2", env.getProperty("ds.hibernate.dialect2"));
        props.setProperty("ds.hibernate.show_sql2", env.getProperty("ds.hibernate.show_sql2"));

        return props;
    }

 // need a helper method 
    // read environment property and convert to int

    private int getIntProperty2(String propName) {

        String propVal = env.getProperty(propName);

        // now convert to int
        int intPropVal = Integer.parseInt(propVal);

        return intPropVal;
    }


    @Bean
    public LocalSessionFactoryBean sessionFactory2() throws SQLException, PropertyVetoException{

        // create session factorys
        LocalSessionFactoryBean sessionFactory1 = new LocalSessionFactoryBean();

        // set the properties
        sessionFactory1.setDataSource(getDataSource2());
        sessionFactory1.setPackagesToScan(env.getProperty("ds.hibernate.packagesToScan2"));
        sessionFactory1.setHibernateProperties(getHibernateProperties2());

        return sessionFactory1;
    }


    @Autowired
    @Bean(name = "transactionManager")
    public HibernateTransactionManager getTransactionManage2(SessionFactory sessionFactory1) {

        // setup transaction manager based on session factory 
        HibernateTransactionManager txManager = new HibernateTransactionManager();

        txManager.setSessionFactory(sessionFactory1);

        return txManager;
    }

}

Dao class :

package com.pack1.dao;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

@Repository
public class DataDAO extends JdbcDaoSupport {


     @Autowired
     @Qualifier(value="t1")
     private SessionFactory firstDBSessionFactory;


       public List<Publisher> queryPublishers() {

            // get the current hibernate session
            Session currentSession = firstDBSessionFactory.getCurrentSession();

            // create a query 
            Query<Publisher> theQuery = 
                    currentSession.createQuery("from Publisher", Publisher.class);

            // execute query and get result list
            List<Publisher> customers = theQuery.getResultList();

            // return the results
            return customers;

            return customers;
          }


        @Autowired
        @Qualifier(value="t2")
        private SessionFactory secondDBSessionFactory;


        @Transactional(propagation= Propagation.REQUIRED, readOnly=true, value="t2")
        public List<Advertiser> queryAdvertisers() {

            // get the current hibernate session
            Session currentSession = secondDBSessionFactory.getCurrentSession();

            // create a query 
            Query<Advertiser> theQuery = 
                    currentSession.createQuery("from Advertiser", Advertiser.class);

            // execute query and get result list
            List<Advertiser> customers = theQuery.getResultList();

            // return the results
            return customers;

        }

        }

        public List<String> queryDashboard() {

            return null;
        }
}

Properties file :

# DataSource (PUBLISHER System).

ds.database-driver1=com.mysql.jdbc.Driver
ds.url1=jdbc:mysql://127.0.0.1:3306/pan_db
ds.username1=hbstudent
ds.password1=hbstudent


# DataSource (ADVERTISER System).

ds.database-driver2=com.mysql.jdbc.Driver
ds.url2=jdbc:mysql://127.0.0.1:3306/voter_db
ds.username2=hbstudent
ds.password2=hbstudent


#
# Hibernate properties1
#
ds.hibernate.dialect1=org.hibernate.dialect.MySQLDialect
ds.hibernate.show_sql1=true
ds.hibernate.hbm2ddl.auto1=update


#
# Hibernate properties2
#
ds.hibernate.dialect2=org.hibernate.dialect.MySQLDialect
ds.hibernate.show_sql2=true
ds.hibernate.hbm2ddl.auto2=update

SpringWebAppInitializer class :

package com.pack1.config;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRegistration.Dynamic;

import org.springframework.web.WebApplicationInitializer;
import org.springframework.web.context.support.AnnotationConfigWebApplicationContext;
import org.springframework.web.filter.CharacterEncodingFilter;
import org.springframework.web.servlet.DispatcherServlet;

public class SpringWebAppInitializer implements WebApplicationInitializer
{

    @Override
    public void onStartup(ServletContext servletContext) throws ServletException {


            AnnotationConfigWebApplicationContext ctx = new AnnotationConfigWebApplicationContext();
            ctx.register(ApplicationContextConfig.class);
            ctx.setServletContext(servletContext); // ②

            Dynamic servlet = servletContext.addServlet("dispatcher",new DispatcherServlet(ctx)); // ③
            servlet.addMapping("/*");
            servlet.setLoadOnStartup(1);

            servletContext.addFilter("name", CharacterEncodingFilter.class)
            .addMappingForUrlPatterns(null, false, "/*");

        }



    /*@Override
    public void onStartup(ServletContext servletContext) throws ServletException
    {
        AnnotationConfigWebApplicationContext appContext = new AnnotationConfigWebApplicationContext();
        appContext.register(ApplicationContextConfig.class);

        ServletRegistration.Dynamic dispatcher = servletContext.addServlet("SpringDispatcher",
                new DispatcherServlet(appContext));
        dispatcher.setLoadOnStartup(1);
        dispatcher.addMapping("/");       
        // 
        dispatcher.setInitParameter("contextClass", appContext.getClass().getName());

        servletContext.addListener(new ContextLoaderListener(appContext));

        // UTF8 Charactor Filter.
        FilterRegistration.Dynamic fr = servletContext.addFilter("encodingFilter", CharacterEncodingFilter.class);

        fr.setInitParameter("encoding", "UTF-8");
        fr.setInitParameter("forceEncoding", "true");
        fr.addMappingForUrlPatterns(null, true, "/*");
    }*/

    }

WebMvcConfigurerAdapter Class :

package com.pack1.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;

import com.pack1.intercepter.DataSourceIntercepter;


@Configuration
@EnableWebMvc
public class WebMvcConfig extends WebMvcConfigurerAdapter{

    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) 
    {

        // Default..
    }

    @Override
    public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
        configurer.enable();
    }

    //
    @Override
    public void addInterceptors(InterceptorRegistry registry) {

        registry.addInterceptor(new DataSourceIntercepter())//
                .addPathPatterns("/publisher/*", "/advertiser/*");
    }
}

following exception is :

INFO: Server startup in [8,376] milliseconds
Dec 05, 2019 12:05:18 PM org.springframework.web.servlet.DispatcherServlet noHandlerFound
WARNING: No mapping for GET /multiple_DB1/
Dec 05, 2019 12:05:22 PM org.springframework.web.servlet.DispatcherServlet noHandlerFound
WARNING: No mapping for GET /multiple_DB1/
Dec 05, 2019 12:05:23 PM org.springframework.web.servlet.DispatcherServlet noHandlerFound
WARNING: No mapping for GET /multiple_DB1/

I am new to using multiple Datasource in Spring MVC. Please help me Coding in Hibernate Configuration class .

Can you add dataDAO class as well? Issue seems to be different here. — Nawnit Sen
– Nawnit Sen, Commented Nov 30, 2019 at 14:34
Yes, you can, I have updated the repo, please check the controller class how to use sessionFactory — YogendraR
– YogendraR, Commented Dec 4, 2019 at 11:48
I have updated the answer and repo as well, please check, there is one example using jdbcTemplate and one sessionFactory — YogendraR
– YogendraR, Commented Dec 4, 2019 at 12:18
That is completely different error, please start a new thread for that. If my solution worked for you, please accept the answer. — YogendraR
– YogendraR, Commented Dec 4, 2019 at 13:39

Manuel Pap · Accepted Answer · 2019-11-30 14:02:34Z

You will need multiple configuration files for each database and one of them needs to be declared as Primary. I am providing you some examples to have an idea what you need.

PrimaryDB is declared the primary database for the project. Primary means that the JPA by default will be executed on the primary database.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "primaryEntityManagerFactory",
    basePackages = {"com.example.repositories.primary"})
public class PrimaryDBConfig {

  @Primary
  @Bean(name = "primaryDatasource")
  @ConfigurationProperties(prefix = "primary.datasource")
  public DataSource dataSource() {
    return DataSourceBuilder.create().build();
  }

  @Primary
  @Bean(name = "primaryEntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(
      EntityManagerFactoryBuilder builder, @Qualifier("primaryDatasource") DataSource dataSource) {
    return builder.dataSource(dataSource).packages("com.example.entities.primary")
        .persistenceUnit("primary").build();
  }

  @Primary
  @Bean(name = "primaryTransactionManager")
  public PlatformTransactionManager transactionManager(
      @Qualifier("primaryEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);

  }
}

A second configuration file about our second database FooDB

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "fooEntityManagerFactory",
    basePackages = {"com.example.repositories.foo"})
public class FooDBConfig {

  @Bean(name = "fooDatasource")
  @ConfigurationProperties(prefix = "foo.datasource")
  public DataSource dataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean(name = "fooEntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(
      EntityManagerFactoryBuilder builder, @Qualifier("fooDatasource") DataSource dataSource) {
    return builder.dataSource(dataSource).packages("com.example.entities.crf")
        .persistenceUnit("foo").build();
  }

  @Bean(name = "fooTransactionManager")
  public PlatformTransactionManager transactionManager(
      @Qualifier("fooEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }
}

In addition, you will need to add the following on application.properties, because Spring will listen to the default data source configuration.

primary.datasource.url= URL
primary.datasource.username=username
primary.datasource.password=password


foo.datasource.url=URL
foo.datasource.username=username
foo.datasource.password=password

Hello Brother, I am new to Spring MVC Can you Convert JPA to Hibernate Properties and show me Imports also.

YogendraR · Accepted Answer · 2019-12-04 12:18:13Z

Find below Configuration file

package com.yogendra.configuration;

import java.util.Properties;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.hibernate5.HibernateTransactionManager;
import org.springframework.orm.hibernate5.LocalSessionFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@PropertySource("classpath:application.properties")
public class DatabaseConfiguration {

@Autowired
private Environment env;

@Bean("datasource1")
public DataSource dataSource1() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(env.getProperty("app1.db.driverClassName"));
    dataSource.setUrl(env.getProperty("app1.db.URL"));
    dataSource.setUsername(env.getProperty("app1.db.username"));
    dataSource.setPassword(env.getProperty("app1.db.password"));
    return dataSource;
}

@Bean("datasource2")
public DataSource dataSource2() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(env.getProperty("app2.db.driverClassName"));
    dataSource.setUrl(env.getProperty("app2.db.URL"));
    dataSource.setUsername(env.getProperty("app2.db.username"));
    dataSource.setPassword(env.getProperty("app2.db.password"));
    return dataSource;
}

@Bean("jdbc1")
public JdbcTemplate jdbcTemplate1() {
    return new JdbcTemplate(dataSource1());
}

@Bean("jdbc2")
public JdbcTemplate jdbcTemplate2() {
    return new JdbcTemplate(dataSource2());
}

@Bean("session1")
public LocalSessionFactoryBean sessionFactory1() {
    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setDataSource(dataSource1());
    sessionFactory.setPackagesToScan(new String[] { "com.yogendra" });
    sessionFactory.setHibernateProperties(hibernateProperties());
    return sessionFactory;
}

@Bean("session2")
public LocalSessionFactoryBean sessionFactory2() {
    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setDataSource(dataSource2());
    sessionFactory.setPackagesToScan(new String[] { "com.yogendra" });
    sessionFactory.setHibernateProperties(hibernateProperties());
    return sessionFactory;
}

@Bean("tx1")
public HibernateTransactionManager transactionManager1() {
    HibernateTransactionManager transactionManager = new HibernateTransactionManager();
    transactionManager.setSessionFactory(sessionFactory1().getObject());
    return transactionManager;
}

@Bean("tx2")
public HibernateTransactionManager transactionManager2() {
    HibernateTransactionManager transactionManager = new HibernateTransactionManager();
    transactionManager.setSessionFactory(sessionFactory2().getObject());
    return transactionManager;
}

@SuppressWarnings("serial")
private Properties hibernateProperties() {
    return new Properties() {
        {
            setProperty("hibernate.hbm2ddl.auto", "none");
            setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
            setProperty("hibernate.show_sql", "true");
        }
    };
 }

 }

And here is properties files

 app1.db.driverClassName=org.postgresql.Driver
 app1.db.URL=jdbc:postgresql://localhost/test_db
 app1.db.username=postgres
 app1.db.password=postgres


 app2.db.driverClassName=org.postgresql.Driver
 app2.db.URL=jdbc:postgresql://localhost/test_db2
 app2.db.username=postgres
 app2.db.password=postgres

You can find working code here

See how you can use the sessionFactory

   @Autowired
   @Qualifier("session2")
   SessionFactory sessionFactory;

   @GetMapping(value = "/test2")
   @Transactional("tx2")
   public void test2() {
    List<String> cities = sessionFactory.getCurrentSession().createSQLQuery("select city from address")
            .list();
    System.out.println("------------ citties ------------ " + cities);
}

Collectives™ on Stack Overflow

hibernate Configuration class with multiple DataSource

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related