importdelimitedworker.java

eq跨平台查询工具源码 eq跨平台查询工具源码

/*
 * ImportDelimitedWorker.java
 *
 * Copyright (C) 2002, 2003, 2004, 2005, 2006 Takis Diakoumis
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or 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 General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 */


package org.executequery.gui.importexport;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;

import java.sql.BatchUpdateException;
import java.sql.SQLException;
import java.sql.Types;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.swing.JOptionPane;
import org.executequery.GUIUtilities;
import org.executequery.datasource.ConnectionDataSource;
import org.executequery.datasource.ConnectionManager;
import org.executequery.gui.browser.ColumnData;
import org.executequery.util.Log;
import org.underworldlabs.swing.util.SwingWorker;
import org.underworldlabs.util.MiscUtils;


/* ----------------------------------------------------------
 * CVS NOTE: Changes to the CVS repository prior to the 
 *           release of version 3.0.0beta1 has meant a 
 *           resetting of CVS revision numbers.
 * ----------------------------------------------------------
 */

/**
 *
 * @author   Takis Diakoumis
 * @version  $Revision: 1.13 $
 * @date     $Date: 2006/09/05 14:20:52 $
 */
public class ImportDelimitedWorker extends AbstractImportExportWorker {
    
    /** The <code>SwingWorker</code> object for this process */
    private SwingWorker worker;
    
    /** Whether we are halting on errors */
    private boolean haltOnError;
    
    /** 
     * Constructs a new instance with the specified parent object - an 
     * instance of <code>ImportExportDelimitedPanel</code>.
     *
     * @param the parent for this process
     */
    public ImportDelimitedWorker(ImportExportProcess parent,
                                 ImportExportProgressPanel importingDialog) {
        super(parent, importingDialog);
        transferData();
    }
    
    private void transferData() {
        reset();
        worker = new SwingWorker() {
            public Object construct() {
                return doWork();
            }
            public void finished() {
                String result = (String)get();
                setResult(result);
                printResults();
                setProgressStatus(-1);
                getParent().setProcessComplete(result == SUCCESS);
                GUIUtilities.scheduleGC();
            }
        };
        worker.start();
    }
    
