Learn how to work with Java Database Connectivity

Assume that ABC Ltd is an automobile sales part manufacturing company that has many branches across the world. The corporate office at New York maintains MSAccess database for the sales details of various products, stock with each branch, personnel details etc.

The New Delhi office wants to establish connection with this remote database via Java Applet and sends in the above data to the corporate office. The solution for the above issues lies with JDBC or Java Database Connectivity. Before proceeding further, let us take a quick view regarding Microsoft’s ODBC and the preference of JDBC over ODBC.

Microsoft ODBC API offers connectivity to almost all databases on almost all platforms and is the most widely used Programming interface for accessing relational databases. But ODBC cannot be used directly with Java Programs due to various reasons described below.

(1) ODBC Cannot be used directly with Java because it uses a C interface. This will have drawbacks in the Security, implementation, robustness.
(2) ODBC makes use of Pointers which have been removed from Java.
Hence, JDBC came into existence. If you had done Database Programming with Visual Basic, then you will be familiar with ODBC. You can connect a VB Application to MSAccess Database or an Oracle Table directly via ODBC. Since Java is a product of Sun Microsystems, you have to make use of JDBC with ODBC in order to develop Java Database Applications.

Steps required to implement a Java Database Program

JDBC is a set of Java API for executing SQL Statements. This API Consists of a set of classes and interfaces to enable programmers to write pure Database Applications. It is possible to access various relational databases like Sybase, Oracle, Informix using JDBC.

READ  Internet Explorer November 2014 Updates Released

STEP 1: Loading Drivers

First you have to load the appropriate driver. You can use one driver from the available four drivers. However, JDBC-ODBC Driver is the most preferred driver among developers. In order to load the driver, you have to give the following syntax:

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">Class.ForName(&quot;sun.jdbc.odbc.JdbcOdbcDriver&quot;) 

STEP 2: Making the Connection

The getConnection() method of the Driver Manager class is called to obtain the Connection Object. The syntax looks like this:

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">Connection conn = DriverManager.getConnection(&quot;jdbc:odbc:<span style="color: #0000ff">&lt;</span><span style="color: #800000">DSN</span> <span style="color: #ff0000">NAME</span><span style="color: #0000ff">&gt;</span>&quot;); 

Here note that getConnection() is a static method, meaning it should be accessed along with the class associated with the method. The DSN Name is the name which you gave in the Control Panel while registering the Database or Table.

STEP 3: Creating JDBC Statements

A Statement object is what send your SQL Query to the Database Management System. You simply create a statement object and then execute it. It takes an instance of active connection to create a statement object. We have to use our earlier created Connection Object "conn" here to create the Statement object "stmt". The code looks like this:

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">Statement stmt = conn.createStatement(); 

STEP 4: Executing the Statement

In order to execute the query, you have to obtain the Result Set object similar to Record Set in Visual Basic and call the executeQuery() method of the Statement interface. You have to pass a SQL Query like select * from students as a parameter to the executeQuery() method. Actually, the RecordSet object contains both the data returned by the query and the methods for data retrieval. The code for the above step looks like this:

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">ResultSet rs = stmt.executeQuery(&quot;select * from student&quot;); 

If you want to select only the name field you have to issue a SQL Syntax like

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">Select Name from Student 

The executeUpdate() method is called whenever there is a delete or an update operation.

READ  Understanding Data Types and Variables in C#

STEP 5: Looping through the ResultSet

The ResultSet object contains rows of data that is parsed using the next() method like rs.next(). We use the getXXX method of the appropriate type to retrieve the value in each field.

If the first field in each row of ResultSet is Name (Stores String value), then getString method is used. Similarly, if the Second field in each row stores int type, then getInt() method is used like:

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">System.out.println(rs.getInt(&quot;ID&quot;)); 

STEP 6: Closing the Connection and Statement Objects

After performing all the above steps, you have to close the Connection and RecordSet Objects appropriately by calling the close() method. For example, in our above code we will close the object as

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">conn.close()

and statement object as

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">stmt.close()

Leave a Reply

Your email address will not be published.