Making Connections

Making Connections

Overview

A Connection object represents a connection with a database.
To get information about a database, invoke Connection.getMetaData, which returns a DatabaseMetaData object.
One way to create a Connection object is to call DriverManager.getConnection with a URL argument representing the location of a driver. For example:
String url = "jdbc:odbc:dbname";
Connection con = DriverManager.getConnection(url, "username", "upassword");
Another way to create a Connection object is to use the DataSource interface, which allows connection pooling.
Apps should use a finally block to close the connection and return it back to the pool of available connections. Call Connection.close and Statement.close to explicitly free the DBMS resource.
If a Connection object is part of a distributed transaction, an application should not call Connection.commit or Connection.rollback, nor should it turn on the connection's auto-commit mode. These would interfere with the transaction manager's handling of the distributed transaction.

JDBC URLs

Here is the standard syntax for JDBC URLs...
    jdbc:drivername:datasourcename
Here is an example of a URL that connects to an Oracle database called dogshow, residing on a machine called blueterrier:
jdbc:oracle:thin:@blueterrier:1521:dogshow

The odbc drivername allows any number of attribute values to be specified after datasourcename. For example
     jdbc:odbc:dbname
     jdbc:odbc:dbname;CacheSize=30;ExtensionCase=lower
     jdbc:odbc:dbname;UID=uid;PWD=password
Developers can reserve a name to be used as the drivername in a JDBC URL. When the DriverManager class presents this name to its list of registered drivers, the driver for which this name is reserved should recognize it and establish a connection to the database it identifies.

SQL Statements

The JDBC API provides three interfaces for sending SQL statements to a database:

Statement Used for sending SQL statements with no parameters. Created by Connection.createStatement methods.

PreparedStatement Used for precompiled SQL statements. Created by Connection.prepareStatement methods.
Can take one or more parameters as input arguments (IN parameters). Has a group of methods that set the value of IN parameters, which are sent to the database when the statement is executed. PreparedStatement objects are used for SQL statements executed many times.

CallableStatement Created by the Connection.prepareCall methods. CallableStatement objects are used to execute SQL stored procedures-a group of SQL statements that is called by name, much like invoking a function. CallableStatement objects inherit method for handling IN parameters from PreparedStatement and adds methods for handling OUT and INOUT parameters.

Transactions

A transaction consists of one or more statements that have been executed, completed, and then either committed or rolled back. When the method commit or rollback is called, the current transaction ends and another one begins.
By default Connection objects are in auto-commit mode, committing transactions after each statement.
If auto-commit mode is disabled, a transaction will not terminate until the method commit or rollback is called explicitly, at which time all all uncommitted statements will either be committed or rolled back.
With distributed transactions a transaction manager determines when to call commit or rollback. Unlike Connection objects produced by the http://www.setgetweb.com/tmp/j2sdk141/jdbc/drivermanager.html">DriverManager, a Connection object produced by such a DataSource object will have its auto-commit mode disabled by default.

Transaction Isolation Levels

To ameliorate situations where two transactions are both trying to operate on the same object in a database at the same time, specify an appropriate transaction isolation level. For example, one can allow a value to be read before it has been committed (a "dirty read"):
con.setTransactionIsolation(TRANSACTION_READ_UNCOMMITTED);

The Connection interface defines five isolation levels. The lowest specifies that transactions are not supported at all. The highest specifies that while one transaction is operating on a database, no other transactions may make any changes to the data read by that transaction.
Because of increased locking overhead, and decreased concurrency, the higher the level of isolation, the slower the application executes.
Transaction isolation levels for new Connection objects are set by the underlying data source. To explicitely set the isolation level, call setTransactionIsolation

Disabling auto-commit
New JDBC connections are initially in "auto-commit" mode. This means that each statement is executed as a separate transaction at the database.
In order to execute several statements within a single transaction, first disable auto- commit by calling Connection.setAutoCommit(false).
When auto-commit is disabled, the connection always has an implicit transaction associated with it. You can execute a Connection.commit to complete the transaction or a Connection.rollback to abort it. The commit or rollback will also start a new implicit transaction.
The exact semantics of transactions and their isolation levels depend on the underlying database. There are methods on java.sql.DatabaseMetaData to learn the current defaults, and on java.sql.Connection to move a newly opened connection to a different isolation level.

Type Maps

