EJB 2.1 Error - Find failed - java.sql.SQLException: Column not found

[Alys Landle]

Good morning!

I have an old Java application, using EJB 2.1 and BES application server. It's up and runnig fine today, but we have a project to migrate it to a new architecture (and hopefully, get rid of EJB 2.1), the first step being moving it all to JBoss AS 5.1. In order to do so, first I isolated one EJB module, called Address, and I'm doing tests with it, to get it working. After that, I will try moving the rest of the application.

I don't have experience with EJB (I'm more familiar with Spring or GWT), and I'm facing some troubles while doing this migration to JBoss. This first EJB module I'm trying to migrate, Address, has the following folder structure:

Address
    |_(source code)
    |_META-INF  
        |_ejb-jar.xml
        |_jbosscmp-jdbc.xml
        |_jboss.xml

The ejb-jar.xml is like this:

<?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> <display-name>Address</display-name> <enterprise-beans> ... <session> <display-name>AddressFacade</display-name> <ejb-name>AddressFacade</ejb-name> <local-home>com.project.name.AddressFacadeLocalHome</local-home> <local>com.project.name.AddressFacadeLocal</local> <ejb-class>com.project.name.AddressFacadeBean</ejb-class> <session-type>Stateless</session-type> <transaction-type>Container</transaction-type> <ejb-local-ref> <description /> <ejb-ref-name>ejb/Address</ejb-ref-name> <ejb-ref-type>Entity</ejb-ref-type> <local-home>com.project.name.AddressHome</local-home> <local>com.project.name.Address</local> <ejb-link>Address</ejb-link> </ejb-local-ref> </session> <entity> <display-name>Address</display-name> <ejb-name>Address</ejb-name> <local-home>com.project.name.AddressHome</local-home> <local>com.project.name.Address</local> <ejb-class>com.project.name.AddressBean</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>Address</abstract-schema-name> <cmp-field> <field-name>idAddress</field-name> </cmp-field> ... //other fields

Note: com.project.name is just an alias for the real package.

The jbosscmp-jdbc.xml:

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 3.2//EN" "file:///C:/Folder/jbosscmp-jdbc_3_2.dtd"> <jbosscmp-jdbc> <enterprise-beans> <entity> <ejb-name>Address</ejb-name> <table-name>ADDRESS</table-name> <cmp-field> <field-name>idAddress</field-name> <column-name>ID_ADDRESS</column-name> </cmp-field> ...

The jboss.xml:

<?xml version='1.0' ?> <!DOCTYPE jboss PUBLIC "-//JBoss//DTD JBOSS 3.2//EN" "http://www.jboss.org/j2ee/dtd/jboss_3_2.dtd"> <jboss> <enterprise-beans> <session> <ejb-name>AddressFacade</ejb-name> <jndi-name>AddressFacade</jndi-name> <ejb-local-ref> <ejb-ref-name>ejb/AddressFacade</ejb-ref-name> <local-jndi-name>local/AddressFacade</local-jndi-name> </ejb-local-ref> </session> <entity> <ejb-name>Address</ejb-name> <jndi-name>Address</jndi-name> <local-jndi-name>local/Address</local-jndi-name> </entity> </enterprise-beans> </jboss>

Another note: this sources are like 15 years old. The team responsible for it is no more here, and I have no ideia why they structured the project like this.

Inside the Facade (well, the class with *Facade name pattern), the code calling the findByPrimaryKey method it's:

Address address =  getHome().findByPrimaryKey(id);

And, retrieving the home is like this:

return (AddressHome) ServiceLocator.getInstance().getEjbLocalHome("java:comp/env/ejb/Address"); 

But, when I run the project and try to make a simple search, I get the error:

ERROR [Address#findByPrimaryKey] Find failed  
java.sql.SQLException: Column not found: T0_ADDRESS.ID_ADDRESS in statement [SELECT t0_Address.ID_ADDRESS FROM ADDRESS t0_Address WHERE t0_Address.ID_ADDRESS=?]  
    at org.hsqldb.jdbc.Util.throwError(Unknown Source)  
    at org.hsqldb.jdbc.Util.throwError(Unknown Source)  
    at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)  
    at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)  
    at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:465)  
    at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:453)  
    at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:243)  
    at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:227)  
    at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:144)  
    at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.execute(JDBCFindByPrimaryKeyQuery.java:155)  
    at org.jboss.ejb.plugins.cmp.jdbc.JDBCFindEntityCommand.execute(JDBCFindEntityCommand.java:61)  
    at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.findEntity(JDBCStoreManager.java:604)  
    at org.jboss.ejb.plugins.CMPPersistenceManager.findEntity(CMPPersistenceManager.java:315)  
    at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.findEntity(CachedConnectionInterceptor.java:234)  
    at org.jboss.ejb.EntityContainer.findSingleObject(EntityContainer.java:1112)  
    at org.jboss.ejb.EntityContainer.findLocal(EntityContainer.java:689)  
    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.invocation.Invocation.performCall(Invocation.java:386)  
    at org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(EntityContainer.java:1139)  
    at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:106)  
    at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(EntitySynchronizationInterceptor.java:203)  
    at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invokeHome(CachedConnectionInterceptor.java:187)  
    at org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInterceptor.java:106)  
    at org.jboss.ejb.plugins.EntityInstanceInterceptor.invokeHome(EntityInstanceInterceptor.java:137)  
    at org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockInterceptor.java:76)  
    at org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityCreationInterceptor.java:45)  
    at org.jboss.ejb.plugins.CallValidationInterceptor.invokeHome(CallValidationInterceptor.java:56)  
    at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:125)  
    at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:350)  
    at org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT.java:161)  
    at org.jboss.ejb.plugins.SecurityInterceptor.process(SecurityInterceptor.java:230)  
    at org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInterceptor.java:205)  
    at org.jboss.ejb.plugins.security.PreSecurityInterceptor.process(PreSecurityInterceptor.java:99)  
    at org.jboss.ejb.plugins.security.PreSecurityInterceptor.invokeHome(PreSecurityInterceptor.java:88)  
    at org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:132)  
    at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(ProxyFactoryFinderInterceptor.java:107)  
    at org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:527)  
    at org.jboss.ejb.Container.invoke(Container.java:1046)  
    at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invokeHome(BaseLocalProxyFactory.java:362)  
    at org.jboss.ejb.plugins.local.LocalHomeProxy.invoke(LocalHomeProxy.java:133)  
    at $Proxy1253.findByPrimaryKey(Unknown Source)  
    at com.project.name.AddressFacadeBean.getByPrimaryKey(AddressFacadeBean.java:326)  
    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.invocation.Invocation.performCall(Invocation.java:386)  
    at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:228)  
    at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:156)  
    at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:173)  
    at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:63)  
    at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:121)  
    at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:350)  
    at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:181)  
    at org.jboss.ejb.plugins.SecurityInterceptor.process(SecurityInterceptor.java:228)  
    at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:211)  
    at org.jboss.ejb.plugins.security.PreSecurityInterceptor.process(PreSecurityInterceptor.java:97)  
    at org.jboss.ejb.plugins.security.PreSecurityInterceptor.invoke(PreSecurityInterceptor.java:81)  
    at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)  
    at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138)  
    at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:650)  
    at org.jboss.ejb.Container.invoke(Container.java:1029)  
    at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:436)  
    at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:103)  
    at $Proxy1270.getByPrimaryKey(Unknown Source)  
    at com.project.name.AddressDelegateBean.getByPrimaryKey(AddressDelegateBean.java:81)  
    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.invocation.Invocation.performCall(Invocation.java:386)  
    at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:228)  
    at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:156)  
    at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:173)  
    at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:63)  
    at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:121)  
    at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:350)  
    at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:181)  
    at org.jboss.ejb.plugins.SecurityInterceptor.process(SecurityInterceptor.java:228)  
    at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:211)  
    at org.jboss.ejb.plugins.security.PreSecurityInterceptor.process(PreSecurityInterceptor.java:97)  
    at org.jboss.ejb.plugins.security.PreSecurityInterceptor.invoke(PreSecurityInterceptor.java:81)  
    at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:205)  
    at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:138)  
    at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:650)  
    at org.jboss.ejb.Container.invoke(Container.java:1029)  
    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.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:157)  
    at org.jboss.mx.server.Invocation.dispatch(Invocation.java:96)  
    at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)  
    at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)  
    at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:668)  
    at org.jboss.invocation.local.LocalInvoker$MBeanServerAction.invoke(LocalInvoker.java:169)  
    at org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:118)  
    at org.jboss.invocation.InvokerInterceptor.invokeLocal(InvokerInterceptor.java:209)  
    at org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:195)  
    at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:61)  
    at org.jboss.proxy.ejb.SecurityContextInterceptor.invoke(SecurityContextInterceptor.java:64)  
    at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:68)  
    at org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSessionInterceptor.java:112)  
    at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:101)  
    at $Proxy314.getByPrimaryKey(Unknown Source)  
    at com.project.name.AddressClient.getByPrimaryKey(AddressClient.java:65)  
    at com.project.name.SearchAddressAction.searchAddress(SearchAddressAction.java:68)  
    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 com.project.name.ProjectAction.execute(ProjectAction.java:311)  
    at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)  
    at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)  
    at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)  
    at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)  
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)  
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)  
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)  
    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.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:829)  
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:598)  
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)  
    at java.lang.Thread.run(Thread.java:619)  
16:15:56,078 ERROR [ProjectException]   

I can assure the column does exists in the database. This project is up and working fine today on the BES server, and, no changes in the database or the sources have been made (just configuration files).

Thank you all for any help, and please apologise misspellings. English is not my native language.

[Alys Landle]

Strange... the console log above is not showing the "_" character properly, but I wrote it...

The error I'm getting is "Column not found: T0_ADDRESS.ID_ADDRESS". There is a "_" between the words, it's not an empty space. I simply can't get this to show properly on my previous post, sorry.

Anyway... some progress (I think). I found this link in JBoss Community, and, I noted my jbosscmp-jdbc.xml doesn't have a primary key defined. So, I removed the field:
<cmp-field> <field-name>idAddress</field-name> <column-name>ID_ADDRESS</column-name> </cmp-field>

And tried to replace by this:
<unknown-pk> <unknown-pk-class>java.lang.Long</unknown-pk-class> <field-name>idAddress</field-name> <column-name>ID_ADDRESS</column-name> </unknown-pk>

And now, the error I get is this:

11:45:39,921 ERROR [Address#findByPrimaryKey] Find failed
java.sql.SQLException: ORA-00904: "T0_ADDRESS"."IDADDRESS": invalid identifier

The "_" is missing, and, since in the table it's "ID_ADDRESS", I get the Oracle exception. I believe I'm missing some configuration, or have done some wrong one, but I can't see what is it yet...