1 21 22 package org.opensubsystems.core.util; 23 24 import java.sql.Connection ; 25 import java.sql.PreparedStatement ; 26 import java.sql.ResultSet ; 27 import java.sql.SQLException ; 28 import java.sql.Statement ; 29 import java.util.ArrayList ; 30 import java.util.Collection ; 31 import java.util.HashMap ; 32 import java.util.HashSet ; 33 import java.util.Iterator ; 34 import java.util.List ; 35 import java.util.Map ; 36 import java.util.Set ; 37 import java.util.logging.Level ; 38 import java.util.logging.Logger ; 39 40 import org.opensubsystems.core.data.DataObject; 41 import org.opensubsystems.core.error.OSSDatabaseAccessException; 42 import org.opensubsystems.core.error.OSSException; 43 import org.opensubsystems.core.persist.db.DatabaseConnectionFactoryImpl; 44 import org.opensubsystems.core.persist.db.DatabaseFactory; 45 import org.opensubsystems.core.persist.db.DatabaseImpl; 46 import org.opensubsystems.core.persist.db.DatabaseTransactionFactoryImpl; 47 48 56 public final class DatabaseUtils 57 { 58 60 63 private static Logger s_logger = Log.getInstance(DatabaseUtils.class); 64 65 68 protected static Map s_mpDependencyCache = new HashMap (); 69 70 72 75 private DatabaseUtils( 76 ) 77 { 78 } 80 81 83 90 public static int executeUpdateAndClose( 91 PreparedStatement jdbcstatement 92 ) throws SQLException 93 { 94 int iUpdateCount = 0; 95 96 try 97 { 98 iUpdateCount = jdbcstatement.executeUpdate(); 99 } 100 finally 101 { 102 closeStatement(jdbcstatement); 103 } 104 105 return iUpdateCount; 106 } 107 108 115 public static void closeResultSet( 116 ResultSet results 117 ) 118 { 119 try 120 { 121 if (results != null) 122 { 123 results.close(); 124 } 125 } 126 catch (SQLException sqleExc) 127 { 128 s_logger.log(Level.WARNING, 130 "Failed to close the database result set", 131 sqleExc); 132 } 133 } 134 135 143 public static void closeResultSetAndStatement( 144 ResultSet results, 145 Statement jdbcstatement 146 ) 147 { 148 try 149 { 150 closeResultSet(results); 151 } 152 finally 153 { 154 closeStatement(jdbcstatement); 155 } 156 } 157 158 165 public static void closeStatement( 166 Statement jdbcstatement 167 ) 168 { 169 if (jdbcstatement != null) 170 { 171 try 172 { 173 jdbcstatement.close(); 174 } 175 catch (SQLException sqleExc) 176 { 177 s_logger.log(Level.WARNING, 178 "Failed to close the database statement", 179 sqleExc); 180 } 181 } 182 } 183 184 199 public static DataObject loadAtMostOneData( 200 DatabaseFactory factory, 201 PreparedStatement pstmQuery, 202 String strErrorMessage 203 ) throws SQLException , 204 OSSException 205 { 206 DataObject data = null; 207 ResultSet rsQueryResults = null; 208 209 try 210 { 211 rsQueryResults = pstmQuery.executeQuery(); 212 while (rsQueryResults.next()) 213 { 214 if (GlobalConstants.ERROR_CHECKING) 215 { 216 assert data == null : strErrorMessage; 217 } 218 219 data = factory.load(rsQueryResults, 1); 220 } 221 } 222 finally 223 { 224 closeResultSet(rsQueryResults); 225 } 226 227 return data; 228 } 229 230 246 public static int loadAtMostOneInt( 247 PreparedStatement pstmQuery, 248 int iDefault, 249 String strErrorMessage 250 ) throws SQLException , 251 OSSException 252 { 253 int iData = iDefault; 254 ResultSet rsQueryResults = null; 255 256 try 257 { 258 rsQueryResults = pstmQuery.executeQuery(); 259 if (rsQueryResults.next()) 260 { 261 iData = rsQueryResults.getInt(1); 262 if (GlobalConstants.ERROR_CHECKING) 263 { 264 assert (!rsQueryResults.next()) : strErrorMessage; 265 } 266 } 267 } 268 finally 269 { 270 closeResultSet(rsQueryResults); 271 } 272 273 return iData; 274 } 275 276 292 public static String loadAtMostOneString( 293 PreparedStatement pstmQuery, 294 String strDefault, 295 String strErrorMessage 296 ) throws SQLException , 297 OSSException 298 { 299 String strData = strDefault; 300 ResultSet rsQueryResults = null; 301 302 try 303 { 304 rsQueryResults = pstmQuery.executeQuery(); 305 if (rsQueryResults.next()) 306 { 307 strData = rsQueryResults.getString(1); 308 if (GlobalConstants.ERROR_CHECKING) 309 { 310 assert (!rsQueryResults.next()) : strErrorMessage; 311 } 312 } 313 } 314 finally 315 { 316 closeResultSet(rsQueryResults); 317 } 318 319 return strData; 320 } 321 322 335 public static List loadMultipleData( 336 DatabaseFactory factory, 337 PreparedStatement pstmQuery 338 ) throws SQLException , 339 OSSException 340 { 341 ResultSet rsQueryResults = null; 342 List lstData = null; 343 344 try 345 { 346 int iCount; 347 348 rsQueryResults = pstmQuery.executeQuery(); 349 iCount = estimateResultSetSize(rsQueryResults); 350 if (iCount != 0) 351 { 352 if (iCount > 0) 353 { 354 lstData = new ArrayList (iCount); 356 rsQueryResults.next(); 359 } 360 else 361 { 362 if (rsQueryResults.next()) 370 { 371 lstData = new ArrayList (); 375 } 376 } 377 if (lstData != null) 378 { 379 do 380 { 381 lstData.add(factory.load(rsQueryResults, 1)); 382 } 383 while (rsQueryResults.next()); 384 } 385 } 386 } 387 finally 388 { 389 closeResultSet(rsQueryResults); 390 } 391 392 return lstData; 393 } 394 395 413 public static Set loadMultipleDataToSet( 414 DatabaseFactory factory, 415 PreparedStatement pstmQuery, 416 boolean bLoadSpecific 417 ) throws SQLException , 418 OSSException 419 { 420 ResultSet rsQueryResults = null; 421 Set returnSet = null; 422 423 try 424 { 425 int iCount; 426 427 rsQueryResults = pstmQuery.executeQuery(); 428 iCount = estimateResultSetSize(rsQueryResults); 429 if (iCount != 0) 430 { 431 if (iCount > 0) 432 { 433 returnSet = new HashSet (iCount); 435 rsQueryResults.next(); 438 } 439 else 440 { 441 if (rsQueryResults.next()) 449 { 450 returnSet = new HashSet (); 454 } 455 } 456 if (returnSet != null) 457 { 458 do 459 { 460 if (!bLoadSpecific) 461 { 462 returnSet.add(factory.load(rsQueryResults, 1)); 465 } 466 else 467 { 468 returnSet.add(rsQueryResults.getString(1)); 470 } 471 } 472 while (rsQueryResults.next()); 473 } 474 } 475 } 476 finally 477 { 478 closeResultSet(rsQueryResults); 479 } 480 481 return returnSet; 482 } 483 484 496 public static List loadMultipleStrings( 497 PreparedStatement pstmQuery 498 ) throws SQLException , 499 OSSException 500 { 501 ResultSet rsQueryResults = null; 502 List lstData = null; 503 504 try 505 { 506 int iCount; 507 508 rsQueryResults = pstmQuery.executeQuery(); 509 iCount = estimateResultSetSize(rsQueryResults); 510 if (iCount != 0) 511 { 512 if (iCount > 0) 513 { 514 lstData = new ArrayList (iCount); 516 rsQueryResults.next(); 519 } 520 else 521 { 522 if (rsQueryResults.next()) 530 { 531 lstData = new ArrayList (); 535 } 536 } 537 if (lstData != null) 538 { 539 do 540 { 541 lstData.add(rsQueryResults.getString(1)); 542 } 543 while (rsQueryResults.next()); 544 } 545 } 546 } 547 finally 548 { 549 closeResultSet(rsQueryResults); 550 } 551 552 return lstData; 553 } 554 555 567 public static int[] loadMultipleIntsAsArray( 568 PreparedStatement pstmQuery 569 ) throws SQLException , 570 OSSException 571 { 572 ResultSet rsQueryResults = null; 573 int[] arrData = null; 574 575 try 576 { 577 int iCount; 578 579 rsQueryResults = pstmQuery.executeQuery(); 580 iCount = estimateResultSetSize(rsQueryResults); 581 if (iCount != 0) 582 { 583 int iActualCount = 0; 584 if (iCount > 0) 585 { 586 arrData = new int[iCount]; 588 while (rsQueryResults.next()) 589 { 590 arrData[iActualCount++] = rsQueryResults.getInt(1); 591 } 592 } 593 else 594 { 595 if (rsQueryResults.next()) 599 { 600 List lstData = new ArrayList (); 601 do 602 { 603 lstData.add(new Integer (rsQueryResults.getInt(1))); 604 } 605 while (rsQueryResults.next()); 606 arrData = new int[lstData.size()]; 607 for (Iterator items = lstData.iterator(); items.hasNext();) 608 { 609 arrData[iActualCount++] = ((Integer )items.next()).intValue(); 610 } 611 } 612 } 613 } 614 } 615 finally 616 { 617 closeResultSet(rsQueryResults); 618 } 619 620 return arrData; 621 } 622 623 635 public static TwoIntStruct[] loadMultipleTwoIntStruct( 636 PreparedStatement pstmQuery 637 ) throws SQLException , 638 OSSException 639 { 640 ResultSet rsQueryResults = null; 641 TwoIntStruct[] arrData = null; 642 643 try 644 { 645 int iCount; 646 647 rsQueryResults = pstmQuery.executeQuery(); 648 iCount = estimateResultSetSize(rsQueryResults); 649 if (iCount != 0) 650 { 651 int iActualCount = 0; 652 int iItem1; 653 int iItem2; 654 if (iCount > 0) 655 { 656 arrData = new TwoIntStruct[iCount]; 657 while (rsQueryResults.next()) 658 { 659 iItem1 = rsQueryResults.getInt(1); 660 iItem2 = rsQueryResults.getInt(2); 661 662 arrData[iActualCount++] = new TwoIntStruct(iItem1, iItem2); 663 } 664 } 665 else 666 { 667 if (rsQueryResults.next()) 671 { 672 List lstData = new ArrayList (); 673 do 674 { 675 lstData.add(new TwoIntStruct(rsQueryResults.getInt(1), 676 rsQueryResults.getInt(2))); 677 } 678 while (rsQueryResults.next()); 679 arrData = new TwoIntStruct[lstData.size()]; 680 for (Iterator items = lstData.iterator(); items.hasNext();) 681 { 682 arrData[iActualCount++] = (TwoIntStruct)items.next(); 683 } 684 } 685 } 686 } 687 } 688 finally 689 { 690 closeResultSet(rsQueryResults); 691 } 692 693 return arrData; 694 } 695 696 717 public static boolean hasRelations( 718 String [][] arTableColumn, 719 int iId 720 ) throws OSSException 721 { 722 boolean bReturn = false; 723 Connection cntConnection = null; 724 PreparedStatement pstmQuery = null; 725 ResultSet rsResult = null; 726 StringBuffer sbQuery = null; 727 boolean bQueryInitialized = false; 728 boolean bExist; 729 730 if ((arTableColumn != null) && (arTableColumn.length > 0)) 731 { 732 try 733 { 734 cntConnection = DatabaseConnectionFactoryImpl.getInstance().requestConnection(true); 736 737 for (int iCount = 0; iCount < arTableColumn.length; iCount++) 738 { 739 if (s_mpDependencyCache.get(arTableColumn[iCount][0] 741 + "|" + arTableColumn[iCount][1]) == null) 742 { 743 try 744 { 745 pstmQuery = cntConnection.prepareStatement( 746 "select " + 747 arTableColumn[iCount][1] + 748 " from " + 749 arTableColumn[iCount][0]); 750 rsResult = pstmQuery.executeQuery(); 751 752 s_mpDependencyCache.put( 753 arTableColumn[iCount][0] + "|" + arTableColumn[iCount][1], 754 "exist"); 755 bExist = true; 756 757 } 758 catch (SQLException sqleExc) 759 { 760 s_mpDependencyCache.put( 761 arTableColumn[iCount][0] + "|" + arTableColumn[iCount][1], 762 "not in DB"); 763 bExist = false; 764 } 765 finally 766 { 767 DatabaseUtils.closeResultSetAndStatement(rsResult, pstmQuery); 768 } 769 } 770 else 771 { 772 bExist = s_mpDependencyCache.get( 773 arTableColumn[iCount][0] + "|" + arTableColumn[iCount][1] 774 ).equals("exist"); 775 } 776 777 if (bExist) 778 { 779 if (bQueryInitialized) 780 { 781 sbQuery.append(" union "); 782 } 783 else 784 { 785 sbQuery = new StringBuffer (); 786 } 787 788 sbQuery.append("select "); 789 sbQuery.append(arTableColumn[iCount][1]); 790 sbQuery.append(" from "); 791 sbQuery.append(arTableColumn[iCount][0]); 792 sbQuery.append(" where "); 793 sbQuery.append(arTableColumn[iCount][1]); 794 sbQuery.append(" = "); 795 sbQuery.append(iId); 796 797 bQueryInitialized = true; 798 } 799 } 800 801 if (sbQuery != null && sbQuery.length() > 0) 802 { 803 try 804 { 805 pstmQuery = cntConnection.prepareStatement(sbQuery.toString()); 806 rsResult = pstmQuery.executeQuery(); 807 808 if (rsResult.next()) 809 { 810 bReturn = true; 811 } 812 } 813 catch (SQLException sqleExc) 814 { 815 throw new OSSDatabaseAccessException( 816 "Cannot get connection to the database.", 817 sqleExc); 818 } 819 finally 820 { 821 DatabaseUtils.closeResultSetAndStatement(rsResult, pstmQuery); 822 } 823 } 824 } 825 finally 826 { 827 DatabaseConnectionFactoryImpl.getInstance().returnConnection(cntConnection); 828 } 829 } 830 831 return bReturn; 832 } 833 834 841 public static void rollbackAndIgnoreException( 842 Connection cntConnection 843 ) 844 { 845 try 846 { 847 DatabaseTransactionFactoryImpl.getInstance().rollbackTransaction(cntConnection); 852 } 853 catch (SQLException sqleExc) 854 { 855 s_logger.log(Level.WARNING, 857 "Failed to rollback current transaction", 858 sqleExc); 859 } 860 catch (OSSException osseExc) 861 { 862 s_logger.log(Level.WARNING, 864 "Failed to rollback current transaction", 865 osseExc); 866 } 867 } 868 869 879 public static int[] mergeColumnsSafely( 880 int[] arrOriginalColumns, 881 int[] arrExtraColumns 882 ) 883 { 884 int[] arrReturn = arrOriginalColumns; 885 List lstAddedColumns = new ArrayList (); 886 int iIndex; 887 int iIndex1; 888 Integer iColumn; 889 890 boolean foundFlag = false; 891 for (iIndex = 0; iIndex < arrExtraColumns.length; iIndex++) 893 { 894 foundFlag = false; 895 for (iIndex1 = 0; iIndex1 < arrOriginalColumns.length; iIndex1++) 896 { 897 if (arrExtraColumns[iIndex] == arrOriginalColumns[iIndex1]) 898 { 899 foundFlag = true; 901 break; 902 } 903 } 904 if (!foundFlag) 905 { 906 lstAddedColumns.add(new Integer (arrExtraColumns[iIndex])); 908 } 909 } 910 911 if (lstAddedColumns.size() > 0) 912 { 913 arrReturn = new int[arrOriginalColumns.length + lstAddedColumns.size()]; 915 System.arraycopy(arrOriginalColumns, 0, arrReturn, 0, arrOriginalColumns.length); 916 917 for (iIndex = 0; iIndex < lstAddedColumns.size(); iIndex++) 919 { 920 iColumn = (Integer ) lstAddedColumns.get(iIndex); 921 arrReturn[arrOriginalColumns.length + iIndex] = iColumn.intValue(); 922 } 923 } 924 925 return arrReturn; 926 } 927 928 1058 1070 public static List getInListWithSafeLength( 1071 Collection idList, 1072 boolean bQuote 1073 ) 1074 { 1075 List lstRetList = new ArrayList (idList.size() / DatabaseImpl.MAX_SAFE_LENGTH + 1); 1076 int count = 0; 1077 Iterator inputIterator = idList.iterator(); 1078 String currData = null; 1079 StringBuffer dataString = new StringBuffer (); 1080 1081 while (inputIterator.hasNext()) 1082 { 1083 currData = ((Object )inputIterator.next()).toString(); 1085 if (count == 0) 1086 { 1087 dataString.delete(0, dataString.length()); 1089 } 1090 else 1091 { 1092 dataString.append(","); 1094 } 1095 1096 if (bQuote) 1097 { 1098 dataString.append("'"); 1100 } 1101 1102 dataString.append(currData); 1104 1105 if (bQuote) 1106 { 1107 dataString.append("'"); 1109 } 1110 1111 count++; 1112 1113 if (count == DatabaseImpl.MAX_SAFE_LENGTH) 1114 { 1115 lstRetList.add(dataString.toString()); 1117 count = 0; 1119 } 1120 } 1121 if (count > 0) 1122 { 1123 lstRetList.add(dataString.toString()); 1125 } 1126 1127 return lstRetList; 1128 } 1129 1130 1132 1142 protected static int estimateResultSetSize( 1143 ResultSet rsQueryResults 1144 ) throws SQLException , 1145 OSSException 1146 { 1147 int iCount = -1; 1150 1151 if ((DatabaseImpl.getInstance().hasAbsolutePositioningSupport()) 1156 && (!DatabaseImpl.getInstance().preferCountToLast())) 1157 { 1158 if (rsQueryResults.last()) 1162 { 1163 iCount = rsQueryResults.getRow(); 1165 rsQueryResults.beforeFirst(); 1166 } 1167 else 1168 { 1169 iCount = 0; 1171 } 1172 } 1173 1174 return iCount; 1175 } 1176} 1177 | Popular Tags |