Java and Database Connectivity

Author and presenter: Simon Brooke.

The full text of this presentation is online at <URL: http://www.jasmine.org.uk/~simon/bookshelf/papers/inet2001/jdbc/>

Changes to the presentation since your handouts were printed are highlighted like this.

Simon Brooke, 21 Main Street, Auchencairn, Scotland.

What you're going to achieve today

Some of you will know some of this already. Please be patient while I cover the bits you know, someone else may not.

How we're going to get there

Breaks

Nearest WCs, how to get to coffee?

Before we start: what do you know

We've got a lot of ground to cover Just so I can know which bits to concentrate on and which bits to skip, what do you know about...
nothing a little can do it expert
HTML        
Java        
SQL        

Before we start: Namespace

[get participants to write their names on bits of paper. Make sure there are not two in the class with the same name. If there are, get them to add something to their names to disambiguate]

Background to the technology

The Internet and the Web

Open, heterogenous network of networks, carrying distributed hypertext

Open:
the standards are published, and are not protected by patents, non-disclosure agreements, and so on - anyone can copy them.
Heterogenous:
the standards aren't based on the facilites of any one proprietary sort of computer - any computer can be connected
Networks of networks:
not every computer everywhere has to be able to connect to the same network standards, provided that the networks themselves can intercommunicate.

A brief history of Distributed Hypertext

or 'none of this is new' part one

Project Xanadu, Ted Nelson 1960

Distributed hypertext over heterogenous networks

TCP/IP, Xerox PARC, 1970s

Transmission of data over unreliable networks of networks

HTML, Tim Berners-Lee, CERN, 1990

Distributed hypertext over heterogenous networks - much simpler than Xanadu

Forms in HTML

What is Java?

Java: history and background

or 'none of this is new' part two

Brief history of programming languages

LISP, John McCarthy, 1959

BCPL: Martin Richards, Cambridge University, 1969

C: Thompson & Richie, Bell Labs, 1972

Smalltalk, Goldberg, Kay and others, Xerox PARC, 1973

Oak: James Gosling, Sun Microsystems, 1990

Java: James Gosling et al, Sun Microsystems 1995

JDBC: Sun Microsystems 1996

What is SQL

SQL: History and Background

or 'none of this is new' part three

Standard used by JDBC is ANSI 92

The big question: How do you pronounce it?

Types of database systems

In summary:

Java, SQL and Internet technology

There are proprietary, vendor specific ways of doing all these things...

Getting started with Java

A bit about Object Orientation (i): taxonomy

Draw it - extend it.

A bit about Object Orientation (ii): languages

Main features of the language

A bit about syntax: example java source

package uk.co.weft.clock;

import java.lang.*;     // import general utility classes
import java.awt.*;      // for graphics handling

/** A subclass of Hand which is just a blob rotating at a fixed
 *  length about the centre. All the mechanism for moving the 
 *  hand is in Hand.
 *
 *  @author Simon Brooke (simon@jasmine.org.uk)
 *  @version $Revision: 1.9 $
 */

public class BlobHand extends Hand
{
    int radius;         // the radius of my blob

    /** initialise myself; in addition to the things hands have
     *  generically, I have a radius */
    public BlobHand( int l, HandDriver d, int r)
    {
    super( l, d);

    radius = r;
    }

    /** just a blob round my end position */
    public void paint( Graphics g)
    {
        g.setColor( colour);

        g.fillOval( end.x - radius, end.y - radius, 
            2 * radius, 2 * radius);
    }
}

A bit about syntax: declaring and importing packages

package uk.co.weft.clock;

This is the package this class is in

import java.lang.*;     // import general utility classes
import java.awt.*;      // for graphics handling

These are the packages it uses

A bit about syntax: comments

package uk.co.weft.clock;

import java.lang.*;     // import general utility classes
import java.awt.*;      // for graphics handling

/** A subclass of Hand which is just a blob rotating at a fixed
 *  length about the centre. All the mechanism for moving the 
 *  hand is in Hand.
 *
 *  @author Simon Brooke (simon@jasmine.org.uk)
 *  @version $Revision: 1.9 $
 */

A bit about syntax: declaring a class

public final class BlobHand extends Hand
{
    int radius;         // the radius of my blob

A bit about syntax: declaring a constructor

A constructor is a special method called to construct an instance of a class. It has the same name as the class, and no return type declaration

    /** initialise myself; in addition to the things hands have
     *  generically, I have a radius */

Again, a 'javadoc' comment. This one documents this constructor.

    public BlobHand( int l, HandDriver d, int r)

This constructor takes three arguments, respectively an int, a HandDriver, and another int. An int is a primitive data type which is not an object; a HandDriver is an object whose class is either HandDriver or some subclass of HandDriver

    {
        super( l, d);

The first thing this constructor does is to pass it's first two arguments 'up the tree' to its superclass's constructor, which knows how to deal with them.

        radius = r;
    }

The superclass doesn't have a radius, so it can't know how to deal with it. So initialise it here.

A bit about syntax: declaring a method

    /** just a blob round my end position */
    public void paint( Graphics g)
    {
        g.setColor( colour);

        g.fillOval( end.x - radius, end.y - radius, 
            2 * radius, 2 * radius);
    }

Let's see that happen...

static image of clock applet

Using packages

The classpath and using archives

How does Java find the classes it needs?

Java: Reprise

This only scratches the surface - there is masses more to Java but we won't have time today.

Getting started with SQL

What is a Relational Database?

What is a Table?

Representing a set of things in the domain

Example table one: WORKSHOP

Columns
Workshop Tutor Title Venue Date
Rows 2 Simon Brooke Java and Database Connectivity small 07-17-2000
7 Simon Brooke Moving to XML small 07-18-2000
Primary key

Example table two: ATTENDEE

Attendee Name Age Sex Country
1 Jon Smith 37 M UK
2 Jane Doe 42 F US

Example table three: VENUE

Venue Name Capacity
small The small room 20
mid The middle-sized room 60
main The auditorium 2000

Basic operations on a database

Create
creates tables
Insert
creates rows in tables
Select
selects information from rows in tables
Update
changes rows in tables
Delete
deletes rows from tables
Drop
drops tables

Create

Creates a new table (and other things...)

    create table WORKSHOP 
    (
    Workshop int not null primary key,
    Tutor varchar( 40) not null,
    Title varchar( 80) not null,
    Venue varchar( 8)
    );

Insert

Inserts a new row (or rows) into an existing table

    insert into WORKSHOP 
    (Workshop, Tutor, Title) 
        values ( 4, 'Gordon Howell', 
         'E-Business in Practice');

Select

Selects information from rows in tables

    select Title, Venue from Workshop 
     where Tutor = 'Simon Brooke';

We'll come back to this...

Update

Changes values in an existing row (or rows) in an existing table

    update WORKSHOP 
       set Venue = 'mid' 
     where Tutor = 'Simon Brooke';

Delete

Deletes one or more rows from an existing table

    delete from WORKSHOP 
      where Tutor = 'Simon Brooke';

    delete from WORKSHOP;

Drop

Deletes an existing table entirely

    drop table workshop;

Relationships: One to many (i)

What's the relationship between the workshop and the venue?

Easy to describe:

entity-relationship diagram

One to many:

Relationships: One to many (ii)

Easy to represent:

the table at the many end has a column (a foreign key field) which holds the value of the primary key field at the one end.

Relationships: Many to many (i)

What's the relationship between the attendee and the workshop?

Easy to describe:

entity-relationship diagram

Many to Many

Relationships: Many to many (ii)

Hard to represent

Link Table

We need a linking table

entity-relationship diagram: linking table

Attendance:

Workshop Attendee
2 1
4 2
7 1
7 2

Link (or 'linking') table

Join

Once we have relationships, we can join tables to produce more useful data.

SQL: Reprise

Table:
Primary key:
Foreign keys
Join

Other things in SQL which we may use

Views
named joins 
    create view myview as select ...
Transactions
Groups of statements which are executed together, such that if any one of them fails, the effects of all of them can be reversed.
    begin
    delete from attendance where workshop = 7;
    delete from workshop where workshop = 7;
    commit;
Permisssions
    grant select, insert, update, delete 
       on venue 
       to simon;

    revoke update, delete 
        on attendance
      from simon;

    revoke insert, update, delete 
        on venue, attendee, attendance 
      from public;

There's lots more in SQL but we won't have time today.

Getting started with JDBC

What is JDBC (i)

'Java Database Connectivity'

What is JDBC (ii)

Configuring JDBC

Where is the database?
The URL
How do we talk to the database?
The Driver
Who are we?
The username and password

The URL

The Driver

The username and password

Exercise period (i)

Use the supplied SQLExec class to talk to the supplied WORKSHOPS database.

Programming with JDBC

Importing the Package

import java.sql.*;

Registering the Driver

    Class.forName( driverName)

Getting a Connection

    Connection c;

    try
        {
            c = DriverManager.getConnection( url, user, pass);
        }
    catch ( SQLException e)
        {
            out.println( "Could not connect to database " + url);
            out.println( "Does it exist? Are you authorised?");
        }

Creating a Statement

Before you can talk to the database, you need a statement

        Statement stt = c.createStatement();

Getting a ResultSet

You get hold of the data by asking the statement for a ResultSet

    ResultSet r = s.executeQuery( q);

    while ( r.next())
    {
        // do things with the row
    }

If there's time, show examples of doing things with ResultSets, e.g. DataMenuWidget.getOptions()

Putting it all together: untidily

        ResultSet r =
            DriverManager.getConnection().createStatement().executeQuery(
                "select * from ATTENDEE");

        while ( r.next())
        {
            // do things with the row
        }

Don't do this!

Putting it all together: tidily

        Connection c = DriverManager.getConnection( url, user, pass);
        Statement s = c.createStatement();
        String q = "select * from ATTENDEE";
        ResultSet r = s.executeQuery( q);

        while ( r.next())
        {
            // do things with the row
        }

        s.close();          // close the statement
        c.close();          // close the connection

Using resources: Why be tidy?

Using resources: Connection pooling

Transactions with JDBC

        Connection c = DriverManager.getConnection( url, user, pass);

        c.setAutoCommit( false)

        // Do some things which need to be done together

        c.commit();     // commit them

        // Do some more things which need to be done together

        c.commit();     // commit them

        c.close();

Gotchas

Exercise period (ii)

Try to get at least one person in each group who is going to be at tomorrow's XML course - or, at worst, don't let the XML course people all bunch up into one group. Try to encourage groups to do their own analysis and not copy each others'. Ideally I want to end up the day with at least two quite different databases.

Building a (Web based) Application

Jacquard toolkit (i)

Jacquard toolkit (ii): htform

Jacquard toolkit (iii): dbutil

Jacquard toolkit (iv): domutil

Using Jacquard

Example Jacquard Form

import javax.servlet.*;
import javax.servlet.http.*;
import uk.co.weft.dbutil.*;
import uk.co.weft.htform.*;
import java.util.Stack;

/** an example form which allows you to enter and manipulate WORKSHOP
 *  records */
public class WorkshopForm extends TableWrapperForm
{
    /** Initialise me. Principally, set up my widgets */
    public void init( Context config)
        throws InitialisationException
    {
        table = "WORKSHOP";        // set up my table
        keyField = "Workshop";     // and my primary key

        addWidget( new 
            Widget( "Tutor", "Tutor", 
            "The Tutor of this workshop"));
                // add a simple text widget for the
                // tutor's name

        Widget w = addWidget( new 
            Widget( "Title", "Title", 
            "The title of this wokshop."));
                            // another simple text input fo the title  
        w.setSize( 80);     // which is extra long...
        w.setMandatory( "You must supply a title for this wokshop");
                // and which you must complete before proceding

        addWidget( new 
            SimpleDataMenuWidget( "Venue", "Venue", 
                  "The venue for this workshop",
                  "VENUE", "Venue", "Name"));
                // a menu constructed from another
                // table for the venue
    
        addWidget( new DateWidget( "Date", "Date", 
                   "The date of this workshop"));
                // a date, oddly enough!

        addWidget( new 
           LinkTableWidget( "ATTENDANCE", "Attendees",
                 "People who are expected to take part in this workshop.", 
                 false, 7,
                 "WORKSHOP", "ATTENDEE", "Workshop", 
                 "Attendee", "Name"));
                                // and a link-table widget to set up attendees

        super.init( config);    // finally do my superclass configuration
    }

    /** When a workshop is deleted from the database, we don't want to
     *  leave orphanced attendances hanging about. So drop my
     *  attendance links before dropping me! Note
     *  that you don't need methods like this if the database supports
     *  proper referential integrity checks and triggers - which good
     *  databases do.
     *
     *  @param context the context which contains the identifier of
     *  the record to be dropped
     *  @exception throws DataStoreException if delete fails.
     */
    protected void drop( PageContext context)
        throws DataStoreException, ServletException
    {
        Stack drops = new Stack();
        Object id = context.get( keyField);

        drops.push( "delete from ATTENDANCE " + 
                    "where ATTENDANCE.Workshop = " +
                    id.toString());
                                // it's a stack, so push them in reverse order

        super.drop( context, id, drops);
    }
}

Exercise period (iii)

And now, the end is near...

What have you achieved?

Why is this good technology? (ii)

Why is this good technology? (ii)

What should you do next?

Resources

Java

Java(tm) Home Page: http://java.sun.com/
Java home page at Sun.
news:comp.lang.java.databases
Newsgroup for JDBC related issues - recommended
news:comp.lang.java.programmer
Newsgroup for general Java releated issues - a bit noisy but often useful.

Documentation

Java Documentation (primary site): http://www.javasoft.com/docs/index.html
This is the source.... (but if you're doing it properly you need to download a local copy).
Download The Java Tutorial: http://java.sun.com/docs/books/tutorial/information/download.html
This is where to download it from.
Java in a Nutshell, 2nd Edition : A Desktop Quick Reference for Java Programmers: http://www.oreilly.com/catalog/javanut2/
An enormously useful reference book, but...
Java Examples in a Nutshell : A Companion Volume to Java in a Nutshell: http://www.oreilly.com/catalog/jenut/
... if you're learning Java, the examples book is actually far more useful because it gives masses of example code, neatly laid out and annotated, to work through. Strongly recommended.
Java Programmers FAQ: http://www.afu.com/intro.html

Tools

Free/Open Source tools

Java-Linux: http://www.blackdown.org/
Home page for the team responsible for porting Java to Linux.
TYA -- Open Source Java JIT compiler: http://www.dragon1.net/software/tya/
'Just In Time' compilers can substantially improve the performance of Java programs at run time. TYA is a '100% unofficial' JIT-compiler designed as an add-on to Randy Chapman's port of JDK 1.0.2 for Linux
(x86). Later I have added some changes in TYA code for working together with the newer 1.1.x ports by Steve
Welcome to Java WebIDE!: http://www.chamisplace.com/prog/javaide/
Weird but interesting. A complete Java Integrated Development Environment implemented at server-side and delivered as a Web page...
RMI-JDBC - JDBC to remote hosts: http://dyade.inrialpes.fr/mediation/download/
Freeware JDBC middleware layer from INRIA.

Commercial tools

Simplicity for Java Homepage: http://www.datarepresentations.com/
The most interesting commercial Java IDE I've yet seen and the only one I would actually pay money for. 100% pure Java; free evaluation download.
Welcome to NetBeans: http://www.netbeans.com/index.html
Another interesting commercial Java IDE written in 100% pure Java. Free evaluation download.
IBM VisualAge for Java: Download: http://www.software.ibm.com/ad/vajava/download.htm
IBM's Java IDE
Visual Café 3.0: http://www.symantec.com/region/uk/product/vcafe/
Probably the best of the mainstream commercial IDEs
Metamata Home Page: http://www.metamata.com/

Resources

Gamelan: Earthweb's Java Directory: http://www.gamelan.com/
An onlline searchable resource of both freeware and commercial Java components. Useful, but these days overloaded and slow.
www.jrefinery.com: http://www.jrefinery.com/
Algorithmics in Java: http://students.ceid.upatras.gr/~papagel/project/contents.htm

Servlets

Servlet Engines

Locomotive: http://www.locomotive.org/
Enhydra: http://www.enhydra.org/
Bull EJB: home page: http://www.bullsoft.com/ejb/
Caucho Technology Products: http://www.caucho.com/products/resin/index.html

The Java Apache Project: http://java.apache.org/
The best way to serve servlets with Apache.
Servlet Central: The Server-side Java Magazine: http://www.servletcentral.com/
An online newsletter aiming to provide quality information for servlet developers. On the whole, so far, seems good.
View Source: Java Servlets in Netscape Enterprise Server: http://developer.netscape.com/viewsource/kadel_servlet.html
Live Software, Inc.: http://www.livesoftware.com/
Vendors of a Servlet server 'JRun', and, possibly more importantly for us, a ColdFusion interpreter as a Java Servlet
Purple Servlet FAQ by Alex Chaffee: http://www.purpletech.com/java/servlet-faq/
Java Apache Project: How do I create an image with a servlet, for example, a graph out of a list of values?: http://java.apache.org/faq/fom-serve/cache/204.html
WebCream - Java to HTML converter providing automatic conversion of Swing and AWT to HTML: http://creamtec.com:30422/webcream/

Introduction to Java Course - Course Selections: http://www.ibm.com/java/education/intro/courseoptions.htm
Netscape JDK 1.1 Update Support: http://developer.netscape.com/software/jdk/download.html
How to make your older Netscape browser work with Java 1.1. Less important now that Netscape 4.5 does.
Java server performance (InfoWorld): http://www.infoworld.com/cgi-bin/displayTC.pl?/980914analysis.htm#contents
Performance comparison of servlet servers.
IBM Aglets Workbench - Home Page: http://www.ibm.co.jp/trl/aglets/
A proposed standard (with free supporting software) for mobile intelligent agents. Fascinating; worth study.
Java Software: http://www-uk.hpl.hp.com/people/ak/java/
The Kaffe Homepage: http://www.kaffe.org/
GJ: Generic Java: http://www.cis.unisa.edu.au/~pizza/gj/
EJBoss: http://www.ejboss.org/
Ace's Hardware: http://www.aceshardware.com/Spades/read.php?article_id=153
Java Network Programming FAQ: http://www.davidreilly.com/java/java_network_programming/

give me feedback on this page // show previous feedback on this page