B337 Lecture Notes



Topic 5

JDBC | |

• Database systems, database languages

• Relational database model

• Structured Query Language (SQL)

• A note on JDBC and ODBC

• A Java programming example

• A note on the ResultSet interface

• JDBC, servlets, RMI, CORBA

• Transaction processing

Database systems

A database system is an integrated collection of data. A database system involves

• the data itself,

• the hardware on which the data reside,

• the software (called a database management system or DBMS) that controls the storage and retrieval of data, and

• of course, the users themselves.

Having a database system allows data redundancy and inconsistency to be avoided, data integrity to be maintained, data standards to be enforced, (most importantly) the sharing of the data, and security restriction to be enforced.

Database systems organize data in a manner that allows one to pose queries on the data (i.e. to request information that satisfies given criteria).

A distributed database is a database that is spread across the computer systems of network. Usually in such systems each data item is stored at the location where it is most frequently used, but the database is accessible to other network users.

Database languages

Users access a database via statements in a database language. Application programs may use a conventional high-level language like Java, C, C++, Visual Basic, or Pascal.

A user may make requests of the database in a specially designed query language that makes it easy to express requests in the context of a particular application.

Such languages are referred to as host languages. Each host language ordinarily includes a database sublanguage (DSL) concerned with the specifics of database objects and operations.

Each database sublanguage is a combination of two languages:

• a data definition language (DDL) which provides facilities for defining database objects

• a data manipulation language (DML) which provides features for specifying the processing to be performed on database objects

The query language that we will use in this topic is the popular Structured Query Language (SQL).

SQL provides both DDL and DML.

The most popular style of database system on the kinds of computers that use Java is the relational database, although in recent years, object-oriented databases have also become popular.

Java enables programmers to write code that uses SQL queries to access the information in relational database systems. Some popular relational database software packages include Microsoft Access, Sybase, Oracle, Informix, Microsoft SQL server.

In this topic, we will use the Java Database Connectivity (JDBC) to manipulate a Microsoft Access database.

Relational Database Model

The relational database model developed by Codd is a logical representation of the data that allows the relationships between the data to be considered without concerning oneself with the physical implementation of the data structures.

A relational database is composed of tables (rows and columns). A sample table is given below:

The primary key is unique and is used for data referencing. Primary keys are normally used in tables but not compulsory. If used, primary key fields cannot contain duplicate values. The primary key can be composed of more than one column (or field).

Certain subsets of a table can be created by the so-called projection operation. Several smaller tables can also be combined into a larger, more complex table. The combination operation is called a join operation.

Eg. we can use the projection operation to create a new table called DEPARTMENT-LOCATOR whose purpose is to show where departments are located:

Structured Query Language

SQL queries are composed of SQL keywords and table name(s) and possibly field (column) names. Below is a table of SQL keywords:

|SQL keyword |Description |

|SELECT |Select (retrieve) fields from one or more tables |

|FROM |Tables from which to get fields. Required in every SELECT |

|WHERE |Criteria for selection that determine the rows to be retrieved |

|GROUP BY |How to group records |

|HAVING |Used with the GROUP BY clause to specify criteria for grouping records in query results |

|ORDER BY |Criteria for ordering of records |

|INNER JOIN |Used for merging multiple tables into a larger, more complex one |

|INSERT INTO |Used for inserting a record into a table |

|UPDATE |Used for updating an existing record |

|SET |Used in conjunction with UPDATE |

Although SQL queries are case insensitive, SQL keywords are normally written in uppercase while column names are normally in mixed cases for legibility.

Consider a database consisting of 4 tables whose names are Authors, Publishers, AuthorISBN and Titles respectively, as shown on the following page:

(primary keys are shown in bold)

We will use these tables for our SQL query examples.

The PublisherID field in the Titles table is referred to as the foreign key – a field in a table for which every entry has a unique value in another table and where the field in the other table is the primary key for that table (ie. PublisherID in the Publishers table).

Likewise, the AuthorID field of the AuthorISBN table is a foreign key of the AuthorID field of the Authors table.

Foreign keys are specified when creating the table.

The foreign keys help maintain the rule of referential integrity. They enable information from multiple tables to be joined for analysis purpose.

There is a one-to-many relationship between a primary key and its corresponding foreign key.

