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