datacleaning.java

数据仓库工具

/*

  Loader - tool for transfering data from one JDBC source to another and
  doing transformations during copy.

    Copyright (C) 2002-2003  Together

    This library is free software; you can redistribute it and/or
    modify it under the terms of the GNU Lesser General Public
    License as published by the Free Software Foundation; either
    version 2.1 of the License, or (at your option) any later version.

    This library 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
    Lesser General Public License for more details.

    You should have received a copy of the GNU Lesser General Public
    License along with this library; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

 Loader.java
 Date: 03.03.2003.
 @version 2.1 alpha
 @author:
 Radoslav Dutina [e-mail]

*/
package org.webdocwf.util.loader;

import java.sql.*;
import java.io.*;
import java.util.*;
import org.webdocwf.util.loader.logging.*;

/**
 *
 * DataCleaning class contain method for executing data cleaning process
 * @author Radoslav Dutina
 * @version 1.0
 */
public class DataCleaning {

  private Logger logger;
  private Statement stmt;
  private ResultSet rsetTarget;
  private ResultSet rs;
  private Hashtable colNamesDataTypes=new Hashtable();
  private Hashtable colNamesDataLenght=new Hashtable();
  private String currentTableName="";

  private Hashtable colNamesDataTypesUpdate=new Hashtable();
  private Hashtable colNamesDataLenghtUpdate=new Hashtable();
  private ResultSet rsUpdate;

  // Default values for log table
  private String logTableName="LOGTABLENAME";
  private String logTable="LOGTABLE";
  private String logColumnName="LOGCOLUMNNAME";
  private String logRowNumber="LOGROWNUMBER";
  private String logOriginalValue="LOGORIGINALVALUE";
  private String logNewValue="LOGNEWVALUE";
  private String logImportDefinitionName="LOGIMPORTDEFINITIONNAME";
  private String logOperationName="LOGOPERATIONNAME";
  private String logTypeName="LOGTYPENAME";

  private boolean logTableExists=false;
  private boolean checkIsDone=false;
  
  private ConfigReader configReader;


  /**
   * Constructor of DataCleaning class, without parameters.
   */
  public DataCleaning(ConfigReader configReader) {
    this.configReader = configReader;
  }

  /**
   * This method set the value of parameter logTableName
   * @param logTableName is value of parameter
   */
  public void setLogTableName(String logTableName){
    this.logTableName=logTableName;
  }

  /**
   * This method set the value of parameter logTable
   * @param logTable is value of parameter
   */
  public void setLogTable(String logTable){
    this.logTable=logTable;
  }

  /**
   * This method set the value of parameter logColumnName
   * @param logColumnName is value of parmeter
   */
  public void setLogColumnName(String logColumnName){
    this.logColumnName=logColumnName;
  }

  /**
   * This method set the value of parameter logRowNumber
   * @param logRowNumber is value of parameter
   */
  public void setLogRowNumber(String logRowNumber){
    this.logRowNumber=logRowNumber;
  }

  /**
   * This method set the value of parameter logOriginalValue
   * @param logOriginalValue is value of parameter
   */
  public void setLogOriginalValue(String logOriginalValue){
    this.logOriginalValue=logOriginalValue;
  }

  /**
   * This method set the value of parameter logNewValue
   * @param logNewValue is value of parameter
   */
  public void setLogNewValue(String logNewValue){
    this.logNewValue=logNewValue;
  }

  /**
   * This method set the value of parameter logImportDefinitionName
   * @param logImportDefinitionName is value of parameter
   */
  public void setLogImportDefinitionName(String logImportDefinitionName){
    this.logImportDefinitionName=logImportDefinitionName;
  }

  /**
   * This method set the value of parameter logOperationName
   * @param logOperationName is value of parameter
   */
  public void setLogOperationName(String logOperationName){
    this.logOperationName=logOperationName;
  }

  /**
   * This method set the value of parameter logTypeName
   * @param logTypeName is value of parameter
   */
  public void setLogTypeName(String logTypeName){
    this.logTypeName=logTypeName;
  }

  /**
   * This method read value from parameter logTableName
   * @return value of parameter
   */
  public String getLogTableName(){
    return this.logTableName;
  }

  /**
   * This method read value from parameter logTable
   * @return value of parameter
   */
  public String getLogTable(){
    return this.logTable;
  }

  /**
   * This method read value from parameter logColumnName
   * @return value of parameter
   */
  public String getLogColumnName(){
    return this.logColumnName;
  }

  /**
   * This method read value from parameter logRowNumber
   * @return value of parameter
   */
  public String getLogRowNumber(){
    return this.logRowNumber;
  }

  /**
   * This method read value from parameter logOriginalValue
   * @return value of parameter
   */
  public String getLogOriginalValue(){
    return this.logOriginalValue;
  }

  /**
   * This method read value from parameter logNewValue
   * @return value of parameter
   */
  public String getLogNewValue(){
    return this.logNewValue;
  }

  /**
   * This method read value from parameter logImportDefinitionName
   * @return value of parameter
   */
  public String getLogImportDefinitionName(){
    return this.logImportDefinitionName;
  }

  /**
   * This method read value from parameter logImportDefinitionName
   * @return value of parameter
   */
  public String getLogOperationName(){
    return this.logOperationName;
  }

  /**
   * This method read value from parameter logTypeName
   * @return value of parameter
   */
  public String getLogTypeName(){
    return this.logTypeName;
  }

