Tuesday, February 23, 2016

Creating custom CRUD queries with Camunda

Camunda is a forked project of Activiti, which can be used as a bpmn runtime.  When implementing different tasks with Camunda as the bpmn engine, I came across a situation where we need to write custom CRUD queries to a custom table of camunda database. Following [1] is a good starting point which explains the integration with MyBatis framework , and how we can start our own myBatis session to execute our custom mappings against the queries.

[1] https://docs.camunda.org/manual/7.4/examples/tutorials/custom-queries/#custom-mybatis-queries
However there is hardly any  documentation that gives  information on how to work with other operations such as inserts, updates and deletes. Also for insert operation you can't pass the mapping id name as well. Therefore following is an example on how to perform all CRUD operations with camunda and MyBatis.

 For this example suppose you have a mapping file with mappings like below.
<resultMap type="org.model.CarEntity"
               id="selectMetaDataMap">
        <id property="id" column="ID_"/>
        <result property="name" column="NAME_"/>
        <result property="id" column="ID_"/>
        <result property="brand" column="BRAND_"/>
    </resultMap>

    <!-- Default execution as PREPARED statements -->
    <select id="selectCar"
            parameterType="map"
            resultMap="selectMetaDataMap">
        select * from ${prefix}CAR TABLE WHERE ID_ = #{id} AND NAME_
        = #{name}
    </select>
    <select id="selectCars" resultMap="selectMetaDataMap">
        select * from ${prefix}CAR TABLE
    </select>

    <insert id="insertCar"
            parameterType="org.model.CarEntity">
        insert into ${prefix}CAR TABLE(ID_, NAME_, BRAND_)
        values (#{id}, #{name}, #{brand})
    </insert>

    <update id="updateCar"
            parameterType="org.model.CarEntity">
        update ${prefix}CAR TABLE
        <set>
            BRAND_ = #{brand}
        </set>
        where NAME_=#{name} and ID_ = #{id}
    </update>

    <delete id="deleteCar"
            parameterType="org.model.CarEntity">
        delete from ${prefix}CAR TABLE where NAME_= #{name} and ID_
        = #{id}
    </delete>



 Following are the sample queries you can perform at the end for each operation and mapping.

1. Select a single object for the given query with id 'selectCar'.

commandContext.getDbEntityManager() .selectOne( "selectCar",id);

Here if your mapping select statement has a where clause such as 'where id =#id and name =#name, you can combine those variables like below and pass to the method.

Map<String, String> parameters = new HashMap<String, String>();
                parameters.put(id);
                parameters.put(name);


commandContext.getDbEntityManager() .selectOne( "selectCar",parameters);

2. Select a list of objects for the given query with select id 'selectCars'

commandContext.getDbEntityManager() .selectList("selectCars");

3.   Insert a car entity object.

commandContext.getDbEntityManager().insert(CarEntity car);

4. Update car entity object.
commandContext.getDbEntityManager().update(CarEntity.class,"updateCar", car);

5. Delete car entity object.
commandContext.getDbEntityManager().delete(CarEntity.class, "deleteCar", car);

What you need to remember is when it comes to matching the mapping id in insert and update operations there is a naming convention. For example when we directly call the insert operation in 3, a string is created combining the prefix ( which is the operation : either insert/update) with the name of the Entity ( in this case CarEntity) , and it removes the last six characters. That is why we have used 'insertCar' as the id for insert mapping.  However for update and delete method you could use the relevant method which allows you to pass the custom string like in 4 and 5.

As for the Entity class, you could create your own DTO class and implement Entity interface like below.

import org.camunda.bpm.engine.impl.db.DbEntity;
 
public class CarEntity implements Entity{} 

No comments:

Post a Comment