001 /* 002 * Copyright 2001,2004 The Apache Software Foundation. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016 017 package org.apache.commons.scaffold.sql; 018 019 020 import java.beans.PropertyDescriptor; 021 import java.lang.reflect.InvocationTargetException; 022 import java.lang.reflect.Method; 023 import java.sql.ResultSet; 024 import java.sql.ResultSetMetaData; 025 import java.sql.SQLException; 026 import java.sql.Types; 027 import java.util.ArrayList; 028 import java.util.Collection; 029 import java.util.HashMap; 030 import java.util.Iterator; 031 import java.util.Map; 032 033 import org.apache.commons.beanutils.BeanUtils; 034 import org.apache.commons.beanutils.PropertyUtils; 035 036 037 /** 038 * General purpose utility methods related to ResultSets 039 * 040 * @author Ted Husted 041 * @version $Revision: 561366 $ $Date: 2007-07-31 16:58:29 +0100 (Tue, 31 Jul 2007) $ 042 */ 043 public class ResultSetUtils { 044 045 046 /** 047 * Returns next record of result set as a Map. 048 * The keys of the map are the column names, 049 * as returned by the metadata. 050 * The values are the columns as Objects. 051 * 052 * @param resultSet The ResultSet to process. 053 * @exception SQLException if an error occurs. 054 */ 055 public static Map getMap(ResultSet resultSet) 056 throws SQLException { 057 058 // Acquire resultSet MetaData 059 ResultSetMetaData metaData = resultSet.getMetaData(); 060 int cols = metaData.getColumnCount(); 061 062 // Create hashmap, sized to number of columns 063 HashMap row = new HashMap(cols,1); 064 065 // Transfer record into hashmap 066 if (resultSet.next()) { 067 for (int i=1; i<=cols ; i++) { 068 row.put(metaData.getColumnName(i), 069 resultSet.getObject(i)); 070 } 071 } // end while 072 073 return ((Map) row); 074 075 } // end getMap 076 077 078 /** 079 * Return a Collection of Maps, each representing 080 * a row from the ResultSet. 081 * The keys of the map are the column names, 082 * as returned by the metadata. 083 * The values are the columns as Objects. 084 * 085 * @param resultSet The ResultSet to process. 086 * @exception SQLException if an error occurs. 087 */ 088 public static Collection getMaps(ResultSet resultSet) 089 throws SQLException { 090 091 // Acquire resultSet MetaData 092 ResultSetMetaData metaData = resultSet.getMetaData(); 093 int cols = metaData.getColumnCount(); 094 095 // Use ArrayList to maintain ResultSet sequence 096 ArrayList list = new ArrayList(); 097 098 // Scroll to each record, make map of row, add to list 099 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