Skip to content

Commit cd0a1a0

Browse files
author
chengluo
committed
fix: add missing files
1 parent 617036c commit cd0a1a0

File tree

2 files changed

+392
-0
lines changed

2 files changed

+392
-0
lines changed

‎server/api-service/openblocks-plugins/sqlBasedPlugin/src/main/java/com/openblocks/plugin/sql/HikariBasedQueryExecutor.java

Lines changed: 150 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,150 @@
1+
package com.openblocks.plugin.sql;
2+
3+
import static com.openblocks.sdk.exception.PluginCommonError.CONNECTION_ERROR;
4+
import static com.openblocks.sdk.exception.PluginCommonError.QUERY_ARGUMENT_ERROR;
5+
import static com.openblocks.sdk.exception.PluginCommonError.QUERY_EXECUTION_ERROR;
6+
import static com.openblocks.sdk.util.ExceptionUtils.wrapException;
7+
8+
import java.sql.Connection;
9+
import java.sql.SQLException;
10+
import java.util.Map;
11+
import java.util.Set;
12+
13+
import javax.annotation.Nonnull;
14+
15+
import org.apache.commons.collections4.MapUtils;
16+
import org.apache.commons.lang3.StringUtils;
17+
18+
import com.google.common.collect.Maps;
19+
import com.openblocks.sdk.exception.InvalidHikariDatasourceException;
20+
import com.openblocks.sdk.exception.PluginException;
21+
import com.openblocks.sdk.models.DatasourceStructure;
22+
import com.openblocks.sdk.models.QueryExecutionResult;
23+
import com.openblocks.sdk.plugin.common.BlockingQueryExecutor;
24+
import com.openblocks.sdk.plugin.common.SqlQueryUtils;
25+
import com.openblocks.sdk.plugin.common.sql.HikariPerfWrapper;
26+
import com.openblocks.sdk.plugin.common.sql.SqlBasedDatasourceConnectionConfig;
27+
import com.openblocks.sdk.plugin.common.sql.SqlBasedQueryExecutionContext;
28+
import com.openblocks.sdk.plugin.sqlcommand.GuiSqlCommand;
29+
import com.openblocks.sdk.query.QueryVisitorContext;
30+
import com.openblocks.sdk.util.MustacheHelper;
31+
import com.zaxxer.hikari.HikariDataSource;
32+
33+
import lombok.extern.slf4j.Slf4j;
34+
35+
@Slf4j
36+
public abstract class HikariBasedQueryExecutor extends BlockingQueryExecutor<SqlBasedDatasourceConnectionConfig,
37+
HikariPerfWrapper, SqlBasedQueryExecutionContext> {
38+
39+
private final SqlExecutor sqlExecutor;
40+
41+
protected HikariBasedQueryExecutor(SqlExecutor sqlExecutor) {
42+
this.sqlExecutor = sqlExecutor;
43+
}
44+
45+
@Override
46+
public SqlBasedQueryExecutionContext buildQueryExecutionContext(SqlBasedDatasourceConnectionConfig datasourceConfig,
47+
Map<String, Object> queryConfig,
48+
Map<String, Object> requestParams, QueryVisitorContext queryVisitorContext) {
49+
SqlQueryConfig sqlQueryConfig = SqlQueryConfig.from(queryConfig);
50+
51+
if (sqlQueryConfig.isGuiMode()) {
52+
GuiSqlCommand sqlCommand = getGuiSqlCommand(sqlQueryConfig);
53+
return SqlBasedQueryExecutionContext.builder()
54+
.guiSqlCommand(sqlCommand)
55+
.requestParams(requestParams)
56+
.build();
57+
}
58+
59+
String query = SqlQueryUtils.removeQueryComments(sqlQueryConfig.getSql());
60+
if (StringUtils.isBlank(query)) {
61+
throw new PluginException(QUERY_ARGUMENT_ERROR, "SQL_EMPTY");
62+
}
63+
64+
return SqlBasedQueryExecutionContext.builder()
65+
.query(query)
66+
.requestParams(requestParams)
67+
.disablePreparedStatement(datasourceConfig.isEnableTurnOffPreparedStatement() &&
68+
sqlQueryConfig.isDisablePreparedStatement())
69+
.build();
70+
}
71+
72+
private GuiSqlCommand getGuiSqlCommand(SqlQueryConfig sqlQueryConfig) {
73+
String guiStatementType = sqlQueryConfig.getGuiStatementType();
74+
if (StringUtils.isBlank(guiStatementType)) {
75+
throw new PluginException(QUERY_ARGUMENT_ERROR, "GUI_COMMAND_TYPE_EMPTY");
76+
}
77+
Map<String, Object> guiStatementDetail = sqlQueryConfig.getGuiStatementDetail();
78+
if (MapUtils.isEmpty(guiStatementDetail)) {
79+
throw new PluginException(QUERY_ARGUMENT_ERROR, "INVALID_GUI_PARAM");
80+
}
81+
82+
return parseSqlCommand(guiStatementType, guiStatementDetail);
83+
}
84+
85+
protected abstract GuiSqlCommand parseSqlCommand(String guiStatementType, Map<String, Object> detail);
86+
87+
@Nonnull
88+
@Override
89+
protected QueryExecutionResult blockingExecuteQuery(HikariPerfWrapper hikariPerfWrapper, SqlBasedQueryExecutionContext context) {
90+
HikariDataSource dataSource = getHikariDataSource(hikariPerfWrapper);
91+
log.info("Hikari hashcode: {}, active: {}, idle: {}, wait: {}, total: {}", dataSource.hashCode()
92+
, dataSource.getHikariPoolMXBean().getActiveConnections(),
93+
dataSource.getHikariPoolMXBean().getIdleConnections(),
94+
dataSource.getHikariPoolMXBean().getThreadsAwaitingConnection(),
95+
dataSource.getHikariPoolMXBean().getTotalConnections()
96+
);
97+
98+
try (Connection connection = getConnection(dataSource)) {
99+
return sqlExecutor.execute(connection, context);
100+
} catch (SQLException e) {
101+
throw wrapException(QUERY_EXECUTION_ERROR, "QUERY_EXECUTION_ERROR", e);
102+
}
103+
}
104+
105+
private HikariDataSource getHikariDataSource(HikariPerfWrapper hikariDataSource) {
106+
return (HikariDataSource) hikariDataSource.getHikariDataSource();
107+
}
108+
109+
@Nonnull
110+
@Override
111+
public final DatasourceStructure blockingGetStructure(HikariPerfWrapper hikariPerfWrapper, SqlBasedDatasourceConnectionConfig connectionConfig) {
112+
HikariDataSource hikariDataSource = getHikariDataSource(hikariPerfWrapper);
113+
try (Connection connection = getConnection(hikariDataSource)) {
114+
return getDatabaseMetadata(connection, connectionConfig);
115+
} catch (SQLException e) {
116+
throw wrapException(QUERY_EXECUTION_ERROR, "QUERY_EXECUTION_ERROR", e);
117+
}
118+
}
119+
120+
protected abstract DatasourceStructure getDatabaseMetadata(Connection connection,
121+
SqlBasedDatasourceConnectionConfig connectionConfig);
122+
123+
@Override
124+
public Map<String, Object> sanitizeQueryConfig(Map<String, Object> configMap) {
125+
SqlQueryConfig queryConfig = SqlQueryConfig.from(configMap);
126+
Map<String, Object> result = Maps.newHashMap();
127+
if (queryConfig.isGuiMode()) {
128+
GuiSqlCommand guiSqlCommand = getGuiSqlCommand(queryConfig);
129+
result.put("fields", guiSqlCommand.extractMustacheKeys());
130+
return result;
131+
}
132+
133+
String sql = queryConfig.getSql();
134+
Set<String> mustacheKeys = MustacheHelper.extractMustacheKeysWithCurlyBraces(sql);
135+
result.put("fields", mustacheKeys);
136+
return result;
137+
}
138+
139+
private Connection getConnection(HikariDataSource hikariDataSource) {
140+
try {
141+
if (hikariDataSource == null || hikariDataSource.isClosed() || !hikariDataSource.isRunning()) {
142+
throw new InvalidHikariDatasourceException();
143+
}
144+
return hikariDataSource.getConnection();
145+
} catch (SQLException e) {
146+
throw new PluginException(CONNECTION_ERROR, "CONNECTION_ERROR", e.getMessage());
147+
}
148+
}
149+
150+
}

‎server/api-service/openblocks-plugins/sqlBasedPlugin/src/main/java/com/openblocks/plugin/sql/SqlExecutor.java

Lines changed: 242 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,242 @@
1+
package com.openblocks.plugin.sql;
2+
3+
import static com.google.common.collect.Lists.newArrayList;
4+
import static com.google.common.collect.Maps.newHashMapWithExpectedSize;
5+
import static com.openblocks.sdk.exception.PluginCommonError.PREPARED_STATEMENT_BIND_PARAMETERS_ERROR;
6+
import static com.openblocks.sdk.exception.PluginCommonError.QUERY_EXECUTION_ERROR;
7+
import static com.openblocks.sdk.util.ExceptionUtils.wrapException;
8+
import static com.openblocks.sdk.util.JsonUtils.toJson;
9+
import static com.openblocks.sdk.util.MustacheHelper.doPrepareStatement;
10+
import static com.openblocks.sdk.util.MustacheHelper.extractMustacheKeysInOrder;
11+
import static com.openblocks.sdk.util.MustacheHelper.renderMustacheString;
12+
import static java.util.Collections.emptyList;
13+
14+
import java.math.BigDecimal;
15+
import java.sql.Connection;
16+
import java.sql.PreparedStatement;
17+
import java.sql.ResultSet;
18+
import java.sql.SQLException;
19+
import java.sql.Statement;
20+
import java.sql.Types;
21+
import java.util.Collection;
22+
import java.util.HashMap;
23+
import java.util.List;
24+
import java.util.Map;
25+
26+
import javax.annotation.Nonnull;
27+
28+
import org.apache.commons.lang3.tuple.Pair;
29+
30+
import com.openblocks.sdk.exception.PluginException;
31+
import com.openblocks.sdk.models.QueryExecutionResult;
32+
import com.openblocks.sdk.plugin.common.sql.ResultSetParser;
33+
import com.openblocks.sdk.plugin.common.sql.SqlBasedQueryExecutionContext;
34+
import com.openblocks.sdk.plugin.sqlcommand.GuiSqlCommand;
35+
import com.openblocks.sdk.plugin.sqlcommand.GuiSqlCommand.GuiSqlCommandRenderResult;
36+
37+
import lombok.extern.slf4j.Slf4j;
38+
39+
@Slf4j
40+
public class SqlExecutor {
41+
42+
private final boolean supportGenerateKeys;
43+
44+
public SqlExecutor(boolean supportGenerateKeys) {
45+
this.supportGenerateKeys = supportGenerateKeys;
46+
}
47+
48+
public SqlExecutor() {
49+
this(true);
50+
}
51+
52+
@Nonnull
53+
public QueryExecutionResult execute(Connection connection, SqlBasedQueryExecutionContext context) {
54+
55+
GuiSqlCommand guiSqlCommand = context.getGuiSqlCommand();
56+
boolean guiMode = guiSqlCommand != null;
57+
String query = context.getQuery();
58+
boolean isPreparedStatement = guiMode || !context.isDisablePreparedStatement();
59+
Map<String, Object> requestParams = new HashMap<>(context.getRequestParams());
60+
61+
SqlExecutionInput sqlExecutionInput = getSqlExecutionInput(guiSqlCommand, query, isPreparedStatement, requestParams);
62+
Pair<Statement, Boolean> executionResult = getStatementAndExecute(connection, sqlExecutionInput);
63+
64+
boolean isResultSet = executionResult.getRight();
65+
try (Statement statement = executionResult.getLeft()) {
66+
return parseExecuteResult(statement, isResultSet);
67+
} catch (SQLException e) {
68+
throw wrapException(QUERY_EXECUTION_ERROR, "QUERY_EXECUTION_ERROR", e);
69+
}
70+
}
71+
72+
private QueryExecutionResult parseExecuteResult(Statement statement, boolean isResultSet) throws SQLException {
73+
74+
List<Object> result = newArrayList();
75+
int updateCount = statement.getUpdateCount();
76+
do {
77+
if (isResultSet) {
78+
try (ResultSet resultSet = statement.getResultSet()) {
79+
List<Map<String, Object>> dataRows = ResultSetParser.parseRows(resultSet);
80+
if (!isGeneratedKeysWithNullValue(dataRows)) {
81+
result.add(dataRows);
82+
}
83+
}
84+
} else {
85+
result.add(getAffectRowsAndGeneratedKeys(statement, updateCount));
86+
}
87+
88+
isResultSet = statement.getMoreResults();
89+
updateCount = statement.getUpdateCount();
90+
} while (isResultSet || updateCount != -1);
91+
92+
if (result.size() == 1) {
93+
return QueryExecutionResult.success(result.get(0));
94+
}
95+
96+
return QueryExecutionResult.success(result);
97+
}
98+
99+
private Map<String, Object> getAffectRowsAndGeneratedKeys(Statement statement, int updateCount) throws SQLException {
100+
Map<String, Object> result = newHashMapWithExpectedSize(2);
101+
result.put("affectedRows", updateCount);
102+
103+
ResultSet generatedKeys = getGeneratedKeys(statement);
104+
if (generatedKeys == null) {
105+
return result;
106+
}
107+
108+
try (generatedKeys) {
109+
List<Object> generatedIds = getGeneratedIds(generatedKeys);
110+
if (!generatedIds.isEmpty()) {
111+
result.put("generatedKeys", generatedIds);
112+
}
113+
}
114+
return result;
115+
}
116+
117+
private static ResultSet getGeneratedKeys(Statement statement) {
118+
// Oracle will throw exception here in some cases, so we catch the exception here
119+
try {
120+
return statement.getGeneratedKeys();
121+
} catch (Exception e) {
122+
return null;
123+
}
124+
}
125+
126+
private static boolean isGeneratedKeysWithNullValue(List<Map<String, Object>> dataRows) {
127+
if (dataRows.size() != 1) {
128+
return false;
129+
}
130+
Map<String, Object> map = dataRows.get(0);
131+
if (map.size() == 1) {
132+
return map.containsKey("GENERATED_KEYS");
133+
}
134+
return false;
135+
}
136+
137+
private Pair<Statement, Boolean> getStatementAndExecute(Connection connection, SqlExecutionInput sqlExecutionInput) {
138+
try {
139+
if (sqlExecutionInput.preparedStatement()) {
140+
String sql = sqlExecutionInput.sql();
141+
List<Object> params = sqlExecutionInput.params();
142+
var statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
143+
144+
bindPreparedStatementParams(statement, params);
145+
var isResultSet = statement.execute();
146+
return Pair.of(statement, isResultSet);
147+
}
148+
149+
var statement = connection.createStatement();
150+
boolean isResultSet;
151+
if (supportGenerateKeys) {
152+
isResultSet = statement.execute(sqlExecutionInput.sql(), Statement.RETURN_GENERATED_KEYS);
153+
} else {
154+
isResultSet = statement.execute(sqlExecutionInput.sql());
155+
}
156+
return Pair.of(statement, isResultSet);
157+
} catch (Exception e) {
158+
throw wrapException(QUERY_EXECUTION_ERROR, "QUERY_EXECUTION_ERROR", e);
159+
}
160+
}
161+
162+
private SqlExecutionInput getSqlExecutionInput(GuiSqlCommand guiSqlCommand, String query, boolean isPreparedStatement,
163+
Map<String, Object> requestParams) {
164+
if (isPreparedStatement) {
165+
return getPreparedStatementSqlInput(guiSqlCommand, query, requestParams);
166+
}
167+
String renderedSql = renderMustacheString(query, requestParams);
168+
return new SqlExecutionInput(false, renderedSql, emptyList());
169+
}
170+
171+
private SqlExecutionInput getPreparedStatementSqlInput(GuiSqlCommand guiSqlCommand, String query, Map<String, Object> requestParams) {
172+
if (guiSqlCommand != null) {
173+
GuiSqlCommandRenderResult renderResult = guiSqlCommand.render(requestParams);
174+
return new SqlExecutionInput(true, renderResult.sql(), renderResult.bindParams());
175+
}
176+
177+
List<String> mustacheKeysInOrder = extractMustacheKeysInOrder(query);
178+
var preparedSql = doPrepareStatement(query, mustacheKeysInOrder, requestParams);
179+
List<Object> bindParams = mustacheKeysInOrder.stream()
180+
.map(requestParams::get)
181+
.toList();
182+
return new SqlExecutionInput(true, preparedSql, bindParams);
183+
}
184+
185+
private void bindPreparedStatementParams(PreparedStatement preparedQuery, List<Object> bindParams) {
186+
try {
187+
for (int index = 0; index < bindParams.size(); index++) {
188+
Object value = bindParams.get(index);
189+
bindParam(index + 1, value, preparedQuery, "");
190+
}
191+
} catch (Exception e) {
192+
throw wrapException(PREPARED_STATEMENT_BIND_PARAMETERS_ERROR, "PREPARED_STATEMENT_BIND_PARAMETERS_ERROR", e);
193+
}
194+
}
195+
196+
private List<Object> getGeneratedIds(ResultSet generatedKeys) throws SQLException {
197+
if (generatedKeys == null) {
198+
return emptyList();
199+
}
200+
List<Object> array = newArrayList();
201+
while (generatedKeys.next()) {
202+
array.add(generatedKeys.getObject(1));
203+
}
204+
return array;
205+
}
206+
207+
private void bindParam(int bindIndex, Object value, PreparedStatement preparedStatement, String bindKeyName) throws SQLException {
208+
if (value == null) {
209+
preparedStatement.setNull(bindIndex, Types.NULL);
210+
return;
211+
}
212+
if (value instanceof Integer intValue) {
213+
preparedStatement.setInt(bindIndex, intValue);
214+
return;
215+
}
216+
if (value instanceof Long longValue) {
217+
preparedStatement.setLong(bindIndex, longValue);
218+
return;
219+
}
220+
if (value instanceof Float || value instanceof Double) {
221+
preparedStatement.setBigDecimal(bindIndex, new BigDecimal(String.valueOf(value)));
222+
return;
223+
}
224+
if (value instanceof Boolean boolValue) {
225+
preparedStatement.setBoolean(bindIndex, boolValue);
226+
return;
227+
}
228+
if (value instanceof Map<?, ?> || value instanceof Collection<?>) {
229+
preparedStatement.setString(bindIndex, toJson(value));
230+
return;
231+
}
232+
if (value instanceof String strValue) {
233+
preparedStatement.setString(bindIndex, strValue);
234+
return;
235+
}
236+
throw new PluginException(PREPARED_STATEMENT_BIND_PARAMETERS_ERROR, "PS_BIND_ERROR", bindKeyName, value.getClass().getSimpleName());
237+
}
238+
239+
private record SqlExecutionInput(boolean preparedStatement, String sql, List<Object> params) {
240+
241+
}
242+
}

0 commit comments

Comments
 (0)