[Tutorial] JDBC: Java DataBase Connectivity -- The Basics

[rizzix]

Through the JDBC API a Java program may interface with a database using SQL (Structured Query Language). Practically all of the JDBC API is contained within the java.sql package. Knowledge on the design and framework of JDBC is essential for successfully writing a working database-connected application. It is also essential to know a little on the SQL language.

The JDBC framework is a modular framework that relies on JDBC driver plugins for its connectivity to various RDBMS (Relational Database Management Systems). This modularity results in the generic consisitancy within the API, i.e one would not required to call different methods for different RDBMS. The vendors of their respective RDBMS usually provide the plugin for database connectivty. You may attain further information for your RDBMS from your vendor site. In this tutorial we will be using the popular MySQL RDBMS.


An essential bit to creating a JDBC program is choosing the right plugin or driver. Yes, there's usually more than one kind or category of driver that can get us connected to a RDBMS: Type I to Type IV drivers.

Type I: Establishes connections through an ODBC Data Source.
This kind of driver connects through the OS's ODBC Data Source. In windows we can get access to this source through the Control Panel. The data source basically associates a name with a database. Most VB database programmers and Cold Fusion programmers are fimiliar with this approach. In Java, we can utilise this means of database communication through the JDBC-ODBC bridge driver plugins that is default to all JDBC implementations. Its only advantage is that it can access databases that are but only accessable through the ODBC data source such as MS Access. If you are aware of another means of access to your RDBMS please refrain from using this one.

Type II: Establishes connections through native client-side network code.
This kind of driver provides much better performance than most other drivers. It utilises a mixture of native and Java code, thus it is not a 100% pure java solution. It's draw back is portability and for the slight increase in performance it's not worth using it at all. But sometimes, especially when developing real-time applications this might be your best solution.

Type III: Establishes connections through a Middleware component.
A 100% pure Java driver, that sends requests to a middleware server component. These requests sent, are through a protocol not specific to any perticular RDBMS system. The middleware is responsible for converting these requests to the required specific format before forwarding it to the respective RDBMS. The disadvantage to this approach is that it requires a middleware component. Yet is provides a lot of flexibility, letting us change the RDBMS being used without affecting the client code.

Type IV: Establishes connections directly to the RDBMS.
Also a 100% pure Java driver. It directly communicates with the respective RDBMS. No other necessary software is required. It is easy to distribute as it can be packaged within the java application itself. Most importantly it is fast! The MySQL plugin falls under this category of JDBC drivers.


The fist thing we need to do before any databse connectivity is download the MySQL plugin: here. To make this plugin globally visible (i.e not just within individual projects) drop it in the ext folder within the JRE/lib folder. Mac OS X users should drop it in the /Library/Java/Extensions folder instead.


So lets get started...

The first thing we must write in any database program, is the code to activate the plugin. The mechanism is simple. These plugins get activated statically, i.e we don't need to hold an instance of it ever. There are several ways to statically activate a class, but this is the most common:

Java:

Class.forName("com.mysql.jdbc.Driver");

Here, "com.mysql.jdbc.Driver" is the name of the MySQL Driver class. Other ways could include creating a dummy object of that class and so on.

So you might be wondering, what does it mean to statically activate a class. Well, just as you know how objects of a class can be initialised through object constructors, classes also have a means of initializing themselves. Unlike object initialization, classes cannot be passed a value. Class initialization is also known as static initialization. To perform a static initialization simply put code within a static {} block in a class:

Java:

class Test {     static {         // initialze static stuff here     } }

When the class is first loaded the static {} is called and the initialization takes place. If there's no static {} block present, then it is simply not called. Using this mechanism the JDBC driver gets itself registered with the DriverManager.

Before we progress any further, the classes we would need to import for JDBC are in the java.sql package:

Java:

import java.sql.*;

The basic process of database contectivity is as follows: first register the plugin by loading the class (we discussed this already). Then we establish a connection to the database. After that, create an SQL Statement. Execute the statement. Then we may retrive queried results as a ResultSet and extract data from it etc.

A connection to a database is described using a cryptic URL with the following syntax: "jdbc:sub-protocol:sub-name".

The sub-protocol tends to differ from RDBMS to RDBMS. In the case of MySQL it is simply mysql. The sub-name is the part of the URL that differs from sub-protocol to sub-protocol. In the case of mysql it is "//hostname/database-name?key=value&key=value...", where hostname is the domain name or IP address of the MySQL host and database-name is the name of the MySQL database. The optional "key=value&.." string are the additional properties that you may send to the MySQL driver.

For a database named "test", hosted on "localhost", the URL would look as follows: "jdbc:mysql://localhost/test".

We establish the connection by passing the URL and the respective username & password to the DriverManager's getConnection method. It will return a Connection object that represents an active connection to the database.

Java:

Connection dbConn = DriverManager.getConnection("jdbc:mysql://localhost/test", "testuser", "testpass");

We can now perform any SQL operation on the test database through this connection.

For the rest of the tutorial lets assume the test database is populated with data entries and has the following table and structure:

SQL:

CREATE TABLE Names (     id         int NOT NULL PRIMARY KEY AUTO_INCREMENT,     first_name varchar(50),     last_name  varchar(50),     nickname   varchar(50) );

Querying the database...

SQL statements are sent to the RDBMS through a Statement object. We execute our SQL messages through the Statement object. A Statement object is obtained like this:

Java:

Statement stmt = dbConn.createStatement();

To query a database we call the executeQuery(String sqlQuery); method of the statement object. It will return a ResultSet that contains the data that matches the given query.

Java:

ResultSet rset = stmt.executeQuery("SELECT firstname, lastname FROM Names");

We can interate through the data in the ResultSet like this:

Java:

while (rs.next()) {     String firstName = rset.getString("first_name");     String lastName = rset.getString("last_name"); }

The boolean next(); method interates through each record in the ResultSet. The String getSting(String columnName); returns a varchar value of a record under the give column name. You can also pass it an int column index instead of the String column name. The columns are indexed starting with 1 for the first column in the query. So in our case it would be 1 for the column first_name and 2 for last_name. In general, retriving data using the column index, will be more efficient.


Updating the database...

There are a couple of ways provided for updating the database through JDBC. The most common way is calling the int executeUpdate(String sql); method of the statement object. This method returns the number of rows updated. For example, to change the nickname of records with firstname "rizzix" to "riz":

Java:

int rowsUpdated = stmt.excuteUpdate("UPDATE Names set nickname = 'riz' WHERE first_name = 'rizzix'");

Where Names is the name of the table we are updating and nickname is the column we are actually modifying. As you can see we used a WHERE clause to specify a condition. In this case it translates to: where the data in "first_name" equals 'rizzix'. Now all records that have 'rizzix' as a value in the "first_name" column, will have its nickname value changed to 'riz'. The int rowsUpdated variable will hold the number of records that actually matched the criteria and were update.

The other way to update the database invovles first querying the database, as we did above, and then updating the queried results. But we will get back to it later.


Inserting, Deleting, etc..

The updateQuery method isin't actually associated with just the UPDATE sql statement. It actually is a command that lets us execute any Data Manipulation Language (DML) statements which include UPDATE, INSERT and DELETE, as well as Data Definition Language (DDL) statements such as CREATE TABLE, CREATE VIEW, etc. Thus we can use the same method for inserting new records and delete existing ones. As expected, the method will return the number of rows that get affected by our sql statement.

Example: Inserting a new record.

Java:

stmt.executeUpdate(     "INSERT INTO Names (first_name, last_name, nickname) "+     "VALUES ('Hacker', 'Dan', 'dan')" );

Example: Deleting records.

Java:

int rowsDeleted = stmt.executeUpdate("DELETE FROM Names WHERE nickname = 'riz'");

These basics should help you out in most occassions, but there's a lot more to come...