Transcript Database Connectivity

COMP201 Java Programming
Topic 15: Database Connectivity
JDBC
Reading: Chapter 4, Volume 2
COMP201 Topic 15 / Slide 2
Objective & Outline

Objective:


Introduction: How to connect to and communicate with databases
Outline:



Database basics
Connecting to a database with JDBC
Querying and manipulating databases with JDBC
COMP201 Topic 15 / Slide 3
Database Basics

A (relational) database:




A collection of tables, each containing information about certain
aspects of some objects.
Attributes: names of columns
Records or entries: rows
Example:
testdb.mdb

4 tables
COMP201 Topic 15 / Slide 6
Database Basics

Database management systems (DBMS): allow user to
manipulate data

Many DBMS available. We will use Microsoft Access,
which comes with Microsoft Office.
COMP201 Topic 15 / Slide 7
Database Basics

Task:


Find all books by “Fox, David”, list author name, title, publisher name and price
The query involves all four tables and
COMP201 Topic 15 / Slide 8
Database Basics

The query: Select2 in testmd.db
COMP201 Topic 15 / Slide 9
Database Basics

Task: Find all books published by McGraw-Hill

Select3 in testdb
COMP201 Topic 15 / Slide 10
Database Basics

Update:





Modify records
Insert new records
Delete records
Create new Table
….
COMP201 Topic 15 / Slide 11
Database Basics

So far, interact with database manually


The structured query language (SQL):


Usually interact with databases via application programs
Standard language for accessing databases:.
Query database using SELECT statements

Select all records in the Books table:
SELECT * FROM Books

Select only some columns
SELECT ISBN, Price, Title
FROM Books
COMP201 Topic 15 / Slide 12
Database Basics

Select from multiple tables

Find all books by “Fox, David”, list author name, title, publisher name and
price
SELECT Authors.Name, Books.Title, Publishers.Name,
Books.Price
FROM Books, Authors, Publishers, BooksAuthors
WHERE Publishers.Publisher_Id = Books.Publisher_Id
AND Books.ISBN = BooksAuthors.ISBN
AND BooksAuthors.Author_Id = Authors.Author_Id
AND Authors.Name= 'Fox, David'

When we select from multiple tables, a joint table is formed. The joint table
consists of ALL possible combinations of rows from all the tables.
 The first three conditions in the WHERE clause constrain the query to legal
combinations.
COMP201 Topic 15 / Slide 13
Database Basics

Update:

Slash prices for McGraw-Hill books by 0.5
UPDATE Books
SET Price = Price -0.5
WHERE Books.Publisher_Id = ‘00791’

