JavaData & JavaAn Overview of Java Database Connectivity

An Overview of Java Database Connectivity

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

[This article assumes you have a basic understanding of Java application and applet programming.]

Suppose you have a set of records in an Access database that you
have to view through a front-end tool. You can design a user interface
by using various programming languages such as Visual Basic, Visual C++,
etc. Java, however, provides a more consistent approach in developing these
interfaces through the javax.swing package. Moreover, Java provides
the Java Database Connectivity (JDBC) API, with which you can connect your app to any database designed either using Microsoft Access or SQL Server. In this article, we will examine the basic steps required to handle JDBC using javax.swing for creating user interfaces.

Before proceeding further, let us take a quick look at Microsoft’s Object Database Connectivity (ODBC) and the preference of JDBC over ODBC. The 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.

  1. ODBC uses a C interface. This has drawbacks in security, implementation, robustness, etc.
  2. ODBC makes use of pointers (which have been removed from Java).

Hence JDBC came into existence. If you’ve done database programming
using Visual Basic, then you will be familiar with ODBC. You can connect
a VB application to an Access database or an Oracle table directly via
ODBC. Since Java is a product of Sun Microsystems, you have to make use
of JDBC along with ODBC in order to develop Java database applications. JDBC is a set of Java APIs for executing SQL statements. This API consists of a set
of classes and interfaces to enable programmers to write pure database
applications.

Let us now examine the basic steps required in all Java programs to handle JDBC.

Step 1: Loading Drivers

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

Class.ForName("sun.jdbc.odbc.JdbcOdbcDriver");

Step 2: Making a Connection

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

Connection conn = DriverManager.getConnection("jdbc:odbc:<DSN NAME>");

Here, note that getConnection() is a static method, meaning it should
be accessed along with the class associated with the method. You have to
give the Data Source Name as a parameter to this method. (See section below for setting up the Data Source Name in your computer.)

Step 3: Creating JDBC Statements

A Statement object is what sends 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 Connection object “conn” here to create the Statement object "stmt". The code looks like this:

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 a call to 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. If your table name is different, you have to substitute that name in place of students. 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:

ResultSet rs = stmt.executeQuery("select * from students");

If you want to select only the name field, you have to issue a SQL command like Select Name from Student. The executeUpdate() method is called whenever there is a delete or an update operation.

Step 5: Looping Through the ResultSet

The ResultSet object contains rows of data that is parsed using the next() method, such as rs.next(). We use the getXXX() method of the appropriate type to retrieve the value in each field. For example, if your first field name is ID, which accepts Number values, then the getInt() method should be used. In the same way, if the second field Name accepts integer String values, then the getString() method should be used, like the code given below:

   
System.out.println(rs.getInt("ID"));

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 code above, we will close the object as conn.close()and statement object as stmt.close().

The code for the sample program is shown below for your reference:

        import java.sql.*;
        import java.awt.*;
        import java.awt.event.*;
        import javax.swing.*;
        
        public class Datas extends JFrame implements ActionListener   {
        
	JTextField id;
	JTextField name;
	JButton next;
	JButton addnew;
	JPanel p;
	static ResultSet res;
	static Connection conn;
	static Statement stat;
        
	public Datas()  {
	super("Our Application");
	Container c = getContentPane();
	c.setLayout(new GridLayout(5,1));
	id = new JTextField(20);
	name = new JTextField(20);
	next = new JButton("Next");
	p = new JPanel();
	c.add(new JLabel("Customer ID",JLabel.CENTER));
	c.add(id);
        c.add(new JLabel("Customer Name",JLabel.CENTER));
	c.add(name);
	c.add(p);
	p.add(next);
        next.addActionListener(this);
        pack();
	setVisible(true);
	addWindowListener(new WIN());
        
        }
        
	public static void main(String args[])    {
	Datas d = new Datas();
	try  {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	conn = DriverManager.getConnection("jdbc:odbc:cust"); // cust is the DSN
        Name
	stat = conn.createStatement();
	res = stat.executeQuery("Select * from Cutomers");  // Customers is
        the table name
        res.next();
        
	}
	catch(Exception e)   {
	System.out.println("Error" +e);
	}
	d.showRecord(res);
	}
        
	public void actionPerformed(ActionEvent e)  {
	if(e.getSource() == next)   {
	try  {
	res.next();
	}
	catch(Exception ee) {}
	showRecord(res);
	}
	}
        
	public void showRecord(ResultSet res)   {
	try  {
	id.setText(res.getString(1));
	name.setText(res.getString(2));
	}
	catch(Exception e)  {}
        
        }//end of the main
        
        //Inner class WIN implemented
        class WIN extends WindowAdapter  { 
         public void windowClosing(WindowEvent w)  {
	JOptionPane jop = new JOptionPane();
	jop.showMessageDialog(null,"Database","Thanks",JOptionPane.QUESTION_MESSAGE);

	}
        
        } //end of the class

How to Create a Data Source Name

Follow these steps to create a Data Source Name in Windows

  • Open ODBC 32-bit Icon from the control panel. (Start | Settings)
  • Click on the Add button and select Microsoft Access Driver.
  • Select the appropriate driver if you are using other databases.
  • Enter a name as Data Source Name and browse for your database by clicking the Select button.
  • You can also write a short description, but this is optional.
  • Finally, click on the Finish button.

Installation Instructions for the Sample Program

  1. Copy the program code included with this article. You can also download the same by clicking here (ZIP file). This file contains the required Access database and Java files.
  2. Place these files in a appropriate directory and set up the Data Source Name as described above.
  3. Compile and execute the program by using the command javac and java

Suggested Reading

  1. Java Programming Bible by Aaron Walsh and John Fronckowiak, IDG Books Worldwide, First Reprint (2000)
  2. Java 2 Complete Reference by Patrick Naughton and Herbert Schildt, Third Edition.

About the Author

Anand Narayanaswamyis a graduate of the University of Kerala. He currently works as an instructor teaching Java, Visual Basic, and technologies such as ASP and XML. He is a regular contributor to the Gamelan and Developer.com sites. He enjoys learning new programming languages like C#. Currently, Anand lives in Thiruvananthapuram, Kerala State, India. He can be contacted via his Website.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories