JDBC Database Connections approach 2

Approach (II) - DriverManager.registerDriver():

The second approach you can use to register a driver is to use the static DriverManager.registerDriver() method.

You should use the registerDriver() method if you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

The following example uses registerDriver() to register the Oracle driver:

try {

   Driver myDriver = new oracle.jdbc.driver.OracleDriver();

   DriverManager.registerDriver( myDriver );

}

catch(ClassNotFoundException ex) {

   System.out.println("Error: unable to load driver class!");

   System.exit(1);

}

Database URL Formulation:

After you've loaded the driver, you can establish a connection using the DriverManager.getConnection() method. For easy reference, let me list the three overloaded DriverManager.getConnection() methods:

·         getConnection(String url)

·         getConnection(String url, Properties prop)

·         getConnection(String url, String user, String password)

Here each form requires a database URL. A database URL is an address that points to your database.

Formulating a database URL is where most of the problems associated with establishing a connection occur.

Following table lists down popular JDBC driver names and database URL.

RDBMS	JDBC driver name	URL format
MySQL	com.mysql.jdbc.Driver	jdbc:mysql://hostname/ databaseName
ORACLE	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:@hostname:port Number:databaseName
DB2	COM.ibm.db2.jdbc.net.DB2Driver	jdbc:db2:hostname:port Number/databaseName
Sybase	com.sybase.jdbc.SybDriver	jdbc:sybase:Tds:hostname: port Number/databaseName

All the highlighted part in URL format is static and you need to change only remaining part as per your database setup.

Create Connection Object:

Using a database URL with a username and password:

I listed down three forms of DriverManager.getConnection() method to create a connection object. The most commonly used form of getConnection() requires you to pass a database URL, a username, and a password:

Assuming you are using Oracle's thin driver, you'll specify a host:port:databaseName value for the database portion of the URL.

If you have a host at TCP/IP address 192.0.0.1 with a host name of amrood, and your Oracle listener is configured to listen on port 1521, and your database name is EMP, then complete database URL would then be:

jdbc:oracle:thin:@amrood:1521:EMP

Now you have to call getConnection() method with appropriate username and password to get a Connection object as follows:

String URL = "jdbc:oracle:thin:@amrood:1521:EMP";

String USER = "username";

String PASS = "password"

Connection conn = DriverManager.getConnection(URL, USER, PASS);

Using only a database URL:

A second form of the DriverManager.getConnection( ) method requires only a database URL:

DriverManager.getConnection(String url);

However, in this case, the database URL includes the username and password and has the following general form:

jdbc:oracle:driver:username/password@database

So the above connection can be created as follows:

String URL = "jdbc:oracle:thin:username/password@amrood:1521:EMP";

Connection conn = DriverManager.getConnection(URL);

Using a database URL and a Properties object:

A third form of the DriverManager.getConnection( ) method requires a database URL and a Properties object:

DriverManager.getConnection(String url, Properties info);

A Properties object holds a set of keyword-value pairs. It's used to pass driver properties to the driver during a call to the getConnection() method.

To make the same connection made by the previous examples, use the following code:

import java.util.*;

String URL = "jdbc:oracle:thin:@amrood:1521:EMP";

Properties info = new Properties( );

info.put( "user", "username" );

info.put( "password", "password" );

Connection conn = DriverManager.getConnection(URL, info);

Closing JDBC connections:

At the end of your JDBC program, it is required explicitly close all the connections to the database to end each database session. However, if you forget, Java's garbage collector will close the connection when it cleans up stale objects.

Relying on garbage collection, especially in database programming, is very poor programming practice. You should make a habit of always closing the connection with the close() method associated with connection object.

To ensure that a connection is closed, you could provide a finally block in your code. A finally block always executes, regardless if an exception occurs or not.

To close above opened connection you should call close() method as follows:

conn.close();

Explicitly closing a connection conserves DBMS resources, which will make your database administrator happy.

For a better understanding, I would suggest to study our JDBC - Sample Code.

JDBC - Statements, PreparedStatement and CallableStatement

Once a connection is obtained we can interact with the database. The JDBC Statement, CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.

They also define methods that help bridge data type differences between Java and SQL data types used in a database.

Following table provides a summary of each interface's purpose to understand how do you decide which interface to use?

Interfaces	Recommended Use
Statement	Use for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.
PreparedStatement	Use when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime.
CallableStatement	Use when you want to access database stored procedures. The CallableStatement interface can also accept runtime input parameters.