|
4 | 4 | import java.util.*; |
5 | 5 |
|
6 | 6 | /** |
7 | | - * Executes SQL queries against a JDBC connection and serializes results to JSON-compatible Maps. |
| 7 | + * Executes SQL statements against a JDBC connection and serializes results to JSON-compatible Maps. |
8 | 8 | */ |
9 | 9 | public class QueryExecutor { |
10 | 10 |
|
11 | 11 | /** |
12 | | - * Execute a SQL query and return the result as a Map. |
| 12 | + * Execute a SQL statement and return the result as a Map. |
13 | 13 | * <p> |
14 | | - * For SELECT queries, returns {columns: [...], rows: [[...], ...]}. |
15 | | - * For UPDATE/INSERT/DELETE, returns {rows_affected: N}. |
| 14 | + * Uses {@link Statement#execute(String)} so no keyword sniffing is needed: |
| 15 | + * statements that return rows (SELECT, WITH ... SELECT, INSERT ... RETURNING) |
| 16 | + * yield {columns, rows}; everything else (INSERT/UPDATE/DELETE/MERGE/DDL) |
| 17 | + * yields {rows_affected: N}. |
16 | 18 | */ |
17 | 19 | public static Map<String, Object> execute(Connection conn, String sql) throws Exception { |
18 | | - sql = sql.trim(); |
19 | | - |
20 | | - boolean isQuery; |
21 | | - String upper = sql.toUpperCase().trim(); |
22 | | - isQuery = upper.startsWith("SELECT") |
23 | | - || upper.startsWith("WITH") |
24 | | - || upper.startsWith("EXPLAIN") |
25 | | - || upper.startsWith("SHOW") |
26 | | - || upper.startsWith("DESCRIBE") |
27 | | - || upper.startsWith("PRAGMA"); |
28 | | - |
29 | | - if (isQuery) { |
30 | | - return executeQuery(conn, sql); |
31 | | - } else { |
32 | | - return executeUpdate(conn, sql); |
33 | | - } |
34 | | - } |
| 20 | + try (Statement stmt = conn.createStatement()) { |
| 21 | + boolean isResultSet = stmt.execute(sql); |
35 | 22 |
|
36 | | - private static Map<String, Object> executeQuery(Connection conn, String sql) throws Exception { |
37 | | - try (Statement stmt = conn.createStatement(); |
38 | | - ResultSet rs = stmt.executeQuery(sql)) { |
| 23 | + List<String> columns = null; |
| 24 | + List<List<Object>> rows = null; |
| 25 | + long rowsAffected = 0; |
39 | 26 |
|
40 | | - ResultSetMetaData meta = rs.getMetaData(); |
41 | | - int columnCount = meta.getColumnCount(); |
| 27 | + while (true) { |
| 28 | + if (isResultSet) { |
| 29 | + try (ResultSet rs = stmt.getResultSet()) { |
| 30 | + ResultSetMetaData meta = rs.getMetaData(); |
| 31 | + int columnCount = meta.getColumnCount(); |
42 | 32 |
|
43 | | - List<String> columns = new ArrayList<>(); |
44 | | - for (int i = 1; i <= columnCount; i++) { |
45 | | - columns.add(meta.getColumnLabel(i)); |
46 | | - } |
| 33 | + List<String> resultColumns = new ArrayList<>(); |
| 34 | + for (int i = 1; i <= columnCount; i++) { |
| 35 | + resultColumns.add(meta.getColumnLabel(i)); |
| 36 | + } |
47 | 37 |
|
48 | | - List<List<Object>> rows = new ArrayList<>(); |
49 | | - while (rs.next()) { |
50 | | - List<Object> row = new ArrayList<>(); |
51 | | - for (int i = 1; i <= columnCount; i++) { |
52 | | - row.add(getValue(rs, i)); |
| 38 | + List<List<Object>> resultRows = new ArrayList<>(); |
| 39 | + while (rs.next()) { |
| 40 | + List<Object> row = new ArrayList<>(); |
| 41 | + for (int i = 1; i <= columnCount; i++) { |
| 42 | + row.add(getValue(rs, i)); |
| 43 | + } |
| 44 | + resultRows.add(row); |
| 45 | + } |
| 46 | + columns = resultColumns; |
| 47 | + rows = resultRows; |
| 48 | + } |
| 49 | + } else { |
| 50 | + int count = stmt.getUpdateCount(); |
| 51 | + if (count >= 0) { |
| 52 | + rowsAffected = count; |
| 53 | + } |
| 54 | + } |
| 55 | + isResultSet = stmt.getMoreResults(); |
| 56 | + if (!isResultSet && stmt.getUpdateCount() == -1) { |
| 57 | + break; |
53 | 58 | } |
54 | | - rows.add(row); |
55 | 59 | } |
56 | 60 |
|
57 | 61 | Map<String, Object> result = new LinkedHashMap<>(); |
58 | | - result.put("columns", columns); |
59 | | - result.put("rows", rows); |
60 | | - return result; |
61 | | - } |
62 | | - } |
63 | | - |
64 | | - private static Map<String, Object> executeUpdate(Connection conn, String sql) throws Exception { |
65 | | - try (Statement stmt = conn.createStatement()) { |
66 | | - int affected = stmt.executeUpdate(sql); |
67 | | - Map<String, Object> result = new LinkedHashMap<>(); |
68 | | - result.put("rows_affected", (long) affected); |
69 | | - result.put("columns", Collections.emptyList()); |
70 | | - result.put("rows", Collections.emptyList()); |
| 62 | + if (rows != null) { |
| 63 | + result.put("columns", columns); |
| 64 | + result.put("rows", rows); |
| 65 | + } else { |
| 66 | + result.put("rows_affected", rowsAffected); |
| 67 | + result.put("columns", Collections.emptyList()); |
| 68 | + result.put("rows", Collections.emptyList()); |
| 69 | + } |
71 | 70 | return result; |
72 | 71 | } |
73 | 72 | } |
|
0 commit comments