JDBC – Java Database
Connectivity
Talking to Databases
Zulqarnain Hashmi
1
Introduction to JDBC
• JDBC is used for accessing databases
from Java applications
• Information is transferred from relations
to objects and vice-versa
– databases optimized for searching/indexing
– objects optimized for engineering/flexibility
2
JDBC Architecture
We will
Access
These are
use this one… Driver
Java classes
MS Access
SQL Server
Java DB2
Application JDBC
Driver
DB2
Network
Oracle
Driver
Oracle
3
JDBC Architecture (cont.)
Application JDBC Driver
• Java code calls JDBC library
• JDBC loads a driver
• Driver talks to a particular database
• Can have more than one driver -> more than one
database
• Ideal: can change database engines without changing
any application code
JDBC Drivers
Java
application
JDBC-API
JDBC-
Driver manager
JDBC-ODBC Native
bridge API-driver
ODBC Client library
Client library
5
What is ODBC?
• ODBC is (Open Database Connectivity):
• A standard or open application programming
interface (API) for accessing a database.
• By using ODBC statements (DSN, Data Source
Name) in a program, you can access files in a
number of different databases, including
Access, dBase, DB2, Excel, and Text.
• ODBC handles the SQL request and converts it
into a request the individual database system
understands.
6
Running a JDBC Application
Phase Task Relevant [Link] classes
Initialisation Load driver DriverManager
Create connection Connection
Processing Generate SQL statements Statement
Process result data ResultSet etc.
Terminate connection Connection
Termination
Release data structures Statement etc.
7
Seven Steps
• Load the driver
• Define the connection URL
• Establish the connection
• Create a Statement object statement
• Execute a query using statement
• Process the result
• Close the connection
8
Loading the Driver
• We can register the Driver indirectly using the
Java statement:
[Link](“[Link]"); or
[Link](“[Link]");
• Calling [Link] causes the Driver class to be
loaded
• When this class is loaded, it automatically
– creates an instance of itself
– registers this instance with the DriverManager
9
Connecting to the Database
• Every database is identified by a URL
• Given a URL, DriverManager is asked to
find the driver that can talk to the
corresponding database
• DriverManager tries all registered
drivers, until a suitable one is found
10
Connecting to the Database
Connection con = DriverManager.
getConnection("[Link]:Books“,”userName”,”password” );
“[Link]:Books” URL
“userName” Login Name
“Password” Login Password
11
Interaction with the Database
• We use Statement objects in order to
– Extract data from the database
– Update the database
• Three different interfaces are used:
Statement, PreparedStatement, CallableStatement
• All are interfaces, thus cannot be instantiated
• They are created by the Connection
Querying with Statement
String queryStr =
"SELECT * FROM Member " +
"WHERE Name = 'harry potter'";
Statement stmt = [Link]();
ResultSet rs = [Link](queryStr);
• The executeQuery method returns a ResultSet
object representing the query result.
•Will be discussed later…
13
Changing DB with Statement
String deleteStr =
“DELETE FROM Member " +
"WHERE Name = ‘harry potter’";
Statement stmt = [Link]();
int delnum = [Link](deleteStr);
• executeUpdate is used for data manipulation: insert, delete, update,
create table, etc. (anything other than querying!)
• executeUpdate returns the number of rows modified
14
ResultSet
• A ResultSet provides access to a table of data
generated by executing a Statement
• Only one ResultSet per Statement can be open at
once
• The table rows are retrieved in sequence
– A ResultSet maintains a cursor pointing to its current
row of data
– The 'next' method moves the cursor to the next row
ResultSet Methods
• boolean next()
– activates the next row
– the first call to next() activates the first row
– returns false if there are no more rows
• void close()
– disposes of the ResultSet
– allows you to re-use the Statement that created it
– automatically called by most Statement methods
ResultSet Methods
• Type getType(int columnIndex)
– returns the given field as the given type
– fields indexed starting at 1 (not 0)
• Type getType(String columnName)
– same, but uses name of field
– less efficient
• int findColumn(String columnName)
– looks up column index given column name
ResultSet Methods
• String getString(int columnIndex)
• boolean getBoolean(int columnIndex)
• byte getByte(int columnIndex)
• short getShort(int columnIndex)
• int getInt(int columnIndex)
• long getLong(int columnIndex)
• float getFloat(int columnIndex)
• double getDouble(int columnIndex)
• Date getDate(int columnIndex)
• Time getTime(int columnIndex)
• Timestamp getTimestamp(int columnIndex)
ResultSet Methods
• String getString(String columnName)
• boolean getBoolean(String columnName)
• byte getByte(String columnName)
• short getShort(String columnName)
• int getInt(String columnName)
• long getLong(String columnName)
• float getFloat(String columnName)
• double getDouble(String columnName)
• Date getDate(String columnName)
• Time getTime(String columnName)
• Timestamp getTimestamp(String columnName)
ResultSet Example
Statement stmt = [Link]();
ResultSet rs = stmt.
executeQuery("select name, age from Employees");
// Print the result
while([Link]()) {
[Link]([Link](1) + ”:“);
[Link]([Link](“age”)+”“);
}
20
Null Values
• In SQL, NULL means the field is empty
• Not the same as 0 or “”
• In JDBC, you must explicitly ask if a field
is null by calling [Link](column)
• For example, getInt(column) will return 0
if the value is either 0 or null!!
ResultSet Meta-Data
A ResultSetMetaData is an object that can be used
to get information about the properties of the
columns in a ResultSet object.
An example: write the columns of the result set
ResultSetMetaData rsmd = [Link]();
int numcols = [Link]();
for (int i = 1 ; i <= numcols; i++) {
[Link]([Link](i)+” “);
}
22
Mapping Java Types to SQL
Types
SQL type Java Type
CHAR, VARCHAR, LONGVARCHAR String
NUMERIC, DECIMAL [Link]
BIT boolean
TINY INT byte
SMALL INT short
INTEGER int
BIG INT long
REAL float
FLOAT, DOUBLE double
BINARY, VARBINARY, LONGVARBINARY byte[]
DATE [Link]
TIME [Link]
TIMESTAMP [Link]
Database Time
• Times in SQL are notoriously non-standard
• Java defines three classes to help
• [Link]
– year, month, day
• [Link]
– hours, minutes, seconds
• [Link]
– year, month, day, hours, minutes, seconds, nanoseconds
– usually use this one
Cleaning Up After Yourself
• Remember to close the Connections, Statements and ResultSets
[Link]();
[Link]();
[Link]();
25
Dealing With Exceptions
• Jdbc normaly throw ClassNotFoundException and SQLException like.
try {
[Link]( "[Link]“);
connection = [Link](
url, username, password );
}
catch (ClassNotFoundException cnfe) {
[Link]([Link]());
}
26
Dealing With Exceptions
An exception can have more exceptions in it.
•
catch (SQLException e) {
while (e != null) {
[Link]([Link]());
[Link]([Link]());
[Link]([Link]());
e = [Link]();
}
}
27
A Summary of JDBC application
loadDriver [Link]( "[Link]" );
getConnection connection = [Link](jdbc:odbc:Books,‘’”,“”);
createStatement statement = [Link]();
execute(SQL) resultSet = [Link](”SELECT * FROM Authors");
Result handling
while ([Link]()) {
...
no
Last
execution ? }
[Link]();
yes
closeStatment [Link]();
[Link]();
closeConnection
28
