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 public Map<String, Object> selectOne(String sql, Object... args) throws SQLException {
64 List<Map<String, Object>> results = selectAll(sql, args);
65 if (results.size() != 1) {
66 throw new SQLException("Statement returned " + results.size() + " results where exactly one (1) was expected.");
67 }
68 return results.get(0);
69 }
70
71
72
73
74
75
76
77
78
79 public List<Map<String, Object>> selectAll(String sql, Object... args) throws SQLException {
80 PreparedStatement ps = connection.prepareStatement(sql);
81 try {
82 setParameters(ps, args);
83 ResultSet rs = ps.executeQuery();
84 return getResults(rs);
85 } finally {
86 try {
87 ps.close();
88 } catch (SQLException e) {
89
90 }
91 }
92 }
93
94
95
96
97
98
99
100
101
102 public int insert(String sql, Object... args) throws SQLException {
103 PreparedStatement ps;
104 if (useGeneratedKeySupport) {
105 ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
106 } else {
107 ps = connection.prepareStatement(sql);
108 }
109
110 try {
111 setParameters(ps, args);
112 ps.executeUpdate();
113 if (useGeneratedKeySupport) {
114 List<Map<String, Object>> keys = getResults(ps.getGeneratedKeys());
115 if (keys.size() == 1) {
116 Map<String, Object> key = keys.get(0);
117 Iterator<Object> i = key.values().iterator();
118 if (i.hasNext()) {
119 Object genkey = i.next();
120 if (genkey != null) {
121 try {
122 return Integer.parseInt(genkey.toString());
123 } catch (NumberFormatException e) {
124
125 }
126 }
127 }
128 }
129 }
130 return NO_GENERATED_KEY;
131 } finally {
132 try {
133 ps.close();
134 } catch (SQLException e) {
135
136 }
137 }
138 }
139
140
141
142
143
144
145
146
147
148 public int update(String sql, Object... args) throws SQLException {
149 PreparedStatement ps = connection.prepareStatement(sql);
150 try {
151 setParameters(ps, args);
152 return ps.executeUpdate();
153 } finally {
154 try {
155 ps.close();
156 } catch (SQLException e) {
157
158 }
159 }
160 }
161
162
163
164
165
166
167
168
169
170 public int delete(String sql, Object... args) throws SQLException {
171 return update(sql, args);
172 }
173
174
175
176
177
178
179
180
181 public void run(String sql) throws SQLException {
182 Statement stmt = connection.createStatement();
183 try {
184 stmt.execute(sql);
185 } finally {
186 try {
187 stmt.close();
188 } catch (SQLException e) {
189
190 }
191 }
192 }
193
194
195
196
197 @Deprecated
198 public void closeConnection() {
199 try {
200 connection.close();
201 } catch (SQLException e) {
202
203 }
204 }
205
206 private void setParameters(PreparedStatement ps, Object... args) throws SQLException {
207 for (int i = 0, n = args.length; i < n; i++) {
208 if (args[i] == null) {
209 throw new SQLException("SqlRunner requires an instance of Null to represent typed null values for JDBC compatibility");
210 } else if (args[i] instanceof Null) {
211 ((Null) args[i]).getTypeHanNull().setParameter(ps, i + 1, null, ((Null) args[i]).getJdbcType());
212 } else {
213 TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(args[i].getClass());
214 if (typeHandler == null) {
215 throw new SQLException("SqlRunner could not find a TypeHandler instance for " + args[i].getClass());
216 } else {
217 typeHandler.setParameter(ps, i + 1, args[i], null);
218 }
219 }
220 }
221 }
222
223 private List<Map<String, Object>> getResults(ResultSet rs) throws SQLException {
224 try {
225 List<Map<String, Object>> list = new ArrayList<>();
226 List<String> columns = new ArrayList<>();
227 List<TypeHandler<?>> typeHandlers = new ArrayList<>();
228 ResultSetMetaData rsmd = rs.getMetaData();
229 for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) {
230 columns.add(rsmd.getColumnLabel(i + 1));
231 try {
232 Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1));
233 TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type);
234 if (typeHandler == null) {
235 typeHandler = typeHandlerRegistry.getTypeHandler(Object.class);
236 }
237 typeHandlers.add(typeHandler);
238 } catch (Exception e) {
239 typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class));
240 }
241 }
242 while (rs.next()) {
243 Map<String, Object> row = new HashMap<>();
244 for (int i = 0, n = columns.size(); i < n; i++) {
245 String name = columns.get(i);
246 TypeHandler<?> handler = typeHandlers.get(i);
247 row.put(name.toUpperCase(Locale.ENGLISH), handler.getResult(rs, name));
248 }
249 list.add(row);
250 }
251 return list;
252 } finally {
253 if (rs != null) {
254 try {
255 rs.close();
256 } catch (Exception e) {
257
258 }
259 }
260 }
261 }
262
263 }