001 package net.sf.persism; 002 003 import net.sf.persism.annotations.QueryResult; 004 005 import java.io.*; 006 import java.lang.reflect.InvocationTargetException; 007 import java.math.BigDecimal; 008 import java.sql.*; 009 import java.sql.ResultSet; 010 import java.text.DateFormat; 011 import java.text.ParseException; 012 import java.text.SimpleDateFormat; 013 import java.util.*; 014 import java.util.Date; 015 016 /** 017 * Performs read operations from the database. 018 * 019 * @author Dan Howard 020 * @since 9/8/11 6:07 AM 021 */ 022 public final class Query { 023 024 private static final Log log = Log.getLogger(Query.class); 025 026 private Connection connection; 027 028 // JUNIT 029 MetaData metaData; 030 031 public Query(Connection connection) { 032 this.connection = connection; 033 init(connection); 034 } 035 036 private void init(Connection connection) { 037 038 // place any DB specific properties here. 039 try { 040 metaData = MetaData.getInstance(connection); 041 } catch (SQLException e) { 042 throw new PersismException(e); 043 } 044 } 045 046 /** 047 * Read a list of objects of the specified class using the specified SQL query and parameters. 048 * The type of the list can be Data Objects or native Java Objects. 049 * 050 * @param objectClass class of objects to return. 051 * @param sql query string to execute. 052 * @param parameters parameters to the query. 053 * @param <T> 054 * @return a list of objects of the specified class using the specified SQL query and parameters. 055 * @throws PersismException 056 */ 057 public <T> List<T> readList(Class<T> objectClass, String sql, Object... parameters) throws PersismException { 058 List<T> list = new ArrayList<T>(32); 059 060 Result result = new Result(); 061 062 // If we know this type it means it's a primitive type. Not a DAO so we use a different rule to read those 063 boolean readPrimitive = Types.getType(objectClass) != null; 064 065 if (!readPrimitive && objectClass.getAnnotation(QueryResult.class) == null) { 066 metaData.getTableColumns(objectClass, connection); // TODO Make sure columns are initialized properly if this is a table WHY? 067 } 068 069 try { 070 071 exec(result, sql, parameters); 072 073 while (result.rs.next()) { 074 075 if (readPrimitive) { 076 list.add((T) readPrimitive(result.rs, 1, objectClass)); 077 } else { 078 T t = objectClass.newInstance(); 079 t = (T) readObject(t, result.rs); 080 list.add(t); 081 } 082 } 083 084 } catch (IllegalAccessException e) { 085 throw new PersismException(e); 086 } catch (InstantiationException e) { 087 throw new PersismException(e); 088 } catch (InvocationTargetException e) { 089 throw new PersismException(e); 090 } catch (SQLException e) { 091 // todo check transaction and rollback. Even though it's a query a transaction may exist. SEE OTHER PLACES WHERE THIS COULD OCCUR 092 throw new PersismException(e); 093 } catch (IOException e) { 094 throw new PersismException(e); 095 } finally { 096 Util.cleanup(result.st, result.rs); 097 } 098 099 return list; 100 } 101 102 /** 103 * Read an object from the database by it's primary key. 104 * You should instantiate the object and set the primary key properties before calling this method. 105 * 106 * @param object Data object to read from the database. 107 * @return true if the object was found by the primary key. 108 * @throws PersismException if something goes wrong. 109 */ 110 public boolean read(Object object) throws PersismException { 111 112 Class objectClass = object.getClass(); 113 114 // If we know this type it means it's a primitive type. This method cannot be used for primitives 115 boolean readPrimitive = Types.getType(objectClass) != null; 116 if (readPrimitive) { 117 throw new PersismException("Cannot read a primitive type object with this method."); 118 } 119 120 List<String> primaryKeys = metaData.getPrimaryKeys(objectClass, connection); 121 if (primaryKeys.size() == 0) { 122 throw new PersismException("Cannot perform readObjectByPrimary. " + metaData.getTableName(objectClass) + " has no primary keys."); 123 } 124 125 Map<String, PropertyInfo> properties = metaData.getTableColumns(object.getClass(), connection); 126 List<Object> params = new ArrayList<Object>(primaryKeys.size()); 127 128 Result result = new Result(); 129 try { 130 for (String column : primaryKeys) { 131 PropertyInfo propertyInfo = properties.get(column); 132 params.add(propertyInfo.getter.invoke(object)); 133 } 134 135 exec(result, metaData.getSelectStatement(object, connection), params.toArray()); 136 137 if (result.rs.next()) { 138 readObject(object, result.rs); 139 return true; 140 } 141 return false; 142 143 } catch (IllegalAccessException e) { 144 throw new PersismException(e); 145 } catch (InvocationTargetException e) { 146 throw new PersismException(e); 147 } catch (SQLException e) { 148 throw new PersismException(e); 149 } catch (IOException e) { 150 throw new PersismException(e); 151 } finally { 152 Util.cleanup(result.st, result.rs); 153 } 154 } 155 156 /** 157 * Reads an object of the specified type from the database. The type can a be Data Object or a native Java Object. 158 * 159 * @param objectClass Type of returned value 160 * @param sql query - this would usually be a select OR a select of a single column if the type is a primitive. 161 * If this is a primitive type then this method will only look at the 1st column in the result. 162 * @param parameters parameters to the query. 163 * @param <T> 164 * @return value read from the database 165 * @throws PersismException if something funny? 166 */ 167 public <T> T read(Class<T> objectClass, String sql, Object... parameters) throws PersismException { 168 169 // If we know this type it means it's a primitive type. Not a DAO so we use a different rule to read those 170 boolean readPrimitive = Types.getType(objectClass) != null; 171 172 if (!readPrimitive && objectClass.getAnnotation(QueryResult.class) == null) { 173 // Make sure columns are initialized properly if this is a table todo why? 174 metaData.getTableColumns(objectClass, connection); 175 } 176 177 Result result = new Result(); 178 try { 179 180 exec(result, sql, parameters); 181 182 if (result.rs.next()) { 183 184 if (readPrimitive) { 185 return (T) readPrimitive(result.rs, 1, objectClass); 186 187 } else { 188 T t = objectClass.newInstance(); 189 readObject(t, result.rs); 190 return t; 191 } 192 } 193 194 return null; 195 196 } catch (IllegalAccessException e) { 197 throw new PersismException(e); 198 199 } catch (InvocationTargetException e) { 200 throw new PersismException(e); 201 202 } catch (SQLException e) { 203 throw new PersismException(e); 204 205 } catch (InstantiationException e) { 206 throw new PersismException(e); 207 208 } catch (IOException e) { 209 throw new PersismException(e); 210 211 } finally { 212 Util.cleanup(result.st, result.rs); 213 } 214 } 215 216 private <T> T readObject(Object object, ResultSet rs) throws IllegalAccessException, SQLException, InvocationTargetException, IOException { 217 218 Class objectClass = object.getClass(); 219 // We should never call this method with a primitive type. 220 assert Types.getType(objectClass) == null; 221 222 Map<String, PropertyInfo> properties; 223 if (objectClass.getAnnotation(QueryResult.class) == null) { 224 properties = metaData.getTableColumns(objectClass, connection); 225 } else { 226 properties = metaData.getQueryColumns(objectClass, rs); 227 } 228 229 // Test if all properties have column mapping and throw PersismException if not 230 // This block verifies that the object is fully initialized. 231 // Any properties not marked by NoColumn should have been set (or if they have a getter only) 232 // If not throw a PersismException 233 Collection<PropertyInfo> allProperties = MetaData.getPropertyInfo(objectClass); 234 if (properties.values().size() < allProperties.size()) { 235 236 237 Set<PropertyInfo> missing = new HashSet<PropertyInfo>(allProperties.size()); 238 missing.addAll(allProperties); 239 missing.removeAll(properties.values()); 240 241 StringBuilder sb = new StringBuilder(); 242 String sep = ""; 243 for (PropertyInfo prop : missing) { 244 sb.append(sep).append(prop.propertyName); 245 sep = ","; 246 } 247 248 throw new PersismException("Object " + objectClass + " was not properly initialized. Some properties not found in the queried columns (" + sb + ")."); 249 } 250 251 252 ResultSetMetaData rsmd = rs.getMetaData(); 253 int columnCount = rsmd.getColumnCount(); 254 List<String> foundColumns = new ArrayList<String>(columnCount); 255 256 for (int j = 1; j <= columnCount; j++) { 257 258 String columnName = rsmd.getColumnLabel(j); 259 PropertyInfo columnProperty = properties.get(columnName); 260 261 if (columnProperty != null) { 262 Class getterType = columnProperty.getter.getReturnType(); 263 264 Object value = readPrimitive(rs, j, getterType); 265 266 foundColumns.add(columnName); 267 268 if (value != null) { 269 try { 270 columnProperty.setter.invoke(object, value); 271 } catch (IllegalArgumentException e) { 272 String msg = e.getMessage() + "Object " + objectClass + ". Column: " + columnName + " Type of property: " + getterType + " - Type read: " + value.getClass() + " VALUE: " + value; 273 throw new PersismException(msg, e); 274 } 275 276 } 277 } 278 } 279 280 if (foundColumns.size() < properties.keySet().size()) { 281 282 Set<String> missing = new HashSet<String>(columnCount); 283 missing.addAll(properties.keySet()); 284 missing.removeAll(foundColumns); 285 286 throw new PersismException("Object " + objectClass + " was not properly initialized. Some properties not found in the queried columns. : " + missing); 287 } 288 289 if (object instanceof Persistable) { 290 // Save this object's initial state to later detect changed properties 291 ((Persistable) object).saveReadState(); 292 } 293 294 return (T) object; 295 296 } 297 298 private Object readPrimitive(ResultSet rs, int column, Class returnType) throws SQLException, IOException { 299 ResultSetMetaData resultSetMetaData = rs.getMetaData(); 300 int sqlColumnType = resultSetMetaData.getColumnType(column); 301 Types columnType = Types.convert(sqlColumnType); // note this could be null if we can't match a type 302 String columnName = resultSetMetaData.getColumnLabel(column); 303 304 Object value; 305 if (columnType != null) { 306 switch (columnType) { 307 308 case TimestampType: 309 // work around to Oracle reading a oracle.sql.TIMESTAMP class with getObject 310 value = rs.getTimestamp(column); 311 break; 312 case ClobType: 313 value = rs.getClob(column); 314 InputStream in = ((Clob) value).getAsciiStream(); 315 StringWriter write = new StringWriter(); 316 317 int c = -1; 318 while ((c = in.read()) != -1) { 319 write.write(c); 320 } 321 write.flush(); 322 value = write.toString(); 323 break; 324 case BlobType: 325 // todo BlobType 326 case InputStreamType: 327 // todo InputStreamType 328 case ReaderType: 329 // todo ReaderType 330 case EnumType: 331 // todo EnumType? 332 default: 333 value = rs.getObject(column); 334 } 335 336 } else { 337 log.warn("Column type not known for SQL type " + sqlColumnType); 338 value = rs.getObject(column); 339 } 340 341 // If value is null or column type is unknown - no need to try to convert anything. 342 if (value != null && columnType != null) { 343 344 Types valueType = Types.getType(value.getClass()); 345 346 // try to convert or cast the value to the proper type. 347 // todo do code coverage for each specific type 348 switch (valueType) { 349 350 case booleanType: 351 case BooleanType: 352 break; 353 354 case byteType: 355 case ByteType: 356 case shortType: 357 case ShortType: 358 case integerType: 359 case IntegerType: 360 // int to bool 361 if (returnType == Boolean.class || returnType == boolean.class) { 362 value = (Integer.valueOf("" + value) == 0) ? false : true; 363 } 364 break; 365 366 case longType: 367 case LongType: 368 // long to date 369 if (returnType.isAssignableFrom(java.util.Date.class) || returnType.isAssignableFrom(java.sql.Date.class)) { 370 long lval = Long.valueOf("" + value); 371 372 if (returnType.equals(java.sql.Date.class)) { 373 value = new java.sql.Date(lval); 374 } else { 375 value = new java.util.Date(lval); 376 } 377 } else if (returnType == Integer.class || returnType == int.class) { 378 log.warn("Possible overflow column " + columnName + " - Property is INT and column value is LONG"); 379 value = Integer.parseInt("" + value); 380 } 381 382 break; 383 384 case floatType: 385 case FloatType: 386 break; 387 388 case doubleType: 389 case DoubleType: 390 // float or doubles to BigDecimal 391 if (returnType == BigDecimal.class) { 392 value = new BigDecimal("" + value); 393 } else if (returnType == Float.class || returnType == float.class) { 394 // todo add tests for this 395 log.warn("Possible overflow column " + columnName + " - Property is FLOAT and column value is DOUBLE"); 396 value = Float.parseFloat("" + value); 397 } else if (returnType == Integer.class || returnType == int.class) { 398 log.warn("Possible overflow column " + columnName + " - Property is INT and column value is DOUBLE"); 399 String val = "" + value; 400 if (val.contains(".")) { 401 val = val.substring(0, val.indexOf(".")); 402 } 403 value = Integer.parseInt(val); 404 } 405 break; 406 407 case BigDecimalType: 408 // mostly oracle 409 if (returnType == Float.class || returnType == float.class) { 410 value = ((BigDecimal) value).floatValue(); 411 } else if (returnType == Double.class || returnType == double.class) { 412 value = ((BigDecimal) value).doubleValue(); 413 } else if (returnType == Long.class || returnType == long.class) { 414 value = ((BigDecimal) value).longValue(); 415 } else if (returnType == Integer.class || returnType == int.class) { 416 value = ((BigDecimal) value).intValue(); 417 } else if (returnType == Boolean.class || returnType == boolean.class) { 418 value = ((BigDecimal) value).intValue() == 1; 419 } 420 break; 421 422 case StringType: 423 424 // Read a string but we want a date 425 if (returnType.isAssignableFrom(java.util.Date.class) || returnType.isAssignableFrom(java.sql.Date.class)) { 426 // This condition occurs in SQLite when you have a datetime with default annotated 427 // the format returned is 2012-06-02 19:59:49 428 Date dval = null; 429 try { 430 // Used for SQLite returning dates as Strings under some conditions 431 DateFormat df = new SimpleDateFormat("yyyy-MM-DD hh:mm:ss"); 432 dval = df.parse("" + value); 433 } catch (ParseException e) { 434 String msg = e.getMessage() + ". Column: " + columnName + " Type of property: " + returnType + " - Type read: " + value.getClass() + " VALUE: " + value; 435 throw new PersismException(msg, e); 436 } 437 438 if (returnType.equals(java.sql.Date.class)) { 439 value = new java.sql.Date(dval.getTime()); 440 } else { 441 value = dval; 442 } 443 444 } else if (returnType.isEnum()) { 445 // If this is an enum do a case insensitive comparison 446 Object[] enumConstants = returnType.getEnumConstants(); 447 for (Object element : enumConstants) { 448 if (("" + value).equalsIgnoreCase(element.toString())) { 449 value = element; 450 break; 451 } 452 } 453 } 454 455 break; 456 457 case characterType: 458 case CharacterType: 459 break; 460 461 case UtilDateType: 462 break; 463 case SQLDateType: 464 break; 465 case TimeType: 466 break; 467 468 case TimestampType: 469 if (returnType.isAssignableFrom(Date.class) || returnType.isAssignableFrom(java.sql.Date.class)) { 470 if (returnType.equals(java.sql.Date.class)) { 471 value = new java.sql.Date(((Timestamp) value).getTime()); 472 } else { 473 value = new Date(((Timestamp) value).getTime()); 474 } 475 } else { 476 value = ((Timestamp) value).getTime(); 477 } 478 479 break; 480 481 case byteArrayType: 482 break; 483 case ByteArrayType: 484 break; 485 case charArrayType: 486 break; 487 case CharArrayType: 488 break; 489 case ClobType: 490 // Convert to string 491 if (value != null) { 492 value = "" + value; 493 } 494 break; 495 case BlobType: 496 break; 497 case InputStreamType: 498 break; 499 case ReaderType: 500 break; 501 case EnumType: 502 break; 503 } 504 } 505 506 return value; 507 508 /* 509 } else if (type == java.io.InputStream.class) { 510 value = resultSet.getBinaryStream(column); 511 } else if (type == java.io.Reader.class) { 512 value = resultSet.getCharacterStream(column); 513 } else if (type == java.sql.Clob.class) { 514 value = resultSet.getClob(column); 515 } else if (type == java.sql.Blob.class) { 516 value = resultSet.getBlob(column); 517 518 */ 519 } 520 521 private Object XreadPrimitive(ResultSet rs, int column, Class returnType) throws SQLException { 522 // todo in H2 523 // CREATE TABLE X ID INT IDENTITY PRIMARY KEY -- creates INT 524 // CREATE TABLE X ID IDENTITY PRIMARY KEY -- creates LONG - can cause problems if data object uses int 525 526 ResultSetMetaData resultSetMetaData = rs.getMetaData(); 527 Types columnType = Types.convert(resultSetMetaData.getColumnType(column)); // note this could be null if we can't match a type 528 String columnName = resultSetMetaData.getColumnLabel(column); 529 530 Object value; 531 if (Types.TimestampType == columnType) { 532 // work around to Oracle reading a oracle.sql.TIMESTAMP class with getObject 533 value = rs.getTimestamp(column); 534 } else { 535 value = rs.getObject(column); 536 } 537 538 if (value != null) { 539 540 Types valueType = Types.getType(value.getClass()); 541 542 //if (log.isDebugEnabled()) { 543 log.debug("COLUMN: " + columnName + " value " + value + " type: " + valueType + " COLUMN TYPE: " + columnType + " return type: " + returnType); 544 // } 545 546 // Convert value to native java type 547 if (value instanceof Timestamp) { 548 // done 549 if (!returnType.isAssignableFrom(java.util.Date.class) && !returnType.isAssignableFrom(java.sql.Date.class)) { 550 value = ((Timestamp) value).getTime(); 551 } else { 552 if (returnType.equals(java.sql.Date.class)) { 553 value = new java.sql.Date(((Timestamp) value).getTime()); 554 } else { 555 value = new java.util.Date(((Timestamp) value).getTime()); 556 } 557 } 558 559 } else if (value instanceof Long && 560 (returnType.isAssignableFrom(java.util.Date.class) || returnType.isAssignableFrom(java.sql.Date.class))) { 561 // done 562 563 long lval = Long.valueOf("" + value); 564 565 if (returnType.equals(java.sql.Date.class)) { 566 value = new java.sql.Date(lval); 567 } else { 568 value = new java.util.Date(lval); 569 } 570 } else if (value instanceof String && 571 (returnType.isAssignableFrom(java.util.Date.class) || returnType.isAssignableFrom(java.sql.Date.class))) { 572 // done 573 // This condition occurs in SQLite when you have a datetime with default annotated 574 // the format returned is 2012-06-02 19:59:49 575 Date dval = null; 576 try { 577 // Used for SQLite returning dates as Strings under some conditions 578 DateFormat df = new SimpleDateFormat("yyyy-MM-DD hh:mm:ss"); 579 dval = df.parse("" + value); 580 } catch (ParseException e) { 581 String msg = e.getMessage() + ". Column: " + columnName + " Type of property: " + returnType + " - Type read: " + value.getClass() + " VALUE: " + value; 582 throw new PersismException(msg, e); 583 } 584 585 if (returnType.equals(java.sql.Date.class)) { 586 value = new java.sql.Date(dval.getTime()); 587 } else { 588 value = dval; 589 } 590 591 } else if (value instanceof String && returnType.isEnum()) { 592 // done 593 // If this is an enum do a case insensitive comparison 594 Object[] enumConstants = returnType.getEnumConstants(); 595 for (Object element : enumConstants) { 596 if (("" + value).equalsIgnoreCase(element.toString())) { 597 value = element; 598 break; 599 } 600 } 601 } else if (value instanceof BigDecimal) { // mostly oracle. 602 // done 603 if (returnType == Float.class || returnType == float.class) { 604 value = ((BigDecimal) value).floatValue(); 605 } else if (returnType == Double.class || returnType == double.class) { 606 value = ((BigDecimal) value).doubleValue(); 607 } else if (returnType == Long.class || returnType == long.class) { 608 value = ((BigDecimal) value).longValue(); 609 } else if (returnType == Integer.class || returnType == int.class) { 610 value = ((BigDecimal) value).intValue(); 611 } else if (returnType == Boolean.class || returnType == boolean.class) { 612 value = ((BigDecimal) value).intValue() == 1; 613 } 614 615 } else if (value instanceof Integer && (returnType == Boolean.class || returnType == boolean.class)) { 616 // done 617 value = (Integer.valueOf("" + value) == 0) ? false : true; 618 619 } else if (value instanceof Float && returnType == BigDecimal.class) { 620 // done 621 value = new BigDecimal("" + value); 622 } 623 } 624 625 return value; 626 } 627 628 629 private Result exec(Result result, String sql, Object... parameters) throws SQLException { 630 631 if (sql.toLowerCase().contains("select ")) { 632 result.st = connection.prepareStatement(sql); 633 634 PreparedStatement pst = (PreparedStatement) result.st; 635 int n = 1; 636 for (Object o : parameters) { 637 pst.setObject(n, o); 638 n++; 639 } 640 result.rs = pst.executeQuery(); 641 } else { 642 // todo unit tests need to cover this. 643 result.st = connection.prepareCall("{call " + sql + "}"); 644 645 CallableStatement cst = (CallableStatement) result.st; 646 647 int n = 1; 648 for (Object o : parameters) { 649 cst.setObject(n, o); 650 n++; 651 } 652 cst.executeQuery(); 653 } 654 return result; 655 } 656 657 }