| Classes in this File | Line Coverage | Branch Coverage | Complexity | ||||
| DBUtility |
|
| 8.333333333333334;8.333 |
| 1 | package org.apache.ojb.broker.ant; | |
| 2 | ||
| 3 | /* Copyright 2002-2005 The Apache Software Foundation | |
| 4 | * | |
| 5 | * Licensed under the Apache License, Version 2.0 (the "License"); | |
| 6 | * you may not use this file except in compliance with the License. | |
| 7 | * You may obtain a copy of the License at | |
| 8 | * | |
| 9 | * http://www.apache.org/licenses/LICENSE-2.0 | |
| 10 | * | |
| 11 | * Unless required by applicable law or agreed to in writing, software | |
| 12 | * distributed under the License is distributed on an "AS IS" BASIS, | |
| 13 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. | |
| 14 | * See the License for the specific language governing permissions and | |
| 15 | * limitations under the License. | |
| 16 | */ | |
| 17 | ||
| 18 | import java.sql.*; | |
| 19 | import java.util.Hashtable; | |
| 20 | ||
| 21 | /** | |
| 22 | * DBUtility is a utility class for verifying that various Database objects | |
| 23 | * exist in a specified database. This utility does not use the jdbc | |
| 24 | * DatabaseMetaData object because not all jdbc drivers fully implement | |
| 25 | * it (like org.hsqldb.jdbcDriver - suckers). | |
| 26 | * | |
| 27 | * @author <a href="mailto:daren@softwarearena.com">Daren Drummond</a> | |
| 28 | * @version $Id: DBUtility.java,v 1.1 2007-08-24 22:17:41 ewestfal Exp $ | |
| 29 | */ | |
| 30 | public class DBUtility | |
| 31 | { | |
| 32 | private Connection m_connection = null; | |
| 33 | ||
| 34 | private String m_url = null; | |
| 35 | private String m_user = null; | |
| 36 | private String m_pwd = null; | |
| 37 | private static String m_ORA_EXCEPTION_1000 = "ORA-01000"; | |
| 38 | private static String m_ORA_EXCEPTION_604 = "ORA-00604"; | |
| 39 | ||
| 40 | /** | |
| 41 | * DBUtility connects to the database in this constructor. | |
| 42 | * | |
| 43 | * @param url String representing the jdbc connection url. For example, "jdbc:hsqldb:target/test/OJB". | |
| 44 | * @param user The database user account to use for logging on. | |
| 45 | * @param pwd The password for the user | |
| 46 | * | |
| 47 | * @throws SQLException Throws SQLException if there are problems connecting to the database. | |
| 48 | * @throws ClassNotFoundException Throws ClassNotFoundException if the jdbc driver class can not be found. | |
| 49 | */ | |
| 50 | public DBUtility(String url, String user, String pwd) | |
| 51 | throws SQLException | |
| 52 | { | |
| 53 | m_url = url; | |
| 54 | m_user = user; | |
| 55 | m_pwd = pwd; | |
| 56 | m_connection = connect(url, user, pwd); | |
| 57 | } | |
| 58 | ||
| 59 | public void release() throws SQLException | |
| 60 | { | |
| 61 | if(m_connection != null) | |
| 62 | { | |
| 63 | m_connection.close(); | |
| 64 | } | |
| 65 | } | |
| 66 | ||
| 67 | private void resetConnection() | |
| 68 | { | |
| 69 | try | |
| 70 | { | |
| 71 | release(); | |
| 72 | connect(m_url, m_user, m_pwd); | |
| 73 | } | |
| 74 | catch(Exception e) | |
| 75 | { | |
| 76 | System.out.println("Could not reconnect to database!!!! " + e.getMessage()); | |
| 77 | } | |
| 78 | } | |
| 79 | ||
| 80 | private Connection connect(String url, String user, String pwd) throws SQLException | |
| 81 | { | |
| 82 | m_connection = DriverManager.getConnection(url, user, pwd); | |
| 83 | return m_connection; | |
| 84 | } | |
| 85 | ||
| 86 | ||
| 87 | /** | |
| 88 | * Checks the database for the existence of this table. Returns true if it | |
| 89 | * exists, false if it doesn't exist, and throws a SQLException if the | |
| 90 | * connection is not established. NOTE: If a schema is required for your | |
| 91 | * database, then it should have been provided in the connection url. | |
| 92 | * | |
| 93 | * @param tableName String name of the table that you want check for existence. | |
| 94 | * @return boolean true if the table exists, false if it doesn't exist. | |
| 95 | */ | |
| 96 | public boolean exists(String tableName) | |
| 97 | { | |
| 98 | boolean bReturn = false; | |
| 99 | ||
| 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 | } |