Queries can be executed using two different kinds of statements. Simple queries can be executed using static statements, while prepared statements allow the changing of parameters before execution.
To execute a static SQL statement, a Statement object has to be obtained from the Connection object first:
Statement statement = connection.createStatement();
The Statement can then be used to execute queries or updates on the target database.
As with the connection, it is good practice to close a statement as soon as it stops being used. If the statement is not closed, it will be closed implicitly when the underlying connection is closed.
The following example shows the way to get data from a table using the executeQuery method:
ResultSet resultSet = statement.executeQuery(
"SELECT account_id, first_name, last_name FROM account");The method returns a ResultSet, which will be introduced later in this chapter.
To insert, update, or delete rows, the executeUpdate method can be used:
int rowCount = statement.executeUpdate("DELETE FROM account");The returned integer value states the number of rows that were affected by the update or delete operation.
If a table has columns that are configured to auto-generate keys, like account_id in the account table introduced in Chapter 3, PostgreSQL Basic Building Blocks, it is useful to be able to obtain the keys of a newly inserted row for referencing that row later.
Therefore, JDBC contains variations of the executeUpdate method that accept an array of either the names of the key columns or their indexes in the target table as an additional parameter. To configure the statement to return the account_id of a newly inserted account, the executeUpdate method can be called like this:
String sql = "INSERT INTO account (first_name, last_name, email, password) VALUES ('John', 'Doe', '[email protected]', 'youDontKnow')";
statement.executeUpdate(sql, new String[]{"account_id"});Or, assuming that account_id is the first column in the target table, the executeUpdate method can be called as follows:
statement.executeUpdate(sql, new int[]{1});The generated indexes can then be obtained by calling getGeneratedKeys after inserting new data:
ResultSet newKeys = statement.getGeneratedKeys();
if(newKeys.next()){
int newAccountID = newKeys.getInt("account_id");
}For operations that do not insert new rows, or a target table that does not contain columns with auto-generated keys, the additional parameter is ignored, and the ResultSet returned by
getGeneratedKeys will be empty.
In many applications, an SQL statement is executed multiple times with different parameters. It is not efficient to construct the query-string over and over again and execute it using a Statement. In these cases, a PreparedStatement should be used.
A PreparedStatement extends the Statement interface, and contains a precompiled SQL statement. It can be used to efficiently execute the statement multiple times with different parameters.
A PreparedStatement is initialized with a query string, just like a fixed Statement, but allows the usage of question marks as place holders for parameters that can be set later.
JDBC provides setter methods for different data types to assign a value to a parameter.
In the following example, the PreparedStatement is used to change the password of an account with the account_id as 42. It can be reused to do the same with different accounts by simply changing the parameters using the different setter methods and then calling executeUpdate. Moreover, the setter methods take care of any necessary formatting, such as putting quotes around strings:
PreparedStatement statement = connection.prepareStatement(
"UPDATE account SET password=? WHERE account_id=?");
statement.setString(1, "myNewPassword");
statement.setInt(2, 42);
int changedRows = statement.executeUpdate();Before executing a PreparedStatement, all parameters must have a value assigned to them. If this is not the case, an SQLException will be thrown.
Once assigned, the parameters retain their values between executions. If necessary, they can be unset by calling clearParameters on PreparedStatement.
JDBC provides a dedicated setNull method to assign a null value to a column. Passing null to one of the other setter methods will usually throw a NullPointerException. In the preceding example, setting the password column to null could be done by calling:
statement.setNull("password");Or, alternatively, you can call:
statement.setNull(1);
Like the Statement class, PreparedStatement also offers a method to query data from the target table. Again, a
ResultSet is returned:
PreparedStatement statement = connection.prepareStatement(
"SELECT * from account where account_id = ?");
statement.setInt(1, 42);
ResultSet result = statement.executeQuery();In SQL, a result set is defined as a set of rows returned by a query as well as metadata about the structure of the queried table.
In JDBC, the methods that query data from a table are defined to return an object of the type ResultSet.
A ResultSet is always tied to the Statement object that created it. Moreover, there can be only one ResultSet per Statement. If a Statement is closed or re-executed, an existing ResultSet will be closed too.
In order to navigate through the results, the ResultSet contains a cursor, which initially points before the first returned row. Therefore, it is necessary to position it on the first row before getting data from the ResultSet by calling the next method.
The next method moves the cursor forward by one row. It returns true if such a row exists, and false otherwise. Therefore, the next method can be conveniently used to iterate through a ResultSet using a while loop:
ResultSet result = statement.executeQuery(
"select account_id, last_name from account");
while(result.next())
{
//get row data
}The JDBC API also defines a set of methods to query the current position of the cursor:
int getRow(): This returns the row number of the cursor position, or 0 if the cursor is not positioned on any rowboolean isAfterLast(): This returns true if the cursor is positioned after the last rowboolean isBeforeFirst(): This returns true if the cursor is positioned before the first rowboolean ifFirst(): This returns true if the cursor is positioned on the first rowboolean isLast(): This returns true if the cursor is positioned on the last row
To get data from a row, the JDBC API provides several getter methods, such as getInt or getString, which convert the SQL data types to the matching Java types. It also offers the getObject method, which returns data as a Java object without casting it to a specific type. This can be useful if the type of a column is not known in the context of the Java code.
All getter methods accept either a column index or a column name as argument.
To get the first and last name of an account, the body of the while loop in the previous example might contain statements like these:
int accountId = result.getInt(1);
String lastName = result.getString("last_name");While using the index is generally more efficient, using the column name makes the code more readable, and prevents errors in case the number or order of columns is changed in the query or table definition.
It is also possible to find the index of a column by its name using the findColumn method:
int columnIndex = result.findColumn(columnName);
Some SQL data types are mapped to primitive data types like int or boolean in Java. As these primitive types can't hold null values, they will be set to their default value (for example, 0 for numeric values or false for boolean) if the value of the column in the database is null.
However, it is possible to check for null values using the wasNull method after invoking one of the getter methods:
Integer n = result.getInt(1);
if(result.wasNull())
{
n = null;
}This could, for example, be used to wrap primitives in their object-type wrapper classes to achieve the same behavior along all data types.
By default, a
ResultSet can only be navigated forward and is read-only. Moreover, it does not reflect the changes on the underlying data. To change this behavior, the createStatement and prepareStatement methods can be called with additional parameters specifying the type and concurrency of the ResultSets:
connection.createStatement(type, concurrency); connection.prepareStatement(sql, type, concurrency);
Possible values for type are:
ResultSet.TYPE_FORWARD_ONLY: The cursor may only move forward, which is the default settingResultSet.TYPE_SCROLL_INSENSITIVE: The cursor may move in both directions, but theResultSetdoes not reflect the changes made to itResultSet.TYPE_SCROLL_SENSITIVE: The cursor may move in both directions, andResultSetwill reflect the changes that are made to its data
Setting a ResultSet to TYPE_SCROLL_SENSITIVE does not mean that it will reflect changes in the database; it reflects only the changes made to the data stored in the ResultSet itself. To pick up the changes made in the current row in the database, refreshRow has to be called.
Values for concurrency, which configure a read-only or an updateable ResultSet, can be the following:
ResultSet.CONCUR_READ_ONLY(default)ResultSet.CONCUR_UPDATABLE
In addition to moving forward by calling next, a scrollable ResultSet allows backwards navigation and positioning the cursor at a specific row:
boolean previous(): Moves the cursor to the previous row. Returns true if such a row exists. Otherwise, the cursor is positioned before the first row, and false is returned.boolean absolute(int rowNumber): Positions the cursor at the given row, and returns true if such a row exists.boolean relative(int numberOfRows): Moves the cursor forward or backwards by the given number of rows. Returns true if the new position is within theResultSet. Otherwise, the cursor is positioned before the first or after the last row, and false is returned.boolean aferLast(): Positions the cursor after the last row.boolean beforeFirst(): Positions the cursor before the first row.boolean first(): Positions the cursor on the first row.boolean last(): Positions the cursor on the last row.
Both the absolute and relative methods also accept negative values. While the relative method will move the cursor backwards in such a case, the absolute method will handle the value as a position with respect to the end of the ResultSet. Calling absolute(-1) would, for example, have the same result as a call of the method last.
To update the current row in an updateable ResultSet, JDBC provides update methods similar to the getter and setter methods. After changing the fields in a ResultSet, updateRow must be called to make the changes persistent.
Instead of updating a row, it can also be inserted as a new set of data by calling insertRow, or deleted by calling deleteRow.
To navigate a ResultSet in the reverse order, and to change a column of the current row, one can do something like the following:
PreparedStatement statement = connection.prepareStatement(
"select * from account",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet result = statement.executeQuery();
result.afterLast();
while(result.previous())
{
int accountID = result.getInt("account_id");
String email = getNewEmailAddress(accountID);
result.updateString("email", email);
result.updateRow();
}By default, a ResultSet will contain the complete result of a query. When working with large datasets, this behavior might be inefficient, and can be changed by using a cursor.
A cursor is a control structure that points to one row in a set of rows. It can be used to navigate a set of rows similar to an iterator in Java or other object-oriented languages.
To use a cursor, auto-commit must first be turned off for the used connection:
connection.setAutoCommit(false);
Then, after creating a statement, the fetch size can be set:
statement.setFetchSize(10);
ResultSet result = statement.executeQuery(
"select * from account");
while(result.next())
{
//read row data
}
statement.setFetchSize(0);
connection.setAutoCommit(true);Now, the JDBC driver will no longer load the complete result into the ResultSet, but fetch the next ten rows whenever needed.
Setting the fetch size to 0 will restore the original behavior of loading the complete result.
Also, auto-commit should be turned on again after using the cursor.
In some cases, the structure of a table is not known at the time of implementation, or it is intended to keep the code independent of a table structure.
In such cases, a ResultSetMetaData object can be retrieved from ResultSet to query the structural information of a table at runtime.
The following example shows how to get some basic information about a table's columns:
ResultSet result = statement.executeQuery("select * from account");
ResultSetMetaData metaData = result.getMetaData();
int columnCount = metaData.getColumnCount();
for(int c=1;c<=columnCount; c++)
{
String columnName = metaData.getColumnName(c);
int columnType = metaData.getColumnType(c);
String columnClass = metaData.getColumnClassName(c);
String columnTypeName = metaData.getColumnTypeName(c);
}In the preceding example, different methods are used to gather information about the type of column.
The method getColumnType returns an integer value that matches one of the constants defined in java.sql.Types. This can be used when calling the setObject method on a PreparedStatement.
The String returned by
getColumnClassName represents the fully qualified name of the underlying class of the object that would be returned by the ResultSet getObject method.
In the last command, getColumnTypeName returns the type-name of a column on the database side.
Other than getting column-type information, the ResultSetMetaData interface provides further methods to get information about a column like isAutoIncrement or isNullable, which can be looked up in the API's documentation.