Repository navigation
Expand file tree
/
Copy pathDemoSQLExecutor.java
More file actions
141 lines (116 loc) · 4.31 KB
/
Copy pathDemoSQLExecutor.java
File metadata and controls
141 lines (116 loc) · 4.31 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
package apijson.demo.server;
import apijson.demo.server.config.ConfigYml;
import zuo.biao.apijson.Log;
import zuo.biao.apijson.server.AbstractSQLExecutor;
import zuo.biao.apijson.server.SQLConfig;
import javax.validation.constraints.NotNull;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.annotation.JsonFormat.Value;
import java.io.BufferedReader;
import java.sql.*;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**executor for query(read) or update(write) MySQL database
* @author Lemon
*/
public class DemoSQLExecutor extends AbstractSQLExecutor {
private static final String TAG = "DemoSQLExecutor";
static ConfigYml dataSourceConfig = new ConfigYml();
static Map dataSource = dataSourceConfig.read();
static {
try { //加载驱动程序
Class.forName(dataSource.get("driverclassame")+"");
Log.d(TAG, "成功加载 数据库 驱动！");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
@Override
public ResultSet executeQuery(@NotNull SQLConfig config) throws Exception {
return getStatement(config).executeQuery();
}
@Override
public int executeUpdate(@NotNull SQLConfig config) throws Exception {
return getStatement(config).executeUpdate();
}
//TODO String 改为 enum Database 解决大小写不一致(MySQL, mysql等)导致创建多余的 Connection
private Map<String, Connection> connectionMap = new HashMap<>();
/**
* @param config
* @return
* @throws Exception
*/
@SuppressWarnings("resource")
private PreparedStatement getStatement(@NotNull SQLConfig config) throws Exception {
Connection connection = connectionMap.get(config.getDatabase());
if (connection == null || connection.isClosed()) {
Log.i(TAG, "select connection " + (connection == null ? " = null" : ("isClosed = " + connection.isClosed()))) ;
//只有一个数据库
// if (DemoSQLConfig.DATABASE_POSTGRESQL.equalsIgnoreCase(config.getDatabase())) { //PostgreSQL,oracle 不允许 cross-database mysql直接连接会乱码
connection = DriverManager.getConnection(config.getDBUri(), config.getDBAccount(), config.getDBPassword());
// } else {
// connection = DriverManager.getConnection(config.getDBUri() + "?useUnicode=true&characterEncoding=UTF-8&user="
// + config.getDBAccount() + "&password=" + config.getDBPassword());
// }
connectionMap.put(config.getDatabase(), connection);
}
PreparedStatement statement = connection.prepareStatement(config.getSQL(config.isPrepared())); //创建Statement对象
List<Object> valueList = config.isPrepared() ? config.getPreparedValueList() : null;
if (valueList != null && valueList.isEmpty() == false) {
for (int i = 0; i < valueList.size(); i++) {
//只有一个数据库
// if (DemoSQLConfig.DATABASE_POSTGRESQL.equalsIgnoreCase(config.getDatabase())) {
// statement.setObject(i + 1, valueList.get(i)); //PostgreSQL JDBC 不支持隐式类型转换 tinyint = varchar 报错
// }
// else {
statement.setString(i + 1, "" + valueList.get(i)); //MySQL setObject 不支持 JSON 类型
// }
}
}
return statement;
}
@Override
protected Object getValue(SQLConfig config, ResultSet rs, ResultSetMetaData rsmd, int tablePosition,
JSONObject table, int columnIndex, Map<String, JSONObject> childMap) throws Exception {
Object value = super.getValue(config, rs, rsmd, tablePosition, table, columnIndex, childMap);
if (value instanceof Blob) {
value = new String(((Blob) value).getBytes(1, (int) ((Blob) value).length()), "UTF-8");
}
else if (value instanceof Clob) {
StringBuffer sb = new StringBuffer();
BufferedReader br = new BufferedReader(((Clob) value).getCharacterStream());
String s = br.readLine();
while (s != null) {
sb.append(s);
s = br.readLine();
}
value = sb.toString();
}
return value;
}
/**关闭连接，释放资源
*/
@Override
public void close() {
super.close();
if (connectionMap == null) {
return;
}
Collection<Connection> connections = connectionMap.values();
if (connections != null) {
for (Connection connection : connections) {
try {
if (connection != null && connection.isClosed() == false) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
connectionMap.clear();
connectionMap = null;
}
}