cData分页

qweasdzxc

jishuang 发表于 2018-3-6 16:08
2楼发的就是完整的案例
里面有data事件中加载数据的处理，请仔细看

1. public static JSONObject getAdjustmentTask(String psmID,String sStatusID,String filter,String limit,String offset){
   
2.                 System.out.println("psmID:"+psmID+" sStatusID:"+sStatusID+" filter："+filter);
   
3.                 JSONObject json = new JSONObject();
   
4.                 JSONArray ja = new JSONArray();
   
5.                 JSONObject jo = null;
   
6.                 //从Task表中查询出本人未审核和已审核记录
   
7.         String ksql="SELECT a,a.sData1,a.sName,a.sProcess,a.sActivity,a.sActivityName,a.sKindID,a.sEURL,a.sStatusID,a.sExecutorFID FROM SA_Task a WHERE " +
   
8.                             "a.sExecutorPersonID = '"+ psmID +"' AND a.sProcess='/HR/employeeInfo/process/postAdjustmentApply/postAdjustmentApplyProcess' " +
   
9.                             "AND a.sStatusID='"+ sStatusID +"' and a.sActivity<>'businessActivity1'";
   
10.                 Table table =KSQL.select(ksql, null, "/system/data", null);
    
11.         Iterator<Row> rows = table.iterator();
    
12.                 while (rows.hasNext()) {
    
13.                         Row rs = rows.next();
    
14.                         String sData1=rs.getString("sData1");  //获取到sData1
    
15.                         String sName=rs.getString("sName");  
    
16.                         String SA_Task=rs.getString("a");  
    
17.                         String sProcess=rs.getString("sProcess");  
    
18.                         String sActivity=rs.getString("sActivity");  
    
19.                         String sActivityName=rs.getString("sActivityName");  
    
20.                         String sKindID=rs.getString("sKindID");  
    
21.                         String sEURL=rs.getString("sEURL");  
    
22.                         String ssStatusID=rs.getString("sStatusID");
    
23.                         String sExecutorFID=rs.getString("sExecutorFID");
    
24.                         if(sStatusID=="tesExecuting"){
    
25.                                 sStatusID="0";  //未审核
    
26.                         }else if(sStatusID=="tesFinished"){
    
27.                                 sStatusID="1";  //已审核
    
28.                         }
    
29.                        
    
30.                         String filters="";
    
31.                         if(!"".equals(filter))
    
32.                                 filters="where b.fTitle like '%"+ filter +"%' or b.fCreatePsnName like '%"+ filter +"%' or b.fBizStateName like '%"+ filter +"%' or b.fCreateTime like '%"+ filter +"%'";
    
33.                         String sql2="select * from ( SELECT a.fid,a.fTitle,a.fCreatePsnName,to_char(a.fCreateTime,'yyyy-mm-dd hh24:mi:ss') as fCreateTime," +
    
34.                                              "a.fBizStateName FROM HR_emp_AdjustmentApply a WHERE a.fid='"+ sData1 +"' order by a.fCreateTime desc) b "+filters;
    
35.                     Table table2 =SQL.select(sql2, null, "/HR/employeeInfo/data", null);
    
36.                     Iterator<Row> rows2 = table2.iterator();
    
37.                         while (rows2.hasNext()) {
    
38.                                 Row rs2 = rows2.next();
    
39.                                 jo = new JSONObject();
    
40.                                 jo.put("HR_emp_AdjustmentApply", rs2.getString(0));  //主表ID
    
41.                                 jo.put("fTitle", rs2.getString("FTITLE"));  //标题
    
42.                                 jo.put("fCreatePsnName", rs2.getString("FCREATEPSNNAME"));//提交人姓名
    
43.                                 jo.put("fCreateTime", rs2.getString("FCREATETIME"));  //提交日期
    
44.                                 jo.put("fBizStateName", rs2.getString("FBIZSTATENAME"));  //业务流程状态名称
    
45.                     jo.put("Status",sStatusID);
    
46.                     jo.put("sName",sName);
    
47.                     jo.put("SA_Task",SA_Task);
    
48.                     jo.put("sProcess",sProcess);
    
49.                     jo.put("sActivity",sActivity);
    
50.                     jo.put("sActivityName",sActivityName);
    
51.                     jo.put("sKindID",sKindID);
    
52.                     jo.put("sEURL",sEURL);
    
53.                     jo.put("ssStatusID",ssStatusID);
    
54.                     jo.put("sExecutorFID",sExecutorFID);
    
55.                                 ja.add(jo);
    
56.                         }       
    
57.                 }
    
58.                 json.put("task", ja);
    
59.                 return json;
    
60.         }

