CODEWITHSUNDEEP
springspring-mvcjpaspring-bootspring-data-jpa
lesnar
lesnar

Reputation: 2480

Connection to two different databases in Spring JPA fails

I am trying to use 2 different data sources/databases in my application but so far I am not able to connect to them.

FirstDb Config file 

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "xx.xx.xx.crud.repository.running", entityManagerFactoryRef = "dummyTestEntityManagerFactory", transactionManagerRef = "transactionManagerOne")
@PropertySource("classpath:application.properties")
public class dummyTestDbConfig {

    @Value("${spring.datasourcedummyTestraid.driver-class-name}")
    String driverClassName = "";
    @Value("${spring.datasourcedummyTestraid.url}")
    String url = "";
    @Value("${spring.datasourcedummyTestraid.username}")
    String userName = "";
    @Value("${spring.datasourcedummyTestraid.password}")
    String password = "";
    @Autowired
    @Qualifier("jpadummyTestVendorApapter")
    JpaVendorAdapter jpadummyTestVendorApapter;

    @Bean(name = "dummyTestDataSource")
    @Primary
    public DataSource dummyTestDataSource() {
        return DataSourceBuilder.create().url(url)
                .driverClassName(driverClassName).username(userName)
                .password(password).build();
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean dummyTestEntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        factoryBean.setDataSource(dummyTestDataSource());
        factoryBean.setJpaVendorAdapter(jpadummyTestVendorApapter);
        factoryBean.setPackagesToScan(R.dummyTestDB_PACKAGE);
        factoryBean.setPersistenceUnitName("dummyTestPersistenceUnit");
        factoryBean.afterPropertiesSet();

        return factoryBean;
    }

    @Bean
    PlatformTransactionManager transactionManagerOne() {
        return new JpaTransactionManager(dummyTestEntityManagerFactory()
                .getObject());
    }

    @Bean(name = "jpadummyTestVendorApapter")
    @Primary
    public JpaVendorAdapter jpadummyTestVendorAdapter() {
        HibernateJpaVendorAdapter jpadummyTestVendorAdapter = new HibernateJpaVendorAdapter();
        jpadummyTestVendorAdapter.setShowSql(true);
        jpadummyTestVendorAdapter.setGenerateDdl(true);
        jpadummyTestVendorAdapter.setDatabase(Database.MYSQL);
        return jpadummyTestVendorAdapter;
    }

    @Bean
    public HibernateExceptionTranslator hibernateExceptionTranslator() {
        return new HibernateExceptionTranslator();
    }

}

Second db config

 @Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "xx.xx.xx.xx.xx.running", entityManagerFactoryRef = "alpharaidEntityManagerFactory", transactionManagerRef = "transactionManagertwo")
@PropertySource("classpath:application.properties")
public class alphaDbConfig {

    @Value("${spring.datasourcealpharaid.driver-class-name}")
    String driverClassName = "";
    @Value("${spring.datasourcealpharaid.url}")
    String url = "";
    @Value("${spring.datasourcealpharaid.username}")
    String userName = "";
    @Value("${spring.datasourcealpharaid.password}")
    String password = "";
    @Autowired
    @Qualifier("jpaMyalphaVendorAdapter")
    JpaVendorAdapter myalphaVendorAdapter;

    @Bean(name = "alpharaidDataSource")
    public DataSource alpharaidDataSource() {
        return DataSourceBuilder.create().url(url)
                .driverClassName(driverClassName).username(userName)
                .password(password).build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean alpharaidEntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        factoryBean.setDataSource(alpharaidDataSource());

        factoryBean.setJpaVendorAdapter(myalphaVendorAdapter);
        factoryBean.setPackagesToScan(R.alphaDB_PACKAGE);
        factoryBean.setPersistenceUnitName("alpharaidPersistenceUnit");
        factoryBean.afterPropertiesSet();

        return factoryBean;
    }

    @Bean
    PlatformTransactionManager transactionManagerTwo() {
        return new JpaTransactionManager(alpharaidEntityManagerFactory()
                .getObject());
    }

    @Bean(name = "jpaMyalphaVendorAdapter")
    public JpaVendorAdapter jpaMyalphaVendorAdapter() {
        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setShowSql(true);
        jpaVendorAdapter.setGenerateDdl(true);
        jpaVendorAdapter.setDatabase(Database.MYSQL);
        return jpaVendorAdapter;
    }

}

when i start server, i recieve Exception 

    2015-08-19 15:21:25.412 ERROR 16928 --- [           main] org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000299: Could not complete schema update

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'xyz'@'192.168.XX.XX' to database 'xyz'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:870)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1659)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1206)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44)

and Another one in same Exception (though little later)

Caused by: org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 2: alpharaidEntityManagerFactory,dummyTestEntityManagerFactory
    at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.findDefaultEntityManagerFactory(PersistenceAnnotationBeanPostProcessor.java:572)
    at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.findEntityManagerFactory(PersistenceAnnotationBeanPostProcessor.java:531)
    at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor$PersistenceElement.resolveEntityManager(PersistenceAnnotationBeanPostProcessor.java:697)
    at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor$PersistenceElement.getResourceToInject(PersistenceAnnotationBeanPostProcessor.java:670)
    at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:169)
    at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
    at org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor.postProcessPropertyValues(PersistenceAnnotationBeanPostProcessor.java:354)
    ... 29 more

My properties files to read connection Details

spring.datasourcedummyTestraid.driver-class-name=com.mysql.jdbc.Driver
spring.datasourcedummyTestraid.url=jdbc:mysql://xx.xx.xx.xx/xyz
spring.datasourcedummyTestraid.username=xxx
spring.datasourcedummyTestraid.password=xxx

spring.datasourcealpharaid.driver-class-name=com.mysql.jdbc.Driver
spring.datasourcealpharaid.url=jdbc:mysql://xx.xx.xx.xx/abc
spring.datasourcealpharaid.username=xxx
spring.datasourcealpharaid.password=xxx


Few Observations:

In exception it can be seen it is trying to connect to my local db ,192.168.XX.XX is my local address.

 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'xyz'@'192.168.XX.XX' to database 'xyz'

(which do not exist) ,I am trying to connect to 2 different remote db's as defined in Properties file.why it goes to local?

when i comment one of the config then other runs fine (though no access problem comes) problem of No qualifying bean of type [javax.persistence.EntityManagerFactory] for this i have tried to make one of the Bean primary,tried making it simple function but of no use.

Please help me in solving the problem . Thanks.

Upvotes: 2

Views: 1610

Answers (1)

Edd&#250; Mel&#233;ndez
Edd&#250; Mel&#233;ndez

Reputation: 6530

You can avoid doing this

@Value("${spring.datasourcealpharaid.driver-class-name}")
String driverClassName = "";
@Value("${spring.datasourcealpharaid.url}")
String url = "";
@Value("${spring.datasourcealpharaid.username}")
String userName = "";
@Value("${spring.datasourcealpharaid.password}")
String password = "";
@Autowired
@Qualifier("jpaMyAlphaVendorAdapter")
JpaVendorAdapter myAlphaVendorAdapter;

@Bean(name = "dummyTestDataSource")
public DataSource dummyTestDataSource() {
    return DataSourceBuilder.create().url(url)
            .driverClassName(driverClassName).username(userName)
            .password(password).build();
}

and instead reduce your code with the lines below:

@ConfigurationProperties(prefix = "spring.datasourcealpharaid")
@Bean
@Primary
public DataSource postgresDataSource() {
    return DataSourceBuilder.create().
            build();
}

In order to avoid the exception below you can name your bean as a entityManagerFactory

Caused by: org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type [javax.persistence.EntityManagerFactory] is defined: expected single matching bean but found 2: macraidEntityManagerFactory,nextGenEntityManagerFactory

@Bean(name = "entityManagerFactory")
@Primary
public LocalContainerEntityManagerFactoryBean emf1(EntityManagerFactoryBuilder builder){
    return builder
            .dataSource(postgresDataSource())
            .packages("io.eddumelendez.springdatajpa.domain1")
            .persistenceUnit("users")
            .build();
}

Checkout the complete configuration here

Upvotes: 2

Related Questions