File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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 REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Products » JBoss/WildFly
Bookmark "JBOSS EJBQL generate wrong SQL" Watch "JBOSS EJBQL generate wrong SQL" New topic

JBOSS EJBQL generate wrong SQL


Joined: Jul 25, 2005
Posts: 10
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(
at com.mysql.jdbc.MysqlIO.sendCommand(
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
at com.mysql.jdbc.Connection.execSQL(

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,

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() {
// TODO Auto-generated constructor stub

<!-- Entity Beans -->

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



<cmp-field >
<![CDATA[Getter for CMP Field userid]]>
<cmp-field >
<![CDATA[Getter for CMP Field password]]>
<cmp-field >
<![CDATA[Getter for CMP Field group]]>

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


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 3.0//EN" "">










Per Nyfelt

Joined: Aug 13, 2005
Posts: 1
"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.

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

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

Don't get me started about those stupid light bulbs.
subject: JBOSS EJBQL generate wrong SQL
It's not a secret anymore!