Core Java Advanced LiveLessons

Java Database Connectivity

Slide navigation: Forward with space bar, → arrow key, or PgDn. Backwards with ← or PgUp.

.jpg

Understand the design of the Java database connectivity API

The JDBC API

.gif

JDBC (Java Database Connectivity) is an API for interacting with a database in Java.
Each database vendor produces a driver that translates JDBC requests to the database-specific protocol.
JDBC is based on SQL, the Standard Query Language for database access.
JDBC provides classes for issuing SQL statements and for working with the query results.
JDBC has been hugely successful.
- Every commonly used database has a JDBC driver.
- There are several databases written in Java.
Object-relational mappers provide a higher-level mechanism for working with a database, but you should still understand SQL/JDBC to use them effectively.

Be able to connect to a database in a Java program

Requirements for Using JDBC

You need a database.
- In this lesson, we use Apache Derby.
- You can also use MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and so on.
- Go ahead and start the database now. To start Derby, run:
```
java -jar derby/lib/derbyrun.jar server start
```
You need the database driver JAR, such as derbyclient.jar for Derby.
You need the format of the JDBC URL. For Derby, it is:
```
jdbc:derby://localhost:1527/databaseName;create=true
```

Connecting to the Database

You need to have the database driver JAR on the class path.

Use this code to establish a JDBC connection:

String url = "jdbc:derby://localhost:1527/COREJAVA;create=true";
String username = "app";
String password = "secret";
Connection conn = DriverManager.getConnection(url, username, password);

You use the Connection object to create SQL statements.
Database connections are a limited resource. When you are done, be sure to close the connection.

Server-side application frameworks provide mechanisms for pooling database connections:

@Resource(name="jdbc/corejava")
private DataSource source;
...
Connection conn = source.getConnection();

lesson09/test

java -jar /opt/jdk1.8.0/db/lib/derbyrun.jar server start &
java -classpath /opt/jdk1.8.0/db/lib/derbyrun.jar:. test.TestDB
java -jar /opt/jdk1.8.0/db/lib/derbyrun.jar server shutdown

Java 9 News Flash - No More JavaDB

.png

Java DB is gone.
Get Derby from https://db.apache.org/derby/ https://db.apache.org/derby/

Execute SQL statements from a Java program

Executing SQL Statements

To execute a SQL statement, create a Statement object:
```
Statement stat = conn.createStatement();
```

To update the database (INSERT, UPDATE, DELETE), call the executeUpdate method:

String command = "UPDATE Books"
   + " SET Price = Price - 5.00"
   + " WHERE Title NOT LIKE '%Introduction%'";
int rows = stat.executeUpdate(command);

The number of affected rows is returned.

Use executeQuery to issue a SELECT query:

ResultSet result = stat.executeQuery("SELECT * FROM Books");

Use execute to issue arbitrary SQL commands.

Managing JDBC Objects

A Connection object can produce one or more Statement objects.
- Some database drivers only allow one Statement at a time.
- Call DatabaseMetaData.getMaxStatements to find out.
You can use the same Statement object for multiple queries.
A Statement can have at most one open ResultSet.
- Don't work with multiple result sets at a time. Issue a query that gives you all data in one result set.
When you execute another query or close a Statement, an open result set is closed.
- The Statement.closeOnCompletion method closes the statement as soon as an open result set is closed.
When you close a Connection, all statements are closed.

SQL Exceptions and Warnings

A SQLException has a chain of SQLException objects.
This is in addition to the “cause” chain that all exceptions have.
The SQLException class extends the Iterable<Throwable> interface.

You can iterate over all exceptions like this:

for (Throwable t : sqlException) {
    do something with t
}

The SQLException.getSQLState method yields a string that is standardized by either X/Open or SQL:2003.
- Call DatabaseMetaData.getSQLStateType to find out which standard is used by your driver.

There is also a chain of warnings:

SQLWarning w = stat.getWarning();
while (w != null) {
    do something with w
    w = w.nextWarning();
}

lesson09/query

