Bringing data type BigDecimal into Service-Builder

 Any application dealing with currency should really use the Java class BigDecimal.  BigDecimal - java.math.BigDecimal - immutable, arbitrary-precision signed decimal numbers. A BigDecimal consists of an arbitrary precision integer unscaled value and a 32-bit integer scale. If zero or positive, the scale is the number of digits to the right of the decimal point. If negative, the unscaled value of the number is multiplied by ten to the power of the negation of the scale. The value of the number represented by the BigDecimal is therefore (unscaledValue × 10^-scale). The BigDecimal class provides operations for arithmetic, scale manipulation, rounding, comparison, hashing, and format conversion.  

Request for support of BigDecimal in Service-Builder has been there for around many years. Now it is time to implement. right? Service-builder supports following Java data types and database data types mapping.

• 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. The previous blogs post discussed how to apply convert-null element on primitive types. This article will address how to bring data type BigDecimal into Service-Builder.

An example

Consider following service.xml.

 <?xml version="1.0"?>

<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.1.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_1_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="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="BigDecimal" />

<column name="field7" type="BigDecimal" constraint-precision="8" constraint-scale="3" />

<column name="field8" type="BigDecimal" constraint-precision="10" constraint-scale="4" />

<!-- Order -->

<order by="asc">

<order-column name="field1" />

</order>

<!-- Finder methods -->

<finder name="Field2" return-type="Collection">

<finder-column name="field2" />

</finder>

<!-- References -->

<reference package-path="com.liferay.portlet.asset" entity="AssetEntry" />

<reference package-path="com.liferay.portlet.asset" entity="AssetTag" />

</entity>

</service-builder>

 As shown in above code, columns “field6”, “field7”, “field8” have data type “BigDecimal” and columns “field7” and “field8” have constraint precision (8, 10) and scale (3,4). 

Solution proposal

The following are proposed steps to support data-type BigDecimal in Service-Builder.

• Add two constraint elements for the data type BigDecimal in Service-Builder DTD.

constraint-precision: M - the maximum number of digits (the precision);

constraint-scale: D - the number of digits to the right of the decimal point (the scale)

The SQL standard requires that the precision of NUMERIC(M,D) be exactly M digits. For DECIMAL(M,D), the standard requires a precision of at least M digits but permits more. In MySQL, DECIMAL(M,D) and NUMERIC(M,D) are the same, and both have a precision of exactly M digits.  For example, column salary could be presented in database SQL as follows.

salary NUMERIC(5,2)

As shown in above code, constraint-precision has value 5 (M=5) and constraint-scale has value 2 (D=2).

• Add a data type called BigDecimal in service-builder DTD

For example, column salary could be specified in service.xml as follows.

<column name="salary" type="BigDecimal" constraint-precision=”5” constraint-scale=”2” convert-null="false" />

• Add an object type in Liferay portal core called com.liferay.portal.dao.orm.hibernate.BigDecimalType, where specifies default value;
• Map BigDecimal into NUMERIC(M,D) as part of SQL scripts
• Map BigDecimal into object type com.liferay.portal.dao.orm.hibernate.BigDecimalType if convert-null is set to true; or map BigDecimal into object type org.hibernate.type.BigDecimalType, if convert-null is set to false.
• Map BigDecimal into Java class java.math.BigDecimal when generating models and services.

In above steps, we could make data-type BigDecimal available in Service-Builder. 

Results

Solution proposal could be presented as a fix patch. After applying this fix patch, you can generate services, models and SQL scripts with new data type BigDecimal based on above service.xml. The following are generated SQL scripts.

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,

field1 VARCHAR(75) null,

field2 BOOLEAN,

field3 INTEGER,

field4 DATE null,

field5 VARCHAR(75) null,

field6 NUMERIC(5, 2) null,

field7 NUMERIC(8, 3) null,

field8 NUMERIC(10, 4) null

);

Summary

As you can see, data type BigDecimal and two elements constraint-precision and constraint-scale are ready in Service-Builder. And generated services, models and SQL scripts will support data type BigDecimal as well. 

What’s next? Check Service-Builder improvement.

[Jay Patel]

