CODEWITHSUNDEEP
xmlhibernatehql
Angel
Angel

Reputation: 57

How to have a select statement in HQL that will return all rows regardless of null values

I have an object that contains a one-to-one mapping.

Customer.hbm.xml:

<class name="com.asi.shared.Customer" table="Customer">
    <id name="id" column="CustomerID">
         <generator class="assigned"/>
    </id>
    <property name="customerName" column="CustomerName" />
    ...
    <one-to-one name="corp" class="com.asi.shared.Corp"/>
 </class>

Corp.hbm.xml

<class name="com.asi.shared.Corp" table="Corp">
    <id name="id" column="CustomerID">
         <generator class="assigned"/>
    </id>
    <property name="customerName" column="CustomerName" />
    <property name="storeNumber" column="StoreNumber"/>
    <property name="corpCustomerId" column="CorpCustomerId"/>
</class>

I would like to run the following query and have all the rows in the costumer table returned.

    select customerName, id, support, corp.corpCustomerId
    from com.asi.shared.Customer ORDER BY customerName

However, it only returns the rows that have something in the corp table. Not every customer will have something in the corp table, I don't mind having corp.corpCustomerId = null.

What am I doing wrong?

Upvotes: 0

Views: 189

Answers (1)

JB Nizet
JB Nizet

Reputation: 692261

When you select customer.corp.corpCustomerId, you're implicitely doing an inner join between both tables, whereas what you want is a left join. Rewrite your query as is:

select customer.customerName, customer.id, corp.corpCustomerId
from Customer customer
left join customer.corp corp
order by customer.customerName

Side note: your code would be more readable if you didn't have all these redendancies in the properties names. No need to call the property customerName. It's a property of the Customer class, so customer.name is sufficient. Same for corp.corpCustomerId, which would be easier to read as corp.customerId.

Upvotes: 1

Related Questions