-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 
  Previous topic | Next topic 
Author Message
mlozano
 Post subject: SQL Join with annotations
PostPosted: Mon Sep 15, 2008 8:10 am 
Newbie

Joined: Mon Jun 02, 2008 4:03 am
Posts: 2
I have 2 Classes "Personas" and "Empleado"

The ID of the class Empleado has a composite id formed by (nip,year,tipo):

Code:
@Entity
@Table(name="per_empleados")
public class Empleado implements java.io.Serializable {
    // Composite ID   
   @Id
   @Column(name="nip")
    private Long nip;
    @Id
    @Column(name="year")
   private short year;
    @Id
    @Column(name="tipo")
    private String tipo;
   
    @Column(name="departamento",length=2)
    private String departamento;
   
......


And the class Persona has as id (nip), in the database per_empleado has a FK to per_personas by the nip column.

Code:
@Entity
@Table(name="per_personas")
public class Persona implements java.io.Serializable {

   @Id
   @Column(name="nip")
    private Long nip;
   
   @Column(name="nombre",length=40,nullable=false)
    private String nombre;

........



When i need to do a SELECT like this in my DAO "PersonaDAO"

Code:
       
......
String sql = "FROM es.upv.moscosos.modelo.Persona as per" +
                  " WHERE Upper(per.nombre) LIKE :nombre "+
                      " AND EXISTS( FROM es.upv.moscosos.modelo.Empleado as emp "+
                                 " WHERE per.nip = emp.nip AND" +
                                       " emp.year= 2008) ";


       ArrayList<Persona> result = (ArrayList<Persona>) session.createQuery(sql).setString("nombre", nombre.toUpperCase()).list();   
....



An exception is launched

Code:
Caused by: org.hibernate.QueryException: could not resolve property: nip of: es.upv.moscosos.modelo.Empleado [FROM es.upv.moscosos.modelo.Persona as per WHERE Upper(per.nombre) LIKE :nombre  AND EXISTS( FROM es.upv.moscosos.modelo.Empleado as emp  WHERE per.nip = emp.nip AND emp.year= 2008) ]
   at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:67)
   at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:61)
   at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1385)
   at org.hibernate.hql.ast.tree.FromElementType.getPropertyType(FromElementType.java:302)
   at org.hibernate.hql.ast.tree.FromElement.getPropertyType(FromElement.java:407)
   at org.hibernate.hql.ast.tree.DotNode.getDataType(DotNode.java:589)
   at org.hibernate.hql.ast.tree.DotNode.prepareLhs(DotNode.java:264)
   at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:211)
   at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:117)
   at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:113)
   at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:750)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1216)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4041)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3528)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1762)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1687)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.collectionFunctionOrSubselect(HqlSqlBaseWalker.java:4253)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3968)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1762)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1690)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
   at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:251)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1650)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:342)
   at $Proxy12.createQuery(Unknown Source)
   at es.upv.moscosos.dao.PersonaDAO.buscaPersonasBBDD(PersonaDAO.java:84)



What is the problem ?
Top
 Profile  
 
mlozano
 Post subject: My Solution for this problem
PostPosted: Tue Sep 23, 2008 6:04 am 
Newbie

Joined: Mon Jun 02, 2008 4:03 am
Posts: 2
I am workin with a legacy database, then i need to map a composite Id. The solution was found in the "Java Persistence with Hiberante" book(http://www.hibernate.org/beamto.php?act ... beam_id=13).

First i created a new class EmpleadoPK for the composite Id.


Code:

@Embeddable
public class EmpleadoPK implements java.io.Serializable {
   
   @Column(name="nip")
    private Long nip;

    @Column(name="year")
   private short year;

    @Column(name="tipo")
    private String tipo;

   ......



Then i removed the fields of the composite ID of Empleado and replaced by an object of EmpleadoPK

Code:
@Entity
@Table(name="per_empleados")
public class Empleado implements java.io.Serializable {
   
    @EmbeddedId
   @AttributeOverrides({
        @AttributeOverride(name="nip", column=@Column(name="nip")),
        @AttributeOverride(name="year",column=@Column(name="year")),
        @AttributeOverride(name="tipo",column=@Column(name="tipo"))
   })
   private EmpleadoPK empleadoPK;
  .....



And the SQL in my DAO looks like...

Code:
....

        String sql = "FROM es.upv.moscosos.modelo.Persona as per" +
                  " WHERE Upper(per.nombre) LIKE :nombre "+
                      " AND EXISTS( FROM es.upv.moscosos.modelo.Empleado as emp "+
                                 " WHERE per.nip = emp.empleadoPK.nip AND" +
                                       " emp.empleadoPK.year = 2008) ";

.....
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 2 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.