What is the Data Store?
EasyRPA DataStore API
Entity Annotations (package eu.ibagroup.easyrpa.persistence.annotation)
Example

What is the Data Store?

The Data Store is a storage that allows you to manage data. Data Store provides a useful interface to work with your automation processes.

Most processes are concentrated around a certain entity. At different steps of the process, this entity is supplemented with data, checked, entered in different systems, etc.
For example, when processing payments, you may need to extract data about the payer from some system, check this data, enter payment data into the customer database and send a report. For such a case, it is convenient to create the entity of payment in the code and save/update its fields and statuses in the Data Store at each processing stage. Thus, the process architecture becomes simple and clear, the risk of data loss is minimized, and the process becomes easily scalable and changeable.

In this article we are going to consider interaction with Data Store from Automation Process code, exactly saving and receiving java-objects.

EasyRPA DataStore API

Due to EasyRPA DataStore API's conformance to Java persistence API, Java developers who are familiar with Java persistence API will find it easy to use.

Entity

The term entity is used to describe persistent objects. The @Entity annotation specifies that a class and its objects should be persisted. It describes the entity in Data Stores. This annotation must be specified at the class level. The entity name equals the name of the class by default. It can be changed by using the value parameter.

Entity Manager

Entity Manager manages the lifecycle of entity instances. It provides basic methods for work with Data Stores entities which can be injected into your task classes using annotation @Inject.

An entity manager performs the following roles:

Using the entity manager, developers can implement the API and encapsulate it with a single interface.
Entity manager allows you to read, write, and delete entities.
Entity manager manages objects referenced by entities.

Entity Manager provides methods for getting objects using @Query annotation. The query which should be executed is defined inside the @Query annotation along with the expected type in response. The query language uses a JPQL-like query string to select entities. As a result of executing a query, entities are returned. Returned entities can be updated and saved using the persist() method. Also, those entities can be deleted using the method delete().

Repositories

In addition to direct usage of the Entity Manager for database operations repositories can be used to simplify and organize DB related operations. There two types of repositories : Repository and CrudRepository. Repository provides basic operations like saving or deleting an object as well as custom query creation via @Query annotation. CrudRepository provides same functionality as Repository but assumes that given entity has primary key marked with @Id column and provides PK based operations.

Repository

Repository can be used for easier and more convenient access to the Data Store. In addition to using the basic methods from the Repository, custom queries can be added to it using the @Query annotation. Return type of annotated @Query methods can be only the following:

void - any query when result is not required by executor
List<T> - a query that returns select result mapped to an entity
T - a query that returns only one entity (i.e. get unique), returns null if nothing has been found, and throws an Exception if more than one record found
Optional<T> - a wrapper for the previous one
int - an update query, that returns number of affected records
List<Map<String, Object>> - an custom select query that not mapped to an entity object
primitive types: String, int, Integer, long, Long, Float, float, Double, double - an custom select query that returns only one result

T could be any entity, not only the repository's ones

Here are example of using @Query annotation:

import eu.ibagroup.easyrpa.persistence.annotation.Column;
import eu.ibagroup.easyrpa.persistence.annotation.Entity;
import eu.ibagroup.easyrpa.persistence.annotation.EntityType;
import eu.ibagroup.easyrpa.persistence.annotation.Id;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Calendar;

@Data
@Entity(value = "test", type = EntityType.AP_RESULT_DATASTORE)
@AllArgsConstructor
@NoArgsConstructor
public class TestEntity {
	@Id
	@Column(Column.CS_ID)
	private Long id;

	@Column("c 1")
	private String value1;

	@Column("c2")
	private Calendar date1;

	@Column("c3")
	private Integer value2;

	@Column("c4")
	private int value3;

	@Column("c5")
	private float value4;

	@Column(value = "secretString", encrypted = true)
	private String secretString;
}

import eu.ibagroup.easyrpa.persistence.CrudRepository;
import eu.ibagroup.easyrpa.persistence.annotation.Column;
import eu.ibagroup.easyrpa.persistence.annotation.Param;
import eu.ibagroup.easyrpa.persistence.annotation.Query;
import lombok.Data;

import java.util.List;
import java.util.Map;
import java.util.Optional;

interface TestEntityRepo extends Repository<TestEntity> {

	@Query("select * from @Entity")
	List<TestEntity> findAllCustom();

