Persistence

This chapter introduces the Persistence Configuration Editor and the Persistence API of Axon.ivy. The persistence framework is based on the Java Persistence API, aka JPA) and provides support for storing and loading business objects from and to a database in an easy way.

In order to use automated persistence in your business or User Dialog processes you need to define some Entity Classes first. An entity class is similar to a data class (i.e. a business object) but holds additional information that is necessary to map the class to a database table and its attributes to database columns.

Once you have created entity classes, you need to define at least one persistence unit configuration. A persistence unit is responsible for managing all or a subset of your entity classes and defines the database where those entities are stored. Once you have configured one or more persistence units you can use them in your process steps with the Persistence API to load/update entity objects directly from the database or save/update them to the database.

Entity Classes

Entity Classes are like global Data Classes but with additional information where and how to store the data of a class and its attributes to a relational database. An Entity Class is mapped directly to a database table and the attributes of an Entity Class are mapped directly to the fields of a database table. Therefore the database schema can be generated directly out of an Entity Class. It is possible to load, save, and update entity objects with the Persistence API.

Entity Classes are created with the New Entity Class Wizard and can be edited afterwards in the Entity Class Editor. Both of those are similar to the wizard and editor for regular Data Classes, but allow to specify additional settings, that are necessary for automated persistence.

New Entity Class Wizard

The New Entity Class wizard lets you create a new global Entity Class.

New Entity Class Wizard

Accessibility

File > New > Entity Class

Features

Project Name

Chose the name of the project the new Entity Class should belong to.

Namespace

Chose a namespace for your Entity Class. The name space lets you create a structure to organise your data. Use the dot character ‘.’ to separate the folders from each other. The namespace will be visible in the Axon.ivy project tree.

Entity Class Name

Enter the name of your Entity Class. Do not use the same name twice in your project, it may get confusing if you do so.

Entity DB Table Name

Enter the name of the database table name of your Entity Class. If empty the name of your Entity Class is used. This name is used if the database table of this Entity Class is generated.

Entity Class Editor

The Axon.ivy Entity Class editor lets you configure the process data objects of Axon.ivy similar to the Data Class Editor. The process data is the data that “flows” through your processes. Additionally an Entity Class has information where and how to store the data of a class and its attributes to a relational database.

Use this editor to add new data fields to your Entity Class, to change the type of a field or to document your Entity Class or Entity Class Fields.

Accessibility

Axon.ivy Project Tree > double click on a Entity Class entry in the tree

New > Entity Class > then the editor opens if the class was created successfully

Features

Section Class Comment

Enter your text here to describe in prose what kind of data your Entity Class represents.

Section Attributes

Enter a list of attributes into the table. Use the icon to add a new attribute or just click on the next empty cell in the “Name” column of the table.

If you want to reorder your entries in the table, then you can use the icons to do so. The order influences just the presentation and has no logic implication.

Use the icon to toggle the value change breakpoint for the currently selected attribute. The attribute icon shows that a breakpoint is installed on a attribute. More information about value change breakpoints can be found in chapter Breakpoints.

Name

Enter the name of your attribute. The name should not contain any special characters or spaces.

Tip

You may already specify the type of the attribute here by adding a colon ‘:’ to the attribute name, followed by desired type (e.g. myDateAttribute:Date). When only adding a colon to the name without a type, the data type selection dialog will appear.

Type

Enter the type of the attribute (fully qualified) or press the to bring up the Data Type Browser.

DB Field

The name of the field in the database table of this attribute. If you generate the database from this Entity Class for this attribute the DB field name is used as database field.

Persistent

Decide if the data should be saved in the database if you use the persistence API and if the data should be persistent between a task switch. If the data is not set to be persistent, then you loose all information if the execution of the process passes a task switch process element.

Length

You can specify the length of the field in the database. This can only be specified if the type is a String, BigDecimal or BigInteger. The default length for string fields is 255 and for decimal fields 19,2 on the database. Changes of the length has only an effect if the database schema is created new.

Properties

id