• Basic SELECT query

has one of the following forms:

SELECT * FROM TableName

SELECT field1, …, fieldn FROM TableName

Eg.

SELECT * FROM Authors

SELECT AuthorID, LastName FROM Authors

• WHERE clause

has the following form:

SELECT * FROM TableName WHERE criteria

The WHERE clause condition can contain operators , =, =, , and LIKE. The LIKE operator is used for pattern matching with wildcard characters asterisk (*) and question mark (?).

Eg.

SELECT * FROM Authors WHERE YearBorn > ’1960’

SELECT * FROM Authors WHERE LastName LIKE ’D*’

SELECT * FROM Authors WHERE LastName

LIKE ’?[a-I]*’

NOTE: Not all database systems support the LIKE operator.

Criteria in a WHERE clause can be compound, ie. containing AND and/or OR keywords that join up simple criteria.

Eg.

SELECT * FROM Titles WHERE

YearPublished > ’1992’ AND

YearPublished < ’1999’

SELECT * FROM Titles WHERE

EditionNumber = ’2’ OR

EditionNumber = ’3’

• Grouping records using the GROUP BY clause

The GROUP BY clause has a similar effect as the ORDER BY clause except all the fields (or columns) must be specified and the wildcard character ‘*’ cannot be used. The simplest format of this clause is:

SELECT field_1,…,field_n FROM Table

GROUP BY Field_2, Fieldn, …,Field1

Here, field_i's and Field_i's are the same fields but in different orders and there is a one-to-one correspondence between them. Eg.

SELECT LastName, FirstName, YearBorn

FROM Authors GROUP BY

YearBorn, Lastname, FirstName

NOTE: SELECT * FROM Table GROUP BY … is not allowed since it uses the wildcard character ‘*’.

NOTE: the GROUP BY clause can be replaced with the ORDER BY clause (see later) and is therefore seldom used. For instance, the above SELECT statement can be replaced with

SELECT LastName, FirstName, YearBorn

FROM Authors ORDER BY

YearBorn, Lastname, FirstName

• The Having clause

This clause must be used with the GROUP BY clause. The simplest form of the clause is:

SELECT field1, ..., fieldn FROM TableName

GROUP BY Field1, ..., Fieldn

HAVING criteria

where the “GROUP BY…” phrase must appear before the “HAVING…” phrase. The HAVING clause can be replaced with the WHERE clause. The latter is more commonly used in SQL queries. For examples, the following two SQL queries produce the same output:

SELECT LastName, FirstName, YearBorn

GROUP BY LastName, FirstName, YearBorn

HAVING YearBorn > 1930

SELECT LastName, FirstName, YearBorn

WHERE YearBorn > 1930

ORDER BY LastName, FirstName, YearBorn

Note that, unlike the first example, the WHERE clause for the criteria must appear before the ORDER BY clause in the second example.

• The ORDER BY clause

The result of a query can be arranged in ascending or descending order using the optional ORDER BY clause. The simplest form of this clause is:

SELECT * FROM TableName ORDER BY field ASC

SELECT * FROM TableName ORDER BY field DESC

where ASC specifies ascending order, DESC specifies descending order.

Eg.

SELECT * FROM Authors ORDER BY LastName ASC

NOTE: Multiple fields can be used for ordering purpose:

ORDER BY field1 SortingOrder, field2 SortingOrder

where SortingOrder is either ASC or DESC. SortingOrder does not have to be identical for each field. If SortingOrder is not given then ASC is assumed. Eg.

SELECT * FROM Authors ORDER BY LastName,

FirstName

SELECT LastName, FirstName, YearBorn FROM

Authors ORDER BY YearBorn

The WHERE and ORDER BY clauses can be combined in one query.

• Using INNER JOIN to merge data from multiple tables

An INNER JOIN clause merges records from two or more tables by testing for matching values in a field that is common to both tables. The simplest form of an INNER JOIN clause is

SELECT * FROM Table1 INNER JOIN Table2 ON

Table1.field = Table2.field

Eg.

SELECT FirstName, LastName, ISBN FROM

Authors INNER JOIN AuthorISBN

ON Authors.AuthorID = AuthorISBN.AuthorID

ORDER BY LastName, FirstName

