How this SQL Query in hbm.xml file can be written

Question

I am trying to run a SQL query in Hibernate. I am using its documentation. Because of some unknown values i am trying to do inside the Source code.

See this below SQL-Query configuration. How to re-write it in source code itself!! I tried this

personList = session.createSQLQuery("SELECT person.NAME, person.AGE, person.SEX, address.STREET, address.CITY, address.STATE, address.ZIP FROM person JOIN address WHERE person.ID = address.PERSON_ID").addEntity(Person.class).addEntity(Address.class).list();

What i am trying to do with the above query is, it should map person.ID & PERSON_ID. If it matches, then fetch the other columns in the ADDRESS[STREET, CITY, STATE, ZIP] table with the PERSON table columns[NAME, AGE, SEX].

In JDBC the columns of resultset, for the above query looks like

NAME, AGE, SEX, STREET, CITY, STATE, ZIP

But its not working it says, address columns in the query were not found. Is there any syntax error in my version of query!!

Query Declaration in mapping file;

<sql-query name="personsWith">
<return alias="person" class="eg.Person"/>
<return-join alias="address" property="person.mailingAddress"/>
SELECT person.NAME AS {person.name},
       person.AGE AS {person.age},
       person.SEX AS {person.sex},
       address.STREET AS {address.street},
       address.CITY AS {address.city},
       address.STATE AS {address.state},
       address.ZIP AS {address.zip}
FROM PERSON person
JOIN ADDRESS address
    ON person.ID = address.PERSON_ID
</sql-query>

Thanks

Answer 1

It does not work because of your misuse of AddEntity:

This piece of code:

personList = session
  .createSQLQuery("SELECT ...")
  .addEntity(Person.class)
  .addEntity(Address.class).list();

addEntity does add a parameter to the query, of an entity type. For instance, you could do this:

personList = session
  .createSQLQuery("from person where address = :address"
  .addEntity("address", myAddess);

I'm not sure if you could do the same entity mapping in code. I had to read the documentation.

---

Edit:

You could easily find all the persons with addresses like this:

session.createQuery("from Person p where p.address is not null");

or, if there isn't an adress property on person:

session.createQuery("select distinct a.Person from Address");

assumed that you could have several persons on the same address.

Answer 2

Two posible causes for not finding the address columns:

First, in SQL you have to write the name of the table, not the entity name.

Second, your JOIN sentence could be invalid at SQL. There are a few ways to implement a join. I'll take the direct approach (select from both table and stating the join at the where clause).

Suposing that the person entity maps a table called "TABLE_PERSON", and address maps table "TABLE_ADDRESS", a valid query would look as follows:

SELECT person.NAME, person.AGE, person.SEX, 
     address.STREET, address.CITY, address.STATE, address.ZIP 
FROM TABLE_PERSON person, TABLE_ADDRESS address 
WHERE person.ID = address.PERSON_ID

One more point. Checking the hibernate documentation, I have found this example:

List cats = sess.createSQLQuery("select {cat.*}, {kitten.*} from cats cat, cats kitten 
where kitten.mother = cat.id").
     setResultSetMapping("catAndKitten").list();

So, maybe the problem is not at the query itself, but on the resultSet Mapping you are using, and the way to reference the fields.

Let's suppose this mapping (from the Hibernate doc):

<resultset name="personAddress">
    <return alias="person" class="eg.Person"/>
    <return-join alias="address" property="person.mailingAddress"/>
</resultset>

Then, as the exaple states, your query should define the columns between curly brackets ({}), and using the alias you have defined at the mapping:

personList = session.createSQLQuery(
             "SELECT {person.NAME}, {person.AGE}, {person.SEX}, "+
            "{address.STREET}, {address.CITY}, {address.STATE}, {address.ZIP} "+
            "FROM TABLE_PERSON person, TABLE_ADDRESS address "
            "WHERE person.ID = address.PERSON_ID"
        ).setResultSetMapping("personAddress")
         .list();

Please, tell me if any of this examples works.