umontreal.iro.lecuyer.util

Class JDBCManager

java.lang.Object

umontreal.iro.lecuyer.util.JDBCManager

public class JDBCManager
extends java.lang.Object

This class provides some facilities to connect to a SQL database and to retrieve data stored in it. JDBC provides a standardized interface for accessing a database independently of a specific database management system (DBMS). The user of JDBC must create a Connection object used to send SQL queries to the underlying DBMS, but the creation of the connection adds a DBMS-specific portion in the application. This class helps the developer in moving the DBMS-specific information out of the source code by storing it in a properties file. The methods in this class can read such a properties file and establish the JDBC connection. The connection can be made by using a DataSource obtained through a JNDI server, or by a JDBC URI associated with a driver class. Therefore, the properties used to connect to the database must be a JNDI name (jdbc.jndi-name), or a driver to load (jdbc.driver) with the URI of a database (jdbc.uri).

jdbc.driver=com.mysql.jdbc.Driver

jdbc.uri=jdbc:mysql://mysql.iro.umontreal.ca/database?user=foo&password=bar

The connection is established using the connectToDatabase method. Shortcut methods are also available to read the properties from a file or a resource before establishing the connection. This class also provides shortcut methods to read data from a database and to copy the data into Java arrays.

Constructor Summary

Constructors

Constructor and Description
JDBCManager()

Method Summary

Modifier and Type	Method and Description
static java.sql.Connection	connectToDatabase(java.io.File file) Equivalent to connectToDatabase (new FileInputStream (file)).
static java.sql.Connection	connectToDatabase(java.io.InputStream is) Returns a connection to the database using the properties read from stream is.
static java.sql.Connection	connectToDatabase(java.util.Properties prop) Connects to the database using the properties prop and returns the an object representing the connection.
static java.sql.Connection	connectToDatabase(java.lang.String fileName) Equivalent to connectToDatabase (new FileInputStream (fileName)).
static java.sql.Connection	connectToDatabase(java.net.URL url) Equivalent to connectToDatabase (url.openStream()).
static java.sql.Connection	connectToDatabaseFromResource(java.lang.String resource) Uses connectToDatabase with the stream obtained from the resource resource.
static double[]	readDoubleData(java.sql.Connection connection, java.lang.String query) Copies the result of the SQL query query into an array of double-precision values.
static double[]	readDoubleData(java.sql.Connection connection, java.lang.String table, java.lang.String column) Returns the values of the column column of the table table.
static double[]	readDoubleData(java.sql.Statement stmt, java.lang.String query) Copies the result of the SQL query query into an array of double-precision values.
static double[]	readDoubleData(java.sql.Statement stmt, java.lang.String table, java.lang.String column) Returns the values of the column column of the table table.
static double[][]	readDoubleData2D(java.sql.Connection connection, java.lang.String query) Copies the result of the SQL query query into a rectangular 2D array of double-precision values.
static double[][]	readDoubleData2D(java.sql.Statement stmt, java.lang.String query) Copies the result of the SQL query query into a rectangular 2D array of double-precision values.
static double[][]	readDoubleData2DTable(java.sql.Connection connection, java.lang.String table) Returns the values of the columns of the table table.
static double[][]	readDoubleData2DTable(java.sql.Statement stmt, java.lang.String table) Returns the values of the columns of the table table.
static int[]	readIntData(java.sql.Connection connection, java.lang.String query) Copies the result of the SQL query query into an array of integers.
static int[]	readIntData(java.sql.Connection connection, java.lang.String table, java.lang.String column) Returns the values of the column column of the table table.
static int[]	readIntData(java.sql.Statement stmt, java.lang.String query) Copies the result of the SQL query query into an array of integers.
static int[]	readIntData(java.sql.Statement stmt, java.lang.String table, java.lang.String column) Returns the values of the column column of the table table.
static int[][]	readIntData2D(java.sql.Connection connection, java.lang.String query) Copies the result of the SQL query query into a rectangular 2D array of integers.
static int[][]	readIntData2D(java.sql.Statement stmt, java.lang.String query) Copies the result of the SQL query query into a rectangular 2D array of integers.
static int[][]	readIntData2DTable(java.sql.Connection connection, java.lang.String table) Returns the values of the columns of the table table.
static int[][]	readIntData2DTable(java.sql.Statement stmt, java.lang.String table) Returns the values of the columns of the table table.
static java.lang.Object[]	readObjectData(java.sql.Connection connection, java.lang.String query) Copies the result of the SQL query query into an array of objects.
static java.lang.Object[]	readObjectData(java.sql.Connection connection, java.lang.String table, java.lang.String column) Returns the values of the column column of the table table.
static java.lang.Object[]	readObjectData(java.sql.Statement stmt, java.lang.String query) Copies the result of the SQL query query into an array of objects.
static java.lang.Object[]	readObjectData(java.sql.Statement stmt, java.lang.String table, java.lang.String column) Returns the values of the column column of the table table.
static java.lang.Object[][]	readObjectData2D(java.sql.Connection connection, java.lang.String query) Copies the result of the SQL query query into a rectangular 2D array of integers.
static java.lang.Object[][]	readObjectData2D(java.sql.Statement stmt, java.lang.String query) Copies the result of the SQL query query into a rectangular 2D array of objects.
static java.lang.Object[][]	readObjectData2DTable(java.sql.Connection connection, java.lang.String table) Returns the values of the columns of the table table.
static java.lang.Object[][]	readObjectData2DTable(java.sql.Statement stmt, java.lang.String table) Returns the values of the columns of the table table.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

