File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JBoss/WildFly and the fly likes JBOSS EJBQL generate wrong SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » JBoss/WildFly
Bookmark "JBOSS EJBQL generate wrong SQL" Watch "JBOSS EJBQL generate wrong SQL" New topic
Author

JBOSS EJBQL generate wrong SQL

Ivan
Greenhorn

Joined: Jul 25, 2005
Posts: 10
Hi.
I use Lomboz eclipse 3.1 (Build-20050722) and mySQL4.1 to develop my EJB application. I have created a cmp bean in eclipse and can deploy to jboss (version 4.0.2) successfully. However, there is an exception when I call the "findAll()" function in CMP thru' a JSP program.

11:20:09,734 ERROR [User#findAll] Find failed
java.sql.SQLException: Table 'irs.xuser' doesn't exist
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)

I haven't defined any sql setting like 'irs.xuser' or 'xuser' in both Eclipse or Jboss. I don't know where it comes from. Seems it is generated by xdoclet or EJBQL. Do I mis-set some eclipse or jboss setting ? Is it a bugs of JBOSS4 or what else ? Please help.
Note :
"irs" is the mySQL database name of my application and 'user' is the valid table name of my bean

Below please find my program details,

[UserBean]
.....
import ....
/**
* @ejb.bean name="User"
* display-name="Name for User"
* description="Description for User"
* jndi-name="ejb/User"
* type="CMP"
* cmp-version="2.x"
* view-type="both"
*
* @ejb.persistence table-name = "user"
*
* @ejb.transaction type= "Required"
*
* @ejb.finder
* signature="java.util.Collection findAll()"
* query="SELECT OBJECT(u) FROM User AS u"
*
*/
public abstract class UserBean implements EntityBean {

private EntityContext _context;

public UserBean() {
super();
// TODO Auto-generated constructor stub
}
........


[ejb-jar]
.....
<!-- Entity Beans -->

<![CDATA[Description for User]]>
<display-name>Name for User</display-name>

<ejb-name>User</ejb-name>

tutorial.interfaces.UserHome
tutorial.interfaces.User
<local-home>tutorial.interfaces.UserLocalHome</local-home>
tutorial.interfaces.UserLocal

<ejb-class>tutorial.ejb.UserCMP</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>tutorial.interfaces.UserPK</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>User</abstract-schema-name>
<cmp-field >
<![CDATA[Getter for CMP Field userid]]>
<field-name>userid</field-name>
</cmp-field>
<cmp-field >
<![CDATA[Getter for CMP Field password]]>
<field-name>password</field-name>
</cmp-field>
<cmp-field >
<![CDATA[Getter for CMP Field group]]>
<field-name>group</field-name>
</cmp-field>


<query-method>
<method-name>findAll</method-name>
<method-params>
</method-params>
</query-method>
<ejb-ql><![CDATA[SELECT OBJECT(c) FROM User AS c]]></ejb-ql>

<!-- Write a file named ejb-finders-UserBean.xml if you want to define extra finders. -->

.......

[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>

java:/MySqlDS
<datasource-mapping>mySQL</datasource-mapping>
<create-table>false</create-table>

<enterprise-beans>

<ejb-name>User</ejb-name>
<cmp-field>
<field-name>userid</field-name>
<column-name>userid</column-name>
</cmp-field>
<cmp-field>
<field-name>password</field-name>
<column-name>password</column-name>
</cmp-field>
<cmp-field>
<field-name>group</field-name>
<column-name>group</column-name>
</cmp-field>

</enterprise-beans>
</jbosscmp-jdbc>

[mysql-ds.xml]

<local-tx-datasource>
<jndi-name>MySqlDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/irs</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password>ABC1234</password>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>

<type-mapping>mySQL</type-mapping>

</local-tx-datasource>


Thanks
Ivan
Per Nyfelt
Greenhorn

Joined: Aug 13, 2005
Posts: 1
Reason:
"user" is a keyword in some databases and hence in the list of reserved words in JBoss which makes it not possible to use as table name in the default configuration.

Solution:
MySQL does not have user as a keyword so it is safe to change the behavior.
Remove the reserved keyword by editing your conf/standardjbosscmp-jdbc.xml file.
At the bottom there is a section called "reserved-words" where the keyword "user" can be removed.

More info:
JBoss forums
Ivan
Greenhorn

Joined: Jul 25, 2005
Posts: 10
It's working now.
Thank you very much !!!

Ivan
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JBOSS EJBQL generate wrong SQL