JDBC and Database

Programming in Java
 Introduction
 Database Access in Java
 Find out any relevant background and
interest of the audience
 SQL gurus?
 Visual Basic Database Forms?

Copyright © 1997 Alex Chaffee

 Agenda
 Overview of Databases and Java
 Overview of JDBC
 JDBC APIs
 Other Database Techniques

Copyright © 1997 Alex Chaffee

Overview
RMI JDBC CORBA

[Link]

TCP/IP

Network OS
Copyright © 1997 Alex Chaffee
 Vocabulary
 Glossary of terms
 Define the terms as used in this subject

Copyright © 1997 Alex Chaffee

Part I: Overview of Databases
and Java

Copyright © 1997 Alex Chaffee

 Databases in the Enterprise
 All corporate data stored in DB
 SQL standardizes format (sort of)

Copyright © 1997 Alex Chaffee

 Why Java?
 Write once, run anywhere
 Multiple client and server platforms
 Object-relational mapping
 databases optimized for searching/indexing
 objects optimized for engineering/flexibility
 Network independence
 Works across Internet Protocol
 Database independence
 Java can access any database vendor
 Ease of administration
 zero-install client

Copyright © 1997 Alex Chaffee

 Database Architectures
 Two-tier
 Three-tier
 N-tier

Copyright © 1997 Alex Chaffee

 Two-Tier Architecture
 Client connects directly to server
 e.g. HTTP, email
 Pro:
 simple
 client-side scripting offloads work onto the client
 Con:
 fat client
 inflexible

Copyright © 1997 Alex Chaffee

 Three-Tier Architecture
 Application Server sits between client and
database

Copyright © 1997 Alex Chaffee

 Three-Tier Pros
 flexible: can change one part without affecting
others
 can connect to different databases without
changing code
 specialization: presentation / business logic /
data management
 can cache queries
 can implement proxies and firewalls
Copyright © 1997 Alex Chaffee
 Three-Tier Cons
 higher complexity
 higher maintenance
 lower network efficiency
 more parts to configure (and buy)

Copyright © 1997 Alex Chaffee

 N-Tier Architecture
 Design your application using as many
“tiers” as you need
 Use Object-Oriented Design techniques
 Put the various components on whatever
host makes sense
 Java allows N-Tier Architecture, especially
with RMI and JDBC

Copyright © 1997 Alex Chaffee

 Database Technologies
 Hierarchical
 obsolete (in a manner of speaking)
 any specialized file format can be called a hierarchical DB
 Relational (aka SQL) (RDBMS)
 row, column
 most popular
 Object-relational DB (ORDBMS)
 add inheritance, blobs to RDB
 NOT object-oriented -- “object” is mostly a marketing term
 Object-oriented DB (OODB)
 data stored as objects
 high-performance for OO data models

Copyright © 1997 Alex Chaffee

 Relational Databases
 invented by Dr. [Link]
 data stored in records which live in tables
 maps row (record) to column (field) in a
single table
 “relation” (as in “relational”) means row to
column (not table to table)

Copyright © 1997 Alex Chaffee

 Joining Tables
 you can associate tables with one another
 allows data to nest
 allows arbitrarily complicated data
structures
 not object-oriented

Copyright © 1997 Alex Chaffee

 Join example
 People
 name
 homeaddress
 workaddress
 Addresses
 id
 street
 state
 zip

Copyright © 1997 Alex Chaffee

 SQL
 Structured Query Language
 Standardized syntax for “querying”
(accessing) a relational database
 Supposedly database-independent
 Actually, there are important variations
from DB to DB

Copyright © 1997 Alex Chaffee

 SQL Syntax
INSERT INTO table ( field1, field2 ) VALUES
( value1, value2 )
 inserts a new record into the named table

UPDATE table SET ( field1 = value1, field2 =

value2 ) WHERE condition
 changes an existing record or records

DELETE FROM table WHERE condition

 removes all records that match condition

SELECT field1, field2 FROM table WHERE

condition
 retrieves all records that match condition

Copyright © 1997 Alex Chaffee

 Transactions
 Transaction = more than one statement which
must all succeed (or all fail) together
 If one fails, the system must reverse all
previous actions
 Also can’t leave DB in inconsistent state
halfway through a transaction
 COMMIT = complete transaction
 ROLLBACK = abort
Copyright © 1997 Alex Chaffee
Part II: JDBC Overview

