Business

Java Database Connectivity revisited

In this week's Java tech tip, Lee Chuk-Munn looks at JDBC which has been around since JDK 1.1.

Written by Lee Chuk-Munn, Contributor Aug. 31, 2006 at 9:25 p.m. PT

Java Database Connectivity, or JDBC as it is more affectionately known, has been around since JDK 1.1. As you probably know, JDBC is a set of Java API used by Java applications to perform CRUD (Create/Read/Update/Delete) operations on databases.

JDBC is an API that is extremely easy to use; you load the JDBC driver, create a connection to the database via the loaded driver.

Using the Connection object, you now use a Statement to perform select or updates on the data. Selects and updates are performed by passing SQL statements to the appropriate method.

These SQL statements are executed on the database. Results are returned either in a ResultSet object or as an integer. In the former, you then access by indexing into the field of a record; while in the latter, the integer represents the number of records affected in say a SQL DELETE. You can learn about JDBC from the Java Tutorials trials.

However, large and badly designed JDBC applications can be extremely difficult to maintain. The reason is because when you change your underlying database you have to go through your code base to hunt down all the SQL statements that may be incompatible with the new database.

Another problem is when you change your schema. To overcome these problems, developers are advised to build a data model and hide these SQL statements behind well-known interfaces. In this way, changes only affect the few data model classes.

JDBC 4.0
One of the big changes in JDBC 4.0 is that it encourages, or rather enforces, data abstraction early in the development cycle. Let's look at an example of this. Assume we have a database with the following two tables; this one-to-many relationship is where a person can hold multiple accounts.

create table person(id char(10) primary key not null, name varchar(50))
create table accounts(id char(10) not null, site varchar(15), login varchar(40), password varchar(40))

Create a Value Object
A value object is a Java class that is used to hold results. Each value object represents a row. Here is the value object for the person table:

public class Person {
    public String id;
    public String name;
    public String toString() {
        return ("id=" + id + ", name=" + name);
    }
}

Firstly, note that the fields are all public and that the name matches the name of the column name. I will leave the writing of accounts table value object as an exercise for the reader.

Create a Query Interface
A query interface is a Java interface where we put our queries. Queries are expressed as Java methods; think of these methods as business methods ala EntityBean. Queries are marked with either a @Select or @Update annotation. Here is an example of a query interface:

public interface IdentityQuery extends BaseQuery {
    @Select(sql="select * from person")
      public DataSet<Person> getPersons();

    @Select(sql="select * from accounts where id = ?1")
      public DataSet<Account> getAccounts(String id);

    @Update(sql="insert into accounts values(?1, ?2, ?3, ?4)")
      public int addAccount(String id, String site, String login, String password);
}

Here are some rules you must observe:

All query interfaces must extend the BaseQuery class from java.sql package.
JDBC queries are marked with the appropriate annotation. The SQL statement to be executed is specified with the 'sql=' parameter. In the case of getPersons() method, the "select * from person" is executed when getPersons() is invoked.
The result from Select statements is returned in DataSets. You must define the DataSet with the appropriate value object. For instance, in the getPersons() method, the DataSet will be created with Person value object.
The method's formal parameters can be mapped to the SQL statements with a question mark (?) followed by an integer. A '?1' for instance is mapped to the parameter 'id' in the addAccount() method in the example above.

Using the Query Interface
Finally, we come to using our query interface. The example below shows how this is done. First we create a JDBC connection to the database. In my example below, I'm using the Derby database, which is bundled with JDK 6 since build 88. You can get more information about Derby here.

Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/identdb", "user1", "user1");

IdentityQuery query = QueryObjectFactory.createQueryObject(IdentityQuery.class, conn);

DataSet<Person> all = query.getPersons();
    for(Person p: all) {
        System.out.println(p);
        DataSet<Account> accts = query.getAccounts();
        for (Account a: accts)
            System.out.println("\t&" + a);
            accts.close();
        }

all.close();
conn.close();

Create a query interface object with the createQueryObject(). Pass the connection object and our query interface class as parameters into this method. createQueryObject() will then return an implementation of the interface bound to the JDBC connection. We can now use this query interface to query our data by invoking the methods.

There are a lot of other improvements. You can find out about them here.

Lee Chuk-Munn has been programming in the Java language since 1996, when he first joined Sun Microsystems in Hong Kong. He currently works as a senior developer consultant and technology evangelist for Technology Outreach at Sun in Singapore. Chuk's focus is in Java APIs, Java EE, Java SE, and Java ME. Chuk graduated in 1987 from the Royal Melbourne Institute of Technology in Melbourne, Australia, where his favorite subject was compiler theory.

Editorial standards

Show Comments

Java Database Connectivity revisited

Related

7 reasons I use Copilot instead of ChatGPT

Facebook's Meta AI is lying when it says you can disable it - but here's what you can do

The work laptop I recommend to most people is not made by Apple or Lenovo