Transcript JDBC basics lecture

Object-Oriented Enterprise
Application Development
Introduction to JDBC
Topics
During this class we will examine:
What JDBC is and isn't
Installing JDBC
The JDBC Object Model
Transaction Control
JDBC Introduction
JDBC Fact and Fiction
Contrary to popular belief, JDBC is not an
acronym for anything.
Nevertheless it is often interpreted as standing
for Java Data Base Connectivity.
JDBC is a Java API that is used to access
relational databases.
JDBC Goals
The goal of JDBC is to provide a consistent
interface that an application can use to
perform data access.
This allows the data source to be changed
without requiring significant re-work of
existing code.
Common JDBC Tasks
We can use JDBC to perform common
database access tasks such as:
Establish connections
Send SQL requests
Process SQL results
Transaction control
This class assumes a working knowledge of
relational database concepts and SQL.
Architecture
Web Server
Application Server
JDBC
Data Source
The web server acts as
our front end.
The application server
uses JDBC to access a
back-end data source.
The back-end data
source can be any kind
of data source that
supports JDBC.
Versions
The current version of JDBC is 2.0.
For this class we'll use version 1.0.
There are very few changes between the
revisions that impact the mechanics we'll be
discussing.
The biggest change is in the way that
connections to the database are established.
JDBC & Java
Required Packages
JDBC is part of the JDK available from
Sun.
The only package required to use JDBC is
java.sql.*.
You'll find, with few exceptions, that all of
the JDBC elements we use are interfaces
and not classes.
Common Interfaces
We won't use all of the interfaces provided
by JDBC in this class. We'll focus on the
most critical:
DriverManager
Connection
Statement
PreparedStatement
ResultSet
Tasks
There is a consistent set of steps to be
followed when writing an application that
accesses a data source:
Connect to the data source
Manipulate the data source
Disconnect from the data source
While conceptually these are very simple
tasks, care must be taken to do them well.
Database Connectivity
Database Connectivity Evolution
The process of connecting to a database has
undergone a gradual evolution:
Native API
ODBC
JDBC
All database connectivity takes place
through the use of something called a
driver.
Native API
The most efficient
connections use the
native database API.
This is the fastest
approach but the least
portable.
Native API
If we move to a new
database we need to
modify our code base.
ODBC
ODBC was created to
"wrap" each vendor's
native API within a
common interface.
ODBC
Native API
Code was written to
use ODBC rather than
the native API.
This was less efficient
but more portable.
JDBC
JDBC is similar to
ODBC in that it wraps
a vendor's native API.
JDBC
Native API
The JDBC object
model is much simpler
than ODBC or most
native APIs.
Because it's Javabased, it's portable.
JDBC-ODBC Bridge
JDBC
ODBC
Native API
For this class we'll use
the JDBC-ODBC
Bridge driver.
This is the driver
shipped with the JDK
from Sun.
It isn't very efficient,
but it's free and easy
to install.
Configure ODBC
The first step is to configure ODBC with an
appropriate Data Source Name or DSN.
The process for creating this DSN is
outlined in the ODBC document available
on the course web site.
For this course, please use a DSN of se452
for all of your assignments.
Loading a JDBC Driver
The next step is to load an appropriate
JDBC driver.
To do this, we force the JVM to load the
driver using the forName() method of the
Class class:
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
Opening a Connection
(1 of 4)
The next step is to open a connection to the
database using the driver that was just
loaded.
This step must be completed before any
other work can be performed against the
database by our application.
The connection to the database is held by an
instance of the Connection interface.
Opening a Connection
(2 of 4)
We create a physical connection to the data
source by using the getConnection()
method of the DriverManager class:
Connection conn = DriverManager.
getConnection(String URL,
String UID,
String PWD);
Opening a Connection
(3 of 4)
The following code segment connects the
application to an ODBC data source name
called se452 with no user id or password:
Connection conn = DriverManager.
getConnection("jdbc:odbc:se452",
"",
"");
Opening a Connection
(4 of 4)
Creating database connections is an
expensive process requiring significant
database resources.
We typically create connections as late in
our processing as possible and close them as
soon as we can to minimize our resource
usage against the database.
Sample Code – Connect
(1 of 2)
1. import java.sql.*;
2. public class Connect {
3.
Connection conn = null;
4.
public static
void main(String [] args) {
5.
Connect myConnect = new Connect();
6.
myConnect.doConnect();
7.
}
Sample Code – Connect
(2 of 2)
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18. }
public void doConnect() {
try {
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection(
"jdbc:odbc:se452", "", "" );
}
catch (ClassNotFoundException clfe) {
}
catch (SQLException sqle) {
}
}
Error Detection
Notice that the loading of the JDBC driver
and the creation of the connection are
performed within a try…catch block.
The Class.forName() method can
generate a ClassNotFoundException.
All JDBC operations can result in a
SQLException.
Closing a Connection
(1 of 2)
Database connections consume resources on
both the client and the database server.
We need to close the open connections in a
to ensure that these resources are returned to
the client or database in a timely manner.
Do not wait for the garbage collector to free
these resources for you.
Closing a Connection
(2 of 2)
We don't need to do anything to close the
DriverManager.
However, every connection that was opened
using the getConnection() method
must be closed using the close() method
on the Connection interface:
conn.close();
Sample Code – Connect
(1 of 3)
1. import java.sql.*;
2. public class Connect {
3.
Connection conn = null;
4.
public static
void main(String [] args) {
5.
Connect myConnect = new Connect();
6.
myConnect.doConnect();
7.
// do stuff
8.
myConnect.doDisconnect();
9.
}
Sample Code – Connect
(2 of 3)
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
public void doConnect() {
try {
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver" );
conn = DriverManager.getConnection(
"jdbc:odbc:se452", "", "" );
}
catch (ClassNotFoundException clfe) {
}
catch (SQLException sqle) {
}
}
Sample Code – Connect
(3 of 3)
20.
public void doDisconnect() {
21.
try {
22.
conn.close();
23.
}
24.
catch (SQLException sqle) {
25.
}
26.
}
27. }
Making the Code More Robust
While the code I've show you is adequate as
written, it isn't really robust or user-friendly.
In this context a user is any other developer
who is using the code you are writing.
We're going to re-write the code so that it
demonstrates better software engineering
principles of maintainability and reusability.
Sample Code – Connect
(1 of 3)
1. import java.sql.*;
2. public class Connect {
3.
Connection conn = null;
4.
public static
void main(String [] args) {
5.
Connect myConnect = new Connect();
6.
myConnect.doConnect();
7.
// do stuff
8.
myConnect.doDisconnect();
9.
}
Sample Code – Connect (rev.)
(1 of 3)
1. import java.sql.*;
2. public class Connect {
3.
Connection conn = null;
4.
public static
void main(String [] args) {
5.
Connect myConnect = new Connect();
6.
try {
7.
myConnect.doConnect();
8.
// do stuff
9.
}
10.
finally {
11.
myConnect.doDisconnect();
12.
}
13. }
Sample Code – Connect
(2 of 3)
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
public void doConnect() {
try {
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver" );
conn = DriverManager.getConnection(
"jdbc:odbc:se452", "", "" );
}
catch (ClassNotFoundException clfe) {
}
catch (SQLException sqle) {
}
}
Sample Code – Connect (rev.)
(2 of 3)
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
public void doConnect() {
try {
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver" );
if (conn != null) {
this.doDisconnect();
}
conn = DriverManager.getConnection(
"jdbc:odbc:se452", "", "" );
}
catch (ClassNotFoundException clfe) {
}
catch (SQLException sqle) {
}
}
Sample Code – Connect
(3 of 3)
22.
public void doDisconnect() {
23.
try {
24.
conn.close();
25.
}
26.
catch (SQLException sqle) {
27.
}
28.
}
29. }
Sample Code – Connect (rev.)
(3 of 3)
29.
public void doDisconnect() {
30.
try {
31.
if (conn != null) {
32.
conn.close();
33.
conn = null;
34.
}
35.
}
36.
catch (SQLException sqle) {
37.
}
38.
}
39. }
Error Detection
In the sample code there are exceptions that
are caught but nothing is done about it.
Within those blocks I'd likely embed code
to display the stack trace of the exception so
an effective post-mortem could be done.
If you can't handle an exception, you
shouldn't catch it.
Retrieving Data
Select Statements
One we have a connection to the data
source, we can begin to issues queries and
process the results.
This requires three (3) new interfaces:
Statement
PreparedStatement
ResultSet
Tasks
To issue read requests to the data source we
will perform the following tasks:
Create the statement
Execute the statement
Process the results
Creating the Statement
A Statement object is used to send SQL
queries to the database.
It's created using a Connection object:
Statement stmt =
conn.createStatement();
Executing the Statement
(1 of 2)
Creating a Statement object doesn't itself
execute queries against the database.
To do this, we pass a SQL statement to the
database using the executeQuery()
method on that Statement object:
String SQL = "select * from STATE";
stmt.executeQuery( SQL );
Executing the Statement
(2 of 2)
The call to the executeQuery() method
returns a ResultSet object containing the
results of the query:
String SQL = "select * from STATE";
ResultSet rs =
stmt.executeQuery( SQL );
Processing the Result
Processing a ResultSet is similar to
processing a sequential file in that we
process each individual row until we hit the
end of the ResultSet object.
This loop is accomplished using the
next() method of the ResultSet:
while ( rs.next() ) {
…
}
Anatomy of a ResultSet
Start of ResultSet
Row 1
Row 2
…
Row n
A ResultSet is
nothing more than a
two-dimensional table.
There is a "pointer"
showing the current
row.
Each call to next()
moves this pointer to
the next row.
Columns
(1 of 2)
In addition to rows, each ResultSet
contains one column for each column
specified by the underlying select
statement.
Each column can be accessed by either its
name or relative position within the
ResultSet.
Columns
(2 of 2)
To retrieve a column's value for the current
row in the ResultSet we use one of
many column accessor methods.
Each accessor method is overloaded.
One variation accepts a string that corresponds
to the column's name.
One variation accepts an integer that
corresponds to the column's relative position.
Columns by Name
We can now construct code to print each
element within the ResultSet using the
column name:
while ( rs.next() ) {
String code =
rs.getString( "STATE_I" );
String name =
rs.getString( "STATE_M" );
}
Columns by Number
We can now construct code to print each
element within the ResultSet using the
column number:
while ( rs.next() ) {
String code = rs.getString(1);
String name = rs.getString(2);
}
Sample Code – Result
(1 of 4)
1. import java.sql.*;
2. public class Connect {
3.
Connection conn = null;
4.
public static
void main(String [] args) {
5.
Connect myConnect = new Connect();
6.
try {
7.
myConnect.doConnect();
8.
myConnect.doQuery();
9.
}
10.
finally {
11.
myConnect.doDisconnect();
12.
}
13. }
Sample Code – Result
(2 of 4)
14.
15.
28.
public void doConnect() {
// as before
}
29.
30.
38.
public void doDisconnect() {
// as before
}
Sample Code – Result
(3 of 4)
39.
40.
41.
42.
43.
44.
45.
public void doQuery() {
Statement stmt = null;
ResultSet rs = null;
try {
if (conn != null) {
stmt = conn.createStatement();
rs = stmt.executeQuery(
"select * from STATE" );
Sample Code – Result
(4 of 4)
46.
47.
48.
while ( rs.next() ) {
System.out.println( "Code: " +
rs.getString( "STATE_I" );
System.out.println( "Name: " +
rs.getString( "STATE_M" );
}
49.
50.
}
51.
}
52.
catch (SQLException sqle) {
53.
}
54. }
55. }
Closing a Statement
As with a Connection, always close a
Statement object. This ensures that the
appropriate cleanup is performed and the
resources held by that Statement are
released:
stmt.close();
Closing a ResultSet
As with Connection and Statement
objects, always close ResultSet objects.
This ensures that the appropriate cleanup is
performed and the resources held by that
ResultSet are released:
rs.close();
Making the Code More Robust
We're going to re-write the doQuery()
method so that it demonstrates better
software engineering principles by closing
all of the appropriate objects created during
its lifetime.
We'll accomplish this by using a
try…catch…finally block.
Sample Code – Result (rev.)
(1 of 2)
39.
40.
41.
42.
43.
51.
52.
53.
54.
55.
56.
57.
58.
public void doQuery() {
Statement stmt = null;
ResultSet rs = null;
try {
// all code as before
}
catch (SQLException sqle1) {
}
finally {
if ( rs != null ) {
try {
rs.close();
}
Sample Code – Result (rev.)
(2 of 2)
59.
catch (SQLException sqle2) {
60.
}
61.
}
62.
if ( stmt != null) {
63.
try {
64.
stmt.close();
65.
}
66.
catch (SQLException sqle3) {
67.
}
68.
}
69.
}
70.
}
71. }
Commonly Executed
Statements
Statements Revisited
Executing a SQL statement against the data
source involves many stages:
Validate the syntax
Validate permissions
Construct a query plan
Execute the query
Build the result
PreparedStatements
For commonly executed queries, or queries
that can be parameterized, we can use a
PreparedStatement object in place of
a Statement object.
A PreparedStatement can be executed
many times against the database without
incurring the overhead involved with syntax
checking and other validations.
Creating the PreparedStatement
A PreparedStatement object is
created in much the same way as a
Statement.
Instead of createStatement(), we use
the prepareStatement() method:
String SQL = "select * from STATE";
PreparedStatement stmt =
conn.prepareStatement( SQL );
Executing the PreparedStatement
Creating a PreparedStatement object
doesn't execute it against the database.
As with the Statement object we can
execute the PreparedStatement and
capture its ResultSet:
ResultSet rs =
stmt.executeQuery();
Parameters
(1 of 3)
Sometimes a given SQL statement only
changes in the values that we pass to the
database for processing.
For instance we might execute a query for
states with names beginning with 'S' and then
later for states with names beginning with 'T'.
PreparedStatements allow this kind
of query using parameters.
Parameters
(2 of 3)
To indicate that a given value is a
parameter, we simple embed a '?' in the
SQL statement where the argument will
eventually be provided.
You can only use parameters for values in
where and having clauses. You cannot
dynamically alter the columns, tables,
grouping, or sort order of the query.
Parameters
(3 of 3)
To construct a query that will change based
on the value of the state's code we can
construct a parameterized SQL statement:
String SQL = "select * from STATE " +
"where STATE_I like ?";
PreparedStatement stmt =
conn.prepareStatement( SQL );
Executing Parameterized
PreparedStatements
Before we can execute a parameterized
PreparedStatement, we first have to
provide values for the parameters:
stmt.setString( 1, "I%" );
ResultSet rs =
stmt.executeQuery();
Sample Code – Prepared
(1 of 2)
39.
40.
41.
42.
43.
44.
45.
46.
47.
public void doQuery( String arg ) {
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String SQL = "select * from STATE"+
" where STATE_I like ?";
stmt = conn.prepareStatement(SQL);
stmt.setString( 1, arg );
rs = stmt.executeQuery();
}
Sample Code – Prepared
(2 of 2)
48.
49.
50.
51.
52.
53.
catch (SQLException sqle1) {
}
finally {
// all code here as before
}
}
Writing to the Database
Other Queries
So far we've only looked at select
statements.
We can also use the Statement and
PreparedStatements interfaces to
write to the database using insert,
update, and delete statements.
Insert Statement
String SQL = "insert into STATE " +
"values ('OH', 'Ohio')";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate( SQL );
Insert PreparedStatement
String SQL = "insert into STATE " +
"values (?, ?)";
PreparedStatement stmt =
conn.prepareStatement( SQL );
stmt.setString( 1, 'OH' );
stmt.setString( 2, 'Ohio' );
int count = stmt.executeUpdate();
Update Statement
String SQL = "update STATE " +
"set STATE_M = 'Skippy' " +
"where STATE_I = 'IL'";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate( SQL );
Update PreparedStatement
String SQL = "update STATE " +
"set STATE_M = ? " +
"where STATE_I = ?";
PreparedStatement stmt =
conn.prepareStatement( SQL );
stmt.setString( 1, 'Skippy' );
stmt.setString( 2, 'IL' );
int count = stmt.executeUpdate();
Delete Statement
String SQL = "delete from STATE " +
"where STATE_I = 'IL'";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate( SQL );
Delete PreparedStatement
String SQL = "delete from STATE " +
"where STATE_I = ?";
PreparedStatement stmt =
conn.prepareStatement( SQL );
stmt.setString( 1, 'IL' );
int count = stmt.executeUpdate();
Transaction Control
Transactions Defined
Transactions are used to ensure that
multiple SQL insert, update, and
delete statements are completed as a
single unit.
Either all of the statements in the unit
complete successfully, or none of the
changes are applied.
Starting a Transaction
By default most databases are configured
such that each individual SQL statement
executes within its own transaction.
To alert the database that multiple
statements will be a part of the transaction
we need to set the auto-commit property of
the Connection object:
conn.setAutoCommit( false );
Ending a Transaction
(1 of 2)
To end a transaction you must instruct the
Connection object to perform only one
(1) of two (2) actions:
Commit: The changes made to the database are
saved.
Rollback: The changes made to the database
are discarded.
Once a transaction has ended, a new one is
begun automatically.
Ending a Transaction
(2 of 2)
To commit or rollback a transaction, simply
invoke the appropriate method on the
Connection object:
conn.commit()
conn.rollback();
You should always commit or rollback a
transaction as soon as possible to release the
resources it is using.
Review
During this class we have discussed:
What JDBC is and isn't
Installing JDBC
The JDBC Object Model
Transaction Control
Resources
JDBC Database Access with Java
Graham Hamilton, Rick Cattell, Maydene
Fisher, Addison-Wesley, 1997.
ISBN: 0-201-30995-5
Core Servlets and JavaServer Pages
Marty Hall, Prentice-Hall, Inc., 2000.
pp.460-497.
ISBN: 0-13-089340-4
Coming Attractions
Next week we'll look at JDBC 2.0.