JDBCManager

public JDBCManager()

Method Detail

connectToDatabase

public static java.sql.Connection connectToDatabase(java.util.Properties prop)
                                             throws java.sql.SQLException

Connects to the database using the properties prop and returns the an object representing the connection. The properties stored in prop must be a JNDI name (jdbc.jndi-name), or the name of a driver (jdbc.driver) to load and the URI of the database (jdbc.uri). When a JNDI name is given, this method constructs a context using the nullary constructor of InitialContext, uses the context to get a DataSource object, and uses the data source to obtain a connection. This method assumes that JNDI is configured correctly; see the class InitialContext for more information about configuring JNDI. If no JNDI name is specified, the method looks for a JDBC URI. If a driver class name is specified along with the URI, the corresponding driver is loaded and registered with the JDBC DriverManager. The driver manager is then used to obtain the connection using the URI. This method throws an SQLException if the connection failed and an IllegalArgumentException if the properties do not contain the required values.

Parameters:

prop - the properties to connect to the database.

Returns:

the connection to the database.

Throws:

java.sql.SQLException - if the connection failed.

java.lang.IllegalArgumentException - if the properties do not contain the require values.

connectToDatabase

public static java.sql.Connection connectToDatabase(java.io.InputStream is)
                                             throws java.io.IOException,
                                                    java.sql.SQLException

Returns a connection to the database using the properties read from stream is. This method loads the properties from the given stream, and calls connectToDatabase to establish the connection.

Parameters:

is - the stream to read for the properties.

Returns:

the connection to the database.

Throws:

java.sql.SQLException - if the connection failed.

java.io.IOException - if the stream can not be read correctly.

java.lang.IllegalArgumentException - if the properties do not contain the require values.

connectToDatabase

public static java.sql.Connection connectToDatabase(java.net.URL url)
                                             throws java.io.IOException,
                                                    java.sql.SQLException

Equivalent to connectToDatabase (url.openStream()).

Throws:

java.io.IOException

java.sql.SQLException

connectToDatabase

public static java.sql.Connection connectToDatabase(java.io.File file)
                                             throws java.io.IOException,
                                                    java.sql.SQLException

Equivalent to connectToDatabase (new FileInputStream (file)).

Throws:

java.io.IOException

java.sql.SQLException

connectToDatabase

public static java.sql.Connection connectToDatabase(java.lang.String fileName)
                                             throws java.io.IOException,
                                                    java.sql.SQLException

Equivalent to connectToDatabase (new FileInputStream (fileName)).

Throws:

java.io.IOException

java.sql.SQLException

connectToDatabaseFromResource

public static java.sql.Connection connectToDatabaseFromResource(java.lang.String resource)
                                                         throws java.io.IOException,
                                                                java.sql.SQLException

Uses connectToDatabase with the stream obtained from the resource resource. This method searches the file resource on the class path, opens the first resource found, and extracts properties from it. It then uses connectToDatabase to establish the connection.

Throws:

java.io.IOException

java.sql.SQLException

readDoubleData

public static double[] readDoubleData(java.sql.Statement stmt,
                                      java.lang.String query)
                               throws java.sql.SQLException

Copies the result of the SQL query query into an array of double-precision values. This method uses the statement stmt to execute the given query, and assumes that the first column of the result set contains double-precision values. Each row of the result set then becomes an element of an array of double-precision values which is returned by this method. This method throws an SQLException if the query is not valid.

Parameters:

stmt - the statement used to make the query.

query - the SQL query to execute.

Returns:

the first column of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readDoubleData

public static double[] readDoubleData(java.sql.Connection connection,
                                      java.lang.String query)
                               throws java.sql.SQLException

Copies the result of the SQL query query into an array of double-precision values. This method uses the active connection connection to create a statement, and passes this statement, with the query, to readDoubleData, which returns an array of double-precision values.

Parameters:

connection - the active connection to the database.

query - the SQL query to execute.

Returns:

the first column of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readDoubleData

public static double[] readDoubleData(java.sql.Statement stmt,
                                      java.lang.String table,
                                      java.lang.String column)
                               throws java.sql.SQLException

Returns the values of the column column of the table table. This method is equivalent to readDoubleData (stmt, "SELECT column FROM table").

Throws:

java.sql.SQLException

readDoubleData

public static double[] readDoubleData(java.sql.Connection connection,
                                      java.lang.String table,
                                      java.lang.String column)
                               throws java.sql.SQLException

Returns the values of the column column of the table table. This method is equivalent to readDoubleData (connection, "SELECT column FROM table").

Throws:

java.sql.SQLException

readIntData

public static int[] readIntData(java.sql.Statement stmt,
                                java.lang.String query)
                         throws java.sql.SQLException

Copies the result of the SQL query query into an array of integers. This method uses the statement stmt to execute the given query, and assumes that the first column of the result set contains integer values. Each row of the result set then becomes an element of an array of integers which is returned by this method. This method throws an SQLException if the query is not valid. The given statement stmt must not be set up to produce forward-only result sets.

Parameters:

stmt - the statement used to make the query.

query - the SQL query to execute.

Returns:

the first column of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readIntData

public static int[] readIntData(java.sql.Connection connection,
                                java.lang.String query)
                         throws java.sql.SQLException

Copies the result of the SQL query query into an array of integers. This method uses the active connection connection to create a statement, and passes this statement, with the query, to readIntData, which returns an array of integers.

Parameters:

connection - the active connection to the database.

query - the SQL query to execute.

Returns:

the first column of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readIntData

public static int[] readIntData(java.sql.Statement stmt,
                                java.lang.String table,
                                java.lang.String column)
                         throws java.sql.SQLException

Returns the values of the column column of the table table. This method is equivalent to readIntData (stmt, "SELECT column FROM table").

Throws:

java.sql.SQLException

readIntData

public static int[] readIntData(java.sql.Connection connection,
                                java.lang.String table,
                                java.lang.String column)
                         throws java.sql.SQLException

Returns the values of the column column of the table table. This method is equivalent to readIntData (connection, "SELECT column FROM table").

Throws:

java.sql.SQLException

readObjectData

public static java.lang.Object[] readObjectData(java.sql.Statement stmt,
                                                java.lang.String query)
                                         throws java.sql.SQLException

Copies the result of the SQL query query into an array of objects. This method uses the statement stmt to execute the given query, and extracts values from the first column of the obtained result set by using the getObject method. Each row of the result set then becomes an element of an array of objects which is returned by this method. The type of the objects in the array depends on the column type of the result set, which depends on the database and query. This method throws an SQLException if the query is not valid. The given statement stmt must not be set up to produce forward-only result sets.

Parameters:

stmt - the statement used to make the query.

query - the SQL query to execute.

Returns:

the first column of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readObjectData

public static java.lang.Object[] readObjectData(java.sql.Connection connection,
                                                java.lang.String query)
                                         throws java.sql.SQLException

Copies the result of the SQL query query into an array of objects. This method uses the active connection connection to create a statement, and passes this statement, with the query, to readObjectData, which returns an array of integers.

Parameters:

connection - the active connection to the database.

query - the SQL query to execute.

Returns:

the first column of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readObjectData

public static java.lang.Object[] readObjectData(java.sql.Statement stmt,
                                                java.lang.String table,
                                                java.lang.String column)
                                         throws java.sql.SQLException

Returns the values of the column column of the table table. This method is equivalent to readObjectData (stmt, "SELECT column FROM table").

Throws:

java.sql.SQLException

readObjectData

public static java.lang.Object[] readObjectData(java.sql.Connection connection,
                                                java.lang.String table,
                                                java.lang.String column)
                                         throws java.sql.SQLException

Returns the values of the column column of the table table. This method is equivalent to readObjectData (connection, "SELECT column FROM table").

Throws:

java.sql.SQLException

readDoubleData2D

public static double[][] readDoubleData2D(java.sql.Statement stmt,
                                          java.lang.String query)
                                   throws java.sql.SQLException

Copies the result of the SQL query query into a rectangular 2D array of double-precision values. This method uses the statement stmt to execute the given query, and assumes that the columns of the result set contain double-precision values. Each row of the result set then becomes a row of a 2D array of double-precision values which is returned by this method. This method throws an SQLException if the query is not valid. The given statement stmt must not be set up to produce forward-only result sets.

Parameters:

stmt - the statement used to make the query.

query - the SQL query to execute.

Returns:

the columns of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readDoubleData2D

public static double[][] readDoubleData2D(java.sql.Connection connection,
                                          java.lang.String query)
                                   throws java.sql.SQLException

Copies the result of the SQL query query into a rectangular 2D array of double-precision values. This method uses the active connection connection to create a statement, and passes this statement, with the query, to readDoubleData2D, which returns a 2D array of double-precision values.

Parameters:

connection - the active connection to the database.

query - the SQL query to execute.

Returns:

the columns of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readDoubleData2DTable

public static double[][] readDoubleData2DTable(java.sql.Statement stmt,
                                               java.lang.String table)
                                        throws java.sql.SQLException

Returns the values of the columns of the table table. This method is equivalent to readDoubleData2D (stmt, "SELECT * FROM table").

Throws:

java.sql.SQLException

readDoubleData2DTable

public static double[][] readDoubleData2DTable(java.sql.Connection connection,
                                               java.lang.String table)
                                        throws java.sql.SQLException

Returns the values of the columns of the table table. This method is equivalent to readDoubleData2D (connection, "SELECT * FROM table").

Throws:

java.sql.SQLException

readIntData2D

public static int[][] readIntData2D(java.sql.Statement stmt,
                                    java.lang.String query)
                             throws java.sql.SQLException

Copies the result of the SQL query query into a rectangular 2D array of integers. This method uses the statement stmt to execute the given query, and assumes that the columns of the result set contain integers. Each row of the result set then becomes a row of a 2D array of integers which is returned by this method. This method throws an SQLException if the query is not valid. The given statement stmt must not be set up to produce forward-only result sets.

Parameters:

stmt - the statement used to make the query.

query - the SQL query to execute.

Returns:

the columns of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readIntData2D

public static int[][] readIntData2D(java.sql.Connection connection,
                                    java.lang.String query)
                             throws java.sql.SQLException

Copies the result of the SQL query query into a rectangular 2D array of integers. This method uses the active connection connection to create a statement, and passes this statement, with the query, to readIntData2D, which returns a 2D array of integers.

Parameters:

connection - the active connection to the database.

query - the SQL query to execute.

Returns:

the columns of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readIntData2DTable

public static int[][] readIntData2DTable(java.sql.Statement stmt,
                                         java.lang.String table)
                                  throws java.sql.SQLException

Returns the values of the columns of the table table. This method is equivalent to readIntData2D (stmt, "SELECT * FROM table").

Throws:

java.sql.SQLException

readIntData2DTable

public static int[][] readIntData2DTable(java.sql.Connection connection,
                                         java.lang.String table)
                                  throws java.sql.SQLException

Returns the values of the columns of the table table. This method is equivalent to readIntData2D (connection, "SELECT * FROM table").

Throws:

java.sql.SQLException

readObjectData2D

public static java.lang.Object[][] readObjectData2D(java.sql.Statement stmt,
                                                    java.lang.String query)
                                             throws java.sql.SQLException

Copies the result of the SQL query query into a rectangular 2D array of objects. This method uses the statement stmt to execute the given query, and extracts values from the obtained result set by using the getObject method. Each row of the result set then becomes a row of a 2D array of objects which is returned by this method. The type of the objects in the 2D array depends on the column types of the result set, which depend on the database and query. This method throws an SQLException if the query is not valid. The given statement stmt must not be set up to produce forward-only result sets.

Parameters:

stmt - the statement used to make the query.

query - the SQL query to execute.

Returns:

the columns of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readObjectData2D

public static java.lang.Object[][] readObjectData2D(java.sql.Connection connection,
                                                    java.lang.String query)
                                             throws java.sql.SQLException

Copies the result of the SQL query query into a rectangular 2D array of integers. This method uses the active connection connection to create a statement, and passes this statement, with the query, to readObjectData2D, which returns a 2D array of integers.

Parameters:

connection - the active connection to the database.

query - the SQL query to execute.

Returns:

the columns of the result set.

Throws:

java.sql.SQLException - if the query is not valid.

readObjectData2DTable

public static java.lang.Object[][] readObjectData2DTable(java.sql.Statement stmt,
                                                         java.lang.String table)
                                                  throws java.sql.SQLException

Returns the values of the columns of the table table. This method is equivalent to readObjectData2D (stmt, "SELECT * FROM table").

Throws:

java.sql.SQLException

readObjectData2DTable

public static java.lang.Object[][] readObjectData2DTable(java.sql.Connection connection,
                                                         java.lang.String table)
                                                  throws java.sql.SQLException

Returns the values of the columns of the table table. This method is equivalent to readObjectData2D (connection, "SELECT * FROM table").

Throws:

java.sql.SQLException