Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Problem with EJB-QL and primary-keys

 
Ranch Hand
Posts: 109
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I'm using JBoss 4.0.2, and I try to access to a HSQLDB-table with a finder-method.

The name of the HSQLDB-table is RecipePos and it has the following fields:

RECIPEID (CMP-Integer-field and part of the primary-key)
RECIPEPS (CMP-Integer-field and part of the primary-key)
RECIPEPOSPK (Primary-Key-compound-class)
SIDE (CMP-String-field)
WORKDESC (CMP-String-field)
MACHINE (CMP-String-field)
COUNT (CMP-String-field)
COLOR (CMP-String-field)
RECIPEDESC (CMP-String-field)
COMMENT (CMP-String-field)

This is my primary-key-compound-class:

public class RecipePosPK implements Serializable {
public java.lang.Integer recipeId;
public java.lang.Integer recipePs;

public RecipePosPK() { }

public boolean equals(Object obj) {
if (this.getClass().equals(obj.getClass())) {
RecipePosPK other = (RecipePosPK) obj;
return (this.recipeId.intValue() == other.recipeId.intValue()
&& this.recipePs.intValue() == other.recipePs.intValue());
}

return false;
}

public int hashCode() {
return(("" + recipeId.intValue()
+ recipePs.intValue()).hashCode());
}
}

I want to search for all recipe-positions with a specific ID-number. Thus, I have created the following finder-method:

@ejb.finder signature="java.util.Collection
findRecipe(java.lang.Integer recipe)"
query="SELECT OBJECT(p) FROM RecipePos AS p
WHERE p.recipeId = ?1"

I call this finder-method in a session-bean:

Collection col = home.findRecipe(recipeID);

But it fails with an error-message:

Log failed: CausedByException is:
Unexpected token in statement
[SELECT recipeId, recipePs, recipePosPK, side,
workDesc, machine, count, color, recipeDesc, comment)
FROM RECIPEPOS WHERE (recipeId = ? AND recipePs = ?)
OR (recipeId = ? AND recipePs = ?)
OR (recipeId = ? AND recipePs = ?)
OR (recipeId = ? AND recipePs = ?)
OR (recipeId = ? AND recipePs = ?)
...
OR (recipeId = ? AND recipePs = ?)

I have tried many EJB-QLs to read data from the table RecipePos, but the "Unexpected token"-error occurs in all of my EJB-QLs. Even "SELECT OBJECT(p) FROM RecipePos AS p" does not work. JBoss supplements every where-clause with the primary-key-fields "(recipeId = ? AND recipePs = ?)", and I don't know why.

EJB-QLs on other tables without a primary-key-compound-class works fine. For example, there is a table named RecipeHead with a simple Integer-ID as primary-key, and I can access this table through EJB-QL without problems. But RecipePos is my first table with a primary-key-compound-class, and there is no EJB-QL that will work with RecipePos.

Could anybody please help me, whether I have missed something to handle the access on tables with primary-key-compound-classes?

Regards,
Oliver
 
Oliver Rensen
Ranch Hand
Posts: 109
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I suppose, there is no error in the EJB-QL, because I have tried the query "SELECT OBJECT(p) FROM RecipePos AS p WHERE p.color = '999'", and if there is no record with the color 999, the finder-method doesn't throw an exception. But when I create one record with color 999, the finder-method breaks:

TransactionRolledbackLocalException in method:
public abstract java.lang.Integer
entitybeans.interfaces.RecipePosLocal.getRecipePs(),
causedBy: java.sql.SQLException: Unexpected token in statement
[SELECT recipePosPK, side, workDesc, machine, count, color, recipeDesc, comment
FROM RECIPEPOS WHERE (recipeId=? AND recipePs=?)]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement

There is only one "(recipeId=? AND recipePs=?)" in the WHERE-clause, and this indicates, that JBoss knows that there is only one record with the color 999. Hence the query should work correct, but JBoss seems to have problems to replace the placeholders ? in the PreparedStatement with the real values. But how can I influence this behavior?

recipeId and recipePs are Integers, and I have replaced it through Strings, but this doesn't work, too.

I assume, there is only a little bagatelle in my program which is not correct, but I cannot find the error.
 
Oliver Rensen
Ranch Hand
Posts: 109
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have solved the problem. One column in my table is named with count. Count is not a reserved word in EJB-QL, but it is reserved in SQL, and I have used it many many times before. After 14 years experience with SQL I fall into this simple trap.
 
See ya later boys, I think I'm in love. Oh wait, she's just a tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic