aspose 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 Java 8 in Action this week in the Java 8 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
 
 
subject: JBOSS EJBQL generate wrong SQL
 
Similar Threads
CMP 2.0 problem in Jboss 4.0.2
CMP beans not entering data in database
CMP bean Deployment Problem with DataSource (mysql)
Can't deploy EJB to JBOSS after adding '@ejb.finder' clause
Help me to remove this Error in Entity Bean