View Javadoc

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 }