001 package org.apache.ojb.broker.metadata;
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.Connection;
019 import java.sql.DatabaseMetaData;
020 import java.sql.SQLException;
021 import java.util.HashMap;
022
023 import javax.sql.DataSource;
024 import org.apache.commons.beanutils.PropertyUtils;
025
026 /**
027 * This class provides some utility functions to OJB for working with JDBC metadata.
028 *
029 * @author <a href="mailto:tomdz@apache.org">Thomas Dudziak</a>
030 */
031 public class JdbcMetadataUtils
032 {
033 /** The name of the property returned by the {@link #splitConnectionUrl(String)} method
034 that contains the protocol */
035 public static final String PROPERTY_PROTOCOL = "protocol";
036 /** The name of the property returned by the {@link #splitConnectionUrl(String)} method
037 that contains the sub protocol */
038 public static final String PROPERTY_SUBPROTOCOL = "subprotocol";
039 /** The name of the property returned by the {@link #splitConnectionUrl(String)} method
040 that contains the database alias (the actual database url) */
041 public static final String PROPERTY_DBALIAS = "dbAlias";
042
043 /** Identifier for the DB2 platform */
044 public static final String PLATFORM_DB2 = "Db2";
045 /** Identifier for the Firebird platform */
046 public static final String PLATFORM_FIREBIRD = "Firebird";
047 /** Identifier for the Hsqldb platform */
048 public static final String PLATFORM_HSQLDB = "Hsqldb";
049 /** Identifier for the Informix platform */
050 public static final String PLATFORM_INFORMIX = "Informix";
051 /** Identifier for the MaxDB platform */
052 public static final String PLATFORM_MAXDB = "MaxDB";
053 /** Identifier for the McKoi platform */
054 public static final String PLATFORM_MCKOI = "McKoi";
055 /** Identifier for the MsAccess platform */
056 public static final String PLATFORM_MSACCESS = "MsAccess";
057 /** Identifier for the Microsoft SQL Server platform */
058 public static final String PLATFORM_MSSQLSERVER = "MsSQLServer";
059 /** Identifier for the MySQL platform */
060 public static final String PLATFORM_MYSQL = "MySQL";
061 /** Identifier for the generic Oracle platform */
062 public static final String PLATFORM_ORACLE = "Oracle";
063 /** Identifier for the Oracle9i platform */
064 public static final String PLATFORM_ORACLE9I = "Oracle9i";
065 /** Identifier for the PostgresSQL platform */
066 public static final String PLATFORM_POSTGRESQL = "PostgreSQL";
067 /** Identifier for the generic Sybase platform */
068 public static final String PLATFORM_SYBASE = "Sybase";
069 /** Identifier for the Sybase ASA platform */
070 public static final String PLATFORM_SYBASEASA = "SybaseASA";
071 /** Identifier for the Sybase ASE platform */
072 public static final String PLATFORM_SYBASEASE = "SybaseASE";
073 /** Identifier for the Oracle9i for WebLogic platform */
074 public static final String PLATFORM_WLORACLE9I = "WLOracle9i";
075
076 /** The standard DB2 jdbc driver */
077 public static final String DRIVER_DB2 = "COM.ibm.db2.jdbc.app.DB2Driver";
078 /** The i-net DB2 jdbc driver */
079 public static final String DRIVER_DB2_INET = "com.inet.drda.DRDADriver";
080 /** The standard Firebird jdbc driver */
081 public static final String DRIVER_FIREBIRD = "org.firebirdsql.jdbc.FBDriver";
082 /** The standard Hsqldb jdbc driver */
083 public static final String DRIVER_HSQLDB = "org.hsqldb.jdbcDriver";
084 /** The i-net pooled jdbc driver for SQLServer and Sybase */
085 public static final String DRIVER_INET_POOLED = "com.inet.pool.PoolDriver";
086 /** The standard Informix jdbc driver */
087 public static final String DRIVER_INFORMIX = "com.informix.jdbc.IfxDriver";
088 /** The jTDS jdbc driver for SQLServer and Sybase */
089 public static final String DRIVER_JTDS = "net.sourceforge.jtds.jdbc.Driver";
090 /** The standard MaxDB jdbc driver */
091 public static final String DRIVER_MAXDB = "com.sap.dbtech.jdbc.DriverSapDB";
092 /** The standard McKoi jdbc driver */
093 public static final String DRIVER_MCKOI = "com.mckoi.JDBCDriver";
094 /** The standard SQLServer jdbc driver */
095 public static final String DRIVER_MSSQLSERVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
096 /** The i-net SQLServer jdbc driver */
097 public static final String DRIVER_MSSQLSERVER_INET = "com.inet.tds.TdsDriver";
098 /** The JNetDirect SQLServer jdbc driver */
099 public static final String DRIVER_MSSQLSERVER_JSQLCONNECT = "com.jnetdirect.jsql.JSQLDriver";
100 /** The standard MySQL jdbc driver */
101 public static final String DRIVER_MYSQL = "com.mysql.jdbc.Driver";
102 /** The old MySQL jdbc driver */
103 public static final String DRIVER_MYSQL_OLD = "org.gjt.mm.mysql.Driver";
104 /** The standard Oracle jdbc driver */
105 public static final String DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";
106 /** The i-net Oracle jdbc driver */
107 public static final String DRIVER_ORACLE_INET = "com.inet.ora.OraDriver";
108 /** The standard PostgreSQL jdbc driver */
109 public static final String DRIVER_POSTGRESQL = "org.postgresql.Driver";
110 /** The standard Sapdb jdbc driver */
111 public static final String DRIVER_SAPDB = DRIVER_MAXDB;
112 /** The standard Sybase jdbc driver */
113 public static final String DRIVER_SYBASE = "com.sybase.jdbc2.jdbc.SybDriver";
114 /** The old Sybase jdbc driver */
115 public static final String DRIVER_SYBASE_OLD = "com.sybase.jdbc.SybDriver";
116 /** The i-net Sybase jdbc driver */
117 public static final String DRIVER_SYBASE_INET = "com.inet.syb.SybDriver";
118
119 /** The subprotocol used by the standard DB2 driver */
120 public static final String SUBPROTOCOL_DB2 = "db2";
121 /** The subprotocol used by the i-net DB2 driver */
122 public static final String SUBPROTOCOL_DB2_INET = "inetdb2";
123 /** The subprotocol used by the standard Firebird driver */
124 public static final String SUBPROTOCOL_FIREBIRD = "firebirdsql";
125 /** The subprotocol used by the standard Hsqldb driver */
126 public static final String SUBPROTOCOL_HSQLDB = "hsqldb";
127 /** The subprotocol used by the standard Informix driver */
128 public static final String SUBPROTOCOL_INFORMIX = "informix-sqli";
129 /** The subprotocol used by the standard MaxDB driver */
130 public static final String SUBPROTOCOL_MAXDB = "sapdb";
131 /** The subprotocol used by the standard McKoi driver */
132 public static final String SUBPROTOCOL_MCKOI = "mckoi";
133 /** The subprotocol used by the standard SQLServer driver */
134 public static final String SUBPROTOCOL_MSSQLSERVER = "microsoft:sqlserver";
135 /** A subprotocol used by the i-net SQLServer driver */
136 public static final String SUBPROTOCOL_MSSQLSERVER_INET = "inetdae";
137 /** A subprotocol used by the i-net SQLServer driver */
138 public static final String SUBPROTOCOL_MSSQLSERVER6_INET = "inetdae6";
139 /** A subprotocol used by the i-net SQLServer driver */
140 public static final String SUBPROTOCOL_MSSQLSERVER7_INET = "inetdae7";
141 /** A subprotocol used by the i-net SQLServer driver */
142 public static final String SUBPROTOCOL_MSSQLSERVER7A_INET = "inetdae7a";
143 /** A subprotocol used by the pooled i-net SQLServer driver */
144 public static final String SUBPROTOCOL_MSSQLSERVER_INET_POOLED = "inetpool:inetdae";
145 /** A subprotocol used by the pooled i-net SQLServer driver */
146 public static final String SUBPROTOCOL_MSSQLSERVER6_INET_POOLED = "inetpool:inetdae6";
147 /** A subprotocol used by the pooled i-net SQLServer driver */
148 public static final String SUBPROTOCOL_MSSQLSERVER7_INET_POOLED = "inetpool:inetdae7";
149 /** A subprotocol used by the pooled i-net SQLServer driver */
150 public static final String SUBPROTOCOL_MSSQLSERVER7A_INET_POOLED = "inetpool:inetdae7a";
151 /** The subprotocol used by the JNetDirect SQLServer driver */
152 public static final String SUBPROTOCOL_MSSQLSERVER_JSQLCONNECT = "JSQLConnect";
153 /** The subprotocol used by the jTDS SQLServer driver */
154 public static final String SUBPROTOCOL_MSSQLSERVER_JTDS = "jtds:sqlserver";
155 /** The subprotocol used by the standard MySQL driver */
156 public static final String SUBPROTOCOL_MYSQL = "mysql";
157 /** The subprotocol used by the standard Oracle driver */
158 public static final String SUBPROTOCOL_ORACLE = "oracle";
159 /** The subprotocol used by the i-net Oracle driver */
160 public static final String SUBPROTOCOL_ORACLE_INET = "inetora";
161 /** The subprotocol used by the standard PostgreSQL driver */
162 public static final String SUBPROTOCOL_POSTGRESQL = "postgresql";
163 /** The subprotocol used by the standard Sapdb driver */
164 public static final String SUBPROTOCOL_SAPDB = SUBPROTOCOL_MAXDB;
165 /** The subprotocol used by the standard Sybase driver */
166 public static final String SUBPROTOCOL_SYBASE = "sybase:Tds";
167 /** The subprotocol used by the i-net Sybase driver */
168 public static final String SUBPROTOCOL_SYBASE_INET = "inetsyb";
169 /** The subprotocol used by the pooled i-net Sybase driver */
170 public static final String SUBPROTOCOL_SYBASE_INET_POOLED = "inetpool:inetsyb";
171 /** The subprotocol used by the jTDS Sybase driver */
172 public static final String 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 jdbcSubProtocolToPlatform = new HashMap();
177 /** Maps the jdbc driver name to a OJB platform name */
178 private HashMap jdbcDriverToPlatform = new HashMap();
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 dataSource, String username, String password) throws MetadataException
246 {
247 String realUsername = (jcd.getUserName() != null ? jcd.getUserName() : username);
248 String realPassword = (jcd.getPassWord() != null ? jcd.getPassWord() : password);
249 Connection connection = null;
250 DatabaseMetaData 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 t)
267 {
268 if (connection != null)
269 {
270 try
271 {
272 connection.close();
273 }
274 catch (SQLException ex)
275 {}
276 }
277 throw new MetadataException("Could not get the metadata from the given datasource", t);
278 }
279
280 try
281 {
282 HashMap urlComponents = parseConnectionUrl(metadata.getURL());
283
284 if (urlComponents.containsKey(PROPERTY_DBALIAS))
285 {
286 jcd.setProtocol((String)urlComponents.get(PROPERTY_PROTOCOL));
287 jcd.setSubProtocol((String)urlComponents.get(PROPERTY_SUBPROTOCOL));
288 jcd.setDbAlias((String)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)jdbcSubProtocolToPlatform.get(jcd.getSubProtocol()));
293 }
294 }
295 }
296 catch (Throwable t)
297 {
298 try
299 {
300 connection.close();
301 }
302 catch (SQLException 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 majorVersion = (Integer)PropertyUtils.getProperty(metadata, "JDBCMajorVersion");
310 Integer minorVersion = (Integer)PropertyUtils.getProperty(metadata, "JDBCMinorVersion");
311
312 jcd.setJdbcLevel(Double.parseDouble(majorVersion.toString()+"."+minorVersion.toString()));
313 }
314 catch (Throwable 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 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 parseConnectionUrl(String jdbcConnectionUrl)
335 {
336 HashMap result = new HashMap();
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 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 findPlatformFor(String jdbcSubProtocol, String jdbcDriver)
392 {
393 String platform = (String)jdbcSubProtocolToPlatform.get(jdbcSubProtocol);
394
395 if (platform == null)
396 {
397 platform = (String)jdbcDriverToPlatform.get(jdbcDriver);
398 }
399 return platform;
400 }
401 }