Amity School of Engineering & Technology
JDBC and Database Programming in Java
Amity School of Engineering & Technology
JDBC Overview
Amity School of Engineering & Technology
JDBC Goals
SQL-Level
100% Pure Java
Keep it simple
High-performance
Amity School of Engineering & Technology
JDBC Architecture
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
Amity School of Engineering & Technology
JDBC Drivers
• Type I: “Bridge”
• Type II: “Native”
• Type III: “Middleware”
• Type IV: “Pure”
Amity School of Engineering & Technology
JDBC Drivers (Fig.)
Type I ODBC
ODBC
“Bridge” Driver
Type II
CLI (.lib)
“Native”
JDBC
Type III Middleware
“Middleware” Server
Type IV
“Pure”
Amity School of Engineering & Technology
Type I Drivers
• Use bridging technology
• Requires installation/configuration on client machines
• Not good for Web
• e.g. ODBC Bridge
Amity School of Engineering & Technology
Type II Drivers
• Native API drivers
• Requires installation/configuration on client machines
• Usually not thread-safe
Amity School of Engineering & Technology
Type III Drivers
• Calls middleware server, usually on database host
• Very flexible -- allows access to multiple databases using
one driver
• Only need to download one driver
• But it’s another server application to install and maintain
Amity School of Engineering & Technology
Type IV Drivers
• 100% Pure Java
• Use Java networking libraries to talk directly to database
engines
• Only disadvantage: need to download a new driver for
each database engine
• e.g. Oracle, MySQL
Amity School of Engineering & Technology
JDBC APIs
Amity School of Engineering & Technology
java.sql
• JDBC is implemented via classes and interface in the
java.sql package
DriverManager
Amity School of Engineering & Technology
• DriverManager tries all the drivers
• Uses the first one that works
• When a driver class is first loaded, it registers itself with
the DriverManager
• Therefore, to register a driver, just load it!
Registering a Driver
Amity School of Engineering & Technology
• statically load driver
Class.forName(“foo.bar.MyDriver”);
Connection c =
DriverManager.getConnection(...);
JDBC Object Classes
Amity School of Engineering & Technology
DriverManager
Loads, chooses drivers
Driver
connects to actual database
Connection
a series of SQL statements to and from the DB
Statement
a single SQL statement
ResultSet
the records returned from a Statement
JDBC Class Usage
Amity School of Engineering & Technology
DriverManager
Driver
Connection
Statement
ResultSet
JDBC URLs
Amity School of Engineering & Technology
jdbc:subprotocol:source
• each driver has its own subprotocol
• each subprotocol has its own syntax for the source
jdbc:odbc:DataSource
– e.g. jdbc:odbc:Northwind
jdbc:mysql://host[:port]/database
– e.g. jdbc:msql://foo.nowhere.com:4333/accounting
DriverManager
Amity School of Engineering & Technology
Connection getConnection
(String url, String user, String password)
• Connects to given JDBC URL with given user name and
password
• Throws java.sql.SQLException
• returns a Connection object
Amity School of Engineering & Technology
Connection
A Connection represents a session with a specific
database.
Within the context of a Connection, SQL statements
are executed and results are returned.
Can have multiple connections to a database
Also provides “metadata” -- information about the
database, tables, and fields
Obtaining a Connection
Amity School of Engineering & Technology
String url = "jdbc:odbc:Northwind";
try {
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(url);
}
catch (ClassNotFoundException e)
{ e.getMessage(); }
catch (SQLException e)
{ e.getMessage(); }
Connection Methods
Amity School of Engineering & Technology
Statement createStatement()
– returns a new Statement object
PreparedStatement prepareStatement(String
sql)
– returns a new PreparedStatement object
CallableStatement prepareCall(String sql)
– returns a new CallableStatement object
Statement
Amity School of Engineering & Technology
• A Statement object is used for executing a static SQL
statement and obtaining the results produced by it.
Amity School of Engineering & Technology
Statement Methods
ResultSet executeQuery(String)
Execute a SQL statement that returns a single ResultSet.
int executeUpdate(String)
Execute a SQL INSERT, UPDATE or DELETE statement.
Returns the number of rows changed.
Amity School of Engineering & Technology
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.
Amity School of Engineering & Technology
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
Amity School of Engineering & Technology
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
Amity School of Engineering & Technology
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)
Amity School of Engineering & Technology
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)
isNull
Amity School of Engineering & Technology
• 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
ResultSet.isNull(column)
Sample Database
Amity School of Engineering & Technology
Employee ID Last Name First Name
1 Davolio Nancy
2 Fuller Andrew
3 Leverling Janet
4 Peacock Margaret
5 Buchanan Steven
SELECT Example
Amity School of Engineering & Technology
Connection con =
DriverManager.getConnection(url, "alex",
"8675309");
Statement st = con.createStatement();
ResultSet results = st.executeQuery("SELECT
EmployeeID, LastName, FirstName FROM
Employees");
SELECT Example (Cont.)
Amity School of Engineering & Technology
while (results.next()) {
int id = results.getInt(1);
String last = results.getString(2);
String first = results.getString(3);
System.out.println("" + id + ": " + first
+ " " + last);
}
st.close();
con.close();
