1 2 Previous Next 22 Replies Latest reply on Jun 13, 2004 12:02 AM by abcolson

    Mapping Entity Beans to Database Tables

    abcolson

      Greetings,

      I am using JBoss 3.2.3 with Postgres and encountering issues mapping beans/attributes to tables/fields. My table/field names sometimes have spaces and/or other characters that don't exactly match the bean, such as:

      Mapping entity bean "Question" => Table "Questions"
      Mapping entity bean "QuestionCategory" => Table "Question Categories"
      Mapping entity bean getter getParentId() => Field "parent_id"

      Here are my descriptors and stack trace when I try to access the "Questions" table:

      ejb-jar.xml:

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">
      
      <ejb-jar >
      
       <description><![CDATA[No Description.]]></description>
       <display-name>Generated by XDoclet</display-name>
      
       <enterprise-beans>
      
       <!-- Session Beans -->
       <session >
       <description><![CDATA[EJB that computes Fibonacci suite]]></description>
       <display-name>Fibo EJB</display-name>
      
       <ejb-name>Fibo</ejb-name>
      
       <home>tutorial.interfaces.FiboHome</home>
       <remote>tutorial.interfaces.Fibo</remote>
       <ejb-class>tutorial.ejb.FiboBean</ejb-class>
       <session-type>Stateless</session-type>
       <transaction-type>Container</transaction-type>
      
       </session>
      
       <!--
       To add session beans that you have deployment descriptor info for, add
       a file to your XDoclet merge directory called session-beans.xml that contains
       the <session></session> markup for those beans.
       -->
      
       <!-- Entity Beans -->
       <entity >
       <description><![CDATA[EJB that manages a Question]]></description>
       <display-name>Question EJB</display-name>
      
       <ejb-name>Question</ejb-name>
      
       <home>tutorial.interfaces.QuestionHome</home>
       <remote>tutorial.interfaces.Question</remote>
      
       <ejb-class>tutorial.ejb.QuestionBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.Long</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>Questions</abstract-schema-name>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>id</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>question</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>categoryId</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>level</field-name>
       </cmp-field>
       <primkey-field>id</primkey-field>
      
       <resource-ref >
       <res-ref-name>jdbc/PostgresDS</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <res-auth>Container</res-auth>
       </resource-ref>
      
       <query>
       <description><![CDATA[Lookup Question By ID]]></description>
       <query-method>
       <method-name>findByPrimaryKey</method-name>
       <method-params>
       <method-param>java.lang.Long</method-param>
       </method-params>
       </query-method>
       <result-type-mapping>Local</result-type-mapping>
       <ejb-ql><![CDATA[SELECT OBJECT(qb) FROM Questions qb WHERE qb.id = ?1]]></ejb-ql>
       </query>
       <!-- Write a file named ejb-finders-QuestionBean.xml if you want to define extra finders. -->
       </entity>
      
       <entity >
       <description><![CDATA[EJB that manages a Question Category]]></description>
       <display-name>Question Category EJB</display-name>
      
       <ejb-name>QuestionCategory</ejb-name>
      
       <home>tutorial.interfaces.QuestionCategoryHome</home>
       <remote>tutorial.interfaces.QuestionCategory</remote>
      
       <ejb-class>tutorial.ejb.QuestionCategoryBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.Long</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>QuestionCategory</abstract-schema-name>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>id</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>name</field-name>
       </cmp-field>
       <cmp-field >
       <description><![CDATA[]]></description>
       <field-name>parentId</field-name>
       </cmp-field>
       <primkey-field>id</primkey-field>
      
       <resource-ref >
       <res-ref-name>jdbc/PostgresDS</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <res-auth>Container</res-auth>
       </resource-ref>
      
       <query>
       <description><![CDATA[Lookup Question Category By ID]]></description>
       <query-method>
       <method-name>findByPrimaryKey</method-name>
       <method-params>
       <method-param>java.lang.Long</method-param>
       </method-params>
       </query-method>
       <result-type-mapping>Local</result-type-mapping>
       <ejb-ql><![CDATA[SELECT OBJECT(qcb) FROM QuestionCategoryBean qcb WHERE qcb.id = ?1]]></ejb-ql>
       </query>
       <!-- Write a file named ejb-finders-QuestionCategoryBean.xml if you want to define extra finders. -->
       </entity>
      
       <!--
       To add entity beans that you have deployment descriptor info for, add
       a file to your XDoclet merge directory called entity-beans.xml that contains
       the <entity></entity> markup for those beans.
       -->
      
       <!-- Message Driven Beans -->
       <!--
       To add message driven beans that you have deployment descriptor info for, add
       a file to your XDoclet merge directory called message-driven-beans.xml that contains
       the <message-driven></message-driven> markup for those beans.
       -->
      
       </enterprise-beans>
      
       <!-- Relationships -->
      
       <!-- Assembly Descriptor -->
       <assembly-descriptor >
       <!--
       To add additional assembly descriptor info here, add a file to your
       XDoclet merge directory called assembly-descriptor.xml that contains
       the <assembly-descriptor></assembly-descriptor> markup.
       -->
      
       <!-- finder permissions -->
      
       <!-- finder permissions -->
      
       <!-- finder permissions -->
      
       <!-- transactions -->
      
       <!-- finder transactions -->
       </assembly-descriptor>
      
      </ejb-jar>



      jbosscmp-jdbc.xml

      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN" "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_3_0.dtd">
      
      <jbosscmp-jdbc>
       <defaults>
       </defaults>
      
       <enterprise-beans>
      
       <!--
       To add beans that you have deployment descriptor info for, add
       a file to your XDoclet merge directory called jbosscmp-jdbc-beans.xml
       that contains the <entity></entity> markup for those beans.
       -->
      
       <entity>
       <ejb-name>Question</ejb-name>
       <datasource>java:/PostgresDS</datasource>
       <datasource-mapping></datasource-mapping>
       <table-name>Questions</table-name>
      
       <cmp-field>
       <field-name>id</field-name>
       <column-name>id</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>question</field-name>
       <column-name>question</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>categoryId</field-name>
       <column-name>category_id</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>level</field-name>
       <column-name>level</column-name>
      
       </cmp-field>
      
       </entity>
      
       <entity>
       <ejb-name>QuestionCategory</ejb-name>
       <table-name>Question Categories</table-name>
      
       <cmp-field>
       <field-name>id</field-name>
       <column-name>id</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>name</field-name>
       <column-name>name</column-name>
      
       </cmp-field>
       <cmp-field>
       <field-name>parentId</field-name>
       <column-name>parent_id</column-name>
      
       </cmp-field>
      
       </entity>
      
       </enterprise-beans>
      
      </jbosscmp-jdbc>
      


      jboss.xml
      <?xml version="1.0" encoding="UTF-8"?>
      <!DOCTYPE jboss PUBLIC "-//JBoss//DTD JBOSS 3.0//EN" "http://www.jboss.org/j2ee/dtd/jboss_3_0.dtd">
      
      <jboss>
      
       <enterprise-beans>
      
       <!--
       To add beans that you have deployment descriptor info for, add
       a file to your XDoclet merge directory called jboss-beans.xml that contains
       the <session></session>, <entity></entity> and <message-driven></message-driven>
       markup for those beans.
       -->
      
       <entity>
       <ejb-name>Question</ejb-name>
       <jndi-name>ejb/tutorial/Question</jndi-name>
       <resource-ref>
       <res-ref-name>jdbc/PostgresDS</res-ref-name>
       <jndi-name>java:/PostgresDS</jndi-name>
       </resource-ref>
      
       </entity>
       <entity>
       <ejb-name>QuestionCategory</ejb-name>
       <jndi-name>ejb/tutorial/QuestionCategory</jndi-name>
       <resource-ref>
       <res-ref-name>jdbc/PostgresDS</res-ref-name>
       <jndi-name>java:/PostgresDS</jndi-name>
       </resource-ref>
      
       </entity>
      
       <session>
       <ejb-name>Fibo</ejb-name>
       <jndi-name>ejb/tutorial/Fibo</jndi-name>
      
       </session>
      
       </enterprise-beans>
      
       <resource-managers>
       </resource-managers>
      
      </jboss>
      


      Stacktrace:
      Find failed: java.sql.SQLException: ERROR: Relation "question" does not exist
      
      javax.ejb.FinderException: Find failed: java.sql.SQLException: ERROR: Relation "question" does not exist
      
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:238)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:111)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.execute(JDBCFindByPrimaryKeyQuery.java:108)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntityCommand.execute(JDBCFindEntityCommand.java:49)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntity(JDBCStoreManager.java:571)
       at org.jboss.ejb.plugins.CMPPersistenceManager.findEntity(CMPPersistenceManager.java:299)
       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntity(CachedConnectionInterceptor.java:300)
       at org.jboss.ejb.EntityContainer.find(EntityContainer.java:697)
       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:324)
       at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1043)
       at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
       at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:197)
       at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:214)
       at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:88)
       at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:89)
       at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:61)
       at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:28)
       at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:88)
       at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:267)
       at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:98)
       at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:92)
       at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
       at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:93)
       at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:483)
       at org.jboss.ejb.Container.invoke(Container.java:720)
       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:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:101)
       at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:90)
       at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
       at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:45)
       at org.jboss.proxy.ejb.HomeInterceptor.invoke(HomeInterceptor.java:173)
       at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:85)
       at $Proxy43.findByPrimaryKey(Unknown Source)
       at tutorial.web.ComputeServlet.doPost(ComputeServlet.java:107)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
       at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
       at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
       at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
       at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
       at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
       at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
       at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197)
       at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
       at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
       at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
       at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
       at java.lang.Thread.run(Thread.java:534)


        • 1. Re: Mapping Entity Beans to Database Tables
          ironbird

          No matter the matching between table names and bean name, or field name and column name, all the mapping is done in jbosscmp-jdbc.xml.
          I think the problem is the space in your Question Categories. This don't work, even in sql.

          • 2. Re: Mapping Entity Beans to Database Tables
            erik777

            Try removing or commenting the from your ejb-jar.xml to rule that out, since it isn't needed for findByPrimaryKey(). It looks OK, but doesn't hurt to rule it out since this is a needle in a haystack.

            I'm wondering why it's looking for a relation. That seems really odd since you didn't define any relationships.

            • 3. Re: Mapping Entity Beans to Database Tables
              ironbird

              I don't know which findByPrimaryKey throws an error, but the second one (for QuestionCategory bean) has an error. Your bean abstract schema name is QuestionCategory not QuestionCategorybean.
              It's surprising you have no errors at deployment.
              But as says Erik, you can remove both.

              • 4. Re: Mapping Entity Beans to Database Tables
                abcolson

                Thanks for the feedback, guys. But I am not sure I understand your recommendations.

                Try removing or commenting the from your ejb-jar.xml to rule that out, since it isn't needed for findByPrimaryKey().


                I think maybe all the words didn't come through. I am unclear on what exactly you suggest removing.

                I'm wondering why it's looking for a relation. That seems really odd since you didn't define any relationships.


                I think this might be a slip in terminology. It appears to be either a PostgresQL message or a java message i.e.
                java.sql.SQLException: ERROR: Relation "question" does not exist


                BTW, when I create a view called "question" which is simply a "SELECT * FROM Questions" the table is mapped but then I get field mapping errors (makes sense if it is the same mapping pattern problem)

                I think the problem is the space in your Question Categories. This don't work, even in sql.


                I don't think this is the problem. PostgresQL seems to be case sensitive and translates everything you type in a sql statement to lowercase unless you put it in quotes. Same goes for spaces. So in PostgresQL,

                SELECT * FROM "Question Categories"
                works fine.

                At some point I need to investigate the quoting issue, but I can't do that as long as JBoss keeps looking for "question" and not "questions".


                Your bean abstract schema name is QuestionCategory not QuestionCategorybean.
                It's surprising you have no errors at deployment.


                Do you mean QuestionCategoryBean (upper case B)?

                Actually, I am using XDoclet to generate the descriptors and this is what it generates by default. Do I really need to alter this? I am actually confused on what <abstract-schema-name> is used for so I can't comment on the issue of no deployment errors regarding abstract schema names.

                I did try changing the schema in my Question example to QuestionBean and that didn't seem to help.

                Here are the XDoclet headers for my 2 cases:

                QuestionBean:
                /**
                 * @author colson
                 *
                 * @ejb.bean name="Question"
                 * display-name = "Question EJB"
                 * description = "EJB that manages a Question"
                 * view-type = "remote"
                 * jndi-name = "ejb/tutorial/Question"
                 * cmp-version = "2.x"
                 * primkey-field = "id"
                 *
                 * @ejb.persistence table-name = "Questions"
                 *
                 * @ejb.finder description = "Lookup Question By ID"
                 * signature = "tutorial.interfaces.Question findByPrimaryKey(java.lang.Long)"
                 * result-type-mapping = "Local"
                 * query = "SELECT OBJECT(qb) FROM QuestionBean qb WHERE qb.id = ?1"
                 *
                 * @ejb.resource-ref res-ref-name = "jdbc/PostgresDS"
                 * res-type = "javax.sql.DataSource"
                 * res-auth = "Container"
                 *
                 * @jboss.resource-ref res-ref-name = "jdbc/PostgresDS"
                 * jndi-name = "java:/PostgresDS"
                 *
                 * @jboss.persistence table-name = "Questions"
                 * datasource = "java:/PostgresDS"
                 *
                 * To change the template for this generated type comment go to
                 * Window>Preferences>Java>Code Generation>Code and Comments
                 */
                


                QuestionCategoryBean:

                /**
                 * @author colson
                 *
                 *
                 * @ejb.bean name="QuestionCategory"
                 * display-name = "Question Category EJB"
                 * description = "EJB that manages a Question Category"
                 * view-type = "remote"
                 * jndi-name = "ejb/tutorial/QuestionCategory"
                 * cmp-version = "2.x"
                 * primkey-field = "id"
                 *
                 * @ejb.persistence table-name = "Question Categories"
                 *
                 *
                 *
                 * @ejb.finder description = "Lookup Question Category By ID"
                 * signature = "tutorial.interfaces.QuestionCategory findByPrimaryKey(java.lang.Long)"
                 * result-type-mapping = "Local"
                 * query = "SELECT OBJECT(qcb) FROM QuestionCategoryBean qcb WHERE qcb.id = ?1"
                 *
                 * @ejb.resource-ref res-ref-name = "jdbc/PostgresDS"
                 * res-type = "javax.sql.DataSource"
                 * res-auth = "Container"
                 *
                 * @jboss.resource-ref res-ref-name = "jdbc/PostgresDS"
                 * jndi-name = "java:/PostgresDS"
                 *
                 *
                 *
                 * To change the template for this generated type comment go to
                 * Window>Preferences>Java>Code Generation>Code and Comments
                 */
                


                Thanks for all the tips!
                Tony

                • 5. Re: Mapping Entity Beans to Database Tables
                  ironbird

                   


                  I think maybe all the words didn't come through. I am unclear on what exactly you suggest removing.

                  You don't need to define the findByPrimarykey finder. The container knows how to implement it.


                  I think this might be a slip in terminology. It appears to be either a PostgresQL message or a java message i.e.

                  BTW, when I create a view called "question" which is simply a "SELECT * FROM Questions" the table is mapped but then I get field mapping errors (makes sense if it is the same mapping pattern problem)


                  The only term in your code where "question" is referenced is your entity bean name (and it has nothing to do with an SQL string) and your table name "Question Categories" (which has to do with sql string).
                  The error log talks about a relation but there is no relation in your descriptor snippets. So the error text is useless.


                  I don't think this is the problem. PostgresQL seems to be case sensitive and translates everything you type in a sql statement to lowercase unless you put it in quotes. Same goes for spaces. So in PostgresQL,
                  SELECT * FROM "Question Categories" works fine.

                  I agree with you for the database, but when I try myself on my computer, the container don't put the quotes and throws an error.


                  Do you mean QuestionCategoryBean (upper case B)?

                  No, the EJB spec from SUN says that the where clause in EJB-QL must refers to the abstract schema name of your bean, which is QuestionCategory, not QuestionCategoryBean. So your EJB-QL should be
                  SELECT OBJECT(qcb) FROM QuestionCategory qcb WHERE qcb.id = ?1
                  


                  You think that the error is for your questions bean. I think the error is for your QuestionCategory bean.
                  Try to activate cmp plugin traces to see the generated SQL in jboss log file.
                  Add
                  <category name="org.jboss.ejb.plugins">
                   <priority value="TRACE" class="org.jboss.logging.XLevel"/>
                  </category>
                  

                  in Log4j.xml


                  • 6. Re: Mapping Entity Beans to Database Tables
                    abcolson

                    Hi ironbird,

                    Here's the latest...

                    You think that the error is for your questions bean. I think the error is for your QuestionCategory bean.
                    Try to activate cmp plugin traces to see the generated SQL in jboss log file.


                    The reason I think this is that I am not instantiating a QuestionCategory bean anyplace and I am only instantiating a Question bean once in a test servlet. When I go to that servlet (and after turning on log4j tracing) I get

                    2004-05-31 22:35:09,054 TRACE [org.jboss.ejb.plugins.LRUEnterpriseContextCachePolicy] Resized cache for bean QuestionCategory: old capacity = 1000000, new capacity = 50
                    2004-05-31 22:35:10,095 TRACE [org.jboss.system.Registry] lookup 1524151178=jboss.j2ee:jndiName=ejb/tutorial/Fibo,service=EJB
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=create
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is null
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for create
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx null
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Starting new tx TransactionImpl:XidImpl [FormatId=257, GlobalId=pegasus//5, BranchQual=]
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TxInterceptorCMT: In finally
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.LogInterceptor] End method=create
                    2004-05-31 22:35:10,095 TRACE [org.jboss.system.Registry] lookup 102234528=jboss.j2ee:jndiName=ejb/tutorial/Question,service=EJB
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.LogInterceptor] Start method=findByPrimaryKey
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Current transaction in MI is null
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] TX_REQUIRED for findByPrimaryKey
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Thread came in with tx null
                    2004-05-31 22:35:10,095 TRACE [org.jboss.ejb.plugins.TxInterceptorCMT] Starting new tx TransactionImpl:XidImpl [FormatId=257, GlobalId=pegasus//7, BranchQual=]
                    2004-05-31 22:35:10,105 TRACE [org.jboss.ejb.plugins.EntityInstancePool] Get instance org.jboss.ejb.plugins.EntityInstancePool@d88aa2#0#class tutorial.ejb.QuestionBean
                    2004-05-31 22:35:10,105 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Question#findByPrimaryKey] Executing SQL: SELECT id FROM question WHERE id=?
                    2004-05-31 22:35:10,105 TRACE [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Question#findByPrimaryKey] Set parameter: index=1, jdbcType=BIGINT, value=1
                    2004-05-31 22:35:10,105 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Question#findByPrimaryKey] Find failed
                    java.sql.SQLException: ERROR: Relation "question" does not exist


                    which is what I expected: a PreparedStatement with one parameter.

                    BTW, when I did a grep SELECT server.log I got the following:

                    SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
                     SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
                     SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, ESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
                     SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
                     DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=? 2004-05-31 22:22:21,180 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.QuestionCategory] Entity Exists SQL: SELECT COUNT(*) FROM questioncategory WHERE id=?
                    2004-05-31 22:22:21,320 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.QuestionCategory#findByPrimaryKey] SQL: SELECT id FROM questioncategory WHERE id=?
                    2004-05-31 22:22:21,340 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.Question] Entity Exists SQL: SELECT COUNT(*) FROM question WHERE id=?
                    2004-05-31 22:22:21,380 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Question#findByPrimaryKey] SQL: SELECT id FROM question WHERE id=?
                    2004-05-31 22:22:55,379 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Question#findByPrimaryKey] Executing SQL: SELECT id FROM question WHERE id=?


                    So maybe some of these other sql statements are causing the problem.

                    No, the EJB spec from SUN says that the where clause in EJB-QL must refers to the abstract schema name of your bean, which is QuestionCategory, not QuestionCategoryBean. So your EJB-QL should be
                    Code:

                    SELECT OBJECT(qcb) FROM QuestionCategory qcb WHERE qcb.id = ?1


                    Well, then there may be two bugs with JBoss 3.2.3:

                    Bug #1 Non Recognition Of <abstract-schema-name>

                    When I tested without my re-definition of findByPrimaryKey(),
                    I changed Question descriptor in ejb-jar.xml to
                    <entity >
                     <description><![CDATA[EJB that manages a Question]]></description>
                     <display-name>Question EJB</display-name>
                    
                     <ejb-name>Question</ejb-name>
                    
                     <home>tutorial.interfaces.QuestionHome</home>
                     <remote>tutorial.interfaces.Question</remote>
                    
                     <ejb-class>tutorial.ejb.QuestionBean</ejb-class>
                     <persistence-type>Container</persistence-type>
                     <prim-key-class>java.lang.Long</prim-key-class>
                     <reentrant>False</reentrant>
                     <cmp-version>2.x</cmp-version>
                     <abstract-schema-name>Questions</abstract-schema-name>
                     <cmp-field >
                     <description><![CDATA[]]></description>
                     <field-name>id</field-name>
                     </cmp-field>
                     <cmp-field >
                     <description><![CDATA[]]></description>
                     <field-name>question</field-name>
                     </cmp-field>
                     <cmp-field >
                     <description><![CDATA[]]></description>
                     <field-name>categoryId</field-name>
                     </cmp-field>
                     <cmp-field >
                     <description><![CDATA[]]></description>
                     <field-name>level</field-name>
                     </cmp-field>
                     <primkey-field>id</primkey-field>
                    
                     <resource-ref >
                     <res-ref-name>jdbc/PostgresDS</res-ref-name>
                     <res-type>javax.sql.DataSource</res-type>
                     <res-auth>Container</res-auth>
                     </resource-ref>
                    
                     <!-- Write a file named ejb-finders-QuestionBean.xml if you want to define extra finders. -->
                     </entity>
                    
                    


                    and it didn't key of the abstract schema name.

                    Bug #2 No Overiding Of findByPrimaryKey()

                    When I left in my definition of findByPrimaryKey() (and with the changed schema name) I set the query to

                    SELECT OBJECT(qb) FROM Questions qb WHERE qb.id = ?1


                    and it did not take my version; I would have expected my version to trump the default version by the container.


                    I appreciate your comments and I encourage and welcome any other thoughts while I try more tests and debugging.

                    • 7. Re: Mapping Entity Beans to Database Tables
                      aloubyansky

                      There is no bug wrt <abstract-schema-name>. The problem is the space in the table name. It should be quoted in queries but it's not. This can be considered as a bug in JBossCMP. While it's not fixed you have to use table names that can be used w/o quotes.

                      • 8. Re: Mapping Entity Beans to Database Tables
                        ironbird

                        abcolson,

                        For the abstract schema name, it's your bug, not the container.
                        And it's for the QuestionCategory bean, not the Question bean.

                        • 9. Re: Mapping Entity Beans to Database Tables
                          ironbird

                          After looking carefully at your traces, I agree with you.
                          The error concerns the findByprimaryKey on your QuestionBean.
                          And the SQL string refers to a question table which is your bean name.

                          The datasource mapping for the QuestionBean is missing, and the datasource and datasource mapping for the QuestionCategorybean is also missing.

                          Try to specify the datasource mapping to "PostgreSQL3 OR "PostgreSQL 7.2", depends on your version in the jboss.persistence tag
                          I don't understand you don't have a deployment error. With JBoss 3.2.3, I have a deployment error "Error in jbosscmp-jdbc.xml : datasource-mapping not found" if I do the same thing.

                          • 10. Re: Mapping Entity Beans to Database Tables
                            abcolson

                            Well, I have some news, but I don't know how thrilled I am with it.

                            First, without changing anything WRT my Question bean and descriptors, nor without redeploying, I added a view in my database called "question" which maps to "Questions" and maps the fields like category_id to categoryid.

                            The result...it worked.

                            But it seems like a hack. I just spent half the day cursing about Microsoft and now this. There has to be something else we can do, especially since legacy database schemas will not always conform. And with PostgreSQL, quotes are needed to distinguish not only spaces but case, like "Questions" from "questions".

                            Not to mention, BTW, that you can only read from a view, which, of course, is a problem.

                            In response to loubyansky:

                            There is no bug wrt <abstract-schema-name>. The problem is the space in the table name. It should be quoted in queries but it's not. This can be considered as a bug in JBossCMP. While it's not fixed you have to use table names that can be used w/o quotes.


                            Could you please elaborate on distinguishing a bug with <abstract-schema-name> and JBossCMP? Knowing what I do (which is only at the novice level) I don't see how we can make this distinction with the information at hand, i.e. it is all JBoss to me.

                            Note also that my test case with Question worked without doing anything to QuestionCategory or descriptors, so again I am suspect of this analysis.

                            But if it is a bug with the container, and it is not JBoss's responsibility to fix it, from who and where can I get another one?

                            In response to ironbird:

                            I don't understand you don't have a deployment error. With JBoss 3.2.3, I have a deployment error "Error in jbosscmp-jdbc.xml : datasource-mapping not found" if I do the same thing.


                            Perhaps it is because of my standardjbosscmp-jdbc.xml file

                            <jbosscmp-jdbc>
                            
                             <defaults>
                             <datasource>java:/PostgresDS</datasource>
                             <!--
                             <datasource-mapping>Hypersonic SQL</datasource-mapping>
                             -->
                            
                             <datasource-mapping>PostgreSQL 7.2</datasource-mapping>
                            
                             <create-table>false</create-table>
                             <remove-table>false</remove-table>
                             <read-only>false</read-only>
                             <read-time-out>300000</read-time-out>
                             <row-locking>false</row-locking>
                             <pk-constraint>true</pk-constraint>
                             <fk-constraint>false</fk-constraint>
                             <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
                             <read-ahead>
                             <strategy>on-load</strategy>
                             <page-size>1000</page-size>
                             <eager-load-group>*</eager-load-group>
                             </read-ahead>
                             <list-cache-max>1000</list-cache-max>
                            
                             <unknown-pk>
                             <key-generator-factory>UUIDKeyGeneratorFactory</key-generator-factory>
                             <unknown-pk-class>java.lang.String</unknown-pk-class>
                             <jdbc-type>VARCHAR</jdbc-type>
                             <sql-type>VARCHAR(32)</sql-type>
                             </unknown-pk>
                            
                             <entity-command name="default"/>
                            
                             </defaults>
                            
                             <type-mappings>
                            
                             <type-mapping>
                             <name>PostgreSQL 7.2</name>
                             <row-locking-template>SELECT ?1 FROM ?2 WHERE ?3 FOR UPDATE</row-locking-template>
                             <pk-constraint-template>CONSTRAINT ?1 PRIMARY KEY (?2)</pk-constraint-template>
                             <fk-constraint-template>ALTER TABLE ?1 ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>
                             <auto-increment-template>?1</auto-increment-template>
                             <alias-header-prefix>t</alias-header-prefix>
                             <alias-header-suffix>_</alias-header-suffix>
                             <alias-max-length>32</alias-max-length>
                             <subquery-supported>true</subquery-supported>
                             <true-mapping>TRUE</true-mapping>
                             <false-mapping>FALSE</false-mapping>
                            
                             <function-mapping>
                             <function-name>concat</function-name>
                             <function-sql>(?1 || ?2)</function-sql>
                             </function-mapping>
                             <function-mapping>
                             <function-name>substring</function-name>
                             <function-sql>substring(?1 FROM ?2 FOR ?3)</function-sql>
                             </function-mapping>
                             etc.
                             etc.
                             etc.




                            • 11. Re: Mapping Entity Beans to Database Tables
                              aloubyansky

                              I have an imression that the deployment descriptors you post here are not used. How do you package the EJB JAR and what is its contents?

                              • 12. Re: Mapping Entity Beans to Database Tables
                                abcolson

                                Hi loubyansky,

                                I have an imression that the deployment descriptors you post here are not used. How do you package the EJB JAR and what is its contents?


                                Interesting idea. I hadn't thought of that. Here is a breakdown

                                jar -tf FiboApp.ear
                                
                                META-INF/
                                META-INF/MANIFEST.MF
                                META-INF/application.xml
                                FiboEJB.jar
                                FiboWeb.war



                                jar -tf FiboEJB.jar
                                
                                META-INF/
                                META-INF/MANIFEST.MF
                                META-INF/ejb-jar.xml
                                META-INF/jboss.xml
                                tutorial/
                                tutorial/ejb/
                                tutorial/ejb/FiboBean.class
                                tutorial/ejb/QuestionBean.class
                                tutorial/ejb/QuestionCategoryBean.class
                                tutorial/interfaces/
                                tutorial/interfaces/Fibo.class
                                tutorial/interfaces/FiboHome.class
                                tutorial/interfaces/Question.class
                                tutorial/interfaces/QuestionCategory.class
                                tutorial/interfaces/QuestionCategoryHome.class
                                tutorial/interfaces/QuestionHome.class


                                META-INF/ejb-jar.xml looks fine. I checked specifically the <abstract-schema-name> and it was as I changed it previously to "Questions".

                                Do you see anything wrong here?

                                Thanks
                                Tony



                                • 13. Re: Mapping Entity Beans to Database Tables
                                  aloubyansky

                                  So you are missing jbosscmp-jdbc.xml?

                                  • 14. Re: Mapping Entity Beans to Database Tables
                                    abcolson

                                    Hi loubyansky,

                                    So you are missing jbosscmp-jdbc.xml?


                                    Good Call! For some reason this wasn't making it in.

                                    I put it in and tried it and although it didn't work completely, the generated sql did in fact use the table name "questions" and not "question".

                                    But, as you recall from previous posts, the table should be "Questions", with a big "Q". For some reason, the table name gets converted to lower case. I put quotes in the table name and the quotes do get incorporated into the sql statement, but it still gets converted to lower case.

                                    I'll look into the case issue tomorrow and let you know. If you know, please tell me how to do it.

                                    Thanks
                                    Tony



                                    1 2 Previous Next