1 /**************************************************************** 2 * Licensed to the Apache Software Foundation (ASF) under one * 3 * or more contributor license agreements. See the NOTICE file * 4 * distributed with this work for additional information * 5 * regarding copyright ownership. The ASF licenses this file * 6 * to you under the Apache License, Version 2.0 (the * 7 * "License"); you may not use this file except in compliance * 8 * with the License. You may obtain a copy of the License at * 9 * * 10 * http://www.apache.org/licenses/LICENSE-2.0 * 11 * * 12 * Unless required by applicable law or agreed to in writing, * 13 * software distributed under the License is distributed on an * 14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * 15 * KIND, either express or implied. See the License for the * 16 * specific language governing permissions and limitations * 17 * under the License. * 18 ****************************************************************/ 19 20 21 22 package org.apache.james.util.sql; 23 24 import java.sql.Connection; 25 import java.sql.DatabaseMetaData; 26 import java.sql.ResultSet; 27 import java.sql.SQLException; 28 import java.sql.Statement; 29 import java.util.Locale; 30 31 /** 32 * <p>Helper class for managing common JDBC tasks.</p> 33 * 34 * <p>This class is abstract to allow implementations to 35 * take advantage of different logging capabilities/interfaces in 36 * different parts of the code.</p> 37 * 38 * @version CVS $Revision: 684527 $ $Date: 2008-08-10 16:53:29 +0100 (Sun, 10 Aug 2008) $ 39 */ 40 abstract public class JDBCUtil 41 { 42 /** 43 * An abstract method which child classes override to handle logging of 44 * errors in their particular environments. 45 * 46 * @param errorString the error message generated 47 */ 48 abstract protected void delegatedLog(String errorString); 49 50 /** 51 * Checks database metadata to see if a table exists. 52 * Try UPPER, lower, and MixedCase, to see if the table is there. 53 * 54 * @param dbMetaData the database metadata to be used to look up this table 55 * @param tableName the table name 56 * 57 * @throws SQLException if an exception is encountered while accessing the database 58 */ 59 public boolean tableExists(DatabaseMetaData dbMetaData, String tableName) 60 throws SQLException { 61 return ( tableExistsCaseSensitive(dbMetaData, tableName) || 62 tableExistsCaseSensitive(dbMetaData, tableName.toUpperCase(Locale.US)) || 63 tableExistsCaseSensitive(dbMetaData, tableName.toLowerCase(Locale.US)) ); 64 } 65 66 /** 67 * Checks database metadata to see if a table exists. This method 68 * is sensitive to the case of the provided table name. 69 * 70 * @param dbMetaData the database metadata to be used to look up this table 71 * @param tableName the case sensitive table name 72 * 73 * @throws SQLException if an exception is encountered while accessing the database 74 */ 75 public boolean tableExistsCaseSensitive(DatabaseMetaData dbMetaData, String tableName) 76 throws SQLException { 77 ResultSet rsTables = dbMetaData.getTables(null, null, tableName, null); 78 try { 79 boolean found = rsTables.next(); 80 return found; 81 } finally { 82 closeJDBCResultSet(rsTables); 83 } 84 } 85 86 /** 87 * Checks database metadata to see if a column exists in a table 88 * Try UPPER, lower, and MixedCase, both on the table name and the column name, to see if the column is there. 89 * 90 * @param dbMetaData the database metadata to be used to look up this column 91 * @param tableName the table name 92 * @param columnName the column name 93 * 94 * @throws SQLException if an exception is encountered while accessing the database 95 */ 96 public boolean columnExists(DatabaseMetaData dbMetaData, String tableName, String columnName) 97 throws SQLException { 98 return ( columnExistsCaseSensitive(dbMetaData, tableName, columnName) || 99 columnExistsCaseSensitive(dbMetaData, tableName, columnName.toUpperCase(Locale.US)) || 100 columnExistsCaseSensitive(dbMetaData, tableName, columnName.toLowerCase(Locale.US)) || 101 columnExistsCaseSensitive(dbMetaData, tableName.toUpperCase(Locale.US), columnName) || 102 columnExistsCaseSensitive(dbMetaData, tableName.toUpperCase(Locale.US), columnName.toUpperCase(Locale.US)) || 103 columnExistsCaseSensitive(dbMetaData, tableName.toUpperCase(Locale.US), columnName.toLowerCase(Locale.US)) || 104 columnExistsCaseSensitive(dbMetaData, tableName.toLowerCase(Locale.US), columnName) || 105 columnExistsCaseSensitive(dbMetaData, tableName.toLowerCase(Locale.US), columnName.toUpperCase(Locale.US)) || 106 columnExistsCaseSensitive(dbMetaData, tableName.toLowerCase(Locale.US), columnName.toLowerCase(Locale.US)) ); 107 } 108 109 /** 110 * Checks database metadata to see if a column exists in a table. This method 111 * is sensitive to the case of both the provided table name and column name. 112 * 113 * @param dbMetaData the database metadata to be used to look up this column 114 * @param tableName the case sensitive table name 115 * @param columnName the case sensitive column name 116 * 117 * @throws SQLException if an exception is encountered while accessing the database 118 */ 119 public boolean columnExistsCaseSensitive(DatabaseMetaData dbMetaData, String tableName, String columnName) 120 throws SQLException { 121 ResultSet rsTables = dbMetaData.getColumns(null, null, tableName, columnName); 122 try { 123 boolean found = rsTables.next(); 124 return found; 125 } finally { 126 closeJDBCResultSet(rsTables); 127 } 128 } 129 130 /** 131 * Closes database connection and logs if an error 132 * is encountered 133 * 134 * @param conn the connection to be closed 135 */ 136 public void closeJDBCConnection(Connection conn) { 137 try { 138 if (conn != null) { 139 conn.close(); 140 } 141 } catch (SQLException sqle) { 142 // Log exception and continue 143 subclassLogWrapper("Unexpected exception while closing database connection."); 144 } 145 } 146 147 /** 148 * Closes database statement and logs if an error 149 * is encountered 150 * 151 * @param stmt the statement to be closed 152 */ 153 public void closeJDBCStatement(Statement stmt) { 154 try { 155 if (stmt != null) { 156 stmt.close(); 157 } 158 } catch (SQLException sqle) { 159 // Log exception and continue 160 subclassLogWrapper("Unexpected exception while closing database statement."); 161 } 162 } 163 164 /** 165 * Closes database result set and logs if an error 166 * is encountered 167 * 168 * @param aResultSet the result set to be closed 169 */ 170 public void closeJDBCResultSet(ResultSet aResultSet ) { 171 try { 172 if (aResultSet != null) { 173 aResultSet.close(); 174 } 175 } catch (SQLException sqle) { 176 // Log exception and continue 177 subclassLogWrapper("Unexpected exception while closing database result set."); 178 } 179 } 180 181 /** 182 * Wraps the delegated call to the subclass logging method with a Throwable 183 * wrapper. All throwables generated by the subclass logging method are 184 * caught and ignored. 185 * 186 * @param logString the raw string to be passed to the logging method implemented 187 * by the subclass 188 */ 189 private void subclassLogWrapper(String logString) 190 { 191 try { 192 delegatedLog(logString); 193 } 194 catch(Throwable t) { 195 // Throwables generated by the logging system are ignored 196 } 197 } 198 199 }