JavaJDBC Some Database terminology brief A simple stand

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

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

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

Sample Table

Attribute

Tuple

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

Select Statement We will use this data for our examples

From the broker table, select the contents of the last name attribute Query Results SELECT lname FROM broker; SQL is not case sensitive. Key SQL words are capitalized and line breaks are inserted by convention.

From the broker table, select all attributes Query Results SELECT * FROM broker; * Acts as a wildcard

From the broker table, select all attributes where the last name is Smith Query Results SELECT * FROM broker WHERE lname = ‘Smith’; • Note that the string is enclosed by single quotes • The contents of a string are case sensitive

Use AND or OR to connect multiple where clauses Query SELECT * FROM broker WHERE lname = ‘Smith’ AND fname = ‘John’; Results

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

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. )

Query Results SELECT * FROM customer, broker WHERE broker. b_id = 1; 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.

Cartesian Product Query SELECT * FROM customer, broker WHERE broker. b_id = 1 AND broker. b_id = customer. b_id; Results

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

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

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

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

Java’s 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

SQL Query as a Java String From both tables select the last names of all customers whose broker’s 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 <> 1 AND broker. b_id = customer. b_id;

Executing a query in Java // Statement a. Statement = statement got from connection String last = “Smith”; int non. ID = 1; String q = “SELECT customer. lname FROM customer, broker” + “WHERE broker. lname = ’” + last + “’ AND broker. b_id” + “<>” + non. ID + “AND broker. b_id = customer. b_id; ”); Result. Set rs = a. Statement. execute. Query(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 • non. ID does not go within single quotes since it is numeric • Since the String is an SQL statement, it uses = and <> rather than == and !=

A Simple Standalone JDBC Application // This program makes use of a stock database // and the primary JDBC classes (Connection, Statement, // Result. Set and Result. Set. Meta. Data) import java. util. *; import java. sql. *; import java. io. *; public class Test. Cool. Stocks. DB { public static void main(String args[]) { Connection con = null; Statement s = null;

Result. Set rs = null; Result. Set. Meta. Data rsm = null; String answer = ""; try { Driver. Manager. register. Driver( new sun. jdbc. odbc. Jdbc. Odbc. Driver()); con = Driver. Manager. get. Connection("jdbc: odbc: Cool. Stocks"); s = con. create. Statement(); rs = s. execute. Query("select * from customer"); rsm = rs. get. Meta. Data();

while(rs. next()) { for(int col = 1; col <= rsm. get. Column. Count(); col++) answer += rs. get. String(col); } con. close(); } catch (SQLException sqle) { System. err. println("Exception caught in main: " + sqle); } System. out. println(answer); } }

It Works D: Mc. Carthywww95 -713examplesjdbc>java Test. Cool. Stocks. DB 1 Jones. Robert 2 Smith. Elaine 3 Chan. Jane 4 Morales. Hector 5 Schwartz. Michael The carriage returns were added.

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

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

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 get. Count() { return count++; } public void set. Count(int c) { count = c; } }

Under Tomcat webapps my. Application WEB-INF classes Counter. java Some. File. jsp web. xml These programs require a container.

<%-- Use the Counter bean with page scope. --%> <%-- The Counter class must be imported. Its in the WEB-INF/classes directory --%> <%@ page import="Counter" %> <jsp: use. Bean id = "ctr" scope = "page" class = "Counter" /> <html> <head> <title>Page Bean Example</title> </head> <body> <h 3>Page Bean Example </h 3> <center> <b>The current count for the counter bean is: </b> <jsp: get. Property name = "ctr" property ="count" /> </center> </body> </html>

The count never changes.

One Page May Call Another <%-- Caller page Caller. jsp --%> <html> Any response data is <head> cleared and control <title>Caller page </title> passes to the </head> new page. <body> <h 1> Caller page </h 1> <jsp: forward page = "Callee. jsp" /> </body> </html>

Callee. jsp <%-- Callee page --%> <html> <head> <title>Callee page </title> </head> <body> <h 1> Callee page </h 1> </body> </html>

After Visiting Caller. jsp

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.

Request Scope Caller. jsp <%-- Caller page --%> <%@ page import="Counter" %> <jsp: use. Bean id = "ctr" scope = "request" class = "Counter" /> <html> <head> <title>Caller page </title> <jsp: set. Property name = "ctr" property = "count" value = "10" /> </head> <body> <h 1> Caller page </h 1> <jsp: forward page = "Callee. jsp" /> </body> </html>

Request Scope Callee. jsp <%-- Callee page --%> <%@ page import="Counter" %> <jsp: use. Bean id = "ctr" scope = "request" class = "Counter" /> <html> <head> <title>Callee page </title> </head> <body> <h 1> Callee page </h 1> <jsp: get. Property name = "ctr" property ="count" /> </body> </html>

After Visiting Caller. jsp

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.

