Applying convert-null attribute in Service Builder to persist NULL and NOT NULL into data types in databases

Service Builder is a cool tool to automate the creation of interfaces and implementation classes for database persistence, local services and remote services, like find, create, update, and delete operations on the database. In general, the Service Builder is a code generator. Using an XML descriptor, it generates:

  • Java Beans
  • SQL scripts for database tables creation;
  • Hibernate Configuration;
  • Spring Configuration;
  • Axis Web Services and
  • JSON JavaScript Interface

The service classes include persistence services, local services, remote services, models and their implementation classes. The service builder supports following data types and database data types mapping.

Data types                    Database data types

boolean                        BOOLEAN
int, Integer, short         INTEGER
long                               LONG
float, double                 DOUBLE
String                            VARCHAR (<4000), STRING (=4000), TEXT (>4000)
Date                              DATE

Supported primitive types include “boolean”, “int”, “short”, “long”, “float” and “double”; and data types “String” and “Date” got supported , too.

Default value of above data types could be NULL or specific value. Thanks to Brian who introduced “convert-null” element in service builder (LEP-1353 - Add convert-null element to service generator). The convert-null value specifies whether or not the column value is automatically converted to a non null value if it is null. Currently (revision 71166) this feature only applies if the type value is String. This is particularly useful if your entity is referencing a read only table or a database view so that Hibernate does not try to issue unnecessary updates. The default setting of this convert-null attribute is true.

This article will address how to apply convert-null element on other data types like primitive types. For example, you may expect Long value and Integer value to be null value to table, rather than value 0. Mostly importantly you may need option to save Long value and Integer value either null or 0. Specially, following two use cases will be covered.

  • Use case A: default non-NULL values for primitive types, String and Date
  • Use case B: default NULL value for primitive types, String and Date.

An example

Consider following XML in service.xml.

<?xml version="1.0"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.0.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_0_0.dtd">

<service-builder package-path="com.liferay.sampleservicebuilder">
    <namespace>SSB</namespace>
    <entity name="Foo" uuid="true" local-service="true" remote-service="true">

        <!-- PK fields -->

        <column name="fooId" type="long" primary="true" />

        <!-- Group instance -->

        <column name="groupId" type="long" />

        <!-- Audit fields -->

        <column name="companyId" type="long" />
        <column name="userId" type="long" />
        <column name="userName" type="String" />
        <column name="createDate" type="Date" />
        <column name="modifiedDate" type="Date" />

        <!-- Other fields -->

        <column name="field0" type="Integer" convert-null="false" />
        <column name="field1" type="String" />
        <column name="field2" type="boolean" />
        <column name="field3" type="int" />
        <column name="field4" type="Date" />
        <column name="field5" type="String" convert-null="false" />
        <column name="field6" type="long" convert-null="false" />
        <column name="field7" type="long" convert-null="true" />
        <column name="field8" type="short" convert-null="false" />
        <column name="field9" type="short" convert-null="true" />
        <column name="fieldA" type="int" convert-null="false" />
        <column name="fieldB" type="boolean" convert-null="false" />
        <column name="fieldC" type="float" convert-null="true" />
        <column name="fieldD" type="float" convert-null="false" /> 
        <column name="fieldE" type="double" convert-null="true" />
        <column name="fieldF" type="double" convert-null="false" />   
       
        <!-- Order -->

        <order by="asc">
            <order-column name="field1" />
        </order>
    </entity>
</service-builder>

As shown in above code, it expects default value of “field0”, “field5”, “field6”, “field8”, “fieldA”, “fieldB”, “fieldD” and “fieldF” should be NULL. And for the rest, convert NULL value of String to “”, convert null value of Boolean to false, convert NULL value of Integer, Short and Long to 0; convert NULL value of Float to 0.0F; convert NULL value of Double to 0.0D. Especially, data type Date always has default value NULL.

Solution - applying convert-null element on data types

Above requirements can be satisfied by following two steps.

  • Mapping covert-null value into database SQL.

for example, generated SQL table:

create table SSB_Foo (
    uuid_ VARCHAR(75) null,
    fooId LONG not null primary key,
    groupId LONG,
    companyId LONG,
    userId LONG,
    userName VARCHAR(75) null,
    createDate DATE null,
    modifiedDate DATE null,
    field0 INTEGER null,
    field1 VARCHAR(75) null,
    field2 BOOLEAN,
    field3 INTEGER,
    field4 DATE null,
    field5 VARCHAR(75) null,
    field6 LONG null,
    field7 LONG,
    field8 INTEGER null,
    field9 INTEGER,
    fieldA INTEGER null,
    fieldB BOOLEAN null,
    fieldC DOUBLE,
    fieldD DOUBLE null,
    fieldE DOUBLE,
    fieldF DOUBLE null
);

  • Mapping column-types with convert-null="false" into different ORM object types with default value NULL. 

