custom data in default database

[Tamás Matányi]

Hello,

I need to store custom tables in default h2 DB of the process engine.

I'd like to know what would be the best approach to make my webapp able to use CRUD functionality over default database.

Is it possible?

Should I use MyBatis?

Thanks in advance,

Tom

[Tamás Matányi]

Hi Guys,

I checked your documentation at the following link in the meantime:
http://docs.camunda.org/latest/real-life/how-to/

I'm trying to make your "custom-queries" example work but it is always stop working on the following line:

myBatisExtendedSessionFactory.initFromProcessEngineConfiguration(processEngineConfiguration, "/mappings.xml");

There's no any error messages or log messages. Only process engine says that it couldn't start my process what I'm using to call your example.
Object "processEngineConfiguration" is not null and I have "mappings.xml" in the resources folder.
All the files from your example are untouched except I have instantiated TasklistService to call getTasksForRegion(final String assignee, final String region)

What may be the problem?

Thanks,
Tom

[Tamás Matányi]

Finally I found what is the problem but still really don't know how to solve it.

The problem is:

SEVERE: Servlet.service() for servlet [Engine Api] in context with path [/camunda] threw exception

java.lang.RuntimeException: org.jboss.resteasy.spi.UnhandledException: java.lang.NoSuchMethodError: com.VeN_IT.testAutomation.query.MyBatisExtendedSessionFactory.initVariableTypes()V

at org.camunda.bpm.webapp.impl.security.auth.AuthenticationFilter$1.execute(AuthenticationFilter.java:61)

Tom

[thorben....@camunda.com]

Hi Tamas,

Thanks for tracking the error down. It appears the chapter on custom queries in the guide is not compatible with 7.2 and 7.3. I have created a ticket to update the guides, see [1].

Could you try to replace the initVariableTypes() call with initSerialization in the code of the class MyBatisExtendedSessionFactory?

Cheers,
Thorben

[1] https://app.camunda.com/jira/browse/CAM-4205

[Tamás Matányi]

Hi Thorben,

Thanks for your reply!

I replaced initVariableTypes() to initSerialization()

But it says: 

The method initSerialization() is undefined for the type MyBatisExtendedSessionFactory

Cheers,

Tamas

[thorben....@camunda.com]

Hi Tamas,

Which version of Camunda do you use?

Cheers,
Thorben

[Tamás Matányi]

Hi Thorben,

I'm using  v7.3.0

Regards,

Tom

2015. július 6., hétfő 17:35:58 UTC+2 időpontban thorben....@camunda.com a következőt írta:

Hi Tamas,

[thorben....@camunda.com]

Hi Tom,

Could you please post the sources of your application or publish it on github? That makes it easier to understand what's going wrong.

Thanks,
Thorben

[Tamás Matányi]

Hi Thorben,

It is a complex API. I think it is needless to post the whole stuff.

The main concept is that we have an API (jar) in tomcat/lib. This API contains all the business logic and functions to call from camunda webapps.

There may be several processes (BPMN) defined in a webapp. On running a process the elements (service tasks) may call some functions integrated into our API.

The experimental part of my project is the following (this is the point): 

The entry point into your example code is class "PersistenceTestDelegate". 

There's nothing else but calling getTasksForRegion():

All the other is your code.

I attached related files as "Src.zip".

Please let me know if you need more info!

The project is on gitlab at the moment. So, you may post me your gitlab user name and I'll add it to the project to make the source accessable for You.

Thanks,

Tamas

[Tamás Matányi]

Oh, yes.

It may be important information that your "ArquillianTestCase" is not running in my environment:

júl. 07, 2015 3:51:46 DU org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1 call

WARNING: Exception encountered during export of archive

org.jboss.shrinkwrap.api.exporter.ArchiveExportException: Failed to write asset to output: /junit/extensions/TestDecorator.class

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$3.handle(StreamExporterDelegateBase.java:272)

at org.jboss.shrinkwrap.impl.base.io.IOUtil.closeOnComplete(IOUtil.java:219)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase.processNode(StreamExporterDelegateBase.java:233)

