java - Hibernate generates wrong inner join query for MySQL -

i'm developing first project jpa, mysql database , hibernate 4.3.8 jpa provider in spring 4 web project.

in spring config, set database , dialect:

hibernatejpavendoradapter hjpava = new hibernatejpavendoradapter(); hjpava.setdatabase(database.mysql); hjpava.setdatabaseplatform("org.hibernate.dialect.mysql5dialect"); 

i try query gives me trouble:

typedquery<kundedto> query = entitymanager.createquery("select new zdb.dto.kundedto(k.id, k.firma.firmenname, k.regnr, k.kategorie)  kunde k k.id = :id", kundedto.class); 

this sql hibernate generates:

select kunde0_.`id` col_0_0_, firma1_.`firmenname` col_1_0_, kunde0_.`regnr` col_2_0_, kunde0_.id_kategorie col_3_0_  `zdb_e`.`kunde` kunde0_, `zdb_e`.`firma` firma1_  inner join `zdb_e`.`kategorie` kategorie2_ on kunde0_.id_kategorie=kategorie2_.`id`  kunde0_.id_firma=firma1_.`id` , kunde0_.`id`=1; 

note there no parentheses on inner join!

running statement results in following error:

com.mysql.jdbc.exceptions.jdbc4.mysqlsyntaxerrorexception:  unknown column 'kunde0_.id_kategorie' in 'on clause' 

the reason exception detailed here: mysql-unknown-column-in-on-clause

when add parentheses , inner join clauses , run statement directly against database works:

select kunde0_.`id` col_0_0_, firma1_.`firmenname` col_1_0_, kunde0_.`regnr` col_2_0_, kunde0_.id_kategorie col_3_0_  (`zdb_e`.`kunde` kunde0_, `zdb_e`.`firma` firma1_ ) inner join `zdb_e`.`kategorie` kategorie2_ on  ( kunde0_.id_kategorie=kategorie2_.`id` )  kunde0_.id_firma=firma1_.`id` , kunde0_.`id`=1; 

so, how can persuade hibernate generate query that?

update: here entities

kunde

@entity public class kunde implements serializable {     private static final long serialversionuid = 1l;      @id     @generatedvalue     private integer id;      private integer regnr;      @onetoone(optional=false)     @joincolumn(name="id_firma", nullable = false)     private firma firma;      @onetoone(optional=false)     @joincolumn(name="id_kategorie", nullable = false)     private kategorie kategorie;      @onetoone(optional=false)     @joincolumn(name="id_lieferregion", nullable = false)     private lieferregion lieferregion;      // getters , setters.... } 

firma

@entity @table(name = "firma") public class firma implements serializable {     private static final long serialversionuid = 1l;     @id     @generatedvalue     private integer id;      @column(name="firmenname")     private string firmenname;     @column(name="uid")     private string uid;      @onetoone(optional=false)     @joincolumn(name="id_anschrift", nullable = false)     private anschrift anschrift;      @onetomany(mappedby="id_firma", fetch=fetchtype.eager)     private list<person> personen;      public firma() {         personen = new arraylist<person>();     }     // getters , setters.... } 

kategorie

@entity public class kategorie implements serializable {     private static final long serialversionuid = 1l;      @id     @generatedvalue     private integer id;      private integer nummer;     private string bezeichnung;      public kategorie() {     }      public kategorie(int kategorieid, int kategorienummer, string kategoriebezeichnung) {         this.id = kategorieid;         this.nummer = kategorienummer;         this.bezeichnung = kategoriebezeichnung;     }     // getters , setters.... } 

db schemas

create table kategorie      (id integer not null auto_increment,      nummer integer not null,      bezeichnung varchar(100) not null,     primary key (id),             unique (nummer, bezeichnung)     );  create table firma     (id integer not null auto_increment,      firmenname varchar(50) not null,     uid varchar(20) not null,     url varchar(100) not null,     id_anschrift integer not null,     id_logo integer,     primary key (id),     unique (uid),     unique (firmenname),     unique (id_anschrift),     constraint firma_fk1 foreign key (id_anschrift) references anschrift (id),     constraint firma_fk2 foreign key (id_logo) references logo (id));  create table kunde      (id integer not null auto_increment,      regnr integer not null,     id_kategorie integer not null,     id_firma integer not null,     id_benutzer integer,     id_lieferregion integer not null,     primary key (id),      unique (regnr, id_kategorie),     unique (id_firma),     unique (id_benutzer),     constraint kunde_fk1 foreign key (id_firma) references firma (id),     constraint kunde_fk2 foreign key (id_benutzer) references benutzer (id),     constraint kunde_fk3 foreign key (id_kategorie) references kategorie (id),     constraint kunde_fk4 foreign key (id_lieferregion) references lieferregion (id)     ); 

upon further testing

the problem missing parentheses on clause.

going directly against db:

select k.id, k.regnr, f.firmenname (kunde k, firma f) join kategorie kat on k.id_kategorie = kat.id k.id = 1 , k.id_firma = f.id; 

works!

select k.id, k.regnr, f.firmenname kunde k, firma f join kategorie kat on k.id_kategorie = kat.id k.id = 1 , k.id_firma = f.id; 

doesn't work: unknown column 'k.id_kategorie' in 'on clause'

why need parentheses on clause?
, how can make hibernate put them in?

i didn't work jpql, did jgr suggested in comments , wrote own sql native query:

string sql = "select k.id, f.firmenname, k.regnr, kat.id, kat.nummer, kat.bezeichnung (zdb_e.kunde k, zdb_e.firma f) join zdb_e.kategorie kat on k.id_kategorie = kat.id k.id = :id , k.id_firma = f.id;"; query query =  entitymanager.createnativequery(sql, schemaname), "kundenlisterow"); query.setparameter("id", id); return (kundedto) query.getsingleresult(); 

for mapping pojo kundedto added @sqlresultsetmapping kunde entity:

@entity @sqlresultsetmapping(name = "kundenlisterow", classes = {     @constructorresult(targetclass = kundedto.class,             columns = {                     @columnresult(name = "id"),                     @columnresult(name = "firmenname"),                     @columnresult(name = "regnr"),                     @columnresult(name = "kat.id"),                     @columnresult(name = "kat.nummer"),                     @columnresult(name = "kat.bezeichnung")                     }             )   }) public class kunde implements serializable {      .... } 

and corresponding constructor in dto:

public class kundedto {     int id;     string firmenname;     kategorie k;     int regnr;      public kundedto(int id, string firmenname, int regnr, int kategorieid, int kategorienummer, string kategoriebezeichnung) {         this.id = id;         this.firmenname = firmenname;         this.regnr = regnr;         this.k = new kategorie(kategorieid, kategorienummer, kategoriebezeichnung);     }     public kundedto() {     } } 

as said, works. not ideal. jpql use typedquery , not have deal resultsetmapping.

of course, still don't know why jpql doesn't work. :-)