001    package net.sf.persism;
002    
003    import java.lang.reflect.Method;
004    import java.sql.*;
005    import java.util.ArrayList;
006    import java.util.Date;
007    import java.util.List;
008    import java.util.Map;
009    
010    /**
011     * The Command object is used to perform updates in the databases with data objects.
012     *
013     * @author Dan Howard
014     * @since 4/4/12 6:42 PM
015     */
016    public class Command {
017    
018        private static final Log log = Log.getLogger(Command.class);
019    
020    
021        private Connection connection;
022    
023        private MetaData metaData;
024    
025        private Query query = null; // lazy load it - we may not need this object for all cases.
026    
027        public Command(Connection connection) {
028            this.connection = connection;
029            init(connection);
030        }
031    
032        private void init(Connection connection) {
033    
034            // place any DB specific properties here.
035            try {
036                metaData = MetaData.getInstance(connection);
037            } catch (SQLException e) {
038                throw new PersismException(e);
039            }
040        }
041    
042        /**
043         * Updates the data object in the database.
044         *
045         * @param object data object to update.
046         * @return usually 1 to indicate rows changed via JDBC.
047         * @throws PersismException Indicating the upcoming robot uprising.
048         */
049        public int update(Object object) throws PersismException {
050            List<String> primaryKeys = metaData.getPrimaryKeys(object.getClass(), connection);
051            if (primaryKeys.size() == 0) {
052                throw new PersismException("Cannot perform update. " + metaData.getTableName(object.getClass()) + " has no primary keys.");
053            }
054    
055            PreparedStatement st = null;
056            try {
057    
058                String updateStatement = metaData.getUpdateStatement(object, connection);
059                if (updateStatement == null || updateStatement.trim().length() == 0) {
060                    log.warn("No properties changed. No update required for Object: " + object + " class: " + object.getClass().getName());
061                    return 0;
062                }
063    
064                st = connection.prepareStatement(updateStatement);
065    
066                // These keys should always be in sorted order.
067                Map<String, PropertyInfo> allProperties = metaData.getTableColumns(object.getClass(), connection);
068                Map<String, PropertyInfo> changedProperties;
069                if (object instanceof Persistable) {
070                    changedProperties = metaData.getChangedColumns((Persistable) object, connection);
071                } else {
072                    changedProperties = allProperties;
073                }
074    
075                int param = 1;
076                for (String column : changedProperties.keySet()) {
077                    if (!primaryKeys.contains(column)) {
078                        Object value = allProperties.get(column).getter.invoke(object);
079    
080                        if (value != null) {
081    //                        value = Util.convert(value, columns.get(column).columnType);
082    
083                            if (value != null && value.getClass().isEnum()) {
084                                value = "" + value; // convert enum to string.
085                            }
086    
087                            if (value instanceof java.util.Date || value instanceof java.sql.Date) {
088    
089                                Date dt = (Date) value;
090                                value = new Timestamp(dt.getTime());
091                            }
092    
093    
094                            if (value instanceof String) {
095                                // check width
096                                PropertyInfo propertyInfo = allProperties.get(column);
097                                String str = (String) value;
098                                if (str.length() > propertyInfo.length) {
099                                    str = str.substring(0, propertyInfo.length);
100                                    // todo Should Persism strict should throw this as an exception?
101                                    log.warn("TRUNCATION with Column: " + column + " for table: " + metaData.getTableName(object.getClass()) + ". Old value: \"" + value + "\" New value: \"" + str + "\"");
102                                    value = str;
103                                }
104                            }
105                        }
106                        st.setObject(param++, value);
107                    }
108                }
109    
110                for (String column : primaryKeys) {
111                    st.setObject(param++, allProperties.get(column).getter.invoke(object));
112                }
113                int ret = st.executeUpdate();
114                return ret;
115            } catch (Exception e) {
116                try {
117                    if (connection != null && !connection.getAutoCommit()) {
118                        connection.rollback();
119                    }
120                } catch (SQLException e1) {
121                    log.error(e1.getMessage(), e1);
122                }
123                throw new PersismException(e);
124    
125            } finally {
126                Util.cleanup(st, null);
127            }
128        }
129    
130        /**
131         * Inserts the data object in the database.
132         *
133         * @param object the data object to insert.
134         * @return usually 1 to indicate rows changed via JDBC.
135         * @throws PersismException When planet of the apes starts happening.
136         */
137        public int insert(Object object) throws PersismException {
138            String insertStatement = metaData.getInsertStatement(object, connection);
139    
140            PreparedStatement st = null;
141            java.sql.ResultSet rs = null;
142    
143            try {
144                // These keys should always be in sorted order.
145                Map<String, PropertyInfo> properties = metaData.getTableColumns(object.getClass(), connection);
146                Map<String, ColumnInfo> columns = metaData.getColumns(object.getClass(), connection);
147    
148                List<String> generatedKeys = new ArrayList<String>(4);
149                for (ColumnInfo column : columns.values()) {
150                    if (column.generated) {
151                        generatedKeys.add(column.columnName);
152                    }
153                }
154    
155                if (generatedKeys.size() > 0) {
156                    String[] keyArray = generatedKeys.toArray(new String[0]);
157                    st = connection.prepareStatement(insertStatement, keyArray);
158                } else {
159                    st = connection.prepareStatement(insertStatement);
160                }
161    
162                boolean tableHasDefaultColumnValues = false;
163    
164                int param = 1;
165                for (ColumnInfo column : columns.values()) {
166    
167                    PropertyInfo propertyInfo = properties.get(column.columnName);
168    
169                    // todo is propertyInfo null ever? I don't think so. We only include columns where we know the property.
170                    if (!column.generated) {
171    
172                        // TODO This condition is repeated 3 times. We need to rearrange this code.
173                        // See MetaData getInsertStatement - Maybe we should return a new Object type for InsertStatement
174                        if (column.hasDefault) {
175                            // Do not include if this column has a default and no value has been
176                            // set on it's associated property.
177                            if (properties.get(column.columnName).getter.invoke(object) == null) {
178                                tableHasDefaultColumnValues = true;
179                                continue;
180                            }
181                        }
182    
183    
184                        Object value = propertyInfo.getter.invoke(object);
185                        if (log.isDebugEnabled()) {
186                            log.debug("param " + param + " value: " + value);
187                        }
188    
189                        if (value != null) {
190    
191    //                        value = Util.convert(value, column.columnType);             // todo null with Enum?
192    
193                            if (value != null && value.getClass().isEnum()) {
194                                value = "" + value; // convert enum to string.
195                            }
196    
197                            if (value instanceof java.util.Date || value instanceof java.sql.Date) {
198    
199                                Date dt = (Date) value;
200                                value = new Timestamp(dt.getTime());
201                            }
202    
203    
204                            if (value instanceof String) {
205                                // check width
206                                String str = (String) value;
207                                if (str.length() > propertyInfo.length) {
208                                    // todo should Persism strict throw this as an exception?
209                                    str = str.substring(0, propertyInfo.length);
210                                    log.warn("TRUNCATION with Column: " + column + " for table: " + metaData.getTableName(object.getClass()) + ". Old value: \"" + value + "\" New value: \"" + str + "\"");
211                                    value = str;
212                                }
213                            }
214                        }
215    
216                        st.setObject(param++, value);
217                    }
218                }
219    
220                // https://forums.oracle.com/forums/thread.jspa?threadID=879222
221                // http://download.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/statement.html
222                //int ret = st.executeUpdate(insertStatement, Statement.RETURN_GENERATED_KEYS);
223                int ret = st.executeUpdate();
224                if (log.isDebugEnabled()) {
225                    log.debug("insert ret: " + ret);
226                }
227                if (generatedKeys.size() > 0) {
228                    rs = st.getGeneratedKeys();
229                }
230    
231                // TODO for now we can only support a single auto inc - need to test out other possible generated columns
232                for (String column : generatedKeys) {
233                    if (rs.next()) {
234    
235                        Method setter = properties.get(column).setter;
236    
237                        if (setter != null) {
238                            // todo do we really need to type these? Maybe if the DB uses a GUID?
239                            Object value = metaData.getTypedValue(setter.getParameterTypes()[0], rs, 1);
240                            if (log.isDebugEnabled()) {
241                                log.debug(column + " generated " + value);
242                                log.debug(setter);
243                            }
244                            setter.invoke(object, value);
245    
246                        } else {
247                            log.warn("no setter found for column " + column);
248                        }
249                    }
250                }
251    
252                if (tableHasDefaultColumnValues) {
253                    if (query == null) {
254                        query = new Query(connection);
255                    }
256                    // Read the full object back to update any properties which had defaults
257                    query.read(object);
258                }
259    
260                return ret;
261            } catch (Exception e) {
262                try {
263                    if (connection != null && !connection.getAutoCommit()) {
264                        connection.rollback();
265                    }
266                } catch (SQLException e1) {
267                    log.error(e1.getMessage(), e1);
268                }
269    
270                throw new PersismException(e);
271            } finally {
272                Util.cleanup(st, rs);
273            }
274        }
275    
276    
277        /**
278         * Deletes the data object object from the database.
279         *
280         * @param object data object to delete
281         * @return usually 1 to indicate rows changed via JDBC.
282         * @throws PersismException Perhaps when asteroid 1999 RQ36 hits us?
283         */
284        public int delete(Object object) throws PersismException {
285    
286            List<String> primaryKeys = metaData.getPrimaryKeys(object.getClass(), connection);
287            if (primaryKeys.size() == 0) {
288                throw new PersismException("Cannot perform delete. " + metaData.getTableName(object.getClass()) + " has no primary keys.");
289            }
290    
291    
292            PreparedStatement st = null;
293            try {
294                String deleteStatement = metaData.getDeleteStatement(object, connection);
295                st = connection.prepareStatement(deleteStatement);
296    
297                // These keys should always be in sorted order.
298                Map<String, PropertyInfo> columns = metaData.getTableColumns(object.getClass(), connection);
299    
300                int param = 1;
301                for (String column : primaryKeys) {
302                    st.setObject(param++, columns.get(column).getter.invoke(object));
303                }
304                int ret = st.executeUpdate();
305                return ret;
306            } catch (Exception e) {
307                try {
308                    if (connection != null && !connection.getAutoCommit()) {
309                        connection.rollback();
310                    }
311                } catch (SQLException e1) {
312                    log.error(e1.getMessage(), e1);
313                }
314                throw new PersismException(e);
315    
316            } finally {
317                Util.cleanup(st, null);
318            }
319        }
320    
321        /**
322         * Execute an arbitrary SQL statement.
323         *
324         * @param sql
325         * @param parameters
326         */
327        public void executeSQL(String sql, Object... parameters) {
328    
329            Statement st = null;
330    
331            try {
332    
333                if (parameters.length == 0) {
334                    st = connection.createStatement();
335                    st.execute(sql);
336                } else {
337                    st = connection.prepareStatement(sql);
338    
339                    PreparedStatement pst = (PreparedStatement) st;
340                    int n = 1;
341                    for (Object o : parameters) {
342                        pst.setObject(n, o);
343                        n++;
344                    }
345                    pst.execute();
346                }
347    
348            } catch (SQLException e) {
349                throw new PersismException(e);
350            } finally {
351                Util.cleanup(st, null);
352            }
353        }
354    
355    
356        // Not production only for testing for now.
357        protected final java.sql.ResultSet executeQuery(String sql, Object... parameters) {
358    
359            Statement st = null;
360            java.sql.ResultSet rs = null;
361    
362            ResultSet result = new ResultSet();
363    
364            try {
365    
366                if (parameters.length == 0) {
367                    st = connection.createStatement();
368                    rs = st.executeQuery(sql);
369                } else {
370                    st = connection.prepareStatement(sql);
371    
372                    PreparedStatement pst = (PreparedStatement) st;
373                    int n = 1;
374                    for (Object o : parameters) {
375                        pst.setObject(n, o);
376                        n++;
377                    }
378                    rs = pst.executeQuery();
379                }
380    
381                while (rs.next()) {
382                    result.add(rs);
383                }
384    
385    
386            } catch (SQLException e) {
387                throw new PersismException(e);
388            } finally {
389                Util.cleanup(st, rs);
390            }
391            return result;
392        }
393    }