Newbie question - subselect mixing HQL & SQL? Or better

hoist2k · **Joined:** Wed Dec 13, 2006 3:21 pm **Posts:** 4

What I want to do should be so simple, yet I'm pulling out my hair here.

I have a Person object and a Person table, all mapped very nicely.

I also have a Nickname table.

Code:

int ID (PK)
String nickname
int personId  (FK)

I don't have a Nickname object. Nickname is just a string.

I want to lookup a Person object from a nickname. My constraint is that Person does NOT know about nicknames (i.e. Nickname is not a member variable). I guess you'd call this a unidirectional many-to-one relationship?

So what I want to do is this sort of mixing of HQL & SQL:

Code:

find( "from Person where id=(SELECT personId FROM nickname_table WHERE nickname='tom' );

Any suggestions? Can I do this without making more mapping files and more objects?

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

You need to map all your tables in Hibernate. Have a look at using reveng to do that, I find it really nice when your starting out on a new project.
Once you do that your code would look like:

Code:

find("
    from Person 
    where Person.nickname.nickname = 'tom'
");

Have a look at this page for HQL help: http://www.hibernate.org/hib_docs/refer ... ryhql.html

damnhandy · **Posted:** Sat Apr 28, 2007 9:20 am

If mapping the Nickname table is not an option, you could map the property using a secondary table to join in the nickname value. If you're using annotations, it would look something like this:

Code:

@Table(name="person_table")
@SecondaryTables({
    @SecondaryTable(name="nickname_table", pkJoinColumns={
        @PrimaryKeyJoinColumn(name="person_id",  referencedColumnName="id")
    ),

})
public class Person {
   @Id
   @Column(name="person_id")
   private int Id;
   
   @Column(table="nickname_table",name="nickname)
   private String nickName;
   
}

N ote: my syntax maybe off slightly, but this is the general idea. What's going on here is that we're joining in both the person_table & nick_name table into 1 class. This way, nickname appears as a property of the Person and you HQL can be written as if the value was part of the person_table. So once you have that, you could write an HQL query such that:

Code:

find( "from Person as p where p.nickName = 'tom')

Hibernate will generate the proper SQL to join the 2 tables together. This type of this is also possible (and a bit more clear IMHO) using the XML mappings. Have a look at the <join> element in the docs for more details and some of the Hibernate JUnits.

Another option is to use a complete native SQL loader query or a formula.

Ryan-