  /**
   * This method write message in to log table if insert/update fails
   * @param tableName is name of the table which is in the process
   * @param conn is connection to target database (table)
   * @param rowNumber is current row number
   * @param typeOfInsert is the parameter which may be 'insert' or 'update'
   * @param msg is error message
   * @param importDefinitionName is name of the import definition job
   */
  public void cleaningInsert(String tableName, Connection conn, int rowNumber, String typeOfInsert,
                             String msg, String importDefinitionName) {
    this.logger.write("full","\tBecause DataCleaning features is turn on, some replacement were made:");
    this.logger.write("full","\t "+typeOfInsert+":FAILS: in table "+tableName+", in row "+(rowNumber+1)+" . "+
                         msg );
    try{
      String[] types = {"TABLE"};
      String catalogName=conn.getCatalog();
      stmt=conn.createStatement();
//      msg=msg.replaceAll("'","''");
      msg=Utils.replaceAll(msg, "'","''");
      ResultSet check=null;
      try{
        if(!checkIsDone){
          check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
          this.checkIsDone = true;
          this.logTableExists = check.next();
          check.close();
        }
      }catch(UnsupportedOperationException ex){
        String message="Error while trying to get meta data from target table."+"\n"+
            "\tMethod getMetaDatata().getTables() is not supported.";
        throw new SQLException(message);
      }
      if(this.logTableExists){
        stmt.executeUpdate("INSERT INTO "+getLogTableName()+" ("+
        getLogImportDefinitionName()+", "+getLogOperationName()+", "+getLogTypeName()+", "+
        getLogTable()+", "+getLogColumnName()+", "+getLogRowNumber()+", "+
        getLogOriginalValue()+", "+getLogNewValue()+
        ")"+
        " VALUES ("+
        "'"+importDefinitionName+"','"+typeOfInsert+"','ERROR','"+
        tableName+"','','"+(rowNumber+1)+"','"+
        msg+"','"+typeOfInsert+" FAILS'"+
        ")");
      }else{
        this.logger.write("full","\t "+getLogTableName()+" does not exists, or is invalid!");
      }
//      check.close();
      stmt.close();
    }catch(SQLException ex){
      this.logger.write("full","\t Error: "+ex.getMessage());
    }

  }


  /**
   * This method write message in to log table if some data in sql statements has value
   * @param tableName is name of the table which is in the process
   * @param columnName is the name of column in current table
   * @param replacement is new value of data
   * @param rowNumber is current row number
   * @param conn is connection to target database (table)
   * @param typeOfInsert is the parameter which may be 'insert' or 'update'
   * @param importDefinitionName is name of the import definition job
   */
  public void cleaningColumnValues(String tableName, String columnName, String replacement,
                                   int rowNumber, Connection conn, String typeOfInsert,
                                   String importDefinitionName){
    this.logger.write("full","\tBecause DataCleaning features is turn on, some replacement were made:");
    this.logger.write("full","\t "+typeOfInsert+":REPLACED VALUES: In table "+tableName+", column "+columnName+", value 'null' were replaced" );
    this.logger.write("full","\t with value '"+replacement+"' (row number "+(rowNumber+1)+")" );
    try{
      String[] types = {"TABLE"};
      String catalogName=conn.getCatalog();
      stmt=conn.createStatement();
      ResultSet check=null;
      try{
        if(!checkIsDone){
          check = conn.getMetaData().getTables(catalogName, null, getLogTableName(), types);
          this.checkIsDone = true;
          this.logTableExists = check.next();
          check.close();
        }
      }catch(UnsupportedOperationException ex){
        String message="Error while trying to get meta data from target table.";
        throw new SQLException(message);
      }
      if(this.logTableExists){
        stmt.executeUpdate("INSERT INTO "+getLogTableName()+" ("+
                                     getLogImportDefinitionName()+", "+getLogOperationName()+", "+getLogTypeName()+", "+
                                     getLogTable()+", "+getLogColumnName()+", "+getLogRowNumber()+", "+
                                     getLogOriginalValue()+", "+getLogNewValue()+
                                     ")"+
                                     " VALUES ("+
                                     "'"+importDefinitionName+"','"+typeOfInsert+"','REPLACE NULL VALUES','"+
                                     tableName+"','"+columnName+"','"+(rowNumber+1)+"','"+
                                     "null"+"','"+replacement+"'"+
                                     ")");
      }else
        this.logger.write("full","\t "+getLogTableName()+" does not exists, or is invalid!");
//      check.close();
      stmt.close();
    }catch(SQLException ex){
      this.logger.write("full","\t Error:"+ ex.getMessage());
    }
  }

  /**
   * This method write message in to log table if relations between table faild to
   * insert/update
   * @param tableName is name of the table which is in the process
   * @param columnName is the name of column in current table
   * @param replacement is new value of data
   * @param dataType is type of relation column
   * @param rowNumber is current row number
   * @param conn is connection to target database (table)
   * @param typeOfInsert represents type of operation (insert or update)
   * @param currentVersion represents update version
   * @param oid define if the oid logic is present
   * @param importDefinitionName is name of the import definition job
   * @return value of parameter
   */
  public String cleaningRelationValues(String tableName, String columnName,String replacement,
                                       String dataType,int rowNumber, Connection conn,
                                       String typeOfInsert, int currentVersion, boolean oid,
                                       String importDefinitionName, String versionColumnName)throws LoaderException{

    String[] types = {"TABLE"};
    String relValue="";