Skip to content

Commit ceca887

Browse files
TommyLemonTommyLemon
committed
解决 JOIN 当主副表都有条件、子查询当内外查询都有条件时可能预编译值错位
1 parent f84c6de commit ceca887

File tree

1 file changed

+51
-24
lines changed

1 file changed

+51
-24
lines changed

‎APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java‎

Lines changed: 51 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -3808,11 +3808,33 @@ public String getSubqueryString(Subquery subquery) throws Exception {
38083808
cfg.setPreparedValueList(new ArrayList<>());
38093809
String sql = (range == null || range.isEmpty() ? "" : range) + "(" + cfg.getSQL(isPrepared()) + ") ";
38103810

3811-
List<Object> origPreparedValueList = preparedValueList;
3812-
preparedValueList = cfg.getPreparedValueList();
3813-
preparedValueList.addAll(origPreparedValueList);
3811+
//// SELECT .. FROM(SELECT ..) .. WHERE .. 格式需要把子查询中的预编译值提前
3812+
//// 如果外查询 SELECT concat(`name`,?) 这种 SELECT 里也有预编译值，那就不能这样简单反向
3813+
//List<Object> subPvl = cfg.getPreparedValueList();
3814+
//if (subPvl != null && subPvl.isEmpty() == false) {
3815+
// List<Object> pvl = getPreparedValueList();
3816+
//
3817+
// if (pvl != null && pvl.isEmpty() == false) {
3818+
// subPvl.addAll(pvl);
3819+
// }
3820+
// setPreparedValueList(subPvl);
3821+
//}
3822+
3823+
List<Object> subPvl = cfg.getPreparedValueList();
3824+
if (subPvl != null && subPvl.isEmpty() == false) {
3825+
List<Object> pvl = getPreparedValueList();
3826+
3827+
if (pvl == null || pvl.isEmpty()) {
3828+
pvl = subPvl;
3829+
}
3830+
else {
3831+
pvl.addAll(subPvl);
3832+
}
38143833

3815-
return sql;
3834+
setPreparedValueList(pvl);
3835+
}
3836+
3837+
return sql;
38163838
}
38173839