at org.jboss.shrinkwrap.impl.base.exporter.AbstractExporterDelegate.processNode(AbstractExporterDelegate.java:105)

at org.jboss.shrinkwrap.impl.base.exporter.AbstractExporterDelegate.processNode(AbstractExporterDelegate.java:109)

at org.jboss.shrinkwrap.impl.base.exporter.AbstractExporterDelegate.processNode(AbstractExporterDelegate.java:109)

at org.jboss.shrinkwrap.impl.base.exporter.AbstractExporterDelegate.doExport(AbstractExporterDelegate.java:95)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase.access$001(StreamExporterDelegateBase.java:50)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$1.call(StreamExporterDelegateBase.java:121)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$1.call(StreamExporterDelegateBase.java:116)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1.call(JdkZipExporterDelegate.java:124)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1.call(JdkZipExporterDelegate.java:118)

at java.util.concurrent.FutureTask.run(FutureTask.java:262)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:745)

Caused by: java.io.IOException: Pipe closed

at java.io.PipedInputStream.checkStateForReceive(PipedInputStream.java:261)

at java.io.PipedInputStream.receive(PipedInputStream.java:227)

at java.io.PipedOutputStream.write(PipedOutputStream.java:149)

at java.util.zip.DeflaterOutputStream.deflate(DeflaterOutputStream.java:253)

at java.util.zip.ZipOutputStream.closeEntry(ZipOutputStream.java:238)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate.closeEntry(JdkZipExporterDelegate.java:84)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate.closeEntry(JdkZipExporterDelegate.java:40)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$2.execute(StreamExporterDelegateBase.java:265)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$2.execute(StreamExporterDelegateBase.java:233)

at org.jboss.shrinkwrap.impl.base.io.IOUtil.closeOnComplete(IOUtil.java:217)

... 14 more

júl. 07, 2015 3:51:46 DU org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1 call

WARNING: [SHRINKWRAP-120] Possible deadlock scenario: Got exception on closing the ZIP out stream: Pipe closed

java.io.IOException: Pipe closed

at java.io.PipedInputStream.checkStateForReceive(PipedInputStream.java:261)

at java.io.PipedInputStream.receive(PipedInputStream.java:202)

at java.io.PipedOutputStream.write(PipedOutputStream.java:122)

at java.util.zip.ZipOutputStream.writeInt(ZipOutputStream.java:590)

at java.util.zip.ZipOutputStream.writeEXT(ZipOutputStream.java:427)

at java.util.zip.ZipOutputStream.closeEntry(ZipOutputStream.java:262)

at java.util.zip.ZipOutputStream.finish(ZipOutputStream.java:343)

at java.util.zip.DeflaterOutputStream.close(DeflaterOutputStream.java:238)

at java.util.zip.ZipOutputStream.close(ZipOutputStream.java:360)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1.call(JdkZipExporterDelegate.java:148)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1.call(JdkZipExporterDelegate.java:118)

at java.util.concurrent.FutureTask.run(FutureTask.java:262)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:745)

júl. 07, 2015 3:51:50 DU org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1 call

WARNING: Exception encountered during export of archive

org.jboss.shrinkwrap.api.exporter.ArchiveExportException: Failed to write asset to output: /WEB-INF/lib/camunda-ejb-client-7.1.0-Final.jar

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$3.handle(StreamExporterDelegateBase.java:272)

at org.jboss.shrinkwrap.impl.base.io.IOUtil.closeOnComplete(IOUtil.java:219)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase.processNode(StreamExporterDelegateBase.java:233)

at org.jboss.shrinkwrap.impl.base.exporter.AbstractExporterDelegate.processNode(AbstractExporterDelegate.java:105)

at org.jboss.shrinkwrap.impl.base.exporter.AbstractExporterDelegate.processNode(AbstractExporterDelegate.java:109)

at org.jboss.shrinkwrap.impl.base.exporter.AbstractExporterDelegate.processNode(AbstractExporterDelegate.java:109)

