Visitar URL original
Server: APIJSONORM 新增 @json 自动把字段值转为 JSON 格式；APIJSONORM 解决 SQL Server… · zerolugithub/APIJSON@737a615 · GitHub
Skip to content

Commit 737a615

Browse files
TommyLemonTommyLemon
committed
Server: APIJSONORM 新增 @JSON 自动把字段值转为 JSON 格式；APIJSONORM 解决 SQL Server 字段不能输出 JSON 格式；APIJSONORM 放开 DEBUG 模式下对系统表的 SQL 执行数量限制；APIJSONORM 解决非 DEBUG 模式下不能访问 Access 和 Request 表导致部分功能不可用；APIJSONBoot 尝试升级 SpringBoot 至 2.1.9 失败
1 parent 01b9439 commit 737a615

11 files changed

Lines changed: 124 additions & 48 deletions

File tree

‎APIJSON-Java-Server/APIJSONBoot/pom.xml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@
1414
<parent>
1515
<groupId>org.springframework.boot</groupId>
1616
<artifactId>spring-boot-starter-parent</artifactId>
17-
<version>1.4.1.RELEASE</version>
17+
<version>1.4.1.RELEASE</version> <!--改成 2.1.9 直接报错，找不到 mysql-connector-java 等 3 个驱动包 <version>2.1.9.RELEASE</version> -->
1818
<relativePath /> <!-- lookup parent from repository -->
1919
</parent>
2020

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