	@Query("select t from @Entity t order by t.value1")
	List<TestEntity> findAllOrderByVal();

	@Query("select t from @Entity t order by t.date1")
	List<TestEntity> findAllOrderByDate();

	@Query("select t from @Entity t where t.value1 = :value")
	List<TestEntity> findAllWhere(@Param("value") String value);

	@Query("select t from @Entity t where t.value1 = :value")
	TestEntity findOne(@Param("value") String value);

	@Query("select t from @Entity t where t.value1 = :value")
	Optional<TestEntity> findOptional(@Param("value") String value);

	@Query("select t from @Entity t where t.value1 = :value and t.value3 = :other")
	List<TestEntity> findAllWhereAnd(@Param("value") String value, @Param("other") int value3);

	@Query("select t from @Entity t where t.value1 like :value and t.value3 = :other")
	List<TestEntity> findAllWhereLikeAnd(@Param("value") String value, @Param("other") int value3);

	@Query(value = "select count(t) from @Entity t where t.value1 = :value and t.value3 = :other", customSelect = true)
	List<Map<String, Object>> countAllWhereAnd(@Param("value") String value, @Param("other") int value3);

	default int countAllWhereAndMap(String value, int value3) {
		List<Map<String, Object>> rows = countAllWhereAnd(value, value3);
		return rows.size() > 0 ? (int) rows.get(0).get("count") : 0;
	}

	@Query(value = "select count(t) from @Entity t where t.value1 = :value and t.value3 = :other", customSelect = true)
	int countAllWhereAndInt(@Param("value") String value, @Param("other") int value3);

	@Data
	class CustomGroupQuery {
		@Column("count")
		private Integer count;

		@Column("max")
		private Integer max;
	}

	@Query(value = "select count(t), max(t.value3) from @Entity t where t.value1 = :value and t.value3 = :other", customSelect = true)
	CustomGroupQuery customGroupQueryToObject(@Param("value") String value, @Param("other") int value3);

	@Data
	class CustomResultQuery {
		@Column("c 1")
		private String value1;

		@Column("c4")
		private int value3;
	}

	@Query(value = "select t.value1,t.value3 from @Entity t where t.value1 = :value and t.value3 = :other", customSelect = true)
	List<CustomResultQuery> customResultQueryToList(@Param("value") String value, @Param("other") int value3);

	@Query(value = "select t.value1 from @Entity t where t.value1 = :value and t.value3 = :other", customSelect = true)
	List<String> customResultQueryToString(@Param("value") String value, @Param("other") int value3);

	@Query("select t.value1,t.date1 from @Entity t")
	List<TestEntity> findAllBasic();

	@Query("update @Entity set value3=:value3 where value1=:value1")
	int updateEntity(@Param("value1") String value1, @Param("value3") int value3);

	@Query("delete from @Entity")
	int deleteAll(); 
}

Where the @Entity and @Id referenses in the query are replaced by a simple class name of the repository entity and its id field. You also can use the short class name directlly as table target:

@Query(value = "select t.value1 from TestEntity t where t.value1 = :value and t.value3 = :other", customSelect = true)
	List<String> customResultQueryToString(@Param("value") String value, @Param("other") int value3);

The Entity class names should be unique across the project (short class name), otherwice you could have table references mess.

CrudRepository

The CRUD repository can only be used for Data Stores that contain primary keys. Thus, the use of the @Id annotation column within an entity is a prerequisite for using the repository, since all basic methods provided by the repository are based on the use of Id. Here are the code of the previous repository class by with additional predefined id manipulation methods:

interface TestEntityRepo extends CrudRepository<TestEntity, Long> {
	. . . .
}

Entity Annotations (package eu.ibagroup.easyrpa.persistence.annotation)

Class level annotations

@Entity - This annotation tells that it's an Entity Class that should be saved in Data Store. The value parameter sets the logical name of the table.

The value keys defines a target Data Store name and should be uniquie across project.

The type parameter defines type of Entity it could be:

key	Description
DATASTORE	Datastore that is manageable from both sides: Control Server and Automation Process. AP or CS user can create/recreate it or change its schema. When AP uses this DS in its code and it is not exist on CS it will be created automatically.
AP_RESULT_DATASTORE	Automation Process result datastore. When AP uses this DS in its code it will be re-created at every AP run.
DOCUMENT_SET	Document set. It is manageable only from CS side. AP can not create/recreate it.

