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.
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.
- 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.
- 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.
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
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
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.