CIS 270—Application Development II

Chapter 25—Accessing Databases with JDBC

2

25.1 Introduction A _________ is an organized collection of data. A database management system (DBMS) is software

that stores and organizes data in a special format. The most popular type of database is a relational

database where data are stored in related _______. Popular relational database management systems

(RDBMSs) include Access, Oracle, DB2, and MySQL. SQL (structured ______ language) is the standard

RDBMS language for manipulating data. Java communicates with RDBMSs using the ____ API

and drivers that implement it to a given database.

3

25.2 Relational Databases A relational database is a logical representation of

data that allows data access w/o consideration of the __________ structure of the data.

Data are stored in tables. The table represents a particular entity (Employee). Columns represent the _________ of an entity (ssn). A ________ represents a specific instance of an entity (employee John Doe).

A primary ___ for a table is a column (field), or group of columns, that uniquely identifies a row (record).

A ________ key is a column in one table that is a primary key in another related table.

4

25.3 The books Database Four tables:

authors authorID, firstName, lastName

publishers publisherID, publisherName

titles isbn, title, editionNumber, copyright, publisherID,

imageFile, price authorISBN

authorID, isbn

______-relationship diagram ERD

authorsauthorIDfirstNamelastName

authorISBNauthorIDisbn

titlesisbntitleeditionNumbercopyrightpublisherIDimageFileprice

publisherspublisherIDpublisherName

1 n

1 n

n 1

5

25.4.1-3 SELECT SELECT * FROM titles (select all _______ from

titles) SELECT isbn, title, price FROM titles (only

certain columns) SELECT * FROM titles WHERE copyright > 2000

(only certain ______) SELECT * FROM authors WHERE lastname LIKE ‘D%’

(last name starts with D) SELECT * FROM authors ORDER BY lastName, firstName (sort ___________ by default)

SELECT isbn, title, price FROM titles WHERE price < 20 ORDER BY price DESC

6

25.4.4 INNER JOINSELECT firstName, lastName, isbn

FROM authors

INNER JOIN authorISBN

ON authors.authorID = authorISBN.authorID

ORDER BY lastName, firstName This SQL statement lists the authors with their

ISBNs SELECT determines the __________ FROM and INNER JOIN determines the two _______ ON specifies the common columns ORDER BY specifies sorting

7

25.4.5-7 INSERT, UPDATE, DELETE

Insert a row in a tableINSERT INTO authors (firstName, lastName)

VALUES ( ‘Sue’, ‘Smith’ ) Update a row in a tableUPDATE authors

SET lastName = ‘Jones’

WHERE lastName = ‘Smith’ AND firstName = ‘Sue’ Delete a row in a tableDELETE FROM authors

WHERE authorID = 5

8

Using JDBC The four basic steps involved with using JDBC

to connect to a database are: Load the appropriate JDBC driver Request a connection to the database Send SQL to the database Process results, if appropriate

See http://www.onjava.com/pub/a/onjava/synd/2001/08/21/jdbc.html for more information.

9

DisplayAuthors.java I Import package sql

import java.sql.*; Identify driver class JdbcOdbcDriver

static final String JDBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";

Identify database file (books.mdb) static final String DATABASE_URL = “jdbc:odbc:books”;

“jdbc:odbc:books” contains the communication ___________ (jdbc), subprotocol (odbc), and database name (books)

10

DisplayAuthors.java II Create a Connection object to connect the

Java program to the ___________ private Connection connection = DriverManager.getConnection( DATABASE_URL );

Set the location of the database with the property name and value System.setProperty( “odbc.system.home”, “C:/” );

11

DisplayAuthors.java III Get the specified Class object

(JdbcOdbcDriver) Class.forName( JDBC_DRIVER );

Create a Statement object for submitting _______ statements to the database private Statement statement = connection.createStatement();

Execute an SQL statement and store results in a ResultSet object. ResultSet resultSet = statement.executeQuery( “SELECT * FROM authors” );

12

Setting Up ODBC Data Sources

Open Microsoft Windows Control Panel. Double-click the Administrative Tools icon. Double-click on the Data Sources (ODBC)

icon. Select the User DSN tab. To define a new data source for a currently

installed driver, click Add. Complete the dialog boxes.

Select Microsoft Access Driver, Finish Enter a Data Source Name (such as “books”) Click Select… and select the database, click OK