Java Database Connectivity

JDBC
Java Database Connectivity
1
Resources used for this presentation
http://www.cs.huji.ac.il/~dbi/recitations/JDBC-PSQL-c.p
df
http://java.sun.com/docs/books/tutorial/jdbc/
http://www.java-samples.com/showtutorial.php?tutorial
id=202
2
Today’s Menu
JDBC Architecture
Using JDBC
Timeout
ResultSet Object
Null Values
Transactions
3
JDBC (Java Database Connectiveity) is an API
(Application Programming Interface)
 That is, a collection of classes and interfaces
JDBC is used for accessing databases from Java

applications
Information is transferred from relations to

objects and vice-versa
4
JDBC Architecture
5
JDBC Architecture
Java code calls JDBC library

JDBC loads a driver
The driver talks to a particular DBMS
An application can work with several DBMS
by using corresponding drivers
6
“Movies” Relation
moviename producer releasedate
Movie1 Producer1 1.1.2000
7
7 Steps for Using JDBC
1. Load the driver
2. Define the connection URL
3. Establish the connection
4. Create a Statement object
5. Execute a query using the Statement
6. Process the result
7. Close the connection
8
1. Loading the Driver
Class.forName(“com.mysql.jdbc.Driver ”);
Class.forName loads the given class dynamically
When the driver is loaded, it automatically
 creates an instance of itself
 registers this instance within DriverManager
Another way:
Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);
MySql JDBC driver can be downloaded from
here.
10
2. Define the connection URL
Every database is identified by a URL
Given a URL, DriverManager looks for the
driver that can talk to the corresponding
database
DriverManager tries all registered drivers,until
a suitable one is found
12
An Example
// A driver for imaginary1
Class.forName("ORG.img.imgSQL1.imaginary1Driver");
// A driver for imaginary2
Driver driver = new
ORG.img.imgSQL2.imaginary2Driver();
DriverManager.registerDriver(driver);
//A driver for PostgreSQL
Class.forName("org.postgresql.Driver");
14
3. Establish the connection
Connection con =
DriverManager.getConnection("jdbc:imaginaryDB1");
15
4. Create a Statement object
We use Statement objects in order to
 Query the DB
 Update the db(insert, update, create, drop, …)
executeQuery returns a ResultSet object

representing the query result (discussed later…)
17
5. Execute a query using the Statement
executeQuery returns a ResultSet

object representing the query result
(discussed later…)
19
Manipulating DB with Statement
executeUpdate is for data manipulation:

insert, delete, update, create table, etc.
executeUpdate returns the number of rows
modified (or 0 for DDL commands)
21
6. Process the result
We will discuss ResultSet in a while…
22
7. Close the connection
Close Connections, Statements, and
Result Sets
 con.close();
 stmt.close();
 rs.close()
24
ResultSet
ResultSet objects provide access to the
tables generated as results of executing
Statement queries.
Only one ResultSet per Statement can be
open at a given time!
The table rows are retrieved in sequence:
 A ResultSet maintains a cursor pointing to
its current row.

 next() moves the cursor to the next row
26
ResultSet Methods
boolean next()
 Activates the next row
 First call to next() activates the first row
 Returns false if there are no more rows
 Not all of the next calls actually involve the DB
void close()
 Disposes of the ResultSet
 Allows to re-use the Statement that created it
 Automatically called by most Statement methods
Type getType(int columnIndex)

 Returns the given field as the given type
 Indices start at 1 and not 0!
 Add the column name as a comment if it is known!
Type getType(String columnName)

 Same, but uses name of field
int findColumn(String columnName)

 Looks up column index given column name 27
Timeout
Use setQueryTimeOut(int seconds) of
Statement to set a timeout for the driver to
wait for a query to be completed.
If the operation is not completed in the given
time, an SQLException is thrown
What is it good for?
28
Mapping Java Types to SQL Types
29
Null Values
In SQL, NULL means the field is empty
 Not the same as 0 or “”!
In JDBC, you must explicitly ask if the last

read field was null
 ResultSet.wasNull(column)
For example, getInt(column) will return 0 if

the value is either 0 or NULL!
30
Database Time
Times in SQL are notoriously non-standard
Java defines three classes to help
java.sql.Date
 year, month, day
java.sql.Time
 hours, minutes, seconds
java.sql.Timestamp
 year, month, day, hours, minutes, seconds,
nanoseconds
 Usually use this one
31
Exceptions
An SQLException is actually a list of
exceptions
32
Prepared Statements
The PreparedStatement object contains not
just an SQL statement, but an SQL statement
that has been precompiled.
 This means that when the PreparedStatement is
executed, the DBMS can just run the
PreparedStatement SQL statement without having
to compile it first.
Most often used for SQL statements that take
parameters.
33
Creating a PreparedStatement Object
As with Statement objects, you create

PreparedStatement objects with a
Connection method.
The following code create a
PreparedStatement object that takes
two input parameters:
34
Supplying Values for
PreparedStatement Parameters
You need to supply values to be used

in place of the question mark
placeholders (if there are any) before
you can execute a PreparedStatement
object. You do this by calling one of the
setXXX methods defined in the
PreparedStatement class.
35
Example
the following line of code sets the first question mark
placeholder to a Java int with a value of 75:
 updateSales.setInt(1, 75);
The next example sets the second placeholder

parameter to the string " Colombian":
 updateSales.setString(2, "Colombian");
36
Another Example
37
Callable Statements
Execute a call to a database stored
procedure.
38

Java Database Connectivity

Uploaded by

Copyright:

Available Formats

Java Database Connectivity

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Java Database Connectivity

Uploaded by

Copyright:

Available Formats

JDBC

Java Database Connectivity

JDBC is used for accessing databases from Java

Information is transferred from relations to

Java code calls JDBC library

Movie1 Producer1 1.1.2000

Movie2 Producer2 1.1.2001

Movie3 Producer3 3.4.2003

 registers this instance within DriverManager

 Update the db(insert, update, create, drop, …)

executeQuery returns a ResultSet object

executeQuery returns a ResultSet

executeUpdate is for data manipulation:

its current row.

 First call to next() activates the first row

 Returns false if there are no more rows

 Not all of the next calls actually involve the DB

 Allows to re-use the Statement that created it

 Automatically called by most Statement methods

Type getType(int columnIndex)

 Indices start at 1 and not 0!

 Add the column name as a comment if it is known!

Type getType(String columnName)

int findColumn(String columnName)

In JDBC, you must explicitly ask if the last

For example, getInt(column) will return 0 if

As with Statement objects, you create

You need to supply values to be used

The next example sets the second placeholder

You might also like