JDBC–Java Data Base Connectivity–is the specification for the Java application programming interface that lets Java programs work with relational databases systems (RDBMSs).
Explaining databases, and the SQL language used to manipulate them, is beyond the scope of this tutorial. Our mission is to offer an overview of how to work with JDBC.
NOTE: to run the examples and exercises in this lesson, you’ll need to install and initialize a database server on your computer. There are several good ones out there, such as Oracle, MySQL and Derby. We’ve chosen PostgreSQL, a popular open source platform. Here’s what to do to get started.
- Open a web browser and go to https://www.postgresql.org/.
- Click on the Download button, follow the instructions to select the correct installer for your system, and download it.
- Run the installer. You’ll encounter a “Select Components” dialog. Clear the check box for Stack Builder and continue with the installation.
- Accept the defaults. When asked for a password to use for the administrator, choose one you’ll remember. (I used “root”.)
- You’ll be asked which applications to install. Choose the following: Database Drivers/pgJDBC and Database Server PostgreSQL (64 bit). (You may need to choose a different Database Server on a non-Windows system.)
- Download Lesson22-files.zip and open it. Copy file postgresql-42.5.1.jar to the Libs project in your Eclipse workspace. Copy file lesson22.sql to (in Windows) C:\Program Files\PostgreSQL\15\bin.
- (Again, this is Windows specific. If on another operating system, please adjust.) Open the Command Prompt and enter the following:
cd C:\Program Files\PostgreSQL\15\binpsql postgres postgres(Answer the password prompt with the password you chose during the installation.)- At the prompt (“postgres-#”) enter
\i lesson22.sql. - A database table should be created and loaded and you should see a list of its contents.
And now you’re ready!
Next, so you can follow along, download project Lesson-22-Example-01.zip and import it to your workspace.
Try running class Lesson22Reader. It will fail because no suitable driver can be found for the database, but this will give you a run configuration you can fix.
Open the Run/Run Configurations dialog, click the Dependencies tab, click on Classpath Entries, and use the Add JARs button to add postgresql-42.5.1.jar to the execution classpath.
You’ll also have to add the directory containing file lesson22.properties to the execution classpath. While still in the Run Configurations dialog and with Classpath Entries still selected, click on Advanced. Select the Add Folders radio button and click OK. From the Choose folders to add window, open the Lesson-22-Example-01 project node and select props, then click OK.
Then try running again. This time, the program should work properly, and your output should look like this:
1 2022-11-27 2022-11-27 09:55:20.813813 Shoo-ba-dee, Shoo-ba-dee
2 2022-11-27 2022-11-27 09:55:20.813813 Cannonball Adderly
3 2022-11-27 2022-11-27 09:55:20.813813 Came on the scene
4 2022-11-27 2022-11-27 09:55:20.813813 Like a bolt from the blues.
5 2022-11-27 2022-11-27 09:55:20.813813 His popularity
6 2022-11-27 2022-11-27 09:55:20.813813 Coincidentally
7 2022-11-27 2022-11-27 09:55:20.813813 Left me more leisure for
8 2022-11-27 2022-11-27 09:55:20.813813 Romance and booze.
9 2022-11-27 2022-11-27 09:55:20.813813 (no text here)
10 2022-11-27 2022-11-27 09:55:20.813813 -- Paul Desmond
Prepared statement=SELECT LINE_NO
,ENTRY_DATE
,ENTRY_TIME
,TEXT_LINE
FROM PUBLIC.LESSON22
WHERE LINE_NO >= -2147483648
ORDER BY LINE_NO
1 2022-11-27 2022-11-27 09:55:20.813813 Shoo-ba-dee, Shoo-ba-dee
2 2022-11-27 2022-11-27 09:55:20.813813 Cannonball Adderly
3 2022-11-27 2022-11-27 09:55:20.813813 Came on the scene
4 2022-11-27 2022-11-27 09:55:20.813813 Like a bolt from the blues.
5 2022-11-27 2022-11-27 09:55:20.813813 His popularity
6 2022-11-27 2022-11-27 09:55:20.813813 Coincidentally
7 2022-11-27 2022-11-27 09:55:20.813813 Left me more leisure for
8 2022-11-27 2022-11-27 09:55:20.813813 Romance and booze.
9 2022-11-27 2022-11-27 09:55:20.813813 (no text here)
10 2022-11-27 2022-11-27 09:55:20.813813 -- Paul DesmondAnd now we’ll explain how we got here.
Getting Started: The Connection Interface
In our example, class Lesson22Dbo handles the database connection and performs input and output. (DBO stands for database object.) Each instance of this class represents one row in table PUBLIC.LESSON22.
Before you can work with your database, you need to connect to it. Open the source code file Lession22Dbo.java and have a look at the code.
public class Lesson22Dbo {
...
private static Connection conn = null;
...
public static void connect() throws Exception {
// Get the URL, user ID, and password from the properties file.
InputStream is = Lesson22Dbo.class.getClassLoader().getResourceAsStream("lesson22.properties");
Properties props = new Properties();
props.load(is);
String url = props.getProperty("url");
String user = props.getProperty("user");
String password = props.getProperty("password");
The first step is to get a URL to the database, a user ID, and a password. We’ve chosen to get these values from a properties file. This is likely how you’ll get these values in a production environment. They’re subject to change over time, so you don’t want to hard-code them in Java.
Our lesson22.properties file looks like this:
url = jdbc:postgresql://localhost:5432/postgres
user = postgres
password = rootThe URL indicates that it pertains to JDBC; the database system is PostgreSQL, the server is localhost (i.e., the box we’re running on); the port number is 5432; and the default schema name is postgres.
Now that we have the path to our database, a logon ID, and a password, we’re ready to connect.
conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);Many JDBC objects, including Connection, are in fact implementations of interfaces, and you might remember from Lesson 3 that you write your source code against the definition of an interface but often the implementation of the interface isn’t available until runtime. That’s exactly what’s happening here. The job of DriverManager.getConnection() is to figure out which of the available implementations of JBDC is appropriate for the RDBMS indicated by the URL (PostgreSQL in this case), and return an instance of its Connection implementation. The implementation has to be on the execution classpath. When you tried running the first time, it wasn’t. Once you added postgresql-42.5.1.jar to the classpath, it was.
In our example project, we made the properties static–they don’t change for each instance of our class. Neither is the Connection. This may not make sense in a production environment; your mileage may vary.
Transactions
Once we have the Connection, we set its autocommit property to false. In a production environment, it’s usually the case that several updates to a database must either all succeed or all fail. For example, consider a bank’s IT system that transfers funds from one of a depositor’s accounts to another. The system has to perform all these operations on the database:
- Create a record of the withdrawal from the source account.
- Adjust the balance of the source account.
- Create a record of the deposit to the target account.
- Adjust the balance of the target account.
What if something went wrong along the way and only one, two, or three of the operations was completed? The bank accounts would be inconsistent. So it’s important that none of the operations become permanent unless they all succeed. In RDBMS lingo, these four steps constitute a transaction. Each operation is held in a pending state. Once it’s clear that all of them are successful, we commit the transaction, which actually writes the results of all the operations; if there’s a failure, we rollback the transaction. commit and rollback are done at the Connection level.
For this to work, the Connection’s autocommit property must be false, but the default is true. Hence conn.setAutoCommit(false).
Connection Pools
At the end of our job, we’ll execute the statement conn.close() to disconnect from the database server and release our affiliated resources.
It happens that getting a new Connection is a time-consuming operation–at least, from the computer’s perspective. In an online environment, such as an e-commerce web site processing thousands of transactions each second, the lag time in acquiring a fresh connection for each interaction with a user is unacceptable. For this reason, such a system never closes a connection. When it’s done interacting with a user, it returns it to a pool of Connection instances that never close. When some subsequent interaction requires a Connection, the system goes to the pool for an instance that’s already open and ready to go.
Statement and ResultSet: Reading Data
Now let’s try reading our table. We start by declaring the SQL statement that fetches the data.
static final String COL_LINE_NO = "LINE_NO";
static final String COL_ENTRY_DATE = "ENTRY_DATE";
static final String COL_ENTRY_TIME = "ENTRY_TIME";
static final String COL_TEXT_LINE = "TEXT_LINE";
static final String TABLE_NAME = "PUBLIC.LESSON22";
private static final String STATIC_SELECT_STMT_1 = //
"SELECT " + COL_LINE_NO //
+ "\n ," + COL_ENTRY_DATE //
+ "\n ," + COL_ENTRY_TIME //
+ "\n ," + COL_TEXT_LINE //
+ "\n FROM PUBLIC.LESSON22 " //
+ "\n WHERE " + COL_LINE_NO + " >= ";
private static final String STATIC_SELECT_STMT_2 = //
"\n ORDER BY " + COL_LINE_NO;
What we have here is the start and end of a SELECT statement. When we want to use them, we’ll sandwich a numeric value between them to get something that looks like this:
SELECT LINE_NO
, ENTRY_DATE
, ENTRY_TIME
, TEXT_LINE
FROM PUBLIC.LESSON22
WHERE LINE_NO >= x
ORDER BY LINE_NO;You may ask why we just don’t code it with the column and table names instead of incorporating references to constants and without the comment slashes and newline characters.
We use constants to let the Java editor help us avoid typos by making it difficult to refer to a column name incorrectly.
The newline characters are there so when running the code in the debugger, the constant value is nicely formatted when displayed. (If a SQL statement ever fails and you want to tinker with it outside the editor, like in a database utility, having it nicely formatted is a big plus.)
The comment slashes are there so if the source code is ever reformatted, the line breaks will be preserved and the statement remains (relatively) easy to read.
To start the process of retrieving data, we create a Statement object, and use it to create a ResultSet object.
// Use the static SELECT statement to return all rows.
public static List<Lesson22Dbo> selectStatic() throws SQLException {
return selectStatic(Integer.MIN_VALUE);
}
// Use the static SELECT statement to return multiple rows starting
// with the LINE_NO provided.
public static List<Lesson22Dbo> selectStatic(int firstLine) throws SQLException {
List<Lesson22Dbo> returnList = new ArrayList<Lesson22Dbo>();
Statement stmt = conn.createStatement();
ResultSet rs = null;
String query = STATIC_SELECT_STMT_1 + firstLine + STATIC_SELECT_STMT_2;
try {
rs = stmt.executeQuery(query);
Lesson22Dbo dbo = null;
while ((dbo = populateQuery(rs)) != null) {
returnList.add(dbo);
}
} finally {
if (rs != null) {
rs.close();
}
stmt.close();
}
return returnList;
}(Notice the overloads, which give us one call to retrieve all rows in the table and another to retrieve a subset of them.)
A Statement is the object used for executing a single static SQL statement–and by static we mean it executes as is without variable substitution, as opposed to a PreparedStatement we’ll talk about below.
A Statement isn’t limited to reading data–it can create and update data too. But the executeQuery() method is specifically for reading, and it returns a ResultSet, which is a table of data returned by the query and which maintains a cursor pointing to the current row–i.e., the one last fetched.
And to see how we use a ResultSet, let’s look at our populateQuery() method.
// Create instances from the ResultSet and add them to returnLlist.
private static void populateQuery(ResultSet rs, List<Lesson22Dbo> returnList) throws SQLException {
if (rs.next()) {
Integer lineNo = rs.getInt(1);
Date entryDate = rs.getDate(2);
Timestamp entryTime = rs.getTimestamp(3);
String textLine = rs.getString(4);
if (rs.wasNull()) {
textLine = "(no text here)";
}
Lesson22Dbo dbo = new Lesson22Dbo(lineNo, entryDate, entryTime, textLine);
return dbo;
} else {
return null;
}
}First notice that we perform a while loop as long as rs.next() is true. If there are more rows, next() moves the cursor to the next one and returns true; otherwise, it returns false.
If there is another row, then our code populates fields from the data it contains: lineNo is assigned the value of the first column in the SELECT statement, entryDate the second column, and so on.
The construct wasNull() method is hardly ever used. Database columns can be null, for the same reasons that Java object references can be null. wasNull() returns true if the last value fetched from the ResultSet was a null, and is really useful only if the variable in which the value is stored is a primitive–since primitives can’t be valueless. In fact, in our example, if the fourth value fetched is null, textLine, being an object reference, will be null anyway, and we could just as easily (and more sensibly) coded if (textLine == null).
The various get… methods of ResultSet don’t just take integers as arguments; they can take instead the names of the columns retrieved by the SELECT. So the above could have been coded:
...
Integer lineNo = rs.getInt(COL_LINE_NO);
Date entryDate = rs.getDate(COL_ENTRY_DATE);
Timestamp entryTime = rs.getTimestamp(COL_ENTRY_TIME);
String textLine = rs.getString(COL_TEXT_LINE);
...This gives you flexibility: you can shuffle the columns returned by the SELECT and retrieve them without concern for their positions in the statement. (And notice how we’ve used the constants we defined for column names, to make sure they’re spelled correctly.)
The get… methods in the ResultSet class, named for the format of what they return, include:
- getBigDecimal (the value from the database might be any numeric format)
- getBlob (Binary Large Object)
- getBoolean
- getByte
- getDate
- getDouble
- getFloat
- getInt
- getLong
- getObject
- getShort
- getTime
- getTimestamp
PreparedStatement
Earlier we mentioned how a Statement is static–i.e., it’s a string passed to the RDBMS as is. For example, if user SMITH is trying to log in to your system, you might validate them by building a call like this and passing it to a Statement:
SELECT * FROM USERS WHERE LOGON_ID = 'SMITH' AND PASSWORD = 'Kumquat'(In SQL, strings are surrounded by apostrophes.)
There are two problems with this. For starters, suppose the LOGON_ID value you’re looking for is “O’BRIEN”
SELECT * FROM USERS WHERE LOGON_ID = 'O'BRIEN' AND PASSWORD = 'Durian'Right off, you can see that the apostrophes surrounding the LOGON_ID are unbalanced, so the statement as a whole is invalid.
But there’s another, more insidious problem called an injection attack. This is when a malicious user, via an online form or a URL, changes the nature of a SQL call to bypass a system’s defenses. In our example, if the LOGON_ID and PASSWORD are input from an online form, a user might put this into the PASSWORD field:
Durian' OR 1=1 --leaving the SQL statement as a whole to read
SELECT * FROM USERS WHERE LOGON_ID = 'SMITH' AND PASSWORD = 'Durian' OR 1=1 --'Since just as in Java, AND has a higher precedence than OR and anything following “–” is a comment, this statement retrieves every row where 1=1–which is true of every row in the table, of course–and voila! Our malicious user has gained access to the system.
And it can get even worse. Suppose the user enters
Durian' OR 1=1; DROP TABLE LEDGER --In this example, the malicious user not only gets into the system–they discard an entire table of data as well!
To get around both these problems, RDBMS/s provide Prepared Statements, corresponding to the Java interface PreparedStatement. Prepared Statements offer several advantages:
- Every time you issue a query against a database, the RDBMS must compile it and create an execution plan for it. For a statement issued only once or only a very few times, this is fine. But for statements issued frequently–which is nearly all of them–a Prepared Statement is far better because the compilation and execution plans need to be created only once.
- In a Prepared Statement, characters (like apostrophes) that have special significance and appear in data fields are not a problem.
- Prepared Statements execute faster than Statements.
- Prepared Statements allow for the use of binary data.
So let’s look at how we create a PreparedStatement. Here is the SQL.
private static final String DYNAMIC_SELECT_STMT_BASE = //
"SELECT " + COL_LINE_NO //
+ "\n ," + COL_ENTRY_DATE //
+ "\n ," + COL_ENTRY_TIME //
+ "\n ," + COL_TEXT_LINE //
+ "\n FROM " + TABLE_NAME;
private static final String DYNAMIC_SELECT_ALL_STMT = //
DYNAMIC_SELECT_STMT_BASE //
+ "\n WHERE " + COL_LINE_NO + " >= ?" //
+ "\n ORDER BY " + COL_LINE_NO;
private static final String DYNAMIC_SELECT_ONE_STMT = //
DYNAMIC_SELECT_STMT_BASE //
+ "\n WHERE " + COL_LINE_NO + " = ?" //
+ "\n ORDER BY " + COL_LINE_NO;Defined this way, DYNAMIC_SELECT_ALL_STMT and DYNAMIC_SELECT_ONE_STMT are identical except for the WHERE clause; in the first case, the comparison is greater or equal (to retrieve multiple rows), and in the second it’s equal (to retrieve a single row). The resulting constants resolve to:
DYNAMIC_SELECT_ALL_STMT:
SELECT LINE_NO //
, ENTRY_DATE //
, ENTRY_TIME //
, TEXT_LINE //
FROM PUBLIC.LESSON22
WHERE LINE_NO >= ?
ORDER BY LINE_NO;
DYNAMIC_SELECT_ONE_STMT:
SELECT LINE_NO //
, ENTRY_DATE //
, ENTRY_TIME //
, TEXT_LINE //
FROM PUBLIC.LESSON22
WHERE LINE_NO = ?
ORDER BY LINE_NO;The only difference between the SQL used with a Statement and that used with a PreparedStatement is the “?” in place of a hard-coded value.
To use these queries, we create PreparedStatement objects for each of them in the connect() method when the job starts:
private static PreparedStatement selectAllStmt = null;
private static PreparedStatement selectOneStmt = null;
...
selectAllStmt = conn.prepareStatement(DYNAMIC_SELECT_ALL_STMT);
selectOneStmt = conn.prepareStatement(DYNAMIC_SELECT_ONE_STMT);… and we close them when the job ends. The PreparedStatement objects are static because we only need one for the life of the run.
As an example, we use the DYNAMIC_SELECT_ALL_QUERY like so:
// Use the prepared SELECT statement to return all rows.
public static List<Lesson22Dbo> selectAllDynamic() throws SQLException {
return selectAllDynamic(Integer.MIN_VALUE);
}
// Use the prepared SELECT statement to return multiple rows starting
// with the LINE_NO provided.
public static List<Lesson22Dbo> selectAllDynamic(int firstLine) throws SQLException {
List<Lesson22Dbo> returnList = new ArrayList<Lesson22Dbo>();
selectAllStmt.setInt(1, firstLine);
displayPreparedStatement(selectAllStmt);
ResultSet rs = null;
try {
rs = selectAllStmt.executeQuery();
Lesson22Dbo dbo = null;
while ((dbo = populateQuery(rs)) != null) {
returnList.add(dbo);
}
} finally {
if (rs != null) {
rs.close();
}
}
return returnList;
}
The line, stmt.setInt(1, firstLine);, furnishes the value for the spot in the query occupied by the first (and only) question mark, a process called binding. In this example, if firstLine is, say, 3, when the query is executed, it will behave as though the WHERE clause read “LINE_NO > 3“.
Of course, PreparedStatement has a set variant for any kind of data format an RDBMS might store:
- setDouble
- setLong
- setShort
- setInt
- setDate
- setTime
- setTimestamp
- setString
and more.
Lesson22Reader
Having discussed all this, let’s look at our Lesson22Reader class:
public class Lesson22Reader {
public static void main(String[] args) throws Exception {
Lesson22Dbo.connect();
// Retrieve all rows using a static SQL call.
List<Lesson22Dbo> staticList = Lesson22Dbo.selectStatic();
Lesson22Dbo.dump(staticList);
// Retrieve all rows using a PreparedStatement SQL call.
List<Lesson22Dbo> dynamicList = Lesson22Dbo.selectAllDynamic();
Lesson22Dbo.dump(dynamicList);
Lesson22Dbo.close();
}
}Simple, isn’t it? First, we call the static connect() method to initialize. Next, we call the selectStatic() method to retrieve all the rows of our table using a static Statement, and call dump() to display them. Then we do the same, calling selectAllDynamic() to use a PreparedStatement instead of a Statement. Finally, we close the connection.
Notice that selectStatic(), selectDynamic(), and dump() are static methods. That’s because they don’t pertain to individual Lesson22Dbo instances. On the next page, we’ll use some methods that are.