KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > ojb > broker > metadata > JdbcMetadataUtils


1 package org.apache.ojb.broker.metadata;
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.Connection JavaDoc;
19 import java.sql.DatabaseMetaData JavaDoc;
20 import java.sql.SQLException JavaDoc;
21 import java.util.HashMap JavaDoc;
22
23 import javax.sql.DataSource JavaDoc;
24 import org.apache.commons.beanutils.PropertyUtils;
25
26 /**
27  * This class provides some utility functions to OJB for working with JDBC metadata.
28  *
29  * @author <a HREF="mailto:tomdz@apache.org">Thomas Dudziak</a>
30  */
31 public class JdbcMetadataUtils
32 {
33     /** The name of the property returned by the {@link #splitConnectionUrl(String)} method
34         that contains the protocol */
35     public static final String JavaDoc PROPERTY_PROTOCOL = "protocol";
36     /** The name of the property returned by the {@link #splitConnectionUrl(String)} method
37         that contains the sub protocol */
38     public static final String JavaDoc PROPERTY_SUBPROTOCOL = "subprotocol";
39     /** The name of the property returned by the {@link #splitConnectionUrl(String)} method
40         that contains the database alias (the actual database url) */
41     public static final String JavaDoc PROPERTY_DBALIAS = "dbAlias";
42
43     /** Identifier for the DB2 platform */
44     public static final String JavaDoc PLATFORM_DB2 = "Db2";
45     /** Identifier for the Firebird platform */
46     public static final String JavaDoc PLATFORM_FIREBIRD = "Firebird";
47     /** Identifier for the Hsqldb platform */
48     public static final String JavaDoc PLATFORM_HSQLDB = "Hsqldb";
49     /** Identifier for the Informix platform */
50     public static final String JavaDoc PLATFORM_INFORMIX = "Informix";
51     /** Identifier for the MaxDB platform */
52     public static final String JavaDoc PLATFORM_MAXDB = "MaxDB";
53     /** Identifier for the McKoi platform */
54     public static final String JavaDoc PLATFORM_MCKOI = "McKoi";
55     /** Identifier for the MsAccess platform */
56     public static final String JavaDoc PLATFORM_MSACCESS = "MsAccess";
57     /** Identifier for the Microsoft SQL Server platform */
58     public static final String JavaDoc PLATFORM_MSSQLSERVER = "MsSQLServer";
59     /** Identifier for the MySQL platform */
60     public static final String JavaDoc PLATFORM_MYSQL = "MySQL";
61     /** Identifier for the generic Oracle platform */
62     public static final String JavaDoc PLATFORM_ORACLE = "Oracle";
63     /** Identifier for the Oracle9i platform */
64     public static final String JavaDoc PLATFORM_ORACLE9I = "Oracle9i";
65     /** Identifier for the PostgresSQL platform */
66     public static final String JavaDoc PLATFORM_POSTGRESQL = "PostgreSQL";
67     /** Identifier for the generic Sybase platform */
68     public static final String JavaDoc PLATFORM_SYBASE = "Sybase";
69     /** Identifier for the Sybase ASA platform */
70     public static final String JavaDoc PLATFORM_SYBASEASA = "SybaseASA";
71     /** Identifier for the Sybase ASE platform */
72     public static final String JavaDoc PLATFORM_SYBASEASE = "SybaseASE";
73     /** Identifier for the Oracle9i for WebLogic platform */
74     public static final String JavaDoc PLATFORM_WLORACLE9I = "WLOracle9i";
75
76     /** The standard DB2 jdbc driver */
77     public static final String JavaDoc DRIVER_DB2 = "COM.ibm.db2.jdbc.app.DB2Driver";
78     /** The i-net DB2 jdbc driver */
79     public static final String JavaDoc DRIVER_DB2_INET = "com.inet.drda.DRDADriver";
80     /** The standard Firebird jdbc driver */
81     public static final String JavaDoc DRIVER_FIREBIRD = "org.firebirdsql.jdbc.FBDriver";
82     /** The standard Hsqldb jdbc driver */
83     public static final String JavaDoc DRIVER_HSQLDB = "org.hsqldb.jdbcDriver";
84     /** The i-net pooled jdbc driver for SQLServer and Sybase */
85     public static final String JavaDoc DRIVER_INET_POOLED = "com.inet.pool.PoolDriver";
86     /** The standard Informix jdbc driver */
87     public static final String JavaDoc DRIVER_INFORMIX = "com.informix.jdbc.IfxDriver";
88     /** The jTDS jdbc driver for SQLServer and Sybase */
89     public static final String JavaDoc DRIVER_JTDS = "net.sourceforge.jtds.jdbc.Driver";
90     /** The standard MaxDB jdbc driver */
91     public static final String JavaDoc DRIVER_MAXDB = "com.sap.dbtech.jdbc.DriverSapDB";
92     /** The standard McKoi jdbc driver */
93     public static final String JavaDoc DRIVER_MCKOI = "com.mckoi.JDBCDriver";
94     /** The standard SQLServer jdbc driver */
95     public static final String JavaDoc DRIVER_MSSQLSERVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
96     /** The i-net SQLServer jdbc driver */
97     public static final String JavaDoc DRIVER_MSSQLSERVER_INET = "com.inet.tds.TdsDriver";
98     /** The JNetDirect SQLServer jdbc driver */
99     public static final String JavaDoc DRIVER_MSSQLSERVER_JSQLCONNECT = "com.jnetdirect.jsql.JSQLDriver";
100     /** The standard MySQL jdbc driver */
101     public static final String JavaDoc DRIVER_MYSQL = "com.mysql.jdbc.Driver";
102     /** The old MySQL jdbc driver */
103     public static final String JavaDoc DRIVER_MYSQL_OLD = "org.gjt.mm.mysql.Driver";
104     /** The standard Oracle jdbc driver */
105     public static final String JavaDoc DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";
106     /** The i-net Oracle jdbc driver */
107     public static final String JavaDoc DRIVER_ORACLE_INET = "com.inet.ora.OraDriver";
108     /** The standard PostgreSQL jdbc driver */
109     public static final String JavaDoc DRIVER_POSTGRESQL = "org.postgresql.Driver";
110     /** The standard Sapdb jdbc driver */
111     public static final String JavaDoc DRIVER_SAPDB = DRIVER_MAXDB;
112     /** The standard Sybase jdbc driver */
113     public static final String JavaDoc DRIVER_SYBASE = "com.sybase.jdbc2.jdbc.SybDriver";
114     /** The old Sybase jdbc driver */
115     public static final String JavaDoc DRIVER_SYBASE_OLD = "com.sybase.jdbc.SybDriver";
116     /** The i-net Sybase jdbc driver */
117     public static final String JavaDoc DRIVER_SYBASE_INET = "com.inet.syb.SybDriver";
118
119     /** The subprotocol used by the standard DB2 driver */
120     public static final String JavaDoc SUBPROTOCOL_DB2 = "db2";
121     /** The subprotocol used by the i-net DB2 driver */
122     public static final String JavaDoc SUBPROTOCOL_DB2_INET = "inetdb2";
123     /** The subprotocol used by the standard Firebird driver */
124     public static final String JavaDoc SUBPROTOCOL_FIREBIRD = "firebirdsql";
125     /** The subprotocol used by the standard Hsqldb driver */
126     public static final String JavaDoc SUBPROTOCOL_HSQLDB = "hsqldb";
127     /** The subprotocol used by the standard Informix driver */
128     public static final String JavaDoc SUBPROTOCOL_INFORMIX = "informix-sqli";
129     /** The subprotocol used by the standard MaxDB driver */
130     public static final String JavaDoc SUBPROTOCOL_MAXDB = "sapdb";
131     /** The subprotocol used by the standard McKoi driver */
132     public static final String JavaDoc SUBPROTOCOL_MCKOI = "mckoi";
133     /** The subprotocol used by the standard SQLServer driver */
134     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER = "microsoft:sqlserver";
135     /** A subprotocol used by the i-net SQLServer driver */
136     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER_INET = "inetdae";
137     /** A subprotocol used by the i-net SQLServer driver */
138     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER6_INET = "inetdae6";
139     /** A subprotocol used by the i-net SQLServer driver */
140     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER7_INET = "inetdae7";
141     /** A subprotocol used by the i-net SQLServer driver */
142     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER7A_INET = "inetdae7a";
143     /** A subprotocol used by the pooled i-net SQLServer driver */
144     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER_INET_POOLED = "inetpool:inetdae";
145     /** A subprotocol used by the pooled i-net SQLServer driver */
146     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER6_INET_POOLED = "inetpool:inetdae6";
147     /** A subprotocol used by the pooled i-net SQLServer driver */
148     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER7_INET_POOLED = "inetpool:inetdae7";
149     /** A subprotocol used by the pooled i-net SQLServer driver */
150     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER7A_INET_POOLED = "inetpool:inetdae7a";
151     /** The subprotocol used by the JNetDirect SQLServer driver */
152     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER_JSQLCONNECT = "JSQLConnect";
153     /** The subprotocol used by the jTDS SQLServer driver */
154     public static final String JavaDoc SUBPROTOCOL_MSSQLSERVER_JTDS = "jtds:sqlserver";
155     /** The subprotocol used by the standard MySQL driver */
156     public static final String JavaDoc SUBPROTOCOL_MYSQL = "mysql";
157     /** The subprotocol used by the standard Oracle driver */
158     public static final String JavaDoc SUBPROTOCOL_ORACLE = "oracle";
159     /** The subprotocol used by the i-net Oracle driver */
160     public static final String JavaDoc SUBPROTOCOL_ORACLE_INET = "inetora";
161     /** The subprotocol used by the standard PostgreSQL driver */
162     public static final String JavaDoc SUBPROTOCOL_POSTGRESQL = "postgresql";
163     /** The subprotocol used by the standard Sapdb driver */
164     public static final String JavaDoc SUBPROTOCOL_SAPDB = SUBPROTOCOL_MAXDB;
165     /** The subprotocol used by the standard Sybase driver */
166     public static final String JavaDoc SUBPROTOCOL_SYBASE = "sybase:Tds";
167     /** The subprotocol used by the i-net Sybase driver */
168     public static final String JavaDoc SUBPROTOCOL_SYBASE_INET = "inetsyb";
169     /** The subprotocol used by the pooled i-net Sybase driver */
170     public static final String JavaDoc SUBPROTOCOL_SYBASE_INET_POOLED = "inetpool:inetsyb";
171     /** The subprotocol used by the jTDS Sybase driver */
172     public static final String JavaDoc SUBPROTOCOL_SYBASE_JTDS = "jtds:sybase";
173     
174     
175     /** Maps the sub-protocl part of a jdbc connection url to a OJB platform name */
176     private HashMap JavaDoc jdbcSubProtocolToPlatform = new HashMap JavaDoc();
177     /** Maps the jdbc driver name to a OJB platform name */
178     private HashMap JavaDoc jdbcDriverToPlatform = new HashMap JavaDoc();
179
180     /**
181      * Creates a new <code>JdbcMetadataUtils</code> object.
182      */
183     public JdbcMetadataUtils()
184     {
185         // Note that currently Sapdb and MaxDB have equal subprotocols and
186 // drivers so we have no means to distinguish them
187 jdbcSubProtocolToPlatform.put(SUBPROTOCOL_DB2, PLATFORM_DB2);
188         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_DB2_INET, PLATFORM_DB2);
189         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_FIREBIRD, PLATFORM_FIREBIRD);
190         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_HSQLDB, PLATFORM_HSQLDB);
191         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_INFORMIX, PLATFORM_INFORMIX);
192         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MAXDB, PLATFORM_MAXDB);
193         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER, PLATFORM_MSSQLSERVER);
194         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER_INET, PLATFORM_MSSQLSERVER);
195         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER6_INET, PLATFORM_MSSQLSERVER);
196         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER7_INET, PLATFORM_MSSQLSERVER);
197         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER7A_INET, PLATFORM_MSSQLSERVER);
198         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER_INET_POOLED, PLATFORM_MSSQLSERVER);
199         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER6_INET_POOLED, PLATFORM_MSSQLSERVER);
200         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER7_INET_POOLED, PLATFORM_MSSQLSERVER);
201         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER7A_INET_POOLED, PLATFORM_MSSQLSERVER);
202         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MSSQLSERVER_JTDS, PLATFORM_MSSQLSERVER);
203         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_MYSQL, PLATFORM_MYSQL);
204         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_ORACLE, PLATFORM_ORACLE);
205         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_ORACLE_INET, PLATFORM_ORACLE);
206         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_POSTGRESQL, PLATFORM_POSTGRESQL);
207         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_SYBASE, PLATFORM_SYBASE);
208         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_SYBASE_INET, PLATFORM_SYBASE);
209         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_SYBASE_INET_POOLED, PLATFORM_SYBASE);
210         jdbcSubProtocolToPlatform.put(SUBPROTOCOL_SYBASE_JTDS, PLATFORM_SYBASE);
211
212         jdbcDriverToPlatform.put(DRIVER_DB2, PLATFORM_DB2);
213         jdbcDriverToPlatform.put(DRIVER_DB2_INET, PLATFORM_DB2);
214         jdbcDriverToPlatform.put(DRIVER_FIREBIRD, PLATFORM_FIREBIRD);
215         jdbcDriverToPlatform.put(DRIVER_HSQLDB, PLATFORM_HSQLDB);
216         jdbcDriverToPlatform.put(DRIVER_INFORMIX, PLATFORM_INFORMIX);
217         jdbcDriverToPlatform.put(DRIVER_MAXDB, PLATFORM_MAXDB);
218         jdbcDriverToPlatform.put(DRIVER_MCKOI, PLATFORM_MCKOI);
219         jdbcDriverToPlatform.put(DRIVER_MSSQLSERVER, PLATFORM_MSSQLSERVER);
220         jdbcDriverToPlatform.put(DRIVER_MSSQLSERVER_INET, PLATFORM_MSSQLSERVER);
221         jdbcDriverToPlatform.put(DRIVER_MSSQLSERVER_JSQLCONNECT, PLATFORM_MSSQLSERVER);
222         jdbcDriverToPlatform.put(DRIVER_MYSQL, PLATFORM_MYSQL);
223         jdbcDriverToPlatform.put(DRIVER_MYSQL_OLD, PLATFORM_MYSQL);
224         jdbcDriverToPlatform.put(DRIVER_ORACLE, PLATFORM_ORACLE);
225         jdbcDriverToPlatform.put(DRIVER_ORACLE_INET, PLATFORM_ORACLE);
226         jdbcDriverToPlatform.put(DRIVER_POSTGRESQL, PLATFORM_POSTGRESQL);
227         jdbcDriverToPlatform.put(DRIVER_SYBASE, PLATFORM_SYBASE);
228         jdbcDriverToPlatform.put(DRIVER_SYBASE_OLD, PLATFORM_SYBASE);
229         jdbcDriverToPlatform.put(DRIVER_SYBASE_INET, PLATFORM_SYBASE);
230     }
231
232     /**
233      * Fills parameters of the given {@link JdbcConnectionDescriptor} with metadata
234      * extracted from the given datasource.
235      *
236      * @param jcd The jdbc connection descriptor to fill
237      * @param dataSource The data source
238      * @param username The username required to establish a connection via the data source
239      * Can be empty if the data source does not require it or if one
240      * is specified in the jdbc connection descriptor
241      * @param password The username required to establish a connection via the data source
242      * Can be empty if the data source or username does not require it or if one
243      * is specified in the jdbc connection descriptor
244      */
245     public void fillJCDFromDataSource(JdbcConnectionDescriptor jcd, DataSource JavaDoc dataSource, String JavaDoc username, String JavaDoc password) throws MetadataException
246     {
247         String JavaDoc realUsername = (jcd.getUserName() != null ? jcd.getUserName() : username);
248         String JavaDoc realPassword = (jcd.getPassWord() != null ? jcd.getPassWord() : password);
249         Connection JavaDoc connection = null;
250         DatabaseMetaData JavaDoc metadata = null;
251
252         try
253         {
254             // we have to open a connection to be able to retrieve metadata
255 if (realUsername != null)
256             {
257                 connection = dataSource.getConnection(realUsername, realPassword);
258             }
259             else
260             {
261                 connection = dataSource.getConnection();
262             }
263
264             metadata = connection.getMetaData();
265         }
266         catch (Throwable JavaDoc t)
267         {
268             if (connection != null)
269             {
270                 try
271                 {
272                     connection.close();
273                 }
274                 catch (SQLException JavaDoc ex)
275                 {}
276             }
277             throw new MetadataException("Could not get the metadata from the given datasource", t);
278         }
279
280         try
281         {
282             HashMap JavaDoc urlComponents = parseConnectionUrl(metadata.getURL());
283     
284             if (urlComponents.containsKey(PROPERTY_DBALIAS))
285             {
286                 jcd.setProtocol((String JavaDoc)urlComponents.get(PROPERTY_PROTOCOL));
287                 jcd.setSubProtocol((String JavaDoc)urlComponents.get(PROPERTY_SUBPROTOCOL));
288                 jcd.setDbAlias((String JavaDoc)urlComponents.get(PROPERTY_DBALIAS));
289                 if (jdbcSubProtocolToPlatform.containsKey(jcd.getSubProtocol()))
290                 {
291                     // TODO: We might be able to use this: metadata.getDatabaseProductName();
292 jcd.setDbms((String JavaDoc)jdbcSubProtocolToPlatform.get(jcd.getSubProtocol()));
293                 }
294             }
295         }
296         catch (Throwable JavaDoc t)
297         {
298             try
299             {
300                 connection.close();
301             }
302             catch (SQLException JavaDoc ex)
303             {}
304             throw new MetadataException("Could not get the metadata from the given datasource", t);
305         }
306         try
307         {
308             // this will only work with JDK >= 1.4 and only with some jdbc drivers
309 Integer JavaDoc majorVersion = (Integer JavaDoc)PropertyUtils.getProperty(metadata, "JDBCMajorVersion");
310             Integer JavaDoc minorVersion = (Integer JavaDoc)PropertyUtils.getProperty(metadata, "JDBCMinorVersion");
311
312             jcd.setJdbcLevel(Double.parseDouble(majorVersion.toString()+"."+minorVersion.toString()));
313         }
314         catch (Throwable JavaDoc t)
315         {
316             // otherwise we're assuming JDBC 2.0 compliance
317 jcd.setJdbcLevel(2.0);
318         }
319         try
320         {
321             connection.close();
322         }
323         catch (SQLException JavaDoc ex)
324         {}
325     }
326     
327     /**
328      * Splits the given jdbc connection url into its components and puts them into
329      * a hash map using the <code>PROPERTY_</code> constants.
330      *
331      * @param jdbcConnectionUrl The connection url
332      * @return The properties
333      */
334     public HashMap JavaDoc parseConnectionUrl(String JavaDoc jdbcConnectionUrl)
335     {
336         HashMap JavaDoc result = new HashMap JavaDoc();
337
338         if (jdbcConnectionUrl == null)
339         {
340             return result;
341         }
342
343         int pos = jdbcConnectionUrl.indexOf(':');
344         int lastPos;
345
346         result.put(PROPERTY_PROTOCOL, jdbcConnectionUrl.substring(0, pos));
347
348         lastPos = pos;
349         pos = jdbcConnectionUrl.indexOf(':', lastPos + 1);
350
351         String JavaDoc subProtocol = jdbcConnectionUrl.substring(lastPos + 1, pos);
352
353         // there are a few jdbc drivers that have a subprotocol containing one or more ':'
354 if ("inetpool".equals(subProtocol))
355         {
356             // Possible forms are:
357 // inetpool:<subprotocol>
358 // inetpool:jdbc:<subprotocol> (where we'll remove the 'jdbc' part)
359
360             int tmpPos = jdbcConnectionUrl.indexOf(':', pos + 1);
361
362             if ("inetpool:jdbc".equals(jdbcConnectionUrl.substring(lastPos + 1, tmpPos)))
363             {
364                 pos = tmpPos;
365                 tmpPos = jdbcConnectionUrl.indexOf(':', pos + 1);
366             }
367             subProtocol += ":" + jdbcConnectionUrl.substring(pos + 1, tmpPos);
368         }
369         else if ("jtds".equals(subProtocol) ||
370                  "microsoft".equals(subProtocol) ||
371                  "sybase".equals(subProtocol))
372         {
373             pos = jdbcConnectionUrl.indexOf(':', pos + 1);
374             subProtocol = ":" + jdbcConnectionUrl.substring(lastPos + 1, pos);
375         }
376
377         result.put(PROPERTY_SUBPROTOCOL, subProtocol);
378         result.put(PROPERTY_DBALIAS, jdbcConnectionUrl.substring(pos + 1));
379
380         return result;
381     }
382
383     /**
384      * Derives the OJB platform to use for a database that is connected via a url using the specified
385      * subprotocol, and where the specified jdbc driver is used.
386      *
387      * @param jdbcSubProtocol The JDBC subprotocol used to connect to the database
388      * @param jdbcDriver The JDBC driver used to connect to the database
389      * @return The platform identifier or <code>null</code> if no platform could be found
390      */
391     public String JavaDoc findPlatformFor(String JavaDoc jdbcSubProtocol, String JavaDoc jdbcDriver)
392     {
393         String JavaDoc platform = (String JavaDoc)jdbcSubProtocolToPlatform.get(jdbcSubProtocol);
394
395         if (platform == null)
396         {
397             platform = (String JavaDoc)jdbcDriverToPlatform.get(jdbcDriver);
398         }
399         return platform;
400     }
401 }
402
Popular Tags