Skip to content

Commit efb1dd6

Browse files
TommyLemonTommyLemon
committed
Server:新增存储过程 @key():"fun(...)"； 优化 SQLExecutor 缓存的数据结构，提高性能
1 parent 7a1a4c8 commit efb1dd6

File tree

8 files changed

+209
-110
lines changed

8 files changed

+209
-110
lines changed

‎APIJSON-Java-Server/APIJSONBoot/src/main/java/apijson/demo/server/DemoSQLExecutor.java‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -68,12 +68,12 @@ public class DemoSQLExecutor extends AbstractSQLExecutor {
6868

6969
@Override
7070
public ResultSet executeQuery(@NotNull SQLConfig config) throws Exception {
71-
return getStatement(config).executeQuery();
71+
return getStatement(config).executeQuery(); //PreparedStatement 不用传 SQL
7272
}
7373

7474
@Override
7575
public int executeUpdate(@NotNull SQLConfig config) throws Exception {
76-
return getStatement(config).executeUpdate();
76+
return getStatement(config).executeUpdate(); //PreparedStatement 不用传 SQL
7777
}
7878

7979

@@ -85,7 +85,8 @@ public int executeUpdate(@NotNull SQLConfig config) throws Exception {
8585
* @throws Exception
8686
*/
8787
@SuppressWarnings("resource")
88-
private PreparedStatement getStatement(@NotNull SQLConfig config) throws Exception {
88+
@Override
89+
public PreparedStatement getStatement(@NotNull SQLConfig config) throws Exception {
8990
Connection connection = connectionMap.get(config.getDatabase());
9091
if (connection == null || connection.isClosed()) {
9192
Log.i(TAG, "select connection " + (connection == null ? " = null" : ("isClosed = " + connection.isClosed()))) ;

‎APIJSON-Java-Server/APIJSONORM/src/main/java/zuo/biao/apijson/server/AbstractObjectParser.java‎

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -717,7 +717,25 @@ public void onFunctionResponse(String type) throws Exception {
717717
}
718718

719719
public void parseFunction(JSONObject json, String key, String value) throws Exception {
720-
Object result = parser.onFunctionParse(json, value);
720+
Object result;
721+
if (key.startsWith("@")) {
722+
SQLConfig config = newSQLConfig();
723+
config.setProcedure(value);
724+
725+
SQLExecutor executor = null;
726+
try {
727+
executor = parser.createSQLExecutor();
728+
result = executor.execute(config, true);
729+
}
730+
finally {
731+
if (executor != null) {
732+
executor.close();
733+
}
734+
}
735+
}
736+
else {
737+
result = parser.onFunctionParse(json, value);
738+
}
721739

722740
if (result != null) {
723741
String k = AbstractSQLConfig.getRealKey(method, key, false, false, "`"); //FIXME PG 是 "

‎APIJSON-Java-Server/APIJSONORM/src/main/java/zuo/biao/apijson/server/AbstractParser.java‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -609,7 +609,7 @@ public JSONObject getStructure(@NotNull String table, String key, String value,
609609

610610
//too many connections error: 不try-catch，可以让客户端看到是服务器内部异常
611611
try {
612-
JSONObject result = executor.execute(config.setCacheStatic(true));
612+
JSONObject result = executor.execute(config.setCacheStatic(true), false);
613613
return getJSONObject(result, "structure");//解决返回值套了一层 "structure":{}
614614
} finally {
615615
executor.close();
@@ -1289,7 +1289,7 @@ public synchronized JSONObject executeSQL(@NotNull SQLConfig config, boolean isS
12891289
}
12901290

12911291
try {
1292-
return parseCorrectResponse(config.getTable(), sqlExecutor.execute(config));
1292+
return parseCorrectResponse(config.getTable(), sqlExecutor.execute(config, false));
12931293
} catch (Exception e) {
12941294
if (Log.DEBUG == false && e instanceof SQLException) {
12951295
throw new SQLException("数据库驱动执行异常SQLException，非 Log.DEBUG 模式下不显示详情，避免泄漏真实模式名、表名等隐私信息", e);

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

Lines changed: 41 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -128,6 +128,15 @@ public String getUserIdKey() {
128128
//array item >>>>>>>>>>
129129
private boolean test; //测试
130130
private boolean cacheStatic; //静态缓存
131+
132+
private String procedure;
133+
public SQLConfig setProcedure(String procedure) {
134+
this.procedure = procedure;
135+
return this;
136+
}
137+
public String getProcedure() {
138+
return procedure;
139+
}
131140

132141
public AbstractSQLConfig(RequestMethod method) {
133142
setMethod(method);
@@ -218,6 +227,21 @@ public String getQuote() {
218227
public String getSchema() {
219228
return schema;
220229
}
230+
public String getSQLSchema(String sqlTable) {
231+
String sch = getSchema();
232+
if (sch == null) { //PostgreSQL 的 pg_class 和 pg_attribute 表好像不属于任何 Schema StringUtil.isEmpty(sch, true)) {
233+
if ((Table.TABLE_NAME.equals(sqlTable) || Column.TABLE_NAME.equals(sqlTable)) ) {
234+
sch = SCHEMA_INFORMATION;
235+
}
236+
else if ((PgAttribute.TABLE_NAME.equals(sqlTable) || PgClass.TABLE_NAME.equals(sqlTable)) ) {
237+
sch = "";
238+
}
239+
else {
240+
sch = DEFAULT_SCHEMA;
241+
}
242+
}
243+
return sch;
244+
}
221245
@Override
222246
public AbstractSQLConfig setSchema(String schema) {
223247
if (schema != null) {
@@ -255,18 +279,7 @@ public String getTablePath() {
255279
String q = getQuote();
256280

257281
String sqlTable = getSQLTable();
258-
String sch = getSchema();
259-
if (sch == null) { //PostgreSQL 的 pg_class 和 pg_attribute 表好像不属于任何 Schema StringUtil.isEmpty(sch, true)) {
260-
if ((Table.TABLE_NAME.equals(sqlTable) || Column.TABLE_NAME.equals(sqlTable)) ) {
261-
sch = SCHEMA_INFORMATION;
262-
}
263-
else if ((PgAttribute.TABLE_NAME.equals(sqlTable) || PgClass.TABLE_NAME.equals(sqlTable)) ) {
264-
sch = "";
265-
}
266-
else {
267-
sch = DEFAULT_SCHEMA;
268-
}
269-
}
282+
String sch = getSQLSchema(sqlTable);
270283

271284
return (StringUtil.isEmpty(sch, true) ? "" : q + sch + q + ".") + q + sqlTable + q + ( isKeyPrefix() ? " AS " + getAlias() : "");
272285
}
@@ -1868,7 +1881,22 @@ public String getSQL(boolean prepared) throws Exception {
18681881
* @throws Exception
18691882
*/
18701883
public static String getSQL(AbstractSQLConfig config) throws Exception {
1871-
String tablePath = config == null ? null : config.getTablePath();
1884+
if (config == null) {
1885+
Log.i(TAG, "getSQL config == null >> return null;");
1886+
return null;
1887+
}
1888+
1889+
//TODO procedure 改为 List<Procedure> procedureList; behind : true; function: callFunction(); String key; ...
1890+
// for (...) { Call procedure1();\n SQL \n; Call procedure2(); ... }
1891+
// 貌似不需要，因为 ObjecParser 里就已经处理的顺序等，只是这里要解决下 Schema 问题。
1892+
1893+
String sch = config.getSQLSchema(config.getSQLTable());
1894+
if (StringUtil.isNotEmpty(config.getProcedure(), true)) {
1895+
String q = config.getQuote();
1896+
return "CALL " + q + sch + q + "."+ config.getProcedure();
1897+
}
1898+
1899+
String tablePath = config.getTablePath();
18721900
if (StringUtil.isNotEmpty(tablePath, true) == false) {
18731901
Log.i(TAG, "getSQL StringUtil.isNotEmpty(tablePath, true) == false >> return null;");
18741902
return null;

0 commit comments

Comments
 (0)