1 /*
2 * Copyright 2001,2004 The Apache Software Foundation.
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
17 package org.apache.commons.scaffold.sql;
18
19
20 import java.beans.PropertyDescriptor;
21 import java.lang.reflect.InvocationTargetException;
22 import java.lang.reflect.Method;
23 import java.sql.ResultSet;
24 import java.sql.ResultSetMetaData;
25 import java.sql.SQLException;
26 import java.sql.Types;
27 import java.util.ArrayList;
28 import java.util.Collection;
29 import java.util.HashMap;
30 import java.util.Iterator;
31 import java.util.Map;
32
33 import org.apache.commons.beanutils.BeanUtils;
34 import org.apache.commons.beanutils.PropertyUtils;
35
36
37 /**
38 * General purpose utility methods related to ResultSets
39 *
40 * @author Ted Husted
41 * @version $Revision: 561366 $ $Date: 2007-07-31 16:58:29 +0100 (Tue, 31 Jul 2007) $
42 */
43 public class ResultSetUtils {
44
45
46 /**
47 * Returns next record of result set as a Map.
48 * The keys of the map are the column names,
49 * as returned by the metadata.
50 * The values are the columns as Objects.
51 *
52 * @param resultSet The ResultSet to process.
53 * @exception SQLException if an error occurs.
54 */
55 public static Map getMap(ResultSet resultSet)
56 throws SQLException {
57
58 // Acquire resultSet MetaData
59 ResultSetMetaData metaData = resultSet.getMetaData();
60 int cols = metaData.getColumnCount();
61
62 // Create hashmap, sized to number of columns
63 HashMap row = new HashMap(cols,1);
64
65 // Transfer record into hashmap
66 if (resultSet.next()) {
67 for (int i=1; i<=cols ; i++) {
68 row.put(metaData.getColumnName(i),
69 resultSet.getObject(i));
70 }
71 } // end while
72
73 return ((Map) row);
74
75 } // end getMap
76
77
78 /**
79 * Return a Collection of Maps, each representing
80 * a row from the ResultSet.
81 * The keys of the map are the column names,
82 * as returned by the metadata.
83 * The values are the columns as Objects.
84 *
85 * @param resultSet The ResultSet to process.
86 * @exception SQLException if an error occurs.
87 */
88 public static Collection getMaps(ResultSet resultSet)
89 throws SQLException {
90
91 // Acquire resultSet MetaData
92 ResultSetMetaData metaData = resultSet.getMetaData();
93 int cols = metaData.getColumnCount();
94
95 // Use ArrayList to maintain ResultSet sequence
96 ArrayList list = new ArrayList();
97
98 // Scroll to each record, make map of row, add to list
99 while (resultSet.next()) {
100 HashMap row = new HashMap(cols,1);
101 for (int i=1; i<=cols ; i++) {
102 row.put(metaData.getColumnName(i),
103 resultSet.getString(i));
104 }
105 list.add(row);
106 } // end while
107
108 return ((Collection) list);
109
110 } // end getMaps
111
112
113 /**
114 * Populate the JavaBean properties of the specified bean, based on
115 * the specified name/value pairs. This method uses Java reflection APIs
116 * to identify corresponding "property setter" method names. The type of
117 * the value in the Map must match the setter type. The setter must
118 * expect a single arguement (the one on the Map).
119 * <p>
120 * The particular setter method to be called for each property is
121 * determined using the usual JavaBeans introspection mechanisms. Thus,
122 * you may identify custom setter methods using a BeanInfo class that is
123 * associated with the class of the bean itself. If no such BeanInfo
124 * class is available, the standard method name conversion ("set" plus
125 * the capitalized name of the property in question) is used.
126 * <p>
127 * <strong>NOTE</strong>: It is contrary to the JavaBeans Specification
128 * to have more than one setter method (with different argument
129 * signatures) for the same property.
130 * <p>
131 * This method adopted from the Apache Commons BeanUtils.populate.
132 *
133 * @author Craig R. McClanahan
134 * @author Ralph Schaer
135 * @author Chris Audley
136 * @author Rey Fran�ois
137 * @author Gregor Ra�man
138 * @author Ted Husted
139 *
140 * @param bean JavaBean whose properties are being populated
141 * @param properties Map keyed by property name, with the
142 * corresponding value to be set
143 *
144 * @exception IllegalAccessException if the caller does not have
145 * access to the property accessor method
146 * @exception InvocationTargetException if the property accessor method
147 * throws an exception
148 * @deprecated Use BeanUtils.CopyProperties instead.
149 */
150 public static void setProperties(Object bean, Map properties)
151 throws IllegalAccessException, InvocationTargetException {
152
153 if ((bean == null) || (properties == null))
154 return;
155
156 /*
157 if (debug >= 1)
158 System.out.println("BeanUtils.populate(" + bean + ", " +
159 properties + ")");
160 */
161
162 // Loop through the property name/value pairs to be set
163 Iterator names = properties.keySet().iterator();
164 while (names.hasNext()) {
165
166 // Identify the property name and value(s) to be assigned
167 String name = (String) names.next();
168 if (name == null)
169 continue;
170
171 // Get the property descriptor of the requested property (if any)
172 PropertyDescriptor descriptor = null;
173 try {
174 descriptor = PropertyUtils.getPropertyDescriptor(bean, name);
175 } catch (Throwable t) {
176 /*
177 if (debug >= 1)
178 System.out.println(" getPropertyDescriptor: " + t);
179 */
180 descriptor = null;
181 }
182 if (descriptor == null) {
183 /*
184 if (debug >= 1)
185 System.out.println(" No such property, skipping");
186 */
187 continue;
188 }
189
190 /*
191 if (debug >= 1)
192 System.out.println(" Property descriptor is '" +
193 descriptor + "'");
194 */
195
196 // Identify the relevant setter method (if there is one)
197 Method setter = descriptor.getWriteMethod();
198 if (setter == null) {
199 /*
200 if (debug >= 1)
201 System.out.println(" No setter method, skipping");
202 */
203 continue;
204 }
205
206 // Obtain value to be set
207 Object[] args = new Object[1];
208 args[0] = properties.get(name); // This MUST match setter type
209
210 /*
211 if (debug >= 1)
212 System.out.println(" name='" + name + "', value.class='" +
213 (value == null ? "NONE" :
214 value.getClass().getName()) + "'");
215 */
216 /*
217 if (debug >= 1)
218 System.out.println(" Setting to " +
219 (parameters[0] == null ? "NULL" :
220 "'" + parameters[0] + "'"));
221 */
222
223 // Invoke the setter method
224 setter.invoke(bean,args);
225 }
226
227 /*
228 if (debug >= 1)
229 System.out.println("============================================");
230 */
231
232 } // end setProperties
233
234
235 /**
236 * Map JDBC objects to Java equivalents.
237 * Used by getBean() and getBeans().
238 * <p>
239 * Some types not supported.
240 * Many not work with all drivers.
241 * <p>
242 * Makes binary conversions of BIGINT, DATE, DECIMAL, DOUBLE, FLOAT, INTEGER,
243 * REAL, SMALLINT, TIME, TIMESTAMP, TINYINT.
244 * Makes Sting conversions of CHAR, CLOB, VARCHAR, LONGVARCHAR, BLOB, LONGVARBINARY,
245 * VARBINARY.
246 * <p>
247 * DECIMAL, INTEGER, SMALLINT, TIMESTAMP, CHAR, VARCHAR tested with MySQL and Poolman.
248 * Others not guaranteed.
249 */
250 public static void putEntry(
251 Map properties,
252 ResultSetMetaData metaData,
253 ResultSet resultSet,
254 int i)
255 throws SQLException {
256
257 /*
258 In a perfect universe, this would be enough
259 properties.put(
260 metaData.getColumnName(i),
261 resultSet.getObject(i));
262 But only String, Timestamp, and Integer seem to get through that way.
263 */
264
265 String columnName = metaData.getColumnName(i);
266
267 switch (metaData.getColumnType(i)) {
268
269 // http://java.sun.com/j2se/1.3.0/docs/api/java/sql/Types.html
270
271 case Types.BIGINT:
272 properties.put(columnName,
273 new Long(resultSet.getLong(i)));
274 break;
275
276 case Types.DATE:
277 properties.put(columnName,
278 resultSet.getDate(i));
279 break;
280
281 case Types.DECIMAL:
282 case Types.DOUBLE:
283 properties.put(columnName,
284 new Double(resultSet.getDouble(i)));
285 break;
286
287 case Types.FLOAT:
288 properties.put(columnName,
289 new Float(resultSet.getFloat(i)));
290 break;
291
292 case Types.INTEGER:
293 properties.put(columnName,
294 new Integer(resultSet.getInt(i)));
295 break;
296
297 case Types.REAL:
298 properties.put(columnName,
299 new Double(resultSet.getString(i)));
300 break;
301
302 case Types.SMALLINT:
303 properties.put(columnName,
304 new Short(resultSet.getShort(i)));
305 break;
306
307 case Types.TIME:
308 properties.put(columnName,
309 resultSet.getTime(i));
310 break;
311
312 case Types.TIMESTAMP:
313 properties.put(columnName,
314 resultSet.getTimestamp(i));
315 break;
316
317 // :FIXME: Throws java.lang.ClassCastException: java.lang.Integer
318 // :FIXME: with Poolman and MySQL unless use getString.
319 case Types.TINYINT:
320 properties.put(columnName,
321 new Byte(resultSet.getString(i)));
322 break;
323
324 case Types.CHAR:
325 case Types.CLOB:
326 case Types.VARCHAR:
327 case Types.LONGVARCHAR:
328 // :FIXME: Handle binaries differently?
329 case Types.BLOB:
330 case Types.LONGVARBINARY:
331 case Types.VARBINARY:
332 properties.put(columnName,
333 resultSet.getString(i));
334 break;
335
336 /*
337 :FIXME: Add handlers for
338 ARRAY
339 BINARY
340 BIT
341 DISTINCT
342 JAVA_OBJECT
343 NULL
344 NUMERIC
345 OTHER
346 REF
347 STRUCT
348 */
349
350 // Otherwise, pass as *String property to be converted
351 default:
352 properties.put(columnName + "String",
353 resultSet.getString(i));
354 break;
355 } // end switch
356
357 } // end putEntry
358
359
360 /**
361 * Populate target bean with the first record from a ResultSet.
362 *
363 * @param resultSet The ResultSet whose parameters are to be used
364 * to populate bean properties
365 * @param target An instance of the bean to populate
366 * @exception SQLException if an exception is thrown while setting
367 * property values, populating the bean, or accessing the ResultSet
368 * @return True if resultSet contained a next element
369 */
370 public static boolean getElement(Object target, ResultSet resultSet)
371 throws SQLException {
372
373 // Check prerequisites
374 if ((target==null) || (resultSet==null))
375 throw new SQLException("getElement: Null parameter");
376
377 // Acquire resultSet MetaData
378 ResultSetMetaData metaData = resultSet.getMetaData();
379 int cols = metaData.getColumnCount();
380
381 // Create hashmap, sized to number of columns
382 HashMap properties = new HashMap(cols,1);
383
384 // Scroll to next record and pump into hashmap
385 boolean found = false;
386 if (resultSet.next()) {
387 found = true;
388 for (int i=1; i<=cols ; i++) {
389 putEntry(properties,metaData,resultSet,i);
390 }
391 try {
392 BeanUtils.copyProperties(target,properties);
393 }
394 catch (Throwable t) {
395 throw new SQLException("ResultSetUtils.getElement: " +
396 t.getMessage() + properties.toString());
397 }
398
399 } // end if
400
401 return found;
402
403 } // end getElement
404
405
406 /**
407 * Return a ArrayList of beans populated from a ResultSet.
408 *
409 * @param resultSet The ResultSet whose parameters are to be used
410 * to populate bean properties
411 * @param target An instance of the bean to populate
412 * @exception SQLException if an exception is thrown while setting
413 * property values, populating the bean, or accessing the ResultSet
414 */
415 public static Collection getCollection(Object target, ResultSet resultSet)
416 throws SQLException {
417
418 // Check prerequisites
419 if ((target==null) || (resultSet==null))
420 throw new SQLException("getCollection: Null parameter");
421
422 // Acquire resultSet MetaData
423 ResultSetMetaData metaData = resultSet.getMetaData();
424 int cols = metaData.getColumnCount();
425
426 // Create hashmap, sized to number of columns
427 HashMap properties = new HashMap(cols,1);
428
429 // Use ArrayList to maintain ResultSet sequence
430 ArrayList list = new ArrayList();
431
432 // Acquire target class
433 Class factory = target.getClass();
434
435 // Scroll to next record and pump into hashmap
436 while (resultSet.next()) {
437 for (int i=1; i<=cols ; i++) {
438 putEntry(properties,metaData,resultSet,i);
439 }
440 try {
441 Object bean = factory.newInstance();
442 BeanUtils.copyProperties(bean,properties);
443 list.add(bean);
444 }
445 catch (Throwable t) {
446 throw new SQLException("RequestUtils.getCollection: " +
447 t.getMessage());
448 }
449
450 properties.clear();
451
452 } // end while
453
454 return ((Collection) list);
455
456 } // end getCollection
457
458
459 } // end ResultSetUtils
460