Specifies the primary key field of an entity. Every Entity Class must have exactly one primary key.

generated

Specifies if the primary key should be generated automatically.

not nullable

Whether the database column is not nullable.

unique

Whether the field should be a unique key in the database.

not updateable

Whether the column is not included in SQL UPDATE statements generated by the persistence provider.

not insertable

Whether the column is not included in SQL INSERT statements generated by the persistence provider.

version

Specifies the version field of an entity that serves as its optimistic lock value. The version is used to ensure integrity when performing the merge operation.

Association

Defines the association to another Entity Class. The actual configuration is done in the embedded Association Editor.

Comment

Describe the means of your attribute here.

Association Editor

Defines the association to another Entity Class and are only allowed to them and not other types of classes.

Association

ONE_TO_ONE

Defines a one to one (1:1 on the database) association to another Entity Class. Can only be used if the type of the attribute is an Entity Class.

MANY_TO_ONE

Defines a many to one (n:1 on the database) association to another Entity Class. Can only be used if the type of the attribute is a List or Set of an Entity Class. The inverse association of a MANY_TO_ONE is a ONE_TO_MANY association.

ONE_TO_MANY

Defines a one to many (1:n on the database) association to another Entity Class. Can only be used if the type of the attribute is a List or Set of an Entity Class. This type of association needs always a mapped by specification, because this is always the inverse side of an MANY_TO_ONE association.

Cascade

Defines the cascadable operations which are propagated to the associated Entity. E.g. if persist is enabled then the associated object will be persisted automatically if an instance of this class is persisted.

persist

If enabled the associated object is persisted automatically if an instance of the class is persisted. See persist operation.

merge

If enabled the associated object is merged automatically if an instance of the class is merged. See merge operation.

remove

If enabled the associated object is removed automatically if an instance of the class is removed. See remove operation.

refresh

If enabled the associated object is refreshed automatically if an instance of the class is refreshed. See refresh operation.

Mapped by

The field that owns the relationship on the specified type which must be an Entity Class. This element is only specified on the inverse (non-owning) side of the association. Mapped by can only be used for ONE_TO_ONE and ONE_TO_MANY associations. The inverse side of the association must be also the inverse association (ONE_TO_ONE inverse ONE_TO_ONE, ONE_TO_MANY inverse MANY_TO_ONE)

Orphans

If orphans remove is enabled and an entity that is the target of the relationship is removed from the relationship (either by removal from the collection or by setting the relationship to null), the remove operation will be applied to the entity being orphaned. If the entity being orphaned is a detached, new,or removed entity, the semantics do not apply.

If orphan remove is enabled and the remove operation is applied to the source entity, the remove operation will be propagated as defined in the cascade section.

The remove operation is applied at the time of the flush operation. The orphans removal functionality is intended for entities that are privately “owned” by their parent entity.

Example:

A ‘Basket’ entity holds a list of ‘Product’ entities. What happens if basket.getProducts().remove(..) is called?

• orphan remove enabled: the product is removed from the list of referenced products even if the entity is reloaded or refreshed.

• orphan remove disabled: the product stays in the list of referenced products if the entity is reloaded or refreshed.

Persistence Configuration Editor

The Persistence Configuration Editor lets you configure the persistence units you use in your project and the extending projects.

Usually you only need a single persistence unit that manages all of your project’s entity data classes. In this case you can simply create a new persistence unit, associate it with a data source (i.e. data base) and you’re done. All of the project’s entity classes will then automatically be managed by this unit.

However, if you wish to do so, you can divide your entity data classes into subsets and manage each subset through an individual persistence unit. In this case you need to specify multiple persistence units and then define an explicit list of managed entity classes for each unit.

Warning

Although it is theoretically possible to have the same entity class managed by two or more persistence units, you should be aware of the consequences. Once you have generated/attached an object of a specific class through a specific persistence unit, you must ensure that it is managed uniquely by that unit afterwards.

