com/rapide_act/RapideLoader.java

   1 package com.rapide_act;
   2
   3 import java.io.BufferedInputStream;
   4 import java.io.BufferedReader;
   5 import java.io.BufferedWriter;
   6 import java.io.FileInputStream;
   7 import java.io.InputStream;
   8 import java.io.InputStreamReader;
   9 import java.io.IOException;
  10 import java.io.File;
  11 import java.io.FileReader;
  12 import java.io.FileWriter;
  13 import java.io.PrintWriter;
  14 import java.io.PrintWriter;
  15 import java.util.Date;
  16 import java.util.ArrayList;
  17 import java.util.Iterator;
  18 import java.util.Properties;
  19 import java.sql.Blob;
  20 import java.sql.Clob;
  21 import java.sql.Connection;
  22 import java.sql.DriverManager;
  23 import java.sql.PreparedStatement;
  24 import java.sql.ResultSet;
  25 import java.sql.ResultSetMetaData;
  26 import java.sql.SQLException;
  27 import java.sql.Timestamp;
  28 import java.math.BigDecimal;
  29
  30 public class RapideLoader{
  31         private String database = null;
  32         private String user = null;
  33         private String pass = null;
  34         private String url = null;
  35         private String driver = null;
  36         private String sql_table_list = null;
  37         private String sql_table_key_list = null;
  38         private static final int DB_TYPE_ORACLE = 0;
  39         private static final int DB_TYPE_SQLSERVER = 1;
  40         private static final int DB_TYPE_DB2 = 2;
  41         private static final int DB_TYPE_MYSQL = 3;
  42         private static final int DB_TYPE_POSTGRESQL = 4;
  43         private static final int DB_TYPE_UNKNOWN = -1;
  44         private static final String [] DB_TYPE_NAME = {"ORACLE", "SQLSERVER", "DB2", "MYSQL", "POSTGRESQL"};
  45         private static final int MASK_PTN_ALL = 0;
  46         private static final int MASK_PTN_ALT = 1;
  47         private static final int MASK_PTN_EDGE = 2;
  48         private static final int D_USER = 0;
  49         private static final int D_PASS = 1;
  50         private static final int D_URL = 2;
  51         private static final int D_DRIVER = 3;
  52         private static final int D_OUTPUT = 4;
  53         private static final int D_INPUT = 5;
  54         private static final int D_LINESEP = 6;
  55         private static final int D_INLINESEP = 7;
  56         private static final int D_FILENCODE = 8;
  57         private static final int D_NULLMARK = 9;
  58         private static final int D_DELIMITER = 10;
  59         private static final int D_TBLSQL = 11;
  60         private static final int D_TBLKEYSQL = 12;
  61         private static final int D_ORDER = 13;
  62         private static final int D_QUOTATION = 14;
  63         private static final int D_BATCOUNT = 15;
  64         private static final int D_INCLTAB = 16;
  65         private static final int D_EXCLTAB = 17;
  66         private static final int D_INCLCOL = 18;
  67         private static final int D_EXCLCOL = 19;
  68         private static final int D_INCLCOLMASK = 20;
  69         private static final int D_EXCLCOLMASK = 21;
  70         private static final int D_SBYTEMASKCHR = 22;
  71         private static final int D_DBYTEMASKCHR = 23;
  72         private static final int D_MASKPTN = 24;
  73         private static final int D_PROPFILE = 25;
  74         private static final int KEYS = 26;
  75         private static final String [] PROP_KEY = { "us","pw","url","dr","out","in","ls","ils","fe","nm",
  76                                                                                                 "dm","ts","tks","od","qt","bc","it","et","ic","ec",
  77                                                                                                 "icm","ecm","smc","dmc","mp","pf"};
  78
  79         private String [] prop_val = new String[KEYS];
  80         private static final String [] MASK_PTN = {"ALL", "ALT", "EDGE"};
  81         private static final String DEFAULT_IN_FOLDER = "input";
  82         private static final String DEFAULT_PROP_FILE = "RapideLoader.properties";
  83         private static String [] sql_table_list_array = {
  84                 "select "
  85                 + " table_name "
  86                 + " from user_tables "
  87                 + " order by table_name",
  88                 "select "
  89                 + " name as table_name "
  90                 + " from sys.tables "
  91                 + " order by name"
  92         };
  93
  94         private boolean isLob                                   = false;
  95         private boolean isContinue                              = false;
  96         private String nullMark                                 = "";
  97         private boolean isOrder                                 = false;
  98         private boolean isQuotation                     = true;
  99         private int batchCount                                  = 1000;
 100         private boolean isInclTables                    = false;
 101         private boolean isExclTables                    = false;
 102         private boolean isInclCols                              = false;
 103         private boolean isExclCols                              = false;
 104         private boolean isMask                                  = false;
 105         private boolean isInclColsMask                  = false;
 106         private boolean isExclColsMask                  = false;
 107         private String delimiter                                = "\t";
 108         private String lineSeparator                    = System.getProperty("line.separator");
 109         private String inColLineSeparator               = System.getProperty("line.separator");
 110         private String outFolder                                = null;
 111         private String inFolder                                 = null;
 112         private String inclTables                               = null;
 113         private String exclTables                               = null;
 114         private String inclCols                                 = null;
 115         private String exclCols                                 = null;
 116         private String inclColsMask                     = null;
 117         private String exclColsMask                     = null;
 118         private String [] inclTablesArray               = null;
 119         private String [] exclTablesArray               = null;
 120         private String [] inclColsArray                         = null;
 121         private String [] exclColsArray                         = null;
 122         private String [] inclColsMaskArray             = null;
 123         private String [] exclColsMaskArray             = null;
 124         private char singleByteMaskChar                 = '*';
 125         private char doubleByteMaskChar                 = '\u25A0';
 126         private String maskPattern                              = "ALT";
 127         private String fileEncoding                     = System.getProperty("file.encoding");
 128
 129         public static void main(String args[]){
 130                 try {
 131                         if (args.length > 0){
 132                                 RapideLoader rapideLoader = new RapideLoader(args[0]);
 133                                 rapideLoader.load();
 134                         } else {
 135                                 RapideLoader rapideLoader = new RapideLoader(null);
 136                                 rapideLoader.load();
 137                         }
 138                 } catch (Exception e) {
 139                         e.printStackTrace();
 140                 }
 141         }
 142
 143         private void getProperties(String _propFile) throws Exception{
 144                 BufferedReader br = null;
 145                 Properties prop = null;
 146                 try {
 147                         prop = new Properties();
 148                         br = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream(_propFile), "UTF-8"));
 149                         prop.load(br);
 150                         for(int i=0;i<KEYS;i++){
 151                                 prop_val[i] = prop.getProperty(database + "." +PROP_KEY[i]);
 152                         }
 153                         br.close();
 154                         br = null;
 155                 } catch (NullPointerException e) {
 156                                 throw new Exception("Property File (" + _propFile +") Not Found");
 157                 } catch (IOException e) {
 158                                 throw e;
 159                 }
 160                 finally{
 161                         try {
 162                                 if (br != null) {
 163                                         br.close();
 164                                         br = null;
 165                                 }
 166                         }
 167                         catch (IOException e) {
 168                                 throw e;
 169                         }
 170                 }
 171         }
 172
 173
 174         RapideLoader(String _database) throws Exception{
 175                 super();
 176                 database = _database;
 177         }
 178
 179         private void load() throws Exception{
 180                 Connection conn                                 = null;
 181                 PreparedStatement stmt                  = null;
 182                 PreparedStatement stmt_ins              = null;
 183                 BufferedReader br                               = null;
 184                 String sql_load                                 = null;
 185                 String sql_insert                               = null;
 186                 int dbType                                              = DB_TYPE_UNKNOWN;
 187                 String strLine                                  = null;
 188                 String strMltLine                               = null;
 189                 String tbName                                   = null;
 190                 String [] flName                                = null;
 191                 String [] colData                               = null;
 192                 String [] colName                               = null;
 193                 String [] colTypeName                   = null;
 194                 Integer [] isNullable                   = null;
 195                 Boolean [] colIncl                              = null;
 196
 197                 File fldr                                               = null;
 198                 ArrayList<String> alData                = null;
 199                 ArrayList<String> alColName     = null;
 200                 ArrayList<String> alColTypeName = null;
 201                 ArrayList<Integer> alIsNullable = null;
 202                 ArrayList<Boolean> alColMask    = null;
 203                 ArrayList<Boolean> alColIncl    = null;
 204
 205                 int tb_count                                    = 0;
 206                 int tb_col_count                                = 0;
 207                 int fl_col_count                                = 0;
 208
 209
 210
 211
 212                 if(database != null){
 213                         if(!CmnUtils.isEmpty(prop_val[D_PROPFILE])){
 214                                 getProperties(System.getProperty(prop_val[D_PROPFILE]));
 215                         } else {
 216                                 getProperties(DEFAULT_PROP_FILE);
 217                         }
 218                 }
 219
 220                 for(int i=0;i<KEYS;i++){
 221                         if(System.getProperty(PROP_KEY[i])!=null)prop_val[i] = System.getProperty(PROP_KEY[i]);
 222                         CmnUtils.debugPrint(PROP_KEY[i] + "=" + prop_val[i]);
 223                 }
 224
 225                 if(!CmnUtils.isEmpty(prop_val[D_USER]))user = prop_val[D_USER];
 226                 if(!CmnUtils.isEmpty(prop_val[D_PASS]))pass = prop_val[D_PASS];
 227                 if(!CmnUtils.isEmpty(prop_val[D_URL]))url = prop_val[D_URL];
 228                 if(!CmnUtils.isEmpty(prop_val[D_DRIVER]))driver = prop_val[D_DRIVER];
 229                 if(!CmnUtils.isEmpty(prop_val[D_OUTPUT]))outFolder = prop_val[D_OUTPUT];
 230                 if(!CmnUtils.isEmpty(prop_val[D_INPUT]))inFolder = prop_val[D_INPUT];
 231                 if(!CmnUtils.isEmpty(prop_val[D_LINESEP]))lineSeparator = CmnUtils.getLineSeparator(prop_val[D_LINESEP]);
 232                 if(!CmnUtils.isEmpty(prop_val[D_INLINESEP]))inColLineSeparator = CmnUtils.getLineSeparator(prop_val[D_INLINESEP]);
 233                 if(!CmnUtils.isEmpty(prop_val[D_FILENCODE]))fileEncoding = prop_val[D_FILENCODE];
 234                 if(!CmnUtils.isEmpty(prop_val[D_NULLMARK]))nullMark = prop_val[D_NULLMARK];
 235                 if(!CmnUtils.isEmpty(prop_val[D_DELIMITER]))delimiter = CmnUtils.getDelimiter(prop_val[D_DELIMITER]);
 236                 if(!CmnUtils.isEmpty(prop_val[D_TBLSQL]))sql_table_list = prop_val[D_TBLSQL];
 237                 if(!CmnUtils.isEmpty(prop_val[D_TBLKEYSQL]))sql_table_key_list = prop_val[D_TBLKEYSQL];
 238                 if(!CmnUtils.isEmpty(prop_val[D_ORDER]) && prop_val[D_ORDER].toUpperCase().equals("Y"))isOrder = true;
 239                 if(!CmnUtils.isEmpty(prop_val[D_QUOTATION]) && prop_val[D_QUOTATION].toUpperCase().equals("N"))isQuotation = false;
 240                 if(!CmnUtils.isEmpty(prop_val[D_BATCOUNT]))batchCount = Integer.parseInt(prop_val[D_BATCOUNT]);
 241                 if(!CmnUtils.isEmpty(prop_val[D_INCLTAB]))isInclTables = true;
 242                 if(!CmnUtils.isEmpty(prop_val[D_EXCLTAB]))isExclTables = true;
 243                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOL]))isInclCols = true;
 244                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOL]))isExclCols = true;
 245                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOLMASK]))isInclColsMask = true;
 246                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOLMASK]))isExclColsMask = true;
 247                 if(isInclColsMask || isExclColsMask)isMask = true;
 248                 if(!CmnUtils.isEmpty(prop_val[D_INCLTAB]))inclTables = prop_val[D_INCLTAB].toUpperCase();
 249                 if(!CmnUtils.isEmpty(prop_val[D_EXCLTAB]))exclTables = prop_val[D_EXCLTAB].toUpperCase();
 250                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOL]))inclCols = prop_val[D_INCLCOL].toUpperCase();
 251                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOL]))exclCols = prop_val[D_EXCLCOL].toUpperCase();
 252                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOLMASK]))inclColsMask = prop_val[D_INCLCOLMASK].toUpperCase();
 253                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOLMASK]))exclColsMask = prop_val[D_EXCLCOLMASK].toUpperCase();
 254                 if(!CmnUtils.isEmpty(prop_val[D_INCLTAB]))inclTablesArray = CmnUtils.splitCsv(prop_val[D_INCLTAB].toUpperCase());
 255                 if(!CmnUtils.isEmpty(prop_val[D_EXCLTAB]))exclTablesArray = CmnUtils.splitCsv(prop_val[D_EXCLTAB].toUpperCase());
 256                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOL]))inclColsArray = CmnUtils.splitCsv(prop_val[D_INCLCOL].toUpperCase());
 257                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOL]))exclColsArray = CmnUtils.splitCsv(prop_val[D_EXCLCOL].toUpperCase());
 258                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOLMASK]))inclColsMaskArray = CmnUtils.splitCsv(prop_val[D_INCLCOLMASK].toUpperCase());
 259                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOLMASK]))exclColsMaskArray = CmnUtils.splitCsv(prop_val[D_EXCLCOLMASK].toUpperCase());
 260                 if(!CmnUtils.isEmpty(prop_val[D_SBYTEMASKCHR]))singleByteMaskChar = prop_val[D_SBYTEMASKCHR].charAt(0);
 261                 if(!CmnUtils.isEmpty(prop_val[D_DBYTEMASKCHR]))doubleByteMaskChar = prop_val[D_DBYTEMASKCHR].charAt(0);
 262                 if(!CmnUtils.isEmpty(prop_val[D_MASKPTN]))maskPattern = prop_val[D_MASKPTN];
 263
 264                 if(user == null){
 265                         throw new Exception("user is null");
 266                 } else if (pass == null){
 267                         throw new Exception("pass is null");
 268                 } else if (url == null){
 269                         throw new Exception("url is null");
 270                 } else if (driver == null){
 271                         throw new Exception("driver is null");
 272                 }
 273
 274                 for(int i = 0;i<DB_TYPE_NAME.length;i++){
 275                         if (url.toUpperCase().contains(DB_TYPE_NAME[i])){
 276                                 dbType = i;
 277                                 break;
 278                         }
 279                 }
 280
 281                 if (dbType != DB_TYPE_UNKNOWN){
 282                         if(!CmnUtils.isEmpty(prop_val[D_TBLSQL])){
 283                                 sql_table_list = prop_val[D_TBLSQL];
 284                         } else {
 285                                 sql_table_list = sql_table_list_array[dbType];
 286                         }
 287                 } else {
 288                         throw new Exception("dbtype unknown");
 289                 }
 290
 291                 DataAccessObjects dao = new DataAccessObjects(user, pass, url, driver);
 292                 try {
 293                         conn = dao.connect();
 294                         conn.setAutoCommit(false);
 295
 296                         if(inFolder != null){
 297                                 fldr = new File(inFolder);
 298                         } else {
 299                                 if (database != null){
 300                                         fldr = new File(DEFAULT_IN_FOLDER + "/" + database.toUpperCase());
 301                                 } else {
 302                                         fldr = new File(DEFAULT_IN_FOLDER + "/" + DB_TYPE_NAME[dbType].toUpperCase());
 303                                 }
 304                         }
 305                         CmnUtils.infoPrint("-->\91Î\8fÛ\83f\81[\83^\83x\81[\83X='" + database.toUpperCase() + "'");
 306                         CmnUtils.infoPrint("-->\93ü\97Í\8c³='" + fldr + "'");
 307                         CmnUtils.infoPrint("-->\8bæ\90Ø\82è\95¶\8e\9a='" + delimiter + "'");
 308                         CmnUtils.infoPrint("-->\83o\83b\83`\83J\83E\83\93\83g=" + batchCount);
 309                         CmnUtils.infoPrint("-->\88ø\97p\8bå=" + CmnUtils.getYesNo(isQuotation));
 310                         if(isInclTables)CmnUtils.infoPrint("-->\91Î\8fÛ\83e\81[\83u\83\8b='" + inclTables.toUpperCase() + "'");
 311                         if(isExclTables)CmnUtils.infoPrint("-->\8f\9c\8aO\83e\81[\83u\83\8b='" + exclTables.toUpperCase() + "'");
 312                         if(isInclCols)CmnUtils.infoPrint("-->\91Î\8fÛ\83J\83\89\83\80='" + inclCols.toUpperCase() + "'");
 313                         if(isExclCols)CmnUtils.infoPrint("-->\8f\9c\8aO\83J\83\89\83\80='" + exclCols.toUpperCase() + "'");
 314                         CmnUtils.infoPrint("-->\83}\83X\83L\83\93\83O=" +  CmnUtils.getYesNo(isMask));
 315                         if(isMask){
 316                                 CmnUtils.infoPrint("-->\83}\83X\83N\83L\83\93\83O\83p\83^\81[\83\93=" + maskPattern.toUpperCase());
 317                                 if(isInclColsMask)CmnUtils.infoPrint("-->\83}\83X\83N\91Î\8fÛ\83J\83\89\83\80='" + inclColsMask.toUpperCase() + "'");
 318                                 if(isExclColsMask)CmnUtils.infoPrint("-->\83}\83X\83N\8f\9c\8aO\83J\83\89\83\80='" + exclColsMask.toUpperCase() + "'");
 319                                 CmnUtils.infoPrint("-->\94¼\8ap\83}\83X\83N\95¶\8e\9a='" + singleByteMaskChar + "'");
 320                                 CmnUtils.infoPrint("-->\91S\8ap\83}\83X\83N\95¶\8e\9a='" + doubleByteMaskChar + "'");
 321                         }
 322                         CmnUtils.infoPrint("\83f\81[\83^\83\8d\81[\83h\82ð\8aJ\8en\82µ\82Ü\82µ\82½\81B");
 323
 324                         dao.select(sql_table_list);
 325                         alData = dao.getArrayList();
 326                         tb_count = dao.getColumnCount();
 327                         int tb_rec_count = 0;
 328                         int fl_rec_count = 0;
 329                         int ins_rec_count = 0;
 330                         int tb_fl_match_count = 0;
 331                         File [] inFiles = fldr.listFiles();
 332                         if(inFiles != null) {
 333                                 for(int k=0;k<inFiles.length;k++){
 334                                         flName = CmnUtils.splitDot(inFiles[k].getName());
 335                                         tbName = flName[0].toUpperCase();
 336                                         StringBuffer sbColumnName = null;
 337                                         TABLE_LOOP:
 338                                         for(int i=0;i<alData.size();i++){
 339                                                 if(tbName.equals(alData.get(i))){
 340                                                         if(isInclTables && !CmnUtils.isMatch(inclTablesArray,tbName.toUpperCase())){
 341                                                                 CmnUtils.debugPrint("inclTables=" + inclTables);
 342                                                                 CmnUtils.debugPrint("table=" + tbName + "," +CmnUtils.isMatch(inclTablesArray,tbName.toUpperCase()));
 343                                                                 continue;
 344                                                         }
 345                                                         if(isExclTables && CmnUtils.isMatch(exclTablesArray,tbName.toUpperCase())){
 346                                                                 CmnUtils.debugPrint("exclTables=" + exclTables);
 347                                                                 CmnUtils.debugPrint("table=" + tbName + "," +CmnUtils.isMatch(exclTablesArray,tbName.toUpperCase()));
 348                                                                 continue;
 349                                                         }
 350                                                         br = new BufferedReader(new InputStreamReader(new FileInputStream(fldr + "/" + alData.get(i) + "." + flName[1]), fileEncoding));
 351                                                         sql_load = "select * from " + alData.get(i);
 352                                                         stmt = dao.prepareSql(sql_load);
 353                                                         dao.executeSql(stmt);
 354                                                         alColName = dao.getArrayColumnNameList();
 355                                                         alColTypeName = dao.getArrayColumnTypeNameList();
 356                                                         alIsNullable = dao.getArrayIsNullableList();
 357                                                         alColMask = new ArrayList<Boolean>();
 358                                                         alColIncl = new ArrayList<Boolean>();
 359                                                         colName = (String[])alColName.toArray(new String[0]);
 360                                                         colTypeName = (String[])alColTypeName.toArray(new String[0]);
 361                                                         isNullable = (Integer[])alIsNullable.toArray(new Integer[0]);
 362                                                         tb_col_count = dao.getColumnCount();
 363                                                         int [] tb_col_seq = new int[tb_col_count];
 364                                                         int [] fl_col_seq = null;
 365                                                         dao.closeRecordSet();
 366                                                         tb_rec_count = 0;
 367                                                         fl_rec_count = 0;
 368                                                         ins_rec_count = 0;
 369                                                         fl_col_count = 0;
 370                                                         tb_fl_match_count = 0;
 371                                                         strMltLine = "";
 372                                                         isContinue = false;
 373                                                         isLob = false;
 374                                                         for(int j=0;j<colTypeName.length;j++)if(CmnUtils.isLob(colTypeName[j]))isLob = true;
 375                                                         CmnUtils.debugPrint("LOB="+isLob);
 376                                                         while((strLine=br.readLine()) != null){
 377                                                                 if (fl_rec_count == 0){
 378                                                                         if(isQuotation){
 379                                                                                 colData = CmnUtils.split(strLine,"\"" + delimiter + "\"");
 380                                                                         } else {
 381                                                                                 colData = CmnUtils.split(strLine,delimiter);
 382                                                                         }
 383                                                                         fl_col_count = colData.length;
 384                                                                         fl_col_seq = new int[colData.length];
 385                                                                         CmnUtils.debugPrint("TableName=" + tbName);
 386
 387                                                                         for(int j=0;j<colData.length;j++){
 388                                                                                 for(int l=0;l<tb_col_count;l++){
 389                                                                                         if(colName[l].equals(colData[j].replaceAll("\"",""))){
 390                                                                                                 if(isInclCols || isExclCols){
 391                                                                                                         if(
 392                                                                                                                 (isInclCols &&
 393                                                                                                                  !isExclCols &&
 394                                                                                                                  CmnUtils.isMatch(inclColsArray, tbName.toUpperCase(), colName[l].toUpperCase())
 395                                                                                                                 ) ||
 396                                                                                                                 (isInclCols &&
 397                                                                                                                  isExclCols &&
 398                                                                                                                  CmnUtils.isMatch(inclColsArray, tbName.toUpperCase(), colName[l].toUpperCase()) &&
 399                                                                                                                  !CmnUtils.isMatch(exclColsArray, tbName.toUpperCase(), colName[l].toUpperCase())
 400                                                                                                                 ) ||
 401                                                                                                                 (!isInclCols &&
 402                                                                                                                  isExclCols &&
 403                                                                                                                  !CmnUtils.isMatch(exclColsArray, tbName.toUpperCase(), colName[l].toUpperCase())
 404                                                                                                                 )
 405                                                                                                         ){
 406                                                                                                                 CmnUtils.debugPrint("\91Î\8fÛ\83J\83\89\83\80=" + colName[l].toUpperCase());
 407                                                                                                                 alColIncl.add(true);
 408                                                                                                         } else {
 409                                                                                                                 alColIncl.add(false);
 410                                                                                                         }
 411                                                                                                 } else {
 412                                                                                                         alColIncl.add(true);
 413                                                                                                 }
 414                                                                                                 break;
 415                                                                                         }
 416                                                                                 }
 417                                                                         }
 418
 419                                                                         colIncl = (Boolean[])alColIncl.toArray(new Boolean[0]);
 420
 421                                                                         sql_insert = "INSERT INTO " + tbName + "(";
 422                                                                         for(int j=0;j<colData.length;j++){
 423                                                                                 for(int l=0;l<tb_col_count;l++){
 424                                                                                         if(colName[l].equals(colData[j].replaceAll("\"",""))){
 425                                                                                                 if (colIncl[tb_fl_match_count]) {
 426                                                                                                         if (ins_rec_count == 0){
 427                                                                                                                 sql_insert += "\"" + colName[l] + "\"";
 428                                                                                                         } else {
 429                                                                                                                 sql_insert += "," + "\"" + colName[l] + "\"";
 430                                                                                                         }
 431                                                                                                         tb_col_seq[ins_rec_count]=l;
 432                                                                                                         fl_col_seq[ins_rec_count]=j;
 433                                                                                                         ins_rec_count++;
 434                                                                                                 }
 435                                                                                                 tb_fl_match_count++;
 436                                                                                                 break;
 437                                                                                         }
 438                                                                                 }
 439                                                                         }
 440                                                                         if (ins_rec_count > 0){
 441                                                                                 sql_insert += ") VALUES(";
 442                                                                                 for(int j=0;j<ins_rec_count;j++){
 443                                                                                         if(j==0){
 444                                                                                                 sql_insert += "?";
 445                                                                                         } else {
 446                                                                                                 sql_insert += ", ?";
 447                                                                                         }
 448                                                                                 }
 449                                                                                 sql_insert += ")";
 450                                                                                 stmt_ins = dao.prepareSql(sql_insert);
 451                                                                                 CmnUtils.debugPrint("************Record Start");
 452                                                                                 CmnUtils.infoPrinting(String.format("%1$-30s",tbName));
 453                                                                         } else {
 454                                                                                 dao.rollback();
 455                                                                                 CmnUtils.debugPrint("column count is zero");
 456                                                                                 if(br != null){
 457                                                                                         br.close();
 458                                                                                         br=null;
 459                                                                                 }
 460                                                                                 if(stmt_ins != null){
 461                                                                                         stmt_ins.close();
 462                                                                                         stmt_ins=null;
 463                                                                                 }
 464                                                                                 break TABLE_LOOP;
 465                                                                         }
 466                                                                 } else {
 467                                                                         if(isQuotation){
 468                                                                                 colData = CmnUtils.split(strLine,"\"" + delimiter + "\"");
 469                                                                         } else {
 470                                                                                 colData = CmnUtils.split(strLine,delimiter);
 471                                                                         }
 472                                                                         if(colData.length == fl_col_count){
 473                                                                                 if(!strMltLine.equals("")){
 474                                                                                         dao.rollback();
 475                                                                                         System.out.println(String.format("%1$10d",tb_rec_count) + " ERROR:column count is unmatch");
 476                                                                                         CmnUtils.debugPrint("************Record End");
 477                                                                                         if(br != null){
 478                                                                                                 br.close();
 479                                                                                                 br=null;
 480                                                                                         }
 481                                                                                         if(stmt_ins != null){
 482                                                                                                 stmt_ins.close();
 483                                                                                                 stmt_ins=null;
 484                                                                                         }
 485                                                                                         break TABLE_LOOP;
 486                                                                                 } else {
 487                                                                                         isContinue = false;
 488                                                                                 }
 489                                                                         } else {
 490                                                                                 if (strMltLine.equals("")){
 491                                                                                         strMltLine += strLine;
 492                                                                                         isContinue = true;
 493                                                                                 } else {
 494                                                                                         strMltLine += inColLineSeparator + strLine;
 495                                                                                         if(isQuotation){
 496                                                                                                 colData = CmnUtils.split(strMltLine,"\"" + delimiter + "\"");
 497                                                                                         } else {
 498                                                                                                 colData = CmnUtils.split(strMltLine,delimiter);
 499                                                                                         }
 500                                                                                         if(colData.length == fl_col_count){
 501                                                                                                 isContinue = false;
 502                                                                                         } else if(colData.length >fl_col_count){
 503                                                                                                 dao.rollback();
 504                                                                                                 CmnUtils.errorPrint(String.format("%1$10d",tb_rec_count) + "column count is unmatch");
 505                                                                                                 CmnUtils.debugPrint("************Record End");
 506                                                                                                 if(br != null){
 507                                                                                                         br.close();
 508                                                                                                         br=null;
 509                                                                                                 }
 510                                                                                                 if(stmt_ins != null){
 511                                                                                                         stmt_ins.close();
 512                                                                                                         stmt_ins=null;
 513                                                                                                 }
 514                                                                                                 break TABLE_LOOP;
 515                                                                                         } else {
 516                                                                                                 isContinue = true;
 517                                                                                         }
 518                                                                                 }
 519                                                                         }
 520                                                                         if(isContinue == false){
 521                                                                                 for(int j=0;j<ins_rec_count;j++){
 522                                                                                         if(j==0){
 523                                                                                                 if(isQuotation){
 524                                                                                                         colData[fl_col_seq[j]]=colData[fl_col_seq[j]].substring(1,colData[fl_col_seq[j]].length());
 525                                                                                                 }
 526                                                                                         }
 527                                                                                         if(j==ins_rec_count-1){
 528                                                                                                 if(isQuotation){
 529                                                                                                         colData[fl_col_seq[j]]=colData[fl_col_seq[j]].substring(0,colData[fl_col_seq[j]].length()-1);
 530                                                                                                 }
 531                                                                                         }
 532                                                                                         if (CmnUtils.isColString(colTypeName[tb_col_seq[j]])){
 533                                                                                                 if(isMask){
 534                                                                                                         if(
 535                                                                                                                 (isInclColsMask &&
 536                                                                                                                  !isExclColsMask &&
 537                                                                                                                  CmnUtils.isMatch(inclColsMaskArray,tbName.toUpperCase(), colName[tb_col_seq[j]].toUpperCase())
 538                                                                                                                 ) ||
 539                                                                                                                 (isInclColsMask &&
 540                                                                                                                  isExclColsMask &&
 541                                                                                                                  CmnUtils.isMatch(inclColsMaskArray,tbName.toUpperCase(), colName[tb_col_seq[j]].toUpperCase()) &&
 542                                                                                                                  !CmnUtils.isMatch(exclColsMaskArray,tbName.toUpperCase(),colName[tb_col_seq[j]].toUpperCase())
 543                                                                                                                 ) ||
 544                                                                                                                 (!isInclColsMask &&
 545                                                                                                                  isExclColsMask &&
 546                                                                                                                  !CmnUtils.isMatch(exclColsMaskArray,tbName.toUpperCase(),colName[tb_col_seq[j]].toUpperCase())
 547                                                                                                                 )
 548                                                                                                         ){
 549                                                                                                                 StringBuffer sb = new StringBuffer();
 550                                                                                                                 for(int l=0;l<colData[fl_col_seq[j]].length();l++){
 551                                                                                                                         if((maskPattern.toUpperCase().equals(MASK_PTN[MASK_PTN_ALT]) &&
 552                                                                                                                                         (l % 2) == 1) ||
 553                                                                                                                            (maskPattern.toUpperCase().equals(MASK_PTN[MASK_PTN_EDGE]) &&
 554                                                                                                                                         !(l == 0 || j == colData[fl_col_seq[j]].length() - 1) ||
 555                                                                                                                            (maskPattern.toUpperCase().equals(MASK_PTN[MASK_PTN_ALL]))
 556                                                                                                                            )
 557                                                                                                                         ){
 558                                                                                                                                 if(CmnUtils.isHankaku(colData[fl_col_seq[j]].charAt(l))){
 559                                                                                                                                         sb.append(singleByteMaskChar);
 560                                                                                                                                 } else {
 561                                                                                                                                         sb.append(doubleByteMaskChar);
 562                                                                                                                                 }
 563                                                                                                                         } else {
 564                                                                                                                                 sb.append(colData[fl_col_seq[j]].charAt(l));
 565                                                                                                                         }
 566                                                                                                                 }
 567                                                                                                                 colData[fl_col_seq[j]] = sb.toString();
 568                                                                                                         }
 569                                                                                                 }
 570                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 571                                                                                                         if(isQuotation){
 572                                                                                                                 stmt_ins.setString(j+1,colData[fl_col_seq[j]].replaceAll("\"\"","\""));
 573                                                                                                         } else {
 574                                                                                                                 stmt_ins.setString(j+1,colData[fl_col_seq[j]]);
 575                                                                                                         }
 576                                                                                                 } else {
 577                                                                                                         if(isNullable[j] == 0){
 578                                                                                                                 stmt_ins.setString(j+1," ");
 579                                                                                                         } else {
 580                                                                                                                 stmt_ins.setString(j+1,null);
 581                                                                                                         }
 582                                                                                                 }
 583                                                                                         } else if (CmnUtils.isColDate(colTypeName[tb_col_seq[j]])){
 584                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 585                                                                                                         stmt_ins.setDate(j+1,java.sql.Date.valueOf(colData[fl_col_seq[j]]));
 586                                                                                                 } else {
 587                                                                                                         stmt_ins.setDate(j+1,null);
 588                                                                                                 }
 589                                                                                         } else if (CmnUtils.isColTime(colTypeName[tb_col_seq[j]])){
 590                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 591                                                                                                         stmt_ins.setTime(j+1,java.sql.Time.valueOf(colData[fl_col_seq[j]]));
 592                                                                                                 } else {
 593                                                                                                         stmt_ins.setTime(j+1,null);
 594                                                                                                 }
 595                                                                                         } else if (CmnUtils.isColTimestamp(colTypeName[tb_col_seq[j]])){
 596                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 597                                                                                                         stmt_ins.setTimestamp(j+1,java.sql.Timestamp.valueOf(colData[fl_col_seq[j]]));
 598                                                                                                 } else {
 599                                                                                                         stmt_ins.setTimestamp(j+1,null);
 600                                                                                                 }
 601                                                                                         } else if (CmnUtils.isColBigDecimal(colTypeName[tb_col_seq[j]])){
 602                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 603                                                                                                         stmt_ins.setBigDecimal(j+1,new BigDecimal(colData[fl_col_seq[j]]));
 604                                                                                                 } else {
 605                                                                                                         stmt_ins.setNull(j+1,java.sql.Types.DECIMAL);
 606                                                                                                 }
 607                                                                                         } else if (CmnUtils.isColShort(colTypeName[tb_col_seq[j]])){
 608                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 609                                                                                                         stmt_ins.setShort(j+1,Short.parseShort(colData[fl_col_seq[j]]));
 610                                                                                                 } else {
 611                                                                                                         stmt_ins.setNull(j+1,java.sql.Types.SMALLINT);
 612                                                                                                 }
 613                                                                                         } else if (CmnUtils.isColInt(colTypeName[tb_col_seq[j]])){
 614                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 615                                                                                                         stmt_ins.setInt(j+1,Integer.parseInt(colData[fl_col_seq[j]]));
 616                                                                                                 } else {
 617                                                                                                         stmt_ins.setNull(j+1,java.sql.Types.INTEGER);
 618                                                                                                 }
 619                                                                                         } else if (CmnUtils.isColFloat(colTypeName[tb_col_seq[j]])){
 620                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 621                                                                                                         stmt_ins.setFloat(j+1,Float.parseFloat(colData[fl_col_seq[j]]));
 622                                                                                                 } else {
 623                                                                                                         stmt_ins.setNull(j+1,java.sql.Types.REAL);
 624                                                                                                 }
 625                                                                                         } else if (CmnUtils.isColDouble(colTypeName[tb_col_seq[j]])){
 626                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 627                                                                                                         stmt_ins.setDouble(j+1,Double.parseDouble(colData[fl_col_seq[j]]));
 628                                                                                                 } else {
 629                                                                                                         stmt_ins.setNull(j+1,java.sql.Types.FLOAT);
 630                                                                                                 }
 631                                                                                         } else if (CmnUtils.isColBytes(colTypeName[tb_col_seq[j]])){
 632                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 633                                                                                                         stmt_ins.setBytes(j+1,CmnUtils.base64ToBytes(colData[fl_col_seq[j]]));
 634                                                                                                 } else {
 635                                                                                                         stmt_ins.setBytes(j+1,null);
 636                                                                                                 }
 637                                                                                         } else if (CmnUtils.isColBlob(colTypeName[tb_col_seq[j]])){
 638                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 639                                                                                                         stmt_ins.setBytes(j+1,CmnUtils.base64ToBytes(colData[fl_col_seq[j]]));
 640                                                                                                 } else {
 641                                                                                                         stmt_ins.setBlob(j+1,null,0);
 642                                                                                                 }
 643                                                                                         } else if (CmnUtils.isColClob(colTypeName[tb_col_seq[j]])){
 644                                                                                                 if (!colData[fl_col_seq[j]].equals("")){
 645                                                                                                         stmt_ins.setString(j+1,colData[fl_col_seq[j]]);
 646                                                                                                 } else {
 647                                                                                                         stmt_ins.setClob(j+1,null,0);
 648                                                                                                 }
 649                                                                                         } else {
 650                                                                                                 CmnUtils.errorPrint("unmatch column type=" + colTypeName[tb_col_seq[j]]);
 651                                                                                                 break TABLE_LOOP;
 652                                                                                         }
 653                                                                                 }
 654                                                                                 if(isLob){
 655                                                                                         stmt_ins.executeUpdate();
 656                                                                                 } else {
 657                                                                                         stmt_ins.addBatch();
 658                                                                                 }
 659                                                                                 strMltLine = "";
 660                                                                                 tb_rec_count++;
 661                                                                         }
 662                                                                 }
 663                                                                 fl_rec_count++;
 664                                                                 if (fl_rec_count % batchCount == 0){
 665                                                                         if(!isLob)stmt_ins.executeBatch();
 666                                                                         CmnUtils.debugPrint("insert record=" + fl_rec_count);
 667                                                                 }
 668                                                         }
 669                                                         if(!isLob)stmt_ins.executeBatch();
 670                                                         dao.commit();
 671                                                         System.out.println(String.format("%1$10d",tb_rec_count) + " rows imported");
 672                                                         CmnUtils.debugPrint("************Record End");
 673                                                         if(br != null){
 674                                                                 br.close();
 675                                                                 br=null;
 676                                                         }
 677                                                         if(stmt_ins != null){
 678                                                                 stmt_ins.close();
 679                                                                 stmt_ins=null;
 680                                                         }
 681
 682                                                 }
 683                                         }
 684                                 }
 685                         } else {
 686                                 CmnUtils.errorPrint("Folder Not Found(" + fldr + ")");
 687                                 throw new Exception("Folder Not Found(" + fldr + ")");
 688                         }
 689
 690                         dao.disconnect();
 691
 692                 } catch (Exception e) {
 693                         dao.rollback();
 694                         throw e;
 695                 } finally{
 696                         if(stmt != null){
 697                                 stmt.close();
 698                                 stmt=null;
 699                         }
 700                         if(stmt_ins != null){
 701                                 stmt_ins.close();
 702                                 stmt_ins=null;
 703                         }
 704                         if(br!=null){
 705                                 br.close();
 706                                 br=null;
 707                         }
 708                 }
 709                 CmnUtils.infoPrint("\83f\81[\83^\83\8d\81[\83h\82ð\8fI\97¹\82µ\82Ü\82µ\82½\81B");
 710         }
 711
 712
 713 }
 714