Copyright © 1997 Alex Chaffee

 JDBC Goals
 SQL-Level
 100% Pure Java
 Keep it simple
 High-performance
 Leverage existing database technology
 why reinvent the wheel?
 Use strong, static typing wherever possible
 Use multiple methods to express multiple functionality

Copyright © 1997 Alex Chaffee

JDBC Ancestry

X/OPEN

ODBC JDBC

Copyright © 1997 Alex Chaffee

 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

Copyright © 1997 Alex Chaffee

 JDBC Drivers
 Type I: “Bridge”
 Type II: “Native”
 Type III: “Middleware”
 Type IV: “Pure”

Copyright © 1997 Alex Chaffee

 JDBC Drivers (Fig.)
Type I ODBC
ODBC
“Bridge” Driver

Type II
JDBC CLI (.lib)
“Native”

Type III Middleware

“Middleware” Server

Type IV
“Pure”
Copyright © 1997 Alex Chaffee
 Type I Drivers
 Use bridging technology
 Requires installation/configuration on client
machines
 Not good for Web
 e.g. ODBC Bridge

Copyright © 1997 Alex Chaffee

 Type II Drivers
 Native API drivers
 Requires installation/configuration on client
machines
 Used to leverage existing CLI libraries
 Usually not thread-safe
 Mostly obsolete now
 e.g. Intersolv Oracle Driver, WebLogic drivers

Copyright © 1997 Alex Chaffee

 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
 e.g. Symantec DBAnywhere
Copyright © 1997 Alex Chaffee
 Type IV Drivers
 100% Pure Java -- the Holy Grail
 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, mSQL

Copyright © 1997 Alex Chaffee

 JDBC Limitations
 No scrolling cursors
 No bookmarks

Copyright © 1997 Alex Chaffee

 Related Technologies
 ODBC
 Requires configuration ([Link])
 RDO, ADO
 Requires Win32
 OODB
 e.g. ObjectStore from ODI
 JavaBlend
 maps objects to tables transparently (more or less)

Copyright © 1997 Alex Chaffee

Part III: JDBC APIs

Copyright © 1997 Alex Chaffee

 [Link]
 JDBC is implemented via classes in the
[Link] package

Copyright © 1997 Alex Chaffee

 Loading a Driver Directly
Driver d = new
[Link]();
Connection c = [Link](...);
 Not recommended, use DriverManager
instead
 Useful if you know you want a particular
driver

Copyright © 1997 Alex Chaffee

 DriverManager
 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!

Copyright © 1997 Alex Chaffee

 Registering a Driver
 statically load driver
[Link](“[Link]”);
Connection c =
[Link](...);
 or use the [Link] system
property

Copyright © 1997 Alex Chaffee

 JDBC Object Classes
 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

Copyright © 1997 Alex Chaffee

 JDBC Class Usage
DriverManager

Driver

Connection

Statement

ResultSet

Copyright © 1997 Alex Chaffee

 JDBC URLs
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:msql://host[:port]/database
 e.g. jdbc:msql://[Link]/accounting

Copyright © 1997 Alex Chaffee

 DriverManager
Connection getConnection
(String url, String user,
String password)
 Connects to given JDBC URL with given
user name and password
 Throws [Link]
 returns a Connection object

Copyright © 1997 Alex Chaffee

 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
 NB: Some drivers don’t support serialized connections
 Fortunately, most do (now)
 Also provides “metadata” -- information about the database,
tables, and fields
 Also methods to deal with transactions

Copyright © 1997 Alex Chaffee

 Obtaining a Connection
String url = "jdbc:odbc:Northwind";
try {
[Link] ("[Link]");
Connection con = [Link](url);
}
catch (ClassNotFoundException e)
{ [Link](); }
catch (SQLException e)
{ [Link](); }

Copyright © 1997 Alex Chaffee

 Connection Methods
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
 Why all these different kinds of statements?
Optimization.

Copyright © 1997 Alex Chaffee

 Statement
 A Statement object is used for executing a
static SQL statement and obtaining the
results produced by it.

Copyright © 1997 Alex Chaffee

 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.
boolean execute(String)
 Execute a SQL statement that may return multiple results.
 Why all these different kinds of queries? Optimization.

Copyright © 1997 Alex Chaffee

 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.
 you can’t rewind

Copyright © 1997 Alex Chaffee

 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

Copyright © 1997 Alex Chaffee

 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

Copyright © 1997 Alex Chaffee

 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)

Copyright © 1997 Alex Chaffee

 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)

Copyright © 1997 Alex Chaffee

 isNull
 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)

Copyright © 1997 Alex Chaffee

 Sample Database
Employee ID Last Name First Name
1 Davolio Nancy
2 Fuller Andrew
3 Leverling Janet
4 Peacock Margaret
5 Buchanan Steven

Copyright © 1997 Alex Chaffee

 SELECT Example
Connection con =
[Link](url,
"alex", "8675309");
Statement st = [Link]();
ResultSet results =
[Link]("SELECT EmployeeID,
LastName, FirstName FROM Employees");

Copyright © 1997 Alex Chaffee

 SELECT Example (Cont.)
while ([Link]()) {
int id = [Link](1);
String last = [Link](2);
String first = [Link](3);
[Link]("" + id + ": " +
first + " " + last);
}
[Link]();
[Link]();

Copyright © 1997 Alex Chaffee

 Mapping Java Types to SQL
Types
SQL type Java Type
CHAR, VARCHAR, LONGVARCHAR String
NUMERIC, DECIMAL [Link]
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT, DOUBLE double
BINARY, VARBINARY, LONGVARBINARY byte[]
DATE [Link]
TIME [Link]
TIMESTAMP [Link]

Copyright © 1997 Alex Chaffee

 Database Time
 Times in SQL are notoriously unstandard
 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

Copyright © 1997 Alex Chaffee

 Modifying the Database
 use executeUpdate if the SQL contains
“INSERT” or “UPDATE”
 Why isn’t it smart enough to parse the SQL?
Optimization.
 executeUpdate returns the number of rows
modified
 executeUpdate also used for “CREATE
TABLE” etc. (DDL)
Copyright © 1997 Alex Chaffee
INSERT example

Copyright © 1997 Alex Chaffee

 Transaction Management
 Transactions are not explicitly opened and
closed
 Instead, the connection has a state called
AutoCommit mode
 if AutoCommit is true, then every statement
is automatically committed
 default case: true

Copyright © 1997 Alex Chaffee

 setAutoCommit
[Link](boolean)
 if AutoCommit is false, then every statement
is added to an ongoing transaction
 you must explicitly commit or rollback the
transaction using [Link]() and
[Link]()

Copyright © 1997 Alex Chaffee

 Connection Managers
 Hint: for a large threaded database server, create a
Connection Manager object
 It is responsible for maintaining a certain number of open
connections to the database
 When your applications need a connection, they ask for
one from the CM’s pool
 Why? Because opening and closing connections takes a
long time
 Warning: the CM should always setAutoCommit(false)
when a connection is returned

Copyright © 1997 Alex Chaffee

 Optimized Statements
 Prepared Statements
 SQL calls you make again and again
 allows driver to optimize (compile) queries
 created with [Link]()
 Stored Procedures
 written in DB-specific language
 stored inside database
 accesed with [Link]()
Copyright © 1997 Alex Chaffee
JDBC Class Diagram

Whoa!
Copyright © 1997 Alex Chaffee
 Metadata
 Connection:
 DatabaseMetaData getMetaData()
 ResultSet:
 ResultSetMetaData getMetaData()

Copyright © 1997 Alex Chaffee

 ResultSetMetaData
 What's the number of columns in the ResultSet?
 What's a column's name?
 What's a column's SQL type?
 What's the column's normal max width in chars?
 What's the suggested column title for use in printouts and displays?
 What's a column's number of decimal digits?
 Does a column's case matter?
 Is the column a cash value?
 Will a write on the column definitely succeed?
 Can you put a NULL in this column?
 Is a column definitely not writable?
 Can the column be used in a where clause?
 Is the column a signed number?
 Is it possible for a write on the column to succeed?
 and so on...

Copyright © 1997 Alex Chaffee

 DatabaseMetaData
 What tables are available?
 What's our user name as known to the
database?
 Is the database in read-only mode?
 If table correlation names are supported, are
they restricted to be different from the names of
the tables?
 and so on…
Copyright © 1997 Alex Chaffee
JavaBlend: Java to Relational
Mapping

Copyright © 1997 Alex Chaffee

 JDBC 2.0
 Scrollable result set
 Batch updates
 Advanced data types
 Blobs, objects, structured types
 Rowsets
 Persistent JavaBeans
 JNDI
 Connection Pooling
 Distributed transactions via JTS
 Summary
 State what has been learned
 Define ways to apply training
 Request feedback of training session

Copyright © 1997 Alex Chaffee

 Where to get more information
 Other training sessions
 Reese, Database Programming with JDBC
and Java (O’Reilly)
 [Link]
 [Link]
 [Link] (Author’s
site)

Copyright © 1997 Alex Chaffee