JDBC - Environment Setup

To start developing with JDBC setup your JDBC environment by following the steps shown below. We assume that you are working on a Windows platform

Install Java:
Install J2SE Development Kit 5.0 (JDK 5.0)
Make sure following environment variables are set as described below:
  • JAVA_HOME: This environment variable should point to the directory where you installed the JDK, e.g. C:\Program Files\Java\jdk1.5.0
  • CLASSPATH: This environment variable should has appropriate paths set, e.g. C:\Program Files\Java\jdk1.5.0_20\jre\lib
  • PATH: This environment variable should point to appropriate JRE bin, e.g. C:\Program Files\Java\jre1.5.0_20\bin.
    • It is possible you have these variable set already, but just to make sure here's how to check.
  • Go to the control panel and double-click on System. If you are a Windows XP user it's possible you have to open Performance and Maintenance before you will see the System icon.
  • Go to the Advanced tab and click on Environment Variables.
  • Now check all the above mentioned variables are set properly.
You automatically get both JDBC packages java.sql and javax.sql when you install J2SE Development Kit 5.0 (JDK 5.0)

Install Database:

The most important thing you will need, of course is an actual running database with a table that you can query and modify.
Install a database that is most suitable for you. You can have plenty of choices and most common are:

1. MySQL DB: MySQL is an open source database. You can download it from MySQL Official Site. We recommend downloading the full Windows installation. In addition, download and install MySQL Administrator as well as MySQL Query Browser. These are GUI based tools that will make your development much easier. Finally, download and unzip MySQL Connector/J (the MySQL JDBC driver) in a convenient directory. For the purpose of this tutorial we will assume that you have installed the driver at C:\Program Files\MySQL\mysql-connector-java-5.1.8. Accordingly set CLASSPATH variable to C:\Program Files\MySQL\mysql-connector-java-5.1.8\mysql-connector-java-5.1.8-bin.jar. Your driver version may vary based on your installation.

2. PostgreSQL DB: PostgreSQL is an open source database. You can download it from PostgreSQL Official Site. The Postgres installation contains a GUI based administrative tool called pgAdmin III. JDBC drivers are also included as part of the installation.

3. Oracle DB: Oracle DB is an commercial database sold by Oracle . We assume that you have the necessary distribution media to install it. Oracle installation includes a GUI based administrative tool called Enterprise Manager. JDBC drivers are also included as part of the installation

Install Database Drivers:

The latest JDK includes a JDBC-ODBC Bridge driver that makes most Open Database Connectivity (ODBC) drivers available to programmers using the JDBC API. Now a days most of the Database vendors are supplying appropriate JDBC drivers along with Database installation. So you should not worry about this part.

Set Database Credential:

For this tutorial we are going to use MySQL database. When you install any of the above database, its administrator ID is set to root and gives provision to set a password of your choice.

Using root ID and password you can either create another users ID and password or you can use root ID and password for your JDBC application.

There are various database operations like database creation and deletion, which would need administrator ID and password.

For rest of the JDBC tutorial we would use MySQL Database with username as ID and password as password.

If you do not have sufficient privilege to create new users then you can ask your Database Administrator (DBA) to create a user ID and password for you.

JDBC - Sample, Example Code

This tutorial provides an example of how to create a simple JDBC application. This will show you how to open a database connection, execute a SQL query, and display the results.

All the steps mentioned in this template example, would be explained in subsequent chapters of this tutorial.

Creating JDBC Application:

There are following six steps involved in building a JDBC application:
  • 1. Import the packages . Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
  • 2. Register the JDBC driver . Requires that you initialize a driver so you can open a communications channel with the database.
  • 3. Open a connection . Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.
  • 4. Execute a query . Requires using an object of type Statement for building and submitting an SQL statement to the database.
  • 5. Extract data from result set . Requires that you use the appropriate ResultSet.getXXX() method to retrieve the data from the result set.
  • 6. Clean up the environment . Requires explicitly closing all database resources versus relying on the JVM's garbage collection.