1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.apache.ibatis.jdbc;
17
18 import java.sql.Connection;
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.sql.ResultSetMetaData;
22 import java.sql.SQLException;
23 import java.sql.Statement;
24 import java.util.ArrayList;
25 import java.util.HashMap;
26 import java.util.Iterator;
27 import java.util.List;
28 import java.util.Locale;
29 import java.util.Map;
30
31 import org.apache.ibatis.io.Resources;
32 import org.apache.ibatis.type.TypeHandler;
33 import org.apache.ibatis.type.TypeHandlerRegistry;
34
35
36
37
38 public class SqlRunner {
39
40 public static final int NO_GENERATED_KEY = Integer.MIN_VALUE + 1001;
41
42 private final Connection connection;
43 private final TypeHandlerRegistry typeHandlerRegistry;
44 private boolean useGeneratedKeySupport;
45
46 public SqlRunner(Connection connection) {
47 this.connection = connection;
48 this.typeHandlerRegistry = new TypeHandlerRegistry();
49 }
50
51 public void setUseGeneratedKeySupport(boolean useGeneratedKeySupport) {
52 this.useGeneratedKeySupport = useGeneratedKeySupport;
53 }
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68 public Map<String, Object> selectOne(String sql, Object... args) throws SQLException {
69 List<Map<String, Object>> results = selectAll(sql, args);
70 if (results.size() != 1) {
71 throw new SQLException("Statement returned " + results.size() + " results where exactly one (1) was expected.");
72 }
73 return results.get(0);
74 }
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89 public List<Map<String, Object>> selectAll(String sql, Object... args) throws SQLException {
90 try (PreparedStatement ps = connection.prepareStatement(sql)) {
91 setParameters(ps, args);
92 try (ResultSet rs = ps.executeQuery()) {
93 return getResults(rs);
94 }
95 }
96 }
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111 public int insert(String sql, Object... args) throws SQLException {
112 PreparedStatement ps;
113 if (useGeneratedKeySupport) {
114 ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
115 } else {
116 ps = connection.prepareStatement(sql);
117 }
118
119 try {
120 setParameters(ps, args);
121 ps.executeUpdate();
122 if (useGeneratedKeySupport) {
123 try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
124 List<Map<String, Object>> keys = getResults(generatedKeys);
125 if (keys.size() == 1) {
126 Map<String, Object> key = keys.get(0);
127 Iterator<Object> i = key.values().iterator();
128 if (i.hasNext()) {
129 Object genkey = i.next();
130 if (genkey != null) {
131 try {
132 return Integer.parseInt(genkey.toString());
133 } catch (NumberFormatException e) {
134
135 }
136 }
137 }
138 }
139 }
140 }
141 return NO_GENERATED_KEY;
142 } finally {
143 try {
144 ps.close();
145 } catch (SQLException e) {
146
147 }
148 }
149 }
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164 public int update(String sql, Object... args) throws SQLException {
165 try (PreparedStatement ps = connection.prepareStatement(sql)) {
166 setParameters(ps, args);
167 return ps.executeUpdate();
168 }
169 }
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184 public int delete(String sql, Object... args) throws SQLException {
185 return update(sql, args);
186 }
187
188
189
190
191
192
193
194
195
196
197 public void run(String sql) throws SQLException {
198 try (Statement stmt = connection.createStatement()) {
199 stmt.execute(sql);
200 }
201 }
202
203
204
205
206 @Deprecated
207 public void closeConnection() {
208 try {
209 connection.close();
210 } catch (SQLException e) {
211
212 }
213 }
214
215 private void setParameters(PreparedStatement ps, Object... args) throws SQLException {
216 for (int i = 0, n = args.length; i < n; i++) {
217 if (args[i] == null) {
218 throw new SQLException(
219 "SqlRunner requires an instance of Null to represent typed null values for JDBC compatibility");
220 }
221 if (args[i] instanceof Null) {
222 ((Null) args[i]).getTypeHandler().setParameter(ps, i + 1, null, ((Null) args[i]).getJdbcType());
223 } else {
224 TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(args[i].getClass());
225 if (typeHandler == null) {
226 throw new SQLException("SqlRunner could not find a TypeHandler instance for " + args[i].getClass());
227 } else {
228 typeHandler.setParameter(ps, i + 1, args[i], null);
229 }
230 }
231 }
232 }
233
234 private List<Map<String, Object>> getResults(ResultSet rs) throws SQLException {
235 List<Map<String, Object>> list = new ArrayList<>();
236 List<String> columns = new ArrayList<>();
237 List<TypeHandler<?>> typeHandlers = new ArrayList<>();
238 ResultSetMetaData rsmd = rs.getMetaData();
239 for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) {
240 columns.add(rsmd.getColumnLabel(i + 1));
241 try {
242 Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1));
243 TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type);
244 if (typeHandler == null) {
245 typeHandler = typeHandlerRegistry.getTypeHandler(Object.class);
246 }
247 typeHandlers.add(typeHandler);
248 } catch (Exception e) {
249 typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class));
250 }
251 }
252 while (rs.next()) {
253 Map<String, Object> row = new HashMap<>();
254 for (int i = 0, n = columns.size(); i < n; i++) {
255 String name = columns.get(i);
256 TypeHandler<?> handler = typeHandlers.get(i);
257 row.put(name.toUpperCase(Locale.ENGLISH), handler.getResult(rs, name));
258 }
259 list.add(row);
260 }
261 return list;
262 }
263
264 }