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