    private Object doWork() {
        
        // the process result
        String processResult = null;
        
        // are we halting on any error
        int onError = getParent().getOnError();
        haltOnError = (onError == ImportExportProcess.STOP_TRANSFER);
        
        boolean isBatch = getParent().runAsBatchProcess();

        appendProgressText("Beginning import from delimited file process...");
        appendProgressText("Using connection: " + 
                getParent().getDatabaseConnection().getName());

        // ---------------------------------------
        // table specific counters
        
        // the table statement result
        int tableInsertCount = 0;

        // the records processed for this table
        int tableRowCount = 0;

        // the table commit count
        int tableCommitCount = 0;

        // ---------------------------------------
        // total import process counters

        // the current commit block size
        int commitCount = 0;

        // the total records inserted
        int totalInsertCount = 0;

        // the total records processed
        int totalRecordCount = 0;

        // the error count
        int errorCount = 0;
        
        // the current line number
        int lineNumber = 0;
        
        int rollbackSize = getParent().getRollbackSize();
        int rollbackCount = 0;
        
        FileReader fileReader = null;
        BufferedReader reader = null;
        SimpleDateFormat df = null;
        
        try {
            // retrieve the import files
            Vector files = getParent().getDataFileVector();
            int fileCount = files.size();

            // whether to trim whitespace
            boolean trimWhitespace = getParent().trimWhitespace();
            
            // whether this table has a date/time field
            boolean hasDate = false;

            // whether we are parsing date formats
            boolean parsingDates = getParent().parseDateValues();
            
            // column names are first row
            boolean hasColumnNames = getParent().includeColumnNames();

            // currently bound variables in the prepared statement
            Map<ColumnData,String> boundVariables = null;
            
            // ignored indexes of columns from the file
            List<Integer> ignoredIndexes = null;

            if (hasColumnNames) {
                boundVariables = new HashMap<ColumnData,String>();
                ignoredIndexes = new ArrayList<Integer>();
                appendProgressText(
                        "Using column names from input file's first row.");
            }

            // columns to be imported that are in the file
            Map<ColumnData,String> fileImportedColumns = new HashMap<ColumnData,String>();

            // whether the data format failed (switch structure)
            boolean failed = false;
            
            // define the delimiter
            char charDelim = getParent().getDelimiter();
            String delim = Character.toString(charDelim);

            // ---------------------------
            // --- initialise counters ---
            // ---------------------------
            
            // the table's column count
            int columnCount = -1;

            // the length of each line in the file
            int rowLength = -1;

            // progress bar values
            int progressStatus = -1;

            // ongoing progress value
            int progressCheck = -1;

            // the import file size
            long fileSize = -1;
            
            // set the date format
            String dateFormatString = getParent().getDateFormat();
            if (dateFormatString != null) {
                try {
                    df = new SimpleDateFormat(dateFormatString);
                } catch (Exception e) { // catch illegal patterns here
                    errorCount++;
                    outputExceptionError("Error applying date mask", e);
                    return FAILED;
                }
            }
            
            // record the start time
            start();

            // setup the regex matcher for delims
            
            // ----------------------------------------------------------------
            // below was the original pattern from oreilly book.
            // discovered issues when parsing values with quotes 
            // in them - not only around them.
            /*
            String regex = 
                    "(?:^|\\" + 
                    delim +
                    ") (?: \" ( (?> [^\"]*+ ) (?> \"\" [^\"]*+ )*+ ) \" | ( [^\"\\" +
                    delim + "]*+ ) )";
            Matcher matcher = Pattern.compile(regex, Pattern.COMMENTS).matcher("");
            Matcher qMatcher = Pattern.compile("\"\"", Pattern.COMMENTS).matcher("");
            */
            // ----------------------------------------------------------------

            // modified above to regex below
            // added the look-ahead after the close quote
            // and removed the quote from the last regex pattern
            String regex = 
                    "(?:^|\\" + 
                    delim +
                    ") (?: \" ( (?> [^\"]*+ ) (?> \"\" [^\"]*+ )*+ ) \"(?=\\" + 
                    delim +
                    ") | ( [^\\" +
                    delim + "]*+ ) )";

            // ----------------------------------------------------------------
            // changed above to the following - seems to work for now
            // regex pattern in full - where <delim> is the delimiter to use
            //      \"([^\"]+?)\"<delim>?|([^<delim>]+)<delim>?|<delim>
            //
            // fixed oreilly one - not running this one
            // ----------------------------------------------------------------
            
            Matcher matcher = Pattern.compile(regex, Pattern.COMMENTS).matcher("");
            Matcher qMatcher = Pattern.compile("\"\"", Pattern.COMMENTS).matcher("");
            
            // ----------------------------------------
            // --- begin looping through the tables ---
            // ----------------------------------------
            
            // ensure the connection has auto-commit to false
            conn = getConnection();
            conn.setAutoCommit(false);

            int currentRowLength = 0;
            boolean insertLine = false;

            // the number of columns actually available in the file
            int filesColumnCount = 0;
            
            for (int i = 0; i < fileCount; i++) {

                lineNumber = 0;
                tableInsertCount = 0;
                tableCommitCount = 0;
                rollbackCount = 0;
                tableRowCount = 0;
                rowLength = 0;

                if (Thread.interrupted()) {
                    setProgressStatus(100);
                    throw new InterruptedException();
                }

                tableCount++;
                
                DataTransferObject dto = (DataTransferObject)files.elementAt(i);

                // initialise the file object
                File inputFile = new File(dto.getFileName());
                
                outputBuffer.append("---------------------------\nTable: ");
                outputBuffer.append(dto.getTableName());
                outputBuffer.append("\nImport File: ");
                outputBuffer.append(inputFile.getName());
                appendProgressText(outputBuffer);

                // setup the reader objects
                fileReader = new FileReader(inputFile);
                reader = new BufferedReader(fileReader);

                // retrieve the columns to be imported (or all)
                Vector<ColumnData> columns = getColumns(dto.getTableName());
                columnCount = columns.size();
                filesColumnCount = columnCount;

                // the wntire row read
                String row = null;

                // the current delimited value
                String value = null;

                // the ignored column count
                int ignoredCount = 0;
                
                // clear the file columns cache
                fileImportedColumns.clear();
                
                // if the first row in the file has the column
                // names compare these with the columns selected
                if (hasColumnNames) {
                    // init the bound vars cache with the selected columns