You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by to...@apache.org on 2005/11/30 15:08:45 UTC
svn commit: r349954 - in /db/ddlutils/trunk/src/java/org/apache/ddlutils:
Platform.java io/DataToDatabaseSink.java platform/PlatformImplBase.java
task/WriteDataToDatabaseCommand.java
Author: tomdz
Date: Wed Nov 30 06:08:40 2005
New Revision: 349954
URL: http://svn.apache.org/viewcvs?rev=349954&view=rev
Log:
Changed signature of the insert/update/delete methods to be consistent with the other connection-using methods
Added ability to batch-insert a collection of beans, to the platform and the writeDataToDatabase task
Modified:
db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java
db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java
db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java
db/ddlutils/trunk/src/java/org/apache/ddlutils/task/WriteDataToDatabaseCommand.java
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java
URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java?rev=349954&r1=349953&r2=349954&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java Wed Nov 30 06:08:40 2005
@@ -595,11 +595,36 @@
* bean will also be updated with the column value generated by the database.
* Note that the connection will not be closed by this method.
*
+ * @param connection The database connection
* @param model The database model to use
* @param dynaBean The bean
+ */
+ public void insert(Connection connection, Database model, DynaBean dynaBean) throws DynaSqlException;
+
+ /**
+ * Inserts the given beans in the database, assuming the primary key values are specified.
+ * Note that a batch insert is used for subsequent beans of the same type.
+ * Also the properties for the primary keys are not updated in the beans. Hence you should
+ * not use this method when the primary key values are defined by the database (via a sequence
+ * or identity constraint).
+ *
+ * @param model The database model to use
+ * @param dynaBeans The beans to insert
+ */
+ public void insert(Database model, Collection dynaBeans) throws DynaSqlException;
+
+ /**
+ * Inserts the given beans. Note that a batch insert is used for subsequent beans of the same type.
+ * Also the properties for the primary keys are not updated in the beans. Hence you should
+ * not use this method when the primary key values are defined by the database (via a sequence
+ * or identity constraint).
+ * This method does not close the connection.
+ *
* @param connection The database connection
+ * @param model The database model to use
+ * @param dynaBean The bean
*/
- public void insert(Database model, DynaBean dynaBean, Connection connection) throws DynaSqlException;
+ public void insert(Connection connection, Database model, Collection dynaBeans) throws DynaSqlException;
/**
* Returns the sql for updating the given bean in the database.
@@ -621,11 +646,11 @@
/**
* Updates the row which maps to the given bean.
*
+ * @param connection The database connection
* @param model The database model to use
* @param dynaBean The bean
- * @param connection The database connection
*/
- public void update(Database model, DynaBean dynaBean, Connection connection) throws DynaSqlException;
+ public void update(Connection connection, Database model, DynaBean dynaBean) throws DynaSqlException;
/**
* Returns the sql for deleting the given bean from the database.
@@ -651,7 +676,7 @@
* @param dynaBean The bean
* @param connection The database connection
*/
- public void delete(Database model, DynaBean dynaBean, Connection connection) throws DynaSqlException;
+ public void delete(Connection connection, Database model, DynaBean dynaBean) throws DynaSqlException;
/**
* Reads the database model from the live database as specified by the data source set for
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java
URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java?rev=349954&r1=349953&r2=349954&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/io/DataToDatabaseSink.java Wed Nov 30 06:08:40 2005
@@ -58,6 +58,12 @@
private boolean _haltOnErrors = true;
/** Whether to delay the insertion of beans so that the beans referenced by it via foreignkeys, are already inserted into the database. */
private boolean _ensureFkOrder = true;
+ /** Whether to use batch mode inserts. */
+ private boolean _useBatchMode = false;
+ /** The queued objects for batch insertion. */
+ private ArrayList _batchQueue = new ArrayList();
+ /** The number of beans to insert in one batch. */
+ private int _batchSize = 1024;
/** Stores the already-processed identities per table name. */
private HashMap _processedIdentities = new HashMap();
/** Stores the objects that are waiting for other objects to be inserted. */
@@ -123,10 +129,52 @@
}
/**
+ * Determines whether batch mode is used for inserting the beans.
+ *
+ * @return <code>true</code> if batch mode is used (<code>false</code> per default)
+ */
+ public boolean isUseBatchMode()
+ {
+ return _useBatchMode;
+ }
+
+ /**
+ * Specifies whether batch mode is used for inserting the beans. Note that this requires
+ * that the primary key values are not defined by the database.
+ *
+ * @param useBatchMode <code>true</code> if batch mode shall be used
+ */
+ public void setUseBatchMode(boolean useBatchMode)
+ {
+ _useBatchMode = useBatchMode;
+ }
+
+ /**
+ * Returns the (maximum) number of beans to insert in one batch.
+ *
+ * @return The number of beans
+ */
+ public int getBatchSize()
+ {
+ return _batchSize;
+ }
+
+ /**
+ * Sets the (maximum) number of beans to insert in one batch.
+ *
+ * @param batchSize The number of beans
+ */
+ public void setBatchSize(int batchSize)
+ {
+ _batchSize = batchSize;
+ }
+
+ /**
* {@inheritDoc}
*/
public void end() throws DataSinkException
{
+ purgeBatchQueue();
try
{
_connection.close();
@@ -219,31 +267,7 @@
return;
}
}
-
- try
- {
- _platform.insert(_model, bean, _connection);
- if (!_connection.getAutoCommit())
- {
- _connection.commit();
- }
- if (_log.isDebugEnabled())
- {
- _log.debug("Inserted bean "+buildIdentityFromPKs(table, bean).toString());
- }
- }
- catch (Exception ex)
- {
- if (_haltOnErrors)
- {
- _platform.returnConnection(_connection);
- throw new DataSinkException(ex);
- }
- else
- {
- _log.warn("Exception while inserting a bean into the database", ex);
- }
- }
+ insertBeanIntoDatabase(table, bean);
if (_processedIdentities.containsKey(table.getName()))
{
Identity identity = buildIdentityFromPKs(table, bean);
@@ -282,6 +306,94 @@
}
}
+ /**
+ * Inserts the bean into the database or batch queue.
+ *
+ * @param table The table
+ * @param bean The bean
+ */
+ private void insertBeanIntoDatabase(Table table, DynaBean bean) throws DataSinkException
+ {
+ if (_useBatchMode)
+ {
+ _batchQueue.add(bean);
+ if (_batchQueue.size() >= _batchSize)
+ {
+ purgeBatchQueue();
+ }
+ }
+ else
+ {
+ insertSingleBeanIntoDatabase(table, bean);
+ }
+ }
+
+ /**
+ * Purges the batch queue by inserting the objects into the database.
+ */
+ private void purgeBatchQueue() throws DataSinkException
+ {
+ try
+ {
+ _platform.insert(_connection, _model, _batchQueue);
+ if (!_connection.getAutoCommit())
+ {
+ _connection.commit();
+ }
+ if (_log.isDebugEnabled())
+ {
+ _log.debug("Inserted "+_batchQueue.size()+" beans in batch mode ");
+ }
+ }
+ catch (Exception ex)
+ {
+ if (_haltOnErrors)
+ {
+ _platform.returnConnection(_connection);
+ throw new DataSinkException(ex);
+ }
+ else
+ {
+ _log.warn("Exception while inserting "+_batchQueue.size()+" beans via batch mode into the database", ex);
+ }
+ }
+ _batchQueue.clear();
+ }
+
+ /**
+ * Directly inserts the given bean into the database.
+ *
+ * @param table The table of the bean
+ * @param bean The bean
+ */
+ private void insertSingleBeanIntoDatabase(Table table, DynaBean bean) throws DataSinkException
+ {
+ try
+ {
+ _platform.insert(_connection, _model, bean);
+ if (!_connection.getAutoCommit())
+ {
+ _connection.commit();
+ }
+ if (_log.isDebugEnabled())
+ {
+ _log.debug("Inserted bean "+buildIdentityFromPKs(table, bean).toString());
+ }
+ }
+ catch (Exception ex)
+ {
+ if (_haltOnErrors)
+ {
+ _platform.returnConnection(_connection);
+ throw new DataSinkException(ex);
+ }
+ else
+ {
+ _log.warn("Exception while inserting a bean into the database", ex);
+ }
+ }
+ }
+
/**
* Returns the name of the given foreign key. If it has no name, then a temporary one
* is generated from the names of the relevant tables and columns.
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java
URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java?rev=349954&r1=349953&r2=349954&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java Wed Nov 30 06:08:40 2005
@@ -828,7 +828,7 @@
/**
* {@inheritDoc}
*/
- public void insert(Database model, DynaBean dynaBean, Connection connection) throws DynaSqlException
+ public void insert(Connection connection, Database model, DynaBean dynaBean) throws DynaSqlException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] properties = dynaClass.getSqlDynaProperties();
@@ -971,7 +971,149 @@
try
{
- insert(model, dynaBean, connection);
+ insert(connection, model, dynaBean);
+ }
+ finally
+ {
+ returnConnection(connection);
+ }
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void insert(Connection connection, Database model, Collection dynaBeans) throws DynaSqlException
+ {
+ SqlDynaClass dynaClass = null;
+ SqlDynaProperty[] properties = null;
+ PreparedStatement statement = null;
+ int addedStmts = 0;
+
+ for (Iterator it = dynaBeans.iterator(); it.hasNext();)
+ {
+ DynaBean dynaBean = (DynaBean)it.next();
+ SqlDynaClass curDynaClass = model.getDynaClassFor(dynaBean);
+
+ if (curDynaClass != dynaClass)
+ {
+ if (dynaClass != null)
+ {
+ executeBatch(statement, addedStmts, dynaClass.getTableName());
+ addedStmts = 0;
+ }
+
+ dynaClass = curDynaClass;
+ properties = dynaClass.getSqlDynaProperties();
+
+ if (properties.length == 0)
+ {
+ _log.warn("Cannot insert instances of type " + dynaClass + " because it has no properties");
+ continue;
+ }
+
+ Column[] columns = model.findTable(dynaClass.getTableName()).getAutoIncrementColumns();
+
+ if (columns.length > 0)
+ {
+ SqlDynaProperty[] newProperties = new SqlDynaProperty[properties.length - 1];
+ int newIdx = 0;
+
+ // We have to remove the auto-increment columns as some databases won't like
+ // it being present in the insert command
+
+ for (int propIdx = 0; propIdx < properties.length; propIdx++)
+ {
+ for (int autoIncrColumnIdx = 0; autoIncrColumnIdx < columns.length; autoIncrColumnIdx++)
+ {
+ if (properties[propIdx].getColumn() != columns[autoIncrColumnIdx])
+ {
+ newProperties[newIdx++] = properties[propIdx];
+ }
+ }
+ }
+ properties = newProperties;
+ }
+
+ String insertSql = createInsertSql(model, dynaClass, properties, null);
+
+ if (_log.isDebugEnabled())
+ {
+ _log.debug("Starting new batch with SQL: " + insertSql);
+ }
+ try
+ {
+ statement = connection.prepareStatement(insertSql);
+ }
+ catch (SQLException ex)
+ {
+ throw new DynaSqlException("Error while preparing insert statement", ex);
+ }
+ }
+ try
+ {
+ for (int idx = 0; idx < properties.length; idx++ )
+ {
+ setObject(statement, idx + 1, dynaBean, properties[idx]);
+ }
+ statement.addBatch();
+ addedStmts++;
+ }
+ catch (SQLException ex)
+ {
+ throw new DynaSqlException("Error while adding batch insert", ex);
+ }
+ }
+ if (dynaClass != null)
+ {
+ executeBatch(statement, addedStmts, dynaClass.getTableName());
+ }
+ }
+
+ /**
+ * Performs the batch for the given statement, and checks that the specified amount of rows have been changed.
+ *
+ * @param statement The prepared statement
+ * @param numRows The number of rows that should change
+ * @param tableName The name of the changed table
+ */
+ private void executeBatch(PreparedStatement statement, int numRows, String tableName) throws DynaSqlException
+ {
+ if (statement != null)
+ {
+ try
+ {
+ int[] results = statement.executeBatch();
+
+ closeStatement(statement);
+
+ int sum = 0;
+
+ for (int idx = 0; (results != null) && (idx < results.length); idx++)
+ {
+ sum += results[idx];
+ }
+ if (sum != numRows)
+ {
+ _log.warn("Attempted to insert " + numRows + " rows into table " + tableName + " but changed " + sum + " rows");
+ }
+ }
+ catch (SQLException ex)
+ {
+ throw new DynaSqlException("Error while inserting into the database", ex);
+ }
+ }
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void insert(Database model, Collection dynaBeans) throws DynaSqlException
+ {
+ Connection connection = borrowConnection();
+
+ try
+ {
+ insert(connection, model, dynaBeans);
}
finally
{
@@ -1019,7 +1161,7 @@
/**
* {@inheritDoc}
*/
- public void update(Database model, DynaBean dynaBean, Connection connection) throws DynaSqlException
+ public void update(Connection connection, Database model, DynaBean dynaBean) throws DynaSqlException
{
SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
SqlDynaProperty[] primaryKeys = dynaClass.getPrimaryKeyProperties();
@@ -1081,7 +1223,7 @@
try
{
- update(model, dynaBean, connection);
+ update(connection, model, dynaBean);
}
finally
{
@@ -1096,7 +1238,7 @@
* @param connection The connection
* @return <code>true</code> if this dyna bean has a primary key
*/
- protected boolean exists(DynaBean dynaBean, Connection connection)
+ protected boolean exists(Connection connection, DynaBean dynaBean)
{
// TODO: check for the pk value, and if present, query against database
return false;
@@ -1111,13 +1253,13 @@
try
{
- if (exists(dynaBean, connection))
+ if (exists(connection, dynaBean))
{
- update(model, dynaBean, connection);
+ update(connection, model, dynaBean);
}
else
{
- insert(model, dynaBean, connection);
+ insert(connection, model, dynaBean);
}
}
finally
@@ -1173,7 +1315,7 @@
try
{
- delete(model, dynaBean, connection);
+ delete(connection, model, dynaBean);
}
finally
{
@@ -1184,7 +1326,7 @@
/**
* {@inheritDoc}
*/
- public void delete(Database model, DynaBean dynaBean, Connection connection) throws DynaSqlException
+ public void delete(Connection connection, Database model, DynaBean dynaBean) throws DynaSqlException
{
PreparedStatement statement = null;
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/task/WriteDataToDatabaseCommand.java
URL: http://svn.apache.org/viewcvs/db/ddlutils/trunk/src/java/org/apache/ddlutils/task/WriteDataToDatabaseCommand.java?rev=349954&r1=349953&r2=349954&view=diff
==============================================================================
--- db/ddlutils/trunk/src/java/org/apache/ddlutils/task/WriteDataToDatabaseCommand.java (original)
+++ db/ddlutils/trunk/src/java/org/apache/ddlutils/task/WriteDataToDatabaseCommand.java Wed Nov 30 06:08:40 2005
@@ -42,7 +42,11 @@
private File _singleDataFile = null;
/** The input files. */
private ArrayList _fileSets = new ArrayList();
-
+ /** Whether we should use batch mode. */
+ private Boolean _useBatchMode;
+ /** The maximum number of objects to insert in one batch. */
+ private Integer _batchSize;
+
/**
* Adds a fileset.
*
@@ -64,6 +68,26 @@
}
/**
+ * Sets the maximum number of objects to insert in one batch.
+ *
+ * @param batchSize The number of objects
+ */
+ public void setBatchSize(int batchSize)
+ {
+ _batchSize = new Integer(batchSize);
+ }
+
+ /**
+ * Specifies whether we shall be using batch mode.
+ *
+ * @param useBatchMode <code>true</code> if we shall use batch mode
+ */
+ public void setUseBatchMode(boolean useBatchMode)
+ {
+ _useBatchMode = Boolean.valueOf(useBatchMode);
+ }
+
+ /**
* {@inheritDoc}
*/
public void execute(Task task, Database model) throws BuildException
@@ -74,6 +98,15 @@
DataToDatabaseSink sink = new DataToDatabaseSink(platform, model);
DataReader reader = new DataReader();
+ if (_useBatchMode != null)
+ {
+ sink.setUseBatchMode(_useBatchMode.booleanValue());
+ if (_batchSize != null)
+ {
+ sink.setBatchSize(_batchSize.intValue());
+ }
+ }
+
reader.setModel(model);
reader.setSink(sink);
registerConverters(reader.getConverterConfiguration());