How Do You Create a DriverPropertyInfo Object? in JDBC

The java.sql.Driver.getPropertyInfo() method returns an array of DriverPropertyInfo objects (DriverPropertyInfo[]). JDBC’s Driver.getPropertyInfo() method allows a GUI tool to determine the database connection properties for which it should prompt a user in order to get enough information to connect to a database. Depending on the values the user has supplied so far, additional values might become necessary. It might be necessary to iterate through several calls to getPropertyInfo(). If no more properties are necessary (i.e., all required parameters are specified), the call returns an array of zero length. In general, Driver.getPropertyInfo() offers java.sql.Connection choices to the user.

How It Works

For most database systems, the properties returned in the DriverPropertyInfo object are connection URL attributes, including a list of booted databases in a system (the databaseName attribute). When a nonzero-length array (DriverPropertyInfo[]) is returned by the Driver. getPropertyInfo() method, each element is a DriverPropertyInfo object representing a connection URL attribute that has not already been specified.

The signature of the Driver.getPropertyInfo() method is

/**
* @param url - the URL of the database to which to connect
* @param info - a proposed list of tag/value pairs that will
* be sent on connect open
* @return Returns an array of DriverPropertyInfo objects
* describing possible properties. This array may be
* an empty array if no properties are required.
* @throws SQLException - if a database access error occurs
*/
DriverPropertyInfo[] getPropertyInfo(java.lang.String url,
java.util.Properties info)
throws java.sql.SQLException

I’ll describe the Driver.getPropertyInfo() method next. This method gets information about the possible properties for this driver. The getPropertyInfo() method is intended to allow a generic GUI tool to discover what properties it should prompt the user for in order to get enough information to connect to a database. Note that depending on the values the user has supplied so far, additional values may be required, so it may be necessary to iterate through several calls to the getPropertyInfo() method. According to JDBC API Tutorial and Reference, Third Edition (Addison Wesley Publishing, August 1997):

The second argument should be null the first (and generally only) time this method is called. The second argument is included so that it is possible for an application to process input and present the human user with a list of properties from which to choose. Depending on the values the human has supplied so far, it is conceivable that additional values may be needed. In such cases, and assuming that the application has been written to handle the results of multiple calls to getPropertyInfo(), it may be necessary to iterate through several calls to getPropertyInfo(). If the application uses the information it gets to fill in values for a java.util.Properties object, then that object can be supplied to getPropertyInfo() in subsequent calls to the method. The properties returned by this method will often correspond to the properties that are set on a javax.sql.DataSource object for this driver.

The Solution

Here is some example code for a MySQL/Oracle database. This solution displays Driver property information as an XML serialized String object. In this solution, I check for drivers from Oracle, MySQL, and JDBC-ODBC. You may alter the code and add additional drivers. To do this, modify the loadDriver() method.

import java.sql.Driver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import java.sql.DriverPropertyInfo;
import jcb.util.DatabaseUtil;
public class TestDriverPropertyInfo {
// list of drivers to be tested.
public static final String MYSQL_DRIVER =
"com.mysql.jdbc.Driver";
public static final String ORACLE_DRIVER =
"oracle.jdbc.driver.OracleDriver";
public static final String JDBC_ODBC_BRIDGE_DRIVER =
"sun.jdbc.odbc.JdbcOdbcDriver";
public static void loadDriver(String dbVendor) throws Exception {
if (dbVendor.equalsIgnoreCase("mysql")) {
Class.forName(MYSQL_DRIVER); // load MySQL driver
}
else if (dbVendor.equalsIgnoreCase("oracle")) {
Class.forName(ORACLE_DRIVER); // load Oracle driver
}
else if (dbVendor.equalsIgnoreCase("jdbc-odbc")) {
// load JdbcOdbcDriver
Class.forName(JDBC_ODBC_BRIDGE_DRIVER);
}
else {
throw new Exception("db vendor not supported");
}
}
public static String getDriverPropertyInfoAsXML
(DriverPropertyInfo[] properties) throws Exception {
// If the driver is poorly implemented,
// a null object may be returned.
if(properties == null) {
return null;
}
// List all properties.
StringBuilder buffer = new StringBuilder();
buffer.append("<driver_property_info>");
for(int i = 0; i < properties.length; i++) {
// Get the property metadata
String name = properties[i].name;
boolean required = properties[i].required;
buffer.append("<property name=\"");
buffer.append(name);
buffer.append("\" required=\"");
buffer.append(required);
buffer.append("\">");
String value = properties[i].value;
buffer.append("<value>");
buffer.append(value);
buffer.append("</value>");
String description = properties[i].description;
buffer.append("<description>");
buffer.append(description);
buffer.append("</description>");
String[] choices = properties[i].choices;
buffer.append("<choices>");
if(choices != null) {
for(int j = 0; j < choices.length; j++) {
buffer.append("<choice>");
buffer.append(choices[j]);
buffer.append("</choice>");
}
}
buffer.append("</choices>");
buffer.append("</property>");
}
buffer.append("</driver_property_info>");
return buffer.toString();
}
public static void main(String[] args)throws Exception {
String dbVendor = args[0]; // { "mysql", "oracle" }
loadDriver(dbVendor);
// start with the least amount of information
// to see the full list of choices; we could also
// enter with a URL and Properties provided by a user.
// mysql URL = "jdbc:mysql://localhost/octopus";
// oracle URL = "jdbc:oracle:thin:@localhost:1521:caspian";
// JdbcOdbc URL = "jdbc:odbc:northwind";
String url = args[1]; // database url
Properties info = new Properties();
Driver driver = DriverManager.getDriver(url);
System.out.println("driver="+driver);
DriverPropertyInfo[] attributes =
driver.getPropertyInfo(url, info);
System.out.println("attributes="+attributes);
// zero length means a connection attempt can be made
System.out.println("Resolving properties for: " +
driver.getClass().getName());
System.out.println(getDriverPropertyInfoAsXML(attributes));
// you can insert code here to process the array, e.g.,
// display all options in a GUI and allow the user to
// pick and then set the attributes in info or URL.
// try the connection
Connection conn = DriverManager.getConnection(url, info);
System.out.println("----------");
}
}

