com/rapide_act/DataAccessObjects.java

   1 package com.rapide_act;
   2
   3 import java.io.BufferedInputStream;
   4 import java.io.FileInputStream;
   5 import java.io.IOException;
   6 import java.io.InputStream;
   7 import java.io.File;
   8 import java.io.FileWriter;
   9 import java.io.BufferedWriter;
  10 import java.io.PrintWriter;
  11 import java.io.ByteArrayOutputStream;
  12 import java.sql.Connection;
  13 import java.sql.DriverManager;
  14 import java.sql.ResultSet;
  15 import java.sql.ResultSetMetaData;
  16 import java.sql.SQLException;
  17 import java.sql.PreparedStatement;
  18 import java.sql.Time;
  19 import java.sql.Timestamp;
  20 import java.sql.Blob;
  21 import java.sql.Clob;
  22 import java.io.PrintWriter;
  23 import java.util.Date;
  24 import java.util.ArrayList;
  25 import java.util.Iterator;
  26 import java.math.BigDecimal;
  27
  28 public class DataAccessObjects{
  29
  30         private String driver = null;
  31         private String user = null;
  32         private String pass = null;
  33         private String url = null;
  34         private Connection conn = null;
  35         private PreparedStatement stmt = null;
  36         private ResultSet rst = null;
  37         private ResultSetMetaData rsmd = null;
  38         private int ColumnCount = 0;
  39         private ArrayList<String> alColName = null;
  40         private ArrayList<String> alColTypeName = null;
  41         private ArrayList<Integer> alIsNullable = null;
  42         private ArrayList<Boolean> alColMask = null;
  43         private ArrayList<Boolean> alColIncl = null;
  44         private ArrayList<String> alData = null;
  45         private static String sql="select sysdate from dual";
  46         private String[] colTypeName = null;
  47         private String[] colName = null;
  48         private Integer[] isNullable = null;
  49         private Boolean [] colMask = null;
  50         private Boolean [] colIncl = null;
  51
  52         private String colString = null;
  53         private BigDecimal colBigDecimal = null;
  54         private Integer colInt = null;
  55         private Short colShort = null;
  56         private Float colFloat = null;
  57         private Double colDouble = null;
  58         private java.sql.Timestamp colTimestamp = null;
  59         private java.sql.Time colTime = null;
  60         private java.sql.Date colDate = null;
  61         private byte [] colBytes = null;
  62         private Blob colBlob = null;
  63         private Clob colClob = null;
  64         private int recCount = 0;
  65         private InputStream is = null;
  66         private ByteArrayOutputStream baos = null;
  67         private boolean isQuotation = true;
  68         private boolean isMask = false;
  69         private boolean isInclMaskCols = false;
  70         private boolean isExclMaskCols = false;
  71         private String inclMaskCols = null;
  72         private String exclMaskCols = null;
  73         private String tbName = null;
  74         private String nullMark = "";
  75         private String lnSprtr = System.getProperty("line.separator");
  76         private char singleByteChr = '*';
  77         private char doubleByteChr = '\81\96';
  78         private String maskPtn = "ALT";
  79         private static final int MASK_PTN_ALL = 0;
  80         private static final int MASK_PTN_ALT = 1;
  81         private static final int MASK_PTN_EDGE = 2;
  82         private static final String [] MASK_PTN = {"ALL", "ALT", "EDGE"};
  83         private static final int USER = 0;
  84         private static final int PASS = 1;
  85         private static final int URL = 2;
  86         private static final int DRIVER = 3;
  87         private static final int OUTPUT = 4;
  88         private static final int INPUT = 5;
  89         private static final int LINESEP = 6;
  90         private static final int INLINESEP = 7;
  91         private static final int FILENCODE = 8;
  92         private static final int NULLMARK = 9;
  93         private static final int DELIMITER = 10;
  94         private static final int TBLSQL = 11;
  95         private static final int TBLKEYSQL = 12;
  96         private static final int ORDER = 13;
  97         private static final int QUOTATION = 14;
  98         private static final int BATCOUNT = 15;
  99         private static final int INCLTAB = 16;
 100         private static final int EXCLTAB = 17;
 101         private static final int INCLCOL = 18;
 102         private static final int EXCLCOL = 19;
 103         private static final int INCLCOLMASK = 20;
 104         private static final int EXCLCOLMASK = 21;
 105         private static final int SBYTEMASKCHR = 22;
 106         private static final int DBYTEMASKCHR = 23;
 107         private static final int MASKPTN = 24;
 108         private static final int PROPFILE = 25;
 109         private static final String [] PROP_KEY = { "us","pw","url","dr","out","in","ls","ils","fe","nm",
 110                                                                                                 "dm","ts","tks","od","qt","bc","it","et","ic","ec",
 111                                                                                                 "icm","ecm","smc","dmc","mp","pf"};
 112         private static final int KEYS = 26;
 113         private String [] prop_val = null;
 114         DataAccessObjects(String _user, String _pass, String _url, String _driver){
 115                 user = _user;
 116                 pass = _pass;
 117                 url = _url;
 118                 driver = _driver;
 119         }
 120
 121         public Connection connect() throws Exception{
 122
 123                 try {
 124                         CmnUtils.debugPrint("Start Driver class loading");
 125                         Class.forName (driver);
 126                         CmnUtils.debugPrint("End Driver class loading");
 127                         CmnUtils.debugPrint("Start Connect Database>" + url + "," + user);
 128                         conn = DriverManager.getConnection(url, user, pass);
 129                         CmnUtils.debugPrint("End Connect Database");
 130                         return conn;
 131
 132                 }
 133                 catch (ClassNotFoundException e) {
 134                         throw e;
 135                 }
 136                 catch (IOException e) {
 137                         throw e;
 138                 }
 139                 catch (Exception e){
 140                         throw e;
 141                 }
 142         }
 143
 144         public PreparedStatement prepareSql(String _sql) throws Exception{
 145                 try {
 146                         CmnUtils.debugPrint("Start prepared Statement");
 147                         CmnUtils.debugPrint(_sql);
 148                         stmt = conn.prepareStatement(_sql);
 149                         CmnUtils.debugPrint("End prepared Statement");
 150                         return stmt;
 151                 }
 152                 catch (SQLException e) {
 153                         throw e;
 154                 }
 155                 catch (Exception e){
 156                         throw e;
 157                 }
 158         }
 159
 160         public void executeSql() throws Exception{
 161
 162                 try {
 163                         executeSql(stmt);
 164                         CmnUtils.debugPrint("Start get RecordSet");
 165                         while (rst.next()) {
 166                                 for(int i=0;i<ColumnCount;i++){
 167                                         colString = getColString(rst, i + 1, colTypeName[i], isNullable[i], false);
 168                                         if (colString != null) {
 169                                                 alData.add(colString);
 170                                         } else {
 171                                                 alData.add("");
 172                                         }
 173                                 }
 174                         }
 175                         CmnUtils.debugPrint("End get RecordSet");
 176                 }
 177                 catch (SQLException e) {
 178                         throw e;
 179                 }
 180                 catch (Exception e){
 181                         throw e;
 182                 }
 183                 finally{
 184                         if(rst != null){
 185                                 rst.close();
 186                                 rst = null;
 187                         }
 188                         if(stmt != null){
 189                                 stmt.close();
 190                                 stmt = null;
 191                         }
 192                 }
 193         }
 194
 195         public void executeSql(PreparedStatement _stmt) throws Exception{
 196
 197                 try {
 198                         CmnUtils.debugPrint("Start execute Query");
 199                         rst = _stmt.executeQuery();
 200                         CmnUtils.debugPrint("End execute Query");
 201
 202                         CmnUtils.debugPrint("Start get MetaData");
 203                         rsmd = rst.getMetaData();
 204                         CmnUtils.debugPrint("End get MetaData");
 205
 206                         CmnUtils.debugPrint("Start get ColumnCount");
 207                         ColumnCount = rsmd.getColumnCount();
 208                         CmnUtils.debugPrint("End get ColumnCount," + ColumnCount);
 209                         alColTypeName = new ArrayList<String>();
 210                         alColName = new ArrayList<String>();
 211                         alIsNullable = new ArrayList<Integer>();
 212                         alData = new ArrayList<String>();
 213                         for(int i=0;i<ColumnCount;i++){
 214                                 alColTypeName.add(rsmd.getColumnTypeName(i + 1));
 215                                 alColName.add(rsmd.getColumnName(i + 1));
 216                                 alIsNullable.add(rsmd.isNullable(i + 1));
 217                         }
 218                         colTypeName = (String[])alColTypeName.toArray(new String[0]);
 219                         colName = (String[])alColName.toArray(new String[0]);
 220                         isNullable = (Integer[])alIsNullable.toArray(new Integer[0]);
 221
 222                 }
 223                 catch (SQLException e) {
 224                         throw e;
 225                 }
 226                 catch (Exception e){
 227                         throw e;
 228                 }
 229         }
 230
 231         public void commit() throws Exception{
 232                 CmnUtils.debugPrint("Start commit");
 233                 conn.commit();
 234                 CmnUtils.debugPrint("End commit");
 235         }
 236
 237         public void rollback() throws Exception{
 238                 CmnUtils.debugPrint("Start rollback");
 239                 conn.rollback();
 240                 CmnUtils.debugPrint("End rollback");
 241         }
 242
 243         public void closeRecordSet() throws Exception{
 244                 if(rst != null){
 245                         rst.close();
 246                         rst = null;
 247                 }
 248                 if(stmt != null){
 249                         stmt.close();
 250                         stmt = null;
 251                 }
 252         }
 253
 254         public void select(String _sql) throws Exception{
 255                 PreparedStatement stmt;
 256                 stmt = prepareSql(_sql);
 257                 executeSql(stmt);
 258                 getRecordToArray();
 259                 closeRecordSet();
 260         }
 261
 262         public int getColumnCount() throws Exception{
 263                 return ColumnCount;
 264         }
 265
 266         public int getRecCount() throws Exception{
 267                 return recCount;
 268         }
 269
 270         public ArrayList<String> getArrayList() throws Exception{
 271                 return alData;
 272         }
 273
 274         public ArrayList<String> getArrayColumnNameList() throws Exception{
 275                 return alColName;
 276         }
 277
 278         public ArrayList<String> getArrayColumnTypeNameList() throws Exception{
 279                 return alColTypeName;
 280         }
 281
 282         public ArrayList<Integer> getArrayIsNullableList() throws Exception{
 283                 return alIsNullable;
 284         }
 285
 286         public void setArrayColumnMaskList(ArrayList<Boolean> _alColMask) throws Exception{
 287                 alColMask = _alColMask;
 288                 colMask = (Boolean[])alColMask.toArray(new Boolean[0]);
 289         }
 290
 291         public void setArrayColumnInclList(ArrayList<Boolean> _alColIncl) throws Exception{
 292                 alColIncl = _alColIncl;
 293                 colIncl = (Boolean[])alColIncl.toArray(new Boolean[0]);
 294         }
 295
 296         public void setTableName(String _tbName) throws Exception{
 297                 tbName = _tbName;
 298         }
 299
 300         public void setPropVal(String[] _prop_val) throws Exception{
 301                 prop_val = _prop_val;
 302         }
 303
 304         public ResultSet getResultSet() throws Exception{
 305                 return rst;
 306         }
 307
 308         public void getRecordToArray() throws Exception{
 309                 try {
 310                         CmnUtils.debugPrint("Start get RecordSet to Array");
 311                         while (rst.next()) {
 312                                 colString = null;
 313                                 for(int i=0;i<ColumnCount;i++){
 314                                         colString = getColString(rst, i + 1, colTypeName[i], isNullable[i], false);
 315                                         if (colString != null) {
 316                                                 alData.add(colString);
 317                                         } else {
 318                                                 alData.add("");
 319                                         }
 320                                 }
 321                         }
 322                         CmnUtils.debugPrint("End get RecordSet to Array");
 323                 }
 324                 catch (SQLException e) {
 325                         throw e;
 326                 }
 327                 catch (Exception e){
 328                         throw e;
 329                 }
 330         }
 331
 332         public void getRecordToPrint(PrintWriter _pw, String _splt, boolean _isQuotation, boolean _isMask, int _firstCol, int _lastCol) throws Exception{
 333                 try {
 334                         CmnUtils.debugPrint("Start get Record to Print");
 335                         isQuotation = _isQuotation;
 336                         isMask = _isMask;
 337                         recCount = 0;
 338                         if(!CmnUtils.isEmpty(prop_val[LINESEP]))lnSprtr = CmnUtils.getLineSeparator(prop_val[LINESEP]);
 339                         if(!CmnUtils.isEmpty(prop_val[NULLMARK]))nullMark = prop_val[NULLMARK];
 340
 341                         while (rst.next()) {
 342                                 colString = null;
 343                                 for(int i=0;i<ColumnCount;i++){
 344                                         if (colIncl[i]){
 345                                                 colString = getColString(rst, i + 1, colTypeName[i], isNullable[i], colMask[i]);
 346
 347                                                 if (colString != null) {
 348                                                         if (i == _firstCol){
 349                                                                 if(isQuotation){
 350                                                                         if (_firstCol == _lastCol){
 351                                                                                 _pw.print("\"" + colString + "\"" + lnSprtr);
 352                                                                         } else {
 353                                                                                 _pw.print("\"" + colString);
 354                                                                         }
 355                                                                 } else {
 356                                                                         if (_firstCol == _lastCol){
 357                                                                                 _pw.print(colString + lnSprtr);
 358                                                                         } else {
 359                                                                                 _pw.print(colString);
 360                                                                         }
 361                                                                 }
 362
 363                                                         } else if (i == _lastCol){
 364                                                                 if(isQuotation){
 365                                                                         _pw.print("\"" + _splt + "\"" + colString + "\"" + lnSprtr);
 366                                                                 } else {
 367                                                                         _pw.print(_splt + colString + lnSprtr);
 368                                                                 }
 369                                                         } else {
 370                                                                 if(isQuotation){
 371                                                                         _pw.print("\"" + _splt + "\"" + colString);
 372                                                                 } else {
 373                                                                         _pw.print(_splt + colString);
 374                                                                 }
 375                                                         }
 376
 377
 378
 379                                                 } else {
 380                                                         if (i == _firstCol){
 381                                                                 if(isQuotation){
 382                                                                         if (_firstCol == _lastCol){
 383                                                                                 _pw.print("\"" + nullMark + "\"" + lnSprtr);
 384                                                                         } else {
 385                                                                                 _pw.print("\"" + nullMark);
 386                                                                         }
 387                                                                 } else {
 388                                                                         if (_firstCol == _lastCol){
 389                                                                                 _pw.print(nullMark + lnSprtr);
 390                                                                         } else {
 391                                                                                 _pw.print(nullMark);
 392                                                                         }
 393                                                                 }
 394                                                         } else if (i == _lastCol){
 395                                                                 if(isQuotation){
 396                                                                         _pw.print("\"" + _splt + "\"" + nullMark + "\"" + lnSprtr);
 397                                                                 } else {
 398                                                                         _pw.print(_splt + nullMark + lnSprtr);
 399                                                                 }
 400                                                         } else {
 401                                                                 if(isQuotation){
 402                                                                         _pw.print("\"" + _splt + "\"" + nullMark);
 403                                                                 } else {
 404                                                                         _pw.print(_splt + nullMark);
 405                                                                 }
 406                                                         }
 407                                                 }
 408                                         }
 409                                 }
 410                                 recCount++;
 411                         }
 412                         CmnUtils.debugPrint("End get Record to Print");
 413                 }
 414                 catch (SQLException e) {
 415                         throw e;
 416                 }
 417                 catch (Exception e){
 418                         throw e;
 419                 }
 420         }
 421
 422         private String getColString(ResultSet _rst, int _rec_cnt, String _colTypeName, Integer _isNullable, Boolean _colMask) throws Exception{
 423                 String colString = null;
 424                 java.sql.Timestamp colTimestamp = null;
 425                 java.sql.Time colTime = null;
 426                 java.sql.Date colDate = null;
 427                 BigDecimal colBigDecimal = null;
 428                 Integer colInt = null;
 429                 Short colShort = null;
 430                 Float colFloat = null;
 431                 Double colDouble = null;
 432                 byte [] colBytes = null;
 433                 Blob colBlob = null;
 434                 Clob colClob = null;
 435                 ByteArrayOutputStream baos = null;
 436                 InputStream is = null;
 437                 if(!CmnUtils.isEmpty(prop_val[QUOTATION]) && prop_val[QUOTATION].toUpperCase().equals("N"))isQuotation = false;
 438                 if(!CmnUtils.isEmpty(prop_val[INCLCOLMASK]))isInclMaskCols = true;
 439                 if(!CmnUtils.isEmpty(prop_val[EXCLCOLMASK]))isExclMaskCols = true;
 440                 if(isInclMaskCols || isExclMaskCols)isMask = true;
 441                 if(!CmnUtils.isEmpty(prop_val[SBYTEMASKCHR]))singleByteChr = prop_val[SBYTEMASKCHR].charAt(0);
 442                 if(!CmnUtils.isEmpty(prop_val[DBYTEMASKCHR]))doubleByteChr = prop_val[DBYTEMASKCHR].charAt(0);
 443                 if(!CmnUtils.isEmpty(prop_val[MASKPTN]))maskPtn = prop_val[MASKPTN];
 444
 445                 if (CmnUtils.isColString(_colTypeName)) {
 446                         colString = _rst.getString(_rec_cnt);
 447                         if (colString != null) {
 448                                 if(isQuotation){
 449                                         colString =colString.trim().replaceAll("\0","").replaceAll("\"","\"\"");
 450                                 } else {
 451                                         colString =colString.trim().replaceAll("\0","");
 452                                 }
 453                                 if(isMask){
 454                                         if(_colMask==true){
 455                                                 StringBuffer sb = new StringBuffer();
 456                                                 for(int j=0;j<colString.length();j++){
 457                                                         if((maskPtn.toUpperCase().equals(MASK_PTN[MASK_PTN_ALT]) &&
 458                                                                         (j % 2) == 1) ||
 459                                                            (maskPtn.toUpperCase().equals(MASK_PTN[MASK_PTN_EDGE]) &&
 460                                                                         !(j == 0 || j == colString.length() - 1) ||
 461                                                            (maskPtn.toUpperCase().equals(MASK_PTN[MASK_PTN_ALL]))
 462                                                            )
 463                                                         ){
 464                                                                         if(CmnUtils.isHankaku(colString.charAt(j))){
 465                                                                                 sb.append(singleByteChr);
 466                                                                         } else {
 467                                                                                 sb.append(doubleByteChr);
 468                                                                         }
 469                                                         } else {
 470                                                                 sb.append(colString.charAt(j));
 471                                                         }
 472                                                 }
 473                                                 colString = sb.toString();
 474                                         }
 475                                 }
 476                                 if(colString.equals(""))colString = " ";
 477                         }
 478                 } else if (CmnUtils.isColDate(_colTypeName)) {
 479                         colDate = _rst.getDate(_rec_cnt);
 480                         if (colDate != null) {
 481                                 colString = colDate.toString();
 482                         }
 483                 } else if (CmnUtils.isColTimestamp(_colTypeName)) {
 484                         colTimestamp = _rst.getTimestamp(_rec_cnt);
 485                         if (colTimestamp != null) {
 486                                 colString = colTimestamp.toString();
 487                         }
 488                 } else if (CmnUtils.isColTime(_colTypeName)) {
 489                         colTime = _rst.getTime(_rec_cnt);
 490                         if (colTime != null) {
 491                                 colString = colTime.toString();
 492                         }
 493                 } else if (CmnUtils.isColBigDecimal(_colTypeName)) {
 494                         colBigDecimal = _rst.getBigDecimal(_rec_cnt);
 495                         if (colBigDecimal != null) {
 496                                 colString = colBigDecimal.toString();
 497                         }
 498                 } else if (CmnUtils.isColShort(_colTypeName)) {
 499                         colShort = _rst.getShort(_rec_cnt);
 500                         if (colShort != null) {
 501                                 colString = colShort.toString();
 502                         }
 503                 } else if (CmnUtils.isColInt(_colTypeName)) {
 504                         colInt = _rst.getInt(_rec_cnt);
 505                         if (colInt != null) {
 506                                 colString = colInt.toString();
 507                         }
 508                 } else if (CmnUtils.isColFloat(_colTypeName)) {
 509                         colFloat = _rst.getFloat(_rec_cnt);
 510                         if (colFloat != null) {
 511                                 colString = colFloat.toString();
 512                         }
 513                 } else if (CmnUtils.isColDouble(_colTypeName)) {
 514                         colDouble = _rst.getDouble(_rec_cnt);
 515                         if (colDouble != null) {
 516                                 colString = colDouble.toString();
 517                         }
 518                 } else if (CmnUtils.isColBytes(_colTypeName)) {
 519                         colBytes = _rst.getBytes(_rec_cnt);
 520                         if (colBytes != null) {
 521                                 colString = CmnUtils.bytesToBase64(colBytes);
 522                         }
 523                 } else if (CmnUtils.isColBlob(_colTypeName)) {
 524                         colBlob = _rst.getBlob(_rec_cnt);
 525                         if (colBlob != null) {
 526                                 is = colBlob.getBinaryStream();
 527                                 baos = new ByteArrayOutputStream();
 528                                 int len = -1;
 529                                 colBytes = new byte[8192];
 530                                 while ((len = is.read(colBytes)) != -1) {
 531                                         baos.write(colBytes,0,len);
 532                                 }
 533                                 colBytes = baos.toByteArray();
 534                                 colString = CmnUtils.bytesToBase64(colBytes);
 535                                 is.close();
 536                                 baos.close();
 537                                 is = null;
 538                                 baos = null;
 539                         }
 540                 } else if (CmnUtils.isColClob(_colTypeName)) {
 541                         colClob = _rst.getClob(_rec_cnt);
 542                         if (colClob != null) {
 543                                 colString = CmnUtils.clobToString(colClob);
 544                         }
 545                 } else {
 546                         CmnUtils.errorPrint("unmatch column type=" + _colTypeName);
 547                         throw new Exception("unmatch column type=" + _colTypeName);
 548                 }
 549                 return colString;
 550
 551         }
 552
 553         public void disconnect() throws Exception{
 554
 555                 try {
 556                         CmnUtils.debugPrint("Start disconnect database");
 557                         conn.close();
 558                         CmnUtils.debugPrint("End disconnect database");
 559
 560                 }
 561                 catch (SQLException e) {
 562                         throw e;
 563                 }
 564                 catch (Exception e){
 565                         throw e;
 566                 }
 567                 finally{
 568                         if(conn != null){
 569                                 conn.close();
 570                                 conn = null;
 571                         }
 572                 }
 573         }
 574
 575 }
 576