Nice Feature...Thanks Jonas for sharing this

[Steffen Schuler]

Is this theory or does this approach work? with PostGIS, there are other datatypes, I'am very interested in - Geometry, Polygon, Point... these Datatypes are accessible through HibernateSpatial extension. With your proposal, I should be able to integrate them with ServiceBuilder - but before I try it, I would like to know, if your suggestion works like described : )

[Jonas Yuan]

Hi Steffen, Thanks. This is a new feature for LR 6.1. :-)

You are right that date types Geometry, Polygon, Point would be very useful, specially for postGIS. It is possible that these data types could be added as well in Service-Builder. Well, Liferay will go postGIS. right?

Do you have detailed requirements?

[Steffen Schuler]

PostGIS provides very stable and powerful geospatial functions. I managed to use PostGIS through JPA 2.0 (with the help of a session customizer) JPA 2.0 provides a nice QueryAPI, allowing you to call the geospatial functions e.g.
...
List<POI> pois = eventJPAC.getEntityManager()
.createNamedQuery("POI.findByDistanceInMeters")
.setParameter(1, "Point(10.3548617 50.9681888)")
.setParameter(2, 5000)
.getResultList();
...
@Entity
@NamedNativeQueries({
@NamedNativeQuery(name = "POI.findByDistanceInMeters",
query = "select * from poi where ST_Distance_Sphere(" +
" ST_Centroid(event.location), ST_GeomFromText(?1, 4326))" +
" < ?2 order by poi.date", resultClass=POI.class)
})
public class POI implements Serializable { ... }

As you can see, there are methods like ST_Distance_Sphere and ST_Centroid called, which are provided by PostGIS. Accessing these functions are crucial.

Somehow, it seems to be a similar issue when accessing MongoDB from within a service - you need to customize a lot - at least replace the DAO of the recommended code. I mention MongoDB because it provides simple geospatial queries, too... and of course MySQL is somewhere in between PostGIS and MongoDB, when it comes to GIS data processing.

Maybe we need a kind of plugin mechanism for extending the service builder : )

... according to your post, I just build the 6.1 version myself and therefore, your approach should work. Is there a complete code example available?

Regards, Steffen

[Jonas Yuan]

Thanks, Steffen. This is useful info.

By the way, how to present data types Geometry, Polygon, Point in database? should these data types be mapped into BLOCK - database type?

[Hitesh Methani]

Thanks Jonas,
Looking forward to use this feature, as we too are handling currency fields manually which is tedious, bigdecimal type would help a lot.

Regards,
Hitesh Methani

[sascha hartleb]

There are some confusing sentences in this post:

"...Service-builder supports following Java data types and database data types mapping.
[...] TEXT (>4000)..."

The basic service builder never has or had support for the type TEXT. You mention that in the sentence after that, but why do you list TEXT(>4000)?

..."Add a data type called BigDecimal in service-builder DTD"

Where? There is no list of datatypes in the service-builder_x_x_x.dtd.

Where do I have to map the BigDecimal-object to sql and back?

Also BigDecimal should be a build-in datatype of liferay (as I see the request exists for at least 15 months). It's essential for financial calculations.

[Jonas Yuan]

Hi Sascha, thanks for your feedback.

This feature is not implemented yet.

[Puj Z]

Hey,

I am using 6.2 and in the dtd there are no signs of constraint-precision or constraint-scale for "column". Wasn't this feature supposed to be implemented for 6.1? Have I missed anything? Is there another approach now for having decimal type for columns?

[Ghita Benabdellah]

Hi JONAS,

I need to add values of double with null values.
I'm using liferay 6.2 ga6 & I've tried your solution but it didn't work.
I've also tried following the steps that you defined in this post : https://web.liferay.com/fr/community/forums/-/message_boards/message/6811878
but when I copied the patch lps9022-servicebuilder-ce6050-portal-impl-jdk5.jar in $TOMCAT_WEBAPPS/ROOT/WEB-INF/lib folder and tried to rebuild services, it just got stuck upon deleting jar and nothing after that happened.
Is this patch supported in liferay 6.2 ?

Thank you for your reply