Skip to content

Commit 509d245

Browse files
TommyLemonTommyLemon
committed
Server:自动化JOIN新增支持CROSS,SIDE;解决多个JOIN的WHERE条件拼接错误
1 parent 47670c2 commit 509d245

File tree

4 files changed

+57
-43
lines changed

4 files changed

+57
-43
lines changed

‎APIJSON-Java-Server/APIJSONDemo/src/main/java/apijson/demo/server/DemoSQLConfig.java‎

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -32,8 +32,6 @@
3232
*/
3333
public class DemoSQLConfig extends AbstractSQLConfig {
3434

35-
public static final String DATABASE_MYSQL = "MySQL";
36-
public static final String DATABASE_POSTGRESQL = "PostgreSQL";
3735

3836
//表名映射，隐藏真实表名，对安全要求很高的表可以这么做
3937
static {

‎APIJSON-Java-Server/APIJSONLibrary/src/main/java/zuo/biao/apijson/server/AbstractSQLConfig.java‎

Lines changed: 50 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -50,6 +50,7 @@
5050
import com.alibaba.fastjson.JSONArray;
5151
import com.alibaba.fastjson.JSONObject;
5252
import com.alibaba.fastjson.annotation.JSONField;
53+
import com.sun.xml.internal.ws.util.StringUtils;
5354

5455
import zuo.biao.apijson.Log;
5556
import zuo.biao.apijson.RequestMethod;
@@ -191,7 +192,7 @@ public SQLConfig setDatabase(String database) {
191192
this.database = database;
192193
return this;
193194
}
194-
195+
195196
@Override
196197
public String getSchema() {
197198
String sqlTable = getSQLTable();
@@ -611,7 +612,7 @@ public String getColumnString() throws Exception {
611612
}
612613

613614
}
614-
615+
615616
c = StringUtil.getString(keys);
616617

617618
return (c.contains(":") == false ? c : c.replaceAll(":", " AS ")) + (StringUtil.isEmpty(joinColumn, true) ? "" : ", " + joinColumn);//不能在这里改，后续还要用到:
@@ -959,6 +960,12 @@ else if ("!".equals(ce.getKey())) {
959960

960961
if (joinList != null) {
961962

963+
String newWs = "";
964+
String ws = "" + whereString;
965+
966+
List<Object> newPvl = new ArrayList<>();
967+
List<Object> pvl = new ArrayList<>(preparedValueList);
968+
962969
SQLConfig jc;
963970
String js;
964971
//各种 JOIN 没办法统一用 & | ！连接，只能按优先级，和 @combine 一样?
@@ -968,8 +975,8 @@ else if ("!".equals(ce.getKey())) {
968975
case "|": //不支持 <>, [] ，避免太多符号
969976
case "&":
970977
case "!":
971-
logic = Logic.getType(j.getJoinType());
972-
978+
case "^":
979+
case "*":
973980
jc = j.getJoinConfig();
974981
boolean isMain = jc.isMain();
975982
jc.setMain(false).setPrepared(isPrepared()).setPreparedValueList(new ArrayList<Object>());
@@ -980,21 +987,44 @@ else if ("!".equals(ce.getKey())) {
980987
continue;
981988
}
982989

983-
whereString = " ( "
984-
+ getCondition(
985-
Logic.isNot(logic),
986-
whereString
987-
+ ( StringUtil.isEmpty(whereString, true) ? "" : (Logic.isAnd(logic) ? AND : OR) )
988-
+ " ( " + js + " ) "
989-
)
990-
+ " ) ";
990+
if (StringUtil.isEmpty(newWs, true) == false) {
991+
newWs += AND;
992+
}
993+
994+
if ("^".equals(j.getJoinType())) { // (A & ! B) | (B & ! A)
995+
newWs += " ( ( " + ws + ( StringUtil.isEmpty(ws, true) ? "" : AND + NOT ) + " ( " + js + " ) ) "
996+
+ OR
997+
+ " ( " + js + AND + NOT + " ( " + ws + " ) ) ) ";
998+
999+
newPvl.addAll(pvl);
1000+
newPvl.addAll(jc.getPreparedValueList());
1001+
newPvl.addAll(jc.getPreparedValueList());
1002+
newPvl.addAll(pvl);
1003+
}
1004+
else {
1005+
logic = Logic.getType(j.getJoinType());
1006+
1007+
newWs += " ( "
1008+
+ getCondition(
1009+
Logic.isNot(logic),
1010+
ws
1011+
+ ( StringUtil.isEmpty(ws, true) ? "" : (Logic.isAnd(logic) ? AND : OR) )
1012+
+ " ( " + js + " ) "
1013+
)
1014+
+ " ) ";
1015+
1016+
newPvl.addAll(pvl);
1017+
newPvl.addAll(jc.getPreparedValueList());
1018+
}
9911019

992-
preparedValueList.addAll(jc.getPreparedValueList());
9931020
break;
9941021
//可能 LEFT JOIN 和 INNER JOIN 同时存在 default:
9951022
// throw new UnsupportedOperationException("");
9961023
}
9971024
}
1025+
1026+
whereString = newWs;
1027+
preparedValueList = newPvl;
9981028
}
9991029

10001030
String s = whereString.isEmpty() ? "" : (hasPrefix ? " WHERE " : "") + whereString;
@@ -1581,8 +1611,10 @@ public String getJoinString() throws Exception {
15811611
case "|": //不支持 <>, [] ，避免太多符号
15821612
case "&":
15831613
case "!":
1584-
sql = " INNER JOIN " + jc.getTablePath() + " ON " + jc.getTable() + "." + j.getKey() + " = "
1585-
+ j.getTargetName() + "." + j.getTargetKey();
1614+
case "^":
1615+
case "*":
1616+
sql = ("*".equals(j.getJoinType()) ? " CROSS JOIN " : " INNER JOIN ") + jc.getTablePath()
1617+
+ " ON " + jc.getTable() + "." + j.getKey() + " = " + j.getTargetName() + "." + j.getTargetKey();
15861618
break;
15871619
case "<":
15881620
case ">":
@@ -1595,7 +1627,7 @@ public String getJoinString() throws Exception {
15951627
preparedValueList.addAll(jc.getPreparedValueList());
15961628
break;
15971629
default:
1598-
throw new UnsupportedOperationException("服务器内部错误：不支持JOIN类型 " + type + " !");
1630+
throw new UnsupportedOperationException("服务器内部错误：不支持JOIN类型 " + j.getJoinType() + " !");
15991631
}
16001632

16011633
joinOns += " \n " + sql;
@@ -1882,11 +1914,11 @@ LEFT JOIN ( SELECT count(*) AS count FROM sys.Comment ) AS Comment ON Comment.m
18821914
if (RequestMethod.isHeadMethod(method, true)) {
18831915
joinConfig.setMethod(GET); //子查询不能为 SELECT count(*) ，而应该是 SELECT momentId
18841916
joinConfig.setColumn(j.getKey()); //优化性能，不取非必要的字段
1885-
1917+
18861918
cacheConfig.setMethod(GET); //子查询不能为 SELECT count(*) ，而应该是 SELECT momentId
18871919
cacheConfig.setColumn(j.getKey()); //优化性能，不取非必要的字段
18881920
}
1889-
1921+
18901922
j.setJoinConfig(joinConfig);
18911923
j.setCacheConfig(cacheConfig);
18921924
}

‎APIJSON-Java-Server/APIJSONLibrary/src/main/java/zuo/biao/apijson/server/Join.java‎

Lines changed: 1 addition & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@
2121
*/
2222
public class Join {
2323

24-
private String joinType; // "&" - INNER, "|" - FULL, "!" - OUTTER, "<" - LEFT, ">" - RIGHT
24+
private String joinType; // "&" - INNER, "|" - FULL, "!" - OUTTER, "<" - LEFT, ">" - RIGHT, "*" - CROSS, "^" - SIDE
2525
private String relateType; // "" - 一对一, "{}" - 一对多, "<>" - 多对一
2626
private JSONObject table; // { "id@":"/Moment/userId" }
2727
private String name; //User
@@ -114,23 +114,4 @@ else if (originKey.endsWith("<>")) {
114114
}
115115
}
116116

117-
public String getJoinTypeName() throws Exception {
118-
return getJoinTypeName(joinType);
119-
}
120-
public static String getJoinTypeName(@NotNull String type) throws Exception {
121-
switch (type) {
122-
case "":
123-
case "&":
124-
case "|": //不支持 <>, [] ，避免太多符号
125-
case "!":
126-
return " INNER JOIN "; //TODO & | ! 通过 WHERE NOT( {B.where} OR {B.where} ) 解决
127-
case "<":
128-
return " LEFT JOIN ";
129-
case ">":
130-
return " RIGIHT JOIN ";
131-
default:
132-
throw new UnsupportedOperationException("服务器内部错误：不支持JOIN类型 " + type + " !");
133-
}
134-
}
135-
136117
}

‎APIJSON-Java-Server/APIJSONLibrary/src/main/java/zuo/biao/apijson/server/SQLConfig.java‎

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -25,13 +25,16 @@
2525
*/
2626
public interface SQLConfig {
2727

28+
String DATABASE_MYSQL = "MySQL";
29+
String DATABASE_POSTGRESQL = "PostgreSQL";
30+
2831
String SCHEMA_INFORMATION = "`information_schema`";
2932
String TABLE_SCHEMA = "`table_schema`";
3033
String TABLE_NAME = "`table_name`";
3134

32-
public static final int TYPE_CHILD = 0;
33-
public static final int TYPE_ITEM = 1;
34-
public static final int TYPE_ITEM_CHILD_0 = 2;
35+
int TYPE_CHILD = 0;
36+
int TYPE_ITEM = 1;
37+
int TYPE_ITEM_CHILD_0 = 2;
3538

3639
/**获取数据库地址
3740
* @return

0 commit comments

Comments
 (0)