at org.jboss.shrinkwrap.impl.base.exporter.AbstractExporterDelegate.doExport(AbstractExporterDelegate.java:95)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase.access$001(StreamExporterDelegateBase.java:50)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$1.call(StreamExporterDelegateBase.java:121)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$1.call(StreamExporterDelegateBase.java:116)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1.call(JdkZipExporterDelegate.java:124)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1.call(JdkZipExporterDelegate.java:118)

at java.util.concurrent.FutureTask.run(FutureTask.java:262)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:745)

Caused by: java.io.IOException: Pipe closed

at java.io.PipedInputStream.checkStateForReceive(PipedInputStream.java:261)

at java.io.PipedInputStream.receive(PipedInputStream.java:227)

at java.io.PipedOutputStream.write(PipedOutputStream.java:149)

at java.util.zip.DeflaterOutputStream.deflate(DeflaterOutputStream.java:253)

at java.util.zip.ZipOutputStream.closeEntry(ZipOutputStream.java:238)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate.closeEntry(JdkZipExporterDelegate.java:84)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate.closeEntry(JdkZipExporterDelegate.java:40)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$2.execute(StreamExporterDelegateBase.java:265)

at org.jboss.shrinkwrap.impl.base.exporter.StreamExporterDelegateBase$2.execute(StreamExporterDelegateBase.java:233)

at org.jboss.shrinkwrap.impl.base.io.IOUtil.closeOnComplete(IOUtil.java:217)

... 14 more

júl. 07, 2015 3:51:50 DU org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1 call

WARNING: [SHRINKWRAP-120] Possible deadlock scenario: Got exception on closing the ZIP out stream: Pipe closed

java.io.IOException: Pipe closed

at java.io.PipedInputStream.checkStateForReceive(PipedInputStream.java:261)

at java.io.PipedInputStream.receive(PipedInputStream.java:227)

at java.io.PipedOutputStream.write(PipedOutputStream.java:149)

at java.util.zip.DeflaterOutputStream.deflate(DeflaterOutputStream.java:253)

at java.util.zip.ZipOutputStream.closeEntry(ZipOutputStream.java:238)

at java.util.zip.ZipOutputStream.finish(ZipOutputStream.java:343)

at java.util.zip.DeflaterOutputStream.close(DeflaterOutputStream.java:238)

at java.util.zip.ZipOutputStream.close(ZipOutputStream.java:360)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1.call(JdkZipExporterDelegate.java:148)

at org.jboss.shrinkwrap.impl.base.exporter.zip.JdkZipExporterDelegate$1.call(JdkZipExporterDelegate.java:118)

at java.util.concurrent.FutureTask.run(FutureTask.java:262)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:745) 

Cheers,

Tom

[thorben....@camunda.com]

Hi Tom,

In your pom.xml you have set the property camunda.version to 7.1.0. Please set it to 7.3.0.

Cheers,
Thorben

[Tamás Matányi]

Hi Thorben,

Thanks, good job! We stepped forward. :-)

Now, I got the following error:

SEVERE: Error while closing command context

org.apache.ibatis.exceptions.PersistenceException: 

### Error querying database.  Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'parameter' in 'class java.lang.String'

### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'parameter' in 'class java.lang.String'

Cheers,

Tom

[thorben....@camunda.com]

Hi Tom,

I guess in TasklistService, you changed the code

return (List<TaskDTO>) commandContext.getDbSqlSession().selectList("selectTasksForRegion", region, 0, 100);

to

return (List<TaskDTO>) commandContext.getDbSqlSession().selectList("selectTasksForRegion", region);

Please try

return (List<TaskDTO>) commandContext.getDbEntityManager().selectList("selectTasksForRegion", region, 0, 100);

instead.

Cheers,
Thorben

[Tamás Matányi]

Hi,

Thank you Thorben!

You're right. I forgot about this moidification.

It looks working now.

