Skip to content

Commit 17e3502

Browse files
TommyLemonTommyLemon
committed
Server:新增支持PostgreSQL数据库
1 parent 374ef92 commit 17e3502

File tree

6 files changed

+71
-10
lines changed

6 files changed

+71
-10
lines changed

‎APIJSON-Java-Server/APIJSONDemo/libs/postgresql-42.2.4.jar‎

796 KB
Binary file not shown.

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

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@
1717
import java.util.List;
1818

1919
import com.alibaba.fastjson.JSONObject;
20+
import com.alibaba.fastjson.annotation.JSONField;
2021

2122
import apijson.demo.server.model.Privacy;
2223
import apijson.demo.server.model.User;
@@ -32,6 +33,9 @@
3233
*/
3334
public class DemoSQLConfig extends AbstractSQLConfig {
3435

36+
public static final String DATABASE_MYSQL = "MySQL";
37+
public static final String DATABASE_POSTGRESQL = "PostgreSQL";
38+
3539
//表名映射，隐藏真实表名，对安全要求很高的表可以这么做
3640
static {
3741
TABLE_KEY_MAP.put(User.class.getSimpleName(), "apijson_user");
@@ -40,22 +44,29 @@ public class DemoSQLConfig extends AbstractSQLConfig {
4044

4145
@Override
4246
public String getDBUri() {
43-
return "jdbc:mysql://localhost:3306"; //TODO 改成你自己的
47+
//TODO 改成你自己的
48+
return DATABASE_POSTGRESQL.equals(getDatabase()) ? "jdbc:postgresql://localhost:5432" : "jdbc:mysql://localhost:3306";
4449
}
4550
@Override
4651
public String getDBAccount() {
47-
return "root"; //TODO 改成你自己的
52+
return DATABASE_POSTGRESQL.equals(getDatabase()) ? "postgres" : "root"; //TODO 改成你自己的
4853
}
4954
@Override
5055
public String getDBPassword() {
51-
return "apijson"; //TODO 改成你自己的
56+
return DATABASE_POSTGRESQL.equals(getDatabase()) ? null : "apijson"; //TODO 改成你自己的
5257
}
5358
@Override
5459
public String getSchema() {
5560
String s = super.getSchema();
5661
return StringUtil.isEmpty(s, true) ? "sys" : s; //TODO 改成你自己的
5762
}
5863

64+
@JSONField(serialize = false)
65+
@Override
66+
public String getTablePath() {
67+
return DATABASE_POSTGRESQL.equals(getDatabase()) ? getSQLTable() : super.getTablePath();
68+
}
69+
5970

6071
public DemoSQLConfig() {
6172
this(RequestMethod.GET);

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

Lines changed: 26 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -36,9 +36,16 @@ public class DemoSQLExecutor extends AbstractSQLExecutor {
3636

3737

3838
static {
39-
try {//调用Class.forName()方法加载驱动程序
39+
try { //加载驱动程序
4040
Class.forName("com.mysql.jdbc.Driver");
41-
Log.d(TAG, "成功加载MySQL驱动！");
41+
Log.d(TAG, "成功加载 MySQL 驱动！");
42+
} catch (ClassNotFoundException e) {
43+
e.printStackTrace();
44+
}
45+
46+
try { //加载驱动程序
47+
Class.forName("org.postgresql.Driver");
48+
Log.d(TAG, "成功加载 PostgresSQL 驱动！");
4249
} catch (ClassNotFoundException e) {
4350
e.printStackTrace();
4451
}
@@ -69,15 +76,29 @@ private PreparedStatement getStatement(@NotNull SQLConfig config) throws Excepti
6976
if (connection == null || connection.isClosed()) {
7077
Log.i(TAG, "select connection " + (connection == null ? " = null" : ("isClosed = " + connection.isClosed()))) ;
7178

72-
connection = DriverManager.getConnection(config.getDBUri() + "?useUnicode=true&characterEncoding=UTF-8&user="
73-
+ config.getDBAccount() + "&password=" + config.getDBPassword());
79+
80+
if (DemoSQLConfig.DATABASE_POSTGRESQL.equals(config.getDatabase())) {
81+
connection = DriverManager.getConnection(config.getDBUri() + "/" + config.getSchema(), config.getDBAccount(), config.getDBPassword());
82+
}
83+
else {
84+
connection = DriverManager.getConnection(config.getDBUri() + "?useUnicode=true&characterEncoding=UTF-8&user="
85+
+ config.getDBAccount() + "&password=" + config.getDBPassword());
86+
}
7487
}
7588

7689
statement = connection.prepareStatement(config.getSQL(config.isPrepared())); //创建Statement对象
7790
List<Object> valueList = config.isPrepared() ? config.getPreparedValueList() : null;
91+
7892
if (valueList != null && valueList.isEmpty() == false) {
93+
7994
for (int i = 0; i < valueList.size(); i++) {
80-
statement.setString(i + 1, "" + valueList.get(i));
95+
96+
if (DemoSQLConfig.DATABASE_POSTGRESQL.equals(config.getDatabase())) {
97+
statement.setObject(i + 1, valueList.get(i)); //PostgreSQL JDBC 不支持隐式类型转换 tinyint = varchar 报错
98+
}
99+
else {
100+
statement.setString(i + 1, "" + valueList.get(i)); //MySQL setObject 不支持 JSON 类型
101+
}
81102
}
82103
}
83104

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -126,6 +126,7 @@ public JSONObject setUserIdIn(List<Object> list) {
126126
public static final String KEY_DROP = "@drop"; //丢弃，不返回
127127
public static final String KEY_CORRECT = "@correct"; //字段校正
128128

129+
public static final String KEY_DATABASE = "@database"; //数据库类型，默认为MySQL
129130
public static final String KEY_SCHEMA = "@schema"; //数据库，Table在非默认schema内时需要声明
130131
public static final String KEY_COLUMN = "@column"; //查询的Table字段或SQL函数
131132
public static final String KEY_COMBINE = "@combine"; //条件组合，每个条件key前面可以放&,|,!逻辑关系 "id!{},&sex,!name&$"
@@ -137,6 +138,7 @@ public JSONObject setUserIdIn(List<Object> list) {
137138
static {
138139
TABLE_KEY_LIST = new ArrayList<String>();
139140
TABLE_KEY_LIST.add(KEY_ROLE);
141+
TABLE_KEY_LIST.add(KEY_DATABASE);
140142
TABLE_KEY_LIST.add(KEY_SCHEMA);
141143
TABLE_KEY_LIST.add(KEY_COLUMN);
142144
TABLE_KEY_LIST.add(KEY_COMBINE);
@@ -182,6 +184,14 @@ public JSONObject setCorrect(Map<String, String> correct) {
182184

183185

184186

187+
/**set database where table was puts
188+
* @param database
189+
* @return this
190+
*/
191+
public JSONObject setDatabase(String database) {
192+
return puts(KEY_DATABASE, database);
193+
194+
}
185195
/**set schema where table was puts
186196
* @param schema
187197
* @return this

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

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@
1616

1717
import static zuo.biao.apijson.JSONObject.KEY_COLUMN;
1818
import static zuo.biao.apijson.JSONObject.KEY_COMBINE;
19+
import static zuo.biao.apijson.JSONObject.KEY_DATABASE;
1920
import static zuo.biao.apijson.JSONObject.KEY_GROUP;
2021
import static zuo.biao.apijson.JSONObject.KEY_HAVING;
2122
import static zuo.biao.apijson.JSONObject.KEY_ID;
@@ -86,7 +87,8 @@ public abstract class AbstractSQLConfig implements SQLConfig {
8687
* TODO 被关联的表通过就忽略关联的表？(这个不行 User:{"sex@":"/Comment/toId"})
8788
*/
8889
private RequestRole role; //发送请求的用户的角色
89-
private String schema; //表所在的数据库
90+
private String database; //表所在的数据库类型
91+
private String schema; //表所在的数据库名
9092
private String table; //表名
9193
private String alias; //表别名
9294
private String group; //分组方式的字符串数组，','分隔
@@ -180,6 +182,16 @@ public AbstractSQLConfig setRole(RequestRole role) {
180182
return this;
181183
}
182184

185+
@Override
186+
public String getDatabase() {
187+
return database;
188+
}
189+
@Override
190+
public SQLConfig setDatabase(String database) {
191+
this.database = database;
192+
return this;
193+
}
194+
183195
@Override
184196
public String getSchema() {
185197
String sqlTable = getSQLTable();
@@ -761,7 +773,7 @@ public String getLimitString() {
761773
* @return
762774
*/
763775
public static String getLimitString(int page, int count) {
764-
return count <= 0 ? "" : " LIMIT " + getOffset(page, count) + ", " + count;
776+
return count <= 0 ? "" : " LIMIT " + count + " OFFSET " + getOffset(page, count);
765777
}
766778

767779
//WHERE <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
@@ -1637,6 +1649,7 @@ public static AbstractSQLConfig newSQLConfig(RequestMethod method, String table,
16371649

16381650

16391651
String role = request.getString(KEY_ROLE);
1652+
String database = request.getString(KEY_DATABASE);
16401653
String schema = request.getString(KEY_SCHEMA);
16411654
String combine = request.getString(KEY_COMBINE);
16421655
String column = request.getString(KEY_COLUMN);
@@ -1649,6 +1662,7 @@ public static AbstractSQLConfig newSQLConfig(RequestMethod method, String table,
16491662
request.remove(KEY_ID_IN);
16501663
//关键词
16511664
request.remove(KEY_ROLE);
1665+
request.remove(KEY_DATABASE);
16521666
request.remove(KEY_SCHEMA);
16531667
request.remove(KEY_COMBINE);
16541668
request.remove(KEY_COLUMN);
@@ -1817,6 +1831,7 @@ else if (whereList != null && whereList.contains(key)) {
18171831
//在 tableWhere 第0个 config.setIdIn(idIn);
18181832

18191833
config.setRole(role);
1834+
config.setDatabase(database);
18201835
config.setSchema(schema);
18211836
config.setColumn(column);
18221837
config.setGroup(group);
@@ -1834,6 +1849,7 @@ else if (whereList != null && whereList.contains(key)) {
18341849
request.put(KEY_ID_IN, idIn);
18351850
//关键词
18361851
request.put(KEY_ROLE, role);
1852+
request.put(KEY_DATABASE, database);
18371853
request.put(KEY_SCHEMA, schema);
18381854
request.put(KEY_COMBINE, combine);
18391855
request.put(KEY_COLUMN, column);

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,9 @@ public interface SQLConfig {
9090
RequestRole getRole();
9191
SQLConfig setRole(RequestRole role);
9292

93+
String getDatabase();
94+
SQLConfig setDatabase(String database);
95+
9396
String getSchema();
9497
SQLConfig setSchema(String schema);
9598

0 commit comments

Comments
 (0)