Menu
▾
▴
[Openutils-svn] SF.net SVN: openutils: [684] tags
|
From: <fg...@us...> - 2008-02-21 21:03:47
|
Revision: 684
http://openutils.svn.sourceforge.net/openutils/?rev=684&view=rev
Author: fgiust
Date: 2008-02-21 13:03:49 -0800 (Thu, 21 Feb 2008)
Log Message:
-----------
[maven-release-plugin] copy for tag openutils-dbmigration-2.0.3
Added Paths:
-----------
tags/openutils-dbmigration-2.0.3/
tags/openutils-dbmigration-2.0.3/pom.xml
tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java
tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java
tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
tags/openutils-dbmigration-2.0.3/src/site/changes/changes.xml
Removed Paths:
-------------
tags/openutils-dbmigration-2.0.3/pom.xml
tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java
tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
tags/openutils-dbmigration-2.0.3/src/site/changes/changes.xml
Copied: tags/openutils-dbmigration-2.0.3 (from rev 681, trunk/openutils-dbmigration)
Deleted: tags/openutils-dbmigration-2.0.3/pom.xml
===================================================================
--- trunk/openutils-dbmigration/pom.xml 2008-02-21 14:57:33 UTC (rev 681)
+++ tags/openutils-dbmigration-2.0.3/pom.xml 2008-02-21 21:03:49 UTC (rev 684)
@@ -1,96 +0,0 @@
-<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
- <modelVersion>4.0.0</modelVersion>
- <parent>
- <groupId>net.sourceforge.openutils</groupId>
- <artifactId>openutils</artifactId>
- <version>7</version>
- <relativePath>..</relativePath>
- </parent>
- <artifactId>openutils-dbmigration</artifactId>
- <name>openutils db migration</name>
- <version>2.0.3-SNAPSHOT</version>
- <description />
- <dependencies>
- <dependency>
- <groupId>commons-lang</groupId>
- <artifactId>commons-lang</artifactId>
- <version>2.3</version>
- </dependency>
- <dependency>
- <groupId>commons-io</groupId>
- <artifactId>commons-io</artifactId>
- <version>1.3</version>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-jdbc</artifactId>
- <version>${spring.version}</version>
- <exclusions>
- <exclusion>
- <groupId>commons-logging</groupId>
- <artifactId>commons-logging</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-beans</artifactId>
- <version>${spring.version}</version>
- <exclusions>
- <exclusion>
- <groupId>commons-logging</groupId>
- <artifactId>commons-logging</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-core</artifactId>
- <version>${spring.version}</version>
- <exclusions>
- <exclusion>
- <groupId>commons-logging</groupId>
- <artifactId>commons-logging</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.slf4j</groupId>
- <artifactId>slf4j-api</artifactId>
- <version>1.4.1</version>
- </dependency>
- <dependency>
- <groupId>org.slf4j</groupId>
- <artifactId>jcl104-over-slf4j</artifactId>
- <version>1.4.1</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.0-FINAL</version>
- <optional>true</optional>
- <exclusions>
- <exclusion>
- <groupId>commons-logging</groupId>
- <artifactId>commons-logging</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- <dependency>
- <groupId>org.testng</groupId>
- <artifactId>testng</artifactId>
- <classifier>jdk15</classifier>
- <version>5.1</version>
- <scope>test</scope>
- <exclusions>
- <exclusion>
- <groupId>junit</groupId>
- <artifactId>junit</artifactId>
- </exclusion>
- </exclusions>
- </dependency>
- </dependencies>
- <properties>
- <spring.version>2.5.1</spring.version>
- </properties>
-</project>
\ No newline at end of file
Copied: tags/openutils-dbmigration-2.0.3/pom.xml (from rev 683, trunk/openutils-dbmigration/pom.xml)
===================================================================
--- tags/openutils-dbmigration-2.0.3/pom.xml (rev 0)
+++ tags/openutils-dbmigration-2.0.3/pom.xml 2008-02-21 21:03:49 UTC (rev 684)
@@ -0,0 +1,102 @@
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
+ <modelVersion>4.0.0</modelVersion>
+ <parent>
+ <groupId>net.sourceforge.openutils</groupId>
+ <artifactId>openutils</artifactId>
+ <version>7</version>
+ <relativePath>..</relativePath>
+ </parent>
+ <artifactId>openutils-dbmigration</artifactId>
+ <name>openutils db migration</name>
+ <version>2.0.3</version>
+ <description />
+ <dependencies>
+ <dependency>
+ <groupId>commons-lang</groupId>
+ <artifactId>commons-lang</artifactId>
+ <version>2.3</version>
+ </dependency>
+ <dependency>
+ <groupId>commons-io</groupId>
+ <artifactId>commons-io</artifactId>
+ <version>1.3</version>
+ </dependency>
+ <dependency>
+ <groupId>org.springframework</groupId>
+ <artifactId>spring-jdbc</artifactId>
+ <version>${spring.version}</version>
+ <exclusions>
+ <exclusion>
+ <groupId>commons-logging</groupId>
+ <artifactId>commons-logging</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ <dependency>
+ <groupId>org.springframework</groupId>
+ <artifactId>spring-beans</artifactId>
+ <version>${spring.version}</version>
+ <exclusions>
+ <exclusion>
+ <groupId>commons-logging</groupId>
+ <artifactId>commons-logging</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ <dependency>
+ <groupId>org.springframework</groupId>
+ <artifactId>spring-core</artifactId>
+ <version>${spring.version}</version>
+ <exclusions>
+ <exclusion>
+ <groupId>commons-logging</groupId>
+ <artifactId>commons-logging</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ <dependency>
+ <groupId>org.slf4j</groupId>
+ <artifactId>slf4j-api</artifactId>
+ <version>1.4.1</version>
+ </dependency>
+ <dependency>
+ <groupId>org.slf4j</groupId>
+ <artifactId>jcl104-over-slf4j</artifactId>
+ <version>1.4.1</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.poi</groupId>
+ <artifactId>poi</artifactId>
+ <version>3.0-FINAL</version>
+ <optional>true</optional>
+ <exclusions>
+ <exclusion>
+ <groupId>commons-logging</groupId>
+ <artifactId>commons-logging</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ <dependency>
+ <groupId>org.testng</groupId>
+ <artifactId>testng</artifactId>
+ <classifier>jdk15</classifier>
+ <version>5.1</version>
+ <scope>test</scope>
+ <exclusions>
+ <exclusion>
+ <groupId>junit</groupId>
+ <artifactId>junit</artifactId>
+ </exclusion>
+ </exclusions>
+ </dependency>
+ </dependencies>
+ <properties>
+ <spring.version>2.5.1</spring.version>
+ </properties>
+
+ <scm>
+ <connection>scm:svn:https://openutils.svn.sourceforge.net/svnroot/openutils/tags/openutils-dbmigration-2.0.3</connection>
+ <developerConnection>scm:svn:https://openutils.svn.sourceforge.net/svnroot/openutils/tags/openutils-dbmigration-2.0.3</developerConnection>
+ <url>http://openutils.svn.sourceforge.net/viewcvs.cgi/openutils/tags/openutils-dbmigration-2.0.3</url>
+ </scm>
+</project>
\ No newline at end of file
Deleted: tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java 2008-02-21 14:57:33 UTC (rev 681)
+++ tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java 2008-02-21 21:03:49 UTC (rev 684)
@@ -1,134 +0,0 @@
-/*
- * Copyright Openmind http://www.openmindonline.it
- *
- * Licensed under the Apache License, Version 2.0 (the "License");
- * you may not use this file except in compliance with the License.
- * You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-package it.openutils.migration.oracle;
-
-import it.openutils.migration.task.setup.DbTask;
-
-import java.text.MessageFormat;
-import java.util.List;
-
-import javax.sql.DataSource;
-
-import org.apache.commons.lang.StringUtils;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
-
-
-/**
- * Db tasks that handles the initial setup of sequences.
- * @author fgiust
- * @version $Id$
- */
-public class OracleSequenceCreationTask implements DbTask
-{
-
- /**
- * Logger.
- */
- private Logger log = LoggerFactory.getLogger(OracleSequenceCreationTask.class);
-
- private List<String> sequences;
-
- private String creationQuery;
-
- private String selectUserSequences;
-
- private String selectAllSequences;
-
- private int startsWith;
-
- /**
- * Sets the sequences.
- * @param sequences the sequences to set
- */
- public void setSequences(List<String> sequences)
- {
- this.sequences = sequences;
- }
-
- /**
- * Sets the creationQuery.
- * @param creationQuery the creationQuery to set
- */
- public void setCreationQuery(String creationQuery)
- {
- this.creationQuery = creationQuery;
- }
-
- /**
- * Sets the selectAllSequences.
- * @param selectAllSequences the selectAllSequences to set
- */
- public void setSelectAllSequences(String selectAllSequences)
- {
- this.selectAllSequences = selectAllSequences;
- }
-
- /**
- * Sets the startsWith.
- * @param startsWith the startsWith to set
- */
- public void setStartsWith(int startsWith)
- {
- this.startsWith = startsWith;
- }
-
- /**
- * Sets the selectUserSequences.
- * @param selectUserSequences the selectUserSequences to set
- */
- public void setSelectUserSequences(String selectUserSequences)
- {
- this.selectUserSequences = selectUserSequences;
- }
-
- /**
- * {@inheritDoc}
- */
- public String getDescription()
- {
- return "Checking Sequences";
- }
-
- /**
- * {@inheritDoc}
- */
- public void execute(DataSource dataSource)
- {
- SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
- for (String sequenceName : sequences)
- {
- int result = 0;
-
- if (StringUtils.contains(sequenceName, "."))
- {
- String[] tokens = StringUtils.split(sequenceName, ".");
- result = jdbcTemplate.queryForInt(selectAllSequences, new Object[]{tokens[1], tokens[0]});
- }
- else
- {
- result = jdbcTemplate.queryForInt(selectUserSequences, sequenceName);
- }
-
- if (result <= 0)
- {
- log.info("Creating new {}", sequenceName);
- jdbcTemplate.update(MessageFormat.format(creationQuery, new Object[]{sequenceName, startsWith}));
- }
- }
- }
-}
\ No newline at end of file
Copied: tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java (from rev 682, trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java)
===================================================================
--- tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java (rev 0)
+++ tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleSequenceCreationTask.java 2008-02-21 21:03:49 UTC (rev 684)
@@ -0,0 +1,145 @@
+/*
+ * Copyright Openmind http://www.openmindonline.it
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package it.openutils.migration.oracle;
+
+import it.openutils.migration.task.setup.DbTask;
+
+import java.text.MessageFormat;
+import java.util.List;
+
+import javax.sql.DataSource;
+
+import org.apache.commons.lang.StringUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
+
+
+/**
+ * Db tasks that handles the initial setup of sequences.
+ * @author fgiust
+ * @version $Id$
+ */
+public class OracleSequenceCreationTask implements DbTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(OracleSequenceCreationTask.class);
+
+ private List<String> sequences;
+
+ private String creationQuery;
+
+ private String dropQuery;
+
+ private String selectUserSequences;
+
+ private String selectAllSequences;
+
+ private int startsWith;
+
+ /**
+ * Sets the sequences.
+ * @param sequences the sequences to set
+ */
+ public void setSequences(List<String> sequences)
+ {
+ this.sequences = sequences;
+ }
+
+ /**
+ * Sets the creationQuery.
+ * @param creationQuery the creationQuery to set
+ */
+ public void setCreationQuery(String creationQuery)
+ {
+ this.creationQuery = creationQuery;
+ }
+
+ /**
+ * Sets the dropQuery.
+ * @param dropQuery the dropQuery to set
+ */
+ public void setDropQuery(String dropQuery)
+ {
+ this.dropQuery = dropQuery;
+ }
+
+ /**
+ * Sets the selectAllSequences.
+ * @param selectAllSequences the selectAllSequences to set
+ */
+ public void setSelectAllSequences(String selectAllSequences)
+ {
+ this.selectAllSequences = selectAllSequences;
+ }
+
+ /**
+ * Sets the startsWith.
+ * @param startsWith the startsWith to set
+ */
+ public void setStartsWith(int startsWith)
+ {
+ this.startsWith = startsWith;
+ }
+
+ /**
+ * Sets the selectUserSequences.
+ * @param selectUserSequences the selectUserSequences to set
+ */
+ public void setSelectUserSequences(String selectUserSequences)
+ {
+ this.selectUserSequences = selectUserSequences;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public String getDescription()
+ {
+ return "Checking Sequences";
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+ for (String sequenceName : sequences)
+ {
+ int result = 0;
+
+ if (StringUtils.contains(sequenceName, "."))
+ {
+ String[] tokens = StringUtils.split(sequenceName, ".");
+ result = jdbcTemplate.queryForInt(selectAllSequences, new Object[]{tokens[1], tokens[0]});
+ }
+ else
+ {
+ result = jdbcTemplate.queryForInt(selectUserSequences, sequenceName);
+ }
+
+ if (result <= 0)
+ {
+ log.info("Creating new {}", sequenceName);
+ jdbcTemplate.update(MessageFormat.format(creationQuery, new Object[]{sequenceName, startsWith}));
+ }
+ }
+ }
+}
\ No newline at end of file
Copied: tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java (from rev 682, trunk/openutils-dbmigration/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java)
===================================================================
--- tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java (rev 0)
+++ tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/oracle/OracleViewCreateOrUpdateTask.java 2008-02-21 21:03:49 UTC (rev 684)
@@ -0,0 +1,242 @@
+package it.openutils.migration.oracle;
+
+import it.openutils.migration.task.setup.DbTask;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.text.MessageFormat;
+import java.util.List;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import javax.sql.DataSource;
+
+import org.apache.commons.io.IOUtils;
+import org.apache.commons.lang.StringUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.core.io.Resource;
+import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
+
+
+/**
+ * <p>
+ * Db tasks that handles the initial setup of views.
+ * </p>
+ * <p>
+ * Limitations:
+ * </p>
+ * <ol>
+ * <li> * not supported in field list</li>
+ * <li> fields must be enclosed in quotes</li>
+ * </ol>
+ * @author albertoq
+ * @version $Id$
+ */
+public class OracleViewCreateOrUpdateTask implements DbTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(OracleViewCreateOrUpdateTask.class);
+
+ /**
+ * Script list to execute
+ */
+ protected List<Resource> scripts;
+
+ /**
+ * Query to verify view existence
+ */
+ private String selectUserViewExistence;
+
+ /**
+ * Query to retrieve view ddl
+ */
+ private String selectUserViewDDL;
+
+ /**
+ * Statement to drop a view
+ */
+ private String dropView;
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+
+ SimpleJdbcTemplate jdbcTemplate = new SimpleJdbcTemplate(dataSource);
+ for (Resource script : scripts)
+ {
+ String viewName = this.objectNameFromFileName(script);
+
+ int result = jdbcTemplate.queryForInt(getSelectUserViewExistence(), viewName);
+
+ String scriptContent = readFully(script);
+
+ if (scriptContent == null)
+ {
+ continue;
+ }
+
+ if (result == 0)
+ {
+ log.info("View {} not existing. Creating new view", viewName);
+
+ createView(jdbcTemplate, scriptContent);
+ }
+ else
+ {
+ String scriptBody = extractViewBody(scriptContent);
+ if (scriptBody == null)
+ {
+ continue;
+ }
+
+ String previousDDl = (String) jdbcTemplate.getJdbcOperations().queryForObject(
+ getSelectUserViewDDL(),
+ new Object[]{viewName},
+ String.class);
+
+ if (!StringUtils.equals(previousDDl.trim(), scriptBody.trim()))
+ {
+ log.info(
+ "Previous definition of view {} differs from actual. Dropping and recreating view",
+ new Object[]{viewName});
+
+ jdbcTemplate.update(MessageFormat.format(getDropView(), new Object[]{viewName}));
+
+ createView(jdbcTemplate, scriptContent);
+ }
+ }
+ }
+
+ }
+
+ /**
+ * @param script The script resource
+ * @return The script name
+ */
+ protected String objectNameFromFileName(Resource script)
+ {
+ return StringUtils.substringBeforeLast(script.getFilename(), ".");
+ }
+
+ /**
+ * @param scriptContent
+ * @return
+ */
+ private String extractViewBody(String scriptContent)
+ {
+ Pattern pattern = Pattern.compile(".*\\s+AS\\s+(.*)", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
+ Matcher matcher = pattern.matcher(scriptContent);
+ boolean bodyFound = matcher.find();
+ if (bodyFound)
+ {
+ return matcher.group(1);
+ }
+ else
+ {
+ return null;
+ }
+ }
+
+ /**
+ * @param jdbcTemplate
+ * @param script
+ * @return
+ */
+ private void createView(SimpleJdbcTemplate jdbcTemplate, String script)
+ {
+
+ String[] ddls = StringUtils.split(script, ";");
+
+ for (String ddl : ddls)
+ {
+ if (StringUtils.isNotBlank(ddl))
+ {
+ log.debug("Executing:\n{}", ddl);
+ jdbcTemplate.update(ddl);
+ }
+ }
+ }
+
+ /**
+ * @param script
+ * @return
+ */
+ private String readFully(Resource script)
+ {
+ if (script == null || !script.exists())
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
+ return null;
+ }
+
+ String scriptContent;
+ InputStream is = null;
+
+ try
+ {
+ is = script.getInputStream();
+ scriptContent = IOUtils.toString(is, "UTF8");
+ }
+ catch (IOException e)
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" can't be read.", getDescription(), script);
+ return null;
+ }
+ finally
+ {
+ IOUtils.closeQuietly(is);
+ }
+ return scriptContent;
+ }
+
+ public String getDescription()
+ {
+ return "Checking Views";
+ }
+
+ public List<Resource> getScripts()
+ {
+ return scripts;
+ }
+
+ public void setScripts(List<Resource> scripts)
+ {
+ this.scripts = scripts;
+ }
+
+ public String getSelectUserViewExistence()
+ {
+ return selectUserViewExistence;
+ }
+
+ public void setSelectUserViewExistence(String selectUserViewExistence)
+ {
+ this.selectUserViewExistence = selectUserViewExistence;
+ }
+
+ public String getSelectUserViewDDL()
+ {
+ return selectUserViewDDL;
+ }
+
+ public void setSelectUserViewDDL(String selectUserViewDDL)
+ {
+ this.selectUserViewDDL = selectUserViewDDL;
+ }
+
+ public String getDropView()
+ {
+ return dropView;
+ }
+
+ public void setDropView(String dropView)
+ {
+ this.dropView = dropView;
+ }
+}
Deleted: tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java
===================================================================
--- trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2008-02-21 14:57:33 UTC (rev 681)
+++ tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2008-02-21 21:03:49 UTC (rev 684)
@@ -1,527 +0,0 @@
-/*
- * Copyright Openmind http://www.openmindonline.it
- *
- * Licensed under the Apache License, Version 2.0 (the "License");
- * you may not use this file except in compliance with the License.
- * You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-package it.openutils.migration.task.setup;
-
-import java.io.IOException;
-import java.io.InputStream;
-import java.sql.Connection;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Types;
-import java.util.ArrayList;
-import java.util.List;
-import java.util.Map;
-
-import javax.sql.DataSource;
-
-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;
-import org.apache.poi.hssf.usermodel.HSSFSheet;
-import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-import org.apache.poi.poifs.filesystem.POIFSFileSystem;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-import org.springframework.core.io.Resource;
-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;
-
-
-/**
- * @author fgiust
- * @version $Id$
- */
-public class ExcelConfigurationTask extends BaseDbTask implements DbTask
-{
-
- /**
- * Logger.
- */
- private Logger log = LoggerFactory.getLogger(ScriptBasedUnconditionalTask.class);
-
- private Resource script;
-
- private Map<String, ExcelConfigurationTask.QueryConfig> config;
-
- /**
- * If true, when a record already exists and an updated query is defined it will be updated. Set it to false to only
- * insert new records.
- */
- private boolean updateEnabled = true;
-
- /**
- * Sets the script.
- * @param script the script to set
- */
- public void setScript(Resource script)
- {
- this.script = script;
- }
-
- /**
- * Sets the config.
- * @param config the config to set
- */
- public void setConfig(Map<String, ExcelConfigurationTask.QueryConfig> config)
- {
- this.config = config;
- }
-
- /**
- * Sets the updateEnabled.
- * @param updateEnabled the updateEnabled to set
- */
- public void setUpdateEnabled(boolean updateEnabled)
- {
- this.updateEnabled = updateEnabled;
- }
-
- /**
- * {@inheritDoc}
- */
- public void execute(DataSource dataSource)
- {
- if (script == null || !script.exists())
- {
- log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
- return;
- }
-
- InputStream is = null;
- try
- {
- is = script.getInputStream();
- POIFSFileSystem fs = new POIFSFileSystem(is);
- HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
- int sheetNums = hssfworkbook.getNumberOfSheets();
- for (int j = 0; j < sheetNums; j++)
- {
- HSSFSheet sheet = hssfworkbook.getSheetAt(j);
- String tableName = hssfworkbook.getSheetName(j);
-
- QueryConfig conf = config.get(tableName);
- if (conf == null)
- {
- log.error("Unable to handle table {}", tableName);
- continue;
- }
- processSheet(sheet, tableName, conf, dataSource);
-
- }
-
- }
- catch (IOException e)
- {
- log.error(e.getMessage(), e);
- }
- finally
- {
- IOUtils.closeQuietly(is);
- }
-
- }
-
- /**
- * @param sheet
- * @param tableName
- */
- private void processSheet(HSSFSheet sheet, final String tableName, QueryConfig con, DataSource dataSource)
- {
- final List<String> columns = new ArrayList<String>();
-
- HSSFRow row = sheet.getRow(0);
- for (short k = 0; k < row.getLastCellNum(); k++)
- {
- HSSFCell cell = row.getCell(k);
- if (cell != null)
- {
- String columnName = cell.getStringCellValue();
- if (StringUtils.isNotBlank(columnName))
- {
- columns.add(StringUtils.trim(columnName));
- }
- else
- {
- break;
- }
- }
- }
-
- log.debug("Table: {}, Columns: {}", tableName, columns);
-
- final List<Integer> types = new ArrayList<Integer>();
-
- boolean result = (Boolean) new JdbcTemplate(dataSource).execute(new ConnectionCallback()
- {
-
- public Object doInConnection(Connection con) throws SQLException, DataAccessException
- {
- for (String column : columns)
- {
- ResultSet res = con.getMetaData().getColumns(null, null, tableName, column);
- if (res.next())
- {
- types.add(res.getInt("DATA_TYPE"));
- }
- else
- {
- log.warn("Unable to determine type for column '{}' in table '{}'", column, tableName);
- return false;
- }
- res.close();
- }
- return true;
- }
- });
-
- if (!result)
- {
- log.warn("Skipping sheet {} ", tableName);
- }
-
- 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,
- columns,
- ArrayUtils.toPrimitive(types.toArray(new Integer[types.size()]), Types.NULL),
- checkStatement,
- insertStatement,
- selectStatement,
- updateStatement,
- dataSource,
- tableName);
- }
-
- /**
- * @param sheet
- * @param columns
- * @param checkStatement
- * @param insertStatement
- * @param updateStatement
- * @param selectStatement
- */
- @SuppressWarnings("unchecked")
- private void processRecords(HSSFSheet sheet, List<String> columns, int[] types, String checkStatement,
- 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++)
- {
- row = sheet.getRow(rowNum);
- if (row == null)
- {
- return;
- }
-
- List<String> values = new ArrayList<String>();
-
- for (short k = 0; k < columns.size() && k <= row.getLastCellNum(); k++)
- {
- HSSFCell cell = row.getCell(k);
- String value = null;
-
- if (cell == null)
- {
- value = StringUtils.EMPTY;
- }
- else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
- {
- value = cell.getStringCellValue();
- }
- else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
- {
- double valueDouble = cell.getNumericCellValue();
- // when need to really check if it is a double or an int
- double fraction = valueDouble % 1;
- if (fraction == 0)
- {
- value = Integer.toString((int) valueDouble);
- }
- else
- {
- value = Double.toString(valueDouble);
- }
- }
-
- if (StringUtils.isEmpty(value))
- {
- value = StringUtils.EMPTY;
- }
-
- if ("<NULL>".equalsIgnoreCase(value))
- {
- value = null;
- }
-
- values.add(value);
- }
-
- Object[] checkParams = ArrayUtils.subarray(values.toArray(), 0, checkNum);
- for (int i = 0; i < checkParams.length; i++)
- {
- if (StringUtils.isEmpty((String) checkParams[i]))
- {
- return;
- }
- }
-
- int existing;
- try
- {
- existing = jdbcTemplate.queryForInt(checkStatement, checkParams);
- }
- catch (BadSqlGrammarException bsge)
- {
- log.error("Error executing check query, current sheet will be skipped. {} Query in error: {}", bsge
- .getMessage(), checkStatement);
- return;
- }
-
- if (existing == 0)
- {
- Object[] insertParams = ArrayUtils.subarray(values.toArray(), 0, insertNum);
- int[] insertTypes = ArrayUtils.subarray(types, 0, insertNum);
- if (log.isDebugEnabled())
- {
- log.debug("Missing record with key {}; inserting {}", ArrayUtils.toString(checkParams), ArrayUtils
- .toString(insertParams));
- }
-
- if (insertParams.length != insertTypes.length)
- {
- log.warn("Invalid number of param/type for table {}. Params: {}, types: {}", new Object[]{
- tableName,
- insertParams.length,
- insertTypes.length });
- }
-
- try
- {
- jdbcTemplate.update(insertStatement, insertParams, insertTypes);
- }
- catch (DataIntegrityViolationException bsge)
- {
- log
- .error(
- "Error executing update, record at {}:{} will be skipped. Query in error: '{}', values: {}. Error message: {}",
- new Object[]{
- tableName,
- rowNum + 1,
- insertStatement,
- ArrayUtils.toString(insertParams),
- bsge.getMessage() });
- continue;
- }
- }
- else if (updateEnabled
- && StringUtils.isNotBlank(updateStatement)
- && StringUtils.isNotBlank(selectStatement))
- {
- try
- {
- RowMapper rowMapper = new ColumnMapRowMapper();
- Object[] selectParams = ArrayUtils.subarray(values.toArray(), 0, selectNum);
- List<Map<String, Object>> selectResult = jdbcTemplate.query(
- selectStatement,
- selectParams,
- rowMapper);
- Map<String, Object> fetchedColumns = 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
- }
-
- }
- }
-
- /**
- * @author fgiust
- * @version $Id$
- */
- public static class QueryConfig
- {
-
- private String checkQuery;
-
- 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
- */
- public String getCheckQuery()
- {
- return checkQuery;
- }
-
- /**
- * Sets the checkQuery.
- * @param checkQuery the checkQuery to set
- */
- public void setCheckQuery(String checkQuery)
- {
- this.checkQuery = checkQuery;
- }
-
- /**
- * Returns the insertQuery.
- * @return the insertQuery
- */
- public String getInsertQuery()
- {
- return insertQuery;
- }
-
- /**
- * Sets the insertQuery.
- * @param insertQuery the insertQuery to set
- */
- public void setInsertQuery(String insertQuery)
- {
- 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;
- }
- }
-}
Copied: tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java (from rev 682, trunk/openutils-dbmigration/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java)
===================================================================
--- tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java (rev 0)
+++ tags/openutils-dbmigration-2.0.3/src/main/java/it/openutils/migration/task/setup/ExcelConfigurationTask.java 2008-02-21 21:03:49 UTC (rev 684)
@@ -0,0 +1,667 @@
+/*
+ * Copyright Openmind http://www.openmindonline.it
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package it.openutils.migration.task.setup;
+
+import java.io.IOException;
+import java.io.InputStream;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Types;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+
+import javax.sql.DataSource;
+
+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;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.poifs.filesystem.POIFSFileSystem;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.core.io.Resource;
+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;
+
+
+/**
+ * @author fgiust
+ * @version $Id$
+ */
+public class ExcelConfigurationTask extends BaseDbTask implements DbTask
+{
+
+ /**
+ * Logger.
+ */
+ private Logger log = LoggerFactory.getLogger(ScriptBasedUnconditionalTask.class);
+
+ private Resource script;
+
+ private Map<String, ExcelConfigurationTask.QueryConfig> config;
+
+ /**
+ * Enable this task.
+ */
+ private boolean enabled = true;
+
+ /**
+ * If true, when a record already exists and an updated query is defined it will be updated. Set it to false to only
+ * insert new records.
+ */
+ private boolean updateEnabled = true;
+
+ /**
+ * Date format for ISO dates
+ */
+ private SimpleDateFormat isodateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
+
+ /**
+ * Sets the script.
+ * @param script the script to set
+ */
+ public void setScript(Resource script)
+ {
+ this.script = script;
+ }
+
+ /**
+ * Sets the config.
+ * @param config the config to set
+ */
+ public void setConfig(Map<String, ExcelConfigurationTask.QueryConfig> config)
+ {
+ this.config = config;
+ }
+
+ /**
+ * Sets the enabled.
+ * @param enabled the enabled to set
+ */
+ public void setEnabled(boolean enabled)
+ {
+ this.enabled = enabled;
+ }
+
+ /**
+ * Sets the updateEnabled.
+ * @param updateEnabled the updateEnabled to set
+ */
+ public void setUpdateEnabled(boolean updateEnabled)
+ {
+ this.updateEnabled = updateEnabled;
+ }
+
+ /**
+ * {@inheritDoc}
+ */
+ public void execute(DataSource dataSource)
+ {
+ if (!enabled)
+ {
+ return;
+ }
+
+ if (script == null || !script.exists())
+ {
+ log.error("Unable to execute db task \"{}\", script \"{}\" not found.", getDescription(), script);
+ return;
+ }
+
+ InputStream is = null;
+ try
+ {
+ is = script.getInputStream();
+ POIFSFileSystem fs = new POIFSFileSystem(is);
+ HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
+ int sheetNums = hssfworkbook.getNumberOfSheets();
+ for (int j = 0; j < sheetNums; j++)
+ {
+ HSSFSheet sheet = hssfworkbook.getSheetAt(j);
+ String tableName = hssfworkbook.getSheetName(j);
+
+ QueryConfig conf = config.get(tableName);
+ if (conf == null)
+ {
+ suggestSheetConfig(sheet, tableName, conf, dataSource);
+ continue;
+ }
+ processSheet(sheet, tableName, conf, dataSource);
+
+ }
+
+ }
+ catch (IOException e)
+ {
+ log.error(e.getMessage(), e);
+ }
+ finally
+ {
+ IOUtils.closeQuietly(is);
+ }
+
+ }
+
+ public void suggestSheetConfig(HSSFSheet sheet, final String tableName, QueryConfig con, DataSource dataSource)
+ {
+ log.error("Unable to handle table {}", tableName);
+
+ if (!log.isDebugEnabled())
+ {
+ return;
+ }
+
+ final List<String> columns = new ArrayList<String>();
+
+ HSSFRow row = sheet.getRow(0);
+ for (short k = 0; k < row.getLastCellNum(); k++)
+ {
+ HSSFCell cell = row.getCell(k);
+ if (cell != null)
+ {
+ String columnName = cell.getStringCellValue();
+ if (StringUtils.isNotBlank(columnName))
+ {
+ columns.add(StringUtils.trim(columnName));
+ }
+ else
+ {
+ break;
+ }
+ }
+ }
+
+ if (columns.isEmpty())
+ {
+ return;
+ }
+
+ StringBuffer buffer = new StringBuffer();
+
+ buffer.append(" <entry key=\"");
+ buffer.append(tableName);
+ buffer.append("\">\n"
+ + " <bean class=\"it.openutils.migration.task.setup.ExcelConfigurationTask$QueryConfig\">\n"
+ + " <property name=\"checkQuery\">\n"
+ + " <value>");
+
+ String initialCol = columns.get(0);
+ buffer.append("select count(" + initialCol + ") from " + tableName + " where " + initialCol + " = ?");
+ buffer.append("</value>\n"
+ + " </property>\n"
+ + " <property name=\"insertQuery\">\n"
+ + " <value>");
+
+ buffer.append("INSERT INTO ");
+ buffer.append(tableName);
+ buffer.append(" (");
+
+ StringBuffer colNames = new StringBuffer();
+ StringBuffer parNames = new StringBuffer();
+
+ for (Iterator<String> iterator = columns.iterator(); iterator.hasNext();)
+ {
+ String string = iterator.next();
+ colNames.append(string);
+ parNames.append("?");
+ if (iterator.hasNext())
+ {
+ colNames.append(", ");
+ parNames.append(", ");
+ }
+
+ }
+
+ buffer.append(colNames);
+ buffer.append(") VALUES (");
+ buffer.append(parNames);
+ buffer.append(")");
+ buffer.append("</value>\n" + " </property>\n" + " </bean>\n" + " </entry>");
+
+ log.debug("You can use the following suggested config as template:\n{}", buffer.toString());
+ }
+
+ /**
+ * @param sheet
+ * @param tableName
+ */
+ private void processSheet(HSSFSheet sheet, final String tableName, QueryConfig con, DataSource dataSource)
+ {
+ final List<String> columns = new ArrayList<String>();
+
+ HSSFRow row = sheet.getRow(0);
+ for (short k = 0; k < row.getLastCellNum(); k++)
+ {
+ HSSFCell cell = row.getCell(k);
+ if (cell != null)
+ {
+ String columnName = cell.getStringCellValue();
+ if (StringUtils.isNotBlank(columnName))
+ {
+ columns.add(StringUtils.trim(columnName));
+ }
+ else
+ {
+ break;
+ }
+ }
+ }
+
+ log.debug("Table: {}, Columns: {}", tableName, columns);
+
+ final List<Integer> types = new ArrayList<Integer>();
+
+ boolean result = (Boolean) new JdbcTemplate(dataSource).execute(new ConnectionCallback()
+ {
+
+ public Object doInConnection(Connection con) throws SQLException, DataAccessException
+ {
+ for (String column : columns)
+ {
+ ResultSet res = con.getMetaData().getColumns(null, null, tableName, column);
+ if (res.next())
+ {
+ types.add(res.getInt("DATA_TYPE"));
+ }
+ else
+ {
+ log.warn("Unable to determine type for column '{}' in table '{}'", column, tableName);
+ return false;
+ }
+ res.close();
+ }
+ return true;
+ }
+ });
+
+ if (!result)
+ {
+ log.warn("Skipping sheet {} ", tableName);
+ }
+
+ 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,
+ columns,
+ ArrayUtils.toPrimitive(types.toArray(new Integer[types.size()]), Types.NULL),
+ checkStatement,
+ insertStatement,
+ selectStatement,
+ updateStatement,
+ dataSource,
+ tableName);
+ }
+
+ /**
+ * @param sheet
+ * @param columns
+ * @param checkStatement
+ * @param insertStatement
+ * @param updateStatement
+ * @param selectStatement
+ */
+ @SuppressWarnings("unchecked")
+ private void processRecords(HSSFSheet sheet, List<String> columns, int[] types, String checkStatement,
+ 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++)
+ {
+ row = sheet.getRow(rowNum);
+ if (row == null)
+ {
+ return;
+ }
+
+ List<String> values = new ArrayList<String>();
+
+ for (short k = 0; k < columns.size() && k <= row.getLastCellNum(); k++)
+ {
+ HSSFCell cell = row.getCell(k);
+ String value = null;
+
+ if (cell == null)
+ {
+ value = StringUtils.EMPTY;
+ }
+ else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
+ {
+ value = cell.getStringCellValue();
+ }
+ else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
+ {
+ double valueDouble = cell.getNumericCellValue();
+ // when need to really check if it is a double or an int
+ double fraction = valueDouble % 1;
+ if (fraction == 0)
+ {
+ value = Integer.toString((int) valueDouble);
+ }
+ else
+ {
+ value = Double.toString(valueDouble);
+ }
+ }
+
+ if (StringUtils.isEmpty(value))
+ {
+ value = StringUtils.EMPTY;
+ }
+
+ if ("<NULL>".equalsIgnoreCase(value))
+ {
+ value = null;
+ }
+
+ values.add(value);
+ }
+
+ Object[] checkParams = ArrayUtils.subarray(values.toArray(), 0, checkNum);
+ for (int i = 0; i < checkParams.length; i++)
+ {
+ if (StringUtils.isEmpty((String) checkParams[i]))
+ {
+ return;
+ }
+ }
+
+ int existing;
+ try
+ {
+ existing = jdbcTemplate.queryForInt(checkStatement, checkParams);
+ }
+ catch (BadSqlGrammarException bsge)
+ {
+ log.error("Error executing check query, current sheet will be skipped. {} Query in error: {}", bsge
+ .getMessage(), checkStatement);
+ return;
+ }
+
+ if (existing == 0)
+ {
+ Object[] insertParams = ArrayUtils.subarray(values.toArray(), 0, insertNum);
+ int[] insertTypes = ArrayUtils.subarray(types, 0, insertNum);
+
+ // empty strings must be converted to nulls if the columns is numeric or date
+ // Cannot convert class java.lang.String to SQL type requested due to java.lang.NumberFormatException -
+ // For input string: ""
+ for (int j = 0; j < insertTypes.length; j++)
+ {
+ int tip = insertTypes[j];
+ if (tip != Types.CHAR
+ && tip != Types.LONGNVARCHAR
+ && tip != Types.LONGVARCHAR
+ && tip != Types.NCHAR
+ && tip != Types.NVARCHAR
+ && tip != Types.VARCHAR
+ && "".equals(insertParams[j]))
+ {
+ insertParams[j] = null;
+ }
+
+ if (tip == Types.DATE || tip == Types.TIME || tip == Types.TIMESTAMP && insertParams[j] != null)
+ {
+ synchronized (isodateformat)
+ {
+ try
+ {
+ insertParams[j] = isodateformat.parse((String) insertParams[j]);
+ }
+ catch (ParseException e)
+ {
+ log.debug("Cannot parse date \"{}\"", insertParams[j]);
+ }
+ }
+ }
+ }
+
+ if (log.isDebugEnabled())
+ {
+ log.debug("Missing record with key {}; inserting {}", ArrayUtils.toString(checkParams), ArrayUtils
+ .toString(insertParams));
+ }
+
+ if (insertParams.length != insertTypes.length)
+ {
+ log.warn("Invalid number of param/type for table {}. Params: {}, types: {}", new Object[]{
+ tableName,
+ insertParams.length,
+ insertTypes.length});
+ }
+
+ try
+ {
+ jdbcTemplate.update(insertStatement, insertParams, insertTypes);
+ }
+ catch (DataIntegrityViolationException bsge)
+ {
+ log
+ .error(
+ "Error executing update, record at {}:{} will be skipped. Query in error: '{}', values: {}. Error message: {}",
+ new Object[]{
+ tableName,
+ rowNum + 1,
+ insertStatement,
+ ArrayUtils.toString(insertParams),
+ bsge.getMessage()});
+ continue;
+ }
+ }
+ else if (updateEnabled
+ && StringUtils.isNotBlank(updateStatement)
+ && StringUtils.isNotBlank(selectStatement))
+ {
+ try
+ {
+ RowMapper rowMapper = new ColumnMapRowMapper();
+ Object[] selectParams = ArrayUtils.subarray(values.toArray(), 0, selectNum);
+ List<Map<String, Object>> selectResult = jdbcTemplate.query(
+ selectStatement,
+ selectParams,
+ rowMapper);
+ Map<String, Object> fetchedColumns = selectResult.get(0);
+ int i = 0;
+ boolean updateNeeded = false;
+ for (String columnName : columns)
+ ...
[truncated message content] |
