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    }