Relational Database Connectivity
JDBC and JSP CC292
Overview
Relational Databases Making a connection Creating a statement Executing a query Processing a Result Set JDBC with JSP
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!
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 Evolved over thirty years
Wide range of high-quality implementations
Well defined mathematical models for data manipulation based on
relational algebra relational calculus.
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.
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 You’re designing a new application Have an open choice of database design and platform
Object centric
The JDBC Bridge
Application
JDBC/ODBC Bridge
MS Access Database
Oracle Database
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 JDBC:ODBC bridge) OR: can use a native Java driver (just a JDBC driver)
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!!!
Database Querying / Updating
Load a driver Connect to the database Create a Statement Execute the Statement Process the ResultSet
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()
Creating the Connection
Connection con = DriverManager.getConnection( "jdbc:odbc:shopdb" , "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
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);
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
Simple Product DB
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
Sample Output
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
Direct JSP Example
<%
try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); System.out.println("loaded class"); Connection con = DriverManager.getConnection( "jdbc:odbc:artshop", "", ""); %>
| Product | Price |
|---|
Direct contd.
<% // now for each row of the table Statement s = con.createStatement(); ResultSet rs = s.executeQuery( "Select * from Product"); while(rs.next()) { %> | <%= rs.getString("Title") %> | <%= rs.getString("Price") %> |
<% } %>
<% } catch (Exception e) { out.println(e); } %>
General DB Access
Application
DBInterface JDBC/ODBC Bridge
OODB
MS Access Database
HSQL Database
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
Sample Output
Atomic Transactions
Next examples JDBC Db4o Db4o example is more complete In the example, it also appears to be more complex
Why is that?
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
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(); }
Atomic transaction: db4o
Consider 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 < 0) { throw new Exception("Overdraft not allowed: “ + (balance + amount)); } balance += amount; } ...
Transfer between accounts… Bank.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); } }
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 ondisk 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.
Summary
We’ve 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