I am refurbishing an existing utility which imports university candidate and school data from files into a database. It must be able to receive data regarding a given applicant on separate occasions and be able to update the persistent record with the latest state as represented by the file record. For many record types the primary keys are generated by the application/database, and not derived from file information.

When a record has been persisted from a file on a previous occasion I am having difficulty consistently identifying when that persistent record is equivalent to a record in another file. The inconsistency derives from my inability to find a way to use HQL to correctly evaluate an expression when on of the operands could be null.

For example, consider the following (simplified) record exists in the COLLEGE table of the database from a previous execution of

the utility:

clkey collegename collegecode gender
1 St John's STJ null

Here is my java class:

public class Collegecode implements Serializable {
private Long clkey;
private String collegecode;
private String gender;
private String collegename;
/* methods... */
}

And my mapping file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="College" table="COLLEGE">
<id column="clkey" name="clkey" type="java.lang.Long">
<generator class="native"><param name="sequence">seq_pk_collegecode</param></generator>
</id>
<property column="collegecode" length="3" name="collegecode" type="java.lang.String"/>
<property column="gender" length="1" name="gender" type="java.lang.String"/>
<property column="collegename" length="20" name="collegename" type="java.lang.String"/>
</class>
</hibernate-mapping>

Next time I execute the utility I seek to identify using a query rather than s.load, as my file data does not contain the 'clkey' information (please keep any comments on the choice of primary key secondary to the issues outlined below). From the file I get:

String colcd = "STJ";
String colnm = "St John's";
String colgen = null;

Now my attempt to find the persistent record:

Query q = sess.createQuery("from College c where c.collegecode=:colcd and c.collegename=:colnm and c.gender=:colgen")
.setParameter("colcd", colcd)
.setParameter("colnm", colnm)
.setParameter("colgen", colgen);
List lst = q.list();
if (lst.size() == 0) {
// make a new College with the file details for persisting
} else {
// existing record correctly identified.
}

The existing record is not correctly identified presumably because c.gender=:colgen does not evaluate to true. That clause must be substituted for one which tests if 'c.gender is null'. However, does this mean I have to modify my query depending on the values of the attributes for comparison? I tried using 'where ... and (c.gender=:colgen or ((c.gender is null) and (:colgen is null)))' to no avail. Perhaps there is another subclause which will account for the possibilities?

Thanks in advance for any help.

Regards,
Callum Haig