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 }