Java/JDBC

1
Java/JDBC

• Some Database terminology (brief)
• A simple stand alone JDBC Application
• Java Server Pages and Scoping
• A simple JSP and JDBC example
• JSP and JDBC Connection Pooling
• Summary

2
Some Database Terminolgy
Gary Alperson helped developed these slides
and the JDBC example.
3
Database Terminology

• Database A shared collection of logically
  related data (and a description of this data)
  designed to meet the information needs of an
  organization
• Relation A table with columns and rows
• Attribute A named column of a relation
• Tuple A row in a relation
• Definitions from Database Systems
• by Connolly, Begg, and Strachan

4
Sample Table
5
Attribute
6
Tuple
7
SQL

• Data Definition Language (DDL)
• Create tables
• Modify tables
• Delete (drop) tables
• Data Manipulation Language (DML)
• Insert data
• Update data
• Select data

8
Select Statement
We will use this data for our examples
9
From the broker table, select the contents of the
last name attribute

• Query
• SELECT lname
• FROM broker

Results
SQL is not case sensitive. Key SQL words are
capitalized and line breaks are inserted by
convention.
10
From the broker table, select all attributes

• Query
• SELECT
• FROM broker

Results
Acts as a wildcard
11
From the broker table, select all attributes
where the last name is Smith

• Query
• SELECT
• FROM broker
• WHERE lname Smith

Results

• Note that the string is enclosed by single quotes
• The contents of a string are case sensitive

12
Use AND or OR to connect multiple where clauses

• Query
• SELECT
• FROM broker
• WHERE lname Smith
• AND fname John

Results
13
Example with two Tables

• One-to-many relationship
• Each broker may have many customers
• Each customer is only affiliated with one broker
• The b_id joins both tables by identifying the
  unique broker that each customer is associated
  with

14
Cartesian Product
When you do a query on multiple tables, SQL
begins by creating the Cartesian product, which
combines each tuple from one relation from every
tuple of the other relation. (Actual SQL
implementations are free to compute the resulting
table efficiently,i.e., the actual Cartesian
product may not be generated at all.)
15
Query SELECT FROM customer, broker WHERE
broker.b_id 1
Results
SQL does not realize that the b_id in the
customer table is the same as the b_id in the
broker table unless you join them in the where
clause.
16
Cartesian Product
Query SELECT FROM customer, broker WHERE
broker.b_id 1 AND broker.b_id customer.b_id
Results
17
ODBC

• ODBC is a programming interface that enables
  applications to access data in database systems
  that use Structured Query Language (SQL) as a
  data standard.

18
Creating an ODBC Connection

• Click on the Start button.
• Choose Settings, Control Panel
• Double-click on ODBC Data Sources
• Choose the System DSN tab
• Click Add

19

• Click on the desired driver (MSAccess)
• Click on the Finish button

20

• Enter a Data Source Name
• Click on the Select button
• Locate the desired file or directory
• Click OK

21
Javas JDBC

• Allows access to any ANSI SQL-2 DBMS
• Does its work in terms of SQL
• The JDBC has classes that represent
• database connections
• SQL Statements
• Result sets
• database metadata
• Can be connected to ODBC

22
SQL Query as a Java String
From both tables select the last names of all
customers whose brokers last name is Smith but
whose broker ID is not 1.
The SQL SELECT customer.lname FROM customer,
broker WHERE broker.lname Smith AND
broker.b_id ltgt 1 AND broker.b_id customer.b_id
23
Executing a query in Java // Statement
aStatement statement got from connection String
last Smith int nonID 1 String q SELECT
customer.lname FROM customer, broker WHERE
broker.lname \ last \ AND broker.b_id
ltgt nonID AND broker.b_id
customer.b_id) ResultSet rs
aStatement.executeQuery(q)

• The slash (\) is the escape character. It
  precedes the single quote to tell Java to include
  that quote in the String
• The String last is outside of the double quotes,
  because it must be concatonated with the String
  sent to the database, but it falls within the
  single quotes so that SQL treats it as a string
• nonID does not go within single quotes since it
  is numeric
• Since the String is an SQL statement, it uses
  and ltgt rather than and !

