工作流性能优化（敢问activiti有扩展性？）（3）

2015/4/20

周末回去想了下，hibernate、mybatis、jdbc，都行，最终定了用mybatis，谁叫它这么优雅，acvtiviti是依赖了mybatis的，就不用再引入包了；

看了配置文件，之前配置的是jpa，再配置mybatis，配置文件就显得臃肿，很乱了；用jpa吧，貌似也不赖；

再写一个Repository；

写SQL的时候用到了，知识点：

instr：instr(t.DESCRIPTION_, 'conditionCountersign') > 0

mysql查询最后一条记录，不能用orderby：

需求：要取a1的最后一条数据来关联，语句如下；

解决：不能在inner join里边用order by xxx limit 1；

折中解决：用时间来限制；a1.START_TIME_>a2.START_TIME_

select distinct RES.*, a1.ID_, a2.ID_ from ACT_HI_PROCINST RES inner join ACT_HI_ACTINST a1 on (a1.PROC_INST_ID_ = RES.PROC_INST_ID_ and a1.ACT_TYPE_ != 'endEvent')

inner join ACT_RU_TASK t on (a1.TASK_ID_=t.ID_ and instr(t.DESCRIPTION_, 'conditionCountersign') > 0)

inner join ACT_HI_ACTINST a2 on (a1.ACT_ID_=a2.ACT_ID_ and a1.START_TIME_>a2.START_TIME_ and a2.ASSIGNEE_='324')

where RES.END_TIME_ is null

and exists (select LINK.USER_ID_ from ACT_HI_IDENTITYLINK LINK where USER_ID_ = '324' and LINK.PROC_INST_ID_ = RES.ID_) order by START_TIME_ desc;

jpa映射activiti的实体，各种报错；

好麻烦，jpa要写实体，要写HQL，原来我都写好SQL了，还要转换为HQL，hibernate真麻烦；

慢慢拷贝表字段写实体，每个实体要继承原有的entity；

先写一个测试用例，跑通了再说，别连查询都查不了，就浪费时间了；贴一些主要的代码：

实体：

Repository：

测试用例：

可以哦：

那开始慢慢写实体吧，写好再改HQL；

写好了HQL，开始测试：

报错：

Caused by: java.lang.IllegalArgumentException: node to traverse cannot be null!

摸不着头脑，先精简语句，先调通上半部分的union再说；

还是一样；

问度娘；度娘说是HQL写错了，哪里错了，她也不说，真是的；

再次精简：

还是一样；

再次精简：

还是一样；

哥，多了个左括号；继续奋战；

再加SQL：

加参数，加排序，很顺利；

另一个union问题就很多，有点绝望；

换用jdbc试试；

遇到的错误：

错误代码： 1248

Every derived table must have its own alias

解决：

select count(0) as total from (xxx)；后边要带别名；

select count(0) as total from (xxx) dd;

很快就写好了，原生SQL就是好；

jdbc的dao层：

    /**
     * 分页查询待办工作流记录
     * @param involvedUser
     * @param firstResult
     * @param maxResults
     * @return
     */
    public List<HistoricProcessInstanceEntity> qryUnfinishedByInvolvedUser(HistoricProcessInstanceQuery query){
        StringBuilder sql = new StringBuilder();
        sql.append(" select  ");
        sql.append(HistoricProcessInstanceSQLFragment.baseColumnList());
        sql.append(" from (");
        sql.append("(select RES.* from ACT_HI_PROCINST RES ");
        sql.append("where RES.END_TIME_ is null ");
        sql.append("  and (exists (select LINK.USER_ID_ from ACT_HI_IDENTITYLINK LINK where USER_ID_ = ? and LINK.PROC_INST_ID_ = RES.PROC_INST_ID_)) ");
        sql.append("  and exists (select 1 from ACT_RU_TASK t where t.PROC_INST_ID_=RES.ID_ and t.ASSIGNEE_=? order by t.ID_ asc limit 1)) ");
        sql.append(" union ");
        sql.append(" (select distinct RES.* from ACT_HI_PROCINST RES inner join ACT_HI_ACTINST a1 on (a1.PROC_INST_ID_ = RES.PROC_INST_ID_ and a1.ACT_TYPE_ != 'endEvent') ");
        sql.append("     inner join ACT_RU_TASK t on (a1.TASK_ID_=t.ID_ and instr(t.DESCRIPTION_, 'conditionCountersign') > 0)  ");
        sql.append("     inner join ACT_HI_ACTINST a2 on (a1.ACT_ID_=a2.ACT_ID_ and a1.START_TIME_>a2.START_TIME_ and a2.ASSIGNEE_=?) ");
        sql.append("     where RES.END_TIME_ is null  ");
        sql.append("     and exists (select LINK.USER_ID_ from ACT_HI_IDENTITYLINK LINK where USER_ID_ = ? and LINK.PROC_INST_ID_ = RES.ID_)) ");
        sql.append("order by START_TIME_ desc) dd ");
        
        List<Object> param = new ArrayList<Object>();
        param.add(query.getInvolvedUser());
        param.add(query.getInvolvedUser());
        param.add(query.getInvolvedUser());
        param.add(query.getInvolvedUser());
        
        String countSql = PageUtils.getCountSQL(sql.toString());
        logger.info("About to excute sql=\n{}\n{}", countSql, BaseUtils.printList(param));
        Integer total = baseDao.getJdbcTemplate().queryForObject(countSql, param.toArray(), Integer.class);
        query.setTotalRecord(total);

        // 分页设置
        PageUtils.buildPageSqlForMysql(sql, query);

        logger.info(" About to excute sql=\n{}\n{}", sql.toString(), BaseUtils.printList(param));
        List<HistoricProcessInstanceEntity> datas = baseDao.getJdbcTemplate().query(sql.toString(), param.toArray(), new BeanPropertyRowMapper<HistoricProcessInstanceEntity>(HistoricProcessInstanceEntity.class));
        return datas;
    }

启动项目测试：

严重: Servlet.service() for servlet [spring] in context with path [] threw exception [Handler processing failed; nested exception is java.lang.NoSuchMethodError: com.sinotaiyo.srpm.activiti.DetailBuilder.BuildWorkflowDetailByEntity(Lorg/activiti/engine/impl/persistence/entity/HistoricProcessInstanceEntity;)Lcom/sinotaiyo/srpm/activiti/WorkflowDetail;] with root cause

java.lang.NoSuchMethodError: com.sinotaiyo.srpm.activiti.DetailBuilder.BuildWorkflowDetailByEntity(Lorg/activiti/engine/impl/persistence/entity/HistoricProcessInstanceEntity;)Lcom/sinotaiyo/srpm/activiti/WorkflowDetail;

方法是写好的，debug进去是用代理模式；

javap –s DetailBuilder