1 /*
2 * Copyright 2003-2008 the original author or authors.
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 * http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16 package groovy.sql;
17
18 import groovy.lang.Closure;
19 import groovy.lang.GroovyRuntimeException;
20 import org.codehaus.groovy.ast.ClassNode;
21 import org.codehaus.groovy.ast.MethodNode;
22 import org.codehaus.groovy.ast.CodeVisitorSupport;
23 import org.codehaus.groovy.ast.stmt.Statement;
24
25 import java.sql.Connection;
26 import java.sql.PreparedStatement;
27 import java.sql.SQLException;
28 import java.sql.ResultSet;
29 import java.util.ArrayList;
30 import java.util.Iterator;
31 import java.util.List;
32 import java.util.Map;
33 import java.util.logging.Level;
34
35 /**
36 * Represents an extent of objects
37 *
38 * @author Chris Stevenson
39 * @author Paul King
40 * @author <a href="mailto:james@coredevelopers.net">James Strachan</a>
41 * @version $Revision: 16362 $
42 */
43 public class DataSet extends Sql {
44
45 private Closure where;
46 private Closure sort;
47 private boolean reversed = false;
48 private DataSet parent;
49 private String table;
50 private SqlWhereVisitor visitor;
51 private SqlOrderByVisitor sortVisitor;
52 private String sql;
53 private List params;
54 private Sql delegate;
55
56 public DataSet(Sql sql, Class type) {
57 super(sql);
58 delegate = sql;
59 String table = type.getName();
60 int idx = table.lastIndexOf('.');
61 if (idx > 0) {
62 table = table.substring(idx + 1);
63 }
64 this.table = table.toLowerCase();
65 }
66
67 public DataSet(Sql sql, String table) {
68 super(sql);
69 delegate = sql;
70 this.table = table;
71 }
72
73 private DataSet(DataSet parent, Closure where) {
74 super(parent);
75 this.delegate = parent.delegate;
76 this.table = parent.table;
77 this.parent = parent;
78 this.where = where;
79 }
80
81 private DataSet(DataSet parent, Closure where, Closure sort) {
82 super(parent);
83 this.delegate = parent.delegate;
84 this.table = parent.table;
85 this.parent = parent;
86 this.where = where;
87 this.sort = sort;
88 }
89
90 private DataSet(DataSet parent) {
91 super(parent);
92 this.delegate = parent.delegate;
93 this.table = parent.table;
94 this.parent = parent;
95 this.reversed = true;
96 }
97
98 @Override
99 protected Connection createConnection() throws SQLException {
100 return delegate.createConnection();
101 }
102
103 @Override
104 protected void closeResources(Connection connection, java.sql.Statement statement, ResultSet results) {
105 delegate.closeResources(connection, statement, results);
106 }
107
108 @Override
109 protected void closeResources(Connection connection, java.sql.Statement statement) {
110 delegate.closeResources(connection, statement);
111 }
112
113 @Override
114 public void cacheConnection(Closure closure) throws SQLException {
115 delegate.cacheConnection(closure);
116 }
117
118 @Override
119 public void withTransaction(Closure closure) throws SQLException {
120 delegate.withTransaction(closure);
121 }
122
123 @Override
124 public void commit() throws SQLException {
125 delegate.commit();
126 }
127
128 @Override
129 public void rollback() throws SQLException {
130 delegate.rollback();
131 }
132
133 public void add(Map<String, Object> map) throws SQLException {
134 StringBuffer buffer = new StringBuffer("insert into ");
135 buffer.append(table);
136 buffer.append(" (");
137 StringBuffer paramBuffer = new StringBuffer();
138 boolean first = true;
139 for (String column : map.keySet()) {
140 if (first) {
141 first = false;
142 paramBuffer.append("?");
143 } else {
144 buffer.append(", ");
145 paramBuffer.append(", ?");
146 }
147 buffer.append(column);
148 }
149 buffer.append(") values (");
150 buffer.append(paramBuffer.toString());
151 buffer.append(")");
152
153 Connection connection = createConnection();
154 PreparedStatement statement = null;
155 try {
156 statement = connection.prepareStatement(buffer.toString());
157 int i = 1;
158 for (Object value : map.values()) {
159 setObject(statement, i++, value);
160 }
161 int answer = statement.executeUpdate();
162 if (answer != 1) {
163 log.log(Level.WARNING, "Should have updated 1 row not " + answer + " when trying to add: " + map);
164 }
165 }
166 catch (SQLException e) {
167 log.log(Level.WARNING, "Failed to add row for: " + map, e);
168 throw e;
169 }
170 finally {
171 closeResources(connection, statement);
172 }
173 }
174
175 public DataSet findAll(Closure where) {
176 return new DataSet(this, where);
177 }
178
179 public DataSet sort(Closure sort) {
180 return new DataSet(this, null, sort);
181 }
182
183 public DataSet reverse() {
184 if (sort == null) {
185 throw new GroovyRuntimeException("reverse() only allowed immediately after a sort()");
186 }
187 return new DataSet(this);
188 }
189
190 public void each(Closure closure) throws SQLException {
191 eachRow(getSql(), getParameters(), closure);
192 }
193
194 private String getSqlWhere() {
195 String whereClaus = "";
196 String parentClaus = "";
197 if (parent != null) {
198 parentClaus = parent.getSqlWhere();
199 }
200 if (where != null) {
201 whereClaus += getSqlWhereVisitor().getWhere();
202 }
203 if (parentClaus.length() == 0) return whereClaus;
204 if (whereClaus.length() == 0) return parentClaus;
205 return parentClaus + " and " + whereClaus;
206 }
207
208 private String getSqlOrderBy() {
209 String sortByClaus = "";
210 String parentClaus = "";
211 if (parent != null) {
212 parentClaus = parent.getSqlOrderBy();
213 }
214 if (reversed) {
215 if (parentClaus.length() > 0) parentClaus += " DESC";
216 }
217 if (sort != null) {
218 sortByClaus += getSqlOrderByVisitor().getOrderBy();
219 }
220 if (parentClaus.length() == 0) return sortByClaus;
221 if (sortByClaus.length() == 0) return parentClaus;
222 return parentClaus + ", " + sortByClaus;
223 }
224
225 public String getSql() {
226 if (sql == null) {
227 sql = "select * from " + table;
228 String whereClaus = getSqlWhere();
229 if (whereClaus.length() > 0) {
230 sql += " where " + whereClaus;
231 }
232 String orerByClaus = getSqlOrderBy();
233 if (orerByClaus.length() > 0) {
234 sql += " order by " + orerByClaus;
235 }
236 }
237 return sql;
238 }
239
240 public List getParameters() {
241 if (params == null) {
242 params = new ArrayList();
243 if (parent != null) {
244 params.addAll(parent.getParameters());
245 }
246 params.addAll(getSqlWhereVisitor().getParameters());
247 }
248 return params;
249 }
250
251 protected SqlWhereVisitor getSqlWhereVisitor() {
252 if (visitor == null) {
253 visitor = new SqlWhereVisitor();
254 visit(where, visitor);
255 }
256 return visitor;
257 }
258
259 protected SqlOrderByVisitor getSqlOrderByVisitor() {
260 if (sortVisitor == null) {
261 sortVisitor = new SqlOrderByVisitor();
262 visit(sort, sortVisitor);
263 }
264 return sortVisitor;
265 }
266
267 private void visit(Closure closure, CodeVisitorSupport visitor) {
268 if (closure != null) {
269 ClassNode classNode = closure.getMetaClass().getClassNode();
270 if (classNode == null) {
271 throw new GroovyRuntimeException(
272 "Could not find the ClassNode for MetaClass: " + closure.getMetaClass());
273 }
274 List methods = classNode.getDeclaredMethods("doCall");
275 if (!methods.isEmpty()) {
276 MethodNode method = (MethodNode) methods.get(0);
277 if (method != null) {
278 Statement statement = method.getCode();
279 if (statement != null) {
280 statement.visit(visitor);
281 }
282 }
283 }
284 }
285 }
286
287 /*
288 * create a subset of the original dataset
289 */
290 public DataSet createView(Closure criteria) {
291 return new DataSet(this, criteria);
292 }
293
294 /**
295 * Returns a List of all of the rows from the table a DataSet
296 * represents
297 *
298 * @return Returns a list of GroovyRowResult objects from the dataset
299 * @throws SQLException if a database error occurs
300 */
301 public List rows() throws SQLException {
302 return rows(getSql(), getParameters());
303 }
304
305 /**
306 * Returns the first row from a DataSet's underlying table
307 *
308 * @return Returns the first GroovyRowResult object from the dataset
309 * @throws SQLException if a database error occurs
310 */
311 public Object firstRow() throws SQLException {
312 List rows = rows();
313 if (rows.isEmpty()) return null;
314 return (rows.get(0));
315 }
316 }
Save This Page