Two SQL3 user-defined data types, STRUCT and DISTINCT, can be custom mapped to Java classes using java.util.Map
The following code fragment demonstrates retrieving the type map associated with con and adding a new entry to it. After the type map is modified, it is set as the new type map for con.
java.util.Map map = con.getTypeMap();
map.put("SchemaName.ADDRESSES", Class.forName("Addresses"));
con.setTypeMap();
The Map object map, the type map associated with con, now contains at least one custom mapping (or more if any mappings have already been added). The programmer will have previously created the class Addresses, probably using a tool to generate it. Note that it is an error to supply a class that does not implement the interface SQLData. The class Addresses, which does implement SQLData, will have a field for each attribute in ADDRESSES, and whenever a value of type ADDRESSES is operated on by a method in the Java programming language, the default will be to map it to an instance of the class Addresses. The type map associated with a connection is the default type map in the sense that a method will use it if no other type map is explicitly passed to it.
Note that the name of the UDT should be the fully-qualified name. For some DBMSs, this will be of the form catalogName.schemaName.UDTName. Many DBMSs, however, do not use this form and, for example, use a schema name but no catalog name. The important thing is to use the form appropriate for a particular DBMS. The DatabaseMetaData methods getCatalogs, getCatalogTerm, getCatalogSeparator, getSchemas and getSchemaTerm give information about a DBMS's catalogs, schemas, preferred terms, and the separator it uses.
Instead of modifying the existing type map, an application can replace it with a completely different type map. This is done with the Connection method setTypeMap, as shown in the following code fragment. It creates a new type map, gives it two entries (each with an SQL UDT name and the class to which values of that type should be mapped), and then installs the new type map as the one associated with the Connection con.
java.util.Map newConnectionMap = new java.util.HashTable();
newConnectionMap.put(
     "SchemaName.UDTName1", Class.forName("className1"));
newConnectionMap.put(
     "SchemaName.UDTName2", Class.forName("className2"));
con.setTypeMap(newConnectionMap);
The Map object newConnectionMap now replaces the type map originally associated with the Connection con, and it will be used for custom type mappings unless it is itself replaced. Note that the example uses the default constructor for the class HashTable to create the new type map. This class is one of many implementations of java.util.Map provided in the Java 2 platform API, and one of the others could have been used as well.
In the previous examples, the type map associated with a connection was modified to contain additional mappings or set to be a different type map altogether. In either case, though, the connection's type map is the default for custom mapping JDBC types to types in the Java programming language. The next example will show how to supersede the connection's type map by supplying a method with a different type map.
Methods whose implementations may involve a custom mapping for UDTs have two versions, one that takes a type map and one that does not. If a type map is passed to one of these methods, the given type map will be used instead of the one associated with the connection. For example, the Array methods getArray and getResultSet have versions that take a type map and versions that do not. If a type map is passed to a method, it will map the array elements using the given type map. If no type map is specified, the method will use the type map associated with the connection.
The capability for supplying a type map to a method makes it possible for values of the same user-defined type to have different mappings. For example, if two applications are using the same connection and operating on the same column value, one could use the type map associated with the connection, and the other could use a different type map by supplying it as an argument to the appropriate method.
The following code fragment creates a new type map and provides it as a parameter to the Array method getArray.
java.util.Map arrayMap = new java.util.HashTable();
arrayMap.put("SchemaName.DIMENSIONS", Class.forName("Dimensions"));
Dimensions [] d = (Dimensions [])array.getArray(arrayMap);
In the second line, the new type map arrayMap is given an entry with the fully-qualified name of an SQL structured type (SchemaName.DIMENSIONS) and the Java class object (Class.forName("Dimensions")). This establishes the mapping between the Java type Dimensions and the SQL type DIMENSIONS. In the third line, arrayMap is specified as the type map to use for mapping the contents of this Array object, whose base type is SchemaName.DIMENSIONS.
The method getArray will materialize the elements of the SQL3 ARRAY value designated by array, with each element being mapped according to the mapping specified in arrayMap. In other words, each element, which is a value of type Schema.DIMENSIONS, will be translated to an instance of the class Dimensions by mapping the attributes of each DIMENSIONS value to the fields of a Dimensions object. If the base type of the array does not match the UDT named in arrayMap, the driver will convert the array's elements according to the standard mapping. If no type map is specified to the method getArray, the driver uses the mapping indicated in the connection's type map. If that type map has no entry for Schema.DIMENSIONS, the driver will instead use the standard mapping.

Statement	Used for sending SQL statements with no parameters. Created by Connection.createStatement methods.
PreparedStatement	Used for precompiled SQL statements. Created by Connection.prepareStatement methods. Can take one or more parameters as input arguments (IN parameters). Has a group of methods that set the value of IN parameters, which are sent to the database when the statement is executed. PreparedStatement objects are used for SQL statements executed many times.
CallableStatement	Created by the Connection.prepareCall methods. CallableStatement objects are used to execute SQL stored procedures-a group of SQL statements that is called by name, much like invoking a function. CallableStatement objects inherit method for handling IN parameters from PreparedStatement and adds methods for handling OUT and INOUT parameters.