24
A Simple Standalone JDBC Application
// This program makes use of a stock database //
and the primary JDBC classes (Connection,
Statement, // ResultSet and ResultSetMetaData) i
mport java.util. import java.sql. import
java.io. public class TestCoolStocksDB
public static void main(String args)
Connection con null Statement s null
25
ResultSet rs null
ResultSetMetaData rsm null String
answer "" try
DriverManager.registerDriver(
new sun.jdbc.odbc.JdbcOdbcDri
ver()) con DriverManager.getConn
ection("jdbcodbcCoolStocks") s
con.createStatement() rs
s.executeQuery("select from customer")
rsm rs.getMetaData()

26
while(rs.next())
for(int col 1 col lt
rsm.getColumnCount() col)
answer rs.getString(col)
con.close()
catch (SQLException sqle)
System.err.println("Exception caught in main"
sqle)
System.out.println(answer)
27
It Works
D\McCarthy\www\95-713\examples\jdbcgtjava
TestCoolStocksDB 1JonesRobert 2SmithElaine 3ChanJ
ane 4MoralesHector 5SchwartzMichael
The carriage returns were added.
28
JSP and Scoping

• When a browser visits a web site we may
• need to know if that same browser has visited
  before.
• Java provides page scope, request scope, session
  scope, and application scope
• For long term persistence we will often need a
  database

Much of this lecture is from a nice little book
entitled Pure JSP by Goodwill published by SAMS
29
Page Scope

• Beans with page scope are accessible only
  
• within the page where they were created.
• A bean with page-level scope is not
• persistent between requests or outside the
• page

30
Page Scope Example
/ A simple bean that counts visits. / import
java.io. public class Counter implements
Serializable private int count 1
public Counter() public int getCount()
return count public void setCount(int c)
count c
31
Under Tomcat
webapps
myApplication
WEB-INF
SomeFile.jsp
classes
web.xml
These programs require a container.
Counter.java
32
lt-- Use the Counter bean with page scope.
--gt lt-- The Counter class must be imported. Its
in the WEB-INF/classes directory --gt lt_at_ page
import"Counter" gt ltjspuseBean id "ctr"
scope "page" class "Counter" /gt lthtmlgt
ltheadgt lttitlegtPage Bean Examplelt/titlegt
lt/headgt ltbodygt lth3gtPage Bean Example
lt/h3gt ltcentergt ltbgtThe current
count for the counter bean is lt/bgt
ltjspgetProperty name "ctr" property "count"
/gt lt/centergt lt/bodygt lt/htmlgt
33
The count never changes.
34
One Page May Call Another
lt-- Caller page Caller.jsp --gt lthtmlgt
ltheadgt lttitlegtCaller page lt/titlegt
lt/headgt ltbodygt lth1gt Caller page lt/h1gt
ltjspforward page "Callee.jsp" /gt
lt/bodygt lt/htmlgt
Any response data is cleared and control passes
to the new page.
35
Callee.jsp
lt-- Callee page --gt lthtmlgt ltheadgt
lttitlegtCallee page lt/titlegt lt/headgt ltbodygt
lth1gt Callee page lt/h1gt lt/bodygt lt/htmlgt
36
After Visiting Caller.jsp
37
Request Scope

• One page may call another and the bean is still
  available.
• Its considered one request.
• The second page will use an existing bean before
  creating a
• new one.
• When the current request is complete the bean is
  reclaimed
• by the JVM.

38
Request Scope Caller.jsp
lt-- Caller page --gt lt_at_ page import"Counter"
gt ltjspuseBean id "ctr" scope "request"
class "Counter" /gt lthtmlgt ltheadgt
lttitlegtCaller page lt/titlegt
ltjspsetProperty name "ctr" property "count"
value "10" /gt lt/headgt ltbodygt lth1gt
Caller page lt/h1gt ltjspforward page
"Callee.jsp" /gt lt/bodygt lt/htmlgt
39
Request Scope Callee.jsp
lt-- Callee page --gt lt_at_ page import"Counter"
gt ltjspuseBean id "ctr" scope "request"
class "Counter" /gt lthtmlgt ltheadgt
lttitlegtCallee page lt/titlegt lt/headgt ltbodygt
lth1gt Callee page lt/h1gt
ltjspgetProperty name "ctr" property "count"
/gt lt/bodygt lt/htmlgt
40
After Visiting Caller.jsp
41
Session Scope
Beans with session scope are accessible within
pages processing requests that are in the same
session as the one in which the bean was
created. Session lifetime is typically
configurable and is controlled by the servlet
container. Currently, my session ends when
the browser exits. Multiple copies of the same
browser each get their own session bean.
42
Session Scope Example
lt-- SessionBeanPage.jsp --gt lt_at_ page
import"Counter" gt ltjspuseBean id "ctr"
scope "session" class "Counter" /gt lthtmlgt
ltheadgt lttitlegtSession Bean Page lt/titlegt
lt/headgt ltbodygt lth1gt Session Bean Page
lt/h1gt ltBgtVisit number
ltjspgetProperty name "ctr" property
"count"/gt lt/Bgt lt/bodygt lt/htmlgt
43
Session Scope Example
The counter increments on each hit till browser
exits. New browser back to 1.
44
Application Beans
A bean with a scope value of application has an
even broader and further reaching availability
than session beans. Application beans exist
throughout the life of the JSP container itself,
meaning they are not reclaimed until the server
is shut down. Session beans are available on
subsequent requests from the same browser.
Application beans are shared by all users.
45
Application Bean Example 1
lt-- ApplicationBeanPage1.jsp --gt lt_at_ page
import"Counter" gt ltjspuseBean id "ctr"
scope "application" class "Counter"
/gt lthtmlgt ltheadgt lttitlegtApplication Bean
Page lt/titlegt lt/headgt ltbodygt
lth1gt Application Bean Page lt/h1gt ltBgtVisit
number ltjspgetProperty name "ctr
property "count"/gt
lt/Bgt lt/bodygt lt/htmlgt
46
Application Bean Example 2
lt-- ApplicationBeanPage2.jsp --gt lt_at_ page
import"Counter" gt ltjspuseBean id "ctr"
scope "application" class "Counter"
/gt lthtmlgt ltheadgt lttitlegtApplication Bean
Page Two lt/titlegt lt/headgt ltbodygt lth1gt
Application Bean Page Two lt/h1gt ltBgtVisit
number ltjspgetProperty name "ctr
property "count"/gt
lt/Bgt lt/bodygt lt/htmlgt
47
After several visits with IE5 we visit with
Netscape.
48
After visiting from a different machines with a
different browsers, we still keep count.
49
A Simple JSP/JDBC Example
stocks.mdb database schema
There are three tables. Both customer and stocks
have a one-to-many relationship with portfolios.
The database stocks.mdb was registered with the
ODBC driver as CoolStocks
50
Register w/ODBC
Create an ODBC data source. Click on the Start
button. Choose Settings, Control
Panel Double-click on ODBC Data Sources Choose
the System DSN tab Click Add Click on the desired
driver (MSAccess) Click on the Finish
button Enter a Data Source Name (I called my
database CoolStocks and that name appears in the
java code below) Click on the Select
button Locate the directory and file containing
your database. This will be the stock.mdb file
created by Microsoft Access. Click OK
51
A Simple JSP/JDBC Example
ltTITLEgtJSP JDBC Example 1lt/TITLEgt lt/HEADgt ltBODYgt
lt! Adapted from James Goodwills Pure JSP ? lt!--
Set the scripting language to java and --gt lt!--
import the java.sql package --gt lt_at_ page
language"java" import"java.sql." gt lt_at_ page
import "java.io." gt
52
lt Connection con null try //
Load the Driver class file
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
// Make a connection to the ODBC datasource
Movie Catalog con DriverManager.getConnect
ion("jdbcodbcCoolStocks") // Create the
statement Statement statement
con.createStatement() // Use the created
statement to SELECT the DATA // FROM the
customer Table. ResultSet rs
statement.executeQuery("SELECT "
"FROM customer") // Iterate over the
ResultSet gt
53
lt!-- Add an HTML table to format the
results --gt ltTABLE BORDER"1"gt ltTRgt
ltTHgt Customer - IDlt/THgtltTHgtLast Namelt/THgt
ltTHgtFirst Namelt/THgt lt while (
rs.next() ) // get the id, convert to
String out.println("ltTRgt\nltTDgt"
rs.getString("id") "lt/TDgt") // get
the last name out.println("ltTDgt"
rs.getString("lname") "lt/TDgt") // get
the first name out.println("ltTDgt"
rs.getString("fname") "lt/TDgt\nlt/TR")

54
// Close the ResultSet rs.close()
catch (IOException ioe)
out.println(ioe.getMessage()) catch
(SQLException sqle) out.println(sqle.getMe
ssage()) catch (ClassNotFoundException
cnfe) out.println(cnfe.getMessage())
catch (Exception e)
out.println(e.getMessage())
55
finally try if ( con ! null )
// Close the connection no matter
what con.close()
catch (SQLException sqle)
out.println(sqle.getMessage())
gt lt/BODYgt lt/HTMLgt
56
It Works!
57
An Example Using Connection Pooling
The example above opens a connection every
time there is a visit. Goodwill presents another
approach in chapter 14.
58
PooledConnection.java
// Adapted from Goodwill's Pure JSP import
java.sql. public class PooledConnection
// Real JDBC Connection private Connection
connection null // boolean flag used to
determine if connection is in use private
boolean inuse false
59
// Constructor that takes the passed in JDBC
Connection // and stores it in the connection
attribute. public PooledConnection(Connection
value) if ( value ! null )
connection value // Returns a
reference to the JDBC Connection public
Connection getConnection() return
connection
60
// Set the status of the PooledConnection.
public void setInUse(boolean value) inuse
value // Returns the current status of the
PooledConnection. public boolean inUse()
return inuse // Close the real JDBC
Connection public void close() try
connection.close() catch
(SQLException sqle) System.err.println(sql
e.getMessage())
61
ConnectionPool.java
// Adapted from James Goodwill's Pure
Java import java.sql. import
java.util. public class ConnectionPool //
JDBC Driver Name private String driver null
// URL of database private String url null
// Initial number of connections. private int
size 0
62
// Username private String username new
String("") // Password private String
password new String("") // Vector of JDBC
Connections private Vector pool null
public ConnectionPool() // Set the
value of the JDBC Driver public void
setDriver(String value) if ( value ! null
) driver value
63
// Get the value of the JDBC Driver public
String getDriver() return driver //
Set the URL Pointing to the Datasource public
void setURL(String value ) if ( value !
null ) url value // Get
the URL Pointing to the Datasource public
String getURL() return url
64
// Set the initial number of connections
public void setSize(int value) if ( value gt
1 ) size value // Get the
initial number of connections public int
getSize() return size // Set the
username public void setUsername(String value)
if ( value ! null ) username
value
65
// Get the username public String getUserName()
return username // Set the
password public void setPassword(String value)
if ( value ! null ) password
value // Get the password public
String getPassword() return password
66
// Creates and returns a connection private
Connection createConnection() throws Exception
Connection con null // Create a
Connection con DriverManager.getConnection
(url, username, password) return
con
67
// Initialize the pool public synchronized
void initializePool() throws Exception
// Check our initial values if ( driver
null ) throw new Exception("No Driver
Name Specified!") if ( url
null ) throw new Exception("No URL
Specified!") if ( size lt 1 )
throw new Exception("Pool size is less than
1!")
68
// Create the Connections try //
Load the Driver class file
Class.forName(driver) // Create
Connections based on the size member for (
int x 0 x lt size x ) Connection
con createConnection() if ( con !
null ) // Create a PooledConnection
to encapsulate the // real JDBC
Connection PooledConnection pcon
new PooledConnection(con) // Add the
Connection to the pool.
addConnection(pcon)
69
catch (Exception e) System.err.println(e.g
etMessage()) throw new Exception(e.getMessa
ge()) // Adds the PooledConnection
to the pool private void addConnection(PooledCon
nection value) // If the pool is null,
create a new vector // with the initial size
of "size" if ( pool null ) pool
new Vector(size) // Add the
PooledConnection Object to the vector
pool.addElement(value)
70
public synchronized void releaseConnection(Connect
ion con) // find the PooledConnection
Object for ( int x 0 x lt pool.size() x
) PooledConnection pcon
(PooledConnection)pool.elementAt(x) //
Check for correct Connection if (
pcon.getConnection() con )
System.err.println("Releasing Connection " x)
// Set its inuse attribute to false,
which // releases it for use
pcon.setInUse(false) break

71
// Find an available connection public
synchronized Connection getConnection()
throws Exception PooledConnection pcon
null // find a connection not in use for
( int x 0 x lt pool.size() x ) pcon
(PooledConnection)pool.elementAt(x) //
Check to see if the Connection is in use if
( pcon.inUse() false ) // Mark it
as in use pcon.setInUse(true) //
return the JDBC Connection stored in the
// PooledConnection object return
pcon.getConnection()
72
// Could not find a free connection socreate
and add a new one try // Create a
new JDBC Connection Connection con
createConnection() // Create a new
PooledConnection, passing it the JDBC Connection
pcon new PooledConnection(con)
// Mark the connection as in use
pcon.setInUse(true) // Add the new
PooledConnection object to the pool
pool.addElement(pcon) catch (Exception
e) System.err.println(e.getMessage())
throw new Exception(e.getMessage())
// return the new Connection return
pcon.getConnection()
73
// When shutting down the pool, you need to
first empty it. public synchronized void
emptyPool() // Iterate over the entire
pool closing the // JDBC Connections. for
( int x 0 x lt pool.size() x )
System.err.println("Closing JDBC Connection "
x) PooledConnection pcon
(PooledConnection)pool.elementAt(x) // If
the PooledConnection is not in use, close it
if ( pcon.inUse() false )
pcon.close()
74
else // If it is still in use, sleep
for 30 seconds and // force close.
try java.lang.Thread.sleep(30000)
pcon.close() catch
(InterruptedException ie)
System.err.println(ie.getMessage())

75
JDBCPooledExample.jsp
lthtmlgt ltbodygt lt_at_ page errorPage"errorpage.jsp"
gt lt_at_ page import"java.util." gt lt_at_ page
import"java.sql." gt lt_at_ page import
"java.io." gt lt_at_ page import"ConnectionPool"
gt lt!-- Instantiate the ConnectionPool bean with
an id of "pool" --gt ltjspuseBean id"pool"
scope"application" class"ConnectionPool" /gt
76
lt Connection con null try // The
pool is not initialized if ( pool.getDriver()
null ) // initialize the pool
pool.setDriver("sun.jdbc.odbc.JdbcOdbcDriver")
pool.setURL("jdbcodbcCoolStocks")
pool.setSize(5) pool.initializePool()
// Get a connection from the
ConnectionPool con pool.getConnection()
// Create the statement Statement statement
con.createStatement()
77
// Use the created statement to SELECT the
DATA // FROM the customer Table.
ResultSet rs statement.executeQuery("SELECT "
"FROM customer") // Iterate over
the ResultSet gt lt!-- Add an HTML table
to format the results --gt ltcentergt lttable
border"1" cellspacing"0" cellpadding"2"width"5
00"gt lttrgt ltTHgt Customer -
IDlt/THgtltTHgtLast Namelt/THgt ltTHgtFirst
Namelt/THgt
78
lt while ( rs.next() ) // get
the id, convert to String
out.println("ltTRgt\nltTDgt" rs.getString("id")
"lt/TDgt") // get the last name
out.println("ltTDgt" rs.getString("lname")
"lt/TDgt") // get the first name
out.println("ltTDgt" rs.getString("fname")
"lt/TDgt\nlt/TR") // Close
the ResultSet rs.close()
out.println("lt/tablegtlt/centergt") catch
(IOException ioe) out.println(ioe.getMessage
())
79
catch (SQLException sqle)
out.println(sqle.getMessage()) catch
(ClassNotFoundException cnfe)
out.println(cnfe.getMessage()) catch
(Exception e) out.println(e.getMessage())
finally try if ( con ! null )
// release the connection no matter
what pool.releaseConnection(con)

80
catch (Exception e) out.println(e.getMess
age()) gt lt/bodygt lt/htmlgt
81
It works too!
82
Summary

With JDBC we can Write standalone
programs that interact with RDBMS. Write
server side code that interacts with server side
RDBMS. We have not covered the many
possible uses of SOAP clients interacting with
SOAP servers utilizing back end RDBMS.