树型结构

qweasdzxc

想弄一棵树，可是最后一层节点来自另外一个表，这个要怎么做？

jishuang

参考http://docs.wex5.com/comps-tree/中的案例4

qweasdzxc

jishuang 发表于 2017-3-27 17:03
参考http://docs.wex5.com/comps-tree/中的案例4

参照着修改以后代码如下，但是报错，什么原因？
代码：

1. private static final String TABLE_LIBRARY = "SAM_LIBRARY";
   
2.     private static final String TABLE_ASSET = "SAM_ASSET";
   
3.     public static Table querySAM_AssetTree(String concept,String select,String from,
   
4.                 String aggregate,String dataModel,String fnModel,String condition,
   
5.                 List range,Boolean distinct,String idColumn,String filter,Integer limit,
   
6.                 Integer offset,String columns,String orderBy,String aggregateColumns,
   
7.                 Map variables){
   
8.                 int level = (Integer) variables.get("level");
   
9.         String parent = (variables.get("parent")+"");
   
10.         // 当查询资产的时候，需要得到数据库中真正的fID。
    
11.         if (level > 2) {
    
12.             parent = parent.split("-")[1];
    
13.         }
    
14.         String sql = null, sqlWhere = null, showTable = null;
    
15.         // 根据级别判断，来决定访问那个数据库，返回值中返回自定义列fLevel和fIsLeaves
    
16.         switch (level) {
    
17.         case 1:
    
18.             sql = "select null as fParentID,1 as fLevel,false as fIsLeaves,fID,fName from"
    
19.             + TABLE_LIBRARY+" GROUP BY fID limit "+offset+","+limit;
    
20.             sqlWhere = " where SAM_ASSET.fApprovedState='已发布'";
    
21.             showTable = TABLE_LIBRARY;
    
22.             break;
    
23.         case 2:
    
24.             sql = "select fID as fParentID,2 as fLevel,false as fIsLeaves,"
    
25.             +"concat_ws('-','"+parent+"',CONVERT(fID,char)) as fID,fName from "
    
26.             + TABLE_LIBRARY+" where fID = "+parent+" GROUP BY fID limit "+offset+","+limit;
    
27.             sqlWhere = " where fID = "+parent;
    
28.             showTable = TABLE_LIBRARY;
    
29.             break;
    
30.         case 3:
    
31.             sql = "select fID as fParentID,3 as fLevel,true as fIsLeaves,"
    
32.             + "concat_ws('-','"+parent+"',CONVERT(fID,char)) as fID,fName from "
    
33.             + TABLE_ASSET+" where fCityID = "+parent+" GROUP BY fID limit "+offset+","+limit;
    
34.             sqlWhere = " where fID = "+parent;
    
35.             showTable = TABLE_ASSET;
    
36.             break;
    
37.         }
    
38.         HashMap<String,String> sqlMap = new HashMap<String,String>();
    
39.         sqlMap.put(DatabaseProduct.MYSQL.name(),sql);
    
40.         Table table = SQL.select(sqlMap, null, "/SAM/assetManage/data");
    
41.         table.getProperties().put(Table.PROP_NAME_ROWID, "fID");
    
42.         // 设置table的总行数，bizData分页需要
    
43.         String countSql = "select count(*) from "+showTable+" "+sqlWhere;
    
44.         HashMap<String,String> countSqlMap = new HashMap<String,String>();
    
45.         countSqlMap.put(DatabaseProduct.MYSQL.name(),countSql);
    
46.         Table countTable = SQL.select(countSqlMap, null, "/SAM/assetManage/data");
    
47.         Iterator<Row> rows = countTable.iterator();
    
48.         Row row = rows.next();
    
49.         int count = row.getInt(0);
    
50. 
    
51.         table.getProperties().put(Table.PROP_DB_COUNT, count);
    
52.         return table;
    
53.         }

复制代码

报错：

1. 2017-03-27 18:18:41 excute stream action, params:{"accept":"application/json","action":"querySAM_AssetTreeAction","actionFlag":"__action_0__","activity":"mainActivity","contentType":"json","executeContext":"","executor":"","parameters":{"columns":"SAM_AssetTree,fID,fIsLeaves,fLevel,fName,fParentID,version","filter":"(SAM_AssetTree.fParentID IS NULL)","limit":-1,"offset":0,"variables":{"level":1}},"process":"/SAM/assetManage/process/newPortal/newPortalProcess","translateParameter":{"cellnameByRelation":false,"dataType":"row-tree","includeState":true,"rowsConfig":{"concept":"SAM_AssetTree","sequence":"SAM_AssetTree,fID,fIsLeaves,fLevel,fName,fParentID,version"},"transformIdcolumn":true,"useNamespace":true}}
   
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: 编码: JUSTEP150037; 提示: 没有找到与当前数据库"ORACLE"匹配的SQL语句
    
31.         at com.justep.exception.BusinessException.create(Unknown Source)
    
32.         at com.justep.util.Utils.check(Unknown Source)
    
33.         at com.justep.system.data.SQL.select(Unknown Source)
    
34.         at com.justep.system.data.SQL.select(Unknown Source)
    
35.         at AssetManage.querySAM_AssetTree(AssetManage.java:264)
    
36.         ... 27 more

复制代码

jishuang

• 编码: JUSTEP150037; 提示: 没有找到与当前数据库"ORACLE"匹配的SQL语句
•         at com.justep.exception.BusinessException.create(Unknown Source)
•         at com.justep.util.Utils.check(Unknown Source)
•         at com.justep.system.data.SQL.select(Unknown Source)
•         at com.justep.system.data.SQL.select(Unknown Source)
•         at AssetManage.querySAM_AssetTree(AssetManage.java:264)
  

报错已经很明显了啊
java代码有问题，自己跟踪一下java代码，最终执行的时候没有服务的sql

qweasdzxc

jishuang 发表于 2017-3-28 08:43

编码: JUSTEP150037; 提示: 没有找到与当前数据库"ORACLE"匹配的SQL语句

        at com.justep.exc ...

控制台只输出55555，没输出666666，什么原因？

1. HashMap<String,String> sqlMap = new HashMap<String,String>();
   
2.         System.out.println("444444444444444444");
   
3.         sqlMap.put(DatabaseProduct.MYSQL.name(),sql);
   
4.         System.out.println("555555555555555");
   
5.         Table table = SQL.select(sqlMap, null, "/SAM/assetManage/data");
   
6.         System.out.println("66666666666666666666666");
   
7.         table.getProperties().put(Table.PROP_NAME_ROWID, "fID");
   
8.         // 设置table的总行数，bizData分页需要
   
9.         String countSql = "select count(*) from "+showTable+" "+sqlWhere;
   
10.         HashMap<String,String> countSqlMap = new HashMap<String,String>();
    
11.         countSqlMap.put(DatabaseProduct.MYSQL.name(),countSql);
    
12.         Table countTable = SQL.select(countSqlMap, null, "/SAM/assetManage/data");
    
13.         Iterator<Row> rows = countTable.iterator();
    
14.         Row row = rows.next();
    
15.         int count = row.getInt(0);
    
16. 
    
17.         table.getProperties().put(Table.PROP_DB_COUNT, count);
    
18.         return table;

复制代码

jishuang

你的变量sql怎么是地方定义了，确认变量是否有值啊

qweasdzxc

jishuang 发表于 2017-3-28 10:48
你的变量sql怎么是地方定义了，确认变量是否有值啊

这是我现在所有的代码，我是模仿你给我的案例做的？

1. private static final String TABLE_LIBRARY = "SAM_LIBRARY";
   
2.     private static final String TABLE_ASSET = "SAM_ASSET";
   
3.     public static Table querySAM_AssetTree(String concept,String select,String from,
   
4.                 String aggregate,String dataModel,String fnModel,String condition,
   
5.                 List range,Boolean distinct,String idColumn,String filter,Integer limit,
   
6.                 Integer offset,String columns,String orderBy,String aggregateColumns,
   
7.                 Map variables){
   
8.                 int level = (Integer) variables.get("level");
   
9.         String parent = (variables.get("parent")+"");
   
10.         // 当查询资产的时候，需要得到数据库中真正的fID。
    
11.         if (level > 2) {
    
12.             parent = parent.split("-")[1];
    
13.         }
    
14.         String sql = null, sqlWhere = null, showTable = null;
    
15.         System.out.println("11111111");
    
16.         // 根据级别判断，来决定访问那个数据库，返回值中返回自定义列fLevel和fIsLeaves
    
17.         switch (level) {
    
18.         case 1:
    
19.                 System.out.println("2222222222");
    
20.             sql = "select null as fParentID,1 as fLevel,false as fIsLeaves,fID,fName from"
    
21.             + TABLE_LIBRARY+" GROUP BY fID limit "+offset+","+limit;
    
22.             sqlWhere = " where SAM_ASSET.fApprovedState='已发布'";
    
23.             showTable = TABLE_LIBRARY;
    
24.             System.out.println("3333333333333333");
    
25.             break;
    
26.         case 2:
    
27.             sql = "select fID as fParentID,2 as fLevel,false as fIsLeaves,"
    
28.             +"concat_ws('-','"+parent+"',CONVERT(fID,char)) as fID,fName from "
    
29.             + TABLE_LIBRARY+" where fID = "+parent+" GROUP BY fID limit "+offset+","+limit;
    
30.             sqlWhere = " where fID = "+parent;
    
31.             showTable = TABLE_LIBRARY;
    
32.             break;
    
33.         case 3:
    
34.             sql = "select fID as fParentID,3 as fLevel,true as fIsLeaves,"
    
35.             + "concat_ws('-','"+parent+"',CONVERT(fID,char)) as fID,fName from "
    
36.             + TABLE_ASSET+" where fCityID = "+parent+" GROUP BY fID limit "+offset+","+limit;
    
37.             sqlWhere = " where fID = "+parent;
    
38.             showTable = TABLE_ASSET;
    
39.             break;
    
40.         }
    
41.         HashMap<String,String> sqlMap = new HashMap<String,String>();
    
42.         System.out.println("444444444444444444");
    
43.         sqlMap.put(DatabaseProduct.MYSQL.name(),sql);
    
44.         System.out.println("555555555555555");
    
45.         Table table = SQL.select(sqlMap, null, "/SAM/assetManage/data");
    
46.         System.out.println("66666666666666666666666");
    
47.         table.getProperties().put(Table.PROP_NAME_ROWID, "fID");
    
48.         // 设置table的总行数，bizData分页需要
    
49.         String countSql = "select count(*) from "+showTable+" "+sqlWhere;
    
50.         HashMap<String,String> countSqlMap = new HashMap<String,String>();
    
51.         countSqlMap.put(DatabaseProduct.MYSQL.name(),countSql);
    
52.         Table countTable = SQL.select(countSqlMap, null, "/SAM/assetManage/data");
    
53.         Iterator<Row> rows = countTable.iterator();
    
54.         Row row = rows.next();
    
55.         int count = row.getInt(0);
    
56. 
    
57.         table.getProperties().put(Table.PROP_DB_COUNT, count);
    
58.         return table;
    
59.         }