A more intuitive way
UPDATE Books
SET Price = Price -0.5
WHERE Books.Publisher_Id =
( SELECT Publisher_Id
FROM Publishers
WHERE Name = 'McGraw-Hill‘)
COMP201 Topic 15 / Slide 14
Objective & Outline

Outline:



Database basics
Connecting to a database with JDBC
Querying and manipulating databases with JDBC
COMP201 Topic 15 / Slide 15
Connecting to Databases with JDBC

JDBC: Java Database Connectivity kit

Package: java.sql

DriverManager: class for making connection to databases

Connection: interface for communicating with databases once
connection has been established.

Other interfaces: Statement, ResultSet,
DatabaseMetaData, ResultSetMetaData, SQLException,
….
COMP201 Topic 15 / Slide 16
Connecting to Databases with JDBC

To establish connection, DBMS must provide JDBC drivers

Many DBMS venders (222) provide JDBC drivers
– PointBase Mobile Edition: DBMS in java by PointBase Inc.
 JDBC driver: com.pointbase.jdbc.jdbcDriver
– See http://servlet.java.sun.com/products/jdbc/drivers for DBMS with
JDBC drivers

Microsoft Access & many other DBMS
– Has a C interface called ODBC (Open DataBase Connectivity)
– The JDBC/ODBC bridge sun.jdbc.odbc.JdbcOdbcDriver provided
by Sun
COMP201 Topic 15 / Slide 17
Connecting to Databases with JDBC

JDBC-to-database communication path
Java Application
JDBC Driver Manager
JDBC/ODBC
bridge
ODBC drivers
Will discuss this path
using testdb.mdb
Database
Vendor
supplied
JDBC
drivers
Textbook discusses this
path using testdb.mdb
COMP201 Topic 15 / Slide 18
Connecting to Databases with JDBC
Next:

Register testdb.mdb with the
ODBC Data Source
Administrator on your PC.
Java Application


Register JDBC driver
(sun.jdbc.odbc.JdbcOdbc
Driver ) with
DriverManager
Making connection
JDBC Driver Manager
JDBC/ODBC
bridge
Vendor
supplied
JDBC
drivers
ODBC
Database
COMP201 Topic 15 / Slide 19
Connecting to Databases with JDBC

Register testdb.mdb with the ODBC Datasource Administer on
your PC. Instructions:

Open Control Panel and select ODBC Data Sources [32bit] (on
XP under Administrative Tools). You will get the window shown on the
right. Click on Add.
COMP201 Topic 15 / Slide 20
Connecting to Databases with JDBC



Select Microsoft Access Driver and then click on Finish.
You will get the window shown on the right.
Provide a name for the data source, say TestDB.
Click and Select and select testdb.mdb from file system (picture
on the next page)
COMP201 Topic 15 / Slide 21
Connecting to Databases with JDBC



Then you see the picture on the right. Simply click on OK and the
registration is complete.
Thereafter the database testdb.mdb is known as TestDB to the
ODBC Datasource Adminstrator.
For JDBC, URL for the database is: jdbc:odbc:TestDB
COMP201 Topic 15 / Slide 22
Connecting to Databases with JDBC

Register JDBC driver
(sun.jdbc.odbc.JdbcOdbcDriver ) with
DriverManager
 Method 1:
– set system property jdbc.drivers
System.setProperty("jdbc.drivers",
"sun.jdbc.odbc.JdbcOdbcDriver");
– At initialization, DriverManager loads all driver classes
referenced in jdbc.drivers

Method 2: Load driver explicitely at any time
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Java Application
JDBC Driver Manager
JDBC/ODBC
bridge
Vendor
supplied
JDBC
drivers
ODBC
A call to forName("X") causes the class named X to be
initialized.
Database
COMP201 Topic 15 / Slide 23
Connecting to Databases with JDBC

Making connection
URL for database
Connection con=
DriverManager.getConnection("jdbc:odbc:TestDB", "", "");
DB user name
DB password
java.sql.Connection: represents a connection to a DB.

Several drivers might have been registered with the DriverManager. The
DriverManager will find one that can use the protocol on the database URL
COMP201 Topic 15 / Slide 24
Connecting to Databases with JDBC

Connection examples



ConnectMe.java: register JDBC driver using the second method
ConnectMe1.java: register JDBC driver using the first method
Note. Examples (including testdb.mdb) in this topic are
packaged in the zip. See the code page
COMP201 Topic 15 / Slide 25
Connecting to Databases with JDBC

An easier way to connect to DB

Interface javax.sql.DataSource

DBMS Vendor provides An object DataSource object,
– which will typically be registered with a naming service based on the
JavaTM Naming and Directory (JNDI) API for easy retrieval

Programmer get connection using the following method of
DataSource interface
Connection getConnection(String username, String password)
COMP201 Topic 15 / Slide 26
Objective & Outline

Outline:



Database basics
Connecting to a database with JDBC
Querying and manipulating databases with JDBC
COMP201 Topic 15 / Slide 27
Querying and Manipulating Databases with JDBC

getConnection returns an object of java.sql.Connection
Connection con =
DriverManager.getConnection("jdbc:odbc:TestDB","", "");

Creates a Statement object for sending SQL statements to the database
Statement stmt = con.createStatement();

Next:

Queries
 Commands (updates)
COMP201 Topic 15 / Slide 28
Querying and Manipulating Databases with JDBC

Form query as a string
String query = "SELECT ISBN, Title, Price " +
"FROM Books";

Call the executeQuery method of the Statement interface
ResultSet rs = stmt.executeQuery( query );

Which returns an object of the java.sql.ResultSet interface.
– A table of data representing a database result set, which is usually generated by
executing a statement that queries the database.
Database column number starts at 1

Analysis of result:
while (rs.next())
{ for (int i = 1; i <= 3; i++)
System.out.print( rs.getString(i) + " | ");
System.out.println("");
}
Select1.java
COMP201 Topic 15 / Slide 29
Querying and Manipulating Databases with JDBC

More complex queries

Select2.java: Find all books by David Fox and print out author
name, title, publisher name, and price.
String query =
"SELECT Authors.Name, Books.Title, Publishers.Name,
Books.Price " +
"FROM Books, Authors, Publishers, BooksAuthors " +
"WHERE Publishers.Publisher_Id = Books.Publisher_Id " +
"AND Books.ISBN = BooksAuthors.ISBN " +
"AND BooksAuthors.Author_Id = Authors.Author_Id " +
"AND Authors.Name= 'Fox, David' ";
COMP201 Topic 15 / Slide 30
Querying and Manipulating Databases with JDBC

More complex queries

Select3.java: Find all books published by McGraw-Hill and print
out author name, title, publisher name, and price
String query =
"SELECT Authors.Name, Books.Title, Publishers.Name,
Books.Price " +
"FROM Books, Authors, Publishers, BooksAuthors " +
"WHERE Publishers.Publisher_Id = Books.Publisher_Id " +
"AND Books.ISBN = BooksAuthors.ISBN " +
"AND BooksAuthors.Author_Id = Authors.Author_Id " +
"AND Publishers.Name= 'McGraw-Hill' ";
COMP201 Topic 15 / Slide 31
Querying and Manipulating Databases with JDBC

Commands: modify records, insert new records, delete records, ….

Form command as a string
String command =
"UPDATE Books " +
"SET Price = Price + " + "-0.5" +
" WHERE Books.Publisher_Id =
(SELECT Publisher_Id FROM Publishers WHERE Name = 'McGrawHill')";

Call the executeUpdate method of the Statement interface
stmt.executeUpdate( command );
Update.java
COMP201 Topic 15 / Slide 32
Assignment 3
Client Browser
Applet
Swing (GUI)
User

Topics involved:






GUI: Topic 9, 10
Applets, Topic 11
Multithreading: Topic 12
Java Networking: Topic 13
Servlets: Topic 14
JDBC: Topic 15
Applet
Java Networking
Server
Servlet
JDBC
DB