JDBC - TI Aalst

JDBC
Java DataBase Connectivity
1
Inhoud
 Introductie
 Connectie
 DriverManager
 Statement
 ResultSet
 PreparedStatement
 CallableStatement
 Mapping SQL on Java types
 JDBC-ODBC bridge
 Transacties
2
Introductie
 JDBC: platform onafhankelijke toegang
tot relationele databanken via SQL
 De JDBC API maakt een connectie
mogelijk naar een datasource, zendt
queries en update statements en
verwerkt resultaten
3
ODBC vs JDBC
 ODBC is gespecificeerd als een C interface
 gebruikt van native functies -> geen 100% pure
java
 ODBC maakt veel gebruik van void*
 bron van fouten
 JDBC verbergt het geheugenbeheer
 JDBC is eenvoudiger te leren -> OO
 JDBC is nodig voor 100% pure java
 drivers voor 100% java zijn portable
 JDBC is gebouwd op ODBC
4
ODBC vs JDBC
 OLEDB -> low level voor developers
 ADO -> OO maar niet portable
 RDS -> vergelijkbaar met JDBC RowSet,
maar niet portable
 UDA marketing term voor ODBC, OLEDB,
ADO en RDS
maar geen support voor SQL3
5
JDBC implementaties
 2-tier
 applicatie, applet praat met database
 client/server architectuur
 zelfde toestel, lan, internet
 3-tier
 eenvoudiger te beveiligen
 beter te beheren
6
JDBC software
1
the JDBC driver manager
1 included as part of the Java 2 Platform
2
the JDBC driver test suite
 available from the JDBC web site

the JDBC-ODBC bridge
 included in the Solaris and Windows versions of the Java 2
Platform
7
Driver types

1 JDBC-ODBC bridge en ODBC driver: De Java Software bridge product
voorziet in JDBC toegang via ODBC drivers. De ODBC binaire code, en
meestal de database client code, moet op elke client geladen worden.
Deze architectuur is best geschikt voor bedrijven waar de client
installatie onder controle is.

2 Native-API partly-Java driver: Deze driver converteert JDBC calls in

3 JDBC-Net pure Java driver: Deze driver converteert JDBC calls in een

4 Native-protocol pure Java driver: Deze driver converteert JDBC calls
calls voor de API van Oracle, Sybase, Informix, IBM DB2, ... .
Opmerking, de operating systeem-specifieke binaire code moet geladen
worden op elke client.
DBMS-onafhankelijk net protocol, die dan door de server wordt
geconverteerd in het DBMS protocol. Pure Java clients kunnen zo
connecteren op verschillende databases. Het protocol is afhankelijk van
de leverancier. Algemeen is dit het meest flexibele alternatief voor
JDBC. Meestal zijn de producten geschikt voor intranets.
direct in een netwerk protocol dat gebruikt wordt door de DBMS-en. De
client communiceert rechtstreeks met de DBMS, geschikt voor het
intranet. De meeste van deze protocollen zijn proprietary, de database
leveranciers leveren hun eigen drivers. De volgende zijn momenteel
verkrijgbaar: Oracle, Sybase, Informix, IBM DB2, Inprise InterBase, and
Microsoft SQL Server.
8
Het Connection object


Een Connection object beheert de sessie met de server.
Een applicatie kan meerdere Connection objecten
hebben naar dezelfde of meerdere DBMS-en.
String url = "jdbc:odbc:film";
String user = “gebruiker1”;
String pwd = “pwd1”
Connection con = DriverManager.getConnection(url, user, pwd);

Het paswoord in de source opnemen heeft nadelen.
Zelfs een jar file kan niet beveiligd worden tegen decompilatie.
9
De JDBC URL
 De algemene vorm is <protocol>:<sub-protocol>:<sub-naam>
 Het <protocol> staat eerst en is steeds jdbc
 Het <sub-protocol> is naam van de driver of de naam van het
databank connectiviteit mechanisme.
Een voorbeeld van een sub-protocol is odbc.
 De sub-naam verwijst naar de ODBC-DSN.
jdbc:odbc:film
In dit voorbeeld, is het sub-protocol ODBC. De sub-naam film is
een lokale ODBC-DSN. Deze DataSource name wordt
aangemaakt via Control Panel -> Administrative Tools -> Data
Sources (ODBC)
10
Verzenden van SQL-statements
 Eens de connectie is opgebouwd, kunnen
