Using a comparison predicate with a null value

In a relational database, you can define columns that allow null values or that require every row to have a value. The equivalent in a PolicyCenter application is to define entity properties that allow null values or that require every instance to have a value.

Selecting instances based on null or non-null values

Use the compare method with the Equals or NotEquals operator to select entity instances based on null or non-null values. The following Gosu code returns all Person instances where the birthday is unknown. 

uses gw.api.database.Query

var query = Query.make(Person)
query.compare(Person#DateOfBirth, Equals, null)

The following Gosu code returns all Address instances where the first address line is known. 

uses gw.api.database.Query

var query = Query.make(Address)
query.compare(Address#AddressLine1, NotEquals, null)

Handling of null values in a range comparison

If one of the two comparison values that you pass to the between method is null, Gosu performs a simple comparison that is equivalent to calling the compare method. If the first comparison value is null, Gosu performs a less-than-or-equal comparison on the second value. If the second comparison value is null, Gosu performs a greater-than-or-equal comparison on the first value. For example, the following two statements are equivalent:

query.between(Company#Name, null, "Business")
query.compare(Company#Name, LessThanOrEquals, "Business")

How null values get in the database

Null values get in the database only for entity properties that the Data Dictionary does not define as non-null. To assign null values to entity instance properties, use the special Gosu value null. The following Gosu code sets an int property and a java.util.Date property to null on a new entity instance. 

var aPerson = new Person()

aPerson.DateOfBirth = null      // Set a java.util.Date to null in the datbase
aPerson.NumDependents = null    // Set an int to null in the database

After the bundle with the new Person instance commits, its DateOfBirth and NumDependents properties are null in the database.

Blank and empty strings become null in the database

To assign null values to String properties, use the special Gosu value null or the empty string (""). If you set the property of an entity instance to a blank or empty string, PolicyCenter coerces the value to null when it commits the instance to the database.

The following Gosu code sets three String properties to different values.

var anAddress = new Address()

anAddress.AddressLine1 = " "    // Sets a String to null in the database
anAddress.AddressLine2 = ""     // Sets a String to null in the database
anAddress.AddressLine3 = null   // Sets a String to null in the database

After the bundle with the new Address instance commits, all three address lines are null in the database. Before PolicyCenter commits String values to the database, it trims leading and trailing spaces. If the result is the empty string, PolicyCenter coerces the value to null.

Note that for non-null String properties, you must provide at least one non-whitespace character. You cannot work around a non-null requirement by setting the property to a blank or empty string.

Controlling whether PolicyCenter trims whitespace before committing string properties

You can control whether PolicyCenter trims whitespace before committing a String property to the database with the trimwhitespace column parameter in the data model definition of the String column. Columns that you define as type="varchar" trim leading and trailing spaces by default.

To prevent PolicyCenter from trimming whitespace before committing a String property to the database, add the trimwhitespace column parameter in the column definition, and set the parameter to false. The XML text of a column definition that does not trim whitespace looks like the following:

<column 
  desc="Primary email address associated with the contact." 
  name="EmailAddress1" 
  type="varchar">
  <columnParam name="size" value="60"/>
  <columnParam name="trimwhitespace" value="false"/>
</column>

The parameter controls only whether PolicyCenter trims leading and trailing spaces. You cannot configure whether PolicyCenter coerces an empty string to null.

See also