Session Scope Example <%-- Session. Bean. Page. jsp --%> <%@ page import="Counter" %> <jsp: use. Bean id = "ctr" scope = "session" class = "Counter" /> <html> <head> <title>Session Bean Page </title> </head> <body> <h 1> Session Bean Page </h 1> <B>Visit number <jsp: get. Property name = "ctr" property = "count"/> </B> </body> </html>

Session Scope Example The counter increments on each hit till browser exits. New browser back to 1.

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.

Application Bean Example 1 <%-- Application. Bean. Page 1. jsp --%> <%@ page import="Counter" %> <jsp: use. Bean id = "ctr" scope = "application" class = "Counter" /> <html> <head> <title>Application Bean Page </title> </head> <body> <h 1> Application Bean Page </h 1> <B>Visit number <jsp: get. Property name = "ctr“ property = "count"/> </B> </body> </html>

Application Bean Example 2 <%-- Application. Bean. Page 2. jsp --%> <%@ page import="Counter" %> <jsp: use. Bean id = "ctr" scope = "application" class = "Counter" /> <html> <head> <title>Application Bean Page Two </title> </head> <body> <h 1> Application Bean Page Two </h 1> <B>Visit number <jsp: get. Property name = "ctr“ property = "count"/> </B> </body> </html>

After several visits with IE 5 we visit with Netscape.

After visiting from a different machines with a different browsers, we still keep count.

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 “Cool. Stocks”

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 Cool. Stocks 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

A Simple JSP/JDBC Example <TITLE>JSP JDBC Example 1</TITLE> </HEAD> <BODY> <!– Adapted from James Goodwill’s Pure JSP   <%@ page language="java" import="java. sql. *" %> <%@ page import= "java. io. *" %>

<% Connection con = null; try { // Load the Driver class file Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); // Make a connection to the ODBC datasource Movie Catalog con = Driver. Manager. get. Connection("jdbc: odbc: Cool. Stocks"); // Create the statement Statement statement = con. create. Statement(); // Use the created statement to SELECT the DATA // FROM the customer Table. Result. Set rs = statement. execute. Query("SELECT * " + "FROM customer"); // Iterate over the Result. Set %>

<TABLE BORDER="1"> <TR> <TH> Customer - ID</TH><TH>Last Name</TH> <TH>First Name</TH> <% while ( rs. next() ) { // get the id, convert to String out. println("<TR>n<TD>" + rs. get. String("id") + "</TD>"); // get the last name out. println("<TD>" + rs. get. String("lname") + "</TD>"); // get the first name out. println("<TD>" + rs. get. String("fname") + "</TD>n</TR"); }

// Close the Result. Set rs. close(); } catch (IOException ioe) { out. println(ioe. get. Message()); } catch (SQLException sqle) { out. println(sqle. get. Message()); } catch (Class. Not. Found. Exception cnfe) { out. println(cnfe. get. Message()); } catch (Exception e) { out. println(e. get. Message()); }

finally { try { if ( con != null ) { // Close the connection no matter what con. close(); } } catch (SQLException sqle) { out. println(sqle. get. Message()); } } %> </BODY> </HTML>

It Works!

An Example Using Connection Pooling The example above opens a connection every time there is a visit. Goodwill presents another approach in chapter 14.

Pooled. Connection. java // Adapted from Goodwill's Pure JSP import java. sql. *; public class Pooled. Connection { // Real JDBC Connection private Connection connection = null; // boolean flag used to determine if connection is in use private boolean inuse = false;

// Constructor that takes the passed in JDBC Connection // and stores it in the connection attribute. public Pooled. Connection(Connection value) { if ( value != null ) { connection = value; } } // Returns a reference to the JDBC Connection public Connection get. Connection() { return connection; }

// Set the status of the Pooled. Connection. public void set. In. Use(boolean value) { inuse = value; } // Returns the current status of the Pooled. Connection. public boolean in. Use() { return inuse; } // Close the real JDBC Connection public void close() { try { connection. close(); } catch (SQLException sqle) { System. err. println(sqle. get. Message()); } } }

Connection. Pool. java // Adapted from James Goodwill's Pure Java import java. sql. *; import java. util. *; public class Connection. Pool { // JDBC Driver Name private String driver = null; // URL of database private String url = null; // Initial number of connections. private int size = 0;

// Username private String username = new String(""); // Password private String password = new String(""); // Vector of JDBC Connections private Vector pool = null; public Connection. Pool() { } // Set the value of the JDBC Driver public void set. Driver(String value) { if ( value != null ) { driver = value; } }

// Get the value of the JDBC Driver public String get. Driver() { return driver; } // Set the URL Pointing to the Datasource public void set. URL(String value ) { if ( value != null ) { url = value; } } // Get the URL Pointing to the Datasource public String get. URL() { return url; }

// Set the initial number of connections public void set. Size(int value) { if ( value > 1 ) { size = value; } } // Get the initial number of connections public int get. Size() { return size; } // Set the username public void set. Username(String value) { if ( value != null ) { username = value; } }

// Get the username public String get. User. Name() { return username; } // Set the password public void set. Password(String value) { if ( value != null ) { password = value; } } // Get the password public String get. Password() { return password; }

// Creates and returns a connection private Connection create. Connection() throws Exception { Connection con = null; // Create a Connection con = Driver. Manager. get. Connection(url, username, password); return con; }

// Initialize the pool public synchronized void initialize. Pool() 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 < 1 ) { throw new Exception("Pool size is less than 1!"); }

// Create the Connections try { // Load the Driver class file Class. for. Name(driver); // Create Connections based on the size member for ( int x = 0; x < size; x++ ) { Connection con = create. Connection(); if ( con != null ) { // Create a Pooled. Connection to encapsulate the // real JDBC Connection Pooled. Connection pcon = new Pooled. Connection(con); // Add the Connection to the pool. add. Connection(pcon); } } }

catch (Exception e) { System. err. println(e. get. Message()); throw new Exception(e. get. Message()); } } // Adds the Pooled. Connection to the pool private void add. Connection(Pooled. Connection 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 Pooled. Connection Object to the vector pool. add. Element(value); }

public synchronized void release. Connection(Connection con) { // find the Pooled. Connection Object for ( int x = 0; x < pool. size(); x++ ) { Pooled. Connection pcon = (Pooled. Connection)pool. element. At(x); // Check for correct Connection if ( pcon. get. Connection() == con ) { System. err. println("Releasing Connection " + x); // Set its inuse attribute to false, which // releases it for use pcon. set. In. Use(false); break; } } }

// Find an available connection public synchronized Connection get. Connection() throws Exception { Pooled. Connection pcon = null; // find a connection not in use for ( int x = 0; x < pool. size(); x++ ) { pcon = (Pooled. Connection)pool. element. At(x); // Check to see if the Connection is in use if ( pcon. in. Use() == false ) { // Mark it as in use pcon. set. In. Use(true); // return the JDBC Connection stored in the // Pooled. Connection object return pcon. get. Connection(); } }

// Could not find a free connection socreate and add a new one try { // Create a new JDBC Connection con = create. Connection(); // Create a new Pooled. Connection, passing it the JDBC Connection pcon = new Pooled. Connection(con); // Mark the connection as in use pcon. set. In. Use(true); // Add the new Pooled. Connection object to the pool. add. Element(pcon); } catch (Exception e) { System. err. println(e. get. Message()); throw new Exception(e. get. Message()); } // return the new Connection return pcon. get. Connection(); }

// When shutting down the pool, you need to first empty it. public synchronized void empty. Pool() { // Iterate over the entire pool closing the // JDBC Connections. for ( int x = 0; x < pool. size(); x++ ) { System. err. println("Closing JDBC Connection " + x); Pooled. Connection pcon = (Pooled. Connection)pool. element. At(x); // If the Pooled. Connection is not in use, close it if ( pcon. in. Use() == false ) { pcon. close(); }

else { // If it is still in use, sleep for 30 seconds and // force close. try { java. lang. Thread. sleep(30000); pcon. close(); } catch (Interrupted. Exception ie) { System. err. println(ie. get. Message()); } } }

JDBCPooled. Example. jsp <html> <body> <%@ page error. Page="errorpage. jsp" %> <%@ page import="java. util. *" %> <%@ page import="java. sql. *" %> <%@ page import= "java. io. *" %> <%@ page import="Connection. Pool" %>  <jsp: use. Bean id="pool" scope="application" class="Connection. Pool" />

<% Connection con = null; try { // The pool is not initialized if ( pool. get. Driver() == null ) { // initialize the pool. set. Driver("sun. jdbc. odbc. Jdbc. Odbc. Driver"); pool. set. URL("jdbc: odbc: Cool. Stocks"); pool. set. Size(5); pool. initialize. Pool(); } // Get a connection from the Connection. Pool con = pool. get. Connection(); // Create the statement Statement statement = con. create. Statement();

// Use the created statement to SELECT the DATA // FROM the customer Table. Result. Set rs = statement. execute. Query("SELECT * " + "FROM customer"); // Iterate over the Result. Set %>  <center> <table border="1" cellspacing="0" cellpadding="2"width="500"> <tr> <TH> Customer - ID</TH><TH>Last Name</TH> <TH>First Name</TH>

<% while ( rs. next() ) { // get the id, convert to String out. println("<TR>n<TD>" + rs. get. String("id") + "</TD>"); // get the last name out. println("<TD>" + rs. get. String("lname") + "</TD>"); // get the first name out. println("<TD>" + rs. get. String("fname") + "</TD>n</TR"); } // Close the Result. Set rs. close(); out. println("</table></center>"); } catch (IOException ioe) { out. println(ioe. get. Message()); }

catch (SQLException sqle) { out. println(sqle. get. Message()); } catch (Class. Not. Found. Exception cnfe) { out. println(cnfe. get. Message()); } catch (Exception e) { out. println(e. get. Message()); } finally { try { if ( con != null ) { // release the connection no matter what pool. release. Connection(con); } }

catch (Exception e) { out. println(e. get. Message()); } } %> </body> </html>

It works too!

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.