The keyword INNER JOIN is seldom used since the queries with the INNER JOIN keywords can be replaced with those without, eg. the above query can be replaced with

SELECT FirstName, LastName, ISBN FROM

Authors, AuthorISBN

WHERE Authors.AuthorID = AuthorISBN.AuthorID

ORDER BY LastName, FirstName

to obtain the same result.

Here, the ON keyword that must be used in conjunction with the INNER JOIN keyword is replaced with the WHERE keyword.

When joining multiple tables, errors or null results may occur if certain columns have missing information. Consider the following tables in a Company database (file Company.mdb)

The SQL query

SELECT Department.DeptName, Employee.Lastname

FROM Department, Employee where

Department.DeptNumber = Employee.DeptNumber

works despite DeptNumber 40 being not found in the Employee table.

However, some database systems which support the outer join operation would automatically fill in default value (e.g. null or NA) for missing information.

• Using INSERT INTO to insert a record

An INSERT INTO clause inserts a record into a table. The basic format of the clause is

INSERT INTO TableName (ColumnName1,

ColumnName2, ...) VALUES (’value1’,

’value2’, ...)

Eg.

INSERT INTO Authors (AuthorID, FirtName,

LastName, YearBorn) VALUES

(’4’, ’Joe’, ’Wigglesworth’, ’1955’)

Note that not all the fields must be given in an INSERT INTO statement. It is a good practice to leave out the primary key and let it be generated automatically.

• Using UPDATE to update an existing record

A basic UPDATE SQL statement has the form

UPDATE TableName SET ColumnName1=’value1’,

ColumnName2=’value2’, ...

WHERE criteria

Note that not all the columns have to be included in the UPDATE statement.

Note also the use of single quotes in the

Statement.

Eg.

UPDATE Authors SET FirstName=’John’,

LastName=’Donn’ WHERE AuthorID=3

UPDATE Authors SET FirstName=’John’

WHERE LastName=’Donn’

The second example above updates the first

name of all the records that have the last name

equal to “Donn”

JDBC

• API for database access.

• Allows a developer to access a database at any location to query, insert, update, etc.

• Similar API’s exist in most popular programming languages.

• Three main goals when developing

o JDBC should be a SQL-level API.

o JDBC should capitalize on the experience of existing database APIs.

o JDBC should be simple.

• An SQL-level API means that JDBC allows you to construct SQL statements and embed them inside Java API calls.

• In short, you are basically using SQL. But JDBC lets you smoothly translate between the world of the database and the world of the Java application.

• Your results from the database, for instance, are returned as Java objects, and access problems get thrown as exceptions.

• JDBC is probably more difficult than other API’s because of the use of classes and exceptions. However, to a developer who has reasonable java skills, JDBC is still an easy to use API for working with databases.

A note on JDBC and ODBC

1. ODBC (Open Database Connectivity) is a Microsoft standard API that provides access to a multitude of existing databases.

2. JDBC is a pure Java API

3. ODBC is a C or C++ API, using lots of pointers, it is mostly Microsoft Windows-platform specific.

4. JDBC works on any platform running the Java Virtual Machine (JVM).

5. JDBC is also an object-oriented API and so is easier to learn and implement than the ODBC API.

6. JDBC provides the ability to pass new SQL statements directly to the data source.

7. JDBC architecture supports the functionality of common database bridges such as ODBC.

You can access nearly any database through the JDBC API including Microsoft Access, Informix, Oracle, Sybase, xBase.

JDBC provides this broad database support through 4 types of connectivity (via drivers). The JDBC-ODBC bridge belongs to the first type of driver:

Type 1: JDBC-ODBC bridge

o JDBC-ODBC bridge driver provided by Sun with the Java SDK is an example of a type 1 driver

o JDBC-ODBC allows existing ODBC drivers to be used through the JDBC API

o Not the best solution – requires ODBC driver to be installed

o A type 1 driver merely translates queries obtained by the driver into equivalent ODBC queries and forwards directly to the ODBC driver via native API calls.

The JDBC-ODBC bridge provided by the Java Development Kit (jdk) allows one to manipulate a database (in this case, a Microsoft Access database) via JDBC so long as the database has been registered as an ODBC Data Source. Appendix A shows the steps for the registration.

The registration above requires that the ODBC driver has been installed on the machine. Several products (eg. most Microsoft Office) provide the option to install the ODBC driver.

