Commit cfbb978
committed
Server:新增JOIN,初步完成LEFT JOIN
1 parent 0abe229 commit cfbb978
9 files changed
Lines changed: 435 additions & 47 deletions
File tree
- APIJSON-Java-Server
- APIJSONDemo/src/main/java/apijson/demo/server
- APIJSONLibrary/src/main/java/zuo/biao/apijson
- server
Lines changed: 1 addition & 1 deletion
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
@@ -64,7 +64,7 @@ public DemoObjectParser setParser(Parser parser) { | |||
| 64 | 64 | ||
| 65 | 65 | @Override | |
| 66 | 66 | public SQLConfig newSQLConfig() throws Exception { | |
| 67 | - return DemoSQLConfig.newSQLConfig(method, table, sqlRequest); | ||
| 67 | + return DemoSQLConfig.newSQLConfig(method, table, sqlRequest, join); | ||
| 68 | 68 | } | |
| 69 | 69 | ||
| 70 | 70 | ||
Lines changed: 5 additions & 2 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
@@ -14,6 +14,9 @@ | |||
| 14 | 14 | ||
| 15 | 15 | package apijson.demo.server; | |
| 16 | 16 | ||
| 17 | + import java.util.List; | ||
| 18 | + import java.util.Map; | ||
| 19 | + | ||
| 17 | 20 | import com.alibaba.fastjson.JSONObject; | |
| 18 | 21 | ||
| 19 | 22 | import apijson.demo.server.model.Privacy; | |
@@ -74,8 +77,8 @@ public DemoSQLConfig(RequestMethod method, int count, int page) { | |||
| 74 | 77 | * @return | |
| 75 | 78 | * @throws Exception | |
| 76 | 79 | */ | |
| 77 | - public static SQLConfig newSQLConfig(RequestMethod method, String table, JSONObject request) throws Exception { | ||
| 78 | - return newSQLConfig(method, table, request, new Callback() { | ||
| 80 | + public static SQLConfig newSQLConfig(RequestMethod method, String table, JSONObject request, List<Map<String, Object>> join) throws Exception { | ||
| 81 | + return newSQLConfig(method, table, request, join, new Callback() { | ||
| 79 | 82 | ||
| 80 | 83 | @Override | |
| 81 | 84 | public DemoSQLConfig getSQLConfig(RequestMethod method, String table) { | |
Lines changed: 1 addition & 0 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
@@ -82,6 +82,7 @@ public JSONRequest setVersion(String version) { | |||
| 82 | 82 | public static final String KEY_QUERY = "query"; | |
| 83 | 83 | public static final String KEY_COUNT = "count"; | |
| 84 | 84 | public static final String KEY_PAGE = "page"; | |
| 85 | + public static final String KEY_JOIN = "join"; | ||
| 85 | 86 | ||
| 86 | 87 | public static final List<String> ARRAY_KEY_LIST; | |
| 87 | 88 | static { | |
Lines changed: 4 additions & 0 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
@@ -90,5 +90,9 @@ public static boolean isQueryMethod(RequestMethod method) { | |||
| 90 | 90 | public static boolean isPublicMethod(RequestMethod method) { | |
| 91 | 91 | return method == null || method == GET || method == HEAD; | |
| 92 | 92 | } | |
| 93 | + | ||
| 94 | + public static String getName(RequestMethod method) { | ||
| 95 | + return method == null ? GET.name() : method.name(); | ||
| 96 | + } | ||
| 93 | 97 | ||
| 94 | 98 | } | |
APIJSON-Java-Server/APIJSONLibrary/src/main/java/zuo/biao/apijson/server/AbstractObjectParser.java
Lines changed: 2 additions & 0 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
@@ -63,6 +63,7 @@ public AbstractObjectParser setParser(Parser parser) { | |||
| 63 | 63 | protected SQLConfig arrayConfig;//不用final是为了recycle | |
| 64 | 64 | ||
| 65 | 65 | protected final int type; | |
| 66 | + protected final List<Map<String, Object>> join; | ||
| 66 | 67 | protected final boolean isTable; | |
| 67 | 68 | protected final String path; | |
| 68 | 69 | protected final String table; | |
@@ -90,6 +91,7 @@ public AbstractObjectParser(@NotNull JSONObject request, String parentPath, Stri | |||
| 90 | 91 | this.arrayConfig = arrayConfig; | |
| 91 | 92 | ||
| 92 | 93 | this.type = arrayConfig == null ? 0 : arrayConfig.getType(); | |
| 94 | + this.join = arrayConfig == null ? null : arrayConfig.getJoin(); | ||
| 93 | 95 | this.path = AbstractParser.getAbsPath(parentPath, name); | |
| 94 | 96 | this.table = Pair.parseEntry(name, true).getKey(); | |
| 95 | 97 | this.isTable = zuo.biao.apijson.JSONObject.isTableKey(table); | |
Lines changed: 182 additions & 11 deletions
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
@@ -17,7 +17,10 @@ | |||
| 17 | 17 | import static zuo.biao.apijson.RequestMethod.GET; | |
| 18 | 18 | ||
| 19 | 19 | import java.io.UnsupportedEncodingException; | |
| 20 | + import java.util.ArrayList; | ||
| 20 | 21 | import java.util.HashMap; | |
| 22 | + import java.util.HashSet; | ||
| 23 | + import java.util.LinkedHashMap; | ||
| 21 | 24 | import java.util.List; | |
| 22 | 25 | import java.util.Map; | |
| 23 | 26 | import java.util.Set; | |
@@ -78,12 +81,12 @@ public AbstractParser(RequestMethod method, boolean noVerify) { | |||
| 78 | 81 | public Visitor getVisitor() { | |
| 79 | 82 | if (visitor == null) { | |
| 80 | 83 | visitor = new Visitor() { | |
| 81 | - | ||
| 84 | + | ||
| 82 | 85 | @Override | |
| 83 | 86 | public Long getId() { | |
| 84 | 87 | return 0L; | |
| 85 | 88 | } | |
| 86 | - | ||
| 89 | + | ||
| 87 | 90 | @Override | |
| 88 | 91 | public List<Long> getContactIdList() { | |
| 89 | 92 | return null; | |
@@ -302,8 +305,8 @@ protected void onVerifyLogin() throws Exception { | |||
| 302 | 305 | protected void onVerifyContent() throws Exception { | |
| 303 | 306 | requestObject = parseCorrectRequest(); | |
| 304 | 307 | } | |
| 305 | - | ||
| 306 | - | ||
| 308 | + | ||
| 309 | + | ||
| 307 | 310 | /**解析请求JSONObject | |
| 308 | 311 | * @param request => URLDecoder.decode(request, UTF_8); | |
| 309 | 312 | * @return | |
@@ -560,7 +563,7 @@ public JSONObject onObjectParse(final JSONObject request | |||
| 560 | 563 | if (request == null) {// Moment:{} || request.isEmpty()) {//key-value条件 | |
| 561 | 564 | return null; | |
| 562 | 565 | } | |
| 563 | - | ||
| 566 | + | ||
| 564 | 567 | int type = arrayConfig == null ? 0 : arrayConfig.getType(); | |
| 565 | 568 | ||
| 566 | 569 | ObjectParser op = createObjectParser(request, parentPath, name, arrayConfig).parse(); | |
@@ -618,7 +621,7 @@ public JSONObject onObjectParse(final JSONObject request | |||
| 618 | 621 | * @throws Exception | |
| 619 | 622 | */ | |
| 620 | 623 | @Override | |
| 621 | - public JSONArray onArrayParse(final JSONObject request, String parentPath, String name) throws Exception { | ||
| 624 | + public JSONArray onArrayParse(JSONObject request, String parentPath, String name) throws Exception { | ||
| 622 | 625 | Log.i(TAG, "\n\n\n getArray parentPath = " + parentPath | |
| 623 | 626 | + "; name = " + name + "; request = " + JSON.toJSONString(request)); | |
| 624 | 627 | //不能允许GETS，否则会被通过"[]":{"@role":"ADMIN"},"Table":{},"tag":"Table"绕过权限并能批量查询 | |
@@ -634,10 +637,12 @@ public JSONArray onArrayParse(final JSONObject request, String parentPath, Strin | |||
| 634 | 637 | final int query = request.getIntValue(JSONRequest.KEY_QUERY); | |
| 635 | 638 | final int count = request.getIntValue(JSONRequest.KEY_COUNT); | |
| 636 | 639 | final int page = request.getIntValue(JSONRequest.KEY_PAGE); | |
| 640 | + final String join = request.getString(JSONRequest.KEY_JOIN); | ||
| 637 | 641 | request.remove(JSONRequest.KEY_QUERY); | |
| 638 | 642 | request.remove(JSONRequest.KEY_COUNT); | |
| 639 | 643 | request.remove(JSONRequest.KEY_PAGE); | |
| 640 | - Log.d(TAG, "getArray query = " + query + "; count = " + count + "; page = " + page); | ||
| 644 | + request.remove(JSONRequest.KEY_JOIN); | ||
| 645 | + Log.d(TAG, "getArray query = " + query + "; count = " + count + "; page = " + page + "; join = " + join); | ||
| 641 | 646 | ||
| 642 | 647 | if (request.isEmpty()) {//如果条件成立，说明所有的 parentPath/name:request 中request都无效！！！ | |
| 643 | 648 | Log.e(TAG, "getArray request.isEmpty() >> return null;"); | |
@@ -667,7 +672,8 @@ public JSONArray onArrayParse(final JSONObject request, String parentPath, Strin | |||
| 667 | 672 | .setMethod(requestMethod) | |
| 668 | 673 | .setCount(size) | |
| 669 | 674 | .setPage(page) | |
| 670 | - .setQuery(query); | ||
| 675 | + .setQuery(query) | ||
| 676 | + .setJoin(onJoinParse(join, request)); | ||
| 671 | 677 | ||
| 672 | 678 | JSONObject parent; | |
| 673 | 679 | //生成size个 | |
@@ -681,7 +687,7 @@ public JSONArray onArrayParse(final JSONObject request, String parentPath, Strin | |||
| 681 | 687 | } | |
| 682 | 688 | //Table>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
| 683 | 689 | ||
| 684 | - | ||
| 690 | + | ||
| 685 | 691 | /* | |
| 686 | 692 | * 支持引用取值后的数组 | |
| 687 | 693 | { | |
@@ -701,17 +707,182 @@ public JSONArray onArrayParse(final JSONObject request, String parentPath, Strin | |||
| 701 | 707 | if (fo instanceof Boolean || fo instanceof Number || fo instanceof String) { //[{}] 和 [[]] 都没意义 | |
| 702 | 708 | putQueryResult(path, response); | |
| 703 | 709 | } | |
| 704 | - | ||
| 705 | - | ||
| 710 | + | ||
| 711 | + | ||
| 706 | 712 | //后面还可能用到，要还原 | |
| 707 | 713 | request.put(JSONRequest.KEY_QUERY, query); | |
| 708 | 714 | request.put(JSONRequest.KEY_COUNT, count); | |
| 709 | 715 | request.put(JSONRequest.KEY_PAGE, page); | |
| 716 | + request.put(JSONRequest.KEY_JOIN, join); | ||
| 710 | 717 | ||
| 711 | 718 | Log.i(TAG, "getArray return response = \n" + JSON.toJSONString(response) + "\n>>>>>>>>>>>>>>>\n\n\n"); | |
| 712 | 719 | return response; | |
| 713 | 720 | } | |
| 714 | 721 | ||
| 722 | + /**多表同时筛选 | ||
| 723 | + * @param join "&/User/id@,</User[]/User/id{}@,</[]/Comment/momentId@" | ||
| 724 | + * @param request | ||
| 725 | + * @return | ||
| 726 | + * @throws Exception | ||
| 727 | + */ | ||
| 728 | + private List<Map<String, Object>> onJoinParse(String join, JSONObject request) throws Exception { | ||
| 729 | + String[] sArr = request == null || request.isEmpty() ? null : StringUtil.split(join); | ||
| 730 | + if (sArr == null || sArr.length <= 0) { | ||
| 731 | + Log.e(TAG, "doJoin sArr == null || sArr.length <= 0 >> return request;"); | ||
| 732 | + return null; | ||
| 733 | + } | ||
| 734 | + | ||
| 735 | + List<Map<String, Object>> joinList = new ArrayList<>(); | ||
| 736 | + | ||
| 737 | + | ||
| 738 | + JSONObject tableObj; | ||
| 739 | + String targetPath; | ||
| 740 | + | ||
| 741 | + JSONObject targetObj; | ||
| 742 | + String targetTable; | ||
| 743 | + String targetKey; | ||
| 744 | + | ||
| 745 | + String path; | ||
| 746 | + | ||
| 747 | + // List<String> onList = new ArrayList<>(); | ||
| 748 | + for (int i = 0; i < sArr.length; i++) {//User/id@ | ||
| 749 | + //分割 /Table/key | ||
| 750 | + path = "" + sArr[i]; | ||
| 751 | + | ||
| 752 | + int index = path.indexOf("/"); | ||
| 753 | + if (index < 0) { | ||
| 754 | + throw new IllegalArgumentException(JSONRequest.KEY_JOIN + ":value 中value不合法！" | ||
| 755 | + + "必须为 &/Table0/key0,</Table1/key1,... 这种形式！"); | ||
| 756 | + } | ||
| 757 | + String type = path.substring(0, index); //& | ! < > ( ) <> () * | ||
| 758 | + if (StringUtil.isEmpty(type, true)) { | ||
| 759 | + type = "|"; // FULL JOIN / UNIOIN | ||
| 760 | + } | ||
| 761 | + path = path.substring(index + 1); | ||
| 762 | + | ||
| 763 | + index = path.indexOf("/"); | ||
| 764 | + String table = index < 0 ? null : path.substring(0, index);//User | ||
| 765 | + String key = StringUtil.isEmpty(table, true) ? null : path.substring(index + 1);//id@ | ||
| 766 | + if (StringUtil.isEmpty(key, true)) { | ||
| 767 | + throw new IllegalArgumentException(JSONRequest.KEY_JOIN + ":value 中value不合法！" | ||
| 768 | + + "必须为 &/Table0/key0,</Table1/key1,... 这种形式！"); | ||
| 769 | + } | ||
| 770 | + | ||
| 771 | + //取出Table对应的JSONObject，及内部引用赋值 key:value | ||
| 772 | + tableObj = request.getJSONObject(table); | ||
| 773 | + targetPath = tableObj == null ? null : tableObj.getString(key+"@"); | ||
| 774 | + if (StringUtil.isEmpty(targetPath, true)) { | ||
| 775 | + throw new IllegalArgumentException(table + "." + key + "@:value 中value必须为引用赋值的路径 '/targetTable/targetKey' ！"); | ||
| 776 | + } | ||
| 777 | + | ||
| 778 | + //取出引用赋值路径targetPath对应的Table和key | ||
| 779 | + index = targetPath.lastIndexOf("/"); | ||
| 780 | + targetKey = index < 0 ? null : targetPath.substring(index + 1); | ||
| 781 | + if (StringUtil.isEmpty(targetKey, true)) { | ||
| 782 | + throw new IllegalArgumentException(table + "." + key + "@:'/targetTable/targetKey' 中targetKey不能为空！"); | ||
| 783 | + } | ||
| 784 | + | ||
| 785 | + targetPath = targetPath.substring(0, index); | ||
| 786 | + index = targetPath.lastIndexOf("/"); | ||
| 787 | + targetTable = index < 0 ? targetPath : targetPath.substring(index + 1); | ||
| 788 | + | ||
| 789 | + | ||
| 790 | + //对引用的JSONObject添加条件 | ||
| 791 | + targetObj = request.getJSONObject(targetTable); | ||
| 792 | + if (targetObj == null) { | ||
| 793 | + throw new IllegalArgumentException(targetTable + "." + targetKey | ||
| 794 | + + "@:'/targetTable/targetKey' 中路径对应的对象不存在！"); | ||
| 795 | + } | ||
| 796 | + targetObj.put(key+"@", targetObj.remove(key+"@")); //保证和SQLExcecutor缓存的Config里where顺序一致，生成的SQL也就一致 | ||
| 797 | + | ||
| 798 | + Map<String, Object> joinMap = new LinkedHashMap<>(); | ||
| 799 | + joinMap.put("type", type); //TODO 要么减少数量，要么封装为一个对象 | ||
| 800 | + joinMap.put("key", key); | ||
| 801 | + joinMap.put("targetKey", targetKey); | ||
| 802 | + joinMap.put("targetTable", targetTable); | ||
| 803 | + joinMap.put("name", table); | ||
| 804 | + joinMap.put("table", getJoinObject(table, tableObj, key)); | ||
| 805 | + | ||
| 806 | + joinList.add(joinMap); | ||
| 807 | + | ||
| 808 | + // onList.add(table + "." + key + " = " + targetTable + "." + targetKey); // ON User.id = Moment.userId | ||
| 809 | + | ||
| 810 | + } | ||
| 811 | + | ||
| 812 | + | ||
| 813 | + //拼接多个 SQLConfig 的SQL语句，然后执行，再把结果分别缓存(Moment, User等)到 SQLExecutor 的 cacheMap | ||
| 814 | + // AbstractSQLConfig config0 = null; | ||
| 815 | + // String sql = "SELECT " + config0.getColumnString() + " FROM " + config0.getTable() + " INNER JOIN " + targetTable + " ON " | ||
| 816 | + // + onList.get(0) + config0.getGroupString() + config0.getHavingString() + config0.getOrderString(); | ||
| 817 | + | ||
| 818 | + | ||
| 819 | + return joinList; | ||
| 820 | + } | ||
| 821 | + | ||
| 822 | + | ||
| 823 | + | ||
| 824 | + private static final List<String> JOIN_COPY_KEY_LIST; | ||
| 825 | + static {//TODO 不全 | ||
| 826 | + JOIN_COPY_KEY_LIST = new ArrayList<String>(); | ||
| 827 | + JOIN_COPY_KEY_LIST.add(JSONRequest.KEY_SCHEMA); | ||
| 828 | + JOIN_COPY_KEY_LIST.add(JSONRequest.KEY_COLUMN); | ||
| 829 | + JOIN_COPY_KEY_LIST.add(JSONRequest.KEY_ORDER); | ||
| 830 | + JOIN_COPY_KEY_LIST.add(JSONRequest.KEY_COMBINE); | ||
| 831 | + JOIN_COPY_KEY_LIST.add(JSONRequest.KEY_HAVING); | ||
| 832 | + JOIN_COPY_KEY_LIST.add(JSONRequest.KEY_GROUP); | ||
| 833 | + } | ||
| 834 | + | ||
| 835 | + /** | ||
| 836 | + * 取指定json对象的id集合 | ||
| 837 | + * @param table | ||
| 838 | + * @param key | ||
| 839 | + * @param obj | ||
| 840 | + * @param targetKey | ||
| 841 | + * @return null ? 全部 : 有限的数组 | ||
| 842 | + */ | ||
| 843 | + private JSONObject getJoinObject(String table, JSONObject obj, String key) { | ||
| 844 | + if (obj == null || obj.isEmpty()) { | ||
| 845 | + Log.e(TAG, "getIdList obj == null || obj.isEmpty() >> return null;"); | ||
| 846 | + return null; | ||
| 847 | + } | ||
| 848 | + if (StringUtil.isEmpty(key, true)) { | ||
| 849 | + Log.e(TAG, "getIdList StringUtil.isEmpty(key, true) >> return null;"); | ||
| 850 | + return null; | ||
| 851 | + } | ||
| 852 | + | ||
| 853 | + //取出所有join条件 | ||
| 854 | + JSONObject requestObj = new JSONObject(true);//(JSONObject) obj.clone();// | ||
| 855 | + HashSet<String> set = new HashSet<>(obj.keySet()); | ||
| 856 | + for (String k : set) { | ||
| 857 | + if (StringUtil.isEmpty(k, true)) { | ||
| 858 | + continue; | ||
| 859 | + } | ||
| 860 | + | ||
| 861 | + if (k.startsWith("@")) { | ||
| 862 | + if (JOIN_COPY_KEY_LIST.contains(k)) { | ||
| 863 | + requestObj.put(k, obj.get(k)); //保留 | ||
| 864 | + } | ||
| 865 | + } | ||
| 866 | + else { | ||
| 867 | + if (k.endsWith("@")) { | ||
| 868 | + if (k.equals(key+"@")) { | ||
| 869 | + continue; | ||
| 870 | + } | ||
| 871 | + throw new UnsupportedOperationException(table + "." + k + " 不合法！" + JSONRequest.KEY_JOIN | ||
| 872 | + + " 关联的Table中只能有1个 key@:value ！"); | ||
| 873 | + } | ||
| 874 | + | ||
| 875 | + if (k.contains("()") == false) { //不需要远程函数 | ||
| 876 | + // requestObj.put(k, obj.remove(k)); //remove是为了避免重复查询副表 | ||
| 877 | + requestObj.put(k, obj.get(k)); //remove是为了避免重复查询副表 | ||
| 878 | + } | ||
| 879 | + } | ||
| 880 | + } | ||
| 881 | + | ||
| 882 | + | ||
| 883 | + return requestObj; | ||
| 884 | + } | ||
| 885 | + | ||
| 715 | 886 | ||
| 716 | 887 | /**根据路径取值 | |
| 717 | 888 | * @param parent | |
0 commit comments