Hello,
i'm using JPA with hibernate and i have a problem in a query with null fields. The problem is that in my results not happear tuples with fields with null values.

My problem is like this:
Person is a triple (Car car, Boat boat, dog dog)

String query="select new util.PersonReport(p.id, p.car.model, p.boat.speed, p.dog.color) from Person p";

the problem is that car, boat and dog are nullable and when one of this is null the intere tuple not happear in my results.
I want ask how i can avoid this problem.

Thanks
RM

You'll need to explicitly do a "left join" to the car, boat and dog properties. Implicit joins (dot notation) use "inner join" which excludes null references. So, your query should be something like:

Thanks
very clear and useful!

It run!

Hello,
Can you tell me how i can create a join in the case in which i have a N-to-1 relationship?
For example Car-Person is a Nto1 relationship.
How i can select all the cars of my database showing also the cars that don't have owner (person)? I have to fill a Bean like this:

String query="select new util.MyCar(c.id, c.model, p.name, p.surname) from Car c, Person p ??? ";

??? -> what kind of join can i use?

Please help me i have to solve this problem, i know that exsist a solution...
Thanks
RM

It's the same thing... assuming that your Car has an "owner" attribute that is a Person.

Hi to all,

I have the same problem of rmarcello and I try to apply the nordborg's solution but it dosen't work.
I have two tables:

Person (person_id, name, surname) and Car (car_id, model, colour, owner)

where owner in Car table is a FK to person_id field of Person table.
Person and Car are linked with a N-to-1 relationship (a person can have zero or more cars and a car is owned only by a person).

I try to execeute this HQL query:


but I get a Null Pointer Exception.

What's wrong in my query?
Thanks to all that can help me.

You have not defined the 'c' alias, though I don't know if it is related to the NullPointerException or not.

Sorry nordborg I've a mistake in my query.

My previous query was:


but I can not use the field p.car beacuse from person table there is no way to reach car table.
The unique way to get this information is to start my query from car table but I've to use a right join beacuse I want keep
all people's info even if they have no car. I tried to execute this query


but the obtained results are not exact because I can not see the information about people that have no cars.
Finally the questions are:

1) Can you use the right join starting from car table, and what is the correct manner to do this?
2) Can I start my query from table person? (Hibernate have created an HashSet of car)

Thank you very much!

In this stage it would really help if you posted your mappings or code+annotations. It is hard to guess what you have defined and what you have not defined. You can also try to enable logging of the SQL that Hibernate generates for your query and see if that makes sense or not. How to enable logging is described here: http://docs.jboss.org/hibernate/stable/ ... on-logging



Strange... in your very first post you are using p.car.model and when I showed you how to use left join you answered that it worked.

Nordborg the right join solution works perfectly! I had a stupid mistake in the select field and the obtained results was not appear like as expected. I want to thank you for your meaningful support.