复制代码

jishuang

自己java调试看看啊，具体执行的你的那个case找sql了
没有执行那就是case不对
案例是告诉你怎么做，不是完全就跟你需要一样，具体的逻辑判断要自己修改

qweasdzxc

jishuang 发表于 2017-3-28 13:21
自己java调试看看啊，具体执行的你的那个case找sql了
没有执行那就是case不对
案例是告诉你怎么做，不是完 ...

案例中代码我已经修改过一些，是sql导致的吗？

红色的地方要修改吗？
HashMap<String,String> sqlMap = new HashMap<String,String>();        sqlMap.put(DatabaseProduct.MYSQL.name(),sql);
        Table table = SQL.select(sqlMap, null, "/demo/common/data");
        table.getProperties().put(Table.PROP_NAME_ROWID, "fID");
        // 设置table的总行数，bizData分页需要
        String countSql = "select count(*) from "+showTable+" "+sqlWhere;
        HashMap<String,String> countSqlMap = new HashMap<String,String>();
        countSqlMap.put(DatabaseProduct.MYSQL.name(),countSql);
        Table countTable = SQL.select(countSqlMap, null, "/demo/common/data");
        Iterator<Row> rows = countTable.iterator();
        Row row = rows.next();
        int count = row.getInt(0);

        table.getProperties().put(Table.PROP_DB_COUNT, count);
        return table;


java调试没找到源要怎么设置？

qweasdzxc

qweasdzxc 发表于 2017-3-28 14:36
案例中代码我已经修改过一些，是sql导致的吗？

红色的地方要修改吗？

