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