@Indexes - This annotation can be used together with nested annotations @Index and @IndexField to declare indexes for the the underlying database table.

Sample use of @Indexes annotation

import eu.ibagroup.easyrpa.persistence.annotation.Column;
import eu.ibagroup.easyrpa.persistence.annotation.Entity;
import eu.ibagroup.easyrpa.persistence.annotation.Id;
import eu.ibagroup.easyrpa.persistence.annotation.Index;
import eu.ibagroup.easyrpa.persistence.annotation.IndexField;
import eu.ibagroup.easyrpa.persistence.annotation.Indexes;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.FieldNameConstants;

@Data
@NoArgsConstructor
@FieldNameConstants
@Entity(value = "BOOKS_SAMPLE")
@Indexes(@Index(unique = true, fields = @IndexField(fieldName = Book.Fields.name, ascending = false)))
public class Book {

	@Column("book_name")
	private String name;
	 . . . .
}

Field annotations

@Id - indicates that field is used as a unique identifier. Required for Data Stores that have a primary key, so if the primary key is absent then @Id annotation is not required.

@Column("column_name") - maps the field from entity object to corresponding table column by specifying column name in brackets. Optionally 'encrypted' flag can be set to true to enable encryption for the column value (See EncryptDecryptService for the details)

Id could be an Automation Process generated or platform generated. For the second case use a column mapping to a reserved db column like:

import eu.ibagroup.easyrpa.persistence.annotation.Column;
import eu.ibagroup.easyrpa.persistence.annotation.Entity;
import eu.ibagroup.easyrpa.persistence.annotation.Id;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@Entity(value = "HT_SAMPLE_INPUT")
@ToString
public class HtInput {
	@Id
	@Column(Column.CS_ID)
	private String id;
		
	. . . .
}

Repository annotations

@Query defines JPQL like query. Note that entities should be referred by '@Entity' constant in the Repository queries. At runtime '@Entity' placeholder will be automatically replaced by actual entity name.

@Param defines query named parameter in method arguments

Example

Note: Below examples code can be found in the RPA by Example

Class Book is the entity we'll save in the DataStore.

Book.java

import eu.ibagroup.easyrpa.persistence.annotation.Column;
import eu.ibagroup.easyrpa.persistence.annotation.Entity;
import eu.ibagroup.easyrpa.persistence.annotation.EntityType;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity(value = "crud_example_books", type = EntityType.DATASTORE)
@NoArgsConstructor
@AllArgsConstructor
@Data
public class Book {
	@Column("name")
	private String name;

	@Column("author")
	private String author;
}

Class User is simple user entity with primary key.

User

import eu.ibagroup.easyrpa.persistence.annotation.Column;
import eu.ibagroup.easyrpa.persistence.annotation.Entity;
import eu.ibagroup.easyrpa.persistence.annotation.EntityType;
import eu.ibagroup.easyrpa.persistence.annotation.Id;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity(value = "crud_example_users", type = EntityType.DATASTORE)
@NoArgsConstructor
@AllArgsConstructor
@Data
public class User {
	@Column("id")
	@Id
	private Integer id;

	@Column("name")
	private String name;
}

BookRepository class which allows interacting with our storage

BookRepository.java

import java.util.List;

import eu.ibagroup.easyrpa.persistence.annotation.Param;
import eu.ibagroup.easyrpa.persistence.annotation.Query;

public interface BookRepository extends Repository<Book> {
	
	@Query("select b from @Entity b	where b.name = :name")
	List<Book> getBookByName(@Param("name") String name);
}

User repository gives access to PK based operations.

UserRepository

import eu.ibagroup.easyrpa.persistence.CrudRepository;

public interface UserRepository extends CrudRepository<User, Integer> {

}

SampleTask demonstrates how to save and receive objects from the repository.

SampleTask.java

import java.util.Arrays;
import java.util.List;

import javax.inject.Inject;

import eu.ibagroup.easyrpa.engine.annotation.ApTaskEntry;
import eu.ibagroup.easyrpa.engine.apflow.ApTask;
import lombok.extern.slf4j.Slf4j;

@ApTaskEntry(name = "Sample Task")
@Slf4j
public class SampleTask extends ApTask {