Lines changed: 16 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -39,9 +39,9 @@ public static void main(String[] args) throws Exception {
3939
SpringApplication.run(APIJSONApplication.class, args);
4040

4141
Log.DEBUG = true; //上线生产环境前改为 false，可不输出 APIJSONORM 的日志 以及 SQLException 的原始(敏感)信息
42-
42+
4343
System.out.println("\n\n\n\n\n<<<<<<<<<<<<<<<<<<<<<<<<< APIJSON 开始启动 >>>>>>>>>>>>>>>>>>>>>>>>\n");
44-
44+
4545
System.out.println("\n\n\n开始初始化:远程函数配置 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n");
4646
try {
4747
DemoFunction.init(true);
@@ -50,7 +50,7 @@ public static void main(String[] args) throws Exception {
5050
e.printStackTrace();
5151
}
5252
System.out.println("\n完成初始化:远程函数配置 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
53-
53+
5454
System.out.println("开始测试:远程函数 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n");
5555
try {
5656
DemoFunction.test();
@@ -60,8 +60,8 @@ public static void main(String[] args) throws Exception {
6060
}
6161
System.out.println("\n完成测试:远程函数 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
6262

63-
64-
63+
64+
6565
System.out.println("\n\n\n开始初始化:请求校验配置 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n");
6666
try {
6767
StructureUtil.init(true);
@@ -79,9 +79,9 @@ public static void main(String[] args) throws Exception {
7979
e.printStackTrace();
8080
}
8181
System.out.println("\n完成测试:请求校验 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
82-
83-
84-
82+
83+
84+
8585
System.out.println("\n\n\n开始初始化:权限校验配置 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n");
8686
try {
8787
DemoVerifier.init(true);
@@ -91,11 +91,16 @@ public static void main(String[] args) throws Exception {
9191
}
9292
System.out.println("\n完成初始化:权限校验配置 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
9393

94-
94+
9595
System.out.println("\n\n<<<<<<<<<<<<<<<<<<<<<<<<< APIJSON 启动完成，试试调用自动化 API 吧 ^_^ >>>>>>>>>>>>>>>>>>>>>>>>\n");
9696
}
9797

98-
98+
//SpringBoot 2.x 自定义端口方式
99+
// @Bean
100+
// public TomcatServletWebServerFactory servletContainer(){
101+
// return new TomcatServletWebServerFactory(8081) ;
102+
// }
103+
//SpringBoot 1.x 自定义端口方式，配置文件加 server.port=80 无效(MacOS 10.10.?)
99104
@Bean
100105
public EmbeddedServletContainerCustomizer containerCustomizer() {
101106
return new EmbeddedServletContainerCustomizer() {
@@ -107,6 +112,7 @@ public void customize(ConfigurableEmbeddedServletContainer container) {
107112
};
108113
}
109114

115+
110116
//支持JavaScript跨域请求<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
111117
/**
112118
* 跨域过滤器

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

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -38,8 +38,8 @@ public class DemoSQLConfig extends AbstractSQLConfig {
3838

3939

4040
static {
41-
DEFAULT_DATABASE = DATABASE_MYSQL; //TODO 默认数据库类型，改成你自己的
42-
DEFAULT_SCHEMA = "sys"; //TODO 默认模式名，改成你自己的，默认情况是 MySQL: sys, PostgreSQL: public, SQL Server: dbo, Oracle:
41+
DEFAULT_DATABASE = DATABASE_SQLSERVER; //TODO 默认数据库类型，改成你自己的
42+
DEFAULT_SCHEMA = "dbo"; //TODO 默认模式名，改成你自己的，默认情况是 MySQL: sys, PostgreSQL: public, SQL Server: dbo, Oracle:
4343

4444
// 由 DemoVerifier.init 方法读取数据库 Access 表来替代手动输入配置
4545
// //表名映射，隐藏真实表名，对安全要求很高的表可以这么做
@@ -89,13 +89,13 @@ public String getDBVersion() {
8989
@Override
9090
public String getDBUri() {
9191
if (isMySQL()) {
92-
return "jdbc:mysql://localhost:3306"; //TODO 改成你自己的，TiDB 可以当成 MySQL 使用，默认端口为 4000
92+
return "jdbc:mysql://apijson.cn:3306"; //TODO 改成你自己的，TiDB 可以当成 MySQL 使用，默认端口为 4000
9393
}
9494
if (isPostgreSQL()) {
9595
return "jdbc:postgresql://localhost:5432/postgres"; //TODO 改成你自己的
9696
}
9797
if (isSQLServer()) {
98-
return "jdbc:jtds:sqlserver://localhost:1433/pubs;instance=SQLEXPRESS"; //TODO 改成你自己的
98+
return "jdbc:jtds:sqlserver://apijson.org:1433/pubs;instance=SQLEXPRESS"; //TODO 改成你自己的
9999
}
100100
if (isOracle()) {
101101
return "jdbc:oracle:thin:@localhost:1521:orcl"; //TODO 改成你自己的

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -110,9 +110,9 @@ public PreparedStatement setArgument(@NotNull SQLConfig config, @NotNull Prepare
110110

111111
@Override
112112
protected Object getValue(SQLConfig config, ResultSet rs, ResultSetMetaData rsmd, int tablePosition,
113-
JSONObject table, int columnIndex, Map<String, JSONObject> childMap) throws Exception {
113+
JSONObject table, int columnIndex, String lable, Map<String, JSONObject> childMap) throws Exception {
114114

115-
Object value = super.getValue(config, rs, rsmd, tablePosition, table, columnIndex, childMap);
115+
Object value = super.getValue(config, rs, rsmd, tablePosition, table, columnIndex, lable, childMap);
116116

117117
return value instanceof PGobject ? JSON.parse(((PGobject) value).getValue()) : value;
118118
}

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -148,6 +148,7 @@ public JSONObject setUserIdIn(List<Object> list) {
148148
public static final String KEY_GROUP = "@group"; //分组方式
149149
public static final String KEY_HAVING = "@having"; //聚合函数条件，一般和@group一起用
150150
public static final String KEY_ORDER = "@order"; //排序方式
151+
public static final String KEY_JSON = "@json"; //SQL Server 把字段转为 JSON 输出
151152

152153
public static final List<String> TABLE_KEY_LIST;
153154
static {
@@ -163,6 +164,7 @@ public JSONObject setUserIdIn(List<Object> list) {
163164
TABLE_KEY_LIST.add(KEY_GROUP);
164165
TABLE_KEY_LIST.add(KEY_HAVING);
165166
TABLE_KEY_LIST.add(KEY_ORDER);
167+
TABLE_KEY_LIST.add(KEY_JSON);
166168
}
167169

168170
//@key关键字都放这个类 >>>>>>>>>>>>>>>>>>>>>>

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1395,7 +1395,7 @@ public JSONObject executeSQL(SQLConfig config, boolean isSubquery) throws Except
13951395
throw e;
13961396
}
13971397
finally {
1398-
if (config.getPosition() == 0) {
1398+
if (config.getPosition() == 0 && config.limitSQLCount()) {
13991399
int maxSQLCount = getMaxSQLCount();
14001400
int sqlCount = sqlExecutor.getExecutedSQLCount();
14011401
Log.d(TAG, "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< \n\n\n 已执行 " + sqlCount + "/" + maxSQLCount + " 条 SQL \n\n\n >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222
import static zuo.biao.apijson.JSONObject.KEY_FROM;
2323
import static zuo.biao.apijson.JSONObject.KEY_GROUP;
2424
import static zuo.biao.apijson.JSONObject.KEY_HAVING;
25+
import static zuo.biao.apijson.JSONObject.KEY_JSON;
2526
import static zuo.biao.apijson.JSONObject.KEY_ID;
2627
import static zuo.biao.apijson.JSONObject.KEY_ORDER;
2728
import static zuo.biao.apijson.JSONObject.KEY_ROLE;
@@ -102,6 +103,10 @@ public abstract class AbstractSQLConfig implements SQLConfig {
102103
DATABASE_LIST.add(DATABASE_ORACLE);
103104
}
104105

106+
@Override
107+
public boolean limitSQLCount() {
108+
return Log.DEBUG == false || AbstractVerifier.SYSTEM_ACCESS_MAP.containsKey(getTable()) == false;
109+
}
105110

106111
@NotNull
107112
@Override
@@ -131,6 +136,7 @@ public String getUserIdKey() {
131136
private String group; //分组方式的字符串数组，','分隔
132137
private String having; //聚合函数的字符串数组，','分隔
133138
private String order; //排序方式的字符串数组，','分隔
139+
private List<String> json; //需要转为 JSON 的字段，','分隔
134140
private Subquery from; //子查询临时表
135141
private List<String> column; //表内字段名(或函数名，仅查询操作可用)的字符串数组，','分隔
136142
private List<List<Object>> values; //对应表内字段的值的字符串数组，','分隔
@@ -670,6 +676,17 @@ public String getOrderString(boolean hasPrefix) {
670676
}
671677

672678

679+
@Override
680+
public List<String> getJson() {
681+
return json;
682+
}
683+
@Override
684+
public AbstractSQLConfig setJson(List<String> json) {
685+
this.json = json;
686+
return this;
687+
}
688+
689+
673690
@Override
674691
public Subquery getFrom() {
675692
return from;
@@ -2375,6 +2392,7 @@ else if (id instanceof Subquery) {}
23752392
String group = request.getString(KEY_GROUP);
23762393
String having = request.getString(KEY_HAVING);
23772394
String order = request.getString(KEY_ORDER);
2395+
String json = request.getString(KEY_JSON);
23782396

23792397
//强制作为条件且放在最前面优化性能
23802398
request.remove(idKey);
@@ -2391,6 +2409,7 @@ else if (id instanceof Subquery) {}
23912409
request.remove(KEY_GROUP);
23922410
request.remove(KEY_HAVING);
23932411
request.remove(KEY_ORDER);
2412+
request.remove(KEY_JSON);
23942413

23952414

23962415
Map<String, Object> tableWhere = new LinkedHashMap<String, Object>();//保证顺序好优化 WHERE id > 1 AND name LIKE...
@@ -2579,6 +2598,9 @@ else if (whereList != null && whereList.contains(key)) {
25792598
config.setGroup(group);
25802599
config.setHaving(having);
25812600
config.setOrder(order);
2601+
2602+
String[] jsonArr = StringUtil.split(json);
2603+
config.setJson(jsonArr == null || jsonArr.length <= 0 ? null : new ArrayList<>(Arrays.asList(jsonArr)));
25822604

25832605
//TODO 解析JOIN，包括 @column，@group 等要合并
25842606

@@ -2598,6 +2620,7 @@ else if (whereList != null && whereList.contains(key)) {
25982620
request.put(KEY_GROUP, group);
25992621
request.put(KEY_HAVING, having);
26002622
request.put(KEY_ORDER, order);
2623+
request.put(KEY_JSON, json);
26012624

26022625
return config;
26032626
}

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

Lines changed: 48 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -18,13 +18,15 @@
1818
import java.sql.Blob;
1919
import java.sql.Clob;
2020
import java.sql.Connection;
21+
import java.sql.Date;
2122
import java.sql.DriverManager;
2223
import java.sql.PreparedStatement;
2324
import java.sql.ResultSet;
2425
import java.sql.ResultSetMetaData;
2526
import java.sql.SQLException;
2627
import java.sql.Savepoint;
2728
import java.sql.Statement;
29+
import java.sql.Time;
2830
import java.sql.Timestamp;
2931
import java.util.ArrayList;
3032
import java.util.Collection;
@@ -198,7 +200,7 @@ public JSONObject execute(@NotNull SQLConfig config, boolean unknowType) throws
198200
case HEAD:
199201
case HEADS:
200202
rs = executeQuery(config);
201-
203+
202204
executedSQLCount ++;
203205

204206
result = rs.next() ? AbstractParser.newSuccessResult()
@@ -239,7 +241,7 @@ public JSONObject execute(@NotNull SQLConfig config, boolean unknowType) throws
239241
}
240242

241243
rs = executeQuery(config);
242-
244+
243245
if (config.isExplain() == false) { //只有 SELECT 才能 EXPLAIN
244246
executedSQLCount ++;
245247
}
@@ -515,7 +517,7 @@ protected JSONObject onPutColumn(@NotNull SQLConfig config, @NotNull ResultSet r
515517
}
516518
}
517519

518-
finalTable.put(lable, getValue(config, rs, rsmd, tablePosition, table, columnIndex, childMap));
520+
finalTable.put(lable, getValue(config, rs, rsmd, tablePosition, table, columnIndex, lable, childMap));
519521

520522
return table;
521523
}
@@ -538,7 +540,7 @@ protected List<JSONObject> onPutTable(@NotNull SQLConfig config, @NotNull Result
538540

539541

540542
protected Object getValue(@NotNull SQLConfig config, @NotNull ResultSet rs, @NotNull ResultSetMetaData rsmd
541-
, final int tablePosition, @NotNull JSONObject table, final int columnIndex, Map<String, JSONObject> childMap) throws Exception {
543+
, final int tablePosition, @NotNull JSONObject table, final int columnIndex, String lable, Map<String, JSONObject> childMap) throws Exception {
542544

543545
Object value = rs.getObject(columnIndex);
544546
// Log.d(TAG, "name:" + rsmd.getColumnName(i));
@@ -549,18 +551,31 @@ protected Object getValue(@NotNull SQLConfig config, @NotNull ResultSet rs, @Not
549551
// Log.i(TAG, "select while (rs.next()) { >> for (int i = 0; i < length; i++) {"
550552
// + "\n >>> value = " + value);
551553

554+
boolean castToJson = false;
555+
552556
//数据库查出来的null和empty值都有意义，去掉会导致 Moment:{ @column:"content" } 部分无结果及中断数组查询！
553-
if (value instanceof Timestamp) {
557+
if (value instanceof Boolean || value instanceof Number) {
558+
//加快判断速度
559+
}
560+
else if (value instanceof Timestamp) {
554561
value = ((Timestamp) value).toString();
555562
}
556-
else if (value instanceof String && isJSONType(rsmd, columnIndex)) { //json String
557-
value = JSON.parse((String) value);
563+
else if (value instanceof Date) {
564+
value = ((Date) value).toString();
565+
}
566+
else if (value instanceof Time) {
567+
value = ((Time) value).toString();
568+
}
569+
else if (value instanceof String && isJSONType(config, rsmd, columnIndex, lable)) { //json String
570+
castToJson = true;
558571
}
559572
else if (value instanceof Blob) { //FIXME 存的是 abcde，取出来直接就是 [97, 98, 99, 100, 101] 这种 byte[] 类型，没有经过以下处理，但最终序列化后又变成了字符串 YWJjZGU=
573+
castToJson = true;
560574
value = new String(((Blob) value).getBytes(1, (int) ((Blob) value).length()), "UTF-8");
561575
}
562-
else if (value instanceof Clob) {
563-
576+
else if (value instanceof Clob) { //SQL Server TEXT 类型 居然走这个
577+
castToJson = true;
578+
564579
StringBuffer sb = new StringBuffer();
565580
BufferedReader br = new BufferedReader(((Clob) value).getCharacterStream());
566581
String s = br.readLine();
@@ -571,24 +586,44 @@ else if (value instanceof Clob) {
571586
value = sb.toString();
572587
}
573588

589+
if (castToJson == false) {
590+
List<String> json = config.getJson();
591+
castToJson = json != null && json.contains(lable);
592+
}
593+
if (castToJson) {
594+
try {
595+
value = JSON.parse((String) value);
596+
} catch (Exception e) {
597+
Log.e(TAG, "getValue try { value = JSON.parse((String) value); } catch (Exception e) { \n" + e.getMessage());
598+
}
599+
}
600+
574601
return value;
575602
}
576603

577604

578605
/**判断是否为JSON类型
606+
* @param config
607+
* @param lable
579608
* @param rsmd
580609
* @param position
581610
* @return
582611
*/
583612
@Override
584-
public boolean isJSONType(ResultSetMetaData rsmd, int position) {
613+
public boolean isJSONType(@NotNull SQLConfig config, ResultSetMetaData rsmd, int position, String lable) {
585614
try {
615+
String column = rsmd.getColumnTypeName(position);
586616
//TODO CHAR和JSON类型的字段，getColumnType返回值都是1 ，如果不用CHAR，改用VARCHAR，则可以用上面这行来提高性能。
587617
//return rsmd.getColumnType(position) == 1;
588-
return rsmd.getColumnTypeName(position).toLowerCase().contains("json");
618+
619+
if (column.toLowerCase().contains("json")) {
620+
return true;
621+
}
589622
} catch (SQLException e) {
590623
e.printStackTrace();
591624
}
625+
// List<String> json = config.getJson();
626+
// return json != null && json.contains(lable);
592627
return false;
593628
}
594629

@@ -645,7 +680,7 @@ public Connection getConnection(@NotNull SQLConfig config) throws Exception {
645680
v = 1;
646681
Log.e(TAG, "getStatement try { String[] vs = config.getDBVersion().split([.]); ... >> } catch (Exception e) {\n" + e.getMessage());
647682
}
648-
683+
649684
if (v >= 8) {
650685
connection = DriverManager.getConnection(config.getDBUri() + "?userSSL=false&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8&user="
651686
+ config.getDBAccount() + "&password=" + config.getDBPassword());
@@ -658,7 +693,7 @@ public Connection getConnection(@NotNull SQLConfig config) throws Exception {
658693
else { //PostgreSQL 不允许 cross-database
659694
connection = DriverManager.getConnection(config.getDBUri(), config.getDBAccount(), config.getDBPassword());
660695
}
661-
696+
662697
connectionMap.put(config.getDatabase(), connection);
663698
}
664699

0 commit comments

Comments
 (0)