Transcript Connecting to a database with JDBC

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



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 elements 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-to-database communication path
Java Application
JDBC Driver Manager
JDBC/ODBC
bridge
Vendor
supplied
JDBC
drivers
ODBC
Will discuss this path
using testdb.mdb
Database
Textbook discusses this
path using testdb.mdb
COMP201 Topic 15 / Slide 16
Connecting to Databases with JDBC

JDBC -- Java Database Connectivity kit :
Programs written according to the JDBC API would talk to the JDBC
driver manager, which in turn, would use the drivers that were
plugged into it at that moment to talk to the actual database. JDBC
consists of two layers.
1. JDBC API, it communicates with the JDBC manager driver API, sending
it the various SQL statements.
2. The manager should communicate with the various third-party drivers
that actually connect to the database and return the information from
the query or perform the action specified by the query.

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 17
Connecting to Databases with JDBC

To establish connection, DBMS must provide JDBC drivers

PointBase Mobile Edition: DBMS in java by PointBase Inc.
– JDBC driver: com.pointbase.jdbc.jdbcDriver

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

See java.sun.com/products/jdbc/driverdesc.html for
DBMS with JDBC drivers
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]
(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

Java Application
Method 1:
JDBC Driver Manager
– set system property( ~/.hotjava/properties )
System.setProperty("jdbc.drivers",
JDBC/ODBC
Vendor
"sun.jdbc.odbc.JdbcOdbcDriver");
bridge
– At initialization, DriverManager loads all driver
classes referenced in jdbc.drivers

Method 2: Load driver explicitly at any time
supplied
JDBC
drivers
ODBC
Database
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
A call to forName("X") causes the class named X to be initialized.
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



ConnectMe1.java: register JDBC driver using the first method
ConnectMe.java: register JDBC driver using the second method
Note. Examples (including testdb.mdb) in this topic are
packaged in the zip. See the code page
COMP201 Topic 15 / Slide 25
Objective & Outline

Outline:



Database basics
Connecting to a database with JDBC
Querying and manipulating databases with JDBC
COMP201 Topic 15 / Slide 26
Querying and Manipulating Databases with JDBC

getConnection returns an object of java.sql.Connection
Connection con =
DriverManager.getConnection("jdbc:odbc:TestDB","", "");

To execute SQL queries and commands, we first need to get a
java.sql.Statement (interface in java.sql) object
Statement stmt = con.createStatement();
The object used for executing a static SQL statement and returning the
results it produces.

Next:

Queries
 Commands (updates)
COMP201 Topic 15 / Slide 27
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.
Analysis of result:
Database column number starts at 1
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 28
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 29
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 30
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 = 'McGraw-Hill'";

Call the executeUpdate method of the Statement interface
stmt.executeUpdate( command );
Update.java