Transcript 236369 JDBC

JDBC –
Java Database Connectivity
CS 236369, Spring 2010
1
Today’s Menu






JDBC Architecture
Using JDBC
Timeout
ResultSet Object
Null Values
Prepared Statements
2

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
3
JDBC Architecture
4
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
5
“Movies” Relation
movieName
producer
releaseDate
Movie1
Producer1
1.1.2000
Movie2
Producer2
1.1.2001
Movie3
Producer3
3.4.2003
6
7 Steps for Using JDBC
1.
2.
3.
4.
5.
6.
7.
Load the driver
Define the connection URL
Establish the connection
Create a Statement object
Execute a query using the Statement
Process the result
Close the connection
7
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.
9
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
11
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");
12
3.
Establish the connection
Connection con =
DriverManager.getConnection("jdbc:imaginaryDB1://
localhost:3306/");
13
4. Create a Statement object

We use Statement objects in order to
 Query the DB
 Update the DB(insert, update, create, drop, …)
15
5. Execute a query using the Statement

executeQuery returns a ResultSet object
representing the query result (discussed later…)
17
Manipulating DB with Statement
String deleteStr =
“delete from movies where movieName=‘Movie1’ ”;
Statement stmt = con.createStatement();
int rowsDeleted = stmt.executeUpdate(deleteStr);


executeUpdate is for data manipulation: insert, delete,
update, create table, etc.
executeUpdate returns the number of rows modified (or 0
for DDL commands)
19
6. Process the result

We will discuss ResultSet in a while…
20
7. Close the connection


Close Connections, Statements, and Result Sets
 con.close();
 stmt.close();
 rs.close();
‘finally’ block is a good place…
22
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
24
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
Type = int ||
double || long ||
boolean || byte ||
time || date …
25
Timeout



Use setQueryTimeOut(int seconds) of Statement class 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?
26
Mapping Java Types to SQL Types
27
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!
28
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
29
Exceptions

An SQLException is actually a list of exceptions
30
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.
31
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:
32
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.
33
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");
34
Another Example
35
Callable Statements

Execute a call to a database stored procedure.

We will not go into details
36
Resources used for this presentation



http://www.cs.huji.ac.il/~dbi/recitations/JDBC-PSQL-c.pdf
http://java.sun.com/docs/books/tutorial/jdbc/
http://www.java-samples.com/showtutorial.php?tutorialid=202
37