11 Replies Latest reply on Sep 4, 2009 10:02 PM by Arbi Sookazian

    Entity, using multiple datasources

    Antonio Broughton Newbie

      Hi,


      I am still newish to using Seam, and I found an issue that I can't seem to fix, so I thought I might as well post a big post, with all the things that I have done, just to see if I have done it correctly.


      The basic problem I am having, is that I have two datasources defined within my Project-ds.xml:



       <local-tx-datasource>
          <jndi-name>MySQLDatasource</jndi-name>
          <connection-url>jdbc:mysql://localhost:3306/mydb</connection-url>
          <driver-class>com.mysql.jdbc.Driver</driver-class>
          <user-name>myuser</user-name>
          <password>mypass</password>
        </local-tx-datasource>
      
       <local-tx-datasource>
          <jndi-name>PgSQLDatasource</jndi-name>
          <connection-url>jdbc:postgresql://localhost/pgdb</connection-url>
          <driver-class>org.postgresql.Driver</driver-class>
          <user-name>pguser</user-name>
          <password>pgpass</password>  
       </local-tx-datasource>



      And I am trying to use the the PostgreSQL datasource within an xhtml file, using the Entity functionality ie:


        <rich:dataTable id="settingGroupView" var="settingGroup" value="#{settingGroupAction.resultList}">
          <rich:column>
            <f:facet name="header"><h:outputText styleClass="headerText" value="Input Id"/></f:facet>
            <h:outputText value="#{settingGroupAction.setting_group_id}"/>
          </rich:column>
          <rich:column>
            <f:facet name="header"><h:outputText styleClass="headerText" value="Setting Group Id"/></f:facet>
            <h:outputText value="#{settingGroupAction.group_name}"/>
          </rich:column>
        </rich:dataTable>



      However, the error I am getting shown is:


      javax.el.ELException: /page.xhtml @29,103 value="#{settingGroupAction.resultList}": Error reading 'resultList' on type nz.co.company.core.db.SettingGroupAction_$$_javassist_1



      And more interestingly, in the stack trace:


      Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: SELECT command denied to user 'myuser'@'localhost' for table 'setting_group'
           at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
           at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
           at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
           at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)



      Im not sure why it is trying to use the MySQL datasource... (setting_group is located within the PostgreSQL datasource).


      I am thinking that I have done something wrong, so here is the code snippets from my project:


      persistence.xml:


        <persistence-unit name="MyDatabase">
          <provider>org.hibernate.ejb.HibernatePersistence</provider>
          <jta-data-source>java:/MySQLDatasource</jta-data-source>
          <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="jboss.entity.manager.factory.jndi.name" value="java:/EntityManagerFactories/myData"/>
          </properties>
        </persistence-unit>
        <persistence-unit name="PgDatabase">
          <provider>org.hibernate.ejb.HibernatePersistence</provider>
          <jta-data-source>java:/PgSQLDatasource</jta-data-source>
          <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="jboss.entity.manager.factory.jndi.name" value="java:/EntityManagerFactories/pgData"/>
          </properties>
        </persistence-unit>



      components.xml:


        <persistence:managed-persistence-context name="entityManager"
             auto-create="true"
             persistence-unit-jndi-name="java:/EntityManagerFactories/myData"/>
        <persistence:managed-persistence-context name="pgDatabase"
             auto-create="true" 
             persistence-unit-jndi-name="java:/EntityManagerFactories/pgData" />



      SettingGroup.java:


      @Entity
      @Table(name = "setting_group", catalog = "pgdb")
      public class SettingGroup implements Serializable {
      
           @In
           EntityManager pgDatabase;
      
           private Integer setting_group_id;
           private String group_name;
      
           @Id
           @Column(name = "setting_group_id")
           public Integer getsetting_group_id() {
                return setting_group_id;
           }
           
           public void setsetting_group_id(Integer setting_group_id) {
                this.setting_group_id = setting_group_id;
           }
           
           @Column(name = "group_name")
           public String getgroup_name() {
                return group_name;
           }
           
           public void setgroup_name(String group_name) {
                this.group_name = group_name;
           }
      }



      SettingGroupAction.java:


      @Name("settingGroupAction")
      public class SettingGroupAction extends EntityQuery {
           static final long serialVersionUID = 0;
           
           private SettingGroup setting_group = new SettingGroup();
           
           @Override
           public String getEjbql() {
                return "select setting_group from SettingGroup setting_group";
           }
      
           @Override
           public Integer getMaxResults() {
                return 25;
           }
           
           public SettingGroup getSettingGroup() {
                return setting_group;
           }
      }



      Can anyone see what I have done wrong? is it possible to use multiple datasources within the same Seam project?


      Also, I found that at least one of the managed-persistence-context names had to be called entityManager, or else things started to break.


      Is this the reason why it is using the MySQL datasource, is because Seam only uses entityManager?? (even though I have specified the @In pgDatabase within SettingGroup.java?


      Thanks

        • 1. Re: Entity, using multiple datasources
          Fernando Montaño Expert

          Try this:



          
          @Name("settingGroupAction")
          
          public class SettingGroupAction extends EntityQuery {
          
               static final long serialVersionUID = 0;
          
               
          
               private SettingGroup setting_group = new SettingGroup();
          
               
          
               @Override
          
               public String getEjbql() {
          
                    return "select setting_group from SettingGroup setting_group";
          
               }
          
          
               @Override
          
               public Integer getMaxResults() {
          
                    return 25;
          
               }
          
               
          
               public SettingGroup getSettingGroup() {
          
                    return setting_group;
          
               }
          
                   @Override
          
                   protected String getPersistenceContextName() {
          
                      return "pgDatabase";    
          
                   }
          
          }
          
          



          BTW, I don't see any reason to have:


          
          @In EntityManager pgDatabase
          
          



          in your SettingGroup @Entity. Seems a misconception.



          Anyway, HTH.




          • 2. Re: Entity, using multiple datasources
            Fernando Montaño Expert

            I noticied that there are other problems in your code, it will not work, even whether you have persistence context name defined
            (maybe is a type error in your own).



            1.- Your Entity is not following the JavaBean getter/setter conventions (also setters...).
            e.g. This:


            public Integer getsetting_group_id() {
            
                      return setting_group_id;
            
                 }
            
            


            Should be:


            
            public Integer getSetting_group_id() {
            
                      return setting_group_id;
            
                 }
            
            



            2.- Your databable code is invalid, it should look like:


            
            <rich:dataTable id="settingGroupView" var="settingGroup" value="#{settingGroupAction.resultList}">
            
                <rich:column>
            
                  <f:facet name="header"><h:outputText styleClass="headerText" value="Input Id"/></f:facet>
            
                  <h:outputText value="#{settingGroup.setting_group_id}"/>
            
                </rich:column>
            
                <rich:column>
            
                  <f:facet name="header"><h:outputText styleClass="headerText" value="Setting Group Id"/></f:facet>
            
                  <h:outputText value="#{settingGroup.group_name}"/>
            
                </rich:column>




            I really suggest you to take a look to the Seam examples which comes in the seam distribution.



            Regards.

            • 3. Re: Entity, using multiple datasources
              Antonio Broughton Newbie

              Thanks for that, the getPersistenceContextName worked nicely.


              Also, the conventions, I wasn't sure if I could use them in that way, as in I thought that the get / set methods were relative to the same casing as the database columns... eg:


              db col: setting_group_id  get method: getsetting_group_id()


              db col: SeTTing_group_id  get method: getSeTTing_group_id()


              And with postgresql, they treat all column names as lowercase (unless specified within quotes)


              With regard to the datatable, I did that have that, its just I copied it wrong (removed some names that company I work for do not wish to release at moment).


              Thanks again for the reply

              • 4. Re: Entity, using multiple datasources
                Per Wiklander Newbie

                Also, the conventions, I wasn't sure if I could use them in that way, as in I thought that the get / set methods were relative to the same casing as the database columns... eg:

                db col: setting_group_id  get method: getsetting_group_id()

                db col: SeTTing_group_id  get method: getSeTTing_group_id()



                Actually, to conform to conventions you should name your properties and getters/setters in camelCase:


                private final Integer settingGroupId;
                
                public Integer getSettingGroupId() {
                    return this.settingGroupId;
                }
                
                public void setSettingGroupId(Integer settingGroupId) {
                    this.settingGroupId = settingGroupId;
                }
                



                Then Hibernate will take care of the conversion to correct column names through the Hibernate NamingStrategy class you are using (I don't remember the defaults now but I think the column name would become setting_group_id). You can also specify your column name with the @Column annotation if you want to go totally crazy with the names...


                If you use Eclipse you would of course only have to type the property line of the above code block, the rest is done with generate getters and setters.


                You can also generate the Java code from the your db and vice versa using the Hibernate tools. Less code written, less errors.

                • 5. Re: Entity, using multiple datasources
                  Ton Nguyen Hoang Newbie

                  Thanks, getPersistenceContextName work great.


                  But what I have to do if I have a method called List getSettings(), which will get all the Setting of this Setting group, when Setting is on Database 1, and SettingGroup table is on Database 2?

                  • 6. Re: Entity, using multiple datasources
                    Ton Nguyen Hoang Newbie

                    Here is the content of that method:


                         public List<Setting> getSettings() {
                              return getInstance() == null ? null : new ArrayList<Setting>(
                                        getInstance().getSettings());
                         }


                    • 7. Re: Entity, using multiple datasources
                      Carsten Hoehne Master

                      Currently, we are assembling our objects by issuing multiple querys to the different databases.


                      From your post i assume that you have to retrieve the setting groups from db2 and than, retrieve all associated settings from db1


                      Our entitys do not have relations over database boundarys. So it is up to the app to simulate these kind of relations. Hope this help.

                      • 8. Re: Entity, using multiple datasources
                        Srinivas Chamarthi Newbie

                        Hi, I have the same problem. But I am not extending EntityQuery. but using PersistenceContext as shown below


                        @In EntityManager umcEntityManager;


                        in this case, how do I override persitenceContextName method to look for string umcEntityManager?


                        as you have experienced, I also got entityManager null exception.


                        appreciate any help in this regd.


                        thx
                        Srinivas Chamarthi




                        • 9. Re: Entity, using multiple datasources
                          Ton Nguyen Hoang Newbie

                          Try this:



                               @PersistenceContext(unitName="UNITNAME")
                               private EntityManager em;



                          Which UNITNAME is the name of the persistence-unit in persistence.xml.

                          • 10. Re: Entity, using multiple datasources
                            Robson Cardoso Newbie

                            Hello everybody!


                            Guys! I'm in a big trouble here with multiple datasources! I tried follow the hints on this topic but i can't make this thing work!  =(


                            I was wondering if someone could help me  =)


                            I made the configurations on xxx-ds.xml, persistence.xml and components.xml based on the examples in this topic.


                            here's my xxx-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>
                            
                                 <local-tx-datasource>
                                      <jndi-name>gdkDatasource</jndi-name>
                                      <connection-url>jdbc:postgresql://dbhost:5432/prod-gdk</connection-url>
                                      <driver-class>org.postgresql.Driver</driver-class>
                                      <user-name>prod</user-name>
                                      <password>prod</password>
                                 </local-tx-datasource>
                            
                                 <local-tx-datasource>
                                      <jndi-name>dotproject</jndi-name>
                                      <connection-url>jdbc:mysql://dbhost:3306/dotproject</connection-url>
                                      <driver-class>com.mysql.jdbc.Driver</driver-class>
                                      <user-name>prod</user-name>
                                      <password>prod</password>
                                 </local-tx-datasource>
                            
                            </datasources>
                            


                            my persistence.xml:


                            <?xml version="1.0" encoding="UTF-8"?>
                                 <!-- Persistence deployment descriptor for prod profile -->
                            <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="gdk">
                                      <provider>org.hibernate.ejb.HibernatePersistence</provider>
                                      <jta-data-source>java:/gdkDatasource</jta-data-source>
                                      <properties>
                                           <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect" />
                                           <property name="hibernate.hbm2ddl.auto" value="validate" />
                                           <property name="hibernate.jdbc.batch_size" value="20" />
                                           <property name="jboss.entity.manager.factory.jndi.name"     value="java:/gdkEntityManagerFactory" />
                                      </properties>
                                 </persistence-unit>
                            
                                 <persistence-unit name="dotproject">
                                      <provider>org.hibernate.ejb.HibernatePersistence</provider>
                                      <jta-data-source>java:/dotproject</jta-data-source>
                                      <properties>
                                           <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
                                           <property name="hibernate.hbm2ddl.auto" value="validate" />
                                           <property name="hibernate.jdbc.batch_size" value="20" />
                                           <property name="jboss.entity.manager.factory.jndi.name" value="java:/dotprojectEntityManagerFactory" />
                                      </properties>
                                 </persistence-unit>
                            
                            </persistence>
                            


                            the entityManager in my components.xml:


                                    <persistence:managed-persistence-context
                                      name="entityManager" auto-create="true" persistence-unit-jndi-name="java:/gdkEntityManagerFactory" />
                            
                                 <persistence:managed-persistence-context
                                      name="dotproject" auto-create="true" persistence-unit-jndi-name="java:/dotprojectEntityManagerFactory" />
                            



                            And finally there's how I get and use the entityManager:


                                    @PersistenceContext(unitName = "gdk")
                                 private EntityManager pgsql;
                            
                                 @PersistenceContext(unitName = "dotproject")
                                 private EntityManager mysql;
                            
                                 public void test() {
                                      
                                      pgsql.createNativeQuery("select * from projeto").getResultList();
                                      
                                      mysql.createNativeQuery("select * from projects").getResultList();
                            
                                 }
                            



                            I'm getting this error when it executes the mysql query:


                            13:56:50,827 INFO  [STDOUT] Hibernate: 
                                select
                                    * 
                                from
                                    projects
                            13:56:50,830 ERROR [application] javax.ejb.EJBTransactionRolledbackException: org.hibernate.MappingException: No Dialect mapping for JDBC type: -1
                            javax.faces.el.EvaluationException: javax.ejb.EJBTransactionRolledbackException: org.hibernate.MappingException: No Dialect mapping for JDBC type: -1
                                 at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:102)
                                 at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
                                 at javax.faces.component.UICommand.broadcast(UICommand.java:387)
                                 at org.ajax4jsf.component.AjaxActionComponent.broadcast(AjaxActionComponent.java:55)
                                 at org.ajax4jsf.component.AjaxViewRoot.processEvents(AjaxViewRoot.java:321)
                                 at org.ajax4jsf.component.AjaxViewRoot.broadcastEvents(AjaxViewRoot.java:296)
                                 at org.ajax4jsf.component.AjaxViewRoot.processPhase(AjaxViewRoot.java:253)
                                 at org.ajax4jsf.component.AjaxViewRoot.processApplication(AjaxViewRoot.java:466)
                                 at org.jboss.portletbridge.lifecycle.InvokeApplicationPhase.executePhase(InvokeApplicationPhase.java:57)
                                 at org.jboss.portletbridge.lifecycle.LifecyclePhase.execute(LifecyclePhase.java:72)
                                 at org.jboss.portletbridge.lifecycle.UpdateModelPhase.executeNextPhase(UpdateModelPhase.java:49)
                                 at org.jboss.portletbridge.lifecycle.LifecyclePhase.execute(LifecyclePhase.java:96)
                                 at org.jboss.portletbridge.lifecycle.ProcessValidatorsPhase.executeNextPhase(ProcessValidatorsPhase.java:50)
                                 at org.jboss.portletbridge.lifecycle.LifecyclePhase.execute(LifecyclePhase.java:96)
                            



                            No idea what to do ....