commando's naar de databank worden
verzonden.
 Alle aangeboden commando's worden
verzonden.
De gebruiker moet er over waken dat DBMS
specifieke commando's naar de juiste DBMS
gestuurd worden!
 Het Connection object beschikt over 3
methoden om SQL-statements te verzenden
11
Verzenden van SQL-statements



Connection.createStatement creëert een
Statement object voor het verzenden van statische
SQL-statements
Connection.prepareStatement creëert een
PreparedStatement object. De PreparedStatement
interface wordt gebruikt om precompiled statements
uit te voeren. De interface heeft methoden om de IN
parameters in te vullen.
Connection.prepareCall creëert een
CallableStatement object. De CallableStatement
interface wordt gebruikt om stored procedures uit te
voeren. De interface heeft bovendien methoden om
de OUT en INOUT parameters in te vullen
12
Transactions



Het Connection object beheert de transaction
Default staat dit in auto-commit-mode
Deze mode kan gewijzigd worden met de methode
setAutoCommit(boolean)


Het begin van de transactie wordt door het
Connection object vastgesteld
De transactie beëindigen, gebeurt met behulp van de
methode commit() of rollback()
13
Transactions
 De Connection interface heeft een methode om de mate van beveiliging
van transacties in te stellen.
Connection.setTransactionIsolation(level)
 Volgende levels worden ondersteund, sommige DBMS-en kennen er nog
andere.




TRANSACTION_READ_UNCOMMITTED
Dit niveau laat toe dat een rij wordt gewijzigd door de ene transactie, vervolgens wordt
gelezen door een andere transactie zonder dat de eerste transactie is bevestigd. Dit
noemt men een “dirty read”. Als de eerste transactie een rollback uitvoert heeft de
tweede transactie foute data gelezen.
TRANSACTION_READ_COMMITTED
“Ditry reads” worden voorkomen.
TRANSACTION_REPEATABLE_READ
“Dirty reads” en “non-repeatable reads” worden voorkomen.Een “non-repeatable read”
treedt op als tussen twee leesoperaties van een transaction de rij wordt gewijzigd en
bevestigd door een tweede transactie
TRANSACTION_SERIALIZABLE
“Dirty reads”, “non-repeatable reads” en “phantom reads” worden voorkomen. Een
“phantom read” treedt op als twee identieke selecties na elkaar een ander resultaat
14
opleveren.
Transactions, dirty read


Transaction1
begin
transaction
update….
Transaction 2

hhh


rollback
begin
transaction
select ….
Data is nu
foutief
Een dirty read treedt op wanneer data gelezen wordt tussen
wijziging en een commit
15
Transactions, non-repeatable reads


Transaction1
begin
transaction
select….
Transaction 2

hhh


select….
Ander resultaat !

begin
transaction
update ….
commit
Een non-repeatable read treedt op wanneer twee dezelfde leesopdrachten een verschillende waarde opleveren
16
Transactions, phantom reads


Transaction1
begin transaction
select….where
Transaction 2

hhh
hhh

update….where
Ander resultaat !


begin
transaction
update ….
commit
Een phantom read treedt op wanneer twee dezelfde leesopdrachten
een verschillende dataset opleveren
17
De DriverManager



Het DriverManager object beheert de JDBC drivers
Drivers registreren zichzelf bij de DriverManager als
de driver wordt geladen
Wanneer een connectie aanvraag gebeurt, gebruik
hiervoor
DriverManager.getConnection()
dan test de DriverManager elke gekende driver om de
verbinding op te bouwen.
De eerste die reageert maakt de verbinding.
De volgorde van registratie is belangrijk, - instellen
van de system property jdbc.drivers -. Plaats een type
4 driver eerst, dan 3, 2 en uiteindelijk type 1.
18
De DriverManager

Een principe voorbeeld:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//laad de databank driver
String url = "jdbc:odbc:film";
String user = “gebruiker1”;
String pwd = “pwd1”
Connection con = DriverManager.getConnection(url, user, pwd);
// zet een connectie op met de databank
19
De DriverManager
import java.sql.*;
public class App1
{
public static void main (String args[])
{
Connection con=null;
String dsn =“film";
String user="";
String pwd="";
System.out.println("starting");
try
{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:"+dsn,
user, pwd );
}
catch(ClassNotFoundException cnfe)
{ System.out.println("jdbc-odbc driver bridge not found”);
}
catch(SQLException sqle)
{ System.out.println("connection refused to ODBC DSN”);
}
}
}
20
De DriverManager


Alle methoden van de klasse
DriverManager zijn static
De constructor van de klasse
DriverManager is private !

Het is onmogelijk om zelf een instantie te
creëeren van een DriverManager.
21
De Statement class



Dient om een statisch SQL-statement te
verzenden
Een Statement object wordt gecreëerd door
het uitvoeren van de methode
createStatement() van een Connection object
De belangrijkste methoden zijn


executeQuery()
executeUpdate()
22
Het Statement object
executeQuery( String SQL-query)
throws SQLException
Alleen select statements !
returnt een ResultSet object
23
Het Statement object
 executeUpdate( String SQL-query)
 throws SQLException
 Alleen insert, update, delete of DDL
statements !
DDL = create table, drop table, alter table,...
 returnt het aantal gewijzigde rijen,
bij DDL statements is het aantal gewijzigde
rijen steeds 0
24
executeQuery()
try
{
if(con != null)
{ Statement s = con.createStatement();
ResultSet rs = s.executeQuery(“select * from
films”);
//zie voorbeeld resultset
}
}
catch(SQLException sqle)
{
sqle.printStackTrace();
}
25
De ResultSet
 Een ResultSet object bevat de rijen en
kolomen die voldoen aan het select
statement.
 De data in een ResultSet is toegankelijk rij
per rij.
 De cursor in de ResultSet geeft de huidige rij
aan. De cursor wordt verplaatst met de
next() methode.
 De standaard cursor beweegt alleen
voorwaarts.
Dit kan gewijzigd worden door een ander
soort ResultSet in te stellen. Het standaard
type is ResultSet.TYPE_FORWARD_ONLY
26
De ResultSet
 De cursor staat bij een nieuw ResultSet object vóór
de eerste rij.
 De kolomen zijn toegankelijk met de methoden
getInt(), getFloat(), getDouble(), getString(), ....
Al deze methoden bestaan in 2 varianten:
 getType( int kolomnummer ) kolom 0 bestaat niet !
 getType ( String kolomnaam )
 Een ResultSet die gecreëerd is door een de
parameterloze versie van createStatement() is niet
aanpasbaar en kan slechts in 1 richting worden
doorlopen.
27
De ResultSet
while( rs.next() )
{
System.out.println(rs.getString(1)+
” “+rs.getString(2));
}
Op deze wijze kunnen we door alle records heen
lopen.
28
ResultSetMetaData
 Deze klasse bevat kolomnamen, datatypes,...
van de ResultSet (metadata=gegevens over
de gegevens)
 Gebruik ResultSet.getMetaData() om het
ResultSetMetaData object te creëren.
 Enkele methoden:
 getColumnName( int kolom )
 getColumnCount()
 getColumnClassName(int kolom )
returnt de naam van de meest geschikte java
klasse om de data in op te vragen.
29
ResultSet en ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
StringBuffer resultaat = new StringBuffer("<HTML>\n<BODY>\n<TABLE
BORDER=10>\n");
int colCount = rsmd.getColumnCount();
//start vanaf 1 !!!!!
resultaat.append("<TR>");
for(int i=1;i<=colCount;i++)
{
resultaat.append("<TH>"+rsmd.getColumnName(i)+"</TH>");
}
resultaat.append("</TR>\n");
while (rs.next())
{
resultaat.append("<tr>");
for(int i=1;i<=colCount;i++)
resultaat.append("<td>"+rs.getString(i)+"</td>");
resultaat.append("</tr>");
voor gebruik binnen HTML
}
worden al de hier gebruikte
resultaat.append("</TABLE>\n</BODY>\n</HTML>\n");
types ingelezen als String
sqlViewPane.setText(resultaat.toString());
pack();
JEditorPane
contentType = text/html
30
Lezen uit de ResultSet
31
Het Statement object
 executeUpdate( String SQL-query)
 throws SQLException
 Alleen insert, update, delete of DDL statements !
 DDL = create table, drop table, alter table,...
 returnt het aantal gewijzigde rijen,
bij DDL statements is het aantal gewijzigde rijen
steeds 0
32
executeUpdate()
Statement s = con.createStatement();
int rowCount = s.executeUpdate("update films set jaar = ‘2010’");
switch(rowCount)
{
case 0:
JOptionPane.showMessageDialog(this,"There are no rows affected");
break;
case 1:
JOptionPane.showMessageDialog(this,"There's one row affected");
break;
default :
JOptionPane.showMessageDialog(this,"There are "+rowCount+" rows affected");
break;
}
33
Scrollable resultSet


Deze resultSet wordt veel gebruikt in combinatie met
een GUI, de gebruiker heeft dan willekeurige
toegang.
Deze resultSet wordt gecreëerd door:
Connection.createStatement(resultSetType, resultSetConcurrency);

resultSetType is:
 TYPE_FORWARD_ONLY -> NIET GEBRUIKEN !
 TYPE_SCROLL_INSENSITIVE
 TYPE_SCROLL_SENSITIVE

resultSetConcurrency is:
 CONCUR_READ_ONLY
 CONCUR_UPDATABLE
34
Scrollable resultSet
 TYPE_SCROLL_INSENSITIVE
 veranderingen gemaakt door andere gebruikers, zijn niet zichtbaar
 TYPE_SCROLL_SENSITIVE
 veranderingen gemaakt door andere gebruikers, zijn wel zichtbaar
 Scrollen gebeurt door volgende methoden:
 absolute( recordnr ), relative( rows )
 beforeFirst(), afterLast()
 next(), previous()
 first(), last()
35
Aanpasbare ResultSet

Een aanpasbare resultSet wordt
gecreëerd door
Connection.createStatement(resultSetType, resultSetConcurrency);

resultSetType is:
 TYPE_FORWARD_ONLY
 TYPE_SCROLL_INSENSITIVE
 TYPE_SCROLL_SENSITIVE

resultSetConcurrency is:
 CONCUR_READ_ONLY -> NIET GEBRUIKEN !
 CONCUR_UPDATABLE
36
Aanpasbare ResultSet
 Kan niet met de jdbc-odbc bridge
 Gebruik de updateXXX() methoden van
ResultSet, zie tabel van getXXX() methoden
 De aanpassingen in een rij worden
weggeschreven d.m.v de methode UpdateRow()
van ResultSet
 Wanneer je, je aangepaste resultaten opnieuw
wil lezen, moet de resultset van het type
SCROLL_SENSITIVE zijn !
37
Voorbeeld: DisplayAuthors



We raadplegen de databank books
We vragen de tabel authors op
We tonen de gegevens in een JTextArea
38
De databank books
authors
authorISBN
titles
1
8
1
authorID
firstName
isbn
isbn
8
authorID
title
editionNumber
lastName
copyright
8
publishers
publisherID
1
publisherID
imageFile
publisherName
price
Fig. 23.11 Table relationships in books.
39
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// Fig. 23.26: DisplayAuthors.java
// Displaying the contents of the authors table.
import
import
import
import
java.awt.*;
java.sql.*;
java.util.*;
javax.swing.*;
Importeer de package
java.sql, die klassen en
interfaces bevat voor de JDBC
API.
public class DisplayAuthors extends JFrame {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
static final String DATABASE_URL = "jdbc:odbc:books";
// declare Connection and Statement for accessing
// and querying database
private Connection connection;
private Statement statement;
// constructor connects to database, queries database, processes
// results and displays results in window
public DisplayAuthors()
{
super( "Authors Table of Books Database" );
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
try {
// connect to database books and query database
// load database driver class
Class.forName( JDBC_DRIVER );
Laad de klasse
definitie voor de
databank driver.
// establish connection to database
connection = DriverManager.getConnection( DATABASE_URL,””,””);
// create Statement for querying database
statement = connection.createStatement();
Roept de methode createStatement aan om een object te
// query database
krijgen dat de interface Statement implementeert.
ResultSet resultSet =
statement.executeQuery( "SELECT * FROM authors" );
Voert de query uit om alle gegevens uit de tabel authors op te halen
// process query results
StringBuffer results = new StringBuffer();
ResultSetMetaData metaData = resultSet.getMetaData();
int numberOfColumns = metaData.getColumnCount();
getMetaData() haalt de
metadata op van de
ResultSet
De methode getColumnCount
levert het aantal
kolommen van de ResultSet
af
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
for ( int i = 1; i <= numberOfColumns; i++ )
results.append( metaData.getColumnName( i ) + "\t" );
Voeg de namen van de
kolommen toe aan
StringBuffer
results.
results.append( "\n" );
while ( resultSet.next() ) {
for ( int i = 1; i <= numberOfColumns; i++ )
results.append( resultSet.getObject( i ) + "\t" );
results.append( "\n" );
}
Voeg de gegevens van elke rij uit de
ResultSet toe aan de StringBuffer
results.
// set up GUI and display window
JTextArea textArea = new JTextArea( results.toString() );
Container container = getContentPane();
container.add( new JScrollPane( textArea ) );
setSize( 300, 100 );
setVisible( true );
}
// end try
// set window size
// display window
Creeer de GUI die de
StringBuffer results
toont, stel de grootte van het
applicatie window en toon het
applicatie window.
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
// detect problems interacting with the database
catch ( SQLException sqlException ) {
JOptionPane.showMessageDialog( null, sqlException.getMessage(),
"Database Error", JOptionPane.ERROR_MESSAGE );
System.exit( 1 );
}
// detect problems loading database driver
catch ( ClassNotFoundException classNotFound ) {
JOptionPane.showMessageDialog( null, classNotFound.getMessage(),
"Driver Not Found", JOptionPane.ERROR_MESSAGE );
System.exit( 1 );
}
// ensure statement and connection are closed properly
finally {
try {
statement.close();
connection.close();
}
Sluit het Statement en de
databank Connection.
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
// handle exceptions closing statement and connection
catch ( SQLException sqlException ) {
JOptionPane.showMessageDialog( null,
sqlException.getMessage(), "Database Error",
JOptionPane.ERROR_MESSAGE );
System.exit( 1 );
}
}
}
// end DisplayAuthors constructor
// launch the application
public static void main( String args[] )
{
DisplayAuthors window = new DisplayAuthors();
window.setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE );
}
}
// end class DisplayAuthors
PrepareStatement



De query wordt op de server gecompileerd en alleen
de parameters moeten telkens worden ingevuld
PrepareStatement() is aanzienlijk sneller dan het
klassieke Statement()
Wordt gecreërd door
Connection.prepareStatement( query )
of
Connection.prepareStatement( query, resultSetType,
resultSetConcurrency)



query is een string die een of meerdere parameters bevat
in de query-string worden de parameters voorgesteld door
een vraagteken
die parameters worden ingevuld m.b.v. de methoden
setXXX(), zie tabel van getXXX() methoden
45
PrepareStatement
int filmNummer = 12;
preparedStatement ps = con.prepareStemement( select * from films where filmnr = ? );
ps.setInt(1 , filmNummer );
Parameter 1
Parameter 1
Parameterindex
rs = ps.executeQuery();
....
....
ps.setInt( 1 , 13 );
rs = ps.executeQuery();
46
CallableStatement




Hierdoor worden stored procedures uitgevoerd
De query wordt op de server gecompileerd en alleen de
parameters moeten ingevuld worden
CallableStatement() is aanzienlijk sneller dan het klassieke
Statement()
Wordt gecreërd door
Connection.prepareCall( query )
of
Connection.prepareCall( query, resultSetType, resultSetConcurrency)



query is een string die een of meerdere parameters bevat
in de query-string worden de parameters voorgesteld door een
vraagteken
de parameters worden ingevuld m.b.v. de methoden setXXX(), zie
tabel van getXXX() methoden
47
CallableStatement


De query string bevat volgt de volgende syntax
“{call <naam_van_procedure>([?[,?]])}”
Een voorbeeld
“{call zoekFilm(?,?)}”
Het eerste vraagteken zou het jaar van de film kunnen
zijn en het tweede vraagteken de naam van de
regisseur van de film
De parameters worden ingevuld, genummerd vanaf
één tot het aanwezige aantal vraagtekens. Nadien kan
de query worden uitgevoerd.
48
Transacties
Wanneer autoCommit op true staat, worden alle statements
onmiddellijk bevestigd .
Wanneer een stored procedure wordt uitgevoerd, wordt elk
statement op zich binnen de stored procedure bevestigd.
Wanneer het laatste statement van een groep misloopt, is de
database corrupt!
Wanneer autoCommit op false staat, wordt automatisch een
transactie gestart bij elk statement behalve commit() of
rollback().
Wanneer de commit() mislukt, treedt een SQLException op,
waarbij in het catch blok de rollback() plaats vindt.
49