Running the Solution for a MySQL Database

The following output is formatted to fit the page:

$ javac TestDriverPropertyInfo.java
$ java TestDriverPropertyInfo mysql "jdbc:mysql://localhost/octopus"
[email protected]
attributes=[Ljava.sql.DriverPropertyInfo;@addbf1
Resolving properties for: com.mysql.jdbc.Driver
<driver_property_info>
<property name="HOST" required="true">
<value>localhost</value>
<description>Hostname of MySQL Server</description>
<choices></choices>
</property>
<property name="PORT" required="false">
<value>3306</value>
<description>Port number of MySQL Server</description>
<choices></choices>
</property>
<property name="DBNAME" required="false">
<value>octopus</value>
<description>Database name</description>
<choices></choices>
</property>
<property name="user" required="true">
<value>null</value>
<description>Username to authenticate as</description>
<choices></choices>
</property>
<property name="password" required="true">
<value>null</value>
<description>Password to use for authentication</description>
<choices></choices>
</property>
<property name="autoReconnect" required="false">
<value>false</value>
<description>Should the driver try to re-establish bad
connections?</description>
<choices>
<choice>true</choice><choice>false</choice>
</choices>
</property>
<property name="maxReconnects" required="false">
<value>3</value>
<description>Maximum number of reconnects to at
tempt if autoReconnect is true</description>
<choices></choices>
</property>
<property name="initialTimeout" required="false">
<value>2</value>
<description>Initial timeout (seconds)to wait between failed
connections</description>
<choices></choices>
</property>
<property name="profileSql" required="false">
<value>false</value>
<description>Trace queries and their execution/fetch times
on STDERR (true/false) defaults to false</description>
<choices><choice>true</choice><choice>false</choice></choices>
</property>

</driver_property_info>
----------

Running the Solution for an Oracle Database

$ java TestDriverPropertyInfo oracle "jdbc:oracle:thin:@localhost:1521:caspian"
[email protected]
attributes=[Ljava.sql.DriverPropertyInfo;@19b49e6
Resolving properties for: oracle.jdbc.driver.OracleDriver
<driver_property_info>
</driver_property_info>
----------

As you can see from the preceding output, the Oracle driver does not properly return an array of DriverPropertyInfo objects, but instead returns an empty array.

Running the Solution for a JDBC-ODBC Configured Database

$ java TestDriverPropertyInfo "jdbc-odbc" "jdbc:odbc:northwind"
[email protected]
Exception in thread "main" java.sql.SQLException:
[Microsoft][ODBC Driver Manager] Driver does not support this function
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLBrowseConnect(JdbcOdbc.java:2552)
at sun.jdbc.odbc.JdbcOdbcDriver.getConnectionAttributes
(JdbcOdbcDriver.java:664)
at sun.jdbc.odbc.JdbcOdbcDriver.getPropertyInfo
(JdbcOdbcDriver.java:359)
at TestDriverPropertyInfo.main(TestDriverPropertyInfo.java:97)

As the preceding output shows, the JDBC-ODBC bridge driver does not properly return an array of DriverPropertyInfo objects, but instead throws a java.sql.SQLException. The MySQL database driver works better in this respect.



All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics