Слайд 1Lesson 13
Building Database Applications with JDBC
Слайд 2Objectives
After completing this lesson, you should be able to:
Define the
layout of the JDBC API
Connect to a database by using
a JDBC driver
Submit queries and get results from the database
Specify JDBC driver information externally
Use transactions with JDBC
Use the JDBC 4.1 RowSetProvider and RowSetFactory
Use a Data Access Object Pattern to decouple data and business methods
Слайд 4Using a Vendor’s Driver Class
The DriverManager class is used to
get an instance of a Connection object, using the JDBC
driver named in the JDBC URL:
String url = "jdbc:derby://localhost:1527/EmployeeDB";
Connection con = DriverManager.getConnection (url);
The URL syntax for a JDBC driver is:
jdbc::[subsubprotocol:][databaseName][;attribute=value]
Each vendor can implement its own subprotocol.
The URL syntax for an Oracle Thin driver is:
jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
Example:
jdbc:oracle:thin:@//myhost:1521/orcl
Слайд 5Key JDBC API Components
Each vendor’s JDBC driver class also implements
the key API classes that you will use to connect
to the database, execute queries, and manipulate data:
java.sql.Connection: A connection that represents the session between your Java application and the database
Connection con = DriverManager.getConnection(url, username, password);
java.sql.Statement: An object used to execute a static SQL statement and return the result
Statement stmt = con.createStatement();
java.sql.ResultSet: A object representing a database result set
String query = "SELECT * FROM Employee";
ResultSet rs = stmt.executeQuery(query);
Слайд 6Using a ResultSet Object
String query = "SELECT * FROM
Employee";
ResultSet rs = stmt.executeQuery(query);
ResultSet cursor
rs.next()
rs.next()
rs.next()
rs.next()
rs.next()
null
The last next() method
invocation returns false, and the rs instance is now null.
The first next() method invocation returns true, and rs points to the first row of data.
Слайд 7Putting It All Together
package com.example.text;
import java.sql.DriverManager;
import
java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class SimpleJDBCTest {
public static void main(String[] args) {
String url = "jdbc:derby://localhost:1527/EmployeeDB";
String username = "public";
String password = "tiger";
String query = "SELECT * FROM Employee";
try (Connection con =
DriverManager.getConnection (url, username, password);
Statement stmt = con.createStatement ();
ResultSet rs = stmt.executeQuery (query)) {
The hard-coded JDBC URL, username, and password is just for this simple example.
Слайд 8Putting It All Together
while (rs.next())
{
int empID =
rs.getInt("ID");
String first = rs.getString("FirstName");
String last = rs.getString("LastName");
Date birthDate = rs.getDate("BirthDate");
float salary = rs.getFloat("Salary");
System.out.println("Employee ID: " + empID + "\n"
+ "Employee Name: " + first + " " + last + "\n"
+ "Birth Date: " + birthDate + "\n"
+ "Salary: " + salary);
} // end of while
} catch (SQLException e) {
System.out.println("SQL Exception: " + e);
} // end of try-with-resources
}
}
Loop through all of the rows in the ResultSet.
Слайд 9Writing Portable JDBC Code
The JDBC driver provides a programmatic “insulating”
layer between your Java application and the database. However, you
also need to consider SQL syntax and semantics when writing database applications.
Most databases support a standard set of SQL syntax and semantics described by the American National Standards Institute (ANSI) SQL-92 Entry-level specification.
You can programmatically check for support for this specification from your driver:
Connection con = DriverManager.getConnection(url, username, password);
DatabaseMetaData dbm = con.getMetaData();
if (dbm.supportsANSI92EntrySQL()) {
// Support for Entry-level SQL-92 standard
}
Слайд 10The SQLException Class
SQLException can be used to report details about
resulting database errors. To report all the exceptions thrown, you
can iterate through the SQLExceptions thrown:
catch(SQLException ex) {
while(ex != null) {
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("Error Code:" + ex.getErrorCode());
System.out.println("Message: " + ex.getMessage());
Throwable t = ex.getCause();
while(t != null) {
System.out.println("Cause:" + t);
t = t.getCause();
}
ex = ex.getNextException();
}
}
Vendor-dependent state codes, error codes and messages
Слайд 11Closing JDBC Objects
Connection
Statement
Statement
ResultSet
ResultSet
close()
Connection
Closes Statements
Invalidates ResultSets
Resources not
released until
next GC
One Way
Better Way
close()
Resources
released
close()
close()
Call close explicitly or
in try-with-resources
Слайд 12The try-with-resources Construct
Given the following try-with-resources statement:
try (Connection con =
DriverManager.getConnection(url, username, password);
Statement stmt
= con.createStatement();
ResultSet rs = stmt.executeQuery (query)){
The compiler checks to see that the object inside the parentheses implements java.lang.AutoCloseable.
This interface includes one method: void close().
The close method is automatically called at the end of the try block in the proper order (last declaration to first).
Multiple closeable resources can be included in the try block, separated by semicolons.
Слайд 13try-with-resources: Bad Practice
It might be tempting to write try-with-resources more
compactly:
try (ResultSet rs = DriverManager.getConnection(url, username, password).createStatement().executeQuery(query)) {
However, only the
close method of ResultSet is called, which is not a good practice.
Always keep in mind which resources you need to close when using try-with-resources.
Слайд 14Writing Queries and Getting Results
To execute SQL queries with JDBC,
you must create a SQL query wrapper object, an instance
of the Statement object.
Statement stmt = con.createStatement();
Use the Statement instance to execute a SQL query:
ResultSet rs = stmt.executeQuery (query);
Note that there are three Statement execute methods:
Слайд 15ResultSetMetaData
There may be a time where you need to dynamically
discover the number of columns and their type.
int numCols
= rs.getMetaData().getColumnCount();
String [] colNames = new String[numCols];
String [] colTypes = new String[numCols];
for (int i= 0; i < numCols; i++) {
colNames[i] = rs.getMetaData().getColumnName(i+1);
colTypes[i] = rs.getMetaData().getColumnTypeName(i+1);
}
System.out.println ("Number of columns returned: " + numCols);
System.out.println ("Column names/types returned: ");
for (int i = 0; i < numCols; i++) {
System.out.println (colNames[i] + " : " + colTypes[i]);
}
Note that these methods are indexed from 1, not 0.
Слайд 16Getting a Row Count
A common question when executing a query
is: “How many rows were returned?”
public int rowCount(ResultSet rs)
throws SQLException{
int rowCount = 0;
int currRow = rs.getRow();
// Valid ResultSet?
if (!rs.last()) return -1;
rowCount = rs.getRow();
// Return the cursor to the current position
if (currRow == 0) rs.beforeFirst();
else rs.absolute(currRow);
return rowCount;
}
To use this technique, the ResultSet must be scrollable.
Move the cursor to the last row, this method returns false if the ResultSet is empty.
Returning the row cursor to its original position before the call is a good practice.
Слайд 17Controlling ResultSet Fetch Size
By default, the number of rows fetched
at one time by a query is determined by the
JDBC driver. You may wish to control this behavior for large data sets.
For example, if you wanted to limit the number of rows fetched into cache to 25, you could set the fetch size:
rs.setFetchSize(25);
Calls to rs.next() return the data in the cache until the 26th row, at which time the driver will fetch another 25 rows.
Слайд 18Using PreparedStatement
PreparedStatement is a subclass of Statement that allows you
to pass arguments to a precompiled SQL statement.
double value
= 100_000.00;
String query = "SELECT * FROM Employee WHERE Salary > ?";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setDouble(1, value);
ResultSet rs = pStmt.executeQuery();
In this code fragment, a prepared statement returns all columns of all rows whose salary is greater than $100,000.
PreparedStatement is useful when you have a SQL statements that you are going to execute multiple times.
Substitutes value for the first parameter in the prepared statement.
Parameter for substitution.
Слайд 19Using CallableStatement
A CallableStatement allows non-SQL statements (such as stored procedures)
to be executed against the database.
CallableStatement cStmt
= con.prepareCall("{CALL EmplAgeCount (?, ?)}");
int age = 50;
cStmt.setInt (1, age);
ResultSet rs = cStmt.executeQuery();
cStmt.registerOutParameter(2, Types.INTEGER);
boolean result = cStmt.execute();
int count = cStmt.getInt(2);
System.out.println("There are " + count +
" Employees over the age of " + age);
Stored procedures are executed on the database.
The IN parameter is passed in to the stored procedure.
The OUT parameter is returned from the stored procedure.
Слайд 20What Is a Transaction?
A transaction is a mechanism to handle
groups of operations as though they were one.
Either all operations
in a transaction occur or none occur
at all.
The operations involved in a transaction might rely on one or more databases.
Слайд 21ACID Properties of a Transaction
A transaction is formally defined by
the set of properties that is known by the acronym
ACID.
Atomicity: A transaction is done or undone completely. In the event of a failure, all operations and procedures are undone, and all data rolls back to its previous state.
Consistency: A transaction transforms a system from one consistent state to another consistent state.
Isolation: Each transaction occurs independently of other transactions that occur at the same time.
Durability: Completed transactions remain permanent, even during system failure.
Слайд 22Transferring Without Transactions
Successful transfer (A)
Unsuccessful transfer (Accounts are left in
an inconsistent state.) (B)
$1000
+$100
$1100
$500
-$100
$400
2) Deposit: $100
1) Withdraw: $100
Transfer:
$100
From: Acct 1
To: Acct 2
ATM
Account 1
Account 2
$1000
$500
-$100
$400
1) Withdraw: $100
Transfer: $100
From: Acct 1
To: Acct 2
ATM
A
B
Failed
Deposit
Bank
Bank
Account 1
Account 2
Слайд 23Successful Transfer with Transactions
Changes within a transaction are buffered. (A)
If
a transfer is successful, changes are committed (made permanent). (B)
$1100
$400
Commit
Transfer
Successful
Commit
$1000
+$100
$1100
$500
-$100
$400
2) Deposit: $100
1) Withdraw: $100
Transfer: $100
From: Acct 1
To: Acct 2
ATM
Account 1
Account 2
A
Bank
Transaction Started by Bank
Account 1
Account 2
ATM
B
Bank
Transaction Started by Bank
Слайд 24Unsuccessful Transfer with Transactions
Changes within a transaction are buffered. (A)
If
a problem occurs, the transaction is rolled back to the
previous consistent state. (B)
$500
-$100
$400
1) Withdraw: $100
Transfer: $100
From: Acct 1
To: Acct 2
ATM
Account 1
Account 2
A
Bank
Transaction Started by Bank
$1000
Failed
Deposit
$1000
$500
Rollback
Error Message
Rollback
ATM
B
Bank
Transaction Started by Bank
Account 1
Account 2
Слайд 25JDBC Transactions
By default, when a Connection is created, it is
in auto-commit mode.
Each individual SQL statement is treated as a
transaction and automatically committed after it is executed.
To group two or more statements together, you must disable auto-commit mode.
con.setAutoCommit (false);
You must explicitly call the commit method to complete the transaction with the database.
con.commit();
You can also programmatically roll back transactions in the event of a failure.
con.rollback();
Слайд 26RowSet 1.1: RowSetProvider and RowSetFactory
The JDK 7 API specification introduces
the new RowSet 1.1 API. One of the new features
of this API is RowSetProvider.
javax.sql.rowset.RowSetProvider is used to create a RowSetFactory object:
myRowSetFactory = RowSetProvider.newFactory();
The default RowSetFactory implementation is:
com.sun.rowset.RowSetFactoryImpl
RowSetFactory is used to create one of the RowSet 1.1 RowSet object types.
Слайд 27Using RowSet 1.1 RowSetFactory
RowSetFactory is used to create instances of
RowSet implementations:
Слайд 28Example: Using JdbcRowSet
try (JdbcRowSet jdbcRs =
RowSetProvider.newFactory().createJdbcRowSet()) {
jdbcRs.setUrl(url);
jdbcRs.setUsername(username);
jdbcRs.setPassword(password);
jdbcRs.setCommand("SELECT * FROM Employee");
jdbcRs.execute();
// Now just treat JDBC Row Set like a ResultSet object
while (jdbcRs.next()) {
int empID = jdbcRs.getInt("ID");
String first = jdbcRs.getString("FirstName");
String last = jdbcRs.getString("LastName");
Date birthDate = jdbcRs.getDate("BirthDate");
float salary = jdbcRs.getFloat("Salary");
}
//... other methods
}
Слайд 29Data Access Objects
Consider an employee table like the one in
the sample JDBC code.
By combining the code that accesses the
database with the “business” logic, the data access methods and the Employee table are tightly coupled.
Any changes to the table (such as adding a field) will require a complete change to the application.
Employee data is not encapsulated within the example application.
Слайд 30The Data Access Object Pattern
1
2
3
Слайд 31Summary
In this lesson, you should have learned how to:
Define the
layout of the JDBC API
Connect to a database by using
a JDBC driver
Submit queries and get results from the database
Specify JDBC driver information externally
Use transactions with JDBC
Use the JDBC 4.1 RowSetProvider and RowSetFactory
Use a Data Access Object Pattern
to decouple data and business methods
Слайд 32Quiz
Which Statement method executes a SQL statement and returns the
number of rows affected?
stmt.execute(query);
stmt.executeUpdate(query);
stmt.executeQuery(query);
stmt.query(query);
Слайд 33Quiz
When using a Statement to execute a query that returns
only one record, it is not necessary to use the
ResultSet's next() method.
True
False
Слайд 34Quiz
The following try-with-resources statement will properly close the JDBC resources:
try (Statement stmt = con.createStatement();
ResultSet rs =
stmt.executeQuery(query)){
//...
} catch (SQLException s) {
}
True
False
Слайд 35Quiz
Given:
String[] params = {"Bob", "Smith"};
String query = "SELECT
itemCount FROM Customer " +
"WHERE lastName='?' AND firstName='?'";
try (PreparedStatement pStmt = con.prepareStatement(query)) {
for (int i = 0; i < params.length; i++)
pStmt.setObject(i, params[i]);
ResultSet rs = pStmt.executeQuery();
while (rs.next()) System.out.println (rs.getInt("itemCount"));
} catch (SQLException e){ }
Assuming there is a valid Connection object and the SQL query will produce at least one row, what is the result?
Each itemCount value for customer Bob Smith
Compiler error
A run time error
A SQLException