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 Connection connection;
43 private 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 public void closeConnection() {
195 try {
196 connection.close();
197 } catch (SQLException e) {
198
199 }
200 }
201
202 private void setParameters(PreparedStatement ps, Object... args) throws SQLException {
203 for (int i = 0, n = args.length; i < n; i++) {
204 if (args[i] == null) {
205 throw new SQLException("SqlRunner requires an instance of Null to represent typed null values for JDBC compatibility");
206 } else if (args[i] instanceof Null) {
207 ((Null) args[i]).getTypeHandler().setParameter(ps, i + 1, null, ((Null) args[i]).getJdbcType());
208 } else {
209 TypeHandler typeHandler = typeHandlerRegistry.getTypeHandler(args[i].getClass());
210 if (typeHandler == null) {
211 throw new SQLException("SqlRunner could not find a TypeHandler instance for " + args[i].getClass());
212 } else {
213 typeHandler.setParameter(ps, i + 1, args[i], null);
214 }
215 }
216 }
217 }
218
219 private List<Map<String, Object>> getResults(ResultSet rs) throws SQLException {
220 try {
221 List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
222 List<String> columns = new ArrayList<String>();
223 List<TypeHandler<?>> typeHandlers = new ArrayList<TypeHandler<?>>();
224 ResultSetMetaData rsmd = rs.getMetaData();
225 for (int i = 0, n = rsmd.getColumnCount(); i < n; i++) {
226 columns.add(rsmd.getColumnLabel(i + 1));
227 try {
228 Class<?> type = Resources.classForName(rsmd.getColumnClassName(i + 1));
229 TypeHandler<?> typeHandler = typeHandlerRegistry.getTypeHandler(type);
230 if (typeHandler == null) {
231 typeHandler = typeHandlerRegistry.getTypeHandler(Object.class);
232 }
233 typeHandlers.add(typeHandler);
234 } catch (Exception e) {
235 typeHandlers.add(typeHandlerRegistry.getTypeHandler(Object.class));
236 }
237 }
238 while (rs.next()) {
239 Map<String, Object> row = new HashMap<String, Object>();
240 for (int i = 0, n = columns.size(); i < n; i++) {
241 String name = columns.get(i);
242 TypeHandler<?> handler = typeHandlers.get(i);
243 row.put(name.toUpperCase(Locale.ENGLISH), handler.getResult(rs, name));
244 }
245 list.add(row);
246 }
247 return list;
248 } finally {
249 if (rs != null) {
250 try {
251 rs.close();
252 } catch (Exception e) {
253
254 }
255 }
256 }
257 }
258
259 }