MyBatis Multi-db vendor support

[balsarori]

An old post asking to upgrade to MyBatis 3.1.x to take advantage of MultiVendor database support
http://forums.activiti.org/content/mybatis-31x


MyBatis 3.2.5 is currently used in Activiti.

Using MyBatis Multi-db vendor support and other simple tweaks which included

• Postgres specific statements were removed instead a property bolbType was used in original statements
• db2 and mssql required especial handling with native statements. All Native statements now use selectByNativeQuery
• removed statements ending with _integerBoolean (required by oracle, db2, and mssql) a property boolValue was used instead
• DB specific statements (i.e. that used to end with _mysql, _oracle) now hold the same statement id but with corresponding databaseId attribute value

Over 40 statements were removed. Additionally, only configured database specific statements and properties are loaded when the engine starts.

I removed all database vendor specific statements remapping from the org.activiti.engine.impl.db.DbSqlSessionFactory class and externalized databaseSpecificLimitXXX and other properties to dbId.properties files.

However, I didn't make a pull request since I wasn't able to test all use cases and databases. Any help testing the code will be appreciated.

https://github.com/balsarori/Activiti/commit/d5ce0814dc9e4aa11486ebdecf3ebe48dfabaca5

[balsarori]

I ran different JUnit tests on h2 and mysql successfully. For db2 and mssql, I manually changed databaseType to be able to check generated statements. Here are two of the quries I used and their generated statements while setting the databaseType to db2

[java]
taskService.createNativeTaskQuery().sql("SELECT * FROM " + managementService.getTableName(Task.class)).listPage(0, 5);
[/java]
org.activiti.engine.impl.persistence.entity.TaskEntity.selectTaskByNativeQuery - ==>  Preparing:
SELECT SUB.* FROM ( select RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( SELECT * FROM ACT_RU_TASK )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ?

[java]
taskService.createTaskQuery().includeProcessVariables().includeTaskLocalVariables().orderByTaskPriority().desc().listPage(0, 1);
[/java]
org.activiti.engine.impl.persistence.entity.TaskEntity.selectTaskWithVariablesByQueryCriteria - ==>  Preparing:
SELECT SUB.* FROM ( select distinct TEMPRES_ID_ as ID_, TEMPRES_REV_ as REV_, TEMPRES_NAME_ as NAME_, TEMPRES_PARENT_TASK_ID_ as PARENT_TASK_ID_, TEMPRES_DESCRIPTION_ as DESCRIPTION_, TEMPRES_PRIORITY_ as PRIORITY_, TEMPRES_CREATE_TIME_ as CREATE_TIME_, TEMPRES_OWNER_ as OWNER_, TEMPRES_ASSIGNEE_ as ASSIGNEE_, TEMPRES_DELEGATION_ as DELEGATION_, TEMPRES_EXECUTION_ID_ as EXECUTION_ID_, TEMPRES_PROC_INST_ID_ as PROC_INST_ID_, TEMPRES_PROC_DEF_ID_ as PROC_DEF_ID_, TEMPRES_TASK_DEF_KEY_ as TASK_DEF_KEY_, TEMPRES_DUE_DATE_ as DUE_DATE_, TEMPRES_SUSPENSION_STATE_ as SUSPENSION_STATE_, TEMPVAR_ID_ as VAR_ID_, TEMPVAR_NAME_ as VAR_NAME_, TEMPVAR_TYPE_ as VAR_TYPE_, TEMPVAR_REV_ as VAR_REV_, TEMPVAR_PROC_INST_ID_ as VAR_PROC_INST_ID_, TEMPVAR_EXECUTION_ID_ as VAR_EXECUTION_ID_, TEMPVAR_TASK_ID_ as VAR_TASK_ID_, TEMPVAR_BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, TEMPVAR_DOUBLE_ as VAR_DOUBLE_, TEMPVAR_TEXT_ as VAR_TEXT_, TEMPVAR_TEXT2_ as VAR_TEXT2_, TEMPVAR_LONG_ as VAR_LONG_ , row_number() over (ORDER BY TEMPRES_PRIORITY_ desc) rnk FROM ( select distinct RES.ID_ as TEMPRES_ID_, RES.REV_ as TEMPRES_REV_, RES.NAME_ as TEMPRES_NAME_, RES.PARENT_TASK_ID_ as TEMPRES_PARENT_TASK_ID_, RES.DESCRIPTION_ as TEMPRES_DESCRIPTION_, RES.PRIORITY_ as TEMPRES_PRIORITY_, RES.CREATE_TIME_ as TEMPRES_CREATE_TIME_, RES.OWNER_ as TEMPRES_OWNER_, RES.ASSIGNEE_ as TEMPRES_ASSIGNEE_, RES.DELEGATION_ as TEMPRES_DELEGATION_, RES.EXECUTION_ID_ as TEMPRES_EXECUTION_ID_, RES.PROC_INST_ID_ as TEMPRES_PROC_INST_ID_, RES.PROC_DEF_ID_ as TEMPRES_PROC_DEF_ID_, RES.TASK_DEF_KEY_ as TEMPRES_TASK_DEF_KEY_, RES.DUE_DATE_ as TEMPRES_DUE_DATE_, RES.SUSPENSION_STATE_ as TEMPRES_SUSPENSION_STATE_, VAR.ID_ as TEMPVAR_ID_, VAR.NAME_ as TEMPVAR_NAME_, VAR.TYPE_ as TEMPVAR_TYPE_, VAR.REV_ as TEMPVAR_REV_, VAR.PROC_INST_ID_ as TEMPVAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as TEMPVAR_EXECUTION_ID_, VAR.TASK_ID_ as TEMPVAR_TASK_ID_, VAR.BYTEARRAY_ID_ as TEMPVAR_BYTEARRAY_ID_, VAR.DOUBLE_ as TEMPVAR_DOUBLE_, VAR.TEXT_ as TEMPVAR_TEXT_, VAR.TEXT2_ as TEMPVAR_TEXT2_, VAR.LONG_ as TEMPVAR_LONG_ from ACT_RU_TASK RES left outer join ACT_RU_VARIABLE VAR ON RES.ID_ = VAR.TASK_ID_ or RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ?

For postgres and oracle, I think that they will also work.