Problems with connecting Hibernate to two databases

Question

I'm having problems with getting my Java Spring + Hibernate program work with two different databases from two different servers.

I have two DAO-s and two DAO implementation files for different databases. Implementation files looks like this:

ErpDaoImplementation:

@Repository("erpDao")
@Transactional(value="txManager2")
public class ErpDaoImplementation implements ErpDao {

@Autowired
private final SessionFactory sessionFactory2;

@Autowired
public ErpDaoImplementation(@Qualifier(value="sessionFactory2") final SessionFactory sessionFactory2) {
    this.sessionFactory2 = sessionFactory2;
}

public Session getSession() {
    return sessionFactory2.getCurrentSession();
}

@Override
public ArrayList<ErpOrder> getOrder() {
   Criteria criteria = getSession().createCriteria(ErpOrder.class);
    return (ArrayList<ErpOrder>) criteria.list();
  }
}

PortalDaoImplementation:

@Repository("portalDao")
@Transactional(value="txManager1")
public class PortalDaoImplementation implements PortalDao {

@Autowired
private final SessionFactory sessionFactory1;

@Autowired
public PortalDaoImplementation(@Qualifier(value="sessionFactory1") final SessionFactory sessionFactory1) {
    this.sessionFactory1 = sessionFactory1;
}

public Session getSession() {
    return sessionFactory1.getCurrentSession();
}

@Override
public void saveEmployee(NewEmployeeBasic newEmployeeBasic) {
    getSession().save(newEmployeeBasic);
}

@SuppressWarnings("unchecked")
@Override
public ArrayList<BasicInfo> findAllEmployees() {
    Criteria criteria = getSession().createCriteria(BasicInfo.class).addOrder(Order.asc("name"));
    return (ArrayList<BasicInfo>) criteria.list();
}
}

And i have two option files, having data about both databases. Any database from from first options file will work but other not.

Also this is my hibernate configutarion file:

@Configuration
@EnableTransactionManagement
@ComponentScan({"com.alti.hrportal.configuration"})
public class HibernateConfiguration {

private static final PropertiesLoader propertiesLoader1 = new PropertiesLoader();
Properties propHrPortal = propertiesLoader1.load("application.properties");

private static final PropertiesLoader propertiesLoader2 = new PropertiesLoader();
Properties propErp = propertiesLoader2.load("applicationErp.properties");


@Bean
public LocalSessionFactoryBean sessionFactory1() {
    LocalSessionFactoryBean sessionFactory1 = new LocalSessionFactoryBean();
    sessionFactory1.setDataSource(dataSource1());
    sessionFactory1.setPackagesToScan(new String[]{"com.alti.hrportal.model"});
    sessionFactory1.setHibernateProperties(hibernateProperties1());
    return sessionFactory1;
}

@Bean
@Primary
public LocalSessionFactoryBean sessionFactory2() {
    LocalSessionFactoryBean sessionFactory2 = new LocalSessionFactoryBean();
    sessionFactory2.setDataSource(dataSource2());
    sessionFactory2.setPackagesToScan(new String[]{"com.alti.hrportal.model"});
    sessionFactory2.setHibernateProperties(hibernateProperties2());
    return sessionFactory2;
}

@Bean
public DataSource dataSource1() {
    DriverManagerDataSource dataSource1 = new DriverManagerDataSource();
    dataSource1.setDriverClassName(propHrPortal.getProperty("jdbc.driverClassName"));
    dataSource1.setUrl(propHrPortal.getProperty("jdbc.url"));
    dataSource1.setUsername(propHrPortal.getProperty("jdbc.username"));
    dataSource1.setPassword(propHrPortal.getProperty("jdbc.password"));
    return dataSource1;
}

@Bean
public DataSource dataSource2() {
    DriverManagerDataSource dataSource2 = new DriverManagerDataSource();
    dataSource2.setDriverClassName(propErp.getProperty("jdbc.driverClassName"));
    dataSource2.setUrl(propErp.getProperty("jdbc.url"));
    dataSource2.setUsername(propErp.getProperty("jdbc.username"));
    dataSource2.setPassword(propErp.getProperty("jdbc.password"));
    return dataSource2;
}


private Properties hibernateProperties1() {
    Properties properties1 = new Properties();
    properties1.put("hibernate.dialect", propHrPortal.getProperty("hibernate.dialect"));
    properties1.put("hibernate.show_sql", propHrPortal.getProperty("hibernate.show_sql"));
    properties1.put("hibernate.format_sql", propHrPortal.getProperty("hibernate.format_sql"));
    return properties1;
}

private Properties hibernateProperties2() {
    Properties properties2 = new Properties();
    properties2.put("hibernate.dialect", propErp.getProperty("hibernate.dialect"));
    properties2.put("hibernate.show_sql", propErp.getProperty("hibernate.show_sql"));
    properties2.put("hibernate.format_sql", propErp.getProperty("hibernate.format_sql"));
    return properties2;
}

@Bean
@Autowired
@Qualifier(value = "txManager1")
public HibernateTransactionManager transactionManager1(SessionFactory s) {
    HibernateTransactionManager txManager1 = new HibernateTransactionManager();
    txManager1.setSessionFactory(s);
    return txManager1;
}

@Bean
@Autowired
@Qualifier(value = "txManager2")
public HibernateTransactionManager transactionManager2(SessionFactory s) {
    HibernateTransactionManager txManager2 = new HibernateTransactionManager();
    txManager2.setSessionFactory(s);
    return txManager2;
}
}

I can't see what i am doing wrong. Please help! :)

Answer 1

Your configuration and setup is flawed in multiple ways. First you have both @Autowired on the fields and constructors, they are interfering with each other. Due to the @Primary and these annotations basically only a single SessionFactory is being used. To fix remove @Autowired from your SessionFactory fields.

Next you need 2 HibernateTransactionManagers instead of a single one, yuo need one for each SessionFactory and you need to specify in your @Transactional annotation which one to use.

@Repository("erpDao")
@Transactional(value="txManager2")
public class ErpDaoImplementation implements ErpDao {

    private final SessionFactory sessionFactory2;

    @Autowired
    public ErpDaoImplementation(@Qualifier(value="sessionFactory2") final SessionFactory sessionFactory2) {
        this.sessionFactory2 = sessionFactory2;
    }

Finally due the the @Primary in case of conflict this one will always be the one used. You want to explicitly specify which one to use.

@Configuration
@EnableTransactionManagement
@ComponentScan({"com.alti.hrportal.configuration"})
public class HibernateConfiguration {

    private static final PropertiesLoader propertiesLoader1 = new PropertiesLoader();
    Properties propHrPortal = propertiesLoader1.load("application.properties");

    private static final PropertiesLoader propertiesLoader2 = new PropertiesLoader();
    Properties propErp = propertiesLoader2.load("applicationErp.properties");

    @Bean
    public LocalSessionFactoryBean sessionFactory2() {
        LocalSessionFactoryBean sessionFactory2 = new LocalSessionFactoryBean();
        sessionFactory2.setDataSource(dataSource2());
        sessionFactory2.setPackagesToScan(new String[]{"com.alti.hrportal.model"});
        sessionFactory2.setHibernateProperties(hibernateProperties2());
        return sessionFactory2;
    }


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

    @Bean(name="txManager2")
    public HibernateTransactionManager transactionManager2() {
        HibernateTransactionManager txManager2 = new HibernateTransactionManager();
        txManager2.setSessionFactory(sessionFactory2().getObject());
        return txManager2;
    }
    // Omitted other not modified configuration.
}