BUG: handle spring jdbc template batch insert failed will not rollback.

git-svn-id: https://table-data-copier.googlecode.com/svn/trunk@10 fa5e0b39-550b-3b35-3821-e6856afb3cb5
usc · Jan 7, 2012 · 07ec850 · 07ec850
1 parent 93f17ff
commit 07ec850
Show file tree

Hide file tree

Showing 3 changed files with 21 additions and 40 deletions.
diff --git a/src/main/java/com/googlecode/usc/TableDataCopier.java b/src/main/java/com/googlecode/usc/TableDataCopier.java
@@ -310,15 +310,18 @@ private void buttonActionPerformed() {
 
     @SuppressWarnings("unchecked")
     private void copy() {
-        boolean hasException = false;
         // clear
         results.setText("");
 
         String criteria = textField_4_0.getText();
-        String deleteSql = "Delete FROM " + criteria.split("\\s", 2)[0];
+        String tableName = criteria.split("\\s", 2)[0];
+
+        String deleteSql = "Delete FROM " + tableName;
+        String countSql = "select count(1) from " + tableName;
         String selectSql = "SELECT * FROM " + criteria;
 
         int updateNums = 0;
+        boolean hasException = false;
         Timer timer = new Timer();
         StopWatch stopWatch = new StopWatch();
 
@@ -355,6 +358,9 @@ private void copy() {
                 showResults("\nSelect Table ");
             }
 
+            // start count
+            updateNums = jdbcTemplateTo.queryForInt(countSql);
+
             // select all
             if (!IS_DELETE_ORIGINAL_DATA) {
                 showResults("Select Table ");
@@ -378,7 +384,7 @@ private void copy() {
             stopWatch.start("batch insert");
             for (int i = 0; i * BATCH_SIZE < size; i++) {
                 if (i == 0) {
-                    egiOgInsertSql = Utils.buildInsertSql(selectList.get(0).keySet(), criteria);
+                    egiOgInsertSql = Utils.buildInsertSql(selectList.get(0).keySet(), tableName);
                     logger.info("Insert sql is {}", egiOgInsertSql);
                 }
 
@@ -393,28 +399,20 @@ private void copy() {
                 try {
                     // batch insert
                     jdbcTemplateTo.batchUpdate(egiOgInsertSql, subEgiList.toArray(new Map[0]));
-                    updateNums += subEgiList.size();
                 } catch (Exception e) {
                     hasException = true;
+                    exceptionHandler(e);
 
-                    if (!ABORT_WHEN_ABNORMAL_INSERT) {
-                        // if wrong, per insert
-                        for (int j = 0; j < subEgiList.size(); j++) {
-                            try {
-                                jdbcTemplateTo.update(egiOgInsertSql, subEgiList.get(j));
-                                updateNums++;
-                            } catch (Exception e2) {
-                                exceptionHandler("Insert failed at " + String.format(LINE_FORMAT, i * BATCH_SIZE + j + 1) + " line record", e2);
-                            }
-                        }
-                    } else {
-                        exceptionHandler(e);
+                    if (ABORT_WHEN_ABNORMAL_INSERT) {
                         break;
                     }
                 }
             }
             stopWatch.stop();
 
+            // end count
+            updateNums = jdbcTemplateTo.queryForInt(countSql) - updateNums;
+
             showResults("\n----------------------Result----------------------\n");
             for (StopWatch.TaskInfo taskInfo : stopWatch.getTaskInfo()) {
                 showResults(taskInfo.getTaskName() + ",escaped time " + taskInfo.getTimeMillis() + " ms\n");
@@ -430,7 +428,7 @@ private void copy() {
                 stopWatch.stop();
             }
             STOP = true;
-            logger.info("Successfully update {} records", updateNums);
+            logger.info("Successfully insert {} records", updateNums);
 
             if (IS_OPEN_LOG_FILE || hasException) {
                 Timer timer2 = new Timer();
@@ -500,16 +498,10 @@ private void exceptionHandler(Exception e) {
         if (Utils.isBlank(results.getText())) {
             results.setText("[ERROR] " + message);
         } else {
-            results.setText(results.getText() + "\n[ERROR] " + message + " please see log file or contact me(QQ.506817493).Thanks. ");
+            results.setText(results.getText() + "\n[ERROR] " + message + "\nplease see log file or contact me(QQ.506817493).Thanks. ");
         }
     }
 
-    private void exceptionHandler(String errorMsg, Exception e) {
-        showResults("\n[ERROR] " + errorMsg);
-        logger.error(errorMsg);
-        logger.error(e.getMessage());
-    }
-
     private class PrintTimerTask extends java.util.TimerTask {
         @Override
         public void run() {

diff --git a/src/main/java/com/googlecode/usc/Utils.java b/src/main/java/com/googlecode/usc/Utils.java
@@ -25,8 +25,8 @@ public static boolean isBlank(CharSequence cs) {
         return true;
     }
 
-    public static String buildInsertSql(Set<String> columnNames, String criteria) {
-        StringBuffer insertSql = new StringBuffer("INSERT INTO " + criteria.split("\\s", 2)[0] + " (");
+    public static String buildInsertSql(Set<String> columnNames, String tableName) {
+        StringBuffer insertSql = new StringBuffer("INSERT INTO " + tableName + " (");
         insertSql.append(buildParams(columnNames, ""));
         insertSql.append(") VALUES (");
         insertSql.append(buildParams(columnNames, ":"));

diff --git a/src/test/java/com/googlecode/usc/UtilsTest.java b/src/test/java/com/googlecode/usc/UtilsTest.java
@@ -37,23 +37,13 @@ public void testIsBlank3() {
     }
 
     @Test
-    public void testBuildInsertSqlNoWhereStatement() {
-        String criteria = "student";
+    public void testBuildInsertSql() {
+        String tableName = "student";
         Set<String> columnNames = new HashSet<String>();
         columnNames.add("name");
         columnNames.add("age");
 
-        assertEquals("INSERT INTO student (age, name) VALUES (:age, :name)", Utils.buildInsertSql(columnNames, criteria).toString().trim());
-    }
-
-    @Test
-    public void testBuildInsertSqlWithWhereStatement() {
-        String criteria = "student where name = 'lishunli'";
-        Set<String> columnNames = new HashSet<String>();
-        columnNames.add("name");
-        columnNames.add("age");
-
-        assertEquals("INSERT INTO student (age, name) VALUES (:age, :name)", Utils.buildInsertSql(columnNames, criteria).toString().trim());
+        assertEquals("INSERT INTO student (age, name) VALUES (:age, :name)", Utils.buildInsertSql(columnNames, tableName).toString().trim());
     }
 
     @Test
@@ -107,7 +97,6 @@ public void testLoadPropertiesFileByStreamAbnormal() {
         assertNull(prop.get("version"));
     }
 
-
     @Test
     public void testLoadPropertiesFileByPathAndStreamNormal() {
         Properties config = Utils.loadPropertiesFile(