java -jar /opt/jdk1.8.0/db/lib/derbyrun.jar server start &
java -classpath /opt/jdk1.8.0/db/lib/derbyrun.jar:. exec.ExecSQL Authors.sql
java -classpath /opt/jdk1.8.0/db/lib/derbyrun.jar:. exec.ExecSQL Publishers.sql
java -classpath /opt/jdk1.8.0/db/lib/derbyrun.jar:. exec.ExecSQL Books.sql
java -classpath /opt/jdk1.8.0/db/lib/derbyrun.jar:. exec.ExecSQL BooksAuthors.sql

java -classpath /opt/jdk1.8.0/db/lib/derbyrun.jar:. query.QueryTest
java -jar /opt/jdk1.8.0/db/lib/derbyrun.jar server shutdown

Use database query results

Result Sets

A query yields a ResultSet:

ResultSet rs = stat.executeQuery("SELECT * FROM Books")

Use this loop to iterate over the rows:

while (rs.next())
{
   look at a row of the result set
}

To get at the columns of a row, use one of the get methods:

String isbn = rs.getString(1); // The first (!) column
double price = rs.getDouble("Price");

Prepared Statements

When a query has variable parts, you don't want to formulate it through string concatenation:

String query = "SELECT * FROM Books WHERE Books.Title = " + title;
    // Don't—or you may become the victim of SQL injection

Instead, use a prepared statement:

String query = "SELECT * FROM Books WHERE Books.Title = ?";
PreparedStatement stat = conn.prepareStatement(query);
stat.setString(1, title);
ResultSet rs = stat.executeQuery();

A PreparedStatement becomes invalid after closing the Connection that created it.
- But the database will cache the query plan.

SQL Escapes

The “escape” syntax supports features for which database syntax varies.

Specify date and time literals as:

{d '2008-01-24'}
{t '23:59:59'}
{ts '2008-01-24 23:59:59.999'}

To call a scalar function (that is, a function returning a single value), embed the standard function name and arguments like this:
```
{fn left(?, 20)}
{fn user()}
```

Here is how to call a stored procedure:

{call PROC1(?, ?)}
{call PROC2}
{? = call PROC3(?)}

The {oj ...} escape lets you formulate outer joins in a database-independent way.
You can specify an escape character, for example, to match all titles containing a literal %:
```
... WHERE Books.title LIKE %!%% {escape '!'}
```

Retrieving Autogenerated Values

Most databases support some mechanism for automatically generating values.
- MySQL: Id INTEGER AUTO_INCREMENT
- Derby: Id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)

You can retrieve the autogenerated values that were generated by an INSERT statement:

stat.executeUpdate(insertStatement, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stat.getGeneratedKeys();
if (rs.next()) {
    int key = rs.getInt(1);
    Do something with key
}

Multiple Results

It is possible for a query to return multiple result sets or update counts.
- This can happen with stored procedures.
- Some databases allow you to submit multiple SELECT statements together.

Use the following loop to analyze the query result:

boolean isResult = stat.execute(command);
boolean done = false;
while (!done) {
   if (isResult) {
      ResultSet result = stat.getResultSet();
      do something with result
   } else {
      int updateCount = stat.getUpdateCount();
      if (updateCount >= 0)
         do something with updateCount
      else
         done = true;
   }
   if (!done) isResult = stat.getMoreResults();
}

Scrollable Result Sets

The ResultSet.next method iterates over the rows of a result set.
If you want to display query results in a table, you would like to be able to go back and forth in the result set.
A scrollable result set allows you to move the current row by arbitrary amounts and to arbitrary positions.
A scrollable result set can be updatable: you can set the column values, and the changes are reflected in the database.

Construct a scrollable or updatable result set like this:

Statement stat = conn.createStatement(type, concurrency);
PreparedStatement stat = conn.prepareStatement(command, type, concurrency);

Value	Explanation
`TYPE_FORWARD_ONLY`	The result set is not scrollable (default).
`TYPE_SCROLL_INSENSITIVE`	The result set is scrollable but not sensitive to database changes.
`TYPE_SCROLL_SENSITIVE`	The result set is scrollable and sensitive to database changes.
`CONCUR_READ_ONLY`	The result set cannot be used to update the database (default).
`CONCUR_UPDATABLE`	The result set can be used to update the database.

Scrolling and Updating Result Sets

To scroll within the result set, call:

rs.previous()
rs.relative(n)
rs.absolute(n)

The getCurrentRow method yields the current row number (starting at 1) or 0 if the cursor is before the first or after the last row.

If the result set is updatable, call one of the update methods to change a column value:

double price = rs.getDouble("Price");
rs.updateDouble("Price", price + increase);
rs.updateRow(); // Call updateRow to store the changes

To insert a new row, call:

rs.moveToInsertRow();
call rs.updateXxx(...)
rs.insertRow();
rs.moveToCurrentRow();

The ResultSet.deleteRow method removes the row from both the result set and the database.
Updatable result sets are intended for table editing by end users. If you want to update the database programmatically, use SQL queries.

Cached Row Sets

Updatable result sets require a connection to the database.
Connections are scarce resources.
A cached row set is like an updatable result set, but it can be disconnected from the database.

Obtain a cached row set like this:

RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet crs = factory.createCachedRowSet();

Then populate it with a result set:

ResultSet result = . . .;
crs.populate(result);
conn.close(); // now OK to close the database connection

Now you can view and update the row set. When you are done, call crs.acceptChanges(conn), passing a new Connection object.
Alternatively, you can let the cached row set establish a connection automatically as needed. First, set up the connection parameters:
```
crs.setURL("jdbc:derby://localhost:1527/COREJAVA");
crs.setUsername("app");
crs.setPassword("secret");
```

Then set the query statement and any parameters, and execute the query:

crs.setCommand("SELECT * FROM Books WHERE Title = ?");
crs.setString(1, title);
crs.execute();

When you are done updating the row set, you can call crs.acceptChanges() without a Connection parameter.

Group SQL statements into transactions and batches

Transactions

You can group a set of statements to form a transaction.
When all has gone well, the transaction is committed.
If something goes wrong, the transaction is rolled back, as if none of its statement had been issued.
By default, a connection is in “autocommit” mode: Each statement is committed as it is executed.

In order to use transactions, first turn off autocommit. Then execute any number of statements and commit your work:

conn.setAutoCommit(false);
Statement stat = conn.createStatement();
stat.executeUpdate(command1);
stat.executeUpdate(command2);
...
conn.commit();

However, if an error occurred, call:
```
conn.rollback();
```

Batch Updates

Suppose an application populates a database with many INSERT statements.
You can improve performance with a batch update.

Instead of calling executeUpdate, call the addBatch method:

String command = "CREATE TABLE . . ."
stat.addBatch(command);
while (...) {
    command = "INSERT INTO . . . VALUES (" + . . . + ")";
    stat.addBatch(command);
}

Finally, submit the entire batch:

int[] counts = stat.executeBatch();
    // Yields row counts for all submitted statements

Access database metadata

Metadata

Metadata are data that describe the database or its parts.
First get a DatabaseMetaData object from a connection:
```
DatabaseMetaData meta = conn.getMetaData();
```

This call yields a result set that lists all tables in the database:

ResultSet mrs = meta.getTables(null, null, null, new String[] { "TABLE" });

As it happens, the third column is the table name:

while (mrs.next())
    System.out.println(mrs.getString(3));

There are many methods to disclose how the database driver behaves under conditions that are not standardized, such as:
```
meta.supportsCatalogsInPrivilegeDefinitions()
meta.nullPlusNonNullIsNull()
```

Result Set Metadata

The ResultSetMetaData interface has methods to describe a result set.
This is particularly useful for generic tools that work with arbitrary tables.

Here we find out column sizes and column labels, useful information for displaying a result:

ResultSet rs = stat.executeQuery("SELECT * FROM " + tableName);
ResultSetMetaData meta = rs.getMetaData();
for (int i = 1; i <= meta.getColumnCount(); i++) {
    String columnName = meta.getColumnLabel(i);
    int columnWidth = meta.getColumnDisplaySize(i);
    ...
}

lesson09/query

java -jar /opt/jdk1.8.0/db/lib/derbyrun.jar server start &
java -classpath /opt/jdk1.8.0/db/lib/derbyrun.jar:. view.ViewDB
java -jar /opt/jdk1.8.0/db/lib/derbyrun.jar server shutdown