JDBC: Java DataBase connectivity - martenserver.com

JDBC: Java DataBase connectivity
1.1
Wat is JDBC?
=> Biedt toegang tot relationele data vanuit Java
=> Via SQL-compatibele relationele gegevensbanken
=> Een API om SQL-statements uit te voeren en de verkregen resultaten te verwerken
1.1.1 Verschillende versies
JDBC 1.0 :
bevat de basisklassen en interfaces om een verbinding te maken met een gegevensbank, een aantal
SQL-opdrachten uit te voeren op die gegevensbank en de resultaten van die opdrachten te
verwerken.
JDBC 2.0:
Bestaat uit 2 delen: een basispakket en een optioneel pakket. Het basispakket omvat JDBC
1.0
en voegt er een aantal nieuwe functionaliteiten aan toe (o.a. results sets, wijzigingen via batch,
programmatorisch aanpaasen v/d gegevensbank, ondersteuning v/d SQL3)
JDBC 3.0:
Is een uitbreiding van 2.0 en bevat alle extensies, kan ook andere gegevenstructuren in tabelvorm
aan.
1.2 JDBC-drivers
Om client-applicaties toegang te geven tot een databankserver voorzien poducten een stel
productafhankelijke API's. De JDBC API biedt een alternatief voor deze productafhankelijke API's,
namelijk een API die voor elke SQL-compatibele relationele gegevensbank gebruikt kan worden.
De opdrachten van de JDBC API moeten nog vertaald worden naar opdrachten in de
productueafhankelijke API, deze implementatie wordt voorzien door de driver. Deze drivers
vertalen de algemene JDBC-opdrachten naar de productafhankelijke.
1.2.1 verschillende types JDBC-drivers
Er bestaan 4 soorten: “JDBC/ODBC”, ”deels Java,deels productafhankelijk”, “JDBC-Net” en “Pure
java”.
JDBC/ODBC:
Open DataBase Connectivityis de Microsoft's algemene API, de ODBC API voorziet een aantal
methodes. Deze methodes worden op zich geimplementeerd door de ODBC-driver. De
JDBC/ODBC-brug zorgt voor de vertaling van JDBC- naar ODBC-opdrachten, die op hun beurt
door de ODBC-laag vertaald worden naar de gegevensbankenopdrachten.
=> inefficïent, niet-performant, functionaliteit beperkt tot die van ODBC-driver
JDBC Samenvatting 1.0 Jelle De Weerdt
Deels Java, deels productafhankelijk:
Deze vertalen de methoden uit de JDBC API naar methoden vd/ productafhankelijke API
=> volledige functionaliteit v/d API
JDBC-Net:
Maken gebruik v/e tussenliggende gegevensbankserver. De JDBC-opdracht v/d Java-applicatie
wordt nu via de JDBC-driver doorgestuurd naar de tussenliggende server, die de aanvraag dan
verder afhandelt.
=>geschikt voor applicatie met verschillende gegevensbanken
Pure Java:
Volledig in java geschreven, hierbij wordt gebruik gemaakt van productafhankelijke
netwerkprotocols
=> meeste efficïente qua performantie, tijd en installatie
1.2.2 Laden v/d driver
Import java.sql.*;
...
private final static String PAR_DRIVER=”org.postgresql.Driver”;
...
try{
Class.forName(PAR_DRIVER);
}
catch(ClassNotFoundException e){
....//driver niet gevonden
}
Constante PAR_DRIVER stelt de volledige klassenaam vd JDBC-driver, elke driver implementeert
java.sql.Driver-interface. De methode forName laadt de driverklasse in de virtuele machine
1.3 Verbindingen met een gegevensbank
Eeenmaal de gepaste JDBC-driver is ingeladen, kan je een verbinding met een gegevensbank
opzetten. Een dergelijke verbinding wordt voorgesteld door een object v/h type Connection
private final static String PAR_JDBC_URL=”jdbc:postgresql:voorbeelden”;
private final static String PAR_LOGIN=”iii4”;
private final static String PAR_PASWOORD=”iii4pwd”;
Try{
Connection conn = DriverManager.getConnection( PAR_JDBC_URL, PAR_LOGIN,
PAR_PASWOORD);
Try{
...//gegevens ophalne,toevoegen,...
}
finally{
conn.close();
}
}
catch (SQLException e){ .....//gegevensbankproblemen }
verbinding aanmaken => getConnection van DriverManager met 3 argumenten: JDBC-URL,
username en passwoord
JDBC Samenvatting 1.0 Jelle De Weerdt
verbinding verbreken => close(van Connection)
een JDBC-URL bestaat uit 3 stukken:
- het protocol: altijd “jdbc”
- het subprotocol: identificeert de gegevensbankproducent: vb “postgresql”
- de subname: identificeert de gebruikte gegevensbank: bijv naam van gegevnsbank, of
server
uitzondering => SQLException
1.4 SQL-opdrachten
Eens er verbinding is, kan je de gegevensbank vanuit Java-programma een aantal SQL-opdrachten
laten uitvoeren. Hiervoor heb je een Statement nodig, dit object stuurt SQL-opdrachten naar de
gegevensbank en haalt de resultaten ervan op. Statement is een interface, een statement object wordt
op volgende wijze aangemaakt en terug afgesloten:
try{
Statement stmt=conn.createstatement();
try{ ...//SQL-opdrachten uitvoeren
finally{
stmt.close();
}
}
catch (SQLException e){
...
}
}
het object conn is v/h type Connection
1.4.1. DDL-opdrachten, insert, delete en update
Niet-zoek opdrachten zijn opdrachten die deel uitmaken v/d Data Definition Language (DDL) en
opdrachten die rijen in de gegevensbanken toevoegen, verwijderen of aanpassen via de INSERT,
DELETE en UPDATE SQL-opdrachten uit de DDL. In beide gevallen wordt de methode
executeUpdate van Statement aangeroepen.
public int executeUpdate(String sqlopdracht) throws SQLException;
1.4.2 Zoekodprachten
1.4.2.1 ResultSet bepalen
Zoekopdracht zijn SELECT-opdrachten die gegevens selecteren uit 1 of meerdere tabellen in de
gegevensbank en hun resultaat teruggeven als een aantal rijen met gegevens in 1 of meerdere
kolommen. Via executeQuery van statement worden deze uitgevoerd. Het resultaat is een object v/h
type ResultSet, je kan dan de inhoud ervan opvragen via:
public ResultSet executeQuery (String sqlOpdracht) throws SQLException;
JDBC Samenvatting 1.0 Jelle De Weerdt
1.4.2.2 Het ResultSet-object overlopen
elke ResultSet-object bevat een wijzer (cursor), deze wijzer staat na het aanmaken van het object
voor de eerste rij. Met deze wijzer wordt het Resultset-object rij na rij overlopen.
Next => cursor 1 rij naar beneden schuiven
ResultSet res= stmt.executeQuery(...);
while(res.next()){
...//haal de gegevens van de huidige rij op
}
1.4.2.3. Data ophalen
Uit de rij waar de cursor naar wijst, kunnen gegevens opgevraagd worden. De interface ResultSet
biedt verschillende getter-methoden om de gegevens op te vragen bijv:
public boolean getBoolean (int columnIndex) throws SQLException;
public boolean getDouble (int columnIndex) throws SQLException;
public boolean getString (int columnIndex) throws SQLException;
...
de methode getXxx geeft de waarde v/d kolom met volgnummer columnIndex terug als een object
of variable v/h tye xxx.
Er bestaan ook methoden waarbij je de gewenste kolom mag aanduiden met behulp van zijn naam
bijv:
public boolean getBoolean (String columnName) throws SQLException;
public boolean getDouble (String columnName) throws SQLException;
public boolean getString (String columnName) throws SQLException;
1.4.2.4 Voorbeeld
Voorbeeldje pg 103
1.4.2.5 Sluiten van een ResultSet-object
=> wordt automatisch afgesloten door het statement -object wanneer dit gesloten wordt of gerestart
1.5 Prepared Statements
PreparedStatement => is een interface afgeleid v/d interface Statement
Tijdens het aanmaken v/e prepared statement wordt reeds een SQL-opdracht meegegeven. Wanneer
de driver dit ondersteunt wordt deze opdracht onmiddelijk naar de gegevensbank doorgestuurd en
daar gecompileerd. Dit zorgt ervoor dat de SQL-opdracht sneller zal worden uitgevoerd.
1.5.1 Aanmaken van een prepared statement
=> prepareStatement v/e Connection-object opgeroepen, het argument is de SQL-opdracht
JDBC Samenvatting 1.0 Jelle De Weerdt
1.5.2. Parameters toekennen
Vooraleer je het aangemaakte prepared statement kan uitvoeren moet je de waarden van de
parameters toekennen. De methoden executeUpdate en executeQuery voeren de opdracht dan uit
1.5.3. Gegevensconversie
=> via PreparedStatement
1.5.4 SQL-injectie
Kan bijv voorkomen als een gebruiker via een webapplicatie parameters kan ingeven die gebruikt
worden in een SQL-opdracht( misbruik).
Voorbeeld pg 107
=> het gebruik van prepared statements voorkomt SQL-injecties
1.6 Callable Statements
Dienen om stored procedures v/e gegevesbanksysteem uit te voeren. Een stored procedure is een
functie of procedure die bewaard wordt in een gegevensbank en die door clientapplicaties kan
opgeroepen worden, ze halen informatie op uit de gegevensbank of manipuleert zijn data.
1.6.1 Aanmaken callable statement
Om een stored procedure te kunnen oproepen moet je zijn naam en eventuele parameters kennen.
Stored procedure zonder parameters uitvoeren:
=> CallableStatement-object aanmaken, met hulp van een Connection-object
=> Deze bevat een oproep van de te gebruiken procedure
=> prepareCall
static final String SELECTEER_BLA_OPDRACHT=”{call SELECTEER_BLA}”;
try{ CallableStatement stmt=conn.prepareCall(SELECTEER_BLA_OPDRACHT); }
- stored procedure is afgeleid van PreparedStatement
- SELECTEER_BLA = escae-syntax (moet tussen accolades)
1.6.2. Invoerparameters
invoerparameters zijn de argumenten van de procedure en zijn ?
Public void setXxx(int indexParameter, xxx waardeParameter) throws SQLException;
1.6.3 Uitvoerparameters
Na het oproepen van de procedure kan dan de warde opgevraagd worden van de bewuste
uitvoerparameter. Zien er ook uit als ? in de escape-syntax. Vooraleer het callable statement
JDBC Samenvatting 1.0 Jelle De Weerdt
uitgevoerd kan worden moet de uitvoerparameter geregistreerd worden met de methode
registerOutParameter
public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException
=> parameterIndex is het volgnummer
=> sqlType staat voor het generieke SQL-type v/d parameter, te vinden in Types in java.sql
=> indien DECIMAL of NUMERIC komt er een 3de parameter bij: int schaal = aantal cijfers na
komma
Na uitvoeringen kunnen de waarden van de uitvoerparameters opgevraagd worden via getters:
public xxx getXxx(int parameterIndex) throws SQLException
vb:
final static String SELECT_BLA_OPDR=”{ ? = call SELECTEER_BLA}”;
CallacleStatement cstmt= conn.prepareCall(SELECTEER_BLA_OPDR);
try{
cstmt registerOutParameter(1,java.sql.Types.INTEGER); //uitvoerparameter
cstmt.setString(2,blaCode);
//invoerparameter
ResultSet rs= cstmt.executeQuery();
...
// bla uit ResultSet halen
int aantalbla=cstmt.getInt(1);
//waarde ophalen uitvoerpar.
}
1.6.4. Invoer- en uitvoerparameters
Sommige zijn beide.
=> de parameter krijgt een waarde wia setter-methode
=> wordt dan geregistreerd met de emthode registerOutParameter
=> na oproepen kan de waarde afgehaald worden
vb:
final static String DOE_BLA_OPDR=”{ call DOE_BLA(?,?)}”;
CallacleStatement cstmt= conn.prepareCall(DOE_BLA_OPDR);
try{
cstmt.setString(1,blaCode);
//invoerparameter
cstmt.setInt(2,blaCodeInt);
//in- uitvoerparameter
cstmt.registerOutParameter(2,jav.sql.Types.INTEGER); //uitvoerpar registreren
cstmt.executeUpdate();
//procedure oproepen
int aantalbla=cstmt.getInt(2);
//waarde ophalen uitvoerpar.
}
1.7 Transacties
Een transactie is een reeks opdrachten die tesamen uitgevoerd moeten worden.
Sommige applicatie evreisen dat er een reeks SQLopdrachten succesvol uitgevoerd worden, indien
1 or meerdere niet lukken moet alles ongedaan worden. Indien alles gelukt is wordt alles permant
uitgevoerd (commit), indien 1 er 1 niet lukt wordt alles ongedaan gemaakt (rollback).
De interface Connection voorziet volgende methoden:
public void setAutoCommit(boolean autoCommit) throws SQLException);
public void commit() throws SQLException;
JDBC Samenvatting 1.0 Jelle De Weerdt
public void rollback() throws SQLException;
=> een Connection-object wordt aangemaakt in de auto-commit
=> voor meerdere opdrachten samen te kunnen uitvoeren moet auto-commit uitgeschakeld worden
via setAutocommit
=> methode rollback maakt alles ongedaan sinds de laatste commit
try{
conn.setAutoCommit(false);
...// vanalles
conn.commit();
}
catch( SQLException e){
conn.rollback();
finally{
conn.setAutoCommit(true);
}
}
=> andere gebruikers v/d gegevensbank zien het resultaat pas als de methode commit is uitgevoerd
=> bij gelijktijdige transacties heeft elke transactie een ander Connection-object nodig
1.8 Programmatorisch aanpassen v/d gegevensbank
=> sinds JDBC2.0 mogelijk gegevens aantepassen in de ResultSet via javamethodes (aanpassen,
toevoegen of verwijderen)
1.8.1 Scrollable ResultSet
=> de cursor verplaatsen in andere richting dan van boven naar beneden, of de cursor naar een
welbepaalde rij te verplaatsen of de positie v/d cursor te bepalen
hoe?
=> extra opties meegeven bij het aanmaken van het Statement-object
=> Connection heeft volgende methode:
public Statement createStatement(int resultSetType, int resultsetConcurrency)
throws SQLException;
public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)
throws SQLException;
public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency)
throws SQLException;
met resultSetType: - TYPE_FORWARD_ONLY (=eentje naar beneden)
- TYPE_SCROLL_INSENSITIVE (=op willekeurige wijze verplaatst worden,
onzichtbaar in ResultSet-object)
- TYPE_SCROLL_SENSITIVE (=op willekeurige wijze verplaats worden,
zichtbaar in Resultset-object)
Of ResultSet-object aanpasbaar is, wordt bepaald door de parameter resultSetConcurrency met
waarden ResultSet.CONCUR_READ_ONLY of ResultSet.CONCUR_UPDATABLE
vb:
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery(SQLZoekopdracht);
JDBC Samenvatting 1.0 Jelle De Weerdt
=> met SQLZoekOpdracht is een string die een SELEC-opdracht in SQL voorstelt.
=> de ResultSet kan van onder naarr boven doorlopen via afterLast en previous
Er zijn nog veel andere methodes zoals:
isLast (=true als cursor naar laatste rij wijst)
getRow (=geeft het rijnummer weer naarwaar cursor verwijst)
1.8.2 Aanmaken v/e aanpasbaar ResultSet-object
vb:
String SQLZoekopdracht=”select id, titel, code from tabel”;
Statement stmt = conn.createStatement( ResulstSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs= stmt.executeQuery(SQLZoekopdracht);
1.8.3 een rij aanpassen
=> iets aanpassen: via preparedstatement
String PasXAan=”update tabel set x=? where id=?”;
PreparedStatement stmt = conn.prepareStatement(PasXAan);
stmt.setString(1,x);
stmt.setString(2,id);
stmt.executeUpdate();
via JDBC2.0 API:
variabale rijNr= rijnummer v/h aantepassen object
x= nieuwe waarde
uprs= de hierboven aangemaak ResultSet
TITEL= naam v/d kolom
static final String TITEL=”kolom van x”;
...
uprs.absolute(rijNr);
//verplaats de cursor naar de rij in het Resultset-object
uprs.updateString(TITEL,x);
uprs.updateRow();
//voert de veranderingen door in de gegevensbank
De rij waar de cursor naar wijst kan aangepast worden met methodes v/d vorm:
public void updateXxx(String kolomnaam of int kolomnr, xxx waarde) hrows SQLException;
Om alle veranderingen ongedaan te maken: cancelRowUpdates
1.8.4 Een rij toevoegen
=> met preparedStatement
bij start wordt een PreparedStatement-object aangemaakt:
String voegXToe=”insert into tabel values(?,?)”;
PreparedStatement stmt = conn.prepareStatement(voegXToe);
stmt.setString(1,id);
JDBC Samenvatting 1.0 Jelle De Weerdt
stmt.setString(2,titel);
stmt.executeUpdate();
via JDBC2.0:
uprs.moveToInsertRow();
//beweeg de cursor naar de invoegrij
uprs.updateString(ID,id);
//via updateXxx kan je de waarden wijzigen
uprs.updateString(TITEL,titel);
uprs.insertRow();
//voert de wijzigen effetief door op de gegevensbank
met uprs het ResultSet-object uit 1.8.2 en ID en TITEL zijn ergens anders in de applicatie
gedeclareerd en stellen de kolomnamen in de result set voor, er mogen ook kolomnummers gebruikt
worden. Met moveToCurrentRow verplaatsen we de cursor naar de laatste rij voor
moveToInsertRow() aageroepen werd.
1.8.5 Een rij verwijderen
=> verplaats de cursor naar de te verwijderen rij en verwijder dan met deleteRow
uprs.absolute(rijNr);
uprs.deleteRow();
1.8.6 Welke zoekopdrachten?
Er zijn een aantal criteria die normaal gezien voor een aanpasbare result set moeten zorgen:
1. De zoekopdracht verwijst naar 1 tabel
2. De zoekopdracht bevat geen “joins”-of GROUP BY-clausule
3. De zoekopdracht selecteert de primaire sleutel v/d tabel
4. de gebruiker heeft lees- en schijfpermissies op de tabel in de gegevensbank
bij het toevoegen v/e rij meoten volgende waarden vervult zijn:
1. De zoekopdracht selecteert alle kolommen die neit leeg mogen zijn
2. de zoekopdracht selecteert alle kolommen zonder standaardwaarde.
JDBC Samenvatting 1.0 Jelle De Weerdt