(I only have an SQL error message because table CUSTOMER doesn't exists.)

I think it will work after I have created the table. :-)

Thank You for your effective help!

Cheers,

Tamas

[mash...@gmail.com]

Hi Tom,


Just a few additional questions on custom queries. Don't we have to add a finally clause to close the sessions at any point? Also how can we perform other CRUD operations like insert and update using DbEntityManager or DbSqlSession?


Thanks,
Masha

[thorben....@camunda.com]

Hi Masha,

Opening and closing the session is implemented as a command interceptor that is executed whenever a command is executed (in the tutorial: when you perform the statement myBatisExtendedSessionFactory.getCommandExecutorTxRequired().execute(command)).

Regarding inserts and updates: DbEntityManager has a method called insert. Updates are performed implicitly by the process engine for any selected entity which persistent state changes (check DbEntity#getPersistentState and its implementations). You'll have to provide insert and update statements in the MyBatis mapping files of course. The engine sources [1] contain numerous examples for that.

Cheers,
Thorben

[1] https://github.com/camunda/camunda-bpm-platform/tree/master/engine/src/main/resources/org/camunda/bpm/engine/impl/mapping/entity

[Thorben Lindhauer]

Hi Masha,

 

There is a convention for the names of update and insert statements. The logic that determines the names is implemented in DbSqlSessionFactory [1] where the parameter “prefix” is either “update” or “insert”. That means, an insert of an instance of CarEntity becomes “insertCar”. An insert of an instance of FlightPlan becomes “insertFlig”. Please don’t ask me why someone thought performing String magic was a good idea ;)

 

Cheers,

Thorben

 

[1] https://github.com/camunda/camunda-bpm-platform/blob/7.4.0/engine/src/main/java/org/camunda/bpm/engine/impl/db/sql/DbSqlSessionFactory.java#L300-L309

 

Von: Himasha Guruge [mailto:mash...@gmail.com]
Gesendet: Montag, 22. Februar 2016 12:47
An: Thorben Lindhauer <thorben....@camunda.com>
Betreff: Re: custom data in default database

 

Hi Tom,

 

I came across this method, for delete queries where we can pass the delete id of mappings file.   

getDbEntityManager().delete(CommentEntity.class, "deleteCommentsByTaskId", taskId);

 

Apprecitate if you could tell how to go ahead with insert and updates to bind the update and insert ids of mapping files.

 

Thanks,

Masha

 

On Mon, Feb 22, 2016 at 5:08 PM, Himasha Guruge <mash...@gmail.com> wrote:

Hi Tom,

 

Thanks for the clarification, just one more doubt. I went through the engine samples and got an idea on how to create an Entity with the relevant method. But how are we going to map insert id name with dbEntityManager.insert() / delete() ? Because for select queries we were able to pass the select id name "selectTasksForRegion" like below. 

 

 return (List<TaskDTO>) commandContext.getDbEntityManager().selectList("selectTasksForRegion", region, 0, 100); 

How are we going to do that for insert/delete ? For update however as I understood we need to first select the entity and then update attributes separately ,as u mentioned with getPersistentState. 

 

Thanks,

Masha

[Thorben Lindhauer]

Hi,

 

First, the code builds the String „insertPropertyEntity” based on the prefix and the class name (which is PropertyEntity). Then it removes the last six characters. That results in “insertProperty”.

 

Cheers,

Thorben

 

Von: Himasha Guruge [mailto:mash...@gmail.com]
Gesendet: Montag, 22. Februar 2016 13:45
An: Thorben Lindhauer <thorben....@camunda.com>
Betreff: Re: custom data in default database

 

Hi Tom,

 

As far as I understand,  what you mean is that if we call getDbEntity.insert(propertyEntity);   

It will auto detect the relevant statement from the mappings file ? whereas in this case it would look for  'insertPro' ? 

 

 <insert id="insertProperty" parameterType="org.camunda.bpm.engine.impl.persistence.entity.PropertyEntity">

      insert into ${prefix}ACT_GE_PROPERTY (

 

Thanks,

Masha