Visit the web site for the latest info from Sun regarding JDBC drivers.

The other 3 types of JDBC drivers are:

2) Native-API, partly Java eg java program uses Java Native Interface to call a database specific API written in C++ to access a database directly

3) JDBC-Net pure Java driver: takes JDBC requests, turns them into a network-protocol and sends to a server which interacts with a database

4) Native-protocol pure Java driver: database-specific network protocol allowing direct connection to a database.

Using JDBC (Basic Steps)

• The examples used in these notes are available on the ftp site. You will need to setup up an access ODBC Driver with the name Publications to run the programs.

1. Specify the location of the database. This will generally involve specifying the ODBC or type 4 driver.

a. ODBC

String url = ”jdbc:odbc:Publications”;

here we specify that jdbc is the protocol, we are using odbc as the subprotocol, and Publications is the name of the odbc that must exist on the system we are running the application from.

b. Type 4

String url = ”jdbc:mysql://localhost/ict337

here we specify that jdbc is the protocol, we are connecting to a mysql database server and requesting a connection to the ict337 database.

2. Specify the driver to use for the connection.

a. ODBC

String dbDriver="sun.jdbc.odbc.JdbcOdbcDriver”;

here we specify that jdbc odbc bridge driver that is built-in to JDBC.

b. Type 4

String dbDriver="com.mysql.jdbc.Driver";

here we specify a type 4 driver for MySql. This driver will need to be present in the classpath of the machine, but nothing special needs to be done to make it ready for use. Normally you would just package this up with your application.

3. Create an instance of the class of the JDBC driver.

Class.forName(dbDriver);

4. Create a connection to the database with the DriverManager class

Connection con = DriverManager.getConnection(url, username, password);

Once a Driver recognizes your URL, it creates a database connection using the properties you

specified. It then provides the DriverManager with a java.sql.Connection implementation

representing that database connection. The DriverManager then passes that Connection object

back to the application.

5. Do something with/to database.

6. Close the connection

con.close();

Simple example (ex1Connection.java)

import java.sql.*;

public class ex1Connection

{

static public void main(String args[])

{

//String url = "jdbc:mysql://localhost/ict337";

//String dbDriver="com.mysql.jdbc.Driver";

//access

String url="jdbc:odbc:Publications";

String dbDriver="sun.jdbc.odbc.JdbcOdbcDriver";

String username="your";

String password="mother";

Connection connection = null;

try

{ // load the driver

Class.forName(dbDriver);

}

catch( Exception e )

{ // problem loading driver, class not exist?

e.printStackTrace( );

return;

}

try

{

connection = DriverManager.getConnection(url,username,password);

System.out.println("Connection successful!");

// Do whatever queries or updates you want here!!!

}

catch( SQLException e )

{

e.printStackTrace( );

}

finally

{

if( connection != null )

{

try

{

connection.close( );

System.out.println("connection closed successfully");

}

catch( SQLException e )

{

e.printStackTrace( );

}

}

}

}

}

Database Access

The fundamental classes for database access are Statement, Prepared Statement, and ResultSet.

Statement Class

• The Statement class is the most basic of three JDBC classes representing SQL statements.

• It performs all of the basic SQL statements. In general, a simple database transaction uses only one of the three statement execution methods in the Statement class.

• The first such method, executeQuery(), takes a SQL String as an argument and returns a ResultSet object. This method should be used for any SQL calls that expect to return data from the database (select).

• Update statements, on the other hand, are executed using the executeUpdate( ) method. This method returns the number of affected rows. Use this for update, insert, delete, etc.

• Statement also provides an execute() method for when you don’t know whether the query is a select query or an update.

Simple Query Method (ex2SelectQuery.java for full program)

public static void query(Connection con, String sql)

{

try

{

//create the sql statement object

Statement s = con.createStatement();

//execute the sql and get the result

ResultSet res=s.executeQuery(sql);

if(res==null)

{

System.out.println("no results");

return;

}

//loop through the results and display each row

while(res.next())

{

//print out the publisherID

System.out.print(res.getInt("PublisherID")+" ");

//print out the publisher name

System.out.println(res.getString("PublisherName"));

}

}

catch(SQLException e)

{

System.out.println("Exception");

return;

}

}