复制代码

这个是我之前查询，现在返回一个table的话，跨库两张表链接怎么查？

jishuang

跨库参考http://docs.wex5.com/bex5-server-question-list-10003/

或者java中处理数据，返回data需要的格式，也可以直接loadData，但需要自己设置data的总记录数

1. JSONArray array = new JSONArray();   
   
2. while (rs.next()) {
   
3.        JSONObject jsonObj = new JSONObject();
   
4.        for (int i = 1; i <= columnCount; i++) {
   
5.                  String columnName = metaData.getColumnLabel(i);
   
6.                  String value = rs.getString(columnName);
   
7.                  jsonObj.put(columnName, value);
   
8.        }
   
9.       array.add(jsonObj);
   
10. }

复制代码

qweasdzxc

jishuang 发表于 2018-3-6 16:53
跨库参考http://docs.wex5.com/bex5-server-question-list-10003/

或者java中处理数据，返回data需要的格 ...

直接loadData，然后自己设置data的总记录数怎么设置

jishuang

data中提供的有API
还有注意ksql中limit的拼接分页查询

qweasdzxc

jishuang 发表于 2018-3-6 17:22
data中提供的有API
还有注意ksql中limit的拼接分页查询

1. var params = new biz.Request.ActionParam();
   
2.                 params.setString("psmID", psmID);
   
3.                 params.setString("sStatusID","tesFinished" );
   
4.                 params.setString("filter", filter);
   
5.                 biz.Request.sendBizRequest({
   
6.                         "context" : this.getContext(),
   
7.                         "action" : "getAdjustmentTaskAction",
   
8.                         "parameters" : params,
   
9.                         "callback" : function(callbackData) {
   
10.                                 if (callbackData.state) {
    
11.                                    if(callbackData.response){
    
12.                                        var task=callbackData.response.task;
    
13.                                        taskData.loadData(task);
    
14.                                        taskData.setTotal(task.length);
    
15.                                        debugger
    
16.                                    }
    
17.                                 }
    
18.                         }
    
19.                 });

复制代码

设置了总数也不会分页啊

jishuang

要在loadData前设置啊
loadData数据都已经加载给data了

qweasdzxc

jishuang 发表于 2018-3-6 17:40
要在loadData前设置啊
loadData数据都已经加载给data了

交换顺序还是一样全部加载

jishuang

那就后端返回table，table中设置总记录数

qweasdzxc

jishuang 发表于 2018-3-7 08:43
那就后端返回table，table中设置总记录数

1.      public static Table getTask(String psmID,String sStatusID,String filter,Integer limit,Integer offset){
   
2.             System.out.println("psmID:"+psmID+" sStatusID:"+sStatusID+" filter："+filter+" limit:"+limit+" offset:"+offset);
   
3.             //查询出本人未审核和已审核记录
   
4.             String ksql="SELECT a.sName AS sName,a.sProcess AS sProcess,a.sActivity AS sActivity,a.sActivityName AS sActivityName,a.sKindID AS sKindID,"+
   
5.                         "a.sEURL AS sEURL,a.sStatusID AS sStatusID,a.sExecutorFID AS sExecutorFID,b AS HR_EMP_ADJUSTMENTAPPLY,b.fTitle AS fTitle," +
   
6.                         "b.fCreatePsnName AS fCreatePsnName,SQL.TO_CHAR (b.fCreateTime,'yyyy-mm-dd hh24:mi:ss') AS fCreateTime,b.fBizStateName AS fBizStateName " +
   
7.                         "FROM hrsys.SA_Task a optional join hrapp.HR_emp_AdjustmentApply b ON a.sData1 = b WHERE a.sExecutorPersonID = '"+ psmID +"' AND " +
   
8.                         "a.sProcess = '/HR/employeeInfo/process/employeeTransferApply/employeeTransferApplyProcess' AND a.sStatusID = '"+ sStatusID +"' AND " +
   
9.                         "a.sActivity <> 'businessActivity1' ORDER BY b.fCreateTime DESC,a.sActivity DESC";
   
10.             Table table =KSQL.select(ksql+ " limit " + Integer.toString(offset) + "," + Integer.toString(limit), null, "/system/data", null);
    
11.             //设置主键
    
12.             table.getProperties().put(Table.PROP_NAME_ROWID, "HR_EMP_ADJUSTMENTAPPLY");
    
13.             //设置总记录数 , 计算分页            
    
14.             table.getProperties().put(Table.PROP_DB_COUNT, table.size());
    
15.               
    
16.             return table;
    
17.         }

