Java API By Example, From Geeks To Geeks.

1 /* 2   Copyright (C) 2003 Know Gate S.L. All rights reserved. 3                       C/Oña, 107 1º2 28050 Madrid (Spain) 4 5   Redistribution and use in source and binary forms, with or without 6   modification, are permitted provided that the following conditions 7   are met: 8 9   1. Redistributions of source code must retain the above copyright 10      notice, this list of conditions and the following disclaimer. 11 12   2. The end-user documentation included with the redistribution, 13      if any, must include the following acknowledgment: 14      "This product includes software parts from hipergate 15      (http://www.hipergate.org/)." 16      Alternately, this acknowledgment may appear in the software itself, 17      if and wherever such third-party acknowledgments normally appear. 18 19   3. The name hipergate must not be used to endorse or promote products 20      derived from this software without prior written permission. 21      Products derived from this software may not be called hipergate, 22      nor may hipergate appear in their name, without prior written 23      permission. 24 25   This library is distributed in the hope that it will be useful, 26   but WITHOUT ANY WARRANTY; without even the implied warranty of 27   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 28 29   You should have received a copy of hipergate License with this code; 30   if not, visit http://www.hipergate.org or mail to info@hipergate.org 31 */ 32 33 package com.knowgate.dataobjs; 34 35 import java.io.FileNotFoundException ; 36 import java.io.UnsupportedEncodingException ; 37 import java.io.IOException ; 38 import java.io.OutputStream ; 39 import java.io.PrintWriter ; 40 41 import java.sql.Connection ; 42 import java.sql.ResultSet ; 43 import java.sql.ResultSetMetaData ; 44 import java.sql.Statement ; 45 import java.sql.CallableStatement ; 46 import java.sql.PreparedStatement ; 47 import java.sql.SQLException ; 48 import java.sql.Time ; 49 import java.sql.Timestamp ; 50 import java.sql.Types ; 51 52 import java.math.BigDecimal ; 53 54 import java.text.DecimalFormat ; 55 import java.text.SimpleDateFormat ; 56 57 import java.util.StringTokenizer ; 58 import java.util.Vector ; 59 import java.util.Date ; 60 import java.util.List ; 61 import java.util.Map ; 62 import java.util.HashMap ; 63 64 import com.knowgate.debug.DebugFile; 65 import com.knowgate.jdc.JDCConnection; 66 import com.knowgate.misc.Gadgets; 67 import com.knowgate.misc.CSVParser; 68 import com.knowgate.math.Money; 69 70 /** 71  * 72  * <p>A bidimensional array representing data readed from a database table.</p> 73  * The DBSubset object is used for reading a collection of registers from the database 74  * and kept them in memory for inmediate access. 75  * As hipergate reuses database connections by recycling them at the {@link JDCConnectionPool}, 76  * it is a good programming practice to read just the needed number of registers in a single 77  * step operation and then free the connection as soon as possible for being used by another processs. 78  * Another reason for this "read fast and leave" tactic is that some JDBC drivers have problems 79  * managing multiple open resultsets with pending results in a single connection. 80  * @author Sergio Montoro Ten 81  * @version 3.0 82  */ 83 84 public final class DBSubset { 85 86   /** 87    * </p>Contructs a DBSubset.</p> 88    * @param sTableName Base table or tables, ie. "k_products" or "k_products p, k_x_cat_objs x" 89    * @param sColumnList Column list to be retirved from base tables i.e "p.gu_product,p.nm_product" 90    * @param sFilterClause SQL filter clause or <b>null</b> if there is no filter clause to be applied. 91    * @param iFetchSize Space for number of rows initailly allocated. Is DBSubset later loads more rows 92    * the buffer is automatically expanded. This parameter may also have a great effect on reducing 93    * network round trips as the ResultSet.setFetchSize(iFetchSize) method is called prior to fetching 94    * rows. Fetching rows in batches is much faster than doing so one by one. When iFetchSize is set, 95    * the JDBC driver may optimize accesses to fetched rows by reading bursts and 96    * caching rows at client side. 97    */ 98 99   public DBSubset (String sTableName, String sColumnList, String sFilterClause, int iFetchSize) { 100     if (DebugFile.trace) 101       DebugFile.writeln ("new DBSubset(" + sTableName + "," + sColumnList + "," + sFilterClause + "," + String.valueOf(iFetchSize)+")"); 102 103     sTable = sTableName; 104     sColList = sColumnList; 105 106     if (null!=sFilterClause) { 107      sFilter = sFilterClause; 108 109      if (sFilter.length()>0) 110         sSelect = "SELECT " + sColList + " FROM " + sTable + " WHERE " + sFilter; 111       else 112         sSelect = "SELECT " + sColList + " FROM " + sTable; 113     } 114     else { 115       sFilter = ""; 116       sSelect = "SELECT " + sColList + " FROM " + sTable; 117     } 118 119     if (DebugFile.trace) DebugFile.writeln (sSelect); 120 121     oResults = null; 122     sInsert = ""; 123     iFetch = iFetchSize; 124     iColCount = 0; 125     iMaxRows = -1; 126     iTimeOut = 60; 127     bEOF = true; 128     sColDelim = "`"; 129     sRowDelim = "¨"; 130     sTxtQualifier = "\""; 131     oShortDate = null; 132   } 133 134   // ---------------------------------------------------------- 135 136   /** 137    * Delete rows from base table 138    * @param oConn Connection 139    * @param aFilterValues Object[] Parameters for sFilter parameter specified at constructor 140    * @return int Number of rows deleted 141    * @throws SQLException 142    */ 143   public int clear(Connection oConn, Object [] aFilterValues) throws SQLException 144   { 145     int iAffected=0; 146     PreparedStatement oStmt; 147 148     if (DebugFile.trace) 149       { 150       DebugFile.writeln("Begin DBSubset.clear([Connection], Object[])"); 151       DebugFile.incIdent(); 152       } 153 154     // Begin SQLException 155 156       if (sFilter.length()>0) 157         oStmt = oConn.prepareStatement("DELETE FROM " + sTable + " WHERE " + sFilter); 158       else 159         oStmt = oConn.prepareStatement("DELETE FROM " + sTable); 160 161       try { oStmt.setQueryTimeout(iTimeOut); } catch (SQLException sqle) { if (DebugFile.trace) DebugFile.writeln("Error at PreparedStatement.setQueryTimeout(" + String.valueOf(iTimeOut) + ")" + sqle.getMessage()); } 162 163       for (int c=0; c<aFilterValues.length; c++) 164         oStmt.setObject(c+1, aFilterValues[c]); 165 166       iAffected = oStmt.executeUpdate(); 167       oStmt.close(); 168 169     // End SQLException 170 171     oResults = null; 172 173     if (DebugFile.trace) 174       { 175       DebugFile.decIdent(); 176       DebugFile.writeln("End DBSubset.clear()"); 177       } 178 179     return iAffected; 180   } // clear 181 182   // ---------------------------------------------------------- 183 184   /** 185    * @return <b>true</b> if call() or load() methods readed all available rows, 186    * <b>false</b> if more rows where pending of reading when getMaxRows() was 187    * reached.<br> 188    * Also, after calling store(), eof() is <b>true</b> if all rows were stored 189    * successfully or <b>false</b> if any row failed to be inserted or updated. 190    */ 191   public boolean eof() { 192     return bEOF; 193   } 194 195   // ---------------------------------------------------------- 196 197   /** 198    * @return Maximum number of rows to be readed from database 199    */ 200   public int getMaxRows() { 201     return iMaxRows; 202   } 203 204   // ---------------------------------------------------------- 205 206   /** 207    * <p>Maximum number of rows to be readed from database</p> 208    * <p>The exact behavior of this property depends on the RDBMS used.</p> 209    * <p>For <b>PostgreSQL</b> [LIMIT n] clause is appended to DBSubset query and all returned rows are readed.</p> 210    * <p>For <b>Microsoft SQL Server</b> [OPTION FAST(n)] clause is appended to DBSubset query 211    * and then the number of readed rows is limited at client side fetching.</p> 212    * <p>For <b>Oracle</b> there is no standard way of limiting the resultset size. 213    * The number of readed rows is just limited at client side fetching.</p> 214    */ 215 216   public void setMaxRows(int iMax) { 217     iMaxRows = iMax; 218   } 219 220   // ---------------------------------------------------------- 221 222   /** 223    * @return Maximum amount of time in seconds that a query may run before being cancelled. 224    */ 225 226   public int getQueryTimeout() { 227     return iTimeOut; 228   } 229 230   // ---------------------------------------------------------- 231 232   /** 233    * Maximum number of seconds that a query can be executing before raising a timeout exception 234    * @param iMaxSeconds 235    */ 236   public void setQueryTimeout(int iMaxSeconds) { 237     iTimeOut = iMaxSeconds; 238   } 239 240   // ---------------------------------------------------------- 241 242   private void setFetchSize(JDCConnection oConn, ResultSet oRSet) 243     throws SQLException { 244 245     if (DebugFile.trace) { 246       DebugFile.writeln("Begin DBSubset.setFetchSize()"); 247       DebugFile.incIdent(); 248       } 249 250     if (oConn.getDataBaseProduct()==JDCConnection.DBMS_POSTGRESQL) 251       // PostgreSQL does not support setFetchSize() 252 iFetch = 1; 253 254     else { 255 256       try { 257         if (0!=iFetch) 258           oRSet.setFetchSize (iFetch); 259         else 260           iFetch = oRSet.getFetchSize(); 261       } 262       catch (SQLException e) { 263         if (DebugFile.trace) DebugFile.writeln(e.getMessage()); 264         iFetch = 1; 265       } 266     } 267 268     if (DebugFile.trace) { 269       DebugFile.decIdent(); 270       DebugFile.writeln("End DBSubset.setFetchSize() : " + iFetch); 271       } 272     } // setFetchSize 273 274   // ---------------------------------------------------------- 275 276   private int fetchResultSet (ResultSet oRSet, int iSkip) 277     throws SQLException , ArrayIndexOutOfBoundsException 278     { 279     Vector oRow; 280     int iCol; 281     int iRetVal = 0; 282     int iMaxRow = iMaxRows<0 ? 2147483647 : iMaxRows; 283     long lFetchTime = 0; 284     Object oFieldValue; 285 286     if (DebugFile.trace) { 287       DebugFile.writeln("Begin DBSubset.fetchResultSet([ResultSet], " + String.valueOf(iSkip) + ")"); 288       DebugFile.incIdent(); 289       DebugFile.writeln("column count = " + String.valueOf(iColCount)); 290       DebugFile.writeln("max. rows = " + String.valueOf(iMaxRows)); 291       DebugFile.writeln("new Vector(" + String.valueOf(iFetch) + "," + String.valueOf(iFetch) + ")"); 292       lFetchTime = System.currentTimeMillis(); 293     } 294 295     oResults = new Vector (iFetch, iFetch); 296 297     if (0!=iSkip) { 298       oRSet.next(); 299 300       if (DebugFile.trace) DebugFile.writeln("ResultSet.relative(" + String.valueOf(iSkip-1) + ")"); 301 302       oRSet.relative (iSkip-1); 303     } // fi(iSkip) 304 305     boolean bHasNext = oRSet.next(); 306 307     while (bHasNext && iRetVal<iMaxRow) { 308 309       iRetVal++; 310       oRow = new Vector (iColCount); 311 312       for (iCol=1; iCol<=iColCount; iCol++) { 313         oFieldValue = oRSet.getObject(iCol); 314         if (oRSet.wasNull()) 315           oRow.add (null); 316         else 317           oRow.add (oFieldValue); 318       } // next 319 320       oResults.add(oRow); 321 322       bHasNext = oRSet.next(); 323     } // wend 324 325     if (0==iRetVal || iRetVal<iMaxRow) { 326       bEOF = true; 327       if (DebugFile.trace) DebugFile.writeln("readed " + String.valueOf(iRetVal) + " rows eof() = true"); 328     } 329     else { 330       bEOF = !bHasNext; 331 332       if (DebugFile.trace) DebugFile.writeln("readed max " + String.valueOf(iMaxRow) + " rows eof() = " + String.valueOf(bEOF)); 333     } 334 335     if (DebugFile.trace) { 336       DebugFile.writeln("fetching done in " + String.valueOf(System.currentTimeMillis()-lFetchTime) + " ms"); 337       DebugFile.decIdent(); 338       DebugFile.writeln("End DBSubset.fetchResultSet() : " + String.valueOf(iRetVal)); 339     } 340 341     return iRetVal; 342     } // fetchResultSet 343 344   // ---------------------------------------------------------- 345 346   /** 347    * <p>Execute a stored procedure returning a ResultSet</p> 348    * @param oConn Database Connection 349    * @return Number of rows retrieved 350    * @throws SQLException 351    */ 352 353   public int call (JDCConnection oConn) throws SQLException { 354     return call(oConn,0); 355   } 356 357   // ---------------------------------------------------------- 358 359   /** 360    * <p>Execute a stored procedure returning a ResultSet</p> 361    * @param oConn Database Connection 362    * @param iSkip Number of rows to be skipped before reading 363    * @return Number of rows retrieved 364    * @throws SQLException 365    * @throws IllegalArgumentException if iSkip<0 366    * @throws ArrayIndexOutOfBoundsException 367    */ 368 369   public int call (JDCConnection oConn, int iSkip) 370       throws SQLException , IllegalArgumentException , ArrayIndexOutOfBoundsException 371   { 372     CallableStatement oStmt; 373     ResultSet oRSet; 374     ResultSetMetaData oMDat; 375     int iRows = 0; 376     int iType = (iSkip==0 ? ResultSet.TYPE_FORWARD_ONLY : ResultSet.TYPE_SCROLL_INSENSITIVE); 377 378     if (DebugFile.trace) 379       { 380       DebugFile.writeln("Begin DBSubset.call([Connection]," + iSkip + ")"); 381       DebugFile.incIdent(); 382       } 383 384     // Begin SQLException 385 386       if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call " + sTable + "()}"); 387       oStmt = oConn.prepareCall("{call " + sTable + "()}", iType, ResultSet.CONCUR_READ_ONLY); 388 389       if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery(" + sTable + ")"); 390 391       oRSet = oStmt.executeQuery(); 392 393       oMDat = oRSet.getMetaData(); 394       iColCount = oMDat.getColumnCount(); 395       ColNames = new String [iColCount]; 396 397       for (int c=1; c<=iColCount; c++) { 398         ColNames[c-1] = oMDat.getColumnName(c).toLowerCase(); 399       } 400       oMDat = null; 401 402       setFetchSize(oConn, oRSet); 403 404       iRows = fetchResultSet(oRSet,iSkip); 405 406       oRSet.close(); 407       oRSet = null; 408 409       oStmt.close(); 410       oStmt = null; 411 412     // End SQLException 413 414     if (DebugFile.trace) 415       { 416       DebugFile.decIdent(); 417       DebugFile.writeln("End DBSubset.call()"); 418       } 419 420     return iRows; 421   } // call() 422 423   // ---------------------------------------------------------- 424 425   /** 426    * <p>Execute a stored procedure returning a ResultSet</p> 427    * @param oConn Database Connection 428    * @param aFilterValues Values to be binded and JDBC PreparedStatement query paramenters. 429    * @return Number of rows retrieved 430    * @throws SQLException 431    */ 432 433   public int call (JDCConnection oConn, Object [] aFilterValues) throws SQLException { 434     return call(oConn, aFilterValues, 0); 435   } 436 437   // ---------------------------------------------------------- 438 439   /** 440    * <p>Execute a stored procedure returning a ResultSet</p> 441    * @param oConn Database Connection 442    * @param aFilterValues Values to be binded and JDBC PreparedStatement query paramenters. 443    * @param iSkip Number of rows to be skipped before reading 444    * @return Number of rows retrieved, 445    * the maximum number of rows to be retrieved is determined by calling method 446    * setMaxRows(), if setMaxRows() is not called before call() then all rows existing are retrieved. 447    * @throws SQLException 448    * @throws IllegalArgumentException 449    * @throws ArrayIndexOutOfBoundsException 450    */ 451 452   public int call (JDCConnection oConn, Object [] aFilterValues, int iSkip) 453     throws SQLException , IllegalArgumentException , ArrayIndexOutOfBoundsException 454     { 455     CallableStatement oStmt; 456     ResultSet oRSet; 457     ResultSetMetaData oMDat; 458 459     int iRows = 0; 460     int iType = (iSkip==0 ? ResultSet.TYPE_FORWARD_ONLY : ResultSet.TYPE_SCROLL_INSENSITIVE); 461 462     if (DebugFile.trace) 463       { 464       DebugFile.writeln("Begin DBSubset.call([Connection], Object[]," + iSkip + ")"); 465       DebugFile.incIdent(); 466       } 467 468     // Begin SQLException 469 if (DebugFile.trace) DebugFile.writeln("Connection.prepareCall({call " + sTable + "()}"); 470       oStmt = oConn.prepareCall("{call " + sTable + "()}", iType, ResultSet.CONCUR_READ_ONLY); 471 472       for (int p=0; p<aFilterValues.length; p++) 473         oStmt.setObject(p+1, aFilterValues[p]); 474 475       if (DebugFile.trace) DebugFile.writeln("Connection.executeQuery()"); 476 477       oRSet = oStmt.executeQuery(); 478 479       oMDat = oRSet.getMetaData(); 480       iColCount = oMDat.getColumnCount(); 481       ColNames = new String [iColCount]; 482 483       for (int c=1; c<=iColCount; c++) { 484         ColNames[c-1] = oMDat.getColumnName(c).toLowerCase(); 485       } 486       oMDat = null; 487 488       setFetchSize(oConn, oRSet); 489 490       iRows = fetchResultSet(oRSet, iSkip); 491 492       oRSet.close(); 493       oRSet = null; 494 495       oStmt.close(); 496       oStmt = null; 497 498     // End SQLException 499 500     if (DebugFile.trace) 501       { 502       DebugFile.decIdent(); 503       DebugFile.writeln("End DBSubset.call()"); 504       } 505 506     return iRows; 507   } // call 508 509   // ---------------------------------------------------------- 510 511   /** 512    * <p>Execute a JDBC Statement and load query ResultSet in an internal bidimensional matrix</p> 513    * @param oConn Database Connection 514    * @return Number of rows retrieved 515    * the maximum number of rows to be retrieved is determined by calling method 516    * setMaxRows(), if setMaxRows() is not called before call() then all rows existing are retrieved. 517    * @throws SQLException 518    */ 519 520   public int load (JDCConnection oConn) 521     throws SQLException , ArrayIndexOutOfBoundsException , NullPointerException { 522     return load(oConn, 0); 523   } 524 525   // ---------------------------------------------------------- 526 527   /** 528    * <p>Execute a JDBC Statement and load query ResultSet in an internal bidimensional matrix</p> 529    * @param oConn Database Connection 530    * @param iSkip Number of rows to be skipped before reading. On database systems that support an 531    * OFFSET clause (such as PostgreSQL) the native offset feature of the DBMS is used, in case that 532    * the DBMS does not provide offset capabilities, the data is fetched and discarded at client side 533    * before returning the DBSubset. Care must be taken when skipping a large number of rows in client 534    * side mode as it may cause heavy network traffic and round trips to the database. 535    * @return Number of rows retrieved 536    * the maximum number of rows to be retrieved is determined by calling method 537    * setMaxRows(), if setMaxRows() is not called before call() then all rows existing are retrieved. 538    * @throws SQLException 539    * @throws IllegalArgumentException if iSkip<0 540    * @throws ArrayIndexOutOfBoundsException 541    * @throws NullPointerException 542    */ 543 544   public int load (JDCConnection oConn, int iSkip) 545     throws SQLException , IllegalArgumentException , 546     ArrayIndexOutOfBoundsException , NullPointerException { 547 548     Statement oStmt = null; 549     ResultSet oRSet = null; 550     ResultSetMetaData oMDat; 551     int iRows = 0; 552     int iType = (iSkip==0 ? ResultSet.TYPE_FORWARD_ONLY : ResultSet.TYPE_SCROLL_INSENSITIVE); 553     long lQueryTime = 0; 554 555     if (DebugFile.trace) 556       { 557       DebugFile.writeln("Begin DBSubset.load([Connection]," + iSkip + ")"); 558       lQueryTime = System.currentTimeMillis(); 559       } 560 561     if (iSkip<0) 562       throw new IllegalArgumentException ("row offset must be equal to or greater than zero"); 563 564     if (null==oConn) 565       throw new NullPointerException ("DBSubset.load() JDCConnection parameter is null"); 566 567     if (DebugFile.trace) 568       DebugFile.incIdent(); 569 570     try { 571 572       oStmt = oConn.createStatement (iType, ResultSet.CONCUR_READ_ONLY); 573 574       if (iMaxRows>0) { 575 576         switch (oConn.getDataBaseProduct()) { 577 578           case JDCConnection.DBMS_MSSQL: 579             if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSelect + " OPTION (FAST " + String.valueOf(iMaxRows) + ")" + ")"); 580             oRSet = oStmt.executeQuery(sSelect + " OPTION (FAST " + String.valueOf(iMaxRows) + ")"); 581             break; 582 583           case JDCConnection.DBMS_MYSQL: 584             if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSelect + " LIMIT " + String.valueOf(iSkip) + "," + String.valueOf(iMaxRows+2) + ")"); 585             oRSet = oStmt.executeQuery(sSelect + " LIMIT " + String.valueOf(iSkip) + "," + String.valueOf(iMaxRows+2)); 586             iSkip = 0; // Use PostgreSQL native OFFSET clause, so do not skip any rows before client side fetching 587 break; 588 589           case JDCConnection.DBMS_POSTGRESQL: 590             if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSelect + " LIMIT " + String.valueOf(iMaxRows+2) + " OFFSET " + String.valueOf(iSkip) + ")"); 591             oRSet = oStmt.executeQuery(sSelect + " LIMIT " + String.valueOf(iMaxRows+2) + " OFFSET " + String.valueOf(iSkip)); 592             iSkip = 0; // Use PostgreSQL native OFFSET clause, so do not skip any rows before client side fetching 593 break; 594 595           default: 596             if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSelect + ")"); 597             oRSet = oStmt.executeQuery(sSelect); 598         } // end switch 599 } 600       else { 601         switch (oConn.getDataBaseProduct()) { 602 603           case JDCConnection.DBMS_MYSQL: 604             if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSelect + " LIMIT " + String.valueOf(iSkip) + ",2147483647)"); 605             oRSet = oStmt.executeQuery(sSelect + " LIMIT " + String.valueOf(iSkip) + ",2147483647" ); 606             iSkip = 0; // Use MySQL native LIMIT clause, so do not skip any rows before client side fetching 607 break; 608 609           case JDCConnection.DBMS_POSTGRESQL: 610             if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSelect + " OFFSET " + String.valueOf(iSkip) + ")"); 611             oRSet = oStmt.executeQuery(sSelect + " OFFSET " + String.valueOf(iSkip)); 612             iSkip = 0; // Use PostgreSQL native OFFSET clause, so do not skip any rows before client side fetching 613 break; 614 615           default: 616             if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSelect + ")"); 617             oRSet = oStmt.executeQuery(sSelect); 618         } // end switch 619 } 620 621       if (DebugFile.trace) { 622         DebugFile.writeln("query executed in " + String.valueOf(System.currentTimeMillis()-lQueryTime) + " ms"); 623         DebugFile.writeln("ResultSet.getMetaData()"); 624       } 625 626       oMDat = oRSet.getMetaData(); 627       iColCount = oMDat.getColumnCount(); 628       ColNames = new String [iColCount]; 629 630       for (int c=1; c<=iColCount; c++) { 631         ColNames[c-1] = oMDat.getColumnName(c).toLowerCase(); 632       } 633 634       oMDat = null; 635 636       setFetchSize(oConn, oRSet); 637 638       iRows = fetchResultSet(oRSet,iSkip); 639 640       if (DebugFile.trace) DebugFile.writeln("ResultSet.close()"); 641 642       oRSet.close(); 643       oRSet = null; 644 645       if (DebugFile.trace) DebugFile.writeln("PreparedStatement.close()"); 646 647       oStmt.close(); 648       oStmt = null; 649 650     } 651     catch (SQLException sqle) { 652       try { if (null!=oRSet) oRSet.close(); 653       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 654       try { if (null!=oStmt) oStmt.close(); 655       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 656       throw new SQLException (sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode()); 657     } 658     catch (ArrayIndexOutOfBoundsException aiob) { 659       try { if (null!=oRSet) oRSet.close(); 660       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 661       try { if (null!=oStmt) oStmt.close(); 662       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 663       throw new ArrayIndexOutOfBoundsException ("DBSubset.load() " + aiob.getMessage()); 664     } 665     catch (NullPointerException npe) { 666       try { if (null!=oRSet) oRSet.close(); 667       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 668       try { if (null!=oStmt) oStmt.close(); 669       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 670       throw new NullPointerException ("DBSubset.load()"); 671     } 672 673     if (DebugFile.trace) 674       { 675       DebugFile.decIdent(); 676       DebugFile.writeln("End DBSubset.load() : "+String.valueOf(iRows)); 677       } 678 679     return iRows; 680   } // load() 681 682   // ---------------------------------------------------------- 683 684   /** 685    * <p>Execute a JDBC Statement and load query ResultSet in an internal bidimensional matrix</p> 686    * @param oConn Database Connection 687    * @param aFilterValues Values to be binded and JDBC PreparedStatement query paramenters. 688    * @return Number of rows retrieved 689    * the maximum number of rows to be retrieved is determined by calling method 690    * setMaxRows(), if setMaxRows() is not called before call() then all rows existing are retrieved. 691    * @throws SQLException 692    */ 693 694   public int load (JDCConnection oConn, Object [] aFilterValues) 695     throws SQLException , ArrayIndexOutOfBoundsException , NullPointerException { 696 697     return load(oConn, aFilterValues, 0); 698   } 699 700   /** 701    * <p>Execute a JDBC Statement and load query ResultSet in an internal bidimensional matrix</p> 702    * @param oConn Database Connection 703    * @param aFilterValues Values to be binded and JDBC PreparedStatement query paramenters. 704    * @param iSkip Number of rows to be skipped before reading. On database systems that support an 705    * OFFSET clause (such as PostgreSQL) the native offset feature of the DBMS is used, in case that 706    * the DBMS does not provide offset capabilities, the data is fetched and discarded at client side 707    * before returning the DBSubset. Care must be taken when skipping a large number of rows in client 708    * side mode as it may cause heavy network traffic and round trips to the database. 709    * @return Number of rows retrieved 710    * the maximum number of rows to be retrieved is determined by calling method 711    * setMaxRows(), if setMaxRows() is not called before call() then all rows existing are retrieved. 712    * @throws SQLException 713    * @throws IllegalArgumentException if iSkip<0 714    */ 715 716   // ---------------------------------------------------------- 717 718   public int load (JDCConnection oConn, Object [] aFilterValues, int iSkip) 719     throws SQLException , IllegalArgumentException , 720     ArrayIndexOutOfBoundsException , NullPointerException { 721 722     PreparedStatement oStmt = null; 723     ResultSet oRSet = null; 724     ResultSetMetaData oMDat; 725 726     int iRows = 0; 727     int iType = (iSkip==0 ? ResultSet.TYPE_FORWARD_ONLY : ResultSet.TYPE_SCROLL_INSENSITIVE); 728     long lQueryTime = 0; 729 730     if (DebugFile.trace) 731       DebugFile.writeln("Begin DBSubset.load([Connection], Object[]," + iSkip + ")"); 732 733     if (iSkip<0) 734       throw new IllegalArgumentException ("row offset must be equal to or greater than zero"); 735 736     if (null==oConn) 737       throw new NullPointerException ("DBSubset.load() JDCConnection parameter is null"); 738 739     if (DebugFile.trace) 740       DebugFile.incIdent(); 741 742     try { 743 744       if (iMaxRows>0) { 745 746         switch (oConn.getDataBaseProduct()) { 747 748           case JDCConnection.DBMS_MSSQL: 749             if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSelect + " OPTION (FAST " + String.valueOf(iMaxRows) + ")" + ")"); 750             oStmt = oConn.prepareStatement(sSelect + " OPTION (FAST " + String.valueOf(iMaxRows) + ")", iType, ResultSet.CONCUR_READ_ONLY); 751             break; 752 753           case JDCConnection.DBMS_POSTGRESQL: 754           case JDCConnection.DBMS_MYSQL: 755             if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSelect + " LIMIT " + String.valueOf(iMaxRows+2) + " OFFSET " + String.valueOf(iSkip) + ")"); 756             oStmt = oConn.prepareStatement(sSelect + " LIMIT " + String.valueOf(iMaxRows+2) + " OFFSET " + String.valueOf(iSkip), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 757             iSkip = 0; // Use PostgreSQL native OFFSET clause, so do not skip any rows before client side fetching 758 break; 759 760           default: 761             if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSelect + ")"); 762             oStmt = oConn.prepareStatement(sSelect, iType, ResultSet.CONCUR_READ_ONLY); 763         } // end switch 764 } 765 766       else { 767         switch (oConn.getDataBaseProduct()) { 768 769           case JDCConnection.DBMS_POSTGRESQL: 770             if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSelect + " OFFSET " + String.valueOf(iSkip) + ")"); 771             oStmt = oConn.prepareStatement(sSelect + " OFFSET " + String.valueOf(iSkip), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 772             iSkip = 0; // Use PostgreSQL native OFFSET clause, so do not skip any rows before client side fetching 773 break; 774 775           default: 776             if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(" + sSelect + ")"); 777             oStmt = oConn.prepareStatement(sSelect, iType, ResultSet.CONCUR_READ_ONLY); 778         } // end switch 779 780       } // fi (iMaxRows) 781 782       try { oStmt.setQueryTimeout(iTimeOut); } catch (SQLException sqle) { if (DebugFile.trace) DebugFile.writeln("Error at PreparedStatement.setQueryTimeout(" + String.valueOf(iTimeOut) + ")" + sqle.getMessage()); } 783 784       for (int p=0; p<aFilterValues.length; p++) { 785         Object oParam = aFilterValues[p]; 786         if (DebugFile.trace) { 787           if (null==oParam) 788             DebugFile.writeln("PreparedStatement.setObject("+String.valueOf(p+1)+",null)"); 789           else 790             DebugFile.writeln("PreparedStatement.setObject("+String.valueOf(p+1)+","+oParam.toString()+")"); 791         } 792         oStmt.setObject(p+1, oParam); 793       } 794 795       if (DebugFile.trace) { 796         DebugFile.writeln("PreparedStatement.executeQuery()"); 797         lQueryTime = System.currentTimeMillis(); 798       } 799 800       oRSet = oStmt.executeQuery(); 801 802       if (DebugFile.trace) { 803         DebugFile.writeln("query executed in " + String.valueOf(System.currentTimeMillis()-lQueryTime) + " ms"); 804         DebugFile.writeln("ResultSet.getMetaData()"); 805       } 806 807       oMDat = oRSet.getMetaData(); 808       iColCount = oMDat.getColumnCount(); 809       ColNames = new String [iColCount]; 810 811       for (int c=1; c<=iColCount; c++) { 812         ColNames[c-1] = oMDat.getColumnName(c).toLowerCase(); 813       } 814       oMDat = null; 815 816       setFetchSize(oConn, oRSet); 817 818       iRows = fetchResultSet(oRSet, iSkip); 819 820       if (DebugFile.trace) DebugFile.writeln("ResultSet.close()"); 821 822       oRSet.close(); 823       oRSet = null; 824 825       if (DebugFile.trace) DebugFile.writeln("PreparedStatement.close()"); 826 827       oStmt.close(); 828       oStmt = null; 829     } 830     catch (SQLException sqle) { 831       try { if (null!=oRSet) oRSet.close(); 832       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 833       try { if (null!=oStmt) oStmt.close(); 834       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 835       throw new SQLException (sqle.getMessage(), sqle.getSQLState(), sqle.getErrorCode()); 836     } 837     catch (ArrayIndexOutOfBoundsException aiob) { 838       try { if (null!=oRSet) oRSet.close(); 839       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 840       try { if (null!=oStmt) oStmt.close(); 841       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 842       throw new ArrayIndexOutOfBoundsException ("DBSubset.load() " + aiob.getMessage()); 843     } 844     catch (NullPointerException npe) { 845       try { if (null!=oRSet) oRSet.close(); 846       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 847       try { if (null!=oStmt) oStmt.close(); 848       } catch (Exception logit) { if (DebugFile.trace) DebugFile.writeln(logit.getClass().getName()+" "+logit.getMessage()); } 849       throw new NullPointerException ("DBSubset.load()"); 850     } 851 852     if (DebugFile.trace) 853       { 854       DebugFile.decIdent(); 855       DebugFile.writeln("End DBSubset.load() : "+String.valueOf(iRows)); 856       } 857 858     return iRows; 859   } // load() 860 861   // ---------------------------------------------------------- 862 863   /** 864    * <p>Find a value in a given column<p> 865    * Value is searched by brute force from the begining to the end of the column.<br> 866    * Trying to find a <b>null</b> value is allowed.<br> 867    * Find is case sensitive. 868    * @param iCol Column to be searched [0..getColumnCount()-1] 869    * @param oVal Value searched 870    * @return Row where seached value was found or -1 is value was not found. 871    */ 872 873   public int find (int iCol, Object oVal) { 874     int iFound = -1; 875     int iRowCount; 876     Object objCol; 877 878     if (DebugFile.trace) { 879       if (null==oVal) 880         DebugFile.writeln("Begin DBSubset.find(" + String.valueOf(iCol)+ ", null)"); 881       else 882         DebugFile.writeln("Begin DBSubset.find(" + String.valueOf(iCol)+ ", " + oVal.toString() + ")"); 883 884       DebugFile.incIdent(); 885     } 886 887     if (oResults!=null) 888       iRowCount = oResults.size(); 889     else 890       iRowCount = -1; 891 892     if (DebugFile.trace) 893       DebugFile.writeln("row count is " + String.valueOf(iRowCount)); 894 895     for (int iRow=0; iRow<iRowCount; iRow++) { 896 897       objCol = get(iCol,iRow); 898 899       if (null!=objCol) { 900         if (null!=oVal) { 901             if (objCol.equals(oVal)) { 902               iFound = iRow; 903               break; 904             } 905         } 906       } 907       else if (null==oVal) { 908         iFound = iRow; 909         break; 910       } // fi() 911 912     } // next (iRow) 913 914     if (DebugFile.trace) { 915       DebugFile.decIdent(); 916       DebugFile.writeln("Begin DBSubset.find() : " + String.valueOf(iFound)); 917     } 918 919     return iFound; 920   } // find 921 922   // ---------------------------------------------------------- 923 924   /** 925    * @return Column delimiter for print() and toString() methods. 926    */ 927   public String getColumnDelimiter() { 928     return sColDelim; 929   } 930 931   // ---------------------------------------------------------- 932 933   /** 934    * @param sDelim Column delimiter for print() and toString() methods. 935    * The default delimiter is '&grave;' character 936    */ 937   public void setColumnDelimiter(String sDelim) { 938     sColDelim=sDelim; 939   } 940 941   // ---------------------------------------------------------- 942 943   /** 944    * @return Row delimiter for print() and toString() methods. 945    */ 946   public String getRowDelimiter() { 947     return sRowDelim; 948   } 949 950   // ---------------------------------------------------------- 951 952   /** 953    * @param sDelim Row delimiter for print() and toString() methods. 954    * The default delimiter is '&uml;' character 955    */ 956 957   public void setRowDelimiter(String sDelim) { 958     sRowDelim=sDelim; 959   } 960 961   // ---------------------------------------------------------- 962 963   /** 964    * @return Text qualifier for quoting fields in print() and toString() methods. 965    */ 966   public String getTextQualifier() { 967     return sTxtQualifier; 968   } 969 970   // ---------------------------------------------------------- 971 972   /** 973    * @param sQualifier Text qualifier for quoting fields in print() and toString() methods. 974    */ 975   public void setTextQualifier(String sQualifier) { 976     sTxtQualifier=sQualifier; 977   } 978 979   // ---------------------------------------------------------- 980 981   /** 982    * @return Number of columns retrieved. 983    */ 984   public int getColumnCount() { 985     return iColCount; 986   } 987 988   // ---------------------------------------------------------- 989 990   /** 991    * <p>Get column names</p> 992    * @return String[] An array with names of columns from the most recently SQL SELECT sentence. 993    * @since 3.0 994    */ 995   public String [] getColumnNames() { 996     return ColNames; 997   } 998 999   // ---------------------------------------------------------- 1000 1001  /** 1002   * @param sColumnName Name of column witch position is to be returned. Column names are case insensitive. 1003   * @return Column position or -1 if no column with such name exists. 1004   */ 1005  public int getColumnPosition(String sColumnName) { 1006    int iColPos = -1; 1007 1008    for (int iCol=0; iCol<iColCount; iCol++) { 1009      if (sColumnName.equalsIgnoreCase(ColNames[iCol])) { 1010        iColPos = iCol; 1011        break; 1012      } 1013    } // endfor 1014 1015    return iColPos; 1016  } // getColumnPosition 1017 1018  // ---------------------------------------------------------- 1019 1020  /** 1021   * @return number of rows retrieved by last call() or load() method invocation. 1022   */ 1023  public int getRowCount() { 1024    int iRows; 1025 1026    if (null==oResults) 1027      iRows = 0; 1028    else 1029      iRows = oResults.size(); 1030 1031    return iRows; 1032 1033  } // getRowCount 1034 1035  // ---------------------------------------------------------- 1036 1037  /** 1038   * Get DBSubset column as a List interface 1039   * @param iCol int Column position [0..getColumnCount()-1] 1040   * @return List 1041   * @throws ArrayIndexOutOfBoundsException 1042   * @throws IllegalStateException if DBSubset has not been loaded 1043   */ 1044  public List getColumnAsList (int iCol) 1045    throws ArrayIndexOutOfBoundsException ,IllegalStateException { 1046    Vector oRow, oCol; 1047    int iRowCount; 1048    if (oResults==null) 1049      throw new IllegalStateException ("DBSubset.getColumnAsList("+String.valueOf(iCol)+") DBSubset not loaded"); 1050    else 1051      iRowCount = oResults.size(); 1052    if (0==iRowCount) { 1053      oCol = new Vector (); 1054    } else { 1055      oCol = new Vector (iRowCount); 1056      for (int iRow=0; iRow<iRowCount; iRow++) { 1057        oRow = (Vector ) oResults.get(iRow); 1058        oCol.add(oRow.get(iCol)); 1059      } // next 1060 } 1061    return oCol; 1062  } // getColumnAsList 1063 1064  // ---------------------------------------------------------- 1065 1066  /** 1067   * Get DBSubset row as a List interface 1068   * @param iRow int Row position [0..getRowCount()-1] 1069   * @return List 1070   * @throws ArrayIndexOutOfBoundsException 1071   * @throws IllegalStateException if DBSubset has not been loaded 1072   */ 1073  public List getRowAsList (int iRow) 1074    throws ArrayIndexOutOfBoundsException ,IllegalStateException { 1075    if (oResults!=null) 1076      return (List ) oResults.get(iRow); 1077    else 1078      throw new IllegalStateException ("DBSubset.getRowAsList("+String.valueOf(iRow)+") DBSubset not loaded"); 1079  } // getRowAsList 1080 1081  // ---------------------------------------------------------- 1082 1083  /** 1084   * Get DBSubset row as a Map interface 1085   * @param iRow int Row position [0..getRowCount()-1] 1086   * @return Map 1087   * @throws ArrayIndexOutOfBoundsException 1088   * @throws IllegalStateException if DBSubset has not been loaded 1089   */ 1090  public Map getRowAsMap (int iRow) 1091    throws ArrayIndexOutOfBoundsException ,IllegalStateException { 1092    if (oResults==null) 1093      throw new IllegalStateException ("DBSubset.getRowAsMap("+String.valueOf(iRow)+") DBSubset not loaded"); 1094 1095    Vector oRow = (Vector ) oResults.get(iRow); 1096    HashMap oRetVal = new HashMap (iColCount*2); 1097 1098    for (int iCol=0; iCol<iColCount; iCol++) { 1099      oRetVal.put(ColNames[iCol], oRow.get(iCol)); 1100    } // endfor 1101 1102    return oRetVal; 1103  } // getRowMap 1104 1105  // ---------------------------------------------------------- 1106 1107  /** 1108   * Get DBSubset row as a Vector 1109   * @param iRow int Row position [0..getRowCount()-1] 1110   * @return Vector 1111   * @throws ArrayIndexOutOfBoundsException 1112   * @throws IllegalStateException if DBSubset has not been loaded 1113   * @since 3.0 1114   */ 1115  public Vector getRowAsVector (int iRow) 1116    throws ArrayIndexOutOfBoundsException ,IllegalStateException { 1117    if (oResults!=null) 1118      return (Vector ) oResults.get(iRow); 1119    else 1120      throw new IllegalStateException ("DBSubset.getRowAsList("+String.valueOf(iRow)+") DBSubset not loaded"); 1121  } // getRowAsVector 1122 1123  // ---------------------------------------------------------- 1124 1125  /** 1126   * <p>Get pre-loaded field</p> 1127   * @param iCol Column position [0..getColumnCount()-1] 1128   * @param iRow Row position [0..getRowCount()-1] 1129   * @throws ArrayIndexOutOfBoundsException 1130   */ 1131  public Object get (int iCol, int iRow) throws ArrayIndexOutOfBoundsException { 1132    return ((Vector ) oResults.get(iRow)).get(iCol); 1133  } 1134 1135  // ---------------------------------------------------------- 1136 1137  /** 1138   * <p>Get pre-loaded field by name</p> 1139   * @param sCol Column name 1140   * @param iRow Row position [0..getRowCount()-1] 1141   * @throws ArrayIndexOutOfBoundsException If no column with such name was found 1142   */ 1143  public Object get (String sCol, int iRow) 1144    throws ArrayIndexOutOfBoundsException { 1145 1146    int iCol = getColumnPosition(sCol); 1147 1148    if (iCol==-1) 1149      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1150 1151    return ((Vector ) oResults.get(iRow)).get(iCol); 1152  } 1153 1154  // ---------------------------------------------------------- 1155 1156  /** 1157   * <p>Get pre-loaded value for a Boolean field</p> 1158   * @param iCol Column position [0..getColumnCount()-1] 1159   * @param iRow Row position [0..getRowCount()-1] 1160   * @return <b>boolean</b> value for field. 1161   * @throws ClassCastException 1162   * @throws ArrayIndexOutOfBoundsException 1163   * @throws NullPointerException 1164   */ 1165  public boolean getBoolean (int iCol, int iRow) 1166    throws ClassCastException ,ArrayIndexOutOfBoundsException ,NullPointerException { 1167 1168    boolean bRetVal; 1169    Object oObj = get(iCol, iRow); 1170 1171    if (oObj.getClass().equals(Integer.TYPE)) 1172 1173      bRetVal = (((Integer )oObj).intValue()!=0 ? true : false); 1174 1175    else if (oObj.getClass().equals(Short.TYPE)) 1176      bRetVal = (((Short )oObj).shortValue()!=(short)0 ? true : false); 1177 1178    else 1179      bRetVal = ((Boolean ) get(iCol, iRow)).booleanValue(); 1180 1181    return bRetVal; 1182  } 1183 1184  // ---------------------------------------------------------- 1185 1186  /** 1187   * <p>Get pre-loaded value for a Date field</p> 1188   * @param iCol Column position [0..getColumnCount()-1] 1189   * @param iRow Row position [0..getRowCount()-1] 1190   * @throws ClassCastException 1191   * @throws ArrayIndexOutOfBoundsException 1192   */ 1193 1194  public java.util.Date getDate(int iCol, int iRow) 1195    throws ClassCastException ,ArrayIndexOutOfBoundsException { 1196    Object oDt = ((Vector ) oResults.get(iRow)).get(iCol); 1197 1198    if (null!=oDt) { 1199      if (oDt.getClass().equals(ClassUtilDate)) 1200        return (java.util.Date ) oDt; 1201      else if (oDt.getClass().equals(ClassTimestamp)) 1202        return new java.util.Date (((java.sql.Timestamp ) oDt).getTime()); 1203      else if (oDt.getClass().equals(ClassSQLDate)) 1204        return new java.util.Date (((java.sql.Date ) oDt).getYear(), ((java.sql.Date ) oDt).getMonth(), ((java.sql.Date ) oDt).getDate()); 1205      else 1206        throw new ClassCastException ("Cannot cast " + oDt.getClass().getName() + " to Date"); 1207    } 1208    else 1209      return null; 1210 1211  } // getDate() 1212 1213  // ---------------------------------------------------------- 1214 1215  /** 1216   * <p>Get pre-loaded value for a Date field</p> 1217   * @param iCol Column position [0..getColumnCount()-1] 1218   * @param iRow Row position [0..getRowCount()-1] 1219   * @return java.sql.Date 1220   * @throws ClassCastException 1221   * @throws ArrayIndexOutOfBoundsException 1222   * @since 3.0 1223   */ 1224 1225  public java.sql.Date getSQLDate(int iCol, int iRow) 1226    throws ClassCastException ,ArrayIndexOutOfBoundsException { 1227    Object oDt = ((Vector ) oResults.get(iRow)).get(iCol); 1228 1229    if (null!=oDt) { 1230      if (oDt.getClass().equals(ClassSQLDate)) 1231        return (java.sql.Date ) oDt; 1232      else if (oDt.getClass().equals(ClassTimestamp)) 1233        return new java.sql.Date (((java.sql.Timestamp ) oDt).getTime()); 1234      else if (oDt.getClass().equals(ClassUtilDate)) 1235        return new java.sql.Date (((java.util.Date ) oDt).getTime()); 1236      else 1237        throw new ClassCastException ("Cannot cast " + oDt.getClass().getName() + " to Date"); 1238    } 1239    else 1240      return null; 1241  } // getSQLDate() 1242 1243  /** 1244   * <p>Get pre-loaded value for a Date field</p> 1245   * @param sCol String Column name 1246   * @param iRow Row position [0..getRowCount()-1] 1247   * @return java.sql.Date 1248   * @throws ClassCastException 1249   * @throws ArrayIndexOutOfBoundsException 1250   * @since 3.0 1251   */ 1252  public java.sql.Date getSQLDate (String sCol, int iRow) throws ArrayIndexOutOfBoundsException { 1253    int iCol = getColumnPosition(sCol); 1254 1255    if (iCol==-1) 1256      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1257 1258    return getSQLDate(iCol, iRow); 1259  } // getSQLDate() 1260 1261  // ---------------------------------------------------------- 1262 1263  /** 1264   * <p>Get pre-loaded value for a Time field</p> 1265   * @param iCol Column position [0..getColumnCount()-1] 1266   * @param iRow Row position [0..getRowCount()-1] 1267   * @throws ClassCastException 1268   * @throws ArrayIndexOutOfBoundsException 1269   * @since 3.0 1270   */ 1271 1272  public java.sql.Time getSQLTime(int iCol, int iRow) 1273    throws ClassCastException ,ArrayIndexOutOfBoundsException { 1274    Object oDt = ((Vector ) oResults.get(iRow)).get(iCol); 1275 1276    if (null!=oDt) { 1277      if (oDt.getClass().equals(ClassSQLTime)) 1278        return (java.sql.Time ) oDt; 1279      else if (oDt.getClass().equals(ClassTimestamp)) 1280        return new java.sql.Time (((java.sql.Timestamp ) oDt).getTime()); 1281      else if (oDt.getClass().equals(ClassUtilDate)) 1282        return new java.sql.Time (((java.util.Date ) oDt).getTime()); 1283      else 1284        throw new ClassCastException ("Cannot cast " + oDt.getClass().getName() + " to Time"); 1285    } 1286    else 1287      return null; 1288  } // getTime() 1289 1290  // ---------------------------------------------------------- 1291 1292  /** 1293   * <p>Get pre-loaded value for a Time field</p> 1294   * @param sCol Column name 1295   * @param iRow Row position [0..getRowCount()-1] 1296   * @return Time 1297   * @throws ClassCastException 1298   * @throws ArrayIndexOutOfBoundsException 1299   * @since 3.0 1300   */ 1301  public java.sql.Time getSQLTime (String sCol, int iRow) 1302    throws ClassCastException , ArrayIndexOutOfBoundsException { 1303    int iCol = getColumnPosition(sCol); 1304 1305    if (iCol==-1) 1306      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1307 1308    return getSQLTime(iCol, iRow); 1309  } // getSQLTime() 1310 1311  // ---------------------------------------------------------- 1312 1313  /** 1314   * <p>Get pre-loaded value for a Date field</p> 1315   * @param sCol Column name 1316   * @param iRow Row position [0..getRowCount()-1] 1317   * @throws ClassCastException 1318   * @throws ArrayIndexOutOfBoundsException if column is not found 1319   */ 1320 1321  public java.util.Date getDate (String sCol, int iRow) throws ArrayIndexOutOfBoundsException { 1322    int iCol = getColumnPosition(sCol); 1323 1324    if (iCol==-1) 1325      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1326 1327    return getDate(iCol, iRow); 1328  } // getDate() 1329 1330  // ---------------------------------------------------------- 1331 1332  /** 1333   * <p>Get pre-loaded value for a Date field formated as a short Date "yyyy-MM-dd"</p> 1334   * @param iCol Column position [0..getColumnCount()-1] 1335   * @param iRow Row position [0..getRowCount()-1] 1336   * @return String with format "yyyy-MM-dd" or <b>null</b>. 1337   * @throws ClassCastException 1338   */ 1339  public String getDateShort(int iCol, int iRow) { 1340    java.util.Date oDt = getDate(iCol, iRow); 1341 1342    if (null==oShortDate) oShortDate = new SimpleDateFormat ("yyyy-MM-dd"); 1343 1344    if (null!=oDt) 1345      return oShortDate.format(oDt); 1346    else 1347      return null; 1348 1349  } // getDateShort() 1350 1351  // ---------------------------------------------------------- 1352 1353  /** 1354   * <p>Get pre-loaded value for a Date field formated as a DateTime "yyyy-MM-dd HH:mm:ss"</p> 1355   * @param iCol Column position [0..getColumnCount()-1] 1356   * @param iRow Row position [0..getRowCount()-1] 1357   * @return String with format "yyyy-MM-dd HH:mm:ss" or <b>null</b>. 1358   * @throws ClassCastException 1359   * @since 2.1 1360   */ 1361 1362  public String getDateTime24(int iCol, int iRow) { 1363    java.util.Date oDt = getDate(iCol, iRow); 1364 1365    SimpleDateFormat oDateTime24 = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss"); 1366 1367    if (null!=oDt) 1368      return oDateTime24.format(oDt); 1369    else 1370      return null; 1371 1372  } // getDateTime24() 1373 1374  // ---------------------------------------------------------- 1375 1376  /** 1377   * <p>Get pre-loaded value for a Date field formated as a DateTime "yyyy-MM-dd hh:mm:ss"</p> 1378   * @param iCol Column position [0..getColumnCount()-1] 1379   * @param iRow Row position [0..getRowCount()-1] 1380   * @throws ClassCastException 1381   * @throws ArrayIndexOutOfBoundsException 1382   * @return String with format "yyyy-MM-dd hh:mm:ss" or <b>null</b>. 1383   */ 1384 1385  public String getDateTime(int iCol, int iRow) 1386    throws ClassCastException , ArrayIndexOutOfBoundsException { 1387 1388    java.util.Date oDt = getDate(iCol, iRow); 1389 1390    if (null==oDateTime) oDateTime = new SimpleDateFormat ("yyyy-MM-dd hh:mm:ss"); 1391 1392    if (null!=oDt) 1393      return oDateTime.format(oDt); 1394    else 1395      return null; 1396  } // getDateShort() 1397 1398  // ---------------------------------------------------------- 1399 1400  /** 1401   * <p>Get pre-loaded value for a Date field formated with a used defind formar</p> 1402   * @param iCol Column position [0..getColumnCount()-1] 1403   * @param iRow Row position [0..getRowCount()-1] 1404   * @param sFormat Date Format (like "yyyy-MM-dd HH:mm:ss") 1405   * @throws ClassCastException 1406   * @throws ArrayIndexOutOfBoundsException 1407   * @return Date value formated as String. 1408   * @see java.text.SimpleDateFormat 1409   */ 1410 1411  public String getDateFormated(int iCol, int iRow, String sFormat) 1412    throws ArrayIndexOutOfBoundsException , ClassCastException { 1413 1414    java.util.Date oDt = getDate(iCol, iRow); 1415    SimpleDateFormat oSimpleDate; 1416 1417    if (null!=oDt) { 1418      oSimpleDate = new SimpleDateFormat (sFormat); 1419      return oSimpleDate.format(oDt); 1420    } 1421    else 1422      return null; 1423 1424  } // getDateFormated() 1425 1426  // ---------------------------------------------------------- 1427 1428  /** 1429   * <p>Get pre-loaded value and tries to convert it into a Short</p> 1430   * @param iCol Column position [0..getColumnCount()-1] 1431   * @param iRow Row position [0..getRowCount()-1] 1432   * @throws NullPointerException If field value is <b>null</b> 1433   * @throws ArrayIndexOutOfBoundsException 1434   */ 1435 1436  public short getShort (int iCol, int iRow) 1437    throws NullPointerException ,ArrayIndexOutOfBoundsException { 1438 1439    Object oVal = (((Vector ) oResults.get(iRow)).get(iCol)); 1440    Class oCls; 1441    short iRetVal; 1442 1443    oCls = oVal.getClass(); 1444 1445    try { 1446      if (oCls.equals(Short.TYPE)) 1447        iRetVal = ((Short ) oVal).shortValue(); 1448      else if (oCls.equals(Integer.TYPE)) 1449        iRetVal = (short) ((Integer ) oVal).intValue(); 1450      else if (oCls.equals(Class.forName("java.math.BigDecimal"))) 1451        iRetVal = (short) ((java.math.BigDecimal ) oVal).intValue(); 1452      else if (oCls.equals(Float.TYPE)) 1453        iRetVal = (short) ((Float ) oVal).intValue(); 1454      else if (oCls.equals(Double.TYPE)) 1455        iRetVal = (short) ((Double ) oVal).intValue(); 1456      else 1457        iRetVal = new Short (oVal.toString()).shortValue(); 1458    } catch (ClassNotFoundException cnfe) { /* never thrown */ iRetVal = (short)0; } 1459 1460    return iRetVal; 1461  } // getShort 1462 1463  // ---------------------------------------------------------- 1464 1465  /** 1466   * <p>Get pre-loaded value and tries to convert it into a int</p> 1467   * @param iCol Column position [0..getColumnCount()-1] 1468   * @param iRow Row position [0..getRowCount()-1] 1469   * @throws NullPointerException If field value is <b>null</b> 1470   * @throws ArrayIndexOutOfBoundsException 1471   */ 1472 1473  public int getInt (int iCol, int iRow) 1474    throws NullPointerException ,ArrayIndexOutOfBoundsException { 1475 1476    Object oVal = (((Vector ) oResults.get(iRow)).get(iCol)); 1477 1478    if (oVal.getClass().equals(Integer.TYPE)) 1479      return ((Integer )oVal).intValue(); 1480    else 1481      return getInteger(iCol, iRow).intValue(); 1482  } 1483 1484  // ---------------------------------------------------------- 1485 1486  /** 1487   * <p>Get pre-loaded value and tries to convert it into a Short</p> 1488   * @param sCol Column name 1489   * @param iRow Row position [0..getRowCount()-1] 1490   * @throws ArrayIndexOutOfBoundsException if column is not found 1491   * @throws NullPointerException If field value is <b>null</b> 1492   */ 1493 1494  public int getInt (String sCol, int iRow) 1495    throws ArrayIndexOutOfBoundsException , NullPointerException { 1496 1497    int iCol = getColumnPosition(sCol); 1498 1499    if (iCol==-1) 1500      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1501 1502    Object oVal = (((Vector ) oResults.get(iRow)).get(iCol)); 1503 1504    if (oVal.getClass().equals(Integer.TYPE)) 1505 1506      return ((Integer )oVal).intValue(); 1507 1508    else 1509 1510      return getInteger(iCol, iRow).intValue(); 1511 1512  } // getInt 1513 1514  // ---------------------------------------------------------- 1515 1516  /** 1517   * <p>Get pre-loaded value and tries to convert it into a double</p> 1518   * @param iCol Column position [0..getColumnCount()-1] 1519   * @param iRow Row position [0..getRowCount()-1] 1520   * @throws NullPointerException If field value is <b>null</b> 1521   * @throws ArrayIndexOutOfBoundsException 1522   */ 1523 1524  public double getDouble (int iCol, int iRow) 1525    throws NullPointerException ,ArrayIndexOutOfBoundsException { 1526 1527    Object oVal = (((Vector ) oResults.get(iRow)).get(iCol)); 1528    Class oCls; 1529    double dRetVal; 1530 1531    oCls = oVal.getClass(); 1532 1533    try { 1534      if (oCls.equals(Short.TYPE)) 1535        dRetVal = (double) ((Short ) oVal).shortValue(); 1536      else if (oCls.equals(Integer.TYPE)) 1537        dRetVal = (double) ((Integer ) oVal).intValue(); 1538      else if (oCls.equals(Class.forName("java.math.BigDecimal"))) 1539        dRetVal = ((java.math.BigDecimal ) oVal).doubleValue(); 1540      else if (oCls.equals(Float.TYPE)) 1541        dRetVal = ((Float ) oVal).doubleValue(); 1542      else if (oCls.equals(Double.TYPE)) 1543        dRetVal = ((Double ) oVal).doubleValue(); 1544      else 1545        dRetVal = new Double (Gadgets.removeChar(oVal.toString(),',')).doubleValue(); 1546    } catch (ClassNotFoundException cnfe) { /* never thrown */ dRetVal = 0d; } 1547 1548    return dRetVal; 1549  } // getDouble 1550 1551  // ---------------------------------------------------------- 1552 1553  /** 1554   * <p>Get pre-loaded value and tries to convert it into a float</p> 1555   * @param iCol Column position [0..getColumnCount()-1] 1556   * @param iRow Row position [0..getRowCount()-1] 1557   * @throws NullPointerException If field value is <b>null</b> 1558   * @throws ArrayIndexOutOfBoundsException 1559   */ 1560 1561  public float getFloat (int iCol, int iRow) 1562    throws NullPointerException ,ArrayIndexOutOfBoundsException { 1563 1564    Object oVal = (((Vector ) oResults.get(iRow)).get(iCol)); 1565    Class oCls; 1566    float fRetVal; 1567 1568    oCls = oVal.getClass(); 1569 1570    try { 1571      if (oCls.equals(Short.TYPE)) 1572        fRetVal = (float) ((Short ) oVal).shortValue(); 1573      else if (oCls.equals(Integer.TYPE)) 1574        fRetVal = (float) ((Integer ) oVal).intValue(); 1575      else if (oCls.equals(Class.forName("java.math.BigDecimal"))) 1576        fRetVal = ((java.math.BigDecimal ) oVal).floatValue(); 1577      else if (oCls.equals(Float.TYPE)) 1578        fRetVal = ((Float ) oVal).floatValue(); 1579      else if (oCls.equals(Double.TYPE)) 1580        fRetVal = ((Double ) oVal).floatValue(); 1581      else 1582        fRetVal = new Float (Gadgets.removeChar(oVal.toString(),',')).floatValue(); 1583    } catch (ClassNotFoundException cnfe) { /* never thrown */ fRetVal = 0f; } 1584 1585    return fRetVal; 1586  } // getFloat 1587 1588  // ---------------------------------------------------------- 1589 1590  /** 1591   * <p>Get pre-loaded value and tries to convert it into a Short</p> 1592   * @param sCol Column name 1593   * @param iRow Row position [0..getRowCount()-1] 1594   * @throws ArrayIndexOutOfBoundsException if column is not found 1595   * @throws NullPointerException If field value is <b>null</b> 1596   */ 1597 1598  public float getFloat (String sCol, int iRow) 1599    throws NullPointerException ,ArrayIndexOutOfBoundsException { 1600 1601    int iCol = getColumnPosition(sCol); 1602 1603    if (iCol==-1) 1604      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1605 1606    return getFloat(iCol, iRow); 1607  } 1608 1609  // ---------------------------------------------------------- 1610 1611  /** 1612   * <p>Get pre-loaded value and tries to convert it into a float</p> 1613   * @param iCol Column position [0..getColumnCount()-1] 1614   * @param iRow Row position [0..getRowCount()-1] 1615   * @param iDecimals Decimal places for float value 1616   * @throws ArrayIndexOutOfBoundsException if column is not found 1617   * @throws NullPointerException If field value is <b>null</b> 1618   */ 1619 1620  public float getFloat (int iCol, int iRow, int iDecimals) 1621    throws NullPointerException ,ArrayIndexOutOfBoundsException { 1622 1623    float p, f = getFloat (iCol, iRow); 1624    int i; 1625 1626    if (0==iDecimals) 1627 1628      return (float) ((int) f); 1629 1630    else { 1631 1632      p = 10f; 1633      for (int d=0; d<iDecimals; d++) p*=10; 1634      i = (int) (f * p); 1635 1636      return ((float)i) / p; 1637    } 1638  } // getFloat 1639 1640  // ---------------------------------------------------------- 1641 1642  /** 1643   * <p>Get pre-loaded value and tries to convert it into a float</p> 1644   * @param sCol Column name 1645   * @param iRow Row position [0..getRowCount()-1] 1646   * @param iDecimals Decimal places for float value 1647   * @throws ArrayIndexOutOfBoundsException if column is not found 1648   * @throws NullPointerException If field value is <b>null</b> 1649   */ 1650 1651  public float getFloat (String sCol, int iRow, int iDecimals) 1652    throws ArrayIndexOutOfBoundsException , NullPointerException { 1653 1654    int iCol = getColumnPosition(sCol); 1655 1656    if (iCol==-1) 1657      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1658 1659    return getFloat(iCol, iRow, iDecimals); 1660  } // getFloat 1661 1662  // ---------------------------------------------------------- 1663 1664  /** 1665   * <p>Get pre-loaded value and tries to convert it into an Integer</p> 1666   * @param iCol Column position [0..getColumnCount()-1] 1667   * @param iRow Row position [0..getRowCount()-1] 1668   * @return Field value converted to Integer or <b>null</b> if field was NULL. 1669   */ 1670 1671  public Integer getInteger (int iCol, int iRow) 1672    throws ArrayIndexOutOfBoundsException { 1673 1674    Object oVal = (((Vector ) oResults.get(iRow)).get(iCol)); 1675    Class oCls; 1676    Integer iRetVal; 1677 1678    if (null==oVal) return null; 1679 1680    oCls = oVal.getClass(); 1681 1682    try { 1683      if (oCls.equals(Short.TYPE)) 1684        iRetVal = new Integer (((Short ) oVal).intValue()); 1685      else if (oCls.equals(Integer.TYPE)) 1686        iRetVal = (Integer ) oVal; 1687      else if (oCls.equals(Class.forName("java.math.BigDecimal"))) 1688        iRetVal = new Integer (((java.math.BigDecimal ) oVal).intValue()); 1689      else if (oCls.equals(Float.TYPE)) 1690        iRetVal = new Integer (((Float ) oVal).intValue()); 1691      else if (oCls.equals(Double.TYPE)) 1692        iRetVal = new Integer (((Double ) oVal).intValue()); 1693      else 1694        iRetVal = new Integer (oVal.toString()); 1695    } catch (ClassNotFoundException cnfe) { /* never thrown */ iRetVal = null; } 1696 1697    return iRetVal; 1698 1699  } // getInteger 1700 1701  // ---------------------------------------------------------- 1702 1703  /** 1704   * <p>Get pre-loaded value and tries to convert it into an Integer</p> 1705   * @param sCol Column name 1706   * @param iRow Row position [0..getRowCount()-1] 1707   * @return Field value converted to Integer or <b>null</b> if field was NULL. 1708   * @throws ArrayIndexOutOfBoundsException if column is not found 1709   */ 1710 1711  public Integer getInteger (String sCol, int iRow) 1712    throws ArrayIndexOutOfBoundsException { 1713 1714    int iCol = getColumnPosition(sCol); 1715 1716    if (iCol==-1) 1717      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1718 1719    return getInteger(iCol, iRow); 1720  } 1721 1722  // ---------------------------------------------------------- 1723 1724  /** 1725   * <p>Get pre-loaded value and tries to convert it into a BigDecimal</p> 1726   * If column is NULL then <b>null</b> value is returned.<BR> 1727   * If base columnn is of type String then thsi function will try to parse the 1728   * value into a BigDecimal. A single dot '.' is used as decimal delimiter no 1729   * matter which is the current locale. All comma characters ',' are removed 1730   * before parsing String into BigDecimal. 1731   * @param iCol Column position [0..getColumnCount()-1] 1732   * @param iRow Row position [0..getRowCount()-1] 1733   * @return Field value converted to BigDecimal or <b>null</b> if field was NULL. 1734   * @throws java.lang.ClassCastException 1735   * @throws java.lang.NumberFormatException 1736   */ 1737 1738  public BigDecimal getDecimal (int iCol, int iRow) 1739    throws java.lang.ClassCastException , java.lang.NumberFormatException { 1740    Object oVal = (((Vector ) oResults.get(iRow)).get(iCol)); 1741    Class oCls; 1742    BigDecimal oDecVal; 1743 1744    if (oVal==null) return null; 1745 1746    oCls = oVal.getClass(); 1747 1748      if (oCls.equals(Short.TYPE)) 1749        oDecVal = new BigDecimal (((Short ) oVal).doubleValue()); 1750      else if (oCls.equals(Integer.TYPE)) 1751        oDecVal = new BigDecimal (((Integer ) oVal).doubleValue()); 1752      else if (oCls.equals(Float.TYPE)) 1753        oDecVal = new BigDecimal (((Float ) oVal).doubleValue()); 1754      else if (oCls.equals(Double.TYPE)) 1755        oDecVal = new BigDecimal (((Double ) oVal).doubleValue()); 1756      else if (oCls.getName().equalsIgnoreCase("java.lang.String")) 1757        oDecVal = new BigDecimal (Gadgets.removeChar((String ) oVal, ',')); 1758      else { 1759        try { 1760          oDecVal = (BigDecimal ) oVal; 1761        } catch (ClassCastException cce) { 1762          throw new ClassCastException ("Cannot cast column of type " + oVal.getClass().getName() + " to BigDecimal"); 1763        } 1764      } 1765 1766    return oDecVal; 1767  } // getDecimal 1768 1769  // ---------------------------------------------------------- 1770 1771  /** 1772   * <p>Get pre-loaded value and tries to convert it into a BigDecimal</p> 1773   * @param sCol Column name 1774   * @param iRow Row position [0..getRowCount()-1] 1775   * @return Field value converted to BigDecimal or <b>null</b> if field was NULL. 1776   * @throws ArrayIndexOutOfBoundsException if column is not found 1777   */ 1778  public BigDecimal getDecimal (String sCol, int iRow) 1779    throws ArrayIndexOutOfBoundsException { 1780 1781    int iCol = getColumnPosition(sCol); 1782 1783    if (iCol==-1) 1784      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1785 1786    return getDecimal(iCol, iRow); 1787  } // getDecimal 1788 1789  // ---------------------------------------------------------- 1790 1791  /** 1792   * <p>Get decimal formated as a String using the given pattern and the symbols for the default locale</p> 1793   * @param iCol Column position [0..getColumnCount()-1] 1794   * @param iRow Row position [0..getRowCount()-1] 1795   * @param sPattern A non-localized pattern string, for example: "#0.00" 1796   * @return String decimal value formated according to sPatern or <b>null</b> 1797   * @throws ClassCastException 1798   * @throws NumberFormatException 1799   * @throws NullPointerException if sPattern is <b>null</b> 1800   * @throws IllegalArgumentException if sPattern is invalid 1801   */ 1802  public String getDecimalFormated (int iCol, int iRow, String sPattern) 1803    throws java.lang.ClassCastException , java.lang.NumberFormatException , 1804    java.lang.NullPointerException , java.lang.IllegalArgumentException { 1805    BigDecimal oDecVal = getDecimal(iCol, iRow); 1806 1807    if (null==oDecVal) { 1808      return null; 1809    } else { 1810      if (oDecFmt==null) { 1811        oDecFmt = new DecimalFormat (sPattern); 1812        return oDecFmt.format(oDecVal.doubleValue()); 1813      } else { 1814        if (oDecFmt.toPattern().equals(sPattern)) { 1815          return oDecFmt.format(oDecVal.doubleValue()); 1816        } else { 1817          oDecFmt = new DecimalFormat (sPattern); 1818          return oDecFmt.format(oDecVal.doubleValue()); 1819        } 1820      } 1821    } 1822  } // getDecimalFormated 1823 1824  // ---------------------------------------------------------- 1825 1826  /** 1827   * <p>Get decimal formated as a String using the given pattern and the symbols for the default locale</p> 1828   * @param sCol Column name 1829   * @param iRow Row position [0..getRowCount()-1] 1830   * @param sPattern A non-localized pattern string, for example: "#0.00" 1831   * @return String decimal value formated according to sPatern or <b>null</b> 1832   * @throws ClassCastException 1833   * @throws NumberFormatException 1834   * @throws NullPointerException if sPattern is <b>null</b> 1835   * @throws IllegalArgumentException if sPattern is invalid 1836   * @throws ArrayIndexOutOfBoundsException if column is not found 1837   */ 1838  public String getDecimalFormated (String sCol, int iRow, String sPattern) 1839    throws java.lang.ClassCastException , java.lang.NumberFormatException , 1840           java.lang.NullPointerException , java.lang.IllegalArgumentException , 1841           java.lang.ArrayIndexOutOfBoundsException { 1842 1843    int iCol = getColumnPosition(sCol); 1844 1845    if (iCol==-1) 1846      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1847 1848    return getDecimalFormated(iCol, iRow, sPattern); 1849  } // getDecimalFormated 1850 1851  // ---------------------------------------------------------- 1852 1853  /** 1854   * <p>Get value of a VARCHAR field that holds a money+currency amount<p> 1855   * Money values are stored with its currency sign embedded inside, 1856   * like "26.32 USD" or "$48.3" or "35.44 €" 1857   * @param iCol int Column position [0..getColumnCount()-1] 1858   * @param iRow int Row position [0..getRowCount()-1] 1859   * @return com.knowgate.math.Money 1860   * @throws ArrayIndexOutOfBoundsException 1861   * @throws NumberFormatException 1862   * @since 3.0 1863   */ 1864  public Money getMoney(int iCol, int iRow) 1865    throws ArrayIndexOutOfBoundsException ,NumberFormatException { 1866    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 1867 1868    if (null!=obj) 1869      if (obj.toString().length()>0) 1870        return Money.parse(obj.toString()); 1871      else 1872        return null; 1873    else 1874      return null; 1875  } // getMoney 1876 1877  // ---------------------------------------------------------- 1878 1879  /** 1880   * <p>Get value of a VARCHAR field that holds a money+currency amount<p> 1881   * Money values are stored with its currency sign embedded inside, 1882   * like "26.32 USD" or "$48.3" or "35.44 €" 1883   * @param iCol int Column position [0..getColumnCount()-1] 1884   * @param iRow int Row position [0..getRowCount()-1] 1885   * @return com.knowgate.math.Money 1886   * @throws ArrayIndexOutOfBoundsException if column is not found 1887   * @throws NumberFormatException 1888   * @since 3.0 1889   */ 1890  public Money getMoney(String sCol, int iRow) 1891    throws ArrayIndexOutOfBoundsException ,NumberFormatException { 1892    int iCol = getColumnPosition(sCol); 1893 1894    if (iCol==-1) throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1895 1896    return getMoney(iCol, iRow); 1897  } // getMoney 1898 1899  // ---------------------------------------------------------- 1900 1901  /** 1902   * <p>Get toString() form of pre-loaded value</p> 1903   * @param iCol Column position [0..getColumnCount()-1] 1904   * @param iRow Row position [0..getRowCount()-1] 1905   * @return Field value converted to String or <b>null</b> if field was NULL. 1906   * @throws ArrayIndexOutOfBoundsException 1907   */ 1908 1909  public String getString (int iCol, int iRow) 1910    throws ArrayIndexOutOfBoundsException { 1911 1912    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 1913 1914    if (null!=obj) 1915      return obj.toString(); 1916    else 1917      return null; 1918 1919  } // getString 1920 1921  // ---------------------------------------------------------- 1922 1923  /** 1924   * <p>Get toString() form of pre-loaded value</p> 1925   * @param iCol Column position [0..getColumnCount()-1] 1926   * @param iRow Row position [0..getRowCount()-1] 1927   * @param sDef Default value 1928   * @return Field value converted to String default value sDef if field was NULL. 1929   */ 1930 1931  public String getStringNull (int iCol, int iRow, String sDef) 1932    throws ArrayIndexOutOfBoundsException { 1933    String str = getString(iCol,iRow); 1934 1935    return (null!=str ? str : sDef); 1936 1937  } // getStringNull 1938 1939  // ---------------------------------------------------------- 1940 1941  /** 1942   * <p>Get toString() form of pre-loaded value</p> 1943   * @param sCol Column name 1944   * @param iRow Row position [0..getRowCount()-1] 1945   * @return Field value converted to String or <b>null</b> if field was NULL. 1946   * @throws ArrayIndexOutOfBoundsException if column is not found 1947   */ 1948 1949  public String getString (String sCol, int iRow) 1950    throws ArrayIndexOutOfBoundsException { 1951    int iCol = getColumnPosition(sCol); 1952 1953    if (iCol==-1) 1954      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1955 1956    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 1957 1958    if (null!=obj) 1959      return obj.toString(); 1960    else 1961      return null; 1962  } // getString 1963 1964  // ---------------------------------------------------------- 1965 1966  /** 1967   * <p>Get toString() form of pre-loaded value</p> 1968   * @param sCol Column name 1969   * @param iRow Row position [0..getRowCount()-1] 1970   * @param sDef Default value 1971   * @return Field value converted to String default value sDef if field was NULL. 1972   * @throws ArrayIndexOutOfBoundsException if column is not found 1973   */ 1974 1975  public String getStringNull (String sCol, int iRow, String sDef) 1976    throws ArrayIndexOutOfBoundsException { 1977    int iCol = getColumnPosition(sCol); 1978 1979    if (iCol==-1) 1980      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 1981 1982    String str = getString(iCol,iRow); 1983 1984    return (null!=str ? str : sDef); 1985  } // getStringNull 1986 1987  // ---------------------------------------------------------- 1988 1989  /** 1990   * Get Time column 1991   * @param iCol Column position [0..getColumnCount()-1] 1992   * @param iRow Row position [0..getRowCount()-1] 1993   * @return java.sql.Time 1994   * @throws ArrayIndexOutOfBoundsException 1995   * @throws ClassCastException 1996   * @since 3.0 1997   */ 1998  public Time getTimeOfDay (int iCol, int iRow) 1999    throws ArrayIndexOutOfBoundsException , ClassCastException { 2000 2001    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 2002 2003    if (null!=obj) 2004      return (Time ) obj; 2005    else 2006      return null; 2007  } // getTimeOfDay 2008 2009  // ---------------------------------------------------------- 2010 2011  /** 2012   * Get Timestamp columnn 2013   * @param iCol Column position [0..getColumnCount()-1] 2014   * @param iRow Row position [0..getRowCount()-1] 2015   * @return java.sql.Timestamp 2016   * @throws ArrayIndexOutOfBoundsException 2017   * @throws ClassCastException 2018   * @since 2.2 2019   */ 2020  public Timestamp getTimestamp(int iCol, int iRow) 2021    throws ArrayIndexOutOfBoundsException ,ClassCastException { 2022    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 2023 2024    if (null!=obj) { 2025      if (obj instanceof Timestamp ) 2026        return (Timestamp ) obj; 2027      else if (obj instanceof Date ) 2028        return new Timestamp (((Date )obj).getTime()); 2029      else 2030        throw new ClassCastException ("Cannot cast "+obj.getClass().getName()+" to Timestamp"); 2031    } 2032    else 2033      return null; 2034  } 2035 2036  // ---------------------------------------------------------- 2037 2038  /** 2039   * Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT 2040   * @param iCol Column position [0..getColumnCount()-1] 2041   * @param iRow Row position [0..getRowCount()-1] 2042   * @return long Miliseconds or zero if column is <b>null</b> 2043   * @throws ArrayIndexOutOfBoundsException 2044   * @throws ClassCastException 2045   * @since 2.2 2046   */ 2047  public long getTimeMilis(int iCol, int iRow) 2048    throws ArrayIndexOutOfBoundsException ,ClassCastException { 2049    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 2050 2051    if (null!=obj) { 2052      if (obj instanceof Timestamp ) 2053        return ((Timestamp ) obj).getTime(); 2054      else if (obj instanceof Date ) 2055        return ((Date ) obj).getTime(); 2056      else 2057        throw new ClassCastException ("Cannot cast "+obj.getClass().getName()+" to Timestamp"); 2058    } 2059    else 2060      return 0; 2061  } 2062 2063  // ---------------------------------------------------------- 2064 2065  /** 2066   * <p>Return interval value in miliseconds</p> 2067   * This method is only for PostgreSQL 8.0 or later 2068   * @param iCol Column position [0..getColumnCount()-1] 2069   * @param iRow Row position [0..getRowCount()-1] 2070   * @return long Interval in miliseconds. If interval is null then zero is returned.<br> 2071   * For Postgres 7.4 and earlier versions this method always return zero 2072   * even if the interval column is not null. 2073   * @throws ArrayIndexOutOfBoundsException 2074   * @throws ClassCastException 2075   * @since v2.2 2076   */ 2077  public long getIntervalMilis (int iCol, int iRow) 2078    throws ArrayIndexOutOfBoundsException ,ClassCastException { 2079    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 2080 2081    if (null==obj) 2082      return 0l; 2083    else if (obj.getClass().getName().equals("org.postgresql.util.PGInterval")) { 2084      final long SecMilis = 1000l, MinMilis = 60000l, HourMilis=3600000l, DayMilis=86400000l; 2085      long lInterval; 2086      String [] aHMS; 2087      String sInt = obj.toString(); 2088      int iDays = sInt.indexOf("days"); 2089      if (iDays>0) { 2090        lInterval = Long.parseLong(sInt.substring(0,iDays-1)); 2091        aHMS = Gadgets.split(sInt.substring(iDays+5),':'); 2092      } 2093      else { 2094        lInterval =0; 2095        aHMS = Gadgets.split(sInt,':'); 2096      } 2097      lInterval += Long.parseLong(aHMS[0])*HourMilis+Long.parseLong(aHMS[1])*MinMilis+Long.parseLong(aHMS[2])*SecMilis; 2098      return lInterval; 2099    } 2100    else 2101      throw new ClassCastException ("Cannot cast "+obj.getClass().getName()+" to Timestamp"); 2102  } // getIntervalMilis 2103 2104  // ---------------------------------------------------------- 2105 2106  /** 2107   * Pre-allocate a given number of empty rows and columns 2108   * @param nCols Number of columns per row 2109   * @param nRows Number of rows to allocate 2110   * @since 2.2 2111   */ 2112  public void ensureCapacity(int nCols, int nRows) { 2113    if (DebugFile.trace) { 2114      DebugFile.writeln("Begin DBSubset.ensureCapacity("+String.valueOf(nCols)+","+String.valueOf(nRows)+")"); 2115      DebugFile.incIdent(); 2116    } 2117 2118    oResults = new Vector (nRows); 2119    for (int r=0;r<nRows; r++) { 2120      Vector oNewRow = new Vector (nCols); 2121      for (int c=0; c<nCols; c++) { 2122        oNewRow.add(null); 2123      } 2124      oResults.add(oNewRow); 2125    } 2126    if (DebugFile.trace) { 2127      DebugFile.decIdent(); 2128      DebugFile.writeln("End DBSubset.ensureCapacity()"); 2129    } 2130  } // ensureCapacity 2131 2132  // ---------------------------------------------------------- 2133 2134  /** 2135   * Set an element for a loaded DBSubset 2136   * @param oObj Object Reference 2137   * @param iCol Column Index [0..getColumnCount()-1] 2138   * @param iRow Row Index [0..getRowCount()-1] 2139   * @throws ArrayIndexOutOfBoundsException 2140   */ 2141  public void setElementAt (Object oObj, int iCol, int iRow) throws ArrayIndexOutOfBoundsException { 2142 2143    if (DebugFile.trace) { 2144      if (oObj==null) 2145        DebugFile.writeln("DBSubset.setElementAt(null,"+String.valueOf(iCol)+","+String.valueOf(iRow)+")"); 2146      else 2147        DebugFile.writeln("DBSubset.setElementAt("+oObj.toString()+","+String.valueOf(iCol)+","+String.valueOf(iRow)+")"); 2148      DebugFile.incIdent(); 2149    } 2150 2151    if (null==oResults) { 2152      if (DebugFile.trace) DebugFile.writeln("new Vector("+String.valueOf(iFetch)+",1)"); 2153      oResults = new Vector (iFetch, 1); 2154    } 2155 2156    Vector oRow; 2157    Object oRaw = oResults.get(iRow); 2158 2159    if (null==oRaw) { 2160      if (DebugFile.trace) DebugFile.writeln("new Vector("+String.valueOf(iCol)+",1)"); 2161      oRow = new Vector (iCol, 1); 2162      oResults.add(iRow, oRow); 2163    } 2164    else { 2165      oRow = (Vector ) oRaw; 2166    } 2167 2168    oRow.setElementAt (oObj, iCol); 2169 2170    if (DebugFile.trace) { 2171      DebugFile.decIdent(); 2172      DebugFile.writeln("End DBSubset.setElementAt()"); 2173    } 2174  } // setElementAt 2175 2176  // ---------------------------------------------------------- 2177 2178  /** 2179   * Set an element for a loaded DBSubset 2180   * @param oObj Object Reference 2181   * @param sCol Column Name 2182   * @param iRow Row Index [0..getColumnCount()-1] 2183   * @throws ArrayIndexOutOfBoundsException 2184   */ 2185  public void setElementAt (Object oObj, String sCol, int iRow) 2186    throws ArrayIndexOutOfBoundsException { 2187    int iCol = getColumnPosition(sCol); 2188    if (-1==iCol) 2189      throw new ArrayIndexOutOfBoundsException ("DBSubset.setElementAt() column "+sCol+" not found"); 2190    else 2191      setElementAt (oObj, iCol, iRow); 2192  } // setElementAt 2193 2194  // ---------------------------------------------------------- 2195 2196  /** 2197   * @param iCol Column position [0..getColumnCount()-1] 2198   * @param iRow Row position [0..getRowCount()-1] 2199   * @return <b>true</b> if pre-load field is <b>null</b>, <b>false</b> otherwise. 2200   * @throws ArrayIndexOutOfBoundsException 2201   */ 2202  public boolean isNull (int iCol, int iRow) 2203    throws ArrayIndexOutOfBoundsException { 2204    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 2205 2206    return (null==obj); 2207 2208  } // isNull() 2209 2210 2211  // ---------------------------------------------------------- 2212 2213  /** 2214   * @param sCol Column name 2215   * @param iRow Row position [0..getRowCount()-1] 2216   * @return <b>true</b> if pre-load field is <b>null</b>, <b>false</b> otherwise. 2217   * @throws ArrayIndexOutOfBoundsException if column is not found 2218   */ 2219 2220  public boolean isNull (String sCol, int iRow) throws ArrayIndexOutOfBoundsException { 2221    int iCol = getColumnPosition(sCol); 2222 2223    if (iCol==-1) 2224      throw new ArrayIndexOutOfBoundsException ("Column " + sCol + " not found"); 2225 2226    Object obj = (((Vector ) oResults.get(iRow)).get(iCol)); 2227 2228    return (null==obj); 2229  } // isNull() 2230 2231  // ---------------------------------------------------------- 2232 2233  /** 2234   * <p>Write DBSubset to a delimited text string using the column and row delimiters 2235   * stablished at setColumnDelimiter() and setRowDelimiter() properties.</p> 2236   * @return String dump of the whole DBSubset pre-loaded data. 2237   */ 2238 2239  public String toString() { 2240    Vector vRow; 2241    int iCol; 2242    int iRowCount; 2243    StringBuffer strBuff; 2244 2245    if (oResults==null) return ""; 2246 2247    iRowCount = oResults.size(); 2248 2249    if (iRowCount==0) return ""; 2250 2251    strBuff = new StringBuffer (64*iRowCount); 2252 2253    for (int iRow=0; iRow<iRowCount; iRow++) 2254      { 2255      vRow = (Vector ) oResults.get(iRow); 2256      iCol = 0; 2257      while (iCol<iColCount) 2258        { 2259        strBuff.append(vRow.get(iCol)); 2260        if (++iCol<iColCount) strBuff.append(sColDelim); 2261        } 2262      strBuff.append(sRowDelim); 2263      } 2264 2265  return strBuff.toString(); 2266  } // toString() 2267 2268  // ---------------------------------------------------------- 2269 2270  /** 2271   * <p>Write DBSubset to an XML string</p> 2272   * @param sIdent Initial space identations on the left for fields 2273   * @param sNode Name of top parent node. If <b>null</b> then main table name 2274   * for this DBSubset is used. 2275   * @param sDateFormat Output format for date values 2276   * @param sDecimalFormat Output format for decimal and floating point values 2277   * @return XML string dump of the whole DBSubset pre-loaded data. 2278   */ 2279 2280  public String toXML(String sIdent, String sNode, 2281                      String sDateFormat, String sDecimalFormat) { 2282    Vector vRow; 2283    int iAs; 2284    int iCol; 2285    int iDot; 2286    int iRowCount; 2287    int iTokCount; 2288    StringBuffer strBuff; 2289    StringTokenizer strTok; 2290    String sLabel; 2291    String sNodeName; 2292    Object oColValue; 2293    Class oColClass, ClassString = null, ClassDate = null, 2294                     ClassBigDecimal = null, ClassDouble = null, ClassFloat = null; 2295    SimpleDateFormat oXMLDate; 2296    DecimalFormat oDecFmt = null; 2297 2298    if (sDateFormat==null) 2299      oXMLDate = new SimpleDateFormat ("yyyy-MM-dd'T'hh:mm:ss"); 2300    else if (sDateFormat.length()==0) 2301      oXMLDate = new SimpleDateFormat ("yyyy-MM-dd'T'hh:mm:ss"); 2302    else 2303      oXMLDate = new SimpleDateFormat (sDateFormat); 2304 2305    if (null!=sDecimalFormat) { 2306      if (sDecimalFormat.length()>0) 2307        oDecFmt = new DecimalFormat (sDecimalFormat); 2308    } // fi 2309 2310    if (DebugFile.trace) { 2311      DebugFile.writeln("Begin DBSubset.toXML(" + sNode + ")"); 2312      DebugFile.incIdent(); 2313    } 2314 2315    try { 2316      ClassString = Class.forName("java.lang.String"); 2317      ClassDate = Class.forName("java.util.Date"); 2318      ClassBigDecimal = Class.forName("java.math.BigDecimal"); 2319      ClassDouble = Class.forName("java.lang.Double"); 2320      ClassFloat = Class.forName("java.lang.Float"); 2321    } catch (ClassNotFoundException ignore) { } 2322 2323    if (oResults!=null) { 2324 2325      sNodeName = (null!=sNode ? sNode : sTable); 2326 2327      iRowCount = oResults.size(); 2328      strBuff = new StringBuffer (256*iRowCount); 2329 2330      strTok = new StringTokenizer (sColList,","); 2331      iTokCount = strTok.countTokens(); 2332      String [] Labels = new String [iTokCount]; 2333 2334      for (int iTok=0; iTok<iTokCount; iTok++) { 2335        sLabel = strTok.nextToken(); 2336        iAs = sLabel.toUpperCase().indexOf(" AS "); 2337        if (-1!=iAs) sLabel = sLabel.substring(iAs+4); 2338        iDot = sLabel.indexOf('.'); 2339        if (-1!=iDot) sLabel = sLabel.substring(++iDot); 2340        Labels[iTok] = sLabel.trim(); 2341      } // next 2342 2343      for (int iRow=0; iRow<iRowCount; iRow++) 2344        { 2345        vRow = (Vector ) oResults.get(iRow); 2346        iCol = 0; 2347        strBuff.append(sIdent + "<" + sNodeName + ">\n"); 2348        while (iCol<iColCount) 2349          { 2350          strBuff.append(sIdent + " <" + Labels[iCol] + ">"); 2351          oColValue = vRow.get(iCol); 2352          if (null!=oColValue) { 2353            oColClass = oColValue.getClass(); 2354 2355            if (oColClass.equals(ClassString) && !Labels[iCol].startsWith("gu_")) 2356              strBuff.append("<![CDATA[" + oColValue + "]]>"); 2357 2358            else if (oColClass.equals(ClassDate)) 2359              strBuff.append (oXMLDate.format((java.util.Date ) oColValue)); 2360 2361            else if (oColClass.equals(ClassBigDecimal) && (oDecFmt!=null)) 2362              strBuff.append (oDecFmt.format((java.math.BigDecimal ) oColValue)); 2363 2364            else if (oColClass.equals(ClassDouble) && (oDecFmt!=null)) 2365              strBuff.append (oDecFmt.format(((java.lang.Double ) oColValue).doubleValue())); 2366 2367            else if (oColClass.equals(ClassFloat) && (oDecFmt!=null)) 2368              strBuff.append (oDecFmt.format((double)((java.lang.Float ) oColValue).floatValue())); 2369 2370            else 2371              strBuff.append(oColValue); 2372          } 2373          strBuff.append("</" + Labels[iCol] + ">\n"); 2374          iCol++; 2375          } 2376        strBuff.append(sIdent + "</" + sNodeName + ">\n"); 2377        } // wend 2378 } 2379    else 2380      strBuff = new StringBuffer (); 2381 2382    if (DebugFile.trace) { 2383      DebugFile.writeln("End DBSubset.toXML() : " + String.valueOf(strBuff.length())); 2384      DebugFile.decIdent(); 2385    } 2386 2387    return strBuff.toString(); 2388 2389  } // toXML() 2390 2391  /** 2392   * <p>Write DBSubset to an XML string</p> 2393   * Use default output format for date values: yyyy-MM-dd'T'hh:mm:ss 2394   * @param sIdent Initial space identations on the left for fields 2395   * @param sNode Name of top parent node. If <b>null</b> then main table name 2396   * for this DBSubset is used. 2397   * @return XML string dump of the whole DBSubset pre-loaded data. 2398   */ 2399 2400  public String toXML(String sIdent, String sNode) { 2401    return toXML(sIdent, sNode, null, null); 2402  } 2403 2404  // ---------------------------------------------------------- 2405 2406  /** 2407   * <p>Print DBSubset to an output stream<p> 2408   * This method is quite different in behavior from toString() and toXML(). 2409   * In toString() and toXML() methods data is first pre-loaded by invoking 2410   * call() or load() methods and then written to a string buffer. 2411   * For toString() and toXML() memory consumption depends on how many rows 2412   * are pre-loaded in memory. 2413   * print() method directly writes readed data to the output stream without creating 2414   * the bidimimensional internal array for holding readed data. 2415   * This way data is directly piped from database to output stream. 2416   * @param oConn Database Connection 2417   * @param oOutStrm Output Stream 2418   * @throws SQLException 2419   */ 2420 2421  public void print(Connection oConn, OutputStream oOutStrm) throws SQLException { 2422    String sCol; 2423    int iRows; 2424    int iCol; 2425    short jCol; 2426    float fCol; 2427    double dCol; 2428    Date dtCol; 2429    BigDecimal bdCol; 2430    Object oCol; 2431    boolean bQualify = sTxtQualifier.length()>0; 2432 2433    if (DebugFile.trace) { 2434      DebugFile.writeln("Begin DBSubset.print([Connection], [Object])"); 2435      DebugFile.incIdent(); 2436      } 2437 2438    Statement oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 2439 2440    if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(" + sSelect + ")"); 2441 2442    ResultSet oRSet = oStmt.executeQuery(sSelect); 2443 2444    if (DebugFile.trace) DebugFile.writeln("ResultSet.getMetaData()"); 2445 2446    ResultSetMetaData oMDat = oRSet.getMetaData(); 2447    int iCols = oMDat.getColumnCount(); 2448 2449    if (DebugFile.trace) DebugFile.writeln("column count = " + String.valueOf(iCols)); 2450 2451    PrintWriter oWriter = new PrintWriter (oOutStrm); 2452 2453    iRows = 0; 2454    while (oRSet.next()) { 2455      for (int c=1; c<=iCols; c++) { 2456        switch (oMDat.getColumnType(c)) { 2457          case Types.VARCHAR: 2458          case Types.CHAR: 2459            sCol = oRSet.getString(c); 2460            if (!oRSet.wasNull()) { 2461              sCol = sCol.replace('\n',' '); 2462              if (bQualify) 2463                oWriter.print(sTxtQualifier + sCol + sTxtQualifier); 2464              else 2465                oWriter.print(sCol); 2466            } 2467            break; 2468          case Types.DATE: 2469            dtCol = oRSet.getDate(c); 2470            if (!oRSet.wasNull()) oWriter.write(dtCol.toString()); 2471            break; 2472          case Types.INTEGER: 2473            iCol = oRSet.getInt(c); 2474            if (!oRSet.wasNull()) oWriter.print(iCol); 2475            break; 2476          case Types.SMALLINT: 2477            jCol = oRSet.getShort(c); 2478            if (!oRSet.wasNull()) oWriter.print(jCol); 2479            break; 2480          case Types.FLOAT: 2481            fCol = oRSet.getFloat(c); 2482            if (!oRSet.wasNull()) oWriter.print(fCol); 2483            break; 2484          case Types.REAL: 2485            dCol = oRSet.getDouble(c); 2486            if (!oRSet.wasNull()) oWriter.print(dCol); 2487            break; 2488          case Types.DECIMAL: 2489            bdCol = oRSet.getBigDecimal(c); 2490            if (!oRSet.wasNull()) oWriter.print(bdCol.toString()); 2491            break; 2492          default: 2493            oCol = oRSet.getObject(c); 2494            if (!oRSet.wasNull()) oWriter.print(oCol.toString()); 2495            break; 2496        } // end switch() 2497 if (c<iCols) oWriter.print(getColumnDelimiter()); 2498      } // next (c) 2499 oWriter.print(getRowDelimiter()); 2500      iRows++; 2501    } // wend() 2502 2503    oWriter.flush(); 2504    oWriter.close(); 2505    oWriter = null; 2506 2507    oRSet.close(); 2508    oRSet = null; 2509    oStmt.close(); 2510    oStmt = null; 2511 2512    if (DebugFile.trace) { 2513      DebugFile.decIdent(); 2514      DebugFile.writeln("End DBSubset.print() : " + String.valueOf(iRows)); 2515    } 2516 2517  } // print() 2518 2519  // ---------------------------------------------------------- 2520 2521  private static String removeQuotes (String sStr) { 2522    final int iLen = sStr.length(); 2523    StringBuffer oStr = new StringBuffer (iLen); 2524    char c; 2525 2526    for (int i=0; i<iLen; i++) { 2527      c = sStr.charAt(i); 2528      if (c!='"' && c!=' ' && c!='\n' && c!='\t' && c!='\r') 2529        oStr.append(c); 2530    } // next (c) 2531 2532    return oStr.toString(); 2533  } // removeQuotes 2534 2535  // ---------------------------------------------------------- 2536 2537  /** 2538   * <p>Store full contents of this DBSubset at base table</p> 2539   * <p>This method takes all the dat contained in memory for this DBSubsets and 2540   * stores it at the database. For each row, if it does not exist then it is 2541   * inserted, if it exists then it is updated. 2542   * @param oConn JDBC Database Connection 2543   * @param oDBPersistSubclass DBPersist subclass for rows. DBSubset will call the 2544   * proper DBPersist.store() derived method for each row, executing specific code 2545   * for the subclass such as automatic GUID at modification date generation. 2546   * @param bStopOnError <b>true</b> if process should stop if any SQLException is 2547   * thrown, <b>false</b> if process must continue upon an SQLException and leave 2548   * return addional information throught SQLException[] array. 2549   * @return An array with a SQLException object per stored row, if no SQLException 2550   * was trown for a row then the entry at the array for that row is <b>null</b>.<br> 2551   * eof() property is set to <b>true</b> if all rows were inserted successfully, 2552   * and, thus, all entries of the returned SQLException array are null; if any row 2553   * failed to be inserted or updated then eof() is set to <b>false</b> 2554   * @throws SQLException Only if bStopOnError is <b>true</b> 2555   * @trhows ArrayIndexOutOfBoundsException If a table column is not found by its name 2556   * @throws IllegalAccessException 2557   * @throws InstantiationException 2558   */ 2559  public SQLException [] store (JDCConnection oConn, Class oDBPersistSubclass, boolean bStopOnError) 2560    throws SQLException , IllegalAccessException , InstantiationException , ArrayIndexOutOfBoundsException { 2561 2562    DBPersist oDBP; 2563    DBTable oTbl; 2564    Object oFld; 2565    Statement oStmt; 2566    ResultSet oRSet; 2567    ResultSetMetaData oMDat; 2568    SQLException [] aExceptions; 2569    int iExceptions = 0; 2570    int iType = Types.NULL; 2571 2572    if (DebugFile.trace) { 2573      if (null==oDBPersistSubclass) 2574        DebugFile.writeln("Begin DBSubset.store([Connection],null"); 2575      else 2576        DebugFile.writeln("Begin DBSubset.store([Connection],[" + oDBPersistSubclass.getName() + "]"); 2577      DebugFile.incIdent(); 2578    } 2579 2580    String [] aCols = Gadgets.split(removeQuotes(sColList), ','); 2581 2582    iColCount = aCols.length ; 2583 2584    if (oDBPersistSubclass!=null) { 2585      oDBP = (DBPersist) oDBPersistSubclass.newInstance(); 2586      oTbl = oDBP.getTable(); 2587 2588      sColList = ""; 2589      for (int c=0; c<iColCount; c++) 2590        if (null!=oTbl.getColumnByName(aCols[c])) 2591          sColList += (c==0 ? "" : "," ) + aCols[c]; 2592        else 2593          sColList += (c==0 ? "" : "," ) + "'void' AS " + aCols[c]; 2594    } 2595 2596    final int iRowCount = getRowCount(); 2597 2598    if (bStopOnError) 2599      aExceptions = null; 2600    else 2601      aExceptions = new SQLException [iRowCount]; 2602 2603    oStmt = oConn.createStatement(); 2604 2605    if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT " + sColList + " FROM " + sTable + " WHERE 1=0)"); 2606 2607    oRSet = oStmt.executeQuery("SELECT " + sColList + " FROM " + sTable + " WHERE 1=0"); 2608    oMDat = oRSet.getMetaData(); 2609 2610    int[] aTypes = new int[oMDat.getColumnCount()]; 2611 2612    ColNames = new String [oMDat.getColumnCount()]; 2613 2614    for (int t=1; t<=iColCount; t++) { 2615      ColNames[t-1] = oMDat.getColumnName(t).toLowerCase(); 2616      aTypes [t-1] = oMDat.getColumnType(t); 2617    } 2618 2619    oMDat = null; 2620    oRSet.close(); 2621    oStmt.close(); 2622 2623    if (oDBPersistSubclass!=null) 2624      oDBP = (DBPersist) oDBPersistSubclass.newInstance(); 2625    else 2626      oDBP = new DBPersist(sTable, sTable); 2627 2628    for (int r=0; r<iRowCount; r++) { 2629 2630      if (DebugFile.trace) DebugFile.writeln("processing row " + String.valueOf(r)); 2631 2632      for (int c=0; c<iColCount; c++) { 2633 2634        oFld = get(c, r); 2635 2636        if (null!=oFld) { 2637          iType = aTypes[c]; 2638          if (iType==Types.BLOB) iType = Types.LONGVARBINARY; 2639          if (iType==Types.CLOB) iType = Types.LONGVARCHAR; 2640          try { 2641            if (oFld.toString().length()>0 && !oDBP.AllVals.containsKey(aCols[c])) { 2642              if (oFld.getClass().getName().equals("java.util.Date")) 2643                oDBP.put(aCols[c], (java.util.Date ) oFld); 2644              else 2645                oDBP.put(aCols[c], oFld.toString(), iType); 2646            } 2647          } catch (FileNotFoundException e) { /* never thrown */ } 2648        } // fi (null!=oFld) 2649 } // next (c) 2650 2651      if (bStopOnError) { 2652 2653        oDBP.store(oConn); 2654      } 2655      else { 2656 2657        try { 2658 2659          oDBP.store(oConn); 2660          aExceptions[r] = null; 2661 2662        } catch (SQLException sqle) { 2663          iExceptions++; 2664          aExceptions[r] = sqle; 2665        } 2666      } // fi (bStopOnError) 2667 2668      oDBP.clear(); 2669    } // next (r) 2670 2671    ColNames = null; 2672 2673    aTypes = null; 2674 2675    bEOF = (0==iExceptions); 2676 2677    if (DebugFile.trace) { 2678      DebugFile.decIdent(); 2679      DebugFile.writeln("End DBSubset.store() : " + String.valueOf(iExceptions)); 2680    } 2681 2682    return aExceptions; 2683  } // store 2684 2685  // ---------------------------------------------------------- 2686 2687  private boolean swapRows(int iRow1, int iRow2) 2688    throws ArrayIndexOutOfBoundsException { 2689    Object oRow1 = oResults.get(iRow1); 2690    Object oRow2 = oResults.get(iRow2); 2691    oResults.setElementAt(oRow2, iRow1); 2692    oResults.setElementAt(oRow1, iRow2); 2693    return true; 2694  } 2695 2696  /** 2697   * <p>Sort in memory an already loaded ResultSet by a given column</p> 2698   * A modified bubble sort algorithm is used. Resulting in a O(n&sup2;) worst case 2699   * and O(n) best case if the ResultSet was already sorted by the given column. 2700   * @param iCol int Column Index [0..getColumnCount()-1] 2701   * @throws ArrayIndexOutOfBoundsException 2702   * @throws ClassCastException 2703   * @since 3.0 2704   */ 2705  public void sortBy(int iCol) 2706    throws ArrayIndexOutOfBoundsException , ClassCastException { 2707 2708    if (DebugFile.trace) { 2709      DebugFile.writeln("Begin DBSubset.sortBy("+String.valueOf(iCol)+")"); 2710      DebugFile.incIdent(); 2711    } 2712 2713    final int iRows = getRowCount(); 2714    final int iRows1 = iRows-1; 2715    boolean bSwapFlag = true; 2716 2717    for (int q=0; q<iRows && bSwapFlag; q++) { 2718      bSwapFlag = false; 2719      for (int r=0; r<iRows1; r++) { 2720        if (!isNull(iCol,r) || !isNull(iCol,r+1)) { 2721          if (!isNull(iCol,r) && isNull(iCol,r+1)) 2722            bSwapFlag = swapRows(r,r+1); 2723          else if (((Comparable ) get(iCol, r)).compareTo(get(iCol, r+1))>0) 2724            bSwapFlag = swapRows(r,r+1); 2725        } // fi 2726 } // next (r) 2727 } // next (q) 2728 2729    if (DebugFile.trace) { 2730      DebugFile.decIdent(); 2731      DebugFile.writeln("End DBSubset.sortBy("+String.valueOf(iCol)+")"); 2732    } 2733  } // sortBy 2734 2735  // ---------------------------------------------------------- 2736 2737  private BigDecimal sumDecimal(int iCol) 2738    throws NumberFormatException , ArrayIndexOutOfBoundsException { 2739    BigDecimal oRetVal = new BigDecimal (0); 2740    final int iRows = getRowCount(); 2741 2742    for (int r=0; r<iRows; r++) 2743      if (!isNull(iCol, r)) 2744        oRetVal.add(getDecimal(iCol, r)); 2745 2746    return oRetVal; 2747  } 2748 2749  private Integer sumInteger(int iCol) { 2750    int iRetVal = 0; 2751    final int iRows = getRowCount(); 2752 2753    for (int r=0; r<iRows; r++) 2754      if (!isNull(iCol, r)) 2755        iRetVal += getInt(iCol, r); 2756 2757    return new Integer (iRetVal); 2758  } 2759 2760  private Short sumShort(int iCol) { 2761    short iRetVal = 0; 2762    final int iRows = getRowCount(); 2763 2764    for (int r=0; r<iRows; r++) 2765      if (!isNull(iCol, r)) 2766        iRetVal += getShort(iCol, r); 2767 2768    return new Short (iRetVal); 2769  } 2770 2771  private Float sumFloat(int iCol) { 2772    float fRetVal = 0; 2773    final int iRows = getRowCount(); 2774 2775    for (int r=0; r<iRows; r++) 2776      if (!isNull(iCol, r)) 2777        fRetVal += getFloat(iCol, r); 2778 2779    return new Float (fRetVal); 2780  } 2781 2782  private Double sumDouble(int iCol) { 2783    double dRetVal = 0; 2784    final int iRows = getRowCount(); 2785 2786    for (int r=0; r<iRows; r++) 2787      if (!isNull(iCol, r)) 2788        dRetVal += getDouble(iCol, r); 2789 2790    return new Double (dRetVal); 2791  } 2792 2793  public Object sum(int iCol) 2794    throws NumberFormatException , ArrayIndexOutOfBoundsException { 2795    final int iRows = getRowCount(); 2796 2797    if (0==iRows) return null; 2798 2799    Object oFirst = null; 2800    int r = 0; 2801    do 2802      oFirst = get(iCol, 0); 2803    while ((null==oFirst) && (r<iRows)); 2804 2805    if (null==oFirst) return new BigDecimal (0); 2806 2807    if (oFirst.getClass().getName().equals("java.math.BigDecimal")) 2808      return sumDecimal(iCol); 2809    else if (oFirst.getClass().getName().equals("java.lang.Integer")) 2810      return sumInteger(iCol); 2811    else if (oFirst.getClass().getName().equals("java.lang.Short")) 2812      return sumShort(iCol); 2813    else if (oFirst.getClass().getName().equals("java.lang.Float")) 2814      return sumFloat(iCol); 2815    else if (oFirst.getClass().getName().equals("java.lang.Double")) 2816      return sumDouble(iCol); 2817    else 2818      throw new NumberFormatException ("Column " + String.valueOf(iCol) + " is not a suitable type for sum()"); 2819  } 2820 2821  // ---------------------------------------------------------- 2822 2823  /** 2824   * <p>Append rows from given DBSubset to this DBSubset</p> 2825   * @param oDbs DBSubset An already loaded DBSubset 2826   * @throws ArrayIndexOutOfBoundsException If both DBSubsets do not have the same number of columns 2827   * @throws NullPointerException If oDbs is <b>null</b> 2828   * @since 3.0 2829   */ 2830  public void union(DBSubset oDbs) 2831    throws ArrayIndexOutOfBoundsException ,NullPointerException { 2832    if (this.getColumnCount()!=oDbs.getColumnCount()) { 2833      throw new ArrayIndexOutOfBoundsException ("DBSubset.union() subsets to be unified must have the same number of columns"); 2834    } 2835    final int iDbsRows = oDbs.getRowCount(); 2836    if (iDbsRows>0) { 2837      oResults.ensureCapacity(getRowCount()+iDbsRows); 2838      for (int r=0; r<iDbsRows; r++) { 2839        oResults.add(oDbs.oResults.get(r)); 2840      } // next 2841 } // fi 2842 } // union 2843 2844  // ---------------------------------------------------------- 2845 2846  /** 2847   * <p>Parse a delimited text file into DBSubset bi-dimensional array</p> 2848   * The parsed file must have the same column structure as the column list set when the DBSubset constructor was called. 2849   * @param sFilePath File Path 2850   * @param sCharSet Character set encoding for file 2851   * @throws IOException 2852   * @throws FileNotFoundException 2853   * @throws ArrayIndexOutOfBoundsException Delimited values for a file is greater 2854   * than columns specified at descriptor. 2855   * @throws RuntimeException If delimiter is not one of { ',' ';' or '\t' } 2856   * @throws NullPointerException if sFileDescriptor is <b>null</b> 2857   * @throws IllegalArgumentException if sFileDescriptor is "" 2858   */ 2859 2860  public void parseCSV (String sFilePath, String sCharSet) 2861    throws ArrayIndexOutOfBoundsException ,IOException ,FileNotFoundException , 2862           RuntimeException ,NullPointerException ,IllegalArgumentException { 2863 2864    if (DebugFile.trace) { 2865      DebugFile.writeln("Begin DBSubset.parseCSV(" + sFilePath + ")"); 2866      DebugFile.incIdent(); 2867    } 2868 2869    Vector oRow; 2870 2871    String [] aCols = Gadgets.split (removeQuotes(sColList), ','); 2872 2873    iColCount = aCols.length; 2874 2875    CSVParser oParser = new CSVParser (sCharSet); 2876 2877    oParser.parseFile (sFilePath, sColList.replace(',',sColDelim.charAt(0))); 2878 2879    final int iRowCount = oParser.getLineCount(); 2880 2881    oResults = new Vector (iRowCount, 1); 2882 2883    for (int r=0; r<iRowCount; r++) { 2884      oRow = new Vector (iColCount); 2885 2886      for (int c=0; c<iColCount; c++) 2887        oRow.add (oParser.getField(c,r)); 2888 2889      oResults.add (oRow); 2890    } // next 2891 2892    if (DebugFile.trace) { 2893      DebugFile.decIdent(); 2894      DebugFile.writeln("End DBSubset.parseCSV()"); 2895    } 2896  } // parseCSV() 2897 2898  // ---------------------------------------------------------- 2899 2900  /** 2901   * <p>Parse a delimited text file into DBSubset bi-dimensional array</p> 2902   * The parsed file must have the same column structure as the column list set when the DBSubset constructor was called. 2903   * @param sFilePath File Path 2904   */ 2905 2906  public void parseCSV (String sFilePath) 2907    throws ArrayIndexOutOfBoundsException ,IOException ,FileNotFoundException , 2908           RuntimeException ,NullPointerException ,IllegalArgumentException { 2909 2910    parseCSV (sFilePath, null); 2911 2912  } // parseCSV() 2913 2914  // ---------------------------------------------------------- 2915 2916  /** 2917   * <p>Parse character data into DBSubset bi-dimensional array</p> 2918   * The parsed file must have the same column structure as the column list set when the DBSubset constructor was called. 2919   * @param sFilePath Character Data to be parsed 2920   * @param sCharSet Character set encoding for file 2921   */ 2922 2923  public void parseCSV (char[] aData, String sCharSet) 2924    throws ArrayIndexOutOfBoundsException , RuntimeException , NullPointerException , 2925           IllegalArgumentException , UnsupportedEncodingException { 2926 2927    if (DebugFile.trace) { 2928      DebugFile.writeln("Begin DBSubset.parseCSV(char[], " + sCharSet + ")"); 2929      DebugFile.incIdent(); 2930    } 2931 2932    Vector oRow; 2933 2934    String [] aCols = Gadgets.split (removeQuotes(sColList), ','); 2935 2936    CSVParser oParser = new CSVParser (sCharSet); 2937 2938    oParser.parseData (aData, sColList.replace(',',sColDelim.charAt(0))); 2939 2940    final int iRowCount = oParser.getLineCount(); 2941    iColCount = aCols.length; 2942 2943    oResults = new Vector (iRowCount, 1); 2944 2945    for (int r=0; r<iRowCount; r++) { 2946      oRow = new Vector (iColCount); 2947 2948      for (int c=0; c<iColCount; c++) 2949        oRow.add (oParser.getField(c,r)); 2950 2951      oResults.add (oRow); 2952    } // next 2953 2954    if (DebugFile.trace) { 2955      DebugFile.decIdent(); 2956      DebugFile.writeln("End DBSubset.parseCSV()"); 2957    } 2958  } // parseCSV() 2959 2960  // ---------------------------------------------------------- 2961 2962  /** 2963   * <p>Parse character data into DBSubset bi-dimensional array</p> 2964   * The parsed file must have the same column structure as the column list set when the DBSubset constructor was called. 2965   * @param sFilePath Character Data to be parsed 2966   */ 2967 2968  public void parseCSV (char[] aData) 2969    throws ArrayIndexOutOfBoundsException , RuntimeException , NullPointerException , 2970           IllegalArgumentException , UnsupportedEncodingException { 2971 2972    parseCSV(aData, null); 2973  } 2974 2975  // ********************************************************** 2976 // Private Variables 2977 2978  private static Class getClassForName(String sClassName) { 2979    Class oRetVal; 2980    try { 2981      oRetVal = Class.forName(sClassName); 2982    } 2983    catch (ClassNotFoundException cnfe) { oRetVal = null; } 2984 2985    return oRetVal; 2986  } 2987 2988  // ---------------------------------------------------------- 2989 2990  private static Class ClassLangString = getClassForName("java.lang.String"); 2991  private static Class ClassUtilDate = getClassForName("java.util.Date"); 2992  private static Class ClassSQLDate = getClassForName("java.sql.Date"); 2993  private static Class ClassSQLTime = getClassForName("java.sql.Time"); 2994  private static Class ClassTimestamp = getClassForName("java.sql.Timestamp"); 2995 2996  private int iFetch; 2997  private int iTimeOut; 2998  private int iColCount; 2999  private int iMaxRows; 3000  private boolean bEOF; 3001  private String sTable; 3002  private String sColList; 3003  private String sFilter; 3004  private String sSelect; 3005  private String sInsert; 3006  private String sColDelim; 3007  private String sRowDelim; 3008  private String sTxtQualifier; 3009  private Vector oResults; 3010  private String ColNames[]; 3011  private SimpleDateFormat oShortDate; 3012  private SimpleDateFormat oDateTime; 3013  private DecimalFormat oDecFmt; 3014 3015} // DBSubset 3016

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Popular Tags