com/rapide_act/CmnProperty.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 CmnProperty{
  31         protected String database = null;
  32         protected static final int DB_TYPE_ORACLE = 0;
  33         protected static final int DB_TYPE_SQLSERVER = 1;
  34         protected static final int DB_TYPE_DB2 = 2;
  35         protected static final int DB_TYPE_MYSQL = 3;
  36         protected static final int DB_TYPE_POSTGRESQL = 4;
  37         protected static final int DB_TYPE_UNKNOWN = -1;
  38         protected static final String [] DB_TYPE_NAME = {"ORACLE", "SQLSERVER", "DB2", "MYSQL", "POSTGRESQL"};
  39         protected static final int MASK_PTN_ALL = 0;
  40         protected static final int MASK_PTN_ALT = 1;
  41         protected static final int MASK_PTN_EDGE = 2;
  42         protected static final int D_USER = 0;
  43         protected static final int D_PASS = 1;
  44         protected static final int D_URL = 2;
  45         protected static final int D_DRIVER = 3;
  46         protected static final int D_OUTPUT = 4;
  47         protected static final int D_INPUT = 5;
  48         protected static final int D_LINESEP = 6;
  49         protected static final int D_INLINESEP = 7;
  50         protected static final int D_FILENCODE = 8;
  51         protected static final int D_NULLMARK = 9;
  52         protected static final int D_DELIMITER = 10;
  53         protected static final int D_TBLSQL = 11;
  54         protected static final int D_TBLKEYSQL = 12;
  55         protected static final int D_ORDER = 13;
  56         protected static final int D_QUOTATION = 14;
  57         protected static final int D_BATCOUNT = 15;
  58         protected static final int D_INCLTAB = 16;
  59         protected static final int D_EXCLTAB = 17;
  60         protected static final int D_INCLCOL = 18;
  61         protected static final int D_EXCLCOL = 19;
  62         protected static final int D_INCLCOLMASK = 20;
  63         protected static final int D_EXCLCOLMASK = 21;
  64         protected static final int D_SBYTEMASKCHR = 22;
  65         protected static final int D_DBYTEMASKCHR = 23;
  66         protected static final int D_MASKPTN = 24;
  67         protected static final int D_PROPFILE = 25;
  68         protected static final int KEYS = 26;
  69         protected static final String [] PROP_KEY = { "us","pw","url","dr","out","in","ls","ils","fe","nm",
  70                                                                                                 "dm","ts","tks","od","qt","bc","it","et","ic","ec",
  71                                                                                                 "icm","ecm","smc","dmc","mp","pf"};
  72
  73         protected String [] prop_val = new String[KEYS];
  74         protected static final String [] MASK_PTN = {"ALL", "ALT", "EDGE"};
  75         protected static final String DEFAULT_IN_FOLDER = "input";
  76         protected static final String DEFAULT_OUT_FOLDER = "output";
  77         protected static final String DEFAULT_PROP_FILE = "RapideAct.properties";
  78         protected static String [] tSqlArray = {
  79                 "select "
  80                 + " table_name "
  81                 + " from user_tables "
  82                 + " order by table_name",
  83                 "select "
  84                 + " name as table_name "
  85                 + " from sys.tables "
  86                 + " order by name"
  87         };
  88         protected static String [] tkSqlArray = {
  89                 "select "
  90                 + "a.table_name,"
  91                 + "d.column_name "
  92                 + " from user_tables a,"
  93                 + " (select b.table_name, "
  94                 + "   c.column_name, "
  95                 + "   c.position "
  96                 + "  from user_constraints b, "
  97                 + "       user_cons_columns c "
  98                 + "  where b.CONSTRAINT_TYPE = 'P' and "
  99                 + "        b.TABLE_NAME = c.TABLE_NAME and "
 100                 + "        b.CONSTRAINT_NAME = c.CONSTRAINT_NAME "
 101                 + "  ) d "
 102                 + "where a.table_name = d.table_name(+) "
 103                 + "order by a.table_name, d.position",
 104                 "SELECT "
 105                 + " A.name AS table_name, "
 106                 + " D.name AS col_name "
 107                 + "FROM sys.tables AS A "
 108                 + "LEFT OUTER JOIN sys.key_constraints AS B "
 109                 + "ON A.object_id = B.parent_object_id "
 110                 + " AND B.type = 'PK' "
 111                 + "LEFT OUTER JOIN sys.index_columns AS C "
 112                 + "ON B.parent_object_id = C.object_id "
 113                 + " AND B.unique_index_id = C.index_id "
 114                 + "LEFT OUTER JOIN sys.columns AS D "
 115                 + "ON C.object_id = D.object_id "
 116                 + " AND C.column_id = D.column_id "
 117                 + "order by A.name,C.key_ordinal"
 118         };
 119
 120         protected String user = null;
 121         protected String pass = null;
 122         protected String url = null;
 123         protected String driver = null;
 124         protected String outFolder                              = null;
 125         protected String inFolder                               = null;
 126         protected String lineSeparator                  = System.getProperty("line.separator");
 127         protected String inColLineSeparator             = System.getProperty("line.separator");
 128         protected String fileEncoding                   = System.getProperty("file.encoding");
 129         protected String nullMark                               = "";
 130         protected String delimiter                              = "\t";
 131         protected String tSql = null;
 132         protected String tkSql = null;
 133         protected boolean isOrder                               = false;
 134         protected boolean isQuotation                   = true;
 135         protected int batchCount                                        = 1000;
 136         protected String inclTables                             = null;
 137         protected String exclTables                             = null;
 138         protected String inclCols                               = null;
 139         protected String exclCols                               = null;
 140         protected String inclColsMask                   = null;
 141         protected String exclColsMask                   = null;
 142         protected char singleByteMaskChar                       = '*';
 143         protected char doubleByteMaskChar               = '\u25A0';
 144         protected String maskPattern                            = "ALT";
 145
 146         protected String [] inclTablesArray             = null;
 147         protected String [] exclTablesArray             = null;
 148         protected String [] inclColsArray                       = null;
 149         protected String [] exclColsArray                       = null;
 150         protected String [] inclColsMaskArray           = null;
 151         protected String [] exclColsMaskArray           = null;
 152         protected boolean isInclTables                  = false;
 153         protected boolean isExclTables                  = false;
 154         protected boolean isInclCols                            = false;
 155         protected boolean isExclCols                            = false;
 156         protected boolean isMask                                        = false;
 157         protected boolean isInclColsMask                        = false;
 158         protected boolean isExclColsMask                        = false;
 159         protected int dbType                                            = DB_TYPE_UNKNOWN;
 160
 161         protected void getProperties(String _propFile) throws Exception{
 162                 BufferedReader br = null;
 163                 Properties prop = null;
 164                 try {
 165                         prop = new Properties();
 166                         br = new BufferedReader(new InputStreamReader(CmnProperty.class.getClassLoader().getResourceAsStream(_propFile), "UTF-8"));
 167                         prop.load(br);
 168                         for(int i=0;i<KEYS;i++){
 169                                 prop_val[i] = prop.getProperty(database + "." +PROP_KEY[i]);
 170                         }
 171                         br.close();
 172                         br = null;
 173                 } catch (NullPointerException e) {
 174                                 throw new Exception("\83v\83\8d\83p\83e\83B\83t\83@\83C\83\8b\82ª\8c©\82Â\82©\82è\82Ü\82¹\82ñ\81B[" + _propFile +"]");
 175                 } catch (IOException e) {
 176                                 throw e;
 177                 }
 178                 finally{
 179                         try {
 180                                 if (br != null) {
 181                                         br.close();
 182                                         br = null;
 183                                 }
 184                         }
 185                         catch (IOException e) {
 186                                 throw e;
 187                         }
 188                 }
 189         }
 190
 191
 192         CmnProperty() {
 193                 super();
 194         }
 195
 196         protected void setProperty(String _database) throws Exception{
 197                 database = _database;
 198                 if(database != null){
 199                         if(!CmnUtils.isEmpty(prop_val[D_PROPFILE])){
 200                                 getProperties(System.getProperty(prop_val[D_PROPFILE]));
 201                         } else {
 202                                 getProperties(DEFAULT_PROP_FILE);
 203                         }
 204                 }
 205
 206                 for(int i=0;i<KEYS;i++){
 207                         if(System.getProperty(PROP_KEY[i])!=null)prop_val[i] = System.getProperty(PROP_KEY[i]);
 208                         CmnUtils.debugPrint(PROP_KEY[i] + "=" + prop_val[i]);
 209                 }
 210
 211                 if(!CmnUtils.isEmpty(prop_val[D_USER]))user = prop_val[D_USER];
 212                 if(!CmnUtils.isEmpty(prop_val[D_PASS]))pass = prop_val[D_PASS];
 213                 if(!CmnUtils.isEmpty(prop_val[D_URL]))url = prop_val[D_URL];
 214                 if(!CmnUtils.isEmpty(prop_val[D_DRIVER]))driver = prop_val[D_DRIVER];
 215                 if(!CmnUtils.isEmpty(prop_val[D_OUTPUT]))outFolder = prop_val[D_OUTPUT];
 216                 if(!CmnUtils.isEmpty(prop_val[D_INPUT]))inFolder = prop_val[D_INPUT];
 217                 if(!CmnUtils.isEmpty(prop_val[D_LINESEP]))lineSeparator = CmnUtils.getLineSeparator(prop_val[D_LINESEP]);
 218                 if(!CmnUtils.isEmpty(prop_val[D_INLINESEP]))inColLineSeparator = CmnUtils.getLineSeparator(prop_val[D_INLINESEP]);
 219                 if(!CmnUtils.isEmpty(prop_val[D_FILENCODE]))fileEncoding = prop_val[D_FILENCODE];
 220                 if(!CmnUtils.isEmpty(prop_val[D_NULLMARK]))nullMark = prop_val[D_NULLMARK];
 221                 if(!CmnUtils.isEmpty(prop_val[D_DELIMITER]))delimiter = CmnUtils.getDelimiter(prop_val[D_DELIMITER]);
 222                 if(!CmnUtils.isEmpty(prop_val[D_TBLSQL]))tSql = prop_val[D_TBLSQL];
 223                 if(!CmnUtils.isEmpty(prop_val[D_TBLKEYSQL]))tkSql = prop_val[D_TBLKEYSQL];
 224                 if(!CmnUtils.isEmpty(prop_val[D_ORDER]) && prop_val[D_ORDER].toUpperCase().equals("Y"))isOrder = true;
 225                 if(!CmnUtils.isEmpty(prop_val[D_QUOTATION]) && prop_val[D_QUOTATION].toUpperCase().equals("N"))isQuotation = false;
 226                 if(!CmnUtils.isEmpty(prop_val[D_BATCOUNT]) && CmnUtils.isNumeric(prop_val[D_BATCOUNT]))batchCount = Integer.parseInt(prop_val[D_BATCOUNT]);
 227                 if(!CmnUtils.isEmpty(prop_val[D_INCLTAB]))isInclTables = true;
 228                 if(!CmnUtils.isEmpty(prop_val[D_EXCLTAB]))isExclTables = true;
 229                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOL]))isInclCols = true;
 230                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOL]))isExclCols = true;
 231                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOLMASK]))isInclColsMask = true;
 232                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOLMASK]))isExclColsMask = true;
 233                 if(isInclColsMask || isExclColsMask)isMask = true;
 234                 if(!CmnUtils.isEmpty(prop_val[D_INCLTAB]))inclTables = prop_val[D_INCLTAB].toUpperCase();
 235                 if(!CmnUtils.isEmpty(prop_val[D_EXCLTAB]))exclTables = prop_val[D_EXCLTAB].toUpperCase();
 236                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOL]))inclCols = prop_val[D_INCLCOL].toUpperCase();
 237                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOL]))exclCols = prop_val[D_EXCLCOL].toUpperCase();
 238                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOLMASK]))inclColsMask = prop_val[D_INCLCOLMASK].toUpperCase();
 239                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOLMASK]))exclColsMask = prop_val[D_EXCLCOLMASK].toUpperCase();
 240                 if(!CmnUtils.isEmpty(prop_val[D_INCLTAB]))inclTablesArray = CmnUtils.splitCsv(prop_val[D_INCLTAB].toUpperCase());
 241                 if(!CmnUtils.isEmpty(prop_val[D_EXCLTAB]))exclTablesArray = CmnUtils.splitCsv(prop_val[D_EXCLTAB].toUpperCase());
 242                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOL]))inclColsArray = CmnUtils.splitCsv(prop_val[D_INCLCOL].toUpperCase());
 243                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOL]))exclColsArray = CmnUtils.splitCsv(prop_val[D_EXCLCOL].toUpperCase());
 244                 if(!CmnUtils.isEmpty(prop_val[D_INCLCOLMASK]))inclColsMaskArray = CmnUtils.splitCsv(prop_val[D_INCLCOLMASK].toUpperCase());
 245                 if(!CmnUtils.isEmpty(prop_val[D_EXCLCOLMASK]))exclColsMaskArray = CmnUtils.splitCsv(prop_val[D_EXCLCOLMASK].toUpperCase());
 246                 if(!CmnUtils.isEmpty(prop_val[D_SBYTEMASKCHR]))singleByteMaskChar = prop_val[D_SBYTEMASKCHR].charAt(0);
 247                 if(!CmnUtils.isEmpty(prop_val[D_DBYTEMASKCHR]))doubleByteMaskChar = prop_val[D_DBYTEMASKCHR].charAt(0);
 248                 if(!CmnUtils.isEmpty(prop_val[D_MASKPTN]))maskPattern = prop_val[D_MASKPTN];
 249
 250                 if(CmnUtils.isEmpty(user)){
 251                         throw new Exception("us(\83\86\81[\83UID)\82ª\96¢\8ew\92è\82Å\82·\81B");
 252                 } else if (CmnUtils.isEmpty(pass)){
 253                         throw new Exception("pw(\83p\83X\83\8f\81[\83h)\82ª\96¢\8ew\92è\82Å\82·\81B");
 254                 } else if (CmnUtils.isEmpty(url)){
 255                         throw new Exception("url(\90Ú\91±\8fî\95ñ)\82ª\96¢\8ew\92è\82Å\82·\81B");
 256                 } else if (CmnUtils.isEmpty(driver)){
 257                         throw new Exception("dr(\83h\83\89\83C\83o)\82ª\96¢\8ew\92è\82Å\82·\81B");
 258                 }
 259
 260                 for(int i = 0;i<DB_TYPE_NAME.length;i++){
 261                         if (url.toUpperCase().contains(DB_TYPE_NAME[i])){
 262                                 dbType = i;
 263                                 break;
 264                         }
 265                 }
 266
 267                 if (dbType != DB_TYPE_UNKNOWN){
 268                         if(!CmnUtils.isEmpty(prop_val[D_TBLSQL])){
 269                                 tSql = prop_val[D_TBLSQL];
 270                                 tkSql = prop_val[D_TBLKEYSQL];
 271                         } else {
 272                                 tSql = tSqlArray[dbType];
 273                                 tkSql = tkSqlArray[dbType];
 274                         }
 275                 } else {
 276                         throw new Exception("\83f\81[\83^\83x\81[\83X\90»\95i\82ª\8e¯\95Ê\82Å\82«\82Ü\82¹\82ñ\81B[" + url + "]");
 277                 }
 278
 279                 if(database!=null)CmnUtils.infoPrint("-->\91Î\8fÛ\83f\81[\83^\83x\81[\83X='" + database.toUpperCase() + "'");
 280                 if(!CmnUtils.isEmpty(prop_val[D_DELIMITER]))CmnUtils.infoPrint("-->\8bæ\90Ø\82è\95¶\8e\9a='" + prop_val[D_DELIMITER] + "'");
 281                 if(!CmnUtils.isEmpty(prop_val[D_LINESEP]))CmnUtils.infoPrint("-->\89ü\8ds\83R\81[\83h='" + prop_val[D_LINESEP] + "'");
 282                 if(!CmnUtils.isEmpty(prop_val[D_INLINESEP]))CmnUtils.infoPrint("-->\83J\83\89\83\80\93à\89ü\8ds\83R\81[\83h='" + prop_val[D_INLINESEP] + "'");
 283                 if(!CmnUtils.isEmpty(prop_val[D_FILENCODE]))CmnUtils.infoPrint("-->\95¶\8e\9a\83R\81[\83h='" + prop_val[D_FILENCODE] + "'");
 284                 if(!CmnUtils.isEmpty(prop_val[D_QUOTATION]))CmnUtils.infoPrint("-->\88ø\97p\8bå=" + CmnUtils.getYesNo(isQuotation));
 285                 if(!CmnUtils.isEmpty(prop_val[D_ORDER]))CmnUtils.infoPrint("-->\83\\81[\83g=" + CmnUtils.getYesNo(isOrder));
 286                 if(!CmnUtils.isEmpty(prop_val[D_BATCOUNT]))CmnUtils.infoPrint("-->\83o\83b\83`\8f\88\97\9d\90\94=" + batchCount);
 287                 if(isInclTables)CmnUtils.infoPrint("-->\91Î\8fÛ\83e\81[\83u\83\8b='" + inclTables.toUpperCase() + "'");
 288                 if(isExclTables)CmnUtils.infoPrint("-->\8f\9c\8aO\83e\81[\83u\83\8b='" + exclTables.toUpperCase() + "'");
 289                 if(isInclCols)CmnUtils.infoPrint("-->\91Î\8fÛ\83J\83\89\83\80='" + inclCols.toUpperCase() + "'");
 290                 if(isExclCols)CmnUtils.infoPrint("-->\8f\9c\8aO\83J\83\89\83\80='" + exclCols.toUpperCase() + "'");
 291                 if(isMask){
 292                         CmnUtils.infoPrint("-->\83}\83X\83L\83\93\83O=" +  CmnUtils.getYesNo(isMask));
 293                         CmnUtils.infoPrint("-->\83}\83X\83N\83L\83\93\83O\83p\83^\81[\83\93=" + maskPattern.toUpperCase());
 294                         CmnUtils.infoPrint("-->\94¼\8ap\83}\83X\83N\95¶\8e\9a='" + singleByteMaskChar + "'");
 295                         CmnUtils.infoPrint("-->\91S\8ap\83}\83X\83N\95¶\8e\9a='" + doubleByteMaskChar + "'");
 296                         if(isInclColsMask)CmnUtils.infoPrint("-->\83}\83X\83N\91Î\8fÛ\83J\83\89\83\80='" + inclColsMask.toUpperCase() + "'");
 297                         if(isExclColsMask)CmnUtils.infoPrint("-->\83}\83X\83N\8f\9c\8aO\83J\83\89\83\80='" + exclColsMask.toUpperCase() + "'");
 298                 }
 299
 300         }
 301
 302
 303 }
 304