	@Inject
	private BookRepository bookRepository;

	@Override
	public void execute() throws Exception {
		// Create list of books
		Book thinkingInJava = new Book("Thinking in Java", "Bruce Eckel");
		Book cipollino = new Book("Le avventure di Cipollino", "Giovanni Francesco Rodari");
		Book wadAndPeace = new Book("War and Peace", "Lev Tolstoy");

		List<Book> books = Arrays.asList(thinkingInJava, cipollino, wadAndPeace);

		// Save books to DataStore
		books.forEach(b -> bookRepository.save(b));

		// Get books from DataStore and display it
		for (Book book : bookRepository.findAll()) {
			log.info(book.toString());
		}

		// Find book by name
		for (Book book : bookRepository.getBookByName("Thinking in Java")) {
			log.info(book.toString());
		}
		
		// Clean repository
		for (Book book : bookRepository.findAll()) {
			bookRepository.delete(book);
		}

	}
}

An another task example:

UsersTask

import javax.inject.Inject;

import eu.ibagroup.easyrpa.engine.annotation.ApTaskEntry;
import eu.ibagroup.easyrpa.engine.apflow.ApTask;
import lombok.extern.slf4j.Slf4j;

@ApTaskEntry(name = "Users Task")
@Slf4j
public class UsersTask extends ApTask {

	@Inject
	private UserRepository userRepository;

	@Override
	public void execute() throws Exception {
		User root = userRepository.findById(0);
		if (root == null) {
			root = new User(0, "Vasya");
			userRepository.save(root);
			log.info("Created new root user");
		} else {
			log.info("Root user name is {}", root.getName());
		}

	}
}

All operations above can be done without using repositories but by using EntityManager directly. Here is an example

import javax.inject.Inject;

import eu.ibagroup.easyrpa.engine.annotation.ApTaskEntry;
import eu.ibagroup.easyrpa.engine.apflow.ApTask;
import eu.ibagroup.easyrpa.persistence.EntityManager;
import eu.ibagroup.easyrpa.persistence.annotation.Column;
import eu.ibagroup.easyrpa.persistence.annotation.EntityType;
import lombok.extern.slf4j.Slf4j;

@ApTaskEntry(name = "Users Task")
@Slf4j
public class UsersTask extends ApTask {

	@Inject
	private EntityManager entityManager;

	@Override
	public void execute() throws Exception {

		// TypedQuery samples
		List<TestEntity> typedResult;

		typedResult = entityManager.createQuery("select * from @Entity", TestEntity.class).execute();
 		typedResult = entityManager.createQuery("select t from @Entity t where t.value1 = :value", TestEntity.class).withParam("value", "v1").execute(); 

		// CustomQuery samples
 		List<Map<String, Object>> result;
 
		// execute
		result = entityManager.createQuery("select count(t), max(t.value4) from TestEntity t where t.value2 = :v2", EntityType.DATASTORE).withParam("v2", 1).execute();
		result = entityManager.createQuery("select t from TestEntity t where t.value1 like :v1", EntityType.DATASTORE).withParam("v1", "%v%").execute();
		result = entityManager.createQuery("select t from TestEntity t where t.value1 like :v1 order by t." + Column.CS_ID + " desc", EntityType.DATASTORE)
				.withParam("v1", "%v%").execute();
		int size = entityManager.createQuery("select t from TestEntity t where t.value3 in( :v3)", EntityType.DATASTORE).withParam("v3", Arrays.asList("1", "2", "3")).execute()
						.size()
		result = entityManager.createQuery("select t from TestEntity t where t.value2 is null order by t." + Column.CS_ID + " desc", EntityType.DATASTORE).execute();
		result = entityManager.createQuery(
				"select t from TestEntity t where (t.value2 is null and t.value4 is not null) or (t.value1 is not null) order by t." + Column.CS_ID + " desc",
				EntityType.DATASTORE).execute();
		result = entityManager.createQuery(
				"select t from TestEntity t where t.value1 in (select t2.value1 from TestEntity t2 where t2.value2 is null) order by t." + Column.CS_ID + " desc",
				EntityType.DATASTORE).execute();
		// update
		int updated = entityManager.createQuery("update TestEntity set value2 = :v2 where value2 is null", EntityType.DATASTORE).withParam("v2", 33).executeUpdate();

	}
}