Transcript JDBC Java Database Connectivity

Persistenta datelor
• Multe aplicatii doresc sa asigure persistenta
datelor
• Metode/Tehnologii:
– Fisiere
– Serializare
– Baze de date
• Probleme:
– Decuplarea componentelor
care fac accesul la
date(specifice unei anumite
tehnologii) de celelalte parti
ale aplicatiei
– Abstractizarea accesului la
date
– Transformarea datelor intre
OO domain model si un model
de persistenta non-OO (BD
relationale, fisiere diferite
structuri)
Baze de date
(review BD)
• Baza de date: reprezinta o modalitate de stocare a
datelor, astfel incat acestea sa poata fi regasite
• Baza de date relationala (Relational Database):
–
–
–
–
Informatiile sunt organizate in una sau mai multe tabele (Table)
O tabela are un nume si una sau mai multe coloane (Columns)
Fiecare coloana are un nume si un tip de date
Fiecare data elementara este stocata pe o linie (Row) din
taabela
– O tabela = o relatie = o colectie de obiecte de acelasi tip (liniile)
Exemplu
Database: “Company”
Table: “Employees”
Table: “Cars”
Exemplu
Table Employees
Employee_Number
First_name
Last_Name
Date_of_Birth
Car_Number
10001
Axel
Washington
28-Aug-43
5
10083
Arvid
Sharma
24-Nov-54
null
10120
Jonas
Ginsberg
01-Jan-69
null
10005
Florence
Wojokowski
04-Jul-71
12
10099
Sean
Washington
21-Sep-66
null
10035
Elizabeth
Yamaguchi
24-Dec-59
null
Exemplu
Table Cars
Car Number
Make
Model
Year
5
Honda
Civic DX
1996
12
Toyota
Corolla
1999
Reguli de integritate
• Intr-o tabela nu pot exista 2 linii identice (DBMS verifica aceasta)
• Valorile unei coloane nu pot fi tablouri/colectii de valori
• Valori nule: valoare speciala prin care se marcheaza o valoare lipsa
(nu e zero sau blanc !)
• Cheie primara (primary key): una sau mai multe coloane care
identifica (diferentiaza) liniile. Coloanele care fac parte din cheia
primara nu pot avea valori nule
• Cheie straina (foreign key):
– Exemplu: Car_Number: primary key in table Cars, foreign key in table
Employees
DataBase Management Systems
• Sisteme de gestiune a
bazelor de date (Data
Base Management
System – DBMS)
DBMS
DB Query Language
DBMS Engine
Database files
JDBC:
Java <-> Relational Databases
DBMS
DB Query Language
Java Application
DBMS Engine
JDBC API
JDBC Driver
Database files
SQL
JDBC
(Java Database Connectivity) (?)
JDBC
Bibliografie:
Sun tutorial on JDBC:
http://download.oracle.com/javase/tutorial/jdbc/index.html
Laborator: Java DB = Sun’s supported distribution of the open source Apache Derby
database
http://developers.sun.com/javadb/
Exemple cod (folosind JDBC cu Derby Embedded):
http://www.cs.utt.ro/~ioana/arhit/exemple_jdbc.html
Apache Derby tutorial: http://db.apache.org/derby/papers/DerbyTut/index.html
Ce este JDBC
• JDBC: o tehnologie care permite programelor Java programs
sa interactioneze cu baze de date relationale (folosind
limbajul de interogare standard SQL).
• Folosind JDBC, un program poate:
– Stabili conexiuni cu diferite sisteme de baze de date
– Executa comenzi (SQL) pentru a crea, actualiza, manipula
datele si a receptiona rezultate
– Inspecta si manuipula meta-datele bazei de date
• JDBC este doar un API care permite programului sa interactioneze
cu un sistem de gestiune a bazelor de date – acesta (un server de
baze de date) trebuie sa existe separat !
– Java DB (fost Apache Derby)
– MySQL
SQL: Prezentare generala
• SQL (Structured Query Language): un limbaj
standard de interogare a bazelor de date
• Categorii de comenzi SQL:
– Data manipulation – realizeaza adaugarea,
regasirea, modificarea sau stergerea datelor; aceste
comenzi utilizeaza baza de date.
• SELECT, UPDATE, DELETE, INSERT
– Data definition – realizeaza crearea, configurarea
sau stergerea tabelelor, bazelor de date, views, sau
indexes; aceste comenzi administreaza baza de date.
• CREATE, DROP
SQL: Comanda SELECT
• Comanda SELECT este folosita pentru interogari
(queries); selecteaza valorile de pe coloanele
specificate pentru toate liniile care corespund criteriului
specificat de clauza WHERE
• Exemple:
SELECT First_Name, Last_Name FROM Employees WHERE
Car_Number IS NOT NULL
SELECT * FROM Employees
SQL: Clauza WHERE
• Clauza WHERE poate pune conditii privitoare la valorile
anumitor coloane
• Clauza WHERE poate insoti comenzile SELECT,
UPDATE, DELETE
• Exemple:
SELECT First_Name, Last_Name FROM Employees WHERE
Last_Name LIKE 'Washington%'
SELECT First_Name, Last_Name FROM Employees WHERE
Last_Name LIKE ‘Ba_man‘
SELECT First_Name, Last_Name FROM Employees WHERE
Employee_Number < 10100 and Car_Number IS NULL
SQL: Join
• Se refera la posibilitatea de a regasi date din mai mult de
o tabela la o interogare
• Exemplu:
• Gaseste angajatii care au masini de servici si afiseaza
numele angajatului (din tabela employees), numarul
masinii, marca, modelul si anul fabricatiei (din tabela
cars)
• SELECT Employees.First_Name, Employees.Last_Name,
Cars.Make, Cars.Model, Cars.Year FROM Employees,
Cars WHERE Employees.Car_Number = Cars.Car_Number
SQL: Comanda INSERT
• Comanda INSERT se utilizeaza pentru a adauga noi
linii de date intr-un tabel
• Exemple
insert into SUPPLIERS values(49, 'Superior Coffee',
'1 Party Place', 'Mendocino', 'CA', '95460');
insert into COFFEES values('Colombian', 00101, 7.99,
0, 0);
SQL: Comanda UPDATE
• Comanda UPDATE se foloseste pentru a
modifica valori din tabele
• Exemple:
UPDATE suppliers
SET zip = ‘99999’
WHERE zip = ‘95460’
UPDATE coffees SET price = 9.99
WHERE price<=5.0
SQL:Comanda DELETE
• Comanda DELETE se foloseste pentru a sterge linii din
tabele
DELETE FROM coffees
WHERE price > 100
SQL: Comanda CREATE
create table SUPPLIERS (
SUP_ID integer NOT NULL, SUP_NAME varchar(40)
NOT NULL, STREET varchar(40) NOT NULL, CITY
varchar(20) NOT NULL, STATE char(2) NOT NULL,
ZIP char(5), PRIMARY KEY (SUP_ID));
create table COFFEES (
COF_NAME varchar(32) NOT NULL, SUP_ID int NOT
NULL, PRICE numeric(10,2) NOT NULL, SALES
integer NOT NULL, TOTAL integer NOT NULL,
PRIMARY KEY (COF_NAME), FOREIGN KEY (SUP_ID)
REFERENCES SUPPLIERS (SUP_ID));
Arhitectura aplicatiilor JDBC
• API-ul JDBC suporta atat arhitecturi 2-tiers si 3-tiers
From: http://download.oracle.com/javase/tutorial/jdbc/overview/index.html
Arhitectura de baza JDBC
• JDBC Driver: asigura comunicatia cu un anumit server
de baze de date, cu particularitatile lui
• JDBC Driver poate fi realizat dupa mai multe tipuri
Java Application
Application
Java Core
APIs
JDBC API
JDBC Driver
Database
Server
JVM
Database
Tipuri de drivere JDBC (1)
Type 1:
ODBC
Bridge
Java
App
JDBC
JDBC
Bridge
API
Driver
MS
ODBC
Database
Server
Database
Type 1 JDBC drivers: ODBC Bridge: Drivers that implement the
JDBC API as a mapping to another data access API, such as ODBC
(Open Database Connectivity). Drivers of this type are generally
dependent on a native library, which limits their portability. The JDBCODBC Bridge is an example of a Type 1 driver.
Tipuri de drivere JDBC (2)
Type 2:
Native
DB API
Java
App
JDBC JDBC
API Driver
Native API Native
Library
API
Database
Type 2 JDBC drivers: Native DB API: Drivers that are written partly in the
Java programming language and partly in native code. These drivers
use a native client library specific to the data source to which they
connect. Again, because of the native code, their portability is limited.
Oracle's OCI (Oracle Call Interface) client-side driver is an example of a
Type 2 driver.
Tipuri de drivere JDBC (3)
Type 3:
Pure
Java
Java
App
JDBC
JDBC
Driver
API
Database
Translator
Database
Server
Database
Type 3 JDBC drivers: Pure Java: Drivers that use a pure Java client and
communicate with a middleware server using a database-independent
protocol. The middleware server then communicates the client's
requests to the data source.
Tipuri de drivere JDBC (4)
Type 4:
Pure
Java
Direct
Java
App
JDBC
JDBC
Driver
API
Database
Server
Database
Type 4 JDBC drivers: Pure Java Direct: Drivers that are pure Java and
implement the network protocol for a specific data source. The client
connects directly to the data source.
Java DB (Derby) comes with two Type 4 drivers, an Embedded driver and a
Network Client Driver. MySQL Connector/J is a Type 4 driver.
Embedded Derby - Network Derby
The Derby engine does not run in a
separate process, and there are no
separate database processes to start up
and shut down.
The Derby database engine runs inside
the same Java Virtual Machine (JVM) as
the application.
An application can also access a Derby database using
the client/server mode. This is achieved via a framework
(Derby Network Server) that embeds Derby and handles
database requests from applications, including
applications running in different JVMs on the same
machine or on remote machines.
Structura generala a unui
program utilizand JDBC
• Secventa tipica de operatii pentru interactiunea cu o baza
de date via JDBC:
– Incarca driver-ul JDBC potrivit sistemului de baze de date utilizat
(prin classname)
– Deschide o conexiune (Connection) catre baza de date (folosind
URL)
– Creaza instructiuni (Statement) (folosind Connection-ul care a
fost deschis)
– Executa instructiunile
– Eventual proceseaza rezultatele, daca exista (printr-un ResultSet
returnat)
– Inchide Connection
http://download.oracle.com/javase/tutorial/jdbc/basics/index.html
Establishing a Connection
•
First, you need to establish a connection with the data source you want to
use.
– A data source can be: a DBMS, a legacy file system, or some other source of
data with a corresponding JDBC driver.
• DriverManager:
– This fully implemented class connects an application to a data source, which is
specified by a database URL.
– When this class first attempts to establish a connection, it automatically loads
any JDBC 4.0 drivers found within the class path. Note that your application must
manually load any JDBC drivers prior to version 4.0.
– Connecting to your DBMS with the DriverManager class involves calling the
method DriverManager.getConnection.
– The method DriverManager.getConnection establishes a database connection.
This method requires a database URL, which varies depending on your DBMS
Specifying Connection URL’s
• A database connection URL is a string that your DBMS JDBC driver
uses to connect to a database.
• It can contain information such as:
– where to search for the database
– the name of the database to connect to
– configuration properties.
• The exact syntax of a database connection URL is specified by your
DBMS !
• See examples for Java DB and MySQL
Java DB Database Connection
URLs
Java DB Database Connection URLs format:
• jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*
• subsubprotocol specifies where Java DB should search for the
database, either in a directory, in memory, in a class path, or in a
JAR file. It is typically omitted.
• databaseName is the name of the database to connect to.
• attribute=value represents an optional, semicolon-separated list of
attributes. These attributes enable you to instruct Java DB to
perform various tasks, including the following:
–
–
–
–
Create the database specified in the connection URL.
Encrypt the database specified in the connection URL.
Specify directories to store logging and trace information.
Specify a user name and password to connect to the database.
• Examples:
– jdbc:derby:testdb;create=true
MySQL Database Connection
URLs
MySQL Connector/J Database URL syntax:
• jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1]
[=propertyValue1][&propertyName2][=propertyValue2]...
• host:port is the host name and port number of the computer hosting
your database. If not specified, the default values of host and port
are 127.0.0.1 and 3306, respectively.
• database is the name of the database to connect to. If not specified,
a connection is made with no default database.
• failover is the name of a standby database (MySQL Connector/J
supports failover).
• propertyName=propertyValue represents an optional, ampersandseparated list of properties. These attributes enable you to instruct
MySQL Connector/J to perform various tasks.
• Example:
– jdbc:mysql://localhost:3306/
Establishing Connection Example
Connection conn = null;
Properties connectionProps = new Properties();
connectionProps.put("user", this.userName);
connectionProps.put("password", this.password);
if (this.dbms.equals("mysql")) {
conn = DriverManager. getConnection("jdbc:" + this.dbms +
"://" + this.serverName + ":" + this.portNumber + "/",
connectionProps);
}
else if (this.dbms.equals("derby")) {
conn = DriverManager. getConnection("jdbc:" + this.dbms + ":" +
this.dbName + ";create=true", connectionProps);
}
System.out.println("Connected to database");
From JDBCTutorialUtilities :
http://download.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html
The JDBC Connection Class
• There are many methods a program can call on
its valid Connection object.
– The createStatement() method will create a Statement
object that can be used to assemble and run SQL
commands. The preparedStatement() creates an object
that is associated with a predefined SQL command (the
Statement object can be used for arbitrary statements –
and can be reused for other SQL commands)
– The getMetaData() method will return metadata associated
with the database, including descriptions of all of the tables
in the DB.
– The prepareCall() method is used to call stored
procedures in the SQL database.
Creating Statements
•
•
•
A Statement is an interface that represents a SQL statement.
You execute Statement objects, and they generate ResultSet objects, which is a table
of data representing a database result set.
You need a Connection object to create a Statement object.
•
There are three different kinds of statements:
–
–
–
•
Statement: Used to implement simple SQL statements with no parameters.
PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might
contain input parameters.
CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that
may contain both input and output parameters.
Example:
Connection con;
Statement stmt;
…
stmt=con.getStatement();
Executing Statements/Queries
• To execute a query, call an execute method from Statement such as
the following:
– executeQuery: Returns one ResultSet object.
– executeUpdate: Returns an integer representing the number of rows
affected by the SQL statement. Use this method if you are using
INSERT, DELETE, or UPDATE SQL statements.
• ResultSet: A ResultSet is like a database table; it has zero or more
rows (zero if no data elements match the query criteria) and each
row has one or more columns
• Example:
String query = "select COF_NAME, SUP_ID, PRICE, SALES,
TOTAL from COFFEES"; ”;
ResultSet rs = stmt.executeQuery(query);
Processing ResultSets
• You access the data in a ResultSet object through a cursor. This
cursor is a pointer that points to one row of data in the ResultSet
object.
• Initially, the cursor is positioned before the first row. You call various
methods defined in the ResultSet object to move the cursor.
• For example, the method ResultSet.next moves the cursor forward
by one row
• There are multiple methods of extracting data from the current
row in a ResultSet.
– The getString() method returns the value of a particular column in the
current row as a String.
– The getInt() method returns the value of a particular column in the current
row as an int.
–The getBoolean() method returns the value of a particular column in the
current row as an boolean.
–The getDouble() method returns the value of a particular column in the
current row as an double.
–The getObject() method returns the value of a particular column in the
current row as an Object.
ResultSet Example
String query = "select COF_NAME, SUP_ID, PRICE, SALES,
TOTAL from COFFEES"; ”;
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String coffeeName = rs.getString("COF_NAME");
int supplierID = rs.getInt("SUP_ID");
float price = rs.getFloat("PRICE");
int sales = rs.getInt("SALES");
int total = rs.getInt("TOTAL");
System.out.println(coffeeName + "\t" + supplierID +
"\t" + price + "\t" + sales + "\t" +
total);
Populating Tables Example
stmt = con.createStatement();
stmt.executeUpdate("insert into COFFEES” +
"values('Colombian', 00101, 7.99, 0, 0)");
stmt.executeUpdate("insert into COFFEES“ +
“values('French_Roast', 00049, 8.99, 0, 0)");
Creating Tables Example
String createString = "create table " + dbName + ".COFFEES " +
"(COF_NAME varchar(32) NOT NULL, " +
"SUP_ID int NOT NULL, " +
"PRICE float NOT NULL, " +
"SALES integer NOT NULL, " +
"TOTAL integer NOT NULL, " +
"PRIMARY KEY (COF_NAME), " +
"FOREIGN KEY (SUP_ID) REFERENCES " + dbName +
".SUPPLIERS (SUP_ID))";
Statement stmt = con.createStatement();
stmt.executeUpdate(createString);
JDBC Statements - Types
• There are three types of statement objects in
JDBC programming:
– Statement - This allows the execution of arbitrary
statements on the database.
– PreparedStatement - This kind of statement allows
the program to execute the same SQL command
repeatedly, while allowing substitutions for particular
words or values (input parameters).
– CallableStatement - This kind of statement allows the
program to execute SQL stored procedures, with
substitutions for arguments. A stored procedure is a
function that is part of, and stored inside, a SQL
database
Prepared Statement Example
PreparedStatement updateSales = null;
String updateString = "update " + dbName + ".COFFEES " +
"set SALES = ? where COF_NAME = ?";
con.setAutoCommit(false);
updateSales = con.prepareStatement(updateString);
for (Map.Entry<String, Integer> e : salesForWeek.entrySet())
{ updateSales.setInt(1, e.getValue().intValue());
updateSales.setString(2, e.getKey());
updateSales.executeUpdate();
con.commit(); }
JDBC Data Types
• As SQL defines its own datatypes, it is important to
understand the mapping between SQL and Java
datatypes…
SQL
Java
---------------------------------BIT
boolean
BIGINT
long
BINARY byte[]
CHAR
String
DATE
Date
DOUBLE double
FLOAT
float
INTEGER int
SQL
Java
--------------------------------------------NUMERIC BigDecimal
REAL
float
SMALLINT short
TIME
Time
TIMESTAMP Timestamp
TINYINT
byte
VARBINARY byte[]
VARCHAR char[]
BLOB
CLOB
REF
STRUCT
Blob
Clob
Ref
Struct
Summary
• JDBC is a core Java technology that supports access to
relational databases and database servers.
• JDBC uses the standard Structured Query Language
(SQL) for all database interaction.
• In order to use JDBC with a particular database, you
must install a driver for the database server you are using
• Almost all JDBC functionality is accomplished through
the use of SQL statements