Queries, nulls, and multi-column custom types

ethanw · **Joined:** Thu Feb 08, 2007 8:23 pm **Posts:** 2

Hibernate version: 3.2.1.ga

Name and version of the database you are using: DB2 UDB 8.2

I have a datatype that I would like to map using a custom user type. The datatype maps to multiple columns, and for a particular value one more of the multiple columns may be null.

As an example (for simplicity):

Code:

public class Data {
    private Long aNumber;
    private String aString;
    public Data(Long x) {
        aNumber = x;
        aString = null;
    }
    public Data(String x) {
        aNumber = null;
        aString = x;
    }
}

public class MyObject {
    private long id;
    private Data data;
    ...
}

create table MyTable ( id bigint not null, myNum bigint, myStr varchar(10) )

This works reasonably well using a UserType to load and store the Data object into the myNum and myStr columns.

The problem I encounter is with queries. The following query:

Code:

Data aData = ...
Criteria c = session.createCriteria(MyObject.class);
c.add(Restrictions.eq("data", aData));
c.list();

produces SQL like:

Code:

select * from MyTable where myNum = ? and myStr = ?

when what I really need is:

Code:

select * from MyTable where myNum is null and mySTr = ?

Big Question: Is there a way to make hibernate queries generate SQL with "is null" used for those columns that are null?

Ive tried making my custom type as a UserType, CompositeUserType, Type, or AbstractComponentType with no success.

dharmendra.pandey · **Posted:** Fri Feb 09, 2007 12:31 am

Hi ethanw

try

Data aData = ...
Criteria c = session.createCriteria(MyObject.class);
c.add(Restrictions.eq("data.aString ", value));
c.add(Restrictions.isNull("data.aNumber "));

c.list();

ethanw · **Joined:** Thu Feb 08, 2007 8:23 pm **Posts:** 2

I've considered that, but the code would end up something like:

Code:

Data aData = ...
Criteria c = session.createCriteria(MyObject.class);
if (aData.getAString() != null) {
    c.add(Restrictions.eq("data.aString", aData.getAString()));
} else {
    c.add(Restrictions.isNull("data.aString"));
}
if (data.getANumber() != null) {
    c.add(Restrictions.eq("data.aNumber", aData.getANumber()));
} else {
    c.add(Restrictions.isNull("data.aNumber"));
}
c.list();

Since my actual object maps to four columns you can see how any queries involving them would become unwieldy with that approach.

Hopefully I can find a better way.

--
Ethan