报错原因是group by 和limit
我现在修改了一下，代码向下面：
    private static final String TABLE_LIBRARY = "SAM_LIBRARY";
    private static final String TABLE_ASSET = "SAM_ASSET";
    public static Table querySAM_AssetTree(String concept,String select,String from,
            String aggregate,String dataModel,String fnModel,String condition,
            List range,Boolean distinct,String idColumn,String filter,Integer limit,
            Integer offset,String columns,String orderBy,String aggregateColumns,
            Map variables){
        int level = (Integer) variables.get("level");
        String parent = (variables.get("parent")+"");
        // 当查询资产的时候，需要得到数据库中真正的fID。
        if (level > 2) {
            parent = parent.split("-")[1];
        }
        String sql = null, sqlWhere = null, showTable = null;
        System.out.println("parent:"+parent);
        System.out.println("11111111");
        // 根据级别判断，来决定访问那个数据库，返回值中返回自定义列fLevel和fIsLeaves
        switch (level) {
        case 1:
            System.out.println("2222222222");
            sql = "select null as fParentID,1 as fLevel,'false' as fIsLeaves,fID,fName from "
            + TABLE_LIBRARY+" GROUP BY fID limit "+offset+","+limit;
            sqlWhere = "";
            showTable = TABLE_LIBRARY;
            System.out.println("3333333333333333");
            break;
        case 2:
            sql = "select fID as fParentID,2 as fLevel,'false' as fIsLeaves,"
            +"concat_ws('-','"+parent+"',CONVERT(fID,char)) as fID,fName from "
            + TABLE_LIBRARY+" where fID = "+parent+" GROUP BY fID limit "+offset+","+limit;
            sqlWhere = " where fID = "+parent;
            showTable = TABLE_LIBRARY;
            break;
        case 3:
            sql = "select fID as fParentID,3 as fLevel,'true' as fIsLeaves,"
            + "concat_ws('-','"+parent+"',CONVERT(fID,char)) as fID,fName from "
            + TABLE_ASSET+" where fID = "+parent+" GROUP BY fID limit "+offset+","+limit;
            sqlWhere = " where fID = "+parent+" and fApprovedState='已发布'";
            showTable = TABLE_ASSET;
            break;
        }
        System.out.println("sql:"+sql);
        HashMap<String,String> sqlMap = new HashMap<String,String>();
        System.out.println("444444444444444444");
        sqlMap.put(DatabaseProduct.ORACLE.toString(),sql);
        System.out.println("555555555555555");
        Table table = SQL.select(sqlMap, null, "/SAM/assetManage/data");
        System.out.println("66666666666666666666666");
        table.getProperties().put(Table.PROP_NAME_ROWID, "fID");
        // 设置table的总行数，bizData分页需要
        String countSql = "select count(*) from "+showTable+" "+sqlWhere;
        HashMap<String,String> countSqlMap = new HashMap<String,String>();
        countSqlMap.put(DatabaseProduct.ORACLE.toString(),countSql);
        Table countTable = SQL.select(countSqlMap, null, "/SAM/assetManage/data");
        Iterator<Row> rows = countTable.iterator();
        Row row = rows.next();
        int count = row.getInt(0);

        table.getProperties().put(Table.PROP_DB_COUNT, count);
        return table;
    }


控制台输出：

1. parent:null
   
2. 11111111
   
3. 2222222222
   
4. 3333333333333333
   
5. sql:select null as fParentID,1 as fLevel,'false' as fIsLeaves,fID,fName from SAM_LIBRARY GROUP BY fID limit 0,-1
   
6. 444444444444444444
   
7. ORACLE
   
8. 555555555555555
   
9. 2017-03-28 15:37:03 excute stream action, params:{"accept":"application/json","action":"querySAM_AssetTreeAction","actionFlag":"__action_0__","activity":"mainActivity","contentType":"json","executeContext":"","executor":"","parameters":{"columns":"SAM_AssetTree,fID,fIsLeaves,fLevel,fName,fParentID,version","filter":"(SAM_AssetTree.fParentID IS NULL)","limit":-1,"offset":0,"variables":{"level":1}},"process":"/SAM/assetManage/process/newPortal/newPortalProcess","translateParameter":{"cellnameByRelation":false,"dataType":"row-tree","includeState":true,"rowsConfig":{"concept":"SAM_AssetTree","sequence":"SAM_AssetTree,fID,fIsLeaves,fLevel,fName,fParentID,version"},"transformIdcolumn":true,"useNamespace":true}}
   
10. java.lang.reflect.InvocationTargetException
    
11.         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    
12.         at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    
13.         at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    
14.         at java.lang.reflect.Method.invoke(Method.java:497)
    
15.         at com.justep.system.action.Engine.invokeActions(Native Method)
    
16.         at com.justep.system.action.Engine.invokeActions(Unknown Source)
    
17.         at com.justep.business.server.BusinessServer.doAction(Unknown Source)
    
18.         at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
    
19.         at com.justep.business.server.BusinessServer.excute(Unknown Source)
    
20.         at com.justep.business.server.BusinessServer.excute(Unknown Source)
    
21.         at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
    
22.         at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
    
23.         at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    
24.         at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
    
25.         at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
    
26.         at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    
27.         at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    
28.         at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    
29.         at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    
30.         at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    
31.         at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
    
32.         at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    
33.         at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
    
34.         at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
    
35.         at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
    
36.         at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    
37.         at java.lang.Thread.run(Thread.java:745)
    
38. Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: select null as fParentID,1 as fLevel,'false' as fIsLeaves,fID,fName from SAM_LIBRARY GROUP BY fID limit 0,-1, binds: []出错
    
39.         at com.justep.exception.BusinessException.create(Unknown Source)
    
40.         at com.justep.system.data.SQL.select(Unknown Source)
    
41.         at com.justep.system.data.SQL.select(Unknown Source)
    
42.         at AssetManage.querySAM_AssetTree(AssetManage.java:272)
    
43.         ... 27 more
    
44. Caused by: java.sql.SQLException: ORA-00933: SQL 命令未正确结束
    
45. 
    
46.         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    
47.         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    
48.         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    
49.         at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    
50.         at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
    
51.         at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796)
    
52.         at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)
    
53.         at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836)
    
54.         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)
    
55.         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
    
56.         at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
    
57.         at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    
58.         at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
    
59.         ... 30 more

复制代码