How to Working with JDBC

Java database connectivity is essential for building stand-alone database applications and also for developing dynamic Java-based web applications. Before working with JDBC, it is required to install Java, a JDBC driver and the DBMS on the computer systems. Usually, the JDBC—ODBC bridge driver is bundled with the JDK.

Accessing a database using JDBC

The JDBC API provided by Sun Microsystems is designed to allow access to third-party database products. It causes the applications to be platform-independent and also allows making vendor-specific calls to take advantage of different features of database products. To be able to exploit this feature vendors must provide classes for using their database. The JDBC API also allows the programmer to access databases that reside on local as well as remote systems.

Registering the Driver

To start the process of accessing a database, first load the Driver class into the JVM for a specified JDBC driver, which allows the program to communicate with the database. Loading of JDBC Driver class registers itself with the DriverManager. The method Class.forName () is used for this purpose.

The following statements describe the way of loading drivers of the MS SQL server type

(type 4), Oracle thin driver type (type 4) and the IDBC-ODBC bridge driver type (type I),

respectively.

• Class.forName (“com.microsoft.jdbc.sqlserver.SQLServerDriver”);

• Class.forName (“oracle.jdbc.driver.OracleDriver”);

• Class.forName (“sun.jdbc.odbc.jdbcOdbcDriver”);

Before loading the Driver class, classpath is to be set equal to the driver classes. If this is not done, then the Class.forName () method throws the exception ClassNotFoundException.

Consider the example of setting the class path for an Oracle thin driver:

Set classpath=%classpath%;<location-of-oracle-driver>/classes12.jar ;

location-of-oracle-driver will specify the path of the Oracle driver class. This class had one static block. Once the class is loaded, this static block is executed for the purpose of registering the driver with the DriverManager class. This DriverManager class internally uses the Driver class.

Connecting to the Database

Once the driver is loaded, we can get the connection to the database using the static get connection () method of the DriverManager class.

Connection dbcon = DriverManager.getConnection (url, userid, password);

The getConnection () method has three parameters: url, userid and password. The parameters userid and password represent the user name and password that are used to access users’ database. DriverManager matches the parameter url with the registered driver classes and returns the Connection object related to that URL.This Connection object is used to create the JDBC statement that passes the SQL statement to the database management system.

The location of a database is specified using an URL of the following form:

<Protocol>:<subprotocol>:<subname>

Here, we are using the IDBC so protocol is normally jdbc. Subprotocol specifies the database we are using and the version of the database and subname is the name of the data source, which identifies the location of the database.

In the following example, the specification to be following when using the JDBC-ODBC bridge driver (that uses the ODBC) to access the database is given:

jdbc:odbc:dsn

Here, dsn is the data source name for any database (SQL server, Oracle, MS-access) system.

Note that subname can take a slightly different form if the database is on a remote system, it includes the machine, port number and database location on the remote system.

Connecting to a SQL server database with a type-four SQL driver

The statements to be used in this case are the following:

Class.forName (“com.microsoft.jdbc.sqlserver.SQLServerDriver”); //registering driver

String url = “jdbc:microsoft:sqlserver://”+host+”:1433″;

Connection dbcon = DriverManager.getConnection(url, userid, password);

Here, the host specifies the name of the system where the database server is located. The actual name of the host has to be substituted in place of the word host.

Connecting to Oracle server using Oracle thin driver

The statements to be used in this case are the following:

Class.forName (“oracle.jdbc.driver.OracieDriver”);

String url = “jdbc:oracle:thin:@” +host+”:1521 :”+dsource;

Connection dbcon= DriverManager.getConnection (url, userid, password);

The term dsource specifies the service name of the oracle.

Connecting to any database using the bridge driver

The statements used in this case are the following:

Class.forName (“sun.jdbc.odbc.JdbcOdbcDriver”);

url = “jdbc:odbc:dsn”;

Connection dbcon=DriverManager.getConnection (url);

Here, dsn is the data source name of the database that we want to connect to. The actual name of the dsource and dsn have to be given here in a real program.

The object returned by getConnection () is a Connection object for the database. We use this connection object to submit any SQL queries or updates to the database.

Some of the methods in the DriverManager class are given in Table

Table Some important methods in the DriverManager class.