Example: Assume that you have two different objects of the same entity class, e.g. Person, generated through different persistence units, e.g. employee through employee_unit and customer through customer_unit. In this case you must absolutely avoid to mix persistence units and objects. For the above example, handing over employee to customer_unit (or customer to employee_unit) will consequently result in errors.

Persistence Configuration Editor (single persistence unit)

Persistence Configuration Editor (multiple persistence units)

New

Add a new persistence unit configuration

Remove

Remove the selected persistence unit(s)

Generate Schema

Generates the database schema out of the entity classes who belong to the selected persistence unit. See Generate database schema from persistence unit for details.

Accessibility

Axon.ivy Project Tree > double click on the Persistence label.

Features

Data source

Here you have to choose a database configuration which will be the data source of this persistence unit. Means all the data are loaded and stored within this database. Go to the Database Configuration Editor to configure available datasources.

There is also an IvySystemDatabase datasource which points to the current System Database. Normally you would prefer your own database to split valuable customer data from the system data.

Description

You can give your persistence unit any description here.

Managed Classes

Specify the list of classes to be managed in this persistence unit. If ‘Exclude unlisted classes’ is checked, only the entity classes which are defined in the list are included in the persistence unit. Otherwise all entity classes of the project are included automatically plus the entity classes defined in the list.

Properties

Specify some properties for the persistence unit. You do not have to specify something here except you now what you are doing.

Generate database schema from persistence unit

Generation options (Step 1)

On the first wizard page you can specify the environment and the type of the schema generation.

Database schema generation option

Data source

For the schema generation the data source of the persistence unit is used.

Environment

Specify the environment on which you like to generate the database schema.

Type

Specify the type of the schema generation. You can choose between update and create.

• update: Does update the current available schema on the database.

  Warning

  The update does not refactor any changed table names, field names or field types. If a table or field does not exist in the database a new one is created even if the same table or field with another name exists.

• create: Does drop the current schema on the database and create a new one.

  Warning

  This option does delete all data which is stored in the database.

Generation preview (Step 2)

The second wizard page shows a preview what will be executed on the database.

Database schema generation preview

Accessibility

Axon.ivy Project Tree > double click on the Persistence label > Select a persistence unit > Generate Schema.

Persistence API

The Axon.ivy Persistence API is used to load entity objects directly from the database or save/update them on the database. The Persistence API can be accessed by IvyScript anywhere scripting is supported. The Persistence API can only deal with entity objects, means objects of type Entity Classes. The Persistence API can be found under ivy.persistence.<persistence unit>. Here you find all the methods for finding, persisting, updating and querying entity objects. See IIvyEntityManager fore more information. Replace <persistence unit> with the name of a persistence unit. The persistence units can be configured with the Persistence Configuration Editor.

Persist an entity object

To persist (save/create object on the database) you can use the persist() method of the Persistence API.

Warning

This method only works properly if the entity object and all the associated objects are not jet persistent. Otherwise you have to use the merge method.

Example (Product is an Entity Class):

// persist new created product
Product product;
product.name = "Product name";
product.nr = 12;
ivy.persistence.<persistence unit>.persist(product);

// get id of new created product
Number newProductId = product.id;

Find an entity object by id

To find an entity object by id (select object on the database) you can use the find() method of the Persistence API.

Example (Product is an Entity Class):

// load product with id 1 from the database
Product product = ivy.persistence.<persistence unit>
.find(Product.class, 1) as Product;

Merge an entity object

To merge (update or save/create object on the database) you can use the merge() method of the Persistence API.

Example update (Product is an Entity Class):

...
// change before loaded product
product.name = "New product name"
Product updatedProduct = ivy.persistence.<persistence unit>
.merge(product) as Product;

Example save/create (Product is an Entity Class):

// save new created product
Product product;
product.name = "Product name";
product.nr = 12;
Product savedProduct = ivy.persistence.<persistence unit>
.merge(product) as Product;

// get id of new created product
Number newProductId = savedProduct.id;

Remove an entity object

To remove (delete object on the database) you can use the remove() method of the Persistence API.

Example (Product is an Entity Class):

