flow and db transaction?

[vipi_voxa]

Hello guys,

Please help me to get rid of my darkness. I am new to Activiti and BPMN and i am apologizing for this in advance. I have installed eclipse activiti designer, i have managed to get activiti-explorer 5.22 up & running on jboss together with activit-rest ,all works fine, db is oracle. But...

But ... I think i missing some crucial point in understanding of how to work with Activiti Bpmn. 

Let's keep it simple, lets say I have a process "A" which i designed in Eclipse Activiti Desiner and deployed to my Activiti instance running on my server.

Process "A" has an incoming parameter, lets call it "test_table_id". And the process at some point needs to update DB table. E.g. in my flow i need to execute "update test_table set processing_status='S' where test_table_id=${test_table_id}" as one of the steps.

Simple structure of the flow:

Process "A": starts -> UserTask to User "Mr.X"->"Mr. X" completes task-> Update DB table action -> Other actions ->ends

Sounds and looks easy - but how to implement correctly? First question: What type of activiti event is suitable for DB update call?

At the moment I have created groovy ScriptTask which is calling java code. This java code updates DB. It Works, but doesn't  look like a correct approach, because i am making, calling, committing and closing jdbc connection inside that java. But how to handle commits and rollbacks then afterwords in a flow? What if Other Actions fails? In my created diagram the task stays active if 'Other actions' failed, means user can complete task again and then update db action (which i implemented via scriptTask) is performed again.

Please fellows, advice me with best practice hints,

thank you!

[gdharley]

Hello Vipl,

Certainly a script task can handle the update, but that is not really the intent of a script task.

What you need to do is either create a JavaDelegate to be called as a service task or a Spring bean that can be called as a delegateExpression.

It's likely within your process you will have a number of database CRUD operations, so you may choose to write a generic service task, or a separate one for each table.

A second option is to hook into the iBatis layer to create custom ibatis mappings and create associated persistence entities or you can use the managementService.executeCustomSql() (Activiti User Guide - execute custom sql ) call.
The limitation you have with the iBatis approach is that the tables must live in the Activiti database (or have views exposed there).

The third approach (probably my favorite) is to take advantage of the activiti JPA interface (Activiti User Guide - JPA )

As you can see, there are multiple approaches depending on your scenario.

Regards,

Greg

‌

[vipi_voxa]

Thank you Greg for detailed explanations and useful links!

it all seems a bit cumbersome from a first look, especially if i am looking not just for crud operations (just provided  very simplistic example for the start) on table but rather calling sql package methods.

[gdharley · Accepted solution]

Hi Vipi, jus remember, Activiti is a general purpose workflow engine. People use it for high speed straight through processing, user based processes, data translation and mediation and god know how many other things. Think of it as a general purpose state machine with a visual state definition language.
Many processes have no need to updating external systems, and those that do, tend to use services to abstract the business logic from the crud or boiler plate code.

If you are looking for a general purpose SQL JavaDelegate, you can write one in < 20 lines of code using Springs JDBCTemplate classes (19. Data access with JDBC ). Setup inputs as the schema name, sql and list of parameters and transform the resultset into JSON and you have a simple general purpose service that you can throw anything at. IMO, this isnt a good approach as it puts CRUD logic into the business process, but it is an option if you are just trying to hack something together (can't say I havent use the approach).

Cheers,

Greg

‌