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 }