复制代码

这种情况是不是就是跨库查询了，KSQL有什么解决办法没？

1. 2018-03-07 09:54:59 excute stream action, params:{"accept":"application/json","action":"getTaskAction","actionFlag":"__action_0__","activity":"businessActivity1","contentType":"json","executeContext":"","executor":"","parameters":{"filter":"","limit":2,"offset":0,"psmID":"PSN01","sStatusID":"tesExecuting"},"process":"/HR/employeeInfo/process/employeeTransferApply/employeeTransferApplyProcess"}
   
2. java.lang.reflect.InvocationTargetException
   
3.     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   
4.     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   
5.     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   
6.     at java.lang.reflect.Method.invoke(Method.java:497)
   
7.     at com.justep.system.action.Engine.invokeActions(Native Method)
   
8.     at com.justep.system.action.Engine.invokeActions(Unknown Source)
   
9.     at com.justep.business.server.BusinessServer.doAction(Unknown Source)
   
10.     at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
    
11.     at com.justep.business.server.BusinessServer.excute(Unknown Source)
    
12.     at com.justep.business.server.BusinessServer.excute(Unknown Source)
    
13.     at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
    
14.     at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
    
15.     at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    
16.     at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
    
17.     at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    
18.     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    
19.     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    
20.     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    
21.     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    
22.     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    
23.     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    
24.     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    
25.     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    
26.     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    
27.     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    
28.     at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    
29.     at java.lang.Thread.run(Thread.java:745)
    
30. Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: SELECT a.sName AS sName,a.sProcess AS sProcess,a.sActivity AS sActivity,a.sActivityName AS sActivityName,a.sKindID AS sKindID,a.sEURL AS sEURL,a.sStatusID AS sStatusID,a.sExecutorFID AS sExecutorFID,b AS HR_emp_TransferApplyM,b.fTitle AS fTitle,b.fCreatePsnName AS fCreatePsnName,SQL.TO_CHAR (b.fCreateTime,'yyyy-mm-dd hh24:mi:ss') AS fCreateTime,b.fBizStateName AS fBizStateName FROM hrsys.SA_Task a optional join hrapp.HR_emp_TransferApplyM b ON a.sData1 = b WHERE a.sExecutorPersonID = 'PSN01' AND a.sProcess = '/HR/employeeInfo/process/employeeTransferApply/employeeTransferApplyProcess' AND a.sStatusID = 'tesExecuting' AND a.sActivity <> 'businessActivity1' ORDER BY b.fCreateTime DESC,a.sActivity DESC limit 0,2
    
31. --> sql:
    
32. --> binds: []
    
33.     at com.justep.exception.BusinessException.create(Unknown Source)
    
34.     at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
    
35.     at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
    
36.     at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
    
37.     at com.justep.system.data.KSQL.select(Unknown Source)
    
38.     at EmployeeInfo.getTask(EmployeeInfo.java:1598)
    
39.     ... 27 more
    
40. Caused by: com.justep.exception.KSQLException: 编码: JUSTEP154115; 提示: KSQL语法错误, Name FROM hrsys^^.SA_Task a opti中未找到需要的元素, 位置390期望是<Identifier>
    
41.     at com.justep.exception.KSQLException.create(Unknown Source)
    
42.     at com.justep.system.ksql.token.TokenManager.matchToken(Unknown Source)
    
43.     at com.justep.system.ksql.token.TokenManager.match(Unknown Source)
    
44.     at com.justep.system.ksql.parser.AbstractParser.conceptDeclare(Unknown Source)
    
45.     at com.justep.system.ksql.parser.AbstractParser.joinedExpression(Unknown Source)
    
46.     at com.justep.system.ksql.parser.AbstractParser.fromNode(Unknown Source)
    
47.     at com.justep.system.ksql.parser.AbstractParser.selectSentenceNode(Unknown Source)
    
48.     at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
    
49.     at com.justep.system.ksql.parser.QueryParser.start(Unknown Source)
    
50.     at com.justep.system.ksql.parser.AbstractParser.parse(Unknown Source)
    
51.     ... 32 more

复制代码

jishuang

12楼的链接中已经发了跨库查询的方案