Hello guys,
I've stumbled upon an error that's quite strange, at least for me.
I have two tables in an mysql database and I want to do a query on them with an inner join.
My code is
Code:
String sql = "SELECT {star.*} "+
"FROM stars as {star} INNER JOIN starvotes as {starvotes} on {star}.id={starvotes}.starId "+
"WHERE {starvotes}.categoryId="+categoryId+" "+
"ORDER BY {starvotes}.value DESC "+
"LIMIT 0, 5";
List stars = session.createSQLQuery(sql,
new String[] {"star", "starvotes"},
new Class[] {Star.class, Starvote.class} ).list();
the error i get is
Code:
Bad SQL grammar [null] in task 'HibernateAccessor'; nested exception is java.sql.SQLException: Column 'id1_' not found.
The two tables are not linked through FKs in the xml mapping file. And don't want to do it since the mapping file and domain classes are generated automatically (and I cannot generate Fks for my mysql version).
Is the fact that there is no relation between starvotes and star the problem or is my sql malformed ? Or inner joins are not properly handled in hibernate sql queries ?
Note that an equivalent sql query entered on the sql console works without problems:
Code:
SELECT s.*
FROM stars s inner join starvotes sv on s.id=sv.starId
WHERE sv.categoryId=1
ORDER by sv.value DESC
LIMIT 0, 5
The strange part is that the generated sql:
Code:
SELECT star.id as id0_, star.active as active0_, star.fullName as fullName0_, star.realName as realName0_,
star.birthdate as birthdate0_, star.birthplace as birthplace0_, star.biography as biography0_,
star.sex as sex0_, star.type as type0_, star.picture as picture0_, star.quickFacts as quickFacts0_, star.deathdate as deathdate0_,
star.deathReason as deathRe13_0_, star.starpagesId as starpag14_0_, star.allpostersId as allpost15_0_
FROM stars as star INNER JOIN starvotes as starvotes on star.id=starvotes.starId
WHERE starvotes.categoryId=1
ORDER BY starvotes.value DESC
LIMIT 0, 5
contains no id1_ column !? and works if I type it into the sql console.
If I change in my sql query
Code:
{star}.id with {star.id}, {starvotes}.categoryId with {starvotes.categoryId}, etc.
I get another error:
Code:
"Unknown column 'categoryId1_' in 'where clause'"
Which seems a legitimate error since the where clause now is:
Code:
WHERE categoryId1_=1
Which is the correct way? {table.column} or {table}.column ?
I'm sure I've missed something obvious, can anyone point it out please ?
I use: hibernate 2.1.2 with tomcat 4.1.29 and 1.4.2 .
Thx,
Chris
Login
FAQ
Search