SE452: Lecture 7 (JDBC)

Contents [0/12]

Overview of Today's Class [1/12]
JDBC [2/12]
JDBC Versions [3/12]
JDBC Drivers [4/12]
Using JDBC [5/12]
Loading a JDBC Driver [6/12]
JDBC: Statements [7/12]
JDBC: Queries and Updates [8/12]
JDBC: ResultSets [9/12]
JDBC: Managing Resources [10/12]
JDBC: Mapping SQL to Java Types [11/12]
Homework [12/12]

Overview of Today's Class [1/12]

Quiz I review

Homework Questions

JDBC

JNDI

JDBC [2/12]

The Java Database access APIs - (Not actually an acronym for Java DataBase Connectivity)

JDBC Goals

JDBC Versions [3/12]

1.2

2.0

3.0

4.0

JDBC Drivers [4/12]

To access a database, you need a driver for that vendor

Using JDBC [5/12]

JDBC classes/interfaces are in two packages:

To use JDBC in your program:

  1. Load the JDBC Driver
  2. Establish the connection (or get the Connection from the DataSource)
  3. Send SQL Statements
  4. Process results
  5. Close the connection (and other resources)

For most server applications, we use Connection pools that are mapped to a javax.sql.DataSource. So the steps involve:

  1. Define the connection pool (driver and database)
  2. Lookup the DataSource using JNDI
  3. Obtain a connection from the DataSource
  4. Send SQL Statements
  5. Process results
  6. Close the connection (and other resources)

It is important to be aware of the resources that you are using when writing a JDBC program. You need to explicitly close connections, statements, etc.

Loading a JDBC Driver [6/12]

Need to know the driver Class name

The java.sql.DriverManager is responsible for loading the Driver files in memory. It uses the jdbc.drivers system property to load Driver classes.

Or, use Class.forName() at any time to load dynamically: Class.forName("my.sql.Driver");

Drivers can also be managed by the implementation of the javax.sql.DataSource interface via JNDI. This is the preferred method for obtaining connections to a JDBC data source. We'll talk about this in a minute.

Getting a connection:


        DriverManager.getConnection(url)
        DriverManager.getConnection(url, username, password)

All DriverManager methods are declared static.

JDBC: Statements [7/12]

From the Connection class:

The statement is what you want to do with the database, i.e. query it or update it.

JDBC: Queries and Updates [8/12]

From the Statement Class

JDBC: ResultSets [9/12]

The ResultSet class

When processing ResultSets, you will often see code like this:

    while (rs.next()) {
            // do something with row
    }

For the current row, can get data by using


    Type getType(int index)
    Type getType(String name)

JDBC: Managing Resources [10/12]

It is important to note that the resources you use in JDBC correspond to server resources. These must be manged proactively by the client. This means you need to be careful with the references that you have to these resources.

Impacts are to:

Make use of good exception handling practices, as well as finally clauses to keep the code clean

JDBC: Mapping SQL to Java Types [11/12] 

            BigDecimal 		DECIMAL NUMERIC
            Blob 			BLOB
            boolean 			BIT
            byte 			TINYINT
            bytes (byte[]) 		BINARY VARBINARY
            Clob 			CLOB
            java.sql.Date		DATE
            double 			DOUBLE FLOAT
            float 			REAL
            int 				INTEGER
            long 			BIGINT
            Object 			STRUCT JAVA OBJECT
            short 			SMALLINT
            java.sql.Time		TIME
            java.sql.TimeStamp	TIMESTAMP

Homework [12/12]

Goal: To write a command line application that creates a database to store persistent data.

  1. Download and install the hsqldb database.
  2. Create a command line program called BuildDB that builds your database. Follow the examle supplied in class, it should take three arguments, the url, username, and password for the database. I'll use the program to create your database.

Before submitting your code, practice building the entire environment from scratch to ensure that your database building code is correct and that your configurations are set correctly.

Do not write code that has any paths/drivers/urls hardcoded in it. Do not open any text files in your code for data (I know this isn't what we would do in the real world, but it is easier to test and grade). Everything should be configurable via the supplied xml files (config.xml and web.xml)

Grading Breakdown:

ItemPoints
Program to build database20
Proper Submission10
Total:30

Revised: 11/2/2003