By default, Liferay has provided following ORM object types with default non-null value.

ShortType – mapped into data type short - default value 0
IntegerType - mapped into data type int - default value 0
LongType - mapped into data type long - default value 0
BooleanType - mapped into data type boolean - default value false
FloatType - mapped into data type float - default value 0.0f
DoubleType - mapped into data type double - default value 0.0D

That is, introduce new mapping feature in Service Builder that it would have capabilities to map column types into above ORM object types only if convert-null="false" was not set on columns.

Testing Results

After applying new mapping feature in Service Builder, building service based on above service.xml, deploying portlet and adding new row, you would see NULL value was add as default for the columns with setting convert-null="false". More exactly,  default value of “field0”, “field5”, “field6”, “field8”, “fieldA”, “fieldB”, “fieldD”, and “fieldF” is NULL, persisted in databases.

Summary

The convert-null value specifies whether or not the column value is automatically converted to a non null value if it is null (Use case A and Use case B) in Service Builder. This feature applies if the type value is String or primitive types like int, short, long, boolean, float and double.

By the way, this feature will be available for 6.1 (LPS-14863 - applying convert-null element to service generator). Definitely this feature could be available for 5.2 and 6.0 as a fix patch. 

Blogs
Hi Jonas,

you wrote "Definitely this feature could be available for 5.2 and 6.0 as a fix patch." - Do you mean for EE Editions only or also as community plugin or patch?

Thanks.
Denis
Hi Denis, Thanks.

The fix patch is available for 5.2 and 6.0, both CE and EE.

For 6.0.5 CE, you can find it at http://www.liferay.com/community/forums/-/message_boards/message/7244098.

Which version are you using?
Great step forward!

Will it also be possible to set a default value? For example if you want an integer-column to be 1 by default?
Hi Puj, Thanks.

The feature you mentioned is not supported so far for now. I believe that it (ability to set a default value) is possible.

Would you be able to share the details?
Hi Jonas,

sure. Sometimes, especially if you migrate from an old database, the columns have a default value other than 0 which is supported by most databases when you define a column.

For example imagine a workflow process, where the user suggest something (e.g. an entity) and you have to accept or reject it. The new entity is assigned the value of "1", and if it is rejected it is assigned "0", otherwise "2" for acceptance.
How it is possible now, is that you have to set this default value ("1") for the attribute of your model in "entityModelImpl.java".
It would be much nicer and cleaner if you can set this default value in service.xml. Something like:

<column name="mycolumn" type="int" default-value="1" />
Hi Puj, Thanks. This feature (constraint default value) would be useful.

Maybe it is good to add an element (constraint-default-value) like "constraint-default-value="1" and implement the same in Service-Builder. Is this a good idea?

In addition, Vilmos Kozma mentioned other features - "constraint-not-null", "foreign-key", explicit "max-length" - at https://www.liferay.com/community/forums/-/message_boards/message/7354601. These features would be useful, too.

Your suggestions?
sounds great Jonas!
This would service builder more flexible and would save us some work while implementing the models.
Hi Puj, Thanks. These new features are addressed in details at

http://issues.liferay.com/browse/LPS-15136

Hope that it could receive more comments / suggestions before implementing
I would like to ask that whenever we run service builder it generates

<generate class="assigned" /> for all the entities. How can I change this and the service builder starts generateing the code as <generate class="increment" />

Regards,
Asif
Hi Asif, Apologies to miss your post.

Yes, you can change the code to "<generate class="increment" />" with your own implementation. This is doable, but not recommended.

Hope that it helps,

Jonas
This convert-null capability looks slightly close to my issue related to create trigger statements in tables.sql (in my case for a MySQL database). Is there a way to place CREATE TRIGGER statements in tables.sql by ServiceBuilder?
I can put it into tables.sql manually and I get a code completion help from ServiceBuilder (in Eclipse Indigo) for the syntax of creating such a trigger statement but it is not exceuted on Liferay start which means that the tables are created while the trigger is not. See http://www.liferay.com/de/community/forums/-/message_boards/message/11944604 as well.
Hi Jonas,
Can we get a patch for this feature for 6.2.10.1 EE GA1 ? We'd like to use this feature but its not available in this EE version.
Hi,
Not sure if this was address but what if the primary key has null values? Is there a way to make a column both a primary key as well as having null value? When I do, I get errors for datasets that have null value. How to make service.xml work with columns with null data that I want to be part of my compound primary key?

Thanks!
Hi Jonas,

I am facing the same issue. I am using liferay 6.2. But neither <column name="parentId" type="long" convert-null="false" /> solve my issue nor <column name="parentId" type="Long" convert-null="false" /> this. It always saving 0.