HQL error with subquery for a related entity

Question

I've been having problems trying to retrieve a related entity using an HQL subquery. I have three entities: a Customer entity, an Account entity and an additional entity which I called Relation. Initially I had a many to many relationship between Customer and Account, but later I had to add this new entity called Relation because I needed to add additional information to the relation between Customer and Account. Since Customer is no longer mapped to any of the rest of the classes here in the problem I am going to leave it out.

Address (only the interesting parts)

@Entity(name = "Address")
@Table(name = "Address")
public class Address extends {
.....
     @OneToMany(fetch = FetchType.LAZY, mappedBy = "address",
     orphanRemoval = true)
     private Set relations = new HashSet();
    @Column()     private String number = null;
.....
}

Relation (only the interesting parts)

@Entity(name = "Relation")
@Table(name = "CustomerAccount")
@IdClass(Relation.RelationId.class)
public class Relation {
....
    @Id
    @ManyToOne(fetch = FetchType.LAZY)
    private Account account = null;
    @Id
    @ManyToOne(fetch = FetchType.LAZY)
    private Customer customer = null; ....
}

What I am trying to obtain is the following:

Given a specific Customer (using its Id) and a specific Account number, obtain the Account and its Relation to the specified customer (if there is any).

This is the hql query I am trying to execute to accomplished this:

select account, (select relation from account.relations inner join relation.customer customer where customer.id = :id) from Account account where account.number = :number

The resulting sql is the following:

select account0_.Id as col_0_0_,
     (select
       (relations1_.AccountId,
       relations1_.CustomerId)
    from
     CustomerAccount relations1_
    inner join
     Customer customer2_
      on relations1_.CustomerId=customer2_.UserId
    inner join
     OlsUser customer2_1_
      on customer2_.UserId=customer2_1_.Id
    where
     account0_.Id=relations1_.AccountId
      and customer2_.UserId=?) as col_1_0_,
    account0_.Id as Id0_,
    account0_.number as AccountN2_0_,
    account0_.Active as Active0_,
    account0_.Application as Applicat4_0_,
    account0_.Description as Descript8_0_,
    account0_.LastUpdated as LastUpda9_0_,
   from
    Account account0_
   where
    account0_.number = ?

I believe there is something wrong with the subquery in hql, and at last the exception

Caused by: java.lang.NumberFormatException: For input string: "(2, 3)"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
    at java.lang.Long.parseLong(Long.java:410)
    at java.lang.Long.parseLong(Long.java:468)
    at org.h2.value.Value.convertTo(Value.java:811) 


... 61 more

Which I relate to the specific part of the subquery but not sure what its happening underneath. Any help would be greatly appreciated.

JB Nizet JB Nizet · Accepted Answer · 2011-10-28T22:24:20

First of all. Address is in fact Account, right?

To answer your question : your query is much more complex than needed. All you need is

select account, relation from Relation relation
inner join relation.account account
where account.number = :number
and relation.customer.id = :customerId

HQL error with subquery for a related entity

2 Answers