...
// delete the product from the database
ivy.persistence.<persistence unit>.remove(product);

Refresh an entity object

To refresh (reload object from the database) you can use the refresh() method of the Persistence API.

Example (Product is an Entity Class):

...
// change before loaded product
product.name = "New product name"
// reload object from the database and revert local changes
ivy.persistence.<persistence unit>.refresh(product);

Persistence Queries (JPA QL)

With the Persistence API it is possible to execute Java Persistence API Query Language (JPA QL) statements. See IIvyQuery for more information about the Query API. The query language based around the objects that are persisted but with syntax very similar to SQL. You always have to use the names of the Entity Class and the attributes and not the names from the database.

Case Sensitivity

Queries are case-insensitive, except for names of Java classes and properties. So SeLeCT is the same as sELEct is the same as select but PRODUCT is not product and foo.barSet is not foo.BARSET. This manual uses lowercase JPA QL keywords.

Single Result

To execute a JPA query where you are expecting a single value to be returned you would call getSingleResult(). This will return the single Object. If the query returns more than one result then you will get an exception. This should not be called with “UPDATE”/”DELETE” queries.

Example (Product is an Entity Class):

Product product = ivy.persistence.<persistence unit>
.createQuery("select p from Product p where p.id = :id")
.setParameter("id", 1)
.getSingleResult() as Product;

Warning

Calling this method in automatic transaction mode (by default) will close the recordset automatically. Consequently you cannot invoke this method multiple times or in combination with getResultList() on the same query.

Result List

To execute a JPA query you would typically call getResultList(). This will return a list of results. This should not be called with “UPDATE”/”DELETE” queries.

Example (Product is an Entity Class):

List<Product> products = ivy.persistence.<persistence unit>
.createQuery("select p from Product p where p.price > :price")
.setParameter("price", 10)
.getResultList();

Warning

Calling this method in automatic transaction mode (by default) will close the recordset automatically. Consequently you can not invoke this method multiple times or in combination with getSingleResult() on the same query.

Execute Update

To execute a JPA UPDATE/DELETE query you would call executeUpdate(). This will return the number of objects changed by the call. This should not be called with “select” queries.

Example delete (Product is an Entity Class):

// delete all products
Number deletedRows = ivy.persistence.<persistence unit>
.createQuery("delete from Product p")
.executeUpdate()

Example update (Product is an Entity Class):

// update product name
Number updatedRows = ivy.persistence.<persistence unit>
.createQuery("update Product set name = :newName where name = :oldName")
.setParameter( "newName", "New Product Name" )
.setParameter( "oldName", "Old Product Name" )
.executeUpdate();

Parameter binding

The JPA Queries supports named and numbered parameters and provides methods for setting the value of a particular parameter.

Tip

You should always use parameter binding and do not build the query with string concatenation, because of performance reasons.

Example with named parameter:

ivy.persistence.<persistence unit>
.createQuery("select p from Product p where p.price > :price")
.setParameter("price", 10)

Example with positional parameter:

ivy.persistence.<persistence unit>
.createQuery("select p from Product p where p.price > ?1 and p.amount <= ?2")
.setParameter(1, 10).setParameter(2, 80)

Paging the result

To specify the range of a query you have the two methods setFirstResult() and setMaxResults() available. The start position of the first result, numbered from 0.

Example (Product is an Entity Class):

List<Product> products = ivy.persistence.<persistence unit>
.createQuery("select p from Product p where p.price > :price")
.setParameter("price", 10)
.setFirstResult(40)
.setMaxResults(20).getResultList();

The call to setFirstResult(40) means starting from the fortieth object. The call to setMaxResults(20) limits the query result set to 20 objects (rows) returned by the database.

Ordering

JPA QL provide an ORDER BY clause for ordering query results, similar to SQL.

Returns all Products ordered by name:

from Product p order by p.name

You specify ascending and descending order using asc or desc:

from Product p order by p.name desc

You may order by multiple properties:

from Product p order by p.name asc, p.description desc

Distinct results

