1 package sharin.sql.runner;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.util.ArrayList;
7 import java.util.List;
8
9 import javax.sql.DataSource;
10
11 import sharin.sql.Sql;
12 import sharin.util.SqlUtils;
13
14 public class BasicSqlRunner implements SqlRunner {
15
16 private final DataSource dataSource;
17
18 private final ResultSetProcessor objectProcessor;
19
20 private final ResultSetProcessor idProcessor;
21
22 public BasicSqlRunner(DataSource dataSource) {
23 this(dataSource, null);
24 }
25
26 public BasicSqlRunner(DataSource dataSource,
27 ResultSetProcessor objectProcessor) {
28
29 this(dataSource, objectProcessor, null);
30 }
31
32 public BasicSqlRunner(DataSource dataSource,
33 ResultSetProcessor objectProcessor, ResultSetProcessor idProcessor) {
34
35 this.dataSource = dataSource;
36 this.objectProcessor = objectProcessor;
37 this.idProcessor = idProcessor;
38 }
39
40 @SuppressWarnings("unchecked")
41 public <R> R selectForObject(Sql sql) {
42 return (R) selectForObject(sql, objectProcessor);
43 }
44
45 @SuppressWarnings("unchecked")
46 public <R> R selectForObject(Sql sql, ResultSetProcessor processor) {
47 R result = null;
48 Connection conn = SqlUtils.getConnection(dataSource);
49
50 try {
51 PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
52 .getText());
53
54 try {
55 Object[] params = sql.getParams();
56
57 if (params != null) {
58
59 for (int i = 0; i < params.length; i++) {
60 SqlUtils.setObject(stmt, i + 1, params[i]);
61 }
62 }
63
64 ResultSet rs = SqlUtils.executeQuery(stmt);
65
66 try {
67 Object context = processor.prepare(rs);
68
69 if (SqlUtils.next(rs)) {
70 result = (R) processor.process(rs, context);
71 }
72
73 } finally {
74 SqlUtils.close(rs);
75 }
76
77 } finally {
78 SqlUtils.close(stmt);
79 }
80
81 } finally {
82 SqlUtils.close(conn);
83 }
84
85 return result;
86 }
87
88 public <R> List<R> selectForList(Sql sql) {
89 return selectForList(sql, objectProcessor);
90 }
91
92 public <R> List<R> selectForList(Sql sql, ResultSetProcessor processor) {
93 return selectForList(sql, Integer.MAX_VALUE, 0, processor);
94 }
95
96 public <R> List<R> selectForList(Sql sql, int limit, int offset) {
97 return selectForList(sql, limit, offset, objectProcessor);
98 }
99
100 @SuppressWarnings("unchecked")
101 public <R> List<R> selectForList(Sql sql, int limit, int offset,
102 ResultSetProcessor processor) {
103
104 List<R> resultList = new ArrayList<R>();
105 Connection conn = SqlUtils.getConnection(dataSource);
106
107 try {
108 PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
109 .getText());
110
111 try {
112 Object[] params = sql.getParams();
113
114 if (params != null) {
115
116 for (int i = 0; i < params.length; i++) {
117 SqlUtils.setObject(stmt, i + 1, params[i]);
118 }
119 }
120
121 ResultSet rs = SqlUtils.executeQuery(stmt);
122
123 try {
124 Object context = processor.prepare(rs);
125
126 for (int i = 0; i < offset; i++) {
127
128 if (!SqlUtils.next(rs)) {
129 return resultList;
130 }
131 }
132
133 for (int i = 0; i < limit; i++) {
134
135 if (!SqlUtils.next(rs)) {
136 break;
137 }
138
139 resultList.add((R) processor.process(rs, context));
140 }
141
142 } finally {
143 SqlUtils.close(rs);
144 }
145
146 } finally {
147 SqlUtils.close(stmt);
148 }
149
150 } finally {
151 SqlUtils.close(conn);
152 }
153
154 return resultList;
155 }
156
157 public int insert(Sql sql) {
158 int count = 0;
159 Connection conn = SqlUtils.getConnection(dataSource);
160
161 try {
162 PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
163 .getText());
164
165 try {
166 Object[] params = sql.getParams();
167
168 if (params != null) {
169
170 for (int i = 0; i < params.length; i++) {
171 SqlUtils.setObject(stmt, i + 1, params[i]);
172 }
173 }
174
175 count = SqlUtils.executeUpdate(stmt);
176
177 } finally {
178 SqlUtils.close(stmt);
179 }
180
181 } finally {
182 SqlUtils.close(conn);
183 }
184
185 return count;
186 }
187
188 @SuppressWarnings("unchecked")
189 public <I> I insertForId(Sql sql) {
190 return (I) insertForId(sql, idProcessor);
191 }
192
193 @SuppressWarnings("unchecked")
194 public <I> I insertForId(Sql sql, ResultSetProcessor processor) {
195 I id = null;
196 Connection conn = SqlUtils.getConnection(dataSource);
197
198 try {
199 PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
200 .getText());
201
202 try {
203 Object[] params = sql.getParams();
204
205 if (params != null) {
206
207 for (int i = 0; i < params.length; i++) {
208 SqlUtils.setObject(stmt, i + 1, params[i]);
209 }
210 }
211
212 int count = SqlUtils.executeUpdate(stmt);
213
214 if (count == 1) {
215 ResultSet rs = SqlUtils.getGeneratedKeys(stmt);
216
217 try {
218 Object context = processor.prepare(rs);
219
220 if (SqlUtils.next(rs)) {
221 id = (I) processor.process(rs, context);
222 }
223
224 } finally {
225 SqlUtils.close(rs);
226 }
227 }
228
229 } finally {
230 SqlUtils.close(stmt);
231 }
232
233 } finally {
234 SqlUtils.close(conn);
235 }
236
237 return id;
238 }
239
240 public int update(Sql sql) {
241 assert sql.getText().substring(0, 6).equalsIgnoreCase("UPDATE");
242 return execute(sql);
243 }
244
245 public int delete(Sql sql) {
246 assert sql.getText().substring(0, 6).equalsIgnoreCase("DELETE");
247 return execute(sql);
248 }
249
250 public int execute(Sql sql) {
251 int count = 0;
252 Connection conn = SqlUtils.getConnection(dataSource);
253
254 try {
255 PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
256 .getText());
257
258 try {
259 Object[] params = sql.getParams();
260
261 if (params != null) {
262
263 for (int i = 0; i < params.length; i++) {
264 SqlUtils.setObject(stmt, i + 1, params[i]);
265 }
266 }
267
268 count = SqlUtils.executeUpdate(stmt);
269
270 } finally {
271 SqlUtils.close(stmt);
272 }
273
274 } finally {
275 SqlUtils.close(conn);
276 }
277
278 return count;
279 }
280 }