001: package org.apache.ojb.broker.ant;
002:
003: /* Copyright 2002-2005 The Apache Software Foundation
004: *
005: * Licensed under the Apache License, Version 2.0 (the "License");
006: * you may not use this file except in compliance with the License.
007: * You may obtain a copy of the License at
008: *
009: * http://www.apache.org/licenses/LICENSE-2.0
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: */
017:
018: import java.sql.*;
019: import java.util.Hashtable;
020:
021: /**
022: * DBUtility is a utility class for verifying that various Database objects
023: * exist in a specified database. This utility does not use the jdbc
024: * DatabaseMetaData object because not all jdbc drivers fully implement
025: * it (like org.hsqldb.jdbcDriver - suckers).
026: *
027: * @author <a href="mailto:daren@softwarearena.com">Daren Drummond</a>
028: * @version $Id: $
029: */
030: public class DBUtility {
031: private Connection m_connection = null;
032:
033: private String m_url = null;
034: private String m_user = null;
035: private String m_pwd = null;
036: private static String m_ORA_EXCEPTION_1000 = "ORA-01000";
037: private static String m_ORA_EXCEPTION_604 = "ORA-00604";
038:
039: /**
040: * DBUtility connects to the database in this constructor.
041: *
042: * @param url String representing the jdbc connection url. For example, "jdbc:hsqldb:target/test/OJB".
043: * @param user The database user account to use for logging on.
044: * @param pwd The password for the user
045: *
046: * @throws SQLException Throws SQLException if there are problems connecting to the database.
047: * @throws ClassNotFoundException Throws ClassNotFoundException if the jdbc driver class can not be found.
048: */
049: public DBUtility(String url, String user, String pwd)
050: throws SQLException {
051: m_url = url;
052: m_user = user;
053: m_pwd = pwd;
054: m_connection = connect(url, user, pwd);
055: }
056:
057: public void release() throws SQLException {
058: if (m_connection != null) {
059: m_connection.close();
060: }
061: }
062:
063: private void resetConnection() {
064: try {
065: release();
066: connect(m_url, m_user, m_pwd);
067: } catch (Exception e) {
068: System.out.println("Could not reconnect to database!!!! "
069: + e.getMessage());
070: }
071: }
072:
073: private Connection connect(String url, String user, String pwd)
074: throws SQLException {
075: m_connection = DriverManager.getConnection(url, user, pwd);
076: return m_connection;
077: }
078:
079: /**
080: * Checks the database for the existence of this table. Returns true if it
081: * exists, false if it doesn't exist, and throws a SQLException if the
082: * connection is not established. NOTE: If a schema is required for your
083: * database, then it should have been provided in the connection url.
084: *
085: * @param tableName String name of the table that you want check for existence.
086: * @return boolean true if the table exists, false if it doesn't exist.
087: */
088: public boolean exists(String tableName) {
089: boolean bReturn = false;
090:
091: if (tableName == null)
092: return bReturn;
093: PreparedStatement checkTable = null;
094: try {
095: //System.out.println("DBUtility: looking up table: " + tableName);
096: //System.out.println("Select * from " + tableName + " where 1=0");
097: checkTable = m_connection.prepareStatement("Select * from "
098: + tableName + " where 1=0");
099: checkTable.executeQuery();
100: bReturn = true;
101: } catch (Exception e) {
102: if (e.getMessage().startsWith(m_ORA_EXCEPTION_1000)
103: || e.getMessage().startsWith(m_ORA_EXCEPTION_604)) {
104: System.out
105: .println("Exceeded available Oracle cursors. Resetting connection and trying the SQL statement again...");
106: resetConnection();
107: return exists(tableName);
108: } else {
109: //System.out.println("DD - " + e.getMessage());
110: bReturn = false;
111: }
112: }
113:
114: return bReturn;
115: }
116:
117: private Hashtable m_columnCache = new Hashtable(79);
118:
119: private ResultSet getColumns(String tableName) {
120: return (ResultSet) m_columnCache.get(tableName);
121: }
122:
123: private void putColumns(String tableName, ResultSet columns) {
124: m_columnCache.put(tableName, columns);
125: }
126:
127: /**
128: * Checks the database for the existence of this table.column of the specified
129: * jdbc type. Returns true if it exists, false if it doesn't exist, and throws
130: * a SQLException if the connection is not established. NOTE: If a schema is
131: * required for your database, then it should have been provided in the
132: * connection url.
133: *
134: * @param tableName String name of the table to check.
135: * @param columnName String name of the table column to check.
136: * @param jdbcType Case insensitive String representation of
137: * the jdbc type of the column. Valid values
138: * are string representations of the types listed
139: * in java.sql.Types. For example, "bit", "float",
140: * "varchar", "clob", etc.
141: * @param ignoreCase boolean flag that determines if the utility should
142: * consider the column name case when searching for
143: * the database table.column.
144: *
145: * @throws SQLException if the Table doesn't exist, if the column doesn't exist, if the column type doesn't match the specified jdbcType.
146: */
147: public void exists(String tableName, String columnName,
148: String jdbcType, boolean ignoreCase) throws SQLException {
149: if (tableName == null)
150: throw new SQLException(
151: "TableName was null. You must specify a valid table name.");
152: if (columnName == null)
153: throw new SQLException(
154: "Column name was null. You must specify a valid column name.");
155:
156: ResultSet columns = getColumns(tableName);
157:
158: if (columns == null) {
159: //columns not in the cache, look them up and cache
160: PreparedStatement checkTable = null;
161: try {
162: //System.out.println("DBUtility: looking up table: " + tableName);
163: //System.out.println("Select * from " + tableName + " where 1=0");
164: checkTable = m_connection
165: .prepareStatement("Select * from " + tableName
166: + " where 1=0");
167: columns = checkTable.executeQuery();
168: putColumns(tableName, columns);
169: } catch (SQLException sqle) {
170: if (sqle.getMessage().startsWith(m_ORA_EXCEPTION_1000)
171: || sqle.getMessage().startsWith(
172: m_ORA_EXCEPTION_604)) {
173: System.out
174: .println("Exceeded available Oracle cursors. Resetting connection and trying the SQL statement again...");
175: resetConnection();
176: exists(tableName, columnName, jdbcType, ignoreCase);
177: } else {
178: //System.out.println(sqle.getMessage());
179: throw sqle;
180: }
181: }
182: }
183:
184: ResultSetMetaData rsMeta = columns.getMetaData();
185: int iColumns = rsMeta.getColumnCount();
186: int jdbcTypeConst = this .getJdbcType(jdbcType);
187: for (int i = 1; i <= iColumns; i++) {
188: if (ignoreCase) {
189: //ignore case while testing
190: if (columnName
191: .equalsIgnoreCase(rsMeta.getColumnName(i))) {
192: //The column exists, does the type match?
193: if (jdbcTypeConst != rsMeta.getColumnType(i)) {
194: throw new SQLException(
195: "The column '"
196: + tableName
197: + "."
198: + columnName
199: + "' is of type '"
200: + rsMeta.getColumnTypeName(i)
201: + "' and cannot be mapped to the jdbc type '"
202: + jdbcType + "'.");
203: } else {
204: return;
205: }
206: }
207: } else {
208: //enforce case-sensitive compare
209: if (columnName.equals(rsMeta.getColumnName(i))) {
210: //The column exists, does the type match?
211: if (jdbcTypeConst != rsMeta.getColumnType(i)) {
212: throw new SQLException(
213: "The column '"
214: + tableName
215: + "."
216: + columnName
217: + "' is of type '"
218: + rsMeta.getColumnTypeName(i)
219: + "' and cannot be mapped to the jdbc type '"
220: + jdbcType + "'.");
221: } else {
222: return;
223: }
224: }
225:
226: }
227:
228: //System.out.println("Found column: " + rsMeta.getColumnName(i));
229: }
230:
231: throw new SQLException("The column '" + columnName
232: + "' was not found in table '" + tableName + "'.");
233:
234: }
235:
236: /**
237: * Checks the database for the existence of this table.column of the specified
238: * jdbc type. Throws a SQLException if if the Table.Column can not be found, and
239: * throws a SQLWarning if the column type does not match the passed JDBC type.
240: * NOTE: If a schema is required for your database, then it should have been
241: * provided in the connection url.
242: *
243: * @param tableName String name of the table to check.
244: * @param columnName String name of the table column to check.
245: * @param jdbcType Case insensitive String representation of
246: * the jdbc type of the column. Valid values
247: * are string representations of the types listed
248: * in java.sql.Types. For example, "bit", "float",
249: * "varchar", "clob", etc.
250: * @param ignoreCase boolean flag that determines if the utility should
251: * consider the column name case when searching for
252: * the database table.column.
253: *
254: * @throws SQLException if the Table doesn't exist, if the column doesn't exist.
255: * @throws SQLWarning if the column type doesn't match the specified jdbcType.
256: */
257: public void existsUseWarnings(String tableName, String columnName,
258: String jdbcType, boolean ignoreCase) throws SQLException,
259: SQLWarning {
260: if (tableName == null)
261: throw new SQLException(
262: "TableName was null. You must specify a valid table name.");
263: if (columnName == null)
264: throw new SQLException(
265: "Column name was null. You must specify a valid column name.");
266:
267: ResultSet columns = getColumns(tableName);
268:
269: if (columns == null) {
270: //columns not in the cache, look them up and cache
271: try {
272: //System.out.println("DBUtility: looking up table: " + tableName);
273: //System.out.println("Select * from " + tableName + " where 1=0");
274: PreparedStatement checkTable = m_connection
275: .prepareStatement("Select * from " + tableName
276: + " where 1=0");
277: columns = checkTable.executeQuery();
278: putColumns(tableName, columns);
279: } catch (SQLException sqle) {
280: if (sqle.getMessage().startsWith(m_ORA_EXCEPTION_1000)
281: || sqle.getMessage().startsWith(
282: m_ORA_EXCEPTION_604)) {
283: System.out
284: .println("Exceeded available Oracle cursors. Resetting connection and trying the SQL statement again...");
285: resetConnection();
286: existsUseWarnings(tableName, columnName, jdbcType,
287: ignoreCase);
288: } else {
289: //System.out.println(sqle.getMessage());
290: throw sqle;
291: }
292: }
293: }
294:
295: ResultSetMetaData rsMeta = columns.getMetaData();
296: int iColumns = rsMeta.getColumnCount();
297: int jdbcTypeConst = this .getJdbcType(jdbcType);
298: for (int i = 1; i <= iColumns; i++) {
299: if (ignoreCase) {
300: //ignore case while testing
301: if (columnName
302: .equalsIgnoreCase(rsMeta.getColumnName(i))) {
303: //The column exists, does the type match?
304: if (jdbcTypeConst != rsMeta.getColumnType(i)) {
305: throw new SQLWarning(
306: "The column '"
307: + tableName
308: + "."
309: + columnName
310: + "' is of type '"
311: + rsMeta.getColumnTypeName(i)
312: + "' and cannot be mapped to the jdbc type '"
313: + jdbcType + "'.");
314: } else {
315: return;
316: }
317: }
318: } else {
319: //enforce case-sensitive compare
320: if (columnName.equals(rsMeta.getColumnName(i))) {
321: //The column exists, does the type match?
322: if (jdbcTypeConst != rsMeta.getColumnType(i)) {
323: throw new SQLWarning(
324: "The column '"
325: + tableName
326: + "."
327: + columnName
328: + "' is of type '"
329: + rsMeta.getColumnTypeName(i)
330: + "' and cannot be mapped to the jdbc type '"
331: + jdbcType + "'.");
332: } else {
333: return;
334: }
335: }
336:
337: }
338:
339: //System.out.println("Found column: " + rsMeta.getColumnName(i));
340: }
341:
342: throw new SQLException("The column '" + columnName
343: + "' was not found in table '" + tableName + "'.");
344:
345: }
346:
347: /**
348: * Checks the database for the existence of this table.column.
349: * Throws a SQLException if if the Table.Column can not be found.
350: * NOTE: If a schema is required for your
351: * database, then it should have been provided in the connection url.
352: *
353: * @param tableName String name of the table to check.
354: * @param columnName String name of the table column to check.
355: * @param ignoreCase boolean flag that determines if the utility should
356: * consider the column name case when searching for
357: * the database table.column.
358: *
359: * @throws SQLException if the Table doesn't exist, if the column doesn't exist.
360: */
361:
362: public void exists(String tableName, String columnName,
363: boolean ignoreCase) throws SQLException {
364: if (tableName == null)
365: throw new SQLException(
366: "TableName was null. You must specify a valid table name.");
367: if (columnName == null)
368: throw new SQLException(
369: "Column name was null. You must specify a valid column name.");
370:
371: ResultSet columns = getColumns(tableName);
372:
373: if (columns == null) {
374: //columns not in the cache, look them up and cache
375: try {
376: //System.out.println("DBUtility: looking up table: " + tableName);
377: //System.out.println("Select * from " + tableName + " where 1=0");
378: PreparedStatement checkTable = m_connection
379: .prepareStatement("Select * from " + tableName
380: + " where 1=0");
381: columns = checkTable.executeQuery();
382: putColumns(tableName, columns);
383: } catch (SQLException sqle) {
384: if (sqle.getMessage().startsWith(m_ORA_EXCEPTION_1000)
385: || sqle.getMessage().startsWith(
386: m_ORA_EXCEPTION_604)) {
387: System.out
388: .println("Exceeded available Oracle cursors. Resetting connection and trying the SQL statement again...");
389: resetConnection();
390: exists(tableName, columnName, ignoreCase);
391: } else {
392: System.out.println(sqle.getMessage());
393: throw sqle;
394: }
395: }
396: }
397:
398: ResultSetMetaData rsMeta = columns.getMetaData();
399: int iColumns = rsMeta.getColumnCount();
400: for (int i = 1; i <= iColumns; i++) {
401: if (ignoreCase) {
402: //ignore case while testing
403: if (columnName
404: .equalsIgnoreCase(rsMeta.getColumnName(i))) {
405: return;
406: }
407: } else {
408: //enforce case-sensitive compare
409: if (columnName.equals(rsMeta.getColumnName(i))) {
410: return;
411: }
412:
413: }
414:
415: //System.out.println("Found column: " + rsMeta.getColumnName(i));
416: }
417:
418: throw new SQLException("The column '" + columnName
419: + "' was not found in table '" + tableName + "'.");
420:
421: }
422:
423: /**
424: * Determines the java.sql.Types constant value from an OJB
425: * FIELDDESCRIPTOR value.
426: *
427: * @param type The FIELDDESCRIPTOR which JDBC type is to be determined.
428: *
429: * @return int the int value representing the Type according to
430: *
431: * @throws SQLException if the type is not a valid jdbc type.
432: * java.sql.Types
433: */
434: public int getJdbcType(String ojbType) throws SQLException {
435: int result;
436: if (ojbType == null)
437: ojbType = "";
438: ojbType = ojbType.toLowerCase();
439: if (ojbType.equals("bit"))
440: result = Types.BIT;
441: else if (ojbType.equals("tinyint"))
442: result = Types.TINYINT;
443: else if (ojbType.equals("smallint"))
444: result = Types.SMALLINT;
445: else if (ojbType.equals("integer"))
446: result = Types.INTEGER;
447: else if (ojbType.equals("bigint"))
448: result = Types.BIGINT;
449:
450: else if (ojbType.equals("float"))
451: result = Types.FLOAT;
452: else if (ojbType.equals("real"))
453: result = Types.REAL;
454: else if (ojbType.equals("double"))
455: result = Types.DOUBLE;
456:
457: else if (ojbType.equals("numeric"))
458: result = Types.NUMERIC;
459: else if (ojbType.equals("decimal"))
460: result = Types.DECIMAL;
461:
462: else if (ojbType.equals("char"))
463: result = Types.CHAR;
464: else if (ojbType.equals("varchar"))
465: result = Types.VARCHAR;
466: else if (ojbType.equals("longvarchar"))
467: result = Types.LONGVARCHAR;
468:
469: else if (ojbType.equals("date"))
470: result = Types.DATE;
471: else if (ojbType.equals("time"))
472: result = Types.TIME;
473: else if (ojbType.equals("timestamp"))
474: result = Types.TIMESTAMP;
475:
476: else if (ojbType.equals("binary"))
477: result = Types.BINARY;
478: else if (ojbType.equals("varbinary"))
479: result = Types.VARBINARY;
480: else if (ojbType.equals("longvarbinary"))
481: result = Types.LONGVARBINARY;
482:
483: else if (ojbType.equals("clob"))
484: result = Types.CLOB;
485: else if (ojbType.equals("blob"))
486: result = Types.BLOB;
487: else
488: throw new SQLException("The type '" + ojbType
489: + "' is not a valid jdbc type.");
490: return result;
491: }
492:
493: protected void finalize() {
494: try {
495: release();
496: } catch (Exception e) {
497: e.printStackTrace();
498: }
499: }
500: }
|