When you use a select clause, the elements of the result are no longer guaranteed to be unique.

DISTINCT eliminates duplicates from the returned list of product descriptions.

select distinct p.description from Product p

Comparison expressions

JPA QL support the same basic comparison operators as SQL. Here are a few examples that should look familiar if you know SQL:

Binary comparison (=, <>, <, >, >=, <=, [NOT] BETWEEN, [NOT] IN):

from Product p where p.amount = 100
from Product p where p.amount <> 100
from Product p where p.amount > 100
from Product p where p.amount <= 100
from Product p where p.amount between 1 and 10
from Product p where p.name in ('Product A', 'Product B')

Null check (IS [NOT] NULL):

from Product p where p.name is null
from Product p where p.name is not null

Arithmetic expressions (+, -, *, /):

from Product p where ( p.amount / 0.71 ) - 100.0 > 0.0

The LIKE operator accepts a string value as input parameter in which an underscore (_) stands for any single character, a percent (%) character stands for any sequence of characters (including the empty sequence), and all other characters stand for themselves:

from Product p where p.name like 'A%'
from Product p where p.name not like '_a_'

Logical operators (NOT, AND, OR):

from Product p
    where p.name like 'A%' and p.price > 10

Expressions with collections (IS [NOT] EMPTY, [NOT] MEMBER [OF]):

from Product p where p.customers is not empty
from Product p, Category c where p member of c.products

JPA QL operator precedence

Operators	Description
.	Navigation path expression operator
+, -	Unary positive or negative signing (all unsigned numeric values are considered positive)
*, /	Regular multiplication and division of numeric values
+, -	Regular addition and subtraction of numeric values
=, <>, <, >, >=, <=, [NOT] BETWEEN, [NOT] IN, IS [NOT] NULL, [NOT] LIKE	Binary comparison operators with SQL semantics
IS [NOT] EMPTY, [NOT] MEMBER [OF]	Binary operators for collections in JPA QL
NOT, AND, OR	Logical operators for ordering of expression evaluation

Calling functions

An extremely powerful feature of JPA QL is the ability to call SQL functions in the where and HAVING clauses of a query.

Lower cases or upper cases a string (LOWER(string), UPPER(string)):

from Product p where lower(p.name) = 'product name'
from Product p where upper(p.name) = 'PRODUCT NAME'

Another common expression is concatenation, although SQL dialects are different here, JPA QL support a portable concat(string1, string2) function:

from Product p where concat(p.name, p.description) like 'A% B%'

Size of a collection (SIZE(collection)):

from Product p where size(p.customers) > 10

JPA QL functions

Function	Return	Description
UPPER(string), LOWER(string)	string	Lower cases or upper cases a string value
CONCAT(string1, string2)	string	Concatenates string values to one string
SUBSTRING(string, offset, length)	string	Substring string values (offset starts at 1)
TRIM( [[BOTH|LEADING|TRAILI NG] char [from]] string)	string	Trims spaces on BOTH sides of string if no char or other specification is given
LENGTH(string)	number	Gets the length of a string value
LOCATE(search, string, offset)	number	Searches for position of search in string starting at offset
ABS(number), SQRT(number), MOD(dividend, divisor)	number	Returns an absolute of same type as input, square root as double, and the remainder of a division as an integer
SIZE(collection)	integer	Size of a collection; returns an integer, or 0 if empty

Aggregate functions

The aggregate functions that are recognized in JPA QL are count(), min(), max(), sum() and avg().

This query counts all the Products:

Number productCount = ivy.persistence.<persistence unit>
.createQuery("select count(p) from Product p").getSingleResult() as Number;

This query calculates the average the sum, the maximum and the minimum from the amount of all products:

select avg(p.amount), sum(p.amount), max(p.amount) min(p.amount) from Product p

Accessibility

You can use the Persistence API everywhere you have the ivy variable in the IvyScript. Use ivy.persistence.<persistence unit>. Here you find all the methods for finding, persisting, updating and querying entity objects. Replace <persistence unit> with the name of a persistence unit.