Multiple Datasource mapping problem

[oguzyalcin.oguzhanyalcin.gazi.edu.tr]

Hi all,
I'm trying to add an audit system to my application. I'm adding another Db for storing changes in it. The original DB is Taykos and the Log DB is taykos_log.I followed the way described in this link.

I've added the datasources:(project-ds.xml)

   <local-tx-datasource>
      <jndi-name>taykosDatasource</jndi-name>
      <connection-url>jdbc:mysql://localhost:3306/taykos</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>root</user-name>
      <password>111111</password>
   </local-tx-datasource>
   
   <local-tx-datasource>
      <jndi-name>taykosLogDatasource</jndi-name>
      <connection-url>jdbc:mysql://localhost:3306/taykos_log</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>root</user-name>
      <password>111111</password>
   </local-tx-datasource>

Added the persistence units:(persistence.xml)

<persistence-unit name="taykos" transaction-type="JTA">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <jta-data-source>java:/taykosDatasource</jta-data-source>
      <properties>
         <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
         <property name="hibernate.hbm2ddl.auto" value="update"/>
         <property name="hibernate.show_sql" value="true"/>
         <property name="hibernate.format_sql" value="true"/>
         <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.JBossTransactionManagerLookup"/>
         
      </properties>
   </persistence-unit>
   
   <persistence-unit name="taykosLog" transaction-type="JTA">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <jta-data-source>java:/taykosLogDatasource</jta-data-source>
      <properties>
         <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
         <property name="hibernate.hbm2ddl.auto" value="update"/>
         <property name="hibernate.show_sql" value="true"/>
         <property name="hibernate.format_sql" value="true"/>
         <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.JBossTransactionManagerLookup"/>
         
      </properties>
   </persistence-unit>

Made changes in components:(components.xml)

<factory name="dynamicEntityManager" scope="SESSION" value="entityManager"
          auto-create="true" />
     <persistence:managed-persistence-context
          auto-create="true" name="mainEntityManager" entity-manager-factory="#{entityManagerFactory}"
          persistence-unit-jndi-name="java:/taykosDatasource" />
     <persistence:managed-persistence-context
          auto-create="true" name="logEntityManager" entity-manager-factory="#{entityManagerFactoryLog}"
          persistence-unit-jndi-name="java:/taykosLogDatasource" />
     <persistence:entity-manager-factory
          name="entityManagerFactoryLog" persistence-unit-name="taykosLog" />
     <persistence:entity-manager-factory
          name="entityManagerFactory" persistence-unit-name="taykos" />

and added a seam component for managing different entityManagers:

@Name("entityManager")
@Scope(ScopeType.CONVERSATION)
public class DynamicEntityManager {
@Logger 
Log log;
     
     @Unwrap
     public EntityManager getDynamicEntityManager() {
          String dynamicEntityManager = (String)Contexts.getSessionContext().get("dynamicEntityManager");
          log.info("current value: "+dynamicEntityManager);
          if(dynamicEntityManager==null){
               Contexts.getSessionContext().set("dynamicEntityManager", "mainEntityManager");
               dynamicEntityManager="mainEntityManager";
          }
          EntityManager entityManager = (EntityManager)Component.getInstance(dynamicEntityManager);
          log.info("entitymanager is : " + entityManager==null?"null":"not null");
          return entityManager;
     }

}

when I try to get a resultlist on an EntityList object: this exception occurs:

