DBUtility


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: $
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 }
503
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags