JDBC


1   /*
2    *
3    * Derby - Class org.apache.derbyTesting.functionTests.util.JDBC
4    *
5    * Licensed to the Apache Software Foundation (ASF) under one or more
6    * contributor license agreements.  See the NOTICE file distributed with
7    * this work for additional information regarding copyright ownership.
8    * The ASF licenses this file to You under the Apache License, Version 2.0
9    * (the "License"); you may not use this file except in compliance with
10   * the License.  You may obtain a copy of the License at
11   *
12   *    http://www.apache.org/licenses/LICENSE-2.0
13   *
14   * Unless required by applicable law or agreed to in writing, 
15   * software distributed under the License is distributed on an 
16   * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, 
17   * either express or implied. See the License for the specific 
18   * language governing permissions and limitations under the License.
19   */
20  package org.apache.derbyTesting.junit;
21  
22  import java.sql.*;
23  
24  import junit.framework.Assert;
25  
26  /**
27   * JDBC utility methods for the JUnit tests.
28   *
29   */
30  public class JDBC {
31      
32      /**
33       * Tell if we are allowed to use DriverManager to create database
34       * connections.
35       */
36      private static final boolean HAVE_DRIVER
37                             = haveClass("java.sql.Driver");
38      
39      /**
40       * Does the Savepoint class exist, indicates
41       * JDBC 3 (or JSR 169). 
42       */
43      private static final boolean HAVE_SAVEPOINT
44                             = haveClass("java.sql.Savepoint");
45  
46      /**
47       * Does the java.sql.SQLXML class exist, indicates JDBC 4. 
48       */
49      private static final boolean HAVE_SQLXML
50                             = haveClass("java.sql.SQLXML");
51      
52      /**
53       * Can we load a specific class, use this to determine JDBC level.
54       * @param className Class to attempt load on.
55       * @return true if class can be loaded, false otherwise.
56       */
57      private static boolean haveClass(String   className)
58      {
59          try {
60              Class.forName(className);
61              return true;
62          } catch (Exception   e) {
63              return false;
64          }       
65      }
66      /**
67       * <p>
68       * Return true if the virtual machine environment
69       * supports JDBC4 or later.
70       * </p>
71       */
72      public static boolean vmSupportsJDBC4()
73      {
74          return HAVE_DRIVER
75             && HAVE_SQLXML;
76      }
77      /**
78       * <p>
79       * Return true if the virtual machine environment
80       * supports JDBC3 or later.
81       * </p>
82       */
83      public static boolean vmSupportsJDBC3()
84      {
85          return HAVE_DRIVER
86                 && HAVE_SAVEPOINT;
87      }
88  
89      /**
90       * <p>
91       * Return true if the virtual machine environment
92       * supports JDBC2 or later.
93       * </p>
94       */
95      public static boolean vmSupportsJDBC2()
96      {
97          return HAVE_DRIVER;
98      }
99      /**
100      * <p>
101      * Return true if the virtual machine environment
102      * supports JSR169 (JDBC 3 subset).
103      * </p>
104      */
105     public static boolean vmSupportsJSR169()
106     {
107         return !HAVE_DRIVER
108                && HAVE_SAVEPOINT;
109     }   
110     
111     /**
112      * Rollback and close a connection for cleanup.
113      * Test code that is expecting Connection.close to succeed
114      * normally should just call conn.close().
115      * 
116      * <P>
117      * If conn is not-null and isClosed() returns false
118      * then both rollback and close will be called.
119      * If both methods throw exceptions
120      * then they will be chained together and thrown.
121      * @throws SQLException Error closing connection.
122      */
123     public static void cleanup(Connection conn) throws SQLException
124     {
125         if (conn == null)
126             return;
127         if (conn.isClosed())
128             return;
129         
130         SQLException sqle = null;
131         try {
132             conn.rollback();
133         } catch (SQLException e) {
134             sqle = e;
135         }
136         
137         try {
138             conn.close();
139         } catch (SQLException e) {
140             if (sqle == null)
141                 sqle = e;
142             else
143                 sqle.setNextException(e);
144             throw sqle;
145         }
146     }
147     
148     /**
149      * Drop a database schema by dropping all objects in it
150      * and then executing DROP SCHEMA. If the schema is
151      * APP it is cleaned but DROP SCHEMA is not executed.
152      * 
153      * TODO: Handle dependencies by looping in some intelligent
154      * way until everything can be dropped.
155      * 
156 
157      * 
158      * @param dmd DatabaseMetaData object for database
159      * @param schema Name of the schema
160      * @throws SQLException database error
161      */
162     public static void dropSchema(DatabaseMetaData dmd, String   schema) throws SQLException
163     {       
164         Connection conn = dmd.getConnection();
165         Assert.assertFalse(conn.getAutoCommit());
166         Statement s = dmd.getConnection().createStatement();
167         
168         // Functions - not supported by JDBC meta data until JDBC 4
169         PreparedStatement psf = conn.prepareStatement(
170                 "SELECT ALIAS FROM SYS.SYSALIASES A, SYS.SYSSCHEMAS S" +
171                 " WHERE A.SCHEMAID = S.SCHEMAID " +
172                 " AND A.ALIASTYPE = 'F' " +
173                 " AND S.SCHEMANAME = ?");
174         psf.setString(1, schema);
175         ResultSet rs = psf.executeQuery();
176         dropUsingDMD(s, rs, schema, "ALIAS", "FUNCTION");        
177         psf.close();
178   
179         // Procedures
180         rs = dmd.getProcedures((String  ) null,
181                 schema, (String  ) null);
182         
183         dropUsingDMD(s, rs, schema, "PROCEDURE_NAME", "PROCEDURE");
184         
185         // Views
186         rs = dmd.getTables((String  ) null, schema, (String  ) null,
187                 new String  [] {"VIEW"});
188         
189         dropUsingDMD(s, rs, schema, "TABLE_NAME", "VIEW");
190         
191         // Tables
192         rs = dmd.getTables((String  ) null, schema, (String  ) null,
193                 new String  [] {"TABLE"});
194         
195         dropUsingDMD(s, rs, schema, "TABLE_NAME", "TABLE");
196 
197         // Synonyms - need work around for DERBY-1790 where
198         // passing a table type of SYNONYM fails.
199         rs = dmd.getTables((String  ) null, schema, (String  ) null,
200                 new String  [] {"AA_DERBY-1790-SYNONYM"});
201         
202         dropUsingDMD(s, rs, schema, "TABLE_NAME", "SYNONYM");
203         
204         // Finally drop the schema if it is not APP
205         if (!schema.equals("APP")) {
206             s.execute("DROP SCHEMA " + JDBC.escape(schema) + " RESTRICT");
207         }
208         conn.commit();
209         s.close();
210     }
211     
212     /**
213      * DROP a set of objects based upon a ResultSet from a
214      * DatabaseMetaData call.
215      * 
216      * TODO: Handle errors to ensure all objects are dropped,
217      * probably requires interaction with its caller.
218      * 
219      * @param s Statement object used to execute the DROP commands.
220      * @param rs DatabaseMetaData ResultSet
221      * @param schema Schema the objects are contained in
222      * @param mdColumn The column name used to extract the object's
223      * name from rs
224      * @param dropType The keyword to use after DROP in the SQL statement
225      * @throws SQLException database errors.
226      */
227     private static void dropUsingDMD(
228             Statement s, ResultSet rs, String   schema,
229             String   mdColumn,
230             String   dropType) throws SQLException
231     {
232         String   dropLeadIn = "DROP " + dropType + " ";
233         
234         s.clearBatch();
235         int batchCount = 0;
236         while (rs.next())
237         {
238             String   objectName = rs.getString(mdColumn);
239             s.addBatch(dropLeadIn + JDBC.escape(schema, objectName));
240             batchCount++;
241         }
242         rs.close();
243         int[] results;
244         try {
245             results = s.executeBatch();
246             Assert.assertNotNull(results);
247             Assert.assertEquals("Incorrect result length from executeBatch",
248                     batchCount, results.length);
249         } catch (BatchUpdateException batchException) {
250             results = batchException.getUpdateCounts();
251             Assert.assertNotNull(results);
252             Assert.assertTrue("Too many results in BatchUpdateException",
253                     results.length <= batchCount);
254         }
255         
256         boolean hadError = false;
257         boolean didDrop = false;
258         for (int i = 0; i < results.length; i++)
259         {
260             int result = results[i];
261             if (result == -3 /* Statement.EXECUTE_FAILED*/)
262                 hadError = true;
263             else if (result == -2/*Statement.SUCCESS_NO_INFO*/)
264                 didDrop = true;
265             else if (result >= 0)
266                 didDrop = true;
267             else
268                 Assert.fail("Negative executeBatch status");
269         }
270         
271         // Commit any work we did do.
272         s.getConnection().commit();
273         s.clearBatch();
274     }
275     
276     /**
277      * Assert all columns in the ResultSetMetaData match the
278      * table's defintion through DatabaseMetadDta. Only works
279      * if the complete select list correspond to columns from
280      * base tables.
281      * <BR>
282      * Does not require that the complete set of any table's columns are
283      * returned.
284      * @throws SQLException 
285      * 
286      */
287     public static void assertMetaDataMatch(DatabaseMetaData dmd,
288             ResultSetMetaData rsmd) throws SQLException
289     {
290         for (int col = 1; col <= rsmd.getColumnCount(); col++)
291         {
292             // Only expect a single column back
293             ResultSet column = dmd.getColumns(
294                     rsmd.getCatalogName(col),
295                     rsmd.getSchemaName(col),
296                     rsmd.getTableName(col),
297                     rsmd.getColumnName(col));
298             
299             Assert.assertTrue("Column missing " + rsmd.getColumnName(col),
300                     column.next());
301             
302             Assert.assertEquals(column.getInt("DATA_TYPE"),
303                     rsmd.getColumnType(col));
304             
305             Assert.assertEquals(column.getInt("NULLABLE"),
306                     rsmd.isNullable(col));
307             
308             Assert.assertEquals(column.getString("TYPE_NAME"),
309                     rsmd.getColumnTypeName(col));
310             
311             column.close();
312         }
313     }
314     
315     /**
316      * Drain a single ResultSet by reading all of its
317      * rows and columns. Each column is accessed using
318      * getString() and asserted that the returned value
319      * matches the state of ResultSet.wasNull().
320      * Provides simple testing of the ResultSet when the contents
321      * are not important.
322      * @param rs
323      * @throws SQLException
324      */
325     public static void assertDrainResults(ResultSet rs)
326         throws SQLException
327     {
328         ResultSetMetaData rsmd = rs.getMetaData();
329         
330         while (rs.next()) {
331             for (int col = 1; col <= rsmd.getColumnCount(); col++)
332             {
333                 String   s = rs.getString(col);
334                 Assert.assertEquals(s == null, rs.wasNull());
335             }
336         }
337         rs.close();
338     }
339     
340     /**
341      * Escape a non-qualified name so that it is suitable
342      * for use in a SQL query executed by JDBC.
343      */
344     public static String   escape(String   name)
345     {
346         return "\"" + name + "\"";
347     }   
348     /**
349      * Escape a schama-qualified name so that it is suitable
350      * for use in a SQL query executed by JDBC.
351      */
352     public static String   escape(String   schema, String   name)
353     {
354         return "\"" + schema + "\".\"" + name + "\"";
355     }
356 }
357
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Free Books Free Magazines
Popular Tags