Java tutorial
/* * Copyright (c) 2008-2011 Simon Ritchie. * All rights reserved. * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Lesser General Public License as published * by the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, but * WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. * See the GNU Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public License * along with this program. If not, see http://www.gnu.org/licenses/>. ******************************************************************************/ package org.rimudb; import java.lang.reflect.*; import java.sql.*; import java.util.*; import org.apache.commons.logging.*; import org.rimudb.configuration.*; import org.rimudb.exception.*; import org.rimudb.sql.*; import org.rimudb.statistics.*; import org.rimudb.types.*; /** * This class represents a table in the database. * * @author Simon Ritchie * */ public class Table { private static Log log = LogFactory.getLog(Table.class); private Database database = null; private ISQLAdapter sqlAdapter; private int maximumBatchSize = 0; private RecordBinder recordBinder = null; private TableMetaData tableMetaData; private Class<? extends DataObject> dataObjectClass; private boolean usingLockingHints = false; private OptimisticLocking optimisticLocking = null; private String tableName; private SQLStatementCache sqlStatementCache = new SQLStatementCache(); /** * Construct a Table. */ public Table() { } /** * Return the Data Object class. * @return Class */ public Class<? extends DataObject> getDataObjectClass() { return dataObjectClass; } /** Return the SQL used to create the table in the database. The url * parameter is used to allow a different SQL string to be returned * depending on the database. * * @return String * @param url String * @param ignoreIfExists */ public String getCreateTableSQL(String url, boolean ignoreIfExists, boolean prettyFormat) throws RimuDBException { return sqlAdapter.getCreateTableSQL(getTableName(), tableMetaData, ignoreIfExists, prettyFormat); } private int getPrimaryKeyCount() { return tableMetaData.getPKColumnCount(); } /** * Instantiate a Data Object. * * @param database Database * @param record * @return DataObject * @throws RimuDBException */ public DataObject createDataObject(Database database, Record record) throws RimuDBException { try { Constructor constructor = getDataObjectClass().getConstructor(Database.class, Record.class); return (DataObject) constructor.newInstance(database, record); } catch (Exception e) { throw new RimuDBException(e); } } /** * Create a DO instance for this table. * * @param database Database * @param lockResult LockResult * @return DataObject * @throws RimuDBException */ public DataObject createDataObject(Database database, LockResult lockResult) throws RimuDBException { try { Constructor constructor = getDataObjectClass().getConstructor(Database.class, LockResult.class); return (DataObject) constructor.newInstance(database, lockResult); } catch (Exception e) { throw new RimuDBException(e); } } /** * Create DO instance and load with values from a result set. * * @param rs ResultSet * @return DataObject * @throws RimuDBException */ public DataObject createDataObject(ResultSet rs) throws RimuDBException { // Do not use the cached binder, as this must have a guaranteed order // of columns in the ResultSet. return createDataObject(rs, false); } /** * Create an instance of the DataObject and load with values from a result set. * * The boolean useCachedBinder should only be set to true when the columns * (and sequence of columns) in the SQL exactly match those generated by * TableMetaData.getDelimitedColumnNames. This means that only methods from * within the Table class can set it to true, so the method is made private. * * @param rs ResultSet * @param useCachedBinder boolean * @return DataObject * @throws RimuDBException */ private DataObject createDataObject(ResultSet rs, boolean useCachedBinder) throws RimuDBException { Record record = new Record(getDataObjectClass()); loadRecord(record, rs, useCachedBinder); return createDataObject(database, record); } /** * Create Record instance and load with values from a result set. */ public Record createRecord(ResultSet rs) throws RimuDBException, SQLException { Record value = new Record(getDataObjectClass()); if (rs.next()) { loadRecord(value, rs, true); } else { value.setExists(false); } return value; } public void loadRecord(Record record, ResultSet rs, boolean useCachedBinder) throws RimuDBException { if (useCachedBinder) { recordBinder.bindResultSet(rs, record); } else { RecordBinder recordBinder = new RecordBinder(getTableMetaData(), getSQLAdapter()); recordBinder.bindResultSet(rs, record); } record.setExists(true); } /** * Add a record to the table. * * @param record Record */ public void add(Record record) throws RimuDBException { Connection con = null; int statID = 0; PreparedStatement stmt = null; try { con = getDatabase().getDatabaseConnection(); String sql = sqlStatementCache.getInsertSQL(); if (sql == null) { sql = sqlAdapter.getInsertStatement(tableMetaData, getTableName()); sqlStatementCache.setInsertSQL(sql); } // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = createPreparedStatement(con, sql, CrudType.CREATE); recordBinder.bindStatementForInsert(stmt, record); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); stmt.executeUpdate(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); // Mark the record as in existence record.setExists(true); // If we had auto-increment keys then update the record with their new values if (tableMetaData.hasAutoIncrementKeys()) { if (sqlAdapter.getDatabaseMetaData().getSupportsGetGeneratedKeys()) { populateGeneratedKeys(stmt, record); // If the generated key can be derived from the insert statement. } else if (sqlAdapter.isInsertIndentityStatementSupported()) { // TODO: Handle this somehow // If a separate statement needs to be executed. } else { ColumnMetaData columnMetaData = getTableMetaData().getAutoIncrementColumn(); String autoIncrementColumn = columnMetaData.getColumnName(); int columnType = columnMetaData.getColumnType(); String identitySelectSQL = sqlAdapter.getIdentitySelectSQL(getTableName(), autoIncrementColumn, columnType); // Execute the statement to get the identity value try { //fetch the generated id in a separate query PreparedStatement ps = con.prepareStatement(identitySelectSQL); try { populateQueriedKeys(ps, record); } finally { ps.close(); } } catch (SQLException sqle) { throw new RimuDBException(sqle, "Could not retrieve generated id after insert for sql: " + sql); } } } if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } } catch (SQLException e) { if (e.getSQLState() != null && e.getSQLState().startsWith("23")) { throw new ConstraintViolationException(e); } else { throw new RimuDBException(e); } } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { } stmt = null; } if (con != null) { try { con.close(); } catch (SQLException e) { } con = null; } } } /** * Delete a record using the primary key * * @param primaryWhereList WhereList * @throws RimuDBException */ public int deleteByPrimaryKey(WhereList primaryWhereList) throws RimuDBException { checkWhereListIsPrimaryKey(primaryWhereList, getPrimaryKeyCount()); return deleteAll(primaryWhereList); } /** * Delete all the records that match the WhereList * * @param whereList WhereList * @return int The number of records deleted * @throws RimuDBException */ public int deleteAll(WhereList whereList) throws RimuDBException { Connection con = null; int statID = 0; try { con = getDatabase().getDatabaseConnection(); String sql = getSQLAdapter().getDeleteStatement(tableMetaData, getTableName(), whereList); // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } PreparedStatement stmt = createPreparedStatement(con, sql, CrudType.DELETE); recordBinder.bindStatement(stmt, whereList); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); int rows = stmt.executeUpdate(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } return rows; } catch (SQLException e) { throw new RimuDBException(e); } finally { if (con != null) { try { con.close(); } catch (SQLException e) { } con = null; } } } protected PreparedStatement getDeleteStatement(Connection con, String sql, WhereList whereList) throws SQLException, RimuDBException { PreparedStatement stmt = null; // Create the prepared statement stmt = createPreparedStatement(con, sql, CrudType.DELETE); recordBinder.bindStatement(stmt, whereList); return stmt; } /** * Return a List of DO instances. * * @param whereList WhereList * @param groupBylist GroupByList * @param orderByList OrderByList * @param maxRecords int * @return List<? extends DataObject> * @throws RimuDBException */ public List<? extends DataObject> getAllDataObjects(WhereList whereList, GroupByList groupBylist, OrderByList orderByList, int maxRecords) throws RimuDBException { Connection con = null; PreparedStatement stmt = null; List<DataObject> list = null; ResultSet rs = null; int statID = 0; try { con = getDatabase().getDatabaseConnection(); // Get the SQL string String sql = getSQLAdapter().build(tableMetaData, getTableName(), whereList, groupBylist, orderByList, maxRecords, isUsingLockingHints()); // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } // Create the prepared statement stmt = createPreparedStatement(con, sql, CrudType.READ); // Set the parameters on the prepared statement if (whereList != null) { recordBinder.bindStatement(stmt, whereList); } if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); rs = stmt.executeQuery(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); list = new ArrayList<DataObject>(); while (rs.next()) { DataObject dataObject = createDataObject(rs, true); list.add(dataObject); } // Log the SQL statement if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); int statisticsThreshold = getDatabase().getDatabaseConfiguration().getLoggingThreshold(); if (StatisticCollector.getInstance().exceedsThreshold(statID, statisticsThreshold)) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } return list; } catch (SQLException e) { throw new RimuDBException(e, getClass().getName()); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { } stmt = null; } if (con != null) { try { con.close(); } catch (SQLException e) { } con = null; } } } public int getRecordCount() throws RimuDBException { return getRecordCount((WhereList) null); } /** * Return the number of records matching the WhereList. * * @param whereList WhereList * @return int Number of records * @throws RimuDBException */ public int getRecordCount(WhereList whereList) throws RimuDBException { int count = -1; Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; int statID = 0; try { con = getDatabase().getDatabaseConnection(); // Get the prepared statement String sql = getSQLAdapter().getSelectCountSQL(tableMetaData, getTableName(), whereList); // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } // Create the prepared statement stmt = createPreparedStatement(con, sql, CrudType.READ); // Set the parameters on the prepared statement if (whereList != null) { recordBinder.bindStatement(stmt, whereList); } if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); rs = stmt.executeQuery(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); if (rs.next()) { count = rs.getInt(1); } // Log the SQL statement if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); int statisticsThreshold = getDatabase().getDatabaseConfiguration().getLoggingThreshold(); if (StatisticCollector.getInstance().exceedsThreshold(statID, statisticsThreshold)) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } return count; } catch (SQLException e) { throw new RimuDBException(e, getClass().getName()); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { } stmt = null; } if (con != null) { try { con.close(); } catch (SQLException e) { } con = null; } } } /** * Read the DataObject for a primaryWhereList and Lock from the database. * * @param primaryWhereList WhereList * @param lockType Lock * @return DataObject * @throws RimuDBException */ public DataObject getDataObject(WhereList primaryWhereList, Lock lockType) throws RimuDBException { LockResult lockResult = get(primaryWhereList, lockType); if (lockResult != null && lockResult.getRecord().exists()) { return createDataObject(getDatabase(), lockResult); } else { return null; } } /** * Return the record for a primary KeyList. * @return Record * @param primaryWhereList WhereList * @throws RimuDBException */ public Record get(WhereList primaryWhereList) throws RimuDBException { LockResult lockResult = get(primaryWhereList, Lock.NONE); lockResult.close(); return lockResult.getRecord(); } /** * Return the LockResult for a primary WhereList. * @return LockResult * @param primaryWhereList WhereList * @param lockType Lock * @throws RimuDBException */ public LockResult get(WhereList primaryWhereList, Lock lockType) throws RimuDBException { Connection con = null; int statID = 0; checkWhereListIsPrimaryKey(primaryWhereList, getPrimaryKeyCount()); // Check the primaryWhereList has the correct number of keys PreparedStatement stmt = null; ResultSet rs = null; try { con = getDatabase().getDatabaseConnection(); String sql = sqlStatementCache.getSelectPK(lockType); if (sql == null) { sql = sqlAdapter.getPrimaryKeySelectStatement(lockType, tableMetaData, getTableName()); sqlStatementCache.setSelectPK(lockType, sql); } // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = createPreparedStatement(con, sql, CrudType.READ); // Set the parameters on the prepared statement recordBinder.bindStatement(stmt, primaryWhereList); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); rs = stmt.executeQuery(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); Record value = createRecord(rs); if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } // Return the lock result LockResult lockResult = new LockResult(value, con, rs, stmt, lockType); // Close the lock if there was no select for update used if (lockType == Lock.NONE) { lockResult.close(); } return lockResult; } catch (SQLException e) { throw new RimuDBException(e); } } /** * Return a prepared statement for the given SQL. */ protected PreparedStatement createPreparedStatement(Connection con, String sql, CrudType crudType) throws SQLException { if (con == null) throw new SQLException("Connection (con) is null"); if (sql == null) throw new SQLException("SQL statement string (sql) is null"); if (crudType == CrudType.CREATE && hasAutoIncrementKey() && sqlAdapter.getDatabaseMetaData().getSupportsGetGeneratedKeys()) { // If the database permits us to get the key value using getGeneratedKeys() if (sqlAdapter.getSupportsReturnGeneratedKeys()) { return con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); // Else the database requires us to pass an array of identity columns on the prepare } else { int identityColumn = getTableMetaData().getAutoIncrementColumn().getColumnNbr() + 1; int identityColumns[] = new int[] { identityColumn }; return con.prepareStatement(sql, identityColumns); } } else { return con.prepareStatement(sql); } } /** * Return true if there are auto increment keys. * @return boolean */ private boolean hasAutoIncrementKey() { return tableMetaData.hasAutoIncrementKeys(); } /** * Check the primary WhereList contains the property names that match the * primary key. Check that the value of each key is the correct data type. * * @param whereList WhereList * @param count int * @throws InvalidKeyListException */ protected void checkWhereListIsPrimaryKey(WhereList whereList, int count) throws InvalidKeyListException { if (whereList == null && count != 0) throw new InvalidKeyListException("WhereList contains 0 keys. " + count + " keys expected."); if (whereList != null && whereList.size() != count) throw new InvalidKeyListException( "WhereList contains " + whereList.size() + " keys. " + count + " keys expected."); if (whereList != null) { for (int x = 0; x < whereList.size(); x++) { String propertyName = whereList.getPropertyName(x); ColumnMetaData columnMetaData = getTableMetaData().getMetaDataByPropertyName(propertyName); if (columnMetaData == null) { throw new InvalidKeyListException("PropertyName '" + propertyName + "' in position " + x + " of the WhereList is not valid for this table."); } int sqlType = columnMetaData.getColumnType(); if (!DatabaseTypes.typeMatchesValueClass(sqlType, whereList.getValue(x))) { String jdbcTypename = DatabaseTypes.convertSQLTypeToName(sqlType); String className = whereList.getValue(x).getClass().getName(); throw new InvalidKeyListException("The value for propertyName '" + propertyName + "' in position " + x + " of the WhereList does not match the class required for this property. Property is jdbc type " + jdbcTypename + ". The value is class " + className); } if (whereList.getBooleanOperator(x) != BooleanOperator.AND) { throw new InvalidKeyListException("The boolean operator for property '" + propertyName + "' in position " + x + " of the WhereList must be AND."); } if (whereList.getOperator(x) != Operator.EQ) { throw new InvalidKeyListException( "The operator for property '" + propertyName + "' in position " + x + " of the WhereList must be EQ. It is " + whereList.getOperator(x) + "."); } } } } /** * Return the maximum batch size for this table */ protected int getMaximumBatchSize() { return maximumBatchSize; } protected void setMaximumBatchSize(int maximumBatchSize) { this.maximumBatchSize = maximumBatchSize; } /** * Return the Database this table is connected to * * @return Database */ public Database getDatabase() { return database; } /** * Set the Database for this table. * * @param database Database */ public void setDatabase(Database database) { this.database = database; } /** * Set the SQL Adapter. * * @param sqlAdapter ISQLAdapter */ public void setSQLAdpater(ISQLAdapter sqlAdapter) { this.sqlAdapter = sqlAdapter; } /** * Return the SQL Adapter. * * @return ISQLAdapter */ public ISQLAdapter getSQLAdapter() { return sqlAdapter; } public void deleteByPrimaryKeyInBatch(Session session, WhereList primaryWhereList, boolean ignoreAutoCommitBatchErrors) throws RimuDBException { checkWhereListIsPrimaryKey(primaryWhereList, getPrimaryKeyCount()); PreparedStatement stmt = null; int statID = 0; try { String sql = sqlAdapter.getDeleteStatement(tableMetaData, getTableName(), primaryWhereList); // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = session.getBatchStatement(this, Session.BATCH_DELETE); if (stmt == null) { stmt = createPreparedStatement(session.getConnection(), sql, CrudType.DELETE); session.setBatchStatement(this, stmt, Session.BATCH_DELETE); } recordBinder.bindStatement(stmt, primaryWhereList); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); stmt.addBatch(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } } catch (SQLException e) { throw new RimuDBException(e); } } public void addRecordToBatch(Session session, Record record, boolean ignoreAutoCommitBatchErrors) throws RimuDBException { PreparedStatement stmt = null; int statID = 0; try { String sql = sqlStatementCache.getInsertSQL(); if (sql == null) { sql = sqlAdapter.getInsertStatement(tableMetaData, getTableName()); sqlStatementCache.setInsertSQL(sql); } // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = session.getBatchStatement(this, Session.BATCH_INSERT); if (stmt == null) { stmt = createPreparedStatement(session.getConnection(), sql, CrudType.CREATE); session.setBatchStatement(this, stmt, Session.BATCH_INSERT); } recordBinder.bindStatementForInsert(stmt, record); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); stmt.addBatch(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } } catch (SQLException e) { throw new RimuDBException(e); } } /** * Populate the Record with the generated keys. * * @param stmt PreparedStatement * @param record Record * @throws SQLException * @throws RimuDBException */ protected void populateGeneratedKeys(PreparedStatement stmt, Record record) throws SQLException, RimuDBException { populateGeneratedKeys(stmt, new Record[] { record }); } /** * Populate the Records with the generated keys. * * @param stmt PreparedStatement * @param records Record[] * @throws SQLException * @throws RimuDBException */ protected void populateGeneratedKeys(PreparedStatement stmt, Record[] records) throws SQLException, RimuDBException { ResultSet rs = null; try { rs = stmt.getGeneratedKeys(); recordBinder.bindResultSetToAutoIncrementKeys(rs, records); } finally { if (rs != null) { rs.close(); } } } /** * Populate the Record with the keys generated from a query. * * @param stmt PreparedStatement * @param record Record * @throws SQLException * @throws RimuDBException */ protected void populateQueriedKeys(PreparedStatement stmt, Record record) throws SQLException, RimuDBException { ResultSet rs = null; try { rs = stmt.executeQuery(); recordBinder.bindResultSetToAutoIncrementKeys(rs, new Record[] { record }); } finally { if (rs != null) { rs.close(); } } } /** * Return the TableMetaData. * * @return TableMetaData */ public TableMetaData getTableMetaData() { return tableMetaData; } /** * Set the TableMetaData. * * @param tableMetaData TableMetaData */ protected void setTableMetaData(TableMetaData tableMetaData) { this.tableMetaData = tableMetaData; } /** * Delete records that match a WhereList within a Session. Returns the number * of records deleted. * * @param session Session * @param whereList WhereList * @return int */ public int deleteAllUsingSession(Session session, WhereList whereList) throws RimuDBException { PreparedStatement stmt = null; int statID = 0; try { String sql = sqlStatementCache.getDeleteSQL(whereList); if (sql == null) { sql = sqlAdapter.getDeleteStatement(tableMetaData, getTableName(), whereList); sqlStatementCache.addDeleteSQL(whereList, sql); } // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = getDeleteStatement(session.getConnection(), sql, whereList); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); int rows = stmt.executeUpdate(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } return rows; } catch (SQLException e) { throw new RimuDBException(e, getClass().getName(), "deleteAllUsingTransaction"); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { } } } } /** * Add a record using the Session. * * @param session Session * @param record Record * @return int The number of records added * @throws RimuDBException */ public int addUsingSession(Session session, Record record) throws RimuDBException { PreparedStatement stmt = null; int statID = 0; int result = 0; try { String sql = sqlStatementCache.getInsertSQL(); if (sql == null) { sql = sqlAdapter.getInsertStatement(tableMetaData, getTableName()); sqlStatementCache.setInsertSQL(sql); } // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = createPreparedStatement(session.getConnection(), sql, CrudType.CREATE); recordBinder.bindStatementForInsert(stmt, record); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); result = stmt.executeUpdate(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); // Mark the record as in existence record.setExists(true); // If we had auto-increment keys then update the record with their new values if (tableMetaData.hasAutoIncrementKeys()) { if (sqlAdapter.getDatabaseMetaData().getSupportsGetGeneratedKeys()) { populateGeneratedKeys(stmt, record); // If the generated key can be derived from the insert statement. } else if (sqlAdapter.isInsertIndentityStatementSupported()) { // TODO: Handle this somehow // If a separate statement needs to be executed. } else { ColumnMetaData columnMetaData = getTableMetaData().getAutoIncrementColumn(); String autoIncrementColumn = columnMetaData.getColumnName(); int columnType = columnMetaData.getColumnType(); String identitySelectSQL = sqlAdapter.getIdentitySelectSQL(getTableName(), autoIncrementColumn, columnType); // Execute the statement to get the identity value try { //fetch the generated id in a separate query PreparedStatement ps = session.getConnection().prepareStatement(identitySelectSQL); try { populateQueriedKeys(ps, record); } finally { ps.close(); } } catch (SQLException sqle) { throw new RimuDBException(sqle, "Could not retrieve generated id after insert for sql: " + sql); } } } if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } } catch (SQLException e) { throw new RimuDBException(e); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { } stmt = null; } } return result; } /** * Change a record in the table. * * @param oldRecord Record * @param newRecord Record */ protected void update(Record oldRecord, Record newRecord) throws RimuDBException { // Check the old version exists and has not changed if (getOptimisticLocking() == OptimisticLocking.PRIOR_READ) { Record currentValue = get(oldRecord.getPrimaryWhereList()); if (!(currentValue.equals(oldRecord))) { String changedValues = currentValue.getChangedPropertiesText(oldRecord); throw new RecordChangeException( "This record has already been changed by another process. Change was not processed. Changed values: " + changedValues); } } Connection con = null; try { con = getDatabase().getDatabaseConnection(); update(con, oldRecord, newRecord); } catch (SQLException e) { throw new RimuDBException(e); } finally { if (con != null) { try { con.close(); } catch (SQLException e) { } con = null; } } } /** * Update the database with the newRecord data. Key values from the * oldRecord are used. The update is run using the SQL connection con. */ protected void update(Connection con, Record oldRecord, Record newRecord) throws RimuDBException { PreparedStatement stmt = null; int statID = 0; try { List<String> changedPropertyList = newRecord.getModifiedProperties(); List<String> nulledProperties = oldRecord.getNulledProperties(); // Throws an exception if one of the changed properties is a PK and its being changed to a null tableMetaData.checkForNulledPrimaryKey(newRecord.getModifiedToNullProperties()); List<String> nullColumnList = tableMetaData.getColumnNamesFromPropertyNames(nulledProperties); String sql = sqlStatementCache.getUpdatePKSQL(getOptimisticLocking(), changedPropertyList, nullColumnList); if (sql == null) { sql = sqlAdapter.getPrimaryKeyUpdateStatement(tableMetaData, getTableName(), getOptimisticLocking(), changedPropertyList, nullColumnList); sqlStatementCache.addUpdatePKSQL(getOptimisticLocking(), changedPropertyList, nullColumnList, sql); } // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = createPreparedStatement(con, sql, CrudType.UPDATE); recordBinder.bindStatementForUpdate(stmt, oldRecord, newRecord, getOptimisticLocking(), changedPropertyList); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); int rowsUpdated = stmt.executeUpdate(); SQLWarning warning = stmt.getWarnings(); while (warning != null) { log.warn(warning.getMessage()); warning = warning.getNextWarning(); } // If we didn't update a row then the where clause didn't find a record, so we have a stale record if (rowsUpdated == 0) { // Read the current record from the DB so we can discover the values that are different Record currentValue = get(oldRecord.getPrimaryWhereList()); String changedValues = currentValue.getChangedPropertiesText(oldRecord); // log.error("Record in database: "+currentValue.toKeyPairString()); // log.error("Unmodified record in memory: "+oldRecord.toKeyPairString()); // log.error("Modified record in memory: "+newRecord.toKeyPairString()); throw new RecordChangeException( "This record has already been changed by another process. Change was not processed. Changed values: " + changedValues); } if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } } catch (SQLException e) { throw new RimuDBException(e); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { } stmt = null; } } } public void updateUsingSession(Session session, Record oldRecord, Record newRecord) throws RimuDBException { // Check the old version exists and has not changed if (getOptimisticLocking() == OptimisticLocking.PRIOR_READ) { Record currentValue = get(oldRecord.getPrimaryWhereList()); if (!(currentValue.equals(oldRecord))) { String changedValues = currentValue.getChangedPropertiesText(oldRecord); throw new RecordChangeException( "This record has already been changed by another process. Change was not processed. Changed values: " + changedValues); } } PreparedStatement stmt = null; int statID = 0; try { List<String> changedPropertyList = newRecord.getModifiedProperties(); List<String> nulledProperties = oldRecord.getNulledProperties(); // Throws an exception if one of the changed properties is a PK and its being changed to a null tableMetaData.checkForNulledPrimaryKey(newRecord.getModifiedToNullProperties()); List<String> nullColumnList = tableMetaData.getColumnNamesFromPropertyNames(nulledProperties); String sql = sqlStatementCache.getUpdatePKSQL(getOptimisticLocking(), changedPropertyList, nullColumnList); if (sql == null) { sql = sqlAdapter.getPrimaryKeyUpdateStatement(tableMetaData, getTableName(), getOptimisticLocking(), changedPropertyList, nullColumnList); sqlStatementCache.addUpdatePKSQL(getOptimisticLocking(), changedPropertyList, nullColumnList, sql); } // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = createPreparedStatement(session.getConnection(), sql, CrudType.UPDATE); recordBinder.bindStatementForUpdate(stmt, oldRecord, newRecord, getOptimisticLocking(), changedPropertyList); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); int rowsUpdated = stmt.executeUpdate(); // If we didn't update a row then the where clause didn't find a record, so we have a stale record if (rowsUpdated == 0) { // Read the current record from the DB so we can discover the values that are different Record currentValue = get(oldRecord.getPrimaryWhereList()); String changedValues = currentValue.getChangedPropertiesText(oldRecord); throw new RecordChangeException( "This record has already been changed by another process. Change was not processed. Changed values: " + changedValues); } if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } } catch (SQLException e) { throw new RimuDBException(e); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { } stmt = null; } } } /** * * @param session Session * @param oldRecord Record * @param newRecord Record * @throws RimuDBException */ public void updateRecordInBatch(Session session, Record oldRecord, Record newRecord) throws RimuDBException { PreparedStatement stmt = null; int statID = 0; try { List<String> changedPropertyList = newRecord.getModifiedProperties(); List<String> nulledProperties = oldRecord.getNulledProperties(); // Throws an exception if one of the changed properties is a PK and its being changed to a null tableMetaData.checkForNulledPrimaryKey(newRecord.getModifiedToNullProperties()); List<String> nullColumnList = tableMetaData.getColumnNamesFromPropertyNames(nulledProperties); String sql = sqlStatementCache.getUpdatePKSQL(getOptimisticLocking(), changedPropertyList, nullColumnList); if (sql == null) { sql = sqlAdapter.getPrimaryKeyUpdateStatement(tableMetaData, getTableName(), getOptimisticLocking(), changedPropertyList, nullColumnList); sqlStatementCache.addUpdatePKSQL(getOptimisticLocking(), changedPropertyList, nullColumnList, sql); } // Get the statistic ID int loggingType = getDatabase().getDatabaseConfiguration().getLoggingType(); if (loggingType == DatabaseConfiguration.LOG_STATISTICS) { statID = StatisticCollector.getInstance().createStatistic(sql); } else if (loggingType == DatabaseConfiguration.LOG_SQL_ONLY) { log.info("SQL=" + sql); } stmt = session.getBatchStatement(this, Session.BATCH_UPDATE); if (stmt == null) { stmt = createPreparedStatement(session.getConnection(), sql, CrudType.UPDATE); session.setBatchStatement(this, stmt, Session.BATCH_UPDATE); } recordBinder.bindStatementForUpdate(stmt, oldRecord, newRecord, getOptimisticLocking(), changedPropertyList); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "preparetime"); stmt.addBatch(); if (statID > 0) StatisticCollector.getInstance().logEvent(statID, "executetime"); if (statID > 0) { StatisticCollector.getInstance().logEvent(statID, "processtime"); if (StatisticCollector.getInstance().exceedsThreshold(statID, getDatabase().getDatabaseConfiguration().getLoggingThreshold())) { String text = StatisticCollector.getInstance().formatStatistics(statID, getDatabase().getStatisticFormatter()); log.info(text); } StatisticCollector.getInstance().removeID(statID); } } catch (SQLException e) { throw new RimuDBException(e); } } /** * Initialize the table * @throws Exception * @throws SecurityException */ public void initialize() throws RimuDBException { // Potentially do something here } /** * @param dataObjectClass */ /*package*/ void setDataObjectClass(Class<? extends DataObject> dataObjectClass) { this.dataObjectClass = dataObjectClass; } /** * @param usingLockingHints the usingLockingHints to set */ public void setUsingLockingHints(boolean usingLockingHints) { this.usingLockingHints = usingLockingHints; } /** * @return the usingLockingHints */ public boolean isUsingLockingHints() { return usingLockingHints; } /** * Set the optimistic locking strategy. * * @param optimisticLocking OptimisticLocking */ public void setOptimisticLocking(OptimisticLocking optimisticLocking) { this.optimisticLocking = optimisticLocking; } /** * Return the optimistic locking strategy. * * @return OptimisticLocking */ public OptimisticLocking getOptimisticLocking() { return optimisticLocking; } /** * Return the table name. * * @return String */ public String getTableName() { return tableName; } /** * Set the table name. * * @param tableName String */ public void setTableName(String tableName) { this.tableName = tableName; } /** * Set the RecordBinder for the table. * * @param recordBinder RecordBinder */ public void setRecordBinder(RecordBinder recordBinder) { this.recordBinder = recordBinder; } /** * Return true if this table has auto increment keys and the database supports them. * * @return boolean */ public boolean processesGeneratedKeys() { return tableMetaData.hasAutoIncrementKeys() && sqlAdapter.getSupportsMultipleGetGeneratedKeys(); } }