Relational Database Connectivity

1
Relational Database Connectivity

• JDBC and JSP
• CC292

2
Overview

• Relational Databases
• Making a connection
• Creating a statement
• Executing a query
• Processing a Result Set
• JDBC with JSP

3
Relational Databases

• Massive installed base the majority of existing
  database systems are relational
• Strong theory
• high normal forms together with
• integrity rules
• guarantee relational databases that are
• incapable of storing inconsistent information
• May still be incorrect of course!

4
Relational DBs II

• Support for Atomic Transactions (Begin , ... ,
  Commit/Rollback)
• Widely accepted graphical modelling tools
• a relational database design can be easily
  communicated via an ER (or EER) diagram
• Wide range of high-quality implementations
• Evolved over thirty years
• Well defined mathematical models for data
  manipulation based on
• relational algebra
• relational calculus.

5
Relational DBs III

• Powerful (standard!?!) query and manipulation
  languages such as SQL
• Beware though that implementations often differ
  in the details, and there are different versions
  (up to SQL 3?)
• Relational databases systems allow complex
  data-processing problems to be solved with
  relatively simple, elegant solutions.

6
Relational DBs and Web Apps

• DB centric or Object centric code
• DB centric
• The database already exists
• You now want to query and update it from your JSP
  pages
• Object centric
• Youre designing a new application
• Have an open choice of database design and
  platform

7
The JDBC Bridge
Application

• JDBC/ODBC
• Bridge

MS Access Database
Oracle Database
8
JDBC

• Provides a standard way of accessing relational
  databases
• The API is in the java.sql package
• Write code that should work with any relational
  database
• Either need it to have an ODBC driver (then use
  the JDBCODBC bridge)
• OR can use a native Java driver (just a JDBC
  driver)

9
Caution!

• Different drivers support different access modes
• Some only connect to a database on the same
  filing system
• Others connect to a database URL, and potentially
  work anywhere over the Internet
• Though be careful about firewalls!
• Different databases may support different
  operations
• Some database/driver combos are BUGGY!!!

10
Database Querying / Updating

• Load a driver
• Connect to the database
• Create a Statement
• Execute the Statement
• Process the ResultSet

11
Loading the Driver

• Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" )
• This specified the database driver to load
• Note that in the lab we shall use a different
  driver one that connects directly to an HSQLDB
• This driver can then be used in subsequent calls
  to DriverManager.getConnection()

12
Creating the Connection

• Connection con
• DriverManager.getConnection(
• "jdbcodbcshopdb" ,
• "user" , "passwd" )
• Specifies the database URL, the user name and the
  password
• This URL is on the local machine
• Also possible to have web urls, with different
  drivers

13
Creating a Statement

• Statement statement con.createStatement()
• A Statement is used to execute SQL calls on the
  database
• Common methods are
• ResultSet rs statement.executeQuery(String
  query)
• int nRows
• statement.executeUpdate(String update)

14
Using the ResultSet

• Iterating over the ResultSet
• Extracting fields from each tuple
• ResultSet rs stmt.executeQuery( select )
• while ( rs.next() )
• pw.println(
• rs.getString("Description") )
• Here we used getString() can also get other
  types see API

15
Simple Product DB
16
Result Set Metadata

• Metadata data about data
• Result set metadata includes number of columns
  and name of each column
• Can use metadata to write smarter database code
• E.g. generic table writers
• Can use these as helper classes
• Hence simplify our code

17
Sample Output

18
Design Issues

• For simple examples, the JDBC API looks fine
• For more complex cases, the inclusion of SQL code
  directly in JSP is ugly and may introduce
  problems
• Best to place all the DB access code in separate
  classes
• Also whether to implement calculations in Java or
  in SQL (note speed implications).
• For simple cases, SQL can be much faster
• For compex cases, Java code may be easier to
  program

19
Direct JSP Example

• ltbodygt
• lt
• try
• Class.forName("sun.jdbc.odbc.JdbcOdbcDrive
  r")
• System.out.println("loaded class")
• Connection con
• DriverManager.getConnection(
• "jdbcodbcartshop", "",
  "")
• gt
• lttablegt
• lttrgtltthgtProductlt/thgtltthgtPricelt/thgtlt/trgt

20
Direct contd.

• lt // now for each row of the table
• Statement s con.createStatement()
• ResultSet rs s.executeQuery(
  "Select from Product")
• while(rs.next())
• gt
• lttrgt lttdgt lt rs.getString("Title") gt
  lt/tdgt
• lttdgt lt rs.getString("Price") gt
  lt/tdgtlt/trgt
• lt
• gt
• lt/tablegt
• lt
• catch (Exception e)
• out.println(e)
• gt
• lt/bodygt

21
General DB Access
Application
DBInterface

• JDBC/ODBC
• Bridge

OODB
MS Access Database
HSQL Database
22
Generalising DB Code

• Not good to put SQL directly in your JSP code
• Generally better to define DB access in an
  interface
• This makes the JSP code
• Neater
• Easier to understand
• Easier to debug
• Exercise redesign the above JSP code using the
  concept of a DBInterface

23
Sample Output
24
Atomic Transactions

• Next examples
• JDBC
• Db4o
• Db4o example is more complete
• In the example, it also appears to be more
  complex
• Why is that?

25
Example Atomic Transaction

• Following outline example uses a JDBC connection
  to make an atomic transaction
• It assumes that the Strings update1 and update2
  have been set up appropriately
• And that DB constraints have been placed on the
  value of an account (e.g. not allowed to be
  negative)
• Note that con is of type java.sql.Connection

26
JDBC Transaction

• try
• con.setAutoCommit( false )
• statement con.createStatement()
• statement.executeUpdate( update1 )
• statement.executeUpdate( update2 )
• // to get here, both must have worked
• con.commit()
• catch(SQLException e) // something wrong!
• con.rollback()

27
Atomic transaction db4oConsider this Bank
Account class

• public class Account
• String name
• int balance
• public Account(String name, int balance)
• this.name name
• this.balance balance
• public synchronized void transfer(int
  amount) throws Exception
• if (balance amount lt 0)
• throw new Exception("Overdraft not
  allowed
• (balance amount))
• balance amount
• ...

28
Transfer between accountsBank.transfer()

• public static void transfer(ObjectContainer db,
  Account from, Account to, int amount)
• try
• to.transfer(amount)
• db.set(to)
• from.transfer(-amount)
• db.set(from)
• db.commit()
• catch (Exception e)
• System.out.println(e)
• db.rollback()
• db.ext().refresh(from, 10)
• db.ext().refresh(to, 10)

29
Notes on Transfer

• Use of commit() or rollback()
• Similar to use of JDBC updates
• Note also
• db.ext().refresh(from, 10)
• This is used to return the in-memory objects to
  their on-disk state
• In this case, to the state they were in before
  the transaction was aborted
• Exercise try this in the lab and check what
  happens when refresh() is omitted.

30
Summary

• Weve shown how to run queries on a JDBC data
  source
• Also explored the user of helper-classes to write
  more elegant JSP pages
• Can then plug and play any DB type
• Also note use of COMMIT /ROLLBACK etc. to make
  atomic transactions