Menu
▾
▴
[Openutils-svn] SF.net SVN: openutils: [315] trunk/openutils-dbmigration/src/main/java/it/ openutils/migration/task/setup/ExcelConfigurationTask.java
|
From: <fg...@us...> - 2007-03-11 17:45:42
|
Revision: 315
http://svn.sourceforge.net/openutils/?rev=315&view=rev
Author: fgiust
Date: 2007-03-11 10:45:43 -0700 (Sun, 11 Mar 2007)
Log Message:
-----------
allow updates in ExcelConfigurationTask
Modified Paths:
--------------
trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
Modified: trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2007-03-05 10:59:55 UTC (rev 314)
+++ trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2007-03-11 17:45:43 UTC (rev 315)
@@ -17,6 +17,7 @@
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.ArrayUtils;
+import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
@@ -29,8 +30,10 @@
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.jdbc.BadSqlGrammarException;
+import org.springframework.jdbc.core.ColumnMapRowMapper;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.RowMapper;
/**
@@ -172,6 +175,8 @@
String checkStatement = StringUtils.remove(StringUtils.trim(con.getCheckQuery()), "\n");
String insertStatement = StringUtils.remove(StringUtils.trim(con.getInsertQuery()), "\n");
+ String selectStatement = StringUtils.remove(StringUtils.trim(con.getSelectQuery()), "\n");
+ String updateStatement = StringUtils.remove(StringUtils.trim(con.getUpdateQuery()), "\n");
processRecords(
sheet,
@@ -179,6 +184,8 @@
ArrayUtils.toPrimitive(types.toArray(new Integer[types.size()]), Types.NULL),
checkStatement,
insertStatement,
+ selectStatement,
+ updateStatement,
dataSource,
tableName);
}
@@ -188,13 +195,17 @@
* @param columns
* @param checkStatement
* @param insertStatement
+ * @param updateStatement
+ * @param selectStatement
*/
private void processRecords(HSSFSheet sheet, List<String> columns, int[] types, String checkStatement,
- String insertStatement, DataSource dataSource, String tableName)
+ String insertStatement, String selectStatement, String updateStatement, DataSource dataSource, String tableName)
{
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
int checkNum = StringUtils.countMatches(checkStatement, "?");
int insertNum = StringUtils.countMatches(insertStatement, "?");
+ int selectNum = StringUtils.countMatches(selectStatement, "?");
+ int updateNum = StringUtils.countMatches(updateStatement, "?");
HSSFRow row;
for (short rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++)
@@ -289,13 +300,13 @@
try
{
- jdbcTemplate.update(insertStatement, insertParams, insertTypes);
+ jdbcTemplate.update(updateStatement, insertParams, insertTypes);
}
catch (DataIntegrityViolationException bsge)
{
log
.error(
- "Error executing insert, record at {}:{} will be skipped. Query in error: '{}', values: {}. Error message: {}",
+ "Error executing update, record at {}:{} will be skipped. Query in error: '{}', values: {}. Error message: {}",
new Object[]{
tableName,
rowNum + 1,
@@ -305,7 +316,91 @@
continue;
}
}
+ else if (StringUtils.isNotBlank(updateStatement) && StringUtils.isNotBlank(selectStatement))
+ {
+ try
+ {
+ RowMapper rowMapper = new ColumnMapRowMapper();
+ Object[] selectParams = ArrayUtils.subarray(values.toArray(), 0, selectNum);
+ List selectResult = jdbcTemplate.query(selectStatement, selectParams, rowMapper);
+ Map<String, Object> fetchedColumns = (Map<String, Object>) selectResult.get(0);
+ int i = 0;
+ boolean updateNeeded = false;
+ for (String columnName : columns)
+ {
+ Object columnObject = fetchedColumns.get(columnName);
+ if (columnObject == null)
+ {
+ continue;
+ }
+ String columnValue = ObjectUtils.toString(fetchedColumns.get(columnName));
+ if (!StringUtils.equals(columnValue, values.get(i)))
+ {
+ updateNeeded = true;
+ break;
+ }
+ i++;
+ }
+ if (updateNeeded)
+ {
+ Object[] updateParams = ArrayUtils.subarray(values.toArray(), 0, updateNum);
+ int[] insertTypes = ArrayUtils.subarray(types, 0, insertNum);
+ if (log.isDebugEnabled())
+ {
+ log.debug(
+ "Missing record with key {}; updating {}",
+ ArrayUtils.toString(checkParams),
+ ArrayUtils.toString(updateParams));
+ }
+ if (updateParams.length != insertTypes.length)
+ {
+ log.warn("Invalid number of param/type for table {}. Params: {}, types: {}", new Object[]{
+ tableName,
+ updateParams.length,
+ insertTypes.length });
+ }
+
+ try
+ {
+ Object[] compoundUpdateParams = new Object[checkParams.length + updateParams.length];
+ System.arraycopy(updateParams, 0, compoundUpdateParams, 0, updateParams.length);
+ System.arraycopy(
+ checkParams,
+ 0,
+ compoundUpdateParams,
+ compoundUpdateParams.length - 1,
+ checkParams.length);
+ jdbcTemplate.update(updateStatement, compoundUpdateParams);
+ }
+ catch (DataIntegrityViolationException bsge)
+ {
+ log
+ .error(
+ "Error executing insert, record at {}:{} will be skipped. Query in error: '{}', values: {}. Error message: {}",
+ new Object[]{
+ tableName,
+ rowNum + 1,
+ insertStatement,
+ ArrayUtils.toString(updateParams),
+ bsge.getMessage() });
+ continue;
+ }
+ }
+ }
+ catch (BadSqlGrammarException bsge)
+ {
+ log
+ .error(
+ "Error executing query to load row values, current possible update of row will be skipped. {} Query in error: {}",
+ bsge.getMessage(),
+ checkStatement);
+ return;
+ }
+ // 1 check if it is the same
+ // 2 update only if they differ
+ }
+
}
}
@@ -320,7 +415,29 @@
private String insertQuery;
+ private String selectQuery;
+
+ private String updateQuery;
+
/**
+ * Returns the selectQuery.
+ * @return the selectQuery
+ */
+ public String getSelectQuery()
+ {
+ return selectQuery;
+ }
+
+ /**
+ * Sets the selectQuery.
+ * @param selectQuery the selectQuery to set
+ */
+ public void setSelectQuery(String selectQuery)
+ {
+ this.selectQuery = selectQuery;
+ }
+
+ /**
* Returns the checkQuery.
* @return the checkQuery
*/
@@ -355,5 +472,23 @@
{
this.insertQuery = insertQuery;
}
+
+ /**
+ * Returns the updateQuery.
+ * @return the updateQuery
+ */
+ public String getUpdateQuery()
+ {
+ return updateQuery;
+ }
+
+ /**
+ * Sets the updateQuery.
+ * @param updateQuery the updateQuery to set
+ */
+ public void setUpdateQuery(String updateQuery)
+ {
+ this.updateQuery = updateQuery;
+ }
}
}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
