1 /* 2 * Copyright (C) 2004-2008 Jive Software. All rights reserved. 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package org.jivesoftware.database; 18 19 import java.io.File; 20 import java.io.Reader; 21 import java.io.StringReader; 22 import java.io.StringWriter; 23 import java.sql.Connection; 24 import java.sql.DatabaseMetaData; 25 import java.sql.PreparedStatement; 26 import java.sql.ResultSet; 27 import java.sql.SQLException; 28 import java.sql.Statement; 29 import java.util.Arrays; 30 import java.util.HashSet; 31 import java.util.Map; 32 import java.util.MissingResourceException; 33 34 import org.jivesoftware.util.ClassUtils; 35 import org.jivesoftware.util.JiveGlobals; 36 import org.slf4j.Logger; 37 import org.slf4j.LoggerFactory; 38 39 /** 40 * Central manager of database connections. All methods are static so that they 41 * can be easily accessed throughout the classes in the database package.<p> 42 * 43 * This class also provides a set of utility methods that abstract out 44 * operations that may not work on all databases such as setting the max number 45 * or rows that a query should return. 46 * 47 * @author Jive Software 48 * @see ConnectionProvider 49 */ 50 public class DbConnectionManager { 51 52 private static final Logger Log = LoggerFactory.getLogger(DbConnectionManager.class); 53 54 private static ConnectionProvider connectionProvider; 55 private static final Object providerLock = new Object(); 56 57 // True if connection profiling is turned on. Always false by default. 58 private static boolean profilingEnabled = false; 59 60 // True if the database support transactions. 61 private static boolean transactionsSupported; 62 // True if the database requires large text fields to be streamed. 63 private static boolean streamTextRequired; 64 /** True if the database supports the Statement.setMaxRows() method. */ 65 private static boolean maxRowsSupported; 66 /** True if the database supports the rs.setFetchSize() method. */ 67 private static boolean fetchSizeSupported; 68 // True if the database supports correlated subqueries. 69 private static boolean subqueriesSupported; 70 // True if the database supports scroll-insensitive results. 71 private static boolean scrollResultsSupported; 72 // True if the database supports batch updates. 73 private static boolean batchUpdatesSupported; 74 /** True if the database supports the Statement.setFetchSize()) method. */ 75 private static boolean pstmt_fetchSizeSupported = true; 76 /** The char used to quote identifiers */ 77 private static String identifierQuoteString; 78 79 private static final String SETTING_DATABASE_MAX_RETRIES = "database.maxRetries"; 80 private static final String SETTING_DATABASE_RETRY_DELAY = "database.retryDelay"; 81 82 private static DatabaseType databaseType = DatabaseType.unknown; 83 84 private static SchemaManager schemaManager = new SchemaManager(); 85 86 /** 87 * Ensures that the connection provider exists and is set 88 */ ensureConnectionProvider()89 private static void ensureConnectionProvider() { 90 if (connectionProvider != null) return; 91 92 synchronized (providerLock) { 93 if (connectionProvider != null) return; 94 95 // Attempt to load the connection provider classname as a Jive property. 96 String className = JiveGlobals.getXMLProperty("connectionProvider.className"); 97 if (className != null) { 98 // Attempt to load the class. 99 try { 100 Class conClass = ClassUtils.forName(className); 101 setConnectionProvider((ConnectionProvider)conClass.newInstance()); 102 } catch (Exception e) { 103 Log.warn("Failed to create the " + 104 "connection provider specified by connection" + 105 "Provider.className. Using the default pool.", e); 106 setConnectionProvider(new DefaultConnectionProvider()); 107 } 108 } else { 109 setConnectionProvider(new DefaultConnectionProvider()); 110 } 111 } 112 } 113 114 /** 115 * Attempts to create a connection to the database and execute a query. 116 * 117 * @param errors A map populated with errors if they occur. 118 * @return true if the test was successful, otherwise false. 119 */ testConnection( Map<String,String> errors )120 public static boolean testConnection( Map<String,String> errors ) { 121 boolean success = true; 122 try ( final Connection con = DbConnectionManager.getConnection() ) 123 { 124 // See if the Jive db schema is installed. 125 try 126 { 127 Statement stmt = con.createStatement(); 128 // Pick an arbitrary table to see if it's there. 129 stmt.executeQuery( "SELECT * FROM ofID" ); 130 stmt.close(); 131 } 132 catch ( SQLException sqle ) 133 { 134 success = false; 135 Log.error( "The Openfire database schema does not appear to be installed.", sqle ); 136 errors.put( "general", "The Openfire database schema does not " 137 + "appear to be installed. Follow the installation guide to " 138 + "fix this error." ); 139 } 140 } 141 catch ( SQLException exception ) 142 { 143 success = false; 144 Log.error( "Unable to connect to the database.", exception ); 145 errors.put( "general", "A connection to the database could not be " 146 + "made. View the error message by opening the " 147 + "\"" + File.separator + "logs" + File.separator + "error.log\" log " 148 + "file, then go back to fix the problem." ); 149 } 150 return success; 151 } 152 153 /** 154 * Returns a database connection from the currently active connection 155 * provider. An exception will be thrown if no connection was found. 156 * (auto commit is set to true). 157 * 158 * @return a connection. 159 * @throws SQLException if a SQL exception occurs or no connection was found. 160 */ getConnection()161 public static Connection getConnection() throws SQLException { 162 ensureConnectionProvider(); 163 164 Integer currentRetryCount = 0; 165 Integer maxRetries = JiveGlobals.getXMLProperty(SETTING_DATABASE_MAX_RETRIES, 10); 166 Integer retryWait = JiveGlobals.getXMLProperty(SETTING_DATABASE_RETRY_DELAY, 250); // milliseconds 167 SQLException lastException = null; 168 boolean loopIfNoConnection = false; 169 do { 170 try { 171 Connection con = connectionProvider.getConnection(); 172 if (con != null) { 173 // Got one, lets hand it off. 174 // Usually profiling is not enabled. So we return a normal 175 // connection unless profiling is enabled. If yes, wrap the 176 // connection with a profiled connection. 177 if (!profilingEnabled) { 178 return con; 179 } else { 180 return new ProfiledConnection(con); 181 } 182 } 183 } catch (SQLException e) { 184 // TODO distinguish recoverable from non-recoverable exceptions. 185 lastException = e; 186 Log.info("Unable to get a connection from the database pool " + 187 "(attempt " + currentRetryCount + " out of " + maxRetries + ").", e); 188 } 189 190 currentRetryCount++; 191 loopIfNoConnection = currentRetryCount <= maxRetries; 192 if (loopIfNoConnection) { 193 try { 194 Thread.sleep(retryWait); 195 } catch (InterruptedException ex) { 196 String msg = "Interrupted waiting for DB connection"; 197 Log.info(msg,ex); 198 Thread.currentThread().interrupt(); 199 throw new SQLException(msg,ex); 200 } 201 } 202 } while (loopIfNoConnection); 203 204 throw new SQLException("ConnectionManager.getConnection() " + 205 "failed to obtain a connection after " + currentRetryCount + " retries. " + 206 "The exception from the last attempt is as follows: " + lastException); 207 } 208 209 /** 210 * Returns a Connection from the currently active connection provider that 211 * is ready to participate in transactions (auto commit is set to false). 212 * 213 * @return a connection with transactions enabled. 214 * @throws SQLException if a SQL exception occurs. 215 */ getTransactionConnection()216 public static Connection getTransactionConnection() throws SQLException { 217 Connection con = getConnection(); 218 if (isTransactionsSupported()) { 219 con.setAutoCommit(false); 220 } 221 return con; 222 } 223 224 /** 225 * Closes a PreparedStatement and Connection. However, it first rolls back the transaction or 226 * commits it depending on the value of <code>abortTransaction</code>. 227 * 228 * @param pstmt the prepared statement to close. 229 * @param con the connection to close. 230 * @param abortTransaction true if the transaction should be rolled back. 231 */ closeTransactionConnection(PreparedStatement pstmt, Connection con, boolean abortTransaction)232 public static void closeTransactionConnection(PreparedStatement pstmt, Connection con, 233 boolean abortTransaction) 234 { 235 closeStatement(pstmt); 236 closeTransactionConnection(con, abortTransaction); 237 } 238 239 /** 240 * Closes a Connection. However, it first rolls back the transaction or 241 * commits it depending on the value of <code>abortTransaction</code>. 242 * 243 * @param con the connection to close. 244 * @param abortTransaction true if the transaction should be rolled back. 245 */ closeTransactionConnection(Connection con, boolean abortTransaction)246 public static void closeTransactionConnection(Connection con, boolean abortTransaction) { 247 // Rollback or commit the transaction 248 if (isTransactionsSupported()) { 249 try { 250 if (abortTransaction) { 251 con.rollback(); 252 } 253 else { 254 con.commit(); 255 } 256 } 257 catch (Exception e) { 258 Log.error(e.getMessage(), e); 259 } 260 // Reset the connection to auto-commit mode. 261 try { 262 con.setAutoCommit(true); 263 } 264 catch (Exception e) { 265 Log.error(e.getMessage(), e); 266 } 267 } 268 closeConnection(con); 269 } 270 271 /** 272 * Closes a result set. This method should be called within the finally section of 273 * your database logic, as in the following example: 274 * 275 * <pre> 276 * public void doSomething(Connection con) { 277 * ResultSet rs = null; 278 * PreparedStatement pstmt = null; 279 * try { 280 * pstmt = con.prepareStatement("select * from blah"); 281 * rs = pstmt.executeQuery(); 282 * .... 283 * } 284 * catch (SQLException sqle) { 285 * Log.error(sqle.getMessage(), sqle); 286 * } 287 * finally { 288 * ConnectionManager.closeResultSet(rs); 289 * ConnectionManager.closePreparedStatement(pstmt); 290 * } 291 * } </pre> 292 * 293 * @param rs the result set to close. 294 */ closeResultSet(ResultSet rs)295 public static void closeResultSet(ResultSet rs) { 296 if (rs != null) { 297 try { 298 rs.close(); 299 } 300 catch (SQLException e) { 301 Log.error(e.getMessage(), e); 302 } 303 } 304 } 305 306 /** 307 * Closes a statement. This method should be called within the finally section of 308 * your database logic, as in the following example: 309 * 310 * <pre> 311 * public void doSomething(Connection con) { 312 * PreparedStatement pstmt = null; 313 * try { 314 * pstmt = con.prepareStatement("select * from blah"); 315 * .... 316 * } 317 * catch (SQLException sqle) { 318 * Log.error(sqle.getMessage(), sqle); 319 * } 320 * finally { 321 * ConnectionManager.closeStatement(pstmt); 322 * } 323 * } </pre> 324 * 325 * @param stmt the statement. 326 */ closeStatement(Statement stmt)327 public static void closeStatement(Statement stmt) { 328 if (stmt != null) { 329 try { 330 stmt.close(); 331 } 332 catch (Exception e) { 333 Log.error(e.getMessage(), e); 334 } 335 } 336 } 337 338 /** 339 * Closes a statement and a result set. This method should be called within the finally section of 340 * your database logic, as in the following example: 341 * 342 * <pre> 343 * public void doSomething(Connection con) { 344 * PreparedStatement pstmt = null; 345 * ResultSet rs = null; 346 * try { 347 * pstmt = con.prepareStatement("select * from blah"); 348 * rs = ... 349 * .... 350 * } 351 * catch (SQLException sqle) { 352 * Log.error(sqle.getMessage(), sqle); 353 * } 354 * finally { 355 * ConnectionManager.closeStatement(rs, pstmt); 356 * } 357 * } </pre> 358 * 359 * @param rs the result set to close 360 * @param stmt the statement. 361 */ closeStatement(ResultSet rs, Statement stmt)362 public static void closeStatement(ResultSet rs, Statement stmt) { 363 closeResultSet(rs); 364 closeStatement(stmt); 365 } 366 /** 367 * Closes a statement. This method should be called within the try section of 368 * your database logic when you reuse a statement. It may throws an exception, 369 * so don't place it in the finally section.<br> 370 * Example: 371 * 372 * <pre> 373 * public void doSomething(Connection con) { 374 * PreparedStatement pstmt = null; 375 * try { 376 * pstmt = con.prepareStatement("select * from dual"); 377 * pstmt.executeUpdate(); 378 * ... 379 * <b>ConnectionManager.fastcloseStmt(pstmt);</b> 380 * pstmt = con.prepareStatement("select * from blah"); 381 * ... 382 * } 383 * ... 384 * } </pre> 385 * 386 * @param pstmt the statement to close. 387 * @throws SQLException if an exception occurs closing the statement 388 */ fastcloseStmt(PreparedStatement pstmt)389 public static void fastcloseStmt(PreparedStatement pstmt) throws SQLException 390 { 391 pstmt.close(); 392 } 393 394 /** 395 * Closes a statement and a result set. This method should be called within the try section of 396 * your database logic when you reuse a statement. It may throw an exception, 397 * so don't place it in the finally section.<br> 398 * Example: 399 * 400 * <pre> 401 * public void doSomething(Connection con) { 402 * PreparedStatement pstmt = null; 403 * try { 404 * pstmt = con.prepareStatement("select * from blah"); 405 * rs = pstmt.executeQuery(); 406 * ... 407 * ConnectionManager.fastcloseStmt(rs, pstmt); 408 * pstmt = con.prepareStatement("select * from blah"); 409 * ... 410 * } 411 * ... 412 * } </pre> 413 * 414 * @param rs The result set to close 415 * @param pstmt the statement to close. 416 * @throws SQLException if an exception occurs closing the result set or statement 417 */ fastcloseStmt(ResultSet rs, PreparedStatement pstmt)418 public static void fastcloseStmt(ResultSet rs, PreparedStatement pstmt) throws SQLException 419 { 420 rs.close(); 421 pstmt.close(); 422 } 423 424 /** 425 * Closes a result set, statement and database connection (returning the connection to 426 * the connection pool). This method should be called within the finally section of 427 * your database logic, as in the following example: 428 * 429 * <pre> 430 * Connection con = null; 431 * PrepatedStatment pstmt = null; 432 * ResultSet rs = null; 433 * try { 434 * con = ConnectionManager.getConnection(); 435 * pstmt = con.prepareStatement("select * from blah"); 436 * rs = psmt.executeQuery(); 437 * .... 438 * } 439 * catch (SQLException sqle) { 440 * Log.error(sqle.getMessage(), sqle); 441 * } 442 * finally { 443 * ConnectionManager.closeConnection(rs, pstmt, con); 444 * }</pre> 445 * 446 * @param rs the result set. 447 * @param stmt the statement. 448 * @param con the connection. 449 */ closeConnection(ResultSet rs, Statement stmt, Connection con)450 public static void closeConnection(ResultSet rs, Statement stmt, Connection con) { 451 closeResultSet(rs); 452 closeStatement(stmt); 453 closeConnection(con); 454 } 455 456 /** 457 * Closes a statement and database connection (returning the connection to 458 * the connection pool). This method should be called within the finally section of 459 * your database logic, as in the following example: 460 * <pre> 461 * Connection con = null; 462 * PrepatedStatment pstmt = null; 463 * try { 464 * con = ConnectionManager.getConnection(); 465 * pstmt = con.prepareStatement("select * from blah"); 466 * .... 467 * } 468 * catch (SQLException sqle) { 469 * Log.error(sqle.getMessage(), sqle); 470 * } 471 * finally { 472 * DbConnectionManager.closeConnection(pstmt, con); 473 * }</pre> 474 * 475 * @param stmt the statement. 476 * @param con the connection. 477 */ closeConnection(Statement stmt, Connection con)478 public static void closeConnection(Statement stmt, Connection con) { 479 closeStatement(stmt); 480 closeConnection(con); 481 } 482 483 /** 484 * Closes a database connection (returning the connection to the connection pool). Any 485 * statements associated with the connection should be closed before calling this method. 486 * This method should be called within the finally section of your database logic, as 487 * in the following example: 488 * <pre> 489 * Connection con = null; 490 * try { 491 * con = ConnectionManager.getConnection(); 492 * .... 493 * } 494 * catch (SQLException sqle) { 495 * Log.error(sqle.getMessage(), sqle); 496 * } 497 * finally { 498 * DbConnectionManager.closeConnection(con); 499 * }</pre> 500 * 501 * @param con the connection. 502 */ closeConnection(Connection con)503 public static void closeConnection(Connection con) { 504 if (con != null) { 505 try { 506 con.close(); 507 } 508 catch (Exception e) { 509 Log.error(e.getMessage(), e); 510 } 511 } 512 } 513 514 /** 515 * Creates a scroll insensitive PreparedStatement if the JDBC driver supports it, or a normal 516 * PreparedStatement otherwise. 517 * 518 * @param con the database connection. 519 * @param sql the SQL to create the PreparedStatement with. 520 * @return a PreparedStatement 521 * @throws java.sql.SQLException if an error occurs. 522 */ createScrollablePreparedStatement(Connection con, String sql)523 public static PreparedStatement createScrollablePreparedStatement(Connection con, String sql) 524 throws SQLException { 525 if (isScrollResultsSupported()) { 526 return con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, 527 ResultSet.CONCUR_READ_ONLY); 528 } 529 else { 530 return con.prepareStatement(sql); 531 } 532 } 533 534 /** 535 * Scrolls forward in a result set the specified number of rows. If the JDBC driver 536 * supports the feature, the cursor will be moved directly. Otherwise, we scroll 537 * through results one by one manually by calling {@code rs.next()}. 538 * 539 * @param rs the ResultSet object to scroll. 540 * @param rowNumber the row number to scroll forward to. 541 * @throws SQLException if an error occurs. 542 */ scrollResultSet(ResultSet rs, int rowNumber)543 public static void scrollResultSet(ResultSet rs, int rowNumber) throws SQLException { 544 // If the driver supports scrollable result sets, use that feature. 545 if (isScrollResultsSupported()) { 546 if (rowNumber > 0) { 547 // We will attempt to do a relative fetch. This may fail in SQL Server if 548 // <resultset-navigation-strategy> is set to absolute. It would need to be 549 // set to looping to work correctly. 550 // If so, manually scroll to the correct row. 551 try { 552 rs.setFetchDirection(ResultSet.FETCH_FORWARD); 553 rs.relative(rowNumber); 554 } 555 catch (SQLException e) { 556 // TODO change "Error ..." to "Disabling ..." 557 Log.error("Error in JDBC method rs.relative(rowNumber).", e); 558 //Log.error("Disabling JDBC method rs.relative(rowNumber).", e); 559 //scrollResultsSupported = false; 560 for (int i = 0; i < rowNumber; i++) { 561 rs.next(); 562 } 563 } 564 } 565 } 566 // Otherwise, manually scroll to the correct row. 567 else { 568 for (int i = 0; i < rowNumber; i++) { 569 rs.next(); 570 } 571 } 572 } 573 574 /** 575 * Limits the number of the results in a result set (to startIndex + numResults). 576 * Sets the fetch size depending on the features of the JDBC driver and make 577 * sure that the size is not bigger than 500. 578 * @param pstmt the PreparedStatement 579 * @param startIndex the first row with interesting data 580 * @param numResults the number of interesting results 581 */ limitRowsAndFetchSize(PreparedStatement pstmt, int startIndex, int numResults)582 public static void limitRowsAndFetchSize(PreparedStatement pstmt, int startIndex, int numResults) { 583 final int MAX_FETCHRESULTS = 500; 584 final int maxRows = startIndex + numResults; 585 setMaxRows(pstmt, maxRows); 586 if (pstmt_fetchSizeSupported) 587 { 588 if (scrollResultsSupported) { 589 setFetchSize(pstmt, Math.min(MAX_FETCHRESULTS, numResults)); 590 } 591 else { 592 setFetchSize(pstmt, Math.min(MAX_FETCHRESULTS, maxRows)); 593 } 594 } 595 } 596 597 /** 598 * Sets the number of rows that the JDBC driver should buffer at a time. 599 * The operation is automatically bypassed if Openfire knows that the 600 * the JDBC driver or database doesn't support it. 601 * 602 * @param pstmt the PreparedStatement to set the fetch size for. 603 * @param fetchSize the fetchSize. 604 */ setFetchSize(PreparedStatement pstmt, int fetchSize)605 public static void setFetchSize(PreparedStatement pstmt, int fetchSize) { 606 if (pstmt_fetchSizeSupported) { 607 try { 608 pstmt.setFetchSize(fetchSize); 609 } 610 catch (Throwable t) { 611 // Ignore. Exception may happen if the driver doesn't support 612 // this operation and we didn't set meta-data correctly. 613 // However, it is a good idea to update the meta-data so that 614 // we don't have to incur the cost of catching an exception 615 // each time. 616 Log.error("Disabling JDBC method pstmt.setFetchSize(fetchSize).", t); 617 pstmt_fetchSizeSupported = false; 618 } 619 } 620 } 621 622 /** 623 * Returns the current connection provider. The only case in which this 624 * method should be called is if more information about the current 625 * connection provider is needed. Database connections should always be 626 * obtained by calling the getConnection method of this class. 627 * 628 * @return the connection provider. 629 */ getConnectionProvider()630 public static ConnectionProvider getConnectionProvider() { 631 return connectionProvider; 632 } 633 634 /** 635 * Sets the connection provider. The old provider (if it exists) is shut 636 * down before the new one is started. A connection provider <b>should 637 * not</b> be started before being passed to the connection manager 638 * because the manager will call the start() method automatically. 639 * 640 * @param provider the ConnectionProvider that the manager should obtain 641 * connections from. 642 */ setConnectionProvider(ConnectionProvider provider)643 public static void setConnectionProvider(ConnectionProvider provider) { 644 synchronized (providerLock) { 645 if (connectionProvider != null) { 646 connectionProvider.destroy(); 647 connectionProvider = null; 648 } 649 connectionProvider = provider; 650 connectionProvider.start(); 651 // Now, get a connection to determine meta data. 652 Connection con = null; 653 try { 654 con = connectionProvider.getConnection(); 655 setMetaData(con); 656 657 // Check to see if the database schema needs to be upgraded. 658 schemaManager.checkOpenfireSchema(con); 659 } 660 catch (MissingResourceException mre) { 661 Log.error(mre.getMessage()); 662 } 663 catch (Exception e) { 664 Log.error(e.getMessage(), e); 665 } 666 finally { 667 closeConnection(con); 668 } 669 } 670 // Remember what connection provider we want to use for restarts. 671 JiveGlobals.setXMLProperty("connectionProvider.className", provider.getClass().getName()); 672 } 673 674 /** 675 * Destroys the currennt connection provider. Future calls to 676 * {@link #getConnectionProvider()} will return {@code null} until a new 677 * ConnectionProvider is set, or one is automatically loaded by a call to 678 * {@link #getConnection()}. 679 */ destroyConnectionProvider()680 public static void destroyConnectionProvider() { 681 synchronized (providerLock) { 682 if (connectionProvider != null) { 683 connectionProvider.destroy(); 684 connectionProvider = null; 685 } 686 } 687 } 688 689 /** 690 * Retrives a large text column from a result set, automatically performing 691 * streaming if the JDBC driver requires it. This is necessary because 692 * different JDBC drivers have different capabilities and methods for 693 * retrieving large text values. 694 * 695 * @param rs the ResultSet to retrieve the text field from. 696 * @param columnIndex the column in the ResultSet of the text field. 697 * @return the String value of the text field. 698 * @throws SQLException if an SQL exception occurs. 699 */ getLargeTextField(ResultSet rs, int columnIndex)700 public static String getLargeTextField(ResultSet rs, int columnIndex) throws SQLException { 701 if (isStreamTextRequired()) { 702 String value; 703 try (Reader bodyReader = rs.getCharacterStream(columnIndex)) { 704 if (bodyReader == null) { 705 return null; 706 } 707 char[] buf = new char[256]; 708 int len; 709 StringWriter out = new StringWriter(256); 710 while ((len = bodyReader.read(buf)) >= 0) { 711 out.write(buf, 0, len); 712 } 713 value = out.toString(); 714 out.close(); 715 } 716 catch (Exception e) { 717 Log.error(e.getMessage(), e); 718 throw new SQLException("Failed to load text field"); 719 } 720 return value; 721 } 722 else { 723 return rs.getString(columnIndex); 724 } 725 } 726 727 /** 728 * Sets a large text column in a result set, automatically performing 729 * streaming if the JDBC driver requires it. This is necessary because 730 * different JDBC drivers have different capabilities and methods for 731 * setting large text values. 732 * 733 * @param pstmt the PreparedStatement to set the text field in. 734 * @param parameterIndex the index corresponding to the text field. 735 * @param value the String to set. 736 * @throws SQLException if an SQL exception occurs. 737 */ setLargeTextField(PreparedStatement pstmt, int parameterIndex, String value)738 public static void setLargeTextField(PreparedStatement pstmt, int parameterIndex, 739 String value) throws SQLException { 740 if (isStreamTextRequired()) { 741 Reader bodyReader; 742 try { 743 bodyReader = new StringReader(value); 744 pstmt.setCharacterStream(parameterIndex, bodyReader, value.length()); 745 } 746 catch (Exception e) { 747 Log.error(e.getMessage(), e); 748 throw new SQLException("Failed to set text field."); 749 } 750 // Leave bodyReader open so that the db can read from it. It *should* 751 // be garbage collected after it's done without needing to call close. 752 } 753 else { 754 pstmt.setString(parameterIndex, value); 755 } 756 } 757 758 /** 759 * Sets the max number of rows that should be returned from executing a 760 * statement. The operation is automatically bypassed if Jive knows that the 761 * the JDBC driver or database doesn't support it. 762 * 763 * @param stmt the Statement to set the max number of rows for. 764 * @param maxRows the max number of rows to return. 765 */ setMaxRows(Statement stmt, int maxRows)766 public static void setMaxRows(Statement stmt, int maxRows) { 767 if (isMaxRowsSupported()) { 768 try { 769 stmt.setMaxRows(maxRows); 770 } 771 catch (Throwable t) { 772 // Ignore. Exception may happen if the driver doesn't support 773 // this operation and we didn't set meta-data correctly. 774 // However, it is a good idea to update the meta-data so that 775 // we don't have to incur the cost of catching an exception 776 // each time. 777 Log.error("Disabling JDBC method stmt.setMaxRows(maxRows).", t); 778 maxRowsSupported = false; 779 } 780 } 781 } 782 783 /** 784 * Sets the number of rows that the JDBC driver should buffer at a time. 785 * The operation is automatically bypassed if Jive knows that the 786 * the JDBC driver or database doesn't support it. 787 * 788 * @param rs the ResultSet to set the fetch size for. 789 * @param fetchSize the fetchSize. 790 */ setFetchSize(ResultSet rs, int fetchSize)791 public static void setFetchSize(ResultSet rs, int fetchSize) { 792 if (isFetchSizeSupported()) { 793 try { 794 rs.setFetchSize(fetchSize); 795 } 796 catch (Throwable t) { 797 // Ignore. Exception may happen if the driver doesn't support 798 // this operation and we didn't set meta-data correctly. 799 // However, it is a good idea to update the meta-data so that 800 // we don't have to incur the cost of catching an exception 801 // each time. 802 Log.error("Disabling JDBC method rs.setFetchSize(fetchSize).", t); 803 fetchSizeSupported = false; 804 } 805 } 806 } 807 808 /** 809 * Returns a SchemaManager instance, which can be used to manage the database 810 * schema information for Openfire and plugins. 811 * 812 * @return a SchemaManager instance. 813 */ getSchemaManager()814 public static SchemaManager getSchemaManager() { 815 return schemaManager; 816 } 817 818 /** 819 * Uses a connection from the database to set meta data information about 820 * what different JDBC drivers and databases support. 821 * 822 * @param con the connection. 823 * @throws SQLException if an SQL exception occurs. 824 */ setMetaData(Connection con)825 private static void setMetaData(Connection con) throws SQLException { 826 DatabaseMetaData metaData = con.getMetaData(); 827 // Supports transactions? 828 transactionsSupported = metaData.supportsTransactions(); 829 // Supports subqueries? 830 subqueriesSupported = metaData.supportsCorrelatedSubqueries(); 831 // Supports scroll insensitive result sets? Try/catch block is a 832 // workaround for DB2 JDBC driver, which throws an exception on 833 // the method call. 834 try { 835 scrollResultsSupported = metaData.supportsResultSetType( 836 ResultSet.TYPE_SCROLL_INSENSITIVE); 837 } 838 catch (Exception e) { 839 scrollResultsSupported = false; 840 } 841 // Supports batch updates 842 batchUpdatesSupported = metaData.supportsBatchUpdates(); 843 844 // Set defaults for other meta properties 845 streamTextRequired = false; 846 maxRowsSupported = true; 847 fetchSizeSupported = true; 848 identifierQuoteString = metaData.getIdentifierQuoteString(); 849 850 // Get the database name so that we can perform meta data settings. 851 String dbName = metaData.getDatabaseProductName().toLowerCase(); 852 String driverName = metaData.getDriverName().toLowerCase(); 853 854 // Oracle properties. 855 if (dbName.contains("oracle")) { 856 databaseType = DatabaseType.oracle; 857 streamTextRequired = true; 858 // scrollResultsSupported = false; /* comment and test this, it should be supported since 10g */ 859 // The i-net AUGURO JDBC driver 860 if (driverName.contains("auguro")) { 861 streamTextRequired = false; 862 fetchSizeSupported = true; 863 maxRowsSupported = false; 864 } 865 } 866 // Postgres properties 867 else if (dbName.contains("postgres")) { 868 databaseType = DatabaseType.postgresql; 869 // Postgres blows, so disable scrolling result sets. 870 scrollResultsSupported = false; 871 fetchSizeSupported = false; 872 } 873 // Interbase properties 874 else if (dbName.contains("interbase")) { 875 databaseType = DatabaseType.interbase; 876 fetchSizeSupported = false; 877 maxRowsSupported = false; 878 } 879 // SQLServer 880 else if (dbName.contains("sql server")) { 881 databaseType = DatabaseType.sqlserver; 882 // JDBC driver i-net UNA properties 883 if (driverName.contains("una")) { 884 fetchSizeSupported = true; 885 maxRowsSupported = false; 886 } 887 } 888 // MySQL properties 889 else if (dbName.contains("mysql") || dbName.contains("maria")) { 890 databaseType = DatabaseType.mysql; 891 // transactionsSupported = false; /* comment and test this, it should be supported since 5.0 */ 892 } 893 // HSQL properties 894 else if (dbName.contains("hsql")) { 895 databaseType = DatabaseType.hsqldb; 896 // scrollResultsSupported = false; /* comment and test this, it should be supported since 1.7.2 */ 897 } 898 // DB2 properties. 899 else if (dbName.contains("db2")) { 900 databaseType = DatabaseType.db2; 901 } 902 } 903 904 /** 905 * Returns the database type. The possible types are constants of the 906 * DatabaseType class. Any database that doesn't have its own constant 907 * falls into the "Other" category. 908 * 909 * @return the database type. 910 */ getDatabaseType()911 public static DatabaseType getDatabaseType() { 912 return databaseType; 913 } 914 915 /** 916 * Returns true if connection profiling is turned on. You can collect 917 * profiling statistics by using the static methods of the ProfiledConnection 918 * class. 919 * 920 * @return true if connection profiling is enabled. 921 */ isProfilingEnabled()922 public static boolean isProfilingEnabled() { 923 return profilingEnabled; 924 } 925 926 /** 927 * Turns connection profiling on or off. You can collect profiling 928 * statistics by using the static methods of the ProfiledConnection 929 * class. 930 * 931 * @param enable true to enable profiling; false to disable. 932 */ setProfilingEnabled(boolean enable)933 public static void setProfilingEnabled(boolean enable) { 934 // If enabling profiling, call the start method on ProfiledConnection 935 if (!profilingEnabled && enable) { 936 ProfiledConnection.start(); 937 } 938 // Otherwise, if turning off, call stop method. 939 else if (profilingEnabled && !enable) { 940 ProfiledConnection.stop(); 941 } 942 profilingEnabled = enable; 943 } 944 isTransactionsSupported()945 public static boolean isTransactionsSupported() { 946 return transactionsSupported; 947 } 948 isStreamTextRequired()949 public static boolean isStreamTextRequired() { 950 return streamTextRequired; 951 } 952 isMaxRowsSupported()953 public static boolean isMaxRowsSupported() { 954 return maxRowsSupported; 955 } 956 isFetchSizeSupported()957 public static boolean isFetchSizeSupported() { 958 return fetchSizeSupported; 959 } 960 isPstmtFetchSizeSupported()961 public static boolean isPstmtFetchSizeSupported() { 962 return pstmt_fetchSizeSupported; 963 } 964 isSubqueriesSupported()965 public static boolean isSubqueriesSupported() { 966 return subqueriesSupported; 967 } 968 isScrollResultsSupported()969 public static boolean isScrollResultsSupported() { 970 return scrollResultsSupported; 971 } 972 isBatchUpdatesSupported()973 public static boolean isBatchUpdatesSupported() { 974 return batchUpdatesSupported; 975 } 976 isEmbeddedDB()977 public static boolean isEmbeddedDB() { 978 return connectionProvider != null && connectionProvider instanceof EmbeddedConnectionProvider; 979 } 980 getIdentifierQuoteString()981 public static String getIdentifierQuoteString() { 982 return identifierQuoteString; 983 } 984 getTestSQL(String driver)985 public static String getTestSQL(String driver) { 986 if (driver == null) { 987 return "select 1"; 988 } 989 else if (driver.contains("db2")) { 990 return "select 1 from sysibm.sysdummy1"; 991 } 992 else if (driver.contains("oracle")) { 993 return "select 1 from dual"; 994 } 995 else { 996 return "select 1"; 997 } 998 } 999 1000 /** 1001 * A class that identifies the type of the database that Jive is connected 1002 * to. In most cases, we don't want to make any database specific calls 1003 * and have no need to know the type of database we're using. However, 1004 * there are certain cases where it's critical to know the database for 1005 * performance reasons. 1006 */ 1007 public enum DatabaseType { 1008 1009 oracle, 1010 1011 postgresql, 1012 1013 mysql("rank"), 1014 1015 hsqldb, 1016 1017 db2, 1018 1019 sqlserver, 1020 1021 interbase, 1022 1023 unknown; 1024 1025 private final HashSet<String> identifiers; 1026 DatabaseType(final String ... identifiers)1027 DatabaseType(final String ... identifiers) { 1028 this.identifiers = new HashSet<>(Arrays.asList(identifiers)); 1029 } 1030 escapeIdentifier(final String keyword)1031 public String escapeIdentifier(final String keyword) { 1032 if (identifiers.contains(keyword)) { 1033 return String.format("%1$s%2$s%1$s", DbConnectionManager.getIdentifierQuoteString(), keyword); 1034 } else { 1035 return keyword; 1036 } 1037 } 1038 } 1039 } 1040