38183840
//key@:{} Subquery >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
@@ -3830,7 +3852,7 @@ public static String getCondition(boolean not, String condition) {
38303852
/**拼接条件
38313853
* @param not
38323854
* @param condition
3833-
* @param outerBreaket
3855+
* @param addOuterBracket
38343856
* @return
38353857
*/
38363858
public static String getCondition(boolean not, String condition, boolean addOuterBracket) {
@@ -3974,7 +3996,7 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {
39743996

39753997
//TODO procedure 改为 List<Procedure> procedureList; behind : true; function: callFunction(); String key; ...
39763998
// for (...) { Call procedure1();\n SQL \n; Call procedure2(); ... }
3977-
// 貌似不需要，因为 ObjecParser 里就已经处理的顺序等，只是这里要解决下 Schema 问题。
3999+
// 貌似不需要，因为 ObjectParser 里就已经处理的顺序等，只是这里要解决下 Schema 问题。
39784000

39794001
String sch = config.getSQLSchema();
39804002
if (StringUtil.isNotEmpty(config.getProcedure(), true)) {
@@ -4014,14 +4036,14 @@ public static String getSQL(AbstractSQLConfig config) throws Exception {
40144036
//When config's database is oracle,Using subquery since Oracle12 below does not support OFFSET FETCH paging syntax.
40154037
//针对oracle分组后条数的统计
40164038
if (StringUtil.isNotEmpty(config.getGroup(),true) && RequestMethod.isHeadMethod(config.getMethod(), true)){
4017-
return explain + "SELECT count(*) FROM (SELECT " + (config.getCache() == JSONRequest.CACHE_RAM ? "SQL_NO_CACHE " : "") + column + " FROM " + getConditionString(column, tablePath, config) + ") " + config.getLimitString();
4039+
return explain + "SELECT count(*) FROM (SELECT " + (config.getCache() == JSONRequest.CACHE_RAM ? "SQL_NO_CACHE " : "") + column + " FROM " + getConditionString(tablePath, config) + ") " + config.getLimitString();
40184040
}
40194041

4020-
String sql = "SELECT " + (config.getCache() == JSONRequest.CACHE_RAM ? "SQL_NO_CACHE " : "") + column + " FROM " + getConditionString(column, tablePath, config);
4042+
String sql = "SELECT " + (config.getCache() == JSONRequest.CACHE_RAM ? "SQL_NO_CACHE " : "") + column + " FROM " + getConditionString(tablePath, config);
40214043
return explain + config.getOraclePageSql(sql);
40224044
}
40234045

4024-
return explain + "SELECT " + (config.getCache() == JSONRequest.CACHE_RAM ? "SQL_NO_CACHE " : "") + column + " FROM " + getConditionString(column, tablePath, config) + config.getLimitString();
4046+
return explain + "SELECT " + (config.getCache() == JSONRequest.CACHE_RAM ? "SQL_NO_CACHE " : "") + column + " FROM " + getConditionString(tablePath, config) + config.getLimitString();
40254047
}
40264048
}
40274049

@@ -4044,13 +4066,15 @@ protected String getOraclePageSql(String sql) {
40444066
* @return
40454067
* @throws Exception
40464068
*/
4047-
private static String getConditionString(String column, String table, AbstractSQLConfig config) throws Exception {
4048-
String where = config.getWhereString(true);
4069+
private static String getConditionString(String table, AbstractSQLConfig config) throws Exception {
4070+
Subquery from = config.getFrom();
4071+
if (from != null) {
4072+
table = config.getSubqueryString(from) + " AS " + config.getAliasWithQuote() + " ";
4073+
}
40494074

4050-
Subquery from = config.getFrom();
4051-
if (from != null) {
4052-
table = config.getSubqueryString(from) + " AS " + config.getAliasWithQuote() + " ";
4053-
}
4075+
String join = config.getJoinString();
4076+
4077+
String where = config.getWhereString(true);
40544078

40554079
//根据方法不同，聚合语句不同。GROUP BY 和 HAVING 可以加在 HEAD 上, HAVING 可以加在 PUT, DELETE 上，GET 全加，POST 全都不加
40564080
String aggregation;
@@ -4067,7 +4091,7 @@ else if (config.getMethod() == PUT || config.getMethod() == DELETE) {
40674091
aggregation = "";
40684092
}
40694093

4070-
String condition = table + config.getJoinString() + where + aggregation;
4094+
String condition = table + join + where + aggregation;
40714095
; //+ config.getLimitString();
40724096

40734097
//no need to optimize
@@ -4123,8 +4147,8 @@ public String getJoinString() throws Exception {
41234147

41244148
if (joinList != null) {
41254149
String quote = getQuote();
4126-
List<Object> pvl = new ArrayList<>();
4127-
boolean changed = false;
4150+
List<Object> pvl = getPreparedValueList(); // new ArrayList<>();
4151+
//boolean changed = false;
41284152

41294153
// 主表不用别名 String ta;
41304154
for (Join j : joinList) {
@@ -4167,7 +4191,7 @@ public String getJoinString() throws Exception {
41674191
jc.setMain(false).setKeyPrefix(true);
41684192

41694193
pvl.addAll(jc.getPreparedValueList());
4170-
changed = true;
4194+
//changed = true;
41714195
break;
41724196

41734197
case "&": // INNER JOIN: A & B
@@ -4197,17 +4221,20 @@ public String getJoinString() throws Exception {
41974221
ow = oc.getWhereString(false);
41984222

41994223
pvl.addAll(oc.getPreparedValueList());
4200-
changed = true;
4224+
//changed = true;
42014225
}
42024226

42034227
joinOns += " \n " + sql + (StringUtil.isEmpty(ow, true) ? "" : " AND ( " + ow + " ) ");
42044228
}
42054229

42064230

4207-
if (changed) {
4208-
pvl.addAll(preparedValueList);
4209-
preparedValueList = pvl;
4210-
}
4231+
//if (changed) {
4232+
// List<Object> opvl = getPreparedValueList();
4233+
// if (opvl != null && opvl.isEmpty() == false) {
4234+
// pvl.addAll(opvl);
4235+
// }
4236+
setPreparedValueList(pvl);
4237+
//}
42114238

42124239
}
42134240

0 commit comments

Comments
 (0)