Workflow performance Optimization (Dare activiti scalability?) ) (3)

2015/4/20

The weekend went back to think, Hibernate, MyBatis, JDBC, all line, finally decided to use MyBatis, who call it so elegant, Acvtiviti is dependent on MyBatis, will not have to introduce the package;

Look at the configuration file, before the configuration is JPA, and then configure the MyBatis, configuration files appear bloated, very messy; with JPA, it seems not bad;

Write one more repository;

The time to write SQL is used, knowledge points:

Instr:instr (T.description_, ' conditioncountersign ') > 0

MySQL queries the last record, cannot be used by:

Requirements: To take A1 the last data to associate, the statement is as follows;

Resolution: Cannot use ORDER by XXX limit 1 in inner join;

To use time to limit; 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 ')

The 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 mapping Activiti entities, various error;

Good trouble, JPA to write entities, to write HQL, originally I have written SQL, but also to convert to hql,hibernate really troublesome;

Slowly Copy the table field to write entities, each entity to inherit the original entities;

Write a test case first, run through the other, do not even query can not find, waste time; stick some main code:

Entity:

Repository:

Test Case:

Can OH:

Then start to write the entity slowly, write well and then change hql;

Write the HQL and start testing:

Error:

caused By:java.lang.IllegalArgumentException:node to traverse cannot be null!

I can not touch the mind, first streamline the statement, first tune the upper part of the union to say;

Still the same;

Ask Niang, Degree Niang said is hql write wrong, where wrong, she also don't say, really;

Streamline again:

Still the same;

Streamline again:

Still the same;

Elder brother, a lot of left parenthesis, keep fighting;

Plus sql:

Add parameter, add sort, very smooth;

The other union has a lot of problems and a bit of despair;

Try using JDBC.

Error encountered:

Error code: 1248

Every derived table must has its own alias

Solve:

Select COUNT (0) as total from (XXX), with aliases behind;

Select COUNT (0) as total from (XXX) dd;

Soon write, the native SQL is good;

The DAO layer of JDBC:

    /*** Paging query to-do workflow record *@paramInvolveduser *@paramFirstresult *@paramMaxResults *@return     */     PublicListqryunfinishedbyinvolveduser (historicprocessinstancequery query) {StringBuilder SQL=NewStringBuilder (); 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 =NewArraylist<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); //Paging Settingspageutils.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 (),NewBeanpropertyrowmapperclass)); returndatas; }

To start a project test:

Critical: Servlet.service () for Servlets [spring] in context with path [] threw exception [Handler processing failed; nested EXCE Ption 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;

The method is to write well, debug in is to use proxy mode;

Javap–s Detailbuilder

looks the same AH;

Toss for a long time, with the run as server inside the Tomcat can, but with MAVEN plug-in way to start the error;

Why is it?

The optimization ends here.

Questions:

1. How can I write the DAO layer using native SQL in JPA annotations? The HQL is this:

2, Activiti table field and property name although not the same, but still can be recognized, not torture people, why not the same? Isn't it better? All places can be copied, do not copy the changes, keep the underline can understand, if afraid of the field too long, do not set so long field can be, at least to maintain consistency, after a lot of automated things, such as writing code generator;

3, well-designed database, even if the complex query should also be very good to write, why here to do queries so scary?

Summarize:

1, the new entry, take over the existing projects, it is so bumpy, habit is good;

2, on the importance of testing, a sentence measured how many times to test pass Ah, write testing is very necessary, responsible for the withdrawal, but before the company's developers, work 1 years when the technical director, the code has not written a few lines on the director, really no language, there is a job 1 years on the seal as an architect, Write a JavaBean property is all public, are just overtime active point, Love 13, I was drunk;

3, the simple method of troubleshooting is to do subtraction and then do the addition, do not open the square root, hehe simple bar;

4, can be done with SQL once done with SQL, like this, can be completely filtered in the sentence sort it;

Workflow performance Optimization (Dare activiti scalability?) ) (3)