Digital Edition

After The Connection
After The Connection

In this series we've explored the process behind selecting a database and a JDBC driver as well as establishing a connection between your Java application and your database using JDBC. To actually do something useful, however, you need to be able to actively interact with a database using JDBC.

Early on in their history, relational database vendors agreed on a common interpretive language called SQL (Structured Query Language) that could interact with any database that supported this standard. This tactic helped build a large user base and a large number of third-party tools. Relational databases came to supplant the previously popular hierarchical databases as the dominant type of database system.

JDBC technology is built on this standard query language. As a result, to understand the fundamentals of JDBC, you must first understand the basics of SQL. Before delving into SQL, however, we need to cover some of the basic theory of relational database systems.

The two main concepts that underlie relational databases are the idea of a database transaction and the representation of data in a table format. Fundamentally, a transaction represents a logical unit of work with a database. In fact, the basic test for classifying something as a database, the famous ACID test that stands for Atomicity, Consistency, Isolation, and Durability, is defined in terms of transactions. If you wish to save your work, you need to commit your transaction; if you wish to undo your work, you need to roll back your transaction. Data locking, for both read and write operations, is used to prevent users from interfering with each other's transactions.

The data in a relational database is conceptualized to occupy a table (think of a spreadsheet) consisting of rows (records of data) of multiple columns (different types of data that make up the records). An entire theoretical framework has been developed, including such concepts as independent and dependent tables and different normal forms, to prove theorems concerning relational database tables. For our purposes, however, we need to review only four concepts:

  • Primary keys: Used to provide a distinct identity to each row in a table; essentially, they're why you always feel like a number when dealing with a particular bureaucracy (e.g., a call-number or an employee or Social Security number)
  • Joins: Operations used to combine data from two distinct tables
  • Indices: Used to facilitate rapid data retrieval; however, indices can occupy a significant amount of space and are therefore created only for a select number of columns in a table (generally including the primary key)
  • Views: Results of given queries, often considered and treated as new tables

SQL Primer
At its most basic, SQL is used to interact with a relational database system in two different fashions, each with its own name: SQL data definition language (DDL) and SQL data manipulation language (DML). The former is used to create and drop tables from a particular database. The latter is used to select, insert, update, or delete data from a particular table. SQL is, by its very nature, an interpretive language that provides a great deal of flexibility but can also limit performance.

A few caveats are in order when writing or reading SQL. First, SQL commands are case insensitive and can be spread over multiple lines in an interactive SQL editor. An important footnote to this caveat, however, is that table and column names are generally case sensitive (as a point, I'll capitalize all SQL commands and use mixed-case notation for all table and column names). This last issue is one of the biggest stumbling blocks for SQL novices as it's easy to forget. Second, SQL introduces standard data types that include representations for variable-length character arrays, integers, and floating-point numbers (see your database manual for more information). Finally, many interactive SQL tools use a special delimiter (such as the semicolon) to separate multiple SQL commands.

Before doing anything else, we need to create a table to hold our data. If we want to make a table to hold a simple list of contacts, we might use the following SQL statement:

Phone INT,
) ;

This command creates a table named Contacts in the currently connected database. Its five columns are First, Middle, Last, Phone, and Age. The three name columns are variable- length character arrays with an initial size specification of 20 characters. These columns must contain actual data, indicated by the NOT NULL column qualifier. The last two columns are integer data types that are optional since they don't have the NOT NULL qualifier.

The complementary operation to remove a table from the database is considerably simpler:

DROP TABLE Contacts ;

After the necessary tables are created, the next step is to populate them with the relevant data. Many database systems have useful utilities that can automate the bulk inserts of large amounts of data; however, in SQL this is done using the INSERT command:

) ;

Alternatively, we can explicitly specify the order of the columns that the value fields will occupy:

INSERT INTO Contacts (

) ;

Once we have a table filled with the data of interest, we can begin to flex the full power of relational databases. They can use the relations within the data to selectively query, update, and delete data according to specific constraints. Formally, we wish to perform a procedure (including SELECT, UPDATE, DELETE) on a table with a particular group of rows indicated by the appropriate relational operators (see Figure 1). For example, to display the full contents of our Contacts table (assuming we have inserted several entries), we can issue the following SQL command:

SELECT * FROM Contacts ;

While instructive, this example doesn't demonstrate the full power of the SELECT statement. Sometimes you may want to selectively extract certain columns for a subset of all the rows in a table (see Figure 2). For example, to pull out the First, Last, Age, and Phone columns for all rows that have an Age column value greater than 30, we'd use the following SQL command:

SELECT First, Last, Age, Phone
FROM Contacts
WHERE Age > 30 ;

A similar syntax is used for the DELETE and UPDATE SQL commands, which either delete or update specific rows, respectively.

Morphing SQL into JDBC
Anyone serious about learning and using JDBC needs a good reference. I recommend the JDBC API Tutorial and Reference, Second Edition: Universal Data Access for the Java 2 Platform by Seth White et al. (Addison-Wesley), part of the Java Series. To understand why a good reference can be invaluable, recall that SQL has predefined data types, while the Java language has its own predefined data types (one of Java's most important features). To pull data from a database into a Java application using SQL, you have to convert from SQL data types to Java data types and vice versa. The book referenced above devotes an entire chapter to this process, with many informative tables that demonstrate the allowed and recommended conversions.

Before delving into the more commonly used interfaces in the JDBC API, an introduction to the specific error-handling features is appropriate. Any time a database is involved, the whole concept of error handling can quickly become a quagmire. Fortunately, the JDBC API has provided an elegant solution - the SQLException object - which allows for chained exceptions, a novel concept in the Java arena. As a result, the following convention is standard when using the JDBC API:

try {
// JDBC Code
}catch(SQLException e) {
while (e != null) {
System.out.println("\nERROR: \n");
System.out.println("Message: "
+ e.getMessage ());
System.out.println("SQLState: "
+ e.getSQLState ()); System.out.println("ErrorCode: " + e.getErrorCode ());
e = e.getNextException();

Sometimes a database operation can produce a warning condition (encapsulated by the SQLWarning object), which is less severe than an exception but can also be chained. Since these conditions aren't exceptions, they're not handled in the typical try...catch block fashion. Instead, you need to check explicitly to see if a database operation generated a warning and act appropriately (see the previously mentioned JDBC book or the online Java tutorial,, for more information).

Now let's discuss using Java to interact with a database. Once a connection has been established with a database (see JDJ, Vol. 5, issue 10), the next step is to create a Statement object. This object encapsulates the process of passing SQL commands to the database and processing the results. As the code snippet below demonstrates, a Statement object is created from a Connection object, which in effect owns the newly created Statement object.

First we need the SQL command we want to send to the database. In this case it's our earlier table creation command, with the table renamed so we don't try to create a new table with the same name as an existing one (which would throw a SQLException).

String createString =
"CREATE TABLE NewContacts " +
"First VARCHAR(20) NOT NULL, " +
"Middle VARCHAR(20) NOT NULL, " +
"Last VARCHAR(20) NOT NULL, " +
"Phone INT, " +
"Age INT)" ;

Now we can create our SQL Statement object, which is obtained from an already established connection.

Statement stmt = con.createStatement() ;

Since this particular SQL string effectively updates the database and doesn't return any data, we call the executeUpdate method, which will execute the SQL command and return the number of rows affected (i.e., the update count).

int count = stmt.executeUpdate(createString);

// Process the Update Count

To properly release database resources (which are generally controlled by expensive license limitations), we need to close our objects properly:


} catch(SQLException ex) {
// Handle Exception

This same syntax is used for SQL CREATE, DROP, INSERT, UPDATE, and DELETE operations.

On the other hand, SQL SELECT operations generate a new view of the data. To handle this data, the JDBC API uses the ResultSet object, which provides an interface to access the rows and columns of the resultant data. First we create the appropriate SQL string - in this case the previous SELECT statement - which returned certain columns for all rows with Age greater than 30:

String selectStatement =
"SELECT First, Last, Age, Phone " +
"FROM NewContacts " +
"WHERE Age > 30" ;

The SQL Statement object is created as before, but now we'll call the executeQuery method, which will return the data resulting from our query. Notice that the ResultSet object is created from the Statement object; therefore, the Statement object effectively owns the ResultSet object. As a result, when the Statement object is closed or the object reference is reused, the ResultSet object is also closed.


Statement stmt =
con.createStatement() ;

ResultSet rs =
stmt.executeQuery(selectStatement) ;

To access the resulting rows of our query, we use the built-in iterator of the ResultSet object to access all the rows produced by the query. This iterator is always initialized to point to the (fictitious) record that comes before the first record; thus, when it's first accessed (by the next method), it points to the first row:

while( {

To access the columns for each row in the ResultSet object, we need to convert them to their Java data types, which is done using the appropriate getXXX method, where we replace the XXX in the method declaration by the actual data type. For example, for the particular query we're using, we have two String objects followed by two integers. Assuming the appropriate variables have already been declared, we can easily extract the data:

firstName = rs.getString(1) ;
lastName = rs.getString(2) ;
age = rs.getInt(3) ;
phone = rs.getInt(4) ;

// Utilize the new data for some processing
// For example, we could create a new object,
// or print out all of the rows.

As before, you should close everything down properly so as to free up valuable database resources.

Of course, there's considerably more in the JDBC API than I've covered here. Some notable additions are the DatabaseMetadata and ResultSetMetadata interfaces, which can be used to obtain a great deal of information about the database or ResultSet, respectively, at runtime. I also didn't cover the PreparedStatement, which allows a database and JDBC driver to precompile particular SQL statements for improved performance or the CallableStatement interfaces, which allow a Java program to call a database stored procedure.

Hopefully this article has provided a gentle introduction to both SQL and the process of encapsulating SQL statements using JDBC. While I've intentionally avoided many of the details, you certainly have enough knowledge to get started, both with SQL and JDBC. With this basic understanding, you can begin to explore building components on top of the JDBC layer, as well as using applets, servlets, and JavaServer Pages to interact with a database.

In order to post a comment you need to be registered and logged in.

Register | Sign-in

Reader Feedback: Page 1 of 1

Subscribe to the World's Most Powerful Newsletters


The question before companies today is not whether to become intelligent, it’s a question of how and...
While some developers care passionately about how data centers and clouds are architected, for most,...
ChatOps is an emerging topic that has led to the wide availability of integrations between group cha...
As DevOps methodologies expand their reach across the enterprise, organizations face the daunting ch...
As Marc Andreessen says software is eating the world. Everything is rapidly moving toward being soft...
You know you need the cloud, but you’re hesitant to simply dump everything at Amazon since you know ...
Is advanced scheduling in Kubernetes achievable?Yes, however, how do you properly accommodate every ...
The cloud era has reached the stage where it is no longer a question of whether a company should mig...
The need for greater agility and scalability necessitated the digital transformation in the form of ...
In his keynote at 18th Cloud Expo, Andrew Keys, Co-Founder of ConsenSys Enterprise, provided an over...
Coca-Cola’s Google powered digital signage system lays the groundwork for a more valuable connection...
In his session at 21st Cloud Expo, Raju Shreewastava, founder of Big Data Trunk, provided a fun and ...
"Since we launched LinuxONE we learned a lot from our customers. More than anything what they respon...
DevOps is under attack because developers don’t want to mess with infrastructure. They will happily ...
"As we've gone out into the public cloud we've seen that over time we may have lost a few things - w...
In his session at 21st Cloud Expo, Michael Burley, a Senior Business Development Executive in IT Ser...
Sanjeev Sharma Joins June 5-7, 2018 @DevOpsSummit at @Cloud Expo New York Faculty. Sanjeev Sharma is...
We are given a desktop platform with Java 8 or Java 9 installed and seek to find a way to deploy hig...
"I focus on what we are calling CAST Highlight, which is our SaaS application portfolio analysis too...
"Cloud4U builds software services that help people build DevOps platforms for cloud-based software a...