Announcement Announcement Module
Collapse
No announcement yet.
Two Datasources and Entities get lost on which one to use. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Two Datasources and Entities get lost on which one to use.

    Hey all... I'm trying to use 2 datasources, a MySQL and a PostgreSQL.

    application-context.xml
    Code:
    	<context:component-scan base-package="com.iconecta.global" />
    	<context:annotation-config />
    
    	<bean id="datasourceMySQL" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
    		<property name="url" value="jdbc:mysql://integrator.i-next.psi.br/iconecta_sincronia" />
    		<property name="username" value="user" />
    		<property name="password" value="password" />
    	</bean>
    
    	<bean id="datasourcePostgreSQL" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    		<property name="driverClassName" value="org.postgresql.Driver" />
    		<property name="url" value="jdbc:postgresql://localhost/radius" />
    		<property name="username" value="user" />
    		<property name="password" value="password" />
    	</bean>
    
    	<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor">
    		<property name="defaultPersistenceUnitName" value="persistenceDialectPostgreSQL" />
    	</bean>
    
    	<bean id="persistenceUnitManager" class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
    		<property name="persistenceXmlLocations">
    			<list value-type="java.lang.String">
    				<value>classpath:META-INF/mysql-persistence.xml</value>
    				<value>classpath:META-INF/postgresql-persistence.xml</value>
    			</list>
    		</property>
    		<property name="defaultDataSource" ref="datasourcePostgreSQL" />
    		<property name="dataSources">
    			<map>
    				<entry key="mysql" value-ref="datasourceMySQL" />
    				<entry key="postgresql" value-ref="datasourcePostgreSQL" />
    			</map>
    		</property>
    		<property name="defaultPersistenceUnitRootLocation" value="com.iconecta.global"></property>
    	</bean>
    
    	<bean id="entityManagerFactoryPostgreSQL" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    		<property name="jpaVendorAdapter">
    			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" p:showSql="true" />
    		</property>
    		<property name="persistenceUnitManager" ref="persistenceUnitManager" />
    		<property name="persistenceUnitName" value="persistenceDialectPostgreSQL" />
    		<property name="dataSource" ref="datasourcePostgreSQL" />
    <!-- 		<property name="packagesToScan" value="com.iconecta.integrator" /> -->
    	</bean>
    
    	<bean id="entityManagerFactoryMySQL" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    		<property name="jpaVendorAdapter">
    			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" p:showSql="true" />
    		</property>
    		<property name="persistenceUnitManager" ref="persistenceUnitManager" />
    		<property name="persistenceUnitName" value="persistenceDialectMySQL" />
    		<property name="dataSource" ref="datasourceMySQL" />
    <!-- 		<property name="packagesToScan" value="com.iconecta.radius" /> -->
    	</bean>
    
    	<bean id="transactionManagerMySQL" class="org.springframework.orm.jpa.JpaTransactionManager" p:entity-manager-factory-ref="entityManagerFactoryMySQL" />
    	<bean id="transactionManagerPostgreSQL" class="org.springframework.orm.jpa.JpaTransactionManager" p:entity-manager-factory-ref="entityManagerFactoryPostgreSQL" />
    
    	<jpa:repositories base-package="com.iconecta.integrator.repositories" transaction-manager-ref="transactionManagerMySQL" entity-manager-factory-ref="entityManagerFactoryMySQL"></jpa:repositories>
    	<jpa:repositories base-package="com.iconecta.radius.repositories" transaction-manager-ref="transactionManagerPostgreSQL" entity-manager-factory-ref="entityManagerFactoryPostgreSQL"></jpa:repositories>

    mysql-persistence.xml
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0">
    	<persistence-unit name="persistenceDialectMySQL" transaction-type="RESOURCE_LOCAL">
    		<provider>org.hibernate.ejb.HibernatePersistence</provider>
    		<class>com.iconecta.integrator.entities.Cliente</class>
    		<class>com.iconecta.integrator.entities.Nas</class>
    		<exclude-unlisted-classes>true</exclude-unlisted-classes>
    		<properties>
    			<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
    			<property name="hibernate.show_sql" value="true" />
    		</properties>
    	</persistence-unit>
    </persistence>
    postgresql-persistence.xml
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0">
    	<persistence-unit name="persistenceDialectPostgreSQL" transaction-type="RESOURCE_LOCAL">
    		<provider>org.hibernate.ejb.HibernatePersistence</provider>
    		<class>com.iconecta.radius.entities.Bridge</class>
    		<exclude-unlisted-classes>true</exclude-unlisted-classes>
    		<properties>
    			<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL82Dialect" />
    			<property name="hibernate.show_sql" value="true" />
    		</properties>
    	</persistence-unit>
    </persistence>
    Main.java
    Code:
    @Component
    public class Main {
    	
    	Logger logger = LoggerFactory.getLogger(this.getClass());
    	
    	public static void main(String[] args) {
    		AbstractApplicationContext context = new ClassPathXmlApplicationContext("classpath:META-INF/application-context.xml");
    		
    		Main p = context.getBean(Main.class);
    		p.start(args);
    	}
    	
    	@Autowired 
    	ClienteRepository clienteRepository;
    	
    	@Autowired
    	BridgeRepository bridgeRepository;
    
    	private void start(String[] args) {
    		List<Bridge> bridges = bridgeRepository.findAll();
    		for (Bridge b: bridges) {
    			logger.info(b.getNas());
    		}
    		
    		List<Cliente> clientes = clienteRepository.findAll();
    		for (Cliente c: clientes) {
    			logger.info(c.getLogin());
    			break;
    		}
    	}
    }
    So, here's what happens... Once I run this code, BridgeRepository.findAll() gets logged as intended but when it tries to ClienteRepository.findAll() it actually tries to use the same the database as the BridgeRepository.

    I was under the impression that <jpa:repositories> was supposed to avoid that in these 2 lines... Each package would be bound to a transaction and entities manager... But that's not what happens...
    Code:
    	<jpa:repositories base-package="com.iconecta.integrator.repositories" transaction-manager-ref="transactionManagerMySQL" entity-manager-factory-ref="entityManagerFactoryMySQL"></jpa:repositories>
    	<jpa:repositories base-package="com.iconecta.radius.repositories" transaction-manager-ref="transactionManagerPostgreSQL" entity-manager-factory-ref="entityManagerFactoryPostgreSQL"></jpa:repositories>
    Also, I might add I've tried to remove application-context.xml and start everything via code but I gave up trying to create the "persistenceUnitManager"... I can't find any documentation on how to do that on code.

    Thanks in advance for any advice.

  • #2
    The post was too long so here is the stack trace (Obviously, the table doesn't exists because it's the wrong Datasource).

    Code:
    Exception in thread "main" org.springframework.dao.InvalidDataAccessResourceUsageException: ERROR: relation "clientes_proativo" does not exist
      Position: 318; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: ERROR: relation "clientes_proativo" does not exist
      Position: 318
    	at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:635)
    	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:104)
    	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
    	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
    	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:84)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    	at $Proxy18.findAll(Unknown Source)
    	at com.iconecta.global.Main.start(Main.java:41)
    	at com.iconecta.global.Main.main(Main.java:26)
    Caused by: org.hibernate.exception.SQLGrammarException: ERROR: relation "clientes_proativo" does not exist
      Position: 318
    	at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
    	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    	at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    	at $Proxy24.executeQuery(Unknown Source)
    	at org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
    	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
    	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    	at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    	at org.hibernate.loader.Loader.doList(Loader.java:2516)
    	at org.hibernate.loader.Loader.doList(Loader.java:2502)
    	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
    	at org.hibernate.loader.Loader.list(Loader.java:2327)
    	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:490)
    	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
    	at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
    	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1247)
    	at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
    	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:264)
    	at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:254)
    	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:239)
    	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:56)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:601)
    	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:334)
    	at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:319)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:155)
    	... 9 more
    Caused by: org.postgresql.util.PSQLException: ERROR: relation "clientes_proativo" does not exist
      Position: 318
    	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    	at java.lang.reflect.Method.invoke(Method.java:601)
    	at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    	... 39 more
    Last edited by trojahn; Jan 24th, 2013, 03:08 PM. Reason: more info

    Comment


    • #3
      Ok, I'm not sure if it's the right approach but what fixed it for me was getting rid of the persistence manager and then set the persistenceXmlLocation on each entityManagerFactory.

      Here's the resulting application-context.xml
      Code:
      <?xml version="1.0" encoding="UTF-8"?>
      <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jpa="http://www.springframework.org/schema/data/jpa" xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:p="http://www.springframework.org/schema/p" xmlns:util="http://www.springframework.org/schema/util"
      	xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
      		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
      		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
      		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd
      		http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.2.xsd
      		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
      		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd">
      
      	<context:component-scan base-package="com.iconecta.global" />
      	<context:annotation-config />
      
      	<bean id="datasourceMySQL" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
      		<property name="url" value="jdbc:mysql://integrator.i-next.psi.br/iconecta_sincronia" />
      		<property name="username" value="user" />
      		<property name="password" value="pass" />
      	</bean>
      
      	<bean id="datasourcePostgreSQL" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      		<property name="driverClassName" value="org.postgresql.Driver" />
      		<property name="url" value="jdbc:postgresql://localhost/radius" />
      		<property name="username" value="user" />
      		<property name="password" value="pass" />
      	</bean>
      
      	<bean id="entityManagerFactoryPostgreSQL" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
      		<property name="dataSource" ref="datasourcePostgreSQL" />
      		<property name="persistenceXmlLocation" value="classpath:META-INF/postgresql-persistence.xml" />
      	</bean>
      
      	<bean id="entityManagerFactoryMySQL" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
      		<property name="dataSource" ref="datasourceMySQL" />
      		<property name="persistenceXmlLocation" value="classpath:META-INF/mysql-persistence.xml" />
      	</bean>
      
      	<bean id="transactionManagerMySQL" class="org.springframework.orm.jpa.JpaTransactionManager" p:entity-manager-factory-ref="entityManagerFactoryMySQL" />
      	<bean id="transactionManagerPostgreSQL" class="org.springframework.orm.jpa.JpaTransactionManager" p:entity-manager-factory-ref="entityManagerFactoryPostgreSQL" />
      
      	<jpa:repositories base-package="com.iconecta.integrator.repositories" transaction-manager-ref="transactionManagerMySQL" entity-manager-factory-ref="entityManagerFactoryMySQL"></jpa:repositories>
      	<jpa:repositories base-package="com.iconecta.radius.repositories" transaction-manager-ref="transactionManagerPostgreSQL" entity-manager-factory-ref="entityManagerFactoryPostgreSQL"></jpa:repositories>
      
      </beans>

      Comment

      Working...
      X