javax.el.ELException: /layout/common/tabmenu.xhtml @12,63 items="#{programList.resultList}": Error reading 'resultList' on type com.taykos.session.ProgramList_$$_javassist_1
     at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:76)
     at com.sun.facelets.tag.jstl.core.ForEachHandler.apply(ForEachHandler.java:121)
     at com.sun.facelets.tag.CompositeFaceletHandler.apply(CompositeFaceletHandler.java:47)
     at com.sun.facelets.tag.jsf.ComponentHandler.applyNextHandler(ComponentHandler.java:314)
     at com.sun.facelets.tag.jsf.ComponentHandler.apply(ComponentHandler.java:169)
     at com.sun.facelets.tag.jsf.core.ViewHandler.apply(ViewHandler.java:109)
     at com.sun.facelets.compiler.NamespaceHandler.apply(NamespaceHandler.java:49)
     at com.sun.facelets.compiler.EncodingHandler.apply(EncodingHandler.java:25)
     at com.sun.facelets.impl.DefaultFacelet.include(DefaultFacelet.java:248)
     at com.sun.facelets.impl.DefaultFacelet.include(DefaultFacelet.java:294)
     at com.sun.facelets.impl.DefaultFacelet.include(DefaultFacelet.java:273)
     at com.sun.facelets.impl.DefaultFaceletContext.includeFacelet(DefaultFaceletContext.java:140)
     at com.sun.facelets.tag.ui.IncludeHandler.apply(IncludeHandler.java:65)
     at com.sun.facelets.tag.CompositeFaceletHandler.apply(CompositeFaceletHandler.java:47)
     at com.sun.facelets.compiler.NamespaceHandler.apply(NamespaceHandler.java:49)
     at com.sun.facelets.tag.jsf.core.ViewHandler.apply(ViewHandler.java:109)
     at com.sun.facelets.compiler.NamespaceHandler.apply(NamespaceHandler.java:49)
     at com.sun.facelets.tag.CompositeFaceletHandler.apply(CompositeFaceletHandler.java:47)
     at com.sun.facelets.compiler.EncodingHandler.apply(EncodingHandler.java:25)
     at com.sun.facelets.impl.DefaultFacelet.include(DefaultFacelet.java:248)
     at com.sun.facelets.impl.DefaultFacelet.include(DefaultFacelet.java:294)
     at com.sun.facelets.impl.DefaultFacelet.include(DefaultFacelet.java:273)
     at com.sun.facelets.impl.DefaultFaceletContext.includeFacelet(DefaultFaceletContext.java:140)
     at com.sun.facelets.tag.ui.CompositionHandler.apply(CompositionHandler.java:113)
     at com.sun.facelets.compiler.NamespaceHandler.apply(NamespaceHandler.java:49)
     at com.sun.facelets.compiler.EncodingHandler.apply(EncodingHandler.java:25)
     at com.sun.facelets.impl.DefaultFacelet.apply(DefaultFacelet.java:95)
     at com.sun.facelets.FaceletViewHandler.buildView(FaceletViewHandler.java:524)
     at com.sun.facelets.FaceletViewHandler.renderView(FaceletViewHandler.java:567)
     at org.ajax4jsf.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:100)
     at org.ajax4jsf.application.AjaxViewHandler.renderView(AjaxViewHandler.java:176)
     at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:110)
     at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
     at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
     at javax.faces.webapp.FacesServlet.service(FacesServlet.java:266)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:83)
     at org.jboss.seam.web.IdentityFilter.doFilter(IdentityFilter.java:40)
     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
     at org.jboss.seam.web.MultipartFilter.doFilter(MultipartFilter.java:90)
     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
     at org.jboss.seam.web.ExceptionFilter.doFilter(ExceptionFilter.java:64)
     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
     at org.jboss.seam.web.RedirectFilter.doFilter(RedirectFilter.java:45)
     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
     at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:178)
     at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
     at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:390)
     at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:517)
     at org.jboss.seam.web.Ajax4jsfFilter.doFilter(Ajax4jsfFilter.java:56)
     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
     at org.jboss.seam.web.LoggingFilter.doFilter(LoggingFilter.java:58)
     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
     at org.jboss.seam.web.HotDeployFilter.doFilter(HotDeployFilter.java:53)
     at org.jboss.seam.servlet.SeamFilter$FilterChainImpl.doFilter(SeamFilter.java:69)
     at org.jboss.seam.servlet.SeamFilter.doFilter(SeamFilter.java:158)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
     at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)
     at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)
     at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)
     at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)
     at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)
     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
     at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)
     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:601)
     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
     at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: Program is not mapped [select program from Program program]
     at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:601)
     at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:96)
     at org.jboss.seam.persistence.EntityManagerProxy.createQuery(EntityManagerProxy.java:81)
     at org.jboss.seam.framework.EntityQuery.createQuery(EntityQuery.java:175)
     at org.jboss.seam.framework.EntityQuery.initResultList(EntityQuery.java:73)
     at org.jboss.seam.framework.EntityQuery.getResultList(EntityQuery.java:65)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
     at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
     at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
     at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:77)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
     at org.jboss.seam.transaction.TransactionInterceptor$1.work(TransactionInterceptor.java:95)
     at org.jboss.seam.util.Work.workInTransaction(Work.java:47)
     at org.jboss.seam.transaction.TransactionInterceptor.aroundInvoke(TransactionInterceptor.java:89)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
     at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
     at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
     at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185)
     at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103)
     at com.taykos.session.ProgramList_$$_javassist_1.getResultList(ProgramList_$$_javassist_1.java)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
     at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
     at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:72)
     at org.jboss.el.parser.AstPropertySuffix.getValue(AstPropertySuffix.java:53)
     at org.jboss.el.parser.AstValue.getValue(AstValue.java:67)
     at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
     at com.sun.facelets.el.TagValueExpression.getValue(TagValueExpression.java:71)
     ... 77 more
Caused by: org.hibernate.hql.ast.QuerySyntaxException: Program is not mapped [select program from Program program]
     at org.hibernate.hql.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:181)
     at org.hibernate.hql.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:110)
     at org.hibernate.hql.ast.tree.FromClause.addFromElement(FromClause.java:93)
     at org.hibernate.hql.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:277)
     at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3056)
     at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2945)
     at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
     at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
     at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
     at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
     at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:251)
     at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183)
     at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
     at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
     at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
     at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
     at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
     at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
     at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
     at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:93)

Anybody having the same issue? Can anybody suggest me a way to success?
Thanks in advance.

[pragun1]

You can try this,

Your persistence.xml file has transaction-type="JTA" so in your project-ds.xml file try using <xa-datasource> like

<xa-datasource>
<jndi-name>taykosDatasource</jndi-name>
<xa-datasource-property name="URL">jdbc:mysql://localhost:3306/taykos</xa-datasource-property>
<xa-datasource-class>com.mysql.jdbc.Driver</xa-datasource-class>
<user-name>root</user-name>
<password>111111</password>
</xa-datasource>

<xa-datasource>
<jndi-name>taykosLogDatasource</jndi-name>
<xa-datasource-property name="URL">jdbc:mysql://localhost:3306/taykos_log</xa-datasource-property>
<xa-datasource-class>com.mysql.jdbc.Driver</xa-datasource-class>
<user-name>root</user-name>
<password>111111</password>
</xa-datasource>

[lvdberg]

The error states that Program is not mapped, so my first question is if it works with a single database.

Have you looked at Envers for Hibernate for auditing, works great out-of-the-box, but I haven't tried it with two separate databases.

[uesker]

I have the same issue...

Did the suggestion work?

[uesker]

Hi!
I tried using the tag xa-datasource but Eclipse showed an error message about the structure of
XML document.
Here is an excerpt from the error message

The content of element type xa-datasource must match (jndi-name, use-java-context?, track-connection-by-tx, ...)

I used an example posted on the page of JBoss (
My Link)
and the error continues.

This is the complete contents of the file ds.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE datasources
    PUBLIC "-//JBoss//DTD JBOSS JCA Config 1.5//EN"
    "http://www.jboss.org/j2ee/dtd/jboss-ds_1_5.dtd">
<datasources>

        <xa-datasource>
                <jndi-name>ExemploSeamDatasource2</jndi-name>
                <track-connection-by-tx />
                <isSameRM-override-value>false</isSameRM-override-value>
                <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/exemploseam2</xa-datasource-property>
                <xa-datasource-property name="User">xxx</xa-datasource-property>
                <xa-datasource-property name="Password">xxx</xa-datasource-property>
                <min-pool-size>5</min-pool-size>
                <max-pool-size>100</max-pool-size>
                <blocking-timeout-millis>5000</blocking-timeout-millis>
                <idle-timeout-minutes>15</idle-timeout-minutes>
                <metadata>
                        <type-mapping>mySQL</type-mapping>
                </metadata>
        </xa-datasource>

        <xa-datasource>
                <jndi-name>GenericXADS</jndi-name>
                <xa-datasource-class>[fully qualified name of class implementing
                        javax.sql.XADataSource goes here]</xa-datasource-class>
                <xa-datasource-property name="SomeProperty">SomePropertyValue</xa-datasource-property>
                <xa-datasource-property name="SomeOtherProperty">SomeOtherValue</xa-datasource-property>

                <user-name>x</user-name>
                <password>y</password>
                <transaction-isolation>TRANSACTION_SERIALIZABLE</transaction-isolation>

                <!--pooling parameters-->
                <min-pool-size>5</min-pool-size>
                <max-pool-size>100</max-pool-size>
                <blocking-timeout-millis>5000</blocking-timeout-millis>
                <idle-timeout-minutes>15</idle-timeout-minutes>
                <!--
                        sql to call when connection is created <new-connection-sql>some
                        arbitrary sql</new-connection-sql>
                -->

                <!--
                        sql to call on an existing pooled connection when it is obtained from
                        pool <check-valid-connection-sql>some arbitrary
                        sql</check-valid-connection-sql>
                -->

                <!-- pooling criteria.  USE AT MOST ONE-->
                <!--
                        If you don't use JAAS login modules or explicit login
                        getConnection(usr,pw) but rely on user/pw specified above, don't
                        specify anything here
                -->

                <!-- If you supply the usr/pw from a JAAS login module -->
                <security-domain />

                <!--
                        if your app supplies the usr/pw explicitly getConnection(usr, pw)
                -->
                <application-managed-security />

        </xa-datasource>
</datasources>