Transcript Database Drivers

Java the UML Way
http://www.tisip.no/JavaTheUmlWay/
Programming with Databases
What is JDBC?
Database drivers
The example Database
Establishing database contact
The relationship between SQL data types,
Java data types and getxxx() methods
A bigger example
A database application
The three-layer architecture
Transactions
Compiled SQL statements
versjon 2002-04-17
page 2
page 3
page 4
page 5
page 6
page 7
page 8
page 9
page 10
page 11
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20
What is Java Database Connectivity (JDBC)?
• JDBC is
– not a database system, but makes communication with a database system
possible
– not a ”point and click, drag and drop” tool, such as Microsoft Access, but
it may be used by the developers of such tools to communicate with the
database.
• JDBC is an API offering classes that makes it possible to send SQL
statements to a database, and to interpret the results from the
statements.
• This chapter assumes that you are familiar with relational databases
and SQL.
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 2
Database Drivers
Java
application
Java
application
Java
application
JDBC Driver Manager
JDBC-ODBC
Bridge
Oracle Driver
Sybase Driver
ODBC Driver
Microsoft
Access
Microsoft
SQL Server
Oracle
Database
Sybase
Database
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
A database driver
consists of classes
that implement
certain interfaces.
Chapter 20, page 3
The Example Database
create table person(
identno integer primary key,
firstname varchar(30) not null,
lastname varchar(30) not null);
insert into person values (100, 'EDWARD', 'BROWN');
insert into person values (101, 'ANN MARGARET', 'GREEN');
insert into person values (102, 'JOHN', 'JOHNSON');
identno
firstname
lastname
100
EDWARD
BROWN
101
ANN MARGARET GREEN
102
JOHN
JOHNSON
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 4
A Little Program Communicating With the Database
import javax.swing.*;
import java.sql.*;
class DatabaseContact {
public static void main(String[] args) throws Exception {
String databaseDriver = "oracle.jdbc.driver.OracleDriver";
Class.forName(databaseDriver);
String userName = JOptionPane.showInputDialog("User Name: ");
String password = JOptionPane.showInputDialog("Password: ");
String databaseName = "jdbc:oracle:thin:@loiosh.stud.idb.hist.no:1521:orcl";
Connection conn
= DriverManager.getConnection(databaseName, userName, password);
Statement statement = conn.createStatement();
ResultSet res = statement.executeQuery("select * from person");
while (res.next()) {
int idNo = res.getInt("identNo");
String firstName = res.getString("firstname");
Our database driver is the
String lastName = res.getString("lastname");
System.out.println(idNo + ": " + firstName + " " + lastName);
classes111.zip file. CLASSPATH
}
has to contain classes111.zip.
res.close();
This file contains,
statement.close();
conn.close();
among other things
System.exit(0);
oracle.jdbc.driver.OracleDriver.
}
}
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 5
The Relationship Between SQL Data Type,
Java Data Types, and getxxx() Methods
CHAR,
VARCHAR,
LONGVARCHAR
NUMERIC,
DECIMAL
BIT
TINYINT
SMALLINT
INTEGER
BIGINT
REAL
FLOAT,
DOUBLE
BINARY,
VARBINARY,
LONGVARBINARY
DATE
TIME
TIMESTAMP
String
getString()
java.math.BigDecimal
boolean
byte
short
int
long
float
getBigDecimal()
getBoolean()
getByte()
getShort()
getInt()
getLong()
getFloat()
double
getDouble()
byte[]
java.sql.Date
java.sql.Time
java.sql.Timestamp
getBytes()
getDate()
getTime()
getTimestamp()
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 6
A Bigger Example
Database
getAll()
updateName(thePerson: Person)
registerNewPerson(firstName: String, lastName: String)
deletePerson(identNo: int)
closeTheConnection()
Show program listing 20.2, pp. 629-634.
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 7
A Database Application
Show program listing 20.3, pp. 635-639.
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 8
The Three-Layer Architecture
loiosh.stud.idb.hist.no
1. The physical database
:database
:Internet
:clientPC
2. The component which communicates
with the database
theDatabaseContact
:DatabaseApplication
(the user interface)
3. The user interface component which
communicates with the component above.
Solve the problems, page 641.
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 9
Transactions
• A transaction is a logical unit of work and can consist of multiple
update statements to the database.
• For example, if money is going to be transferred from one account to
another, it’s important that the balances in both of the accounts change.
If an error occurs, we can’t risk that only one of the accounts changes
its balance. This transaction consists of two update statements.
• The transaction is worked out in a correct way: Commit!
• The transaction is not worked out in a correct way: Rollback!
• As default, every single SQL statement is a transaction unit.
• The Connection interface let us create transactions consisting of more
than one SQL statement.
– public void setAutoCommit(boolean autocommit) // default is true
– public void commit()
– public void rollback()
Solve problem 1, page 645.
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 10
Compiled SQL Statements
• The Java interpreter sends the SQL statement to the database system.
• The database system sets up a plan so that the search can be done in
the most efficient way possible.
• Unnecessary to compile again if only literals are changed:
– select * from person where firstName like ’ANNE’ and lastName like ’GREEN’;
– select * from person where firstName like ’JOHN’ and lastName like ’JOHNSON’;
• The PreparedStatement interface let us create precompiled statement
objects. An example:
String sqlStatement =
"select * from person where firstName like ? and lastName like ?";
PreparedStatement statement = conn.prepareStatement(sqlStatement);
setning.setString(1, ”ANNE”);
// The search criteria may be input,
setning.setString(2, ”GREEN”);
// see program listing 20.4 pp. 644-645.
ResultSet res = statement.executeQuery();
while (res.next()) {
…osv….
Only to be used in connection with the book "Java the UML Way", by Else Lervik and Vegard B. Havdal.
ISBN 0-470-84386-1, John Wiley & Sons Ltd 2002
The Research Foundation TISIP, http://tisip.no/engelsk/
Chapter 20, page 11