Only 2 statements are required to execute the query-

Statement s = con.createStatement();

ResultSet res=s.executeQuery(sql);

• The first statement creates a statement object. The second executes the statement with the supplied parameter sql. The parameter sql, should of course contain a string with a valid sql statement starting with ‘select’.

• It returns a ResultSet object, which is an object containing the result of the sql query. The ResultSet will be null if no results are returned.

• You need to use the next() method of the ResultSet object to move to the next row of the results. The next() method will return false when there are no more rows in the result. It will throw SQLException if the ResultSet is null, so make sure ResultSet is not null before calling next().

• To retrieve a field from the ResultSet, use one of the get methods, i.e. getString(), getInt(), etc to retrieve a field. You need to specify the type of the field to retrieve it. In the example above, the statement res.getInt("PublisherID") retrieves the field PublisherID from the current row in the ResultSet as an integer.

Simple Update Method (ex3UpdateQuery.java for full program)

public static int updateQuery(String sql,Connection con)

{

try

{

Statement s=con.createStatement();

return s.executeUpdate(sql);

}

catch(SQLException e)

{

return 0;

}

}

• As you can see, updates are very simple and require less developer processing as we generally only want to know how many rows were affected.

• Here use the executeUpdate() method which returns an integer containing the number of rows affected (inserts, deletes, etc.

Creating / deleting tables (ex4CreateDeleteTables.java for program)

A new table can be created in the database with your Java code, using the executeUpdate() method. e.g

Statement s = connect.createStatement();

s.executeUpdate(”CREATE TABLE temp” +

”(ID int, name varchar(25), marks float)”);

// now insert two records into the table

int insCount = s.executeUpdate(”INSERT INTO Temp”

+ ”VALUES (1234, ’Andrew Clark’, 98.5)”);

System.out.println(”Inserted ” + insCount + ”record”);

int insCount = s.executeUpdate(”INSERT INTO Temp”

+ ”VALUES (1258, ’Tom Brooks’, 51.2)”);

System.out.println(”Inserted ” + insCount + ”record”);

…

To delete a table (say table Temp):

s.executeUpdate(”DROP TABLE Temp”);

s.close();

The ResultSet interface

Recall that a ResultSet object is returned when a query is executed via a handle of type Statement:

Connection connection;

String query = ”SELECT * FROM Authors”;

…

Statement statement =

connection.createStatement();

ResultSet rs = statement.executeQuery(query);

Information about the number of columns, column headings and values of the records can be obtained by creating a ResultSetMetaData (also an interface) object:

ResultSetMetaData rsmd = rs.getMetaData();

In older JDBC there was no mechanism defined for a result set to scroll backwards – to avoid the issue of repeatability of data read by a result set.

In JDBC 2, three types of result sets are defined:

• forward-only – (the constant ResultSet.TYPE_FORWARD_ONLY) this is the same as JDBC

• scroll-insensitive – (the constant ResultSet.TYPE_SCROLL_INSENSITIVE) Result sets of this type are scrollable but are not sensitive to changes made by others.

• scroll-sensitive – (the constant ResultSet.TYPE_SCROLL_SENSITIVE) similar to scroll-insensitive but are sensitive to changes made by others.

Scroll Backwards example (ex5Scrolling.java for full program)

public static void ScrollBack(ResultSet R)

{

if(R!=null)

{

try

{

R.afterLast();

while(R.previous())

{

System.out.print(R.getInt("publisherID")+" ");

System.out.println(R.getString("PublisherName"));

}

}catch(SQLException e)

{

System.out.println("scroll error");

}

}

}

The ResultSetMetaData interface

Retrieving the value of a column from a ResultSet object requires us to know the type of that column and either the column heading (e.g. rs.getString(”FirstName”)) or the column index (e.g. rs.getString(2)). Thus, we must combine the use of methods in the ResultSetMetaData interface, e.g.

String getColumnName(int column)

int getColumnType(int column)

String getTableName(int column)

int getColumnCount()

boolean isReadOnly(int column)

Some example methods

The first method returns an array of strings containing the column names

public static String[] getColumnNames(ResultSetMetaData rsmd)

{

if(rsmd==null)

return null;

String colNames[] = new String[getColumnCount(rsmd)];

try

{

for(int counter=0;counter ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download