JDBC tutorial (Dutch)

Snelmenu
Introductie
Foutenafhandeling
JDBC driver installeren
Database voorbereiden
JDBC URL voorbereiden
Eenvoudige INSERT
Eenvoudige SELECT
Eenvoudige UPDATE
Eenvoudige DELETE
Voorbereide statementen
DTO's zijn een must
Een universele DAO ontwerpen?

Introductie

Java kent een aantal standaard klassen waarmee je met SQL databases kunt communiceren, deze klassen (eigenlijk interfaces) zitten in het java.sql package onder de noemer "JDBC API" (Java DataBase Connectivity Application Programming Interface). Hieronder staan enkele klassen en methoden die van belang zijn bij de communicatie met SQL databases.

java.sql klasse	beschrijving
DriverManager	Beheert de JDBC drivers.
Connection	Opent een sessie met een specifieke database.
Statement	Een open statement om SQL queries mee te versturen en resultaten te ontvangen. De volgende twee methoden zijn van nut: executeQuery(sql): nuttig voor SELECT queries en retourneert een ResultSet. executeUpdate(sql): nuttig voor INSERT, UPDATE en DELETE queries en retourneert de hoeveelheid getroffen rijen in vorm van een int.
PreparedStatement	Een open statement met een voorbereide SQL query waaraan je slechts de waarden hoeft toe te voegen. Zeer nuttig om eventuele SQL injecties te voorkomen doordat je met specifieke objecten kunt werken in plaats van met strings. Bij de stringconversies worden de aanhalingstekens automatisch opgevangen. De volgende twee methoden zijn van nut: executeQuery(): nuttig voor SELECT queries en retourneert een ResultSet. executeUpdate(): nuttig voor INSERT, UPDATE en DELETE queries en retourneert de hoeveelheid getroffen rijen in vorm van een int.
ResultSet	Het resultaat van Statement#executeQuery() zal in een ResultSet gezet worden.

Het enige extra wat je buiten de standaard JDBC API nodig hebt is een geldige JDBC driver voor de gewenste database. De JDBC driver is grof gezegd de concrete implementatie van de JDBC API, dat bijna louter uit interfaces bestaat. Deze kun je normaliter als een JAR bestand downloaden, die je dan in de classpath moet zetten. Voorbeelden: MySQL Connector/J, DB2 Driver for JDBC and SQLJ, Oracle JDBC Driver, PostgreSQL JDBC Driver, Microsoft SQL Server JDBC Driver, etcetera.

Terug naar boven

Foutenafhandeling

Praktisch alle klassen van de java.sql package kunnen een SQLException of een subklasse daarvan afwerpen. Je bent dus verplicht om de SQL acties in een try-catch-finally statement te zetten. Anders moet je desbetreffende SQLException doorgooien via de throws bepaling van de methode.

Het is zeer aanbevelenswaardig om de database sessie (Connection) na het gebruik altijd te sluiten met de close() methode om de systeembronnen te vrijgeven. Deze wordt namelijk niet automatisch direct na gebruik gesloten. Als je dat niet doet, dan kun je wanneer je veel connecties maakt na een tijdje een tekort aan systeembronnen krijgen, met alle desastreuze gevolgen van dien.

Wanneer je de Connection sluit, dan zullen de 'goede' JDBC drivers ook alle binnen dezelfde sessie geopende statementen en resultsets ook automatisch gesloten worden. Wanneer je een afzonderlijke Statement of PreparedStatement binnen dezelfde sessie sluit, dan zullen de 'goede' JDBC drivers alle geopende resultsets ook automatisch sluiten, maar de connectie blijft nog wel open. Wanneer je een afzonderlijke ResultSet binnen dezelfde sessie sluit, dan zul je er niet meer doorheen kunnen lopen, maar blijven de connectie en de statementen nog wel open. Wanneer je binnen een connectie meerdere statementen en/of resultsets wilt gaan openen, dan is het verstandig om deze direct te sluiten na het het verwerken ervan, want de hoeveelheid tegelijkertijd geopende statementen en resultsets is niet ongelimiteerd. Wanneer je binnen een statement meerdere resultsets wilt gaan openen, dan zullen de 'goede' JDBC drivers de eerder geopende resultsets automatisch sluiten.

Ondanks dat 'goede' JDBC drivers voor het automatisch sluiten zorgen, is het jouw taak als een 'goede' developer om alle connecties, statementen en resultsets zelf te sluiten! Anders breekt jouw hele applicatie wanneer iemand een 'slechte' JDBC driver gebruikt.

Terug naar boven

JDBC driver installeren

Hier zullen we van een MySQL database uitgaan. MySQL is de meest gebruikte freeware database. Hoe MySQL te installeren valt buiten de scope van dit artikel. Het wijst praktisch van zichzelf uit: even MySQL downloaden en dan de installer uitvoeren.

Om een SQL database vanuit Java te kunnen benaderen zul je dus de bijbehorende JDBC driver moeten downloaden en installeren, voor MySQL is dit de MySQL Connector/J. Download bij voorkeur de meest recente versie, op het moment van schrijven is dit versie 5.0. Download hier het zip bestand. Pak deze zip uit en haal daar het JAR bestand uit, in dit geval heet deze mysql-connector-java-5.0.8-bin.jar (let op: de versie nummer kan verschillen).

Update: er is een nieuwere Connector/J beschikbaar, de versie 5.1. Hiervoor heb je echter minimaal Java 6.0 nodig, aangezien de JDBC 4.0 specificatie pas in Java 6.0 is geintroduceerd. De Connector/J 5.0 werkt nog wel op Java 5.0 met de JDBC 3.0 specificatie.

Om deze driver in je Java code te kunnen gebruiken, zul je deze eerst in de classpath moeten zetten. Wanneer je het lokaal buiten de IDE wil gebruiken, dan zul je mogelijk eerst de omgevingsvariabele classpath moeten definieren waar je de JAR's kunt neerplanten, dit staat hier uitgelegd: Uitvoeren - Classpath. Wanneer je het in een applicatieserver wil gebruiken, dan zul je deze JAR's in de classpath van de applicatieserver moeten zetten, gewoonlijk is dit de /lib directory. Binnen een IDE, zoals Eclipse, volstaat het om deze driver aan de Java Build Path toe te voegen, zie de onderstaande procedure:

1. Maak eventueel een project aan: File - New - Project... - Java Project, klik op Next, geef het een naam, bijvooorbeeld "MyDao", laat de rest van de velden standaard en klik op Finish.


2. Importeer de JAR; rechtsklik op dit project: Import... - General - File System, klik op Next, wijs de directory aan waar het mysql-connector-java-5.0.8-bin.jar bestand zit, vink het bestand aan en klik op Finish.
   


3. Voeg de JAR tenslotte toe aan de classpath van het project; rechtsklik op het project - Properties - Java Build Path - Libraries - Add JARs - selecteer de zojuist geimporteerde JAR en klik op Finish.
   

Voor alle andere databases geldt hetzelfde procedure: download de JAR(s) en zet het in de classpath. Bij sommige database servers worden inderdaad meerdere JAR's geleverd, zoals bij DB2.

Terug naar boven

Database voorbereiden

Ter voorbereiding: we gaan hier ervan uit dat je de SQL basics onder de knie hebt. JDBC en SQL staan op zich volledig los van elkaar: de SQL queries moet je zelf schrijven, dat doet JDBC niet voor jou. Het verzorgt slechts de communicatie tussen de Java code en de SQL database. Mocht SQL jou ook niet helemaal bekend zijn, dan kan het waard zijn jezelf daar eerst in te verdiepen: SQL tutorials op het Internet.

We zullen nu eerst even een voorbeeld MySQL database tabel met een auto-generated technische ID veld "ID", een alfanummeriek veld "Name" en een nummeriek veld "Value" voorbereiden. Voer de onderstaande MySQL SQL uit in de database:

CREATE DATABASE javabase;
CREATE TABLE javabase.maintable (
    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255),
    Value INT
);

Let op: de SQL CREATE commando's kunnen per database verschillen. Voor bijvoorbeeld DB2, Oracle, PostgreSQL en Microsoft SQL Server moet je zo'n tabel op een iets andere manier aanmaken, omdat ze met databaseschema's werken, iets wat MySQL nog volledig onbekend is. Daarnaast heeft iedere database een andere implementatie van een auto-generated ID veld. Hier staat een rijke bron aan database-specifieke SQL commando's: SQLzoo.net.

Hieronder staat hoe je een vergelijkbaar tabel in DB2 kunt aanmaken:

CREATE DATABASE javabase;
CREATE SCHEMA schema;
CREATE TABLE javabase.schema.maintable (
    ID BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    Name VARCHAR(255),
    Value INT
);

En de Oracle en PostgreSQL equivalent:

CREATE DATABASE javabase;
CREATE SCHEMA schema;
CREATE TABLE javabase.schema.maintable (
    ID SERIAL PRIMARY KEY,
    Name VARCHAR(255),
    Value INT
);

Noot: deze query maakt ook automatisch een sequence aan in javabase.schema.maintable_id_seq.

En tenslotte die voor de Microsoft SQL Server:

CREATE DATABASE javabase;
CREATE SCHEMA schema;
CREATE TABLE javabase.schema.maintable (
    ID BIGINT IDENTITY PRIMARY KEY,
    Name VARCHAR(255),
    Value INT
);

Terug naar boven

JDBC URL voorbereiden

Om een database tabel vanuit de Java code te kunnen aanroepen heb je een JDBC URL nodig. Deze is in het geval van MySQL databases als volgt opgebouwd:

jdbc:mysql://hostname:port/database?user=username&password=password

hostname: verplicht, de host naam of de IP. Bijvoorbeeld: localhost of 127.0.0.1

port: optioneel, de poort van de database. Bijvoorbeeld: 3306

database: verplicht, de naam van de database. Bijvoorbeeld: javabase

username: optioneel, de inlog naam voor de database. Bijvoorbeeld: root

password: optioneel, de wachtwoord voor de database. Bijvoorbeeld: d$7hF_r!9Y

Uitgaande van een MySQL database op je eigen computer (localhost) achter de standaard poort 3306 (die je eigenlijk gewoon kunt weglaten) en een gebruikeraccount root met de wachtwoord d$7hF_r!9Y, zou onze MYSQL JDBC URL er zo uitzien:

  jdbc:mysql://localhost:3306/javabase?user=root&password=d$7hF_r!9Y

Let op: dit verschilt dus per database type, zie ook de documentatie bij de JDBC driver. De onderstaande voorbeelden zijn uit desbetreffende documentatie gehaald. Wanneer deze URL's om een of andere reden niet lekker werken, dan zul je het beste even zelf de JDBC documentatie moeten doornemen.

Voor een DB2 server die standaard op poort 50000 zit ziet een vergelijkbare JDBC URL als volgt eruit:

  jdbc:db2://localhost:50000/javabase:user=root;password=d$7hF_r!9Y

Voor Oracle die standaard achter poort 1521 bereikbaar is moet je de JDBC URL als volgt formuleren:

  jdbc:oracle:thin:root/d$7hF_r!9Y@//localhost:1521/javabase

Voor PostgreSQL gelden dezelfde richtlijnen als MySQL, behalve dan dat het als standaard poort 5432 gebruikt:

  jdbc:postgresql://localhost:5432/javabase?user=root&password=d$7hF_r!9Y

Voor de Microsoft SQL Server die standaard op poort 1433 zit zou zo'n JDBC URL als volgt eruitzien:

  jdbc:sqlserver://localhost:1344;databaseName=javabase;user=root;password=d$7hF_r!9Y

Terug naar boven

Eenvoudige INSERT

Hieronder volgt een compleet werkend voorbeeld van een eenvoudige INSERT actie op de database.

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

    public static void main(String[] args) {

        // Laad eerst de driver.
        try {
            Class.forName("com.mysql.jdbc.Driver");

            // Voor DB2 laad je de driver als volgt:
            // Class.forName("com.ibm.db2.jcc.DB2Driver");

            // Voor Oracle laad je de driver als volgt:
            // Class.forName("oracle.jdbc.driver.OracleDriver");

            // Voor PostgreSQL laad je de driver als volgt:
            // Class.forName("org.postgresql.Driver");

            // Voor Microsoft SQL Server laad je de driver als volgt:
            // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            System.out.println("Laden van de driver is gelukt.");
        } catch (ClassNotFoundException e) {
            System.err.println("Kan de driver niet in de classpath vinden.");
            e.printStackTrace();
            return;
        }

        // Voorbereid de connectie, statement, resultset, URL en INSERT query.
        Connection connection = null;
        Statement statement = null;
        ResultSet generatedKeys = null;
        String url = "jdbc:mysql://localhost/javabase?user=root&password=d$7hF_r!9Y";
        String insertQuery = "INSERT INTO maintable (name, value) VALUES ('testnaam', 10)";

        // Voor DB2, Oracle, PostgreSQL en MSSQL moet je ook de database schema aanwijzen:
        // INSERT INTO schema.maintable (name, value) VALUES ('testnaam', 10)

        try {
            // Verkrijg de connectie.
            connection = DriverManager.getConnection(url);

            // Verkrijg de statement.
            statement = connection.createStatement();

            // Voer de INSERT query uit.
            int affectedRows = statement.executeUpdate(insertQuery);

            // Verkrijg de INSERT ID.
            if (affectedRows == 1) {
                generatedKeys = statement.getGeneratedKeys();

                // Let op: het is afhankelijk van de JDBC driver of dit werkt! Bijvoorbeeld de
                // Oracle en PostgreSQL JDBC drivers ondersteunen dit niet. Je zult deze als
                // sequence moeten opvragen, waarbij de sequence naam in regel als volgt is:
                // "schemanaam.tabelnaam" + "_id_seq". Doe dit wel binnen dezelfde statement!
                // 
                // String sequenceQuery = "SELECT currval('schema.maintable_id_seq')";
                // generatedKeys = statement.executeQuery(sequenceQuery);

                if (generatedKeys.next()) {
                    long insertID = generatedKeys.getLong(1);
                    System.out.println("Insert ID is: " + insertID);
                }
            }

            // Klaar!
            System.out.println("Uitvoeren van de INSERT query is gelukt.");
        } catch (SQLException e) {
            // Foutje?
            System.err.println("Uitvoeren van de INSERT query is mislukt.");
            e.printStackTrace();
        } finally {
            // Sluit de resultset, statement en connectie. Doe dit altijd in de finally blok!
            if (generatedKeys != null) {
                try {
                    generatedKeys.close();
                } catch (SQLException e) {
                    // Niks aan te doen.
                    System.err.println("Kan de resultset niet sluiten.");
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    // Niks aan te doen.
                    System.err.println("Kan de statement niet sluiten.");
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    // Niks aan te doen.
                    System.err.println("Kan de connectie niet sluiten.");
                    e.printStackTrace();
                }
            }
        }
    }

}

Laden van de driver is gelukt.

Insert ID is: 1

Uitvoeren van de INSERT query is gelukt.

Je kunt de connectie ook op een andere manier verkrijgen, de DriverManager.getConnection() is ook op de volgende manier te gebruiken:

        ...

        // Voorbereid de URL, de gebruikersnaam en de wachtwoord.
        String url = "jdbc:mysql://localhost/javabase";
        String username = "root";
        String password = "d$7hF_r!9Y";

        try {
            // Verkrijg de connectie.
            connection = DriverManager.getConnection(url, username, password);

            ...

Je kunt dus de username en de password gedeelten uit de JDBC URL weghalen en deze apart doorgeven.

Omdat het sluiten van de connectie, statement en resultset vaker dan eens zal gebeuren, is het handiger om deze te refactoren naar een utility klasse met static methoden dat meer dan eens aangeroepen kan worden. Hieronder staat een voorbeeld:

package test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class SqlUtil {

    public static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // Niks aan te doen.
                System.err.println("Kan de connectie niet sluiten.");
                e.printStackTrace();
            }
        }
    }

    public static void close(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                // Niks aan te doen.
                System.err.println("Kan de statement niet sluiten.");
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                // Niks aan te doen.
                System.err.println("Kan de resultset niet sluiten.");
                e.printStackTrace();
            }
        }
    }

}

Die kun je dan als volgt in de finally blok gebruiken:

            ...

        } finally {
            // Sluit de resultset, statement en connectie. Doe dit altijd in de finally blok!
            SqlUtil.close(generatedKeys);
            SqlUtil.close(statement);
            SqlUtil.close(connection);
        }

Terug naar boven

Eenvoudige SELECT

Hieronder volgt een compleet werkend voorbeeld van een eenvoudige SELECT actie op de database.

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

    public static void main(String[] args) {

        // Laad eerst de driver.
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Laden van de driver is gelukt.");
        } catch (ClassNotFoundException e) {
            System.err.println("Kan de driver niet in de classpath vinden.");
            e.printStackTrace();
            return;
        }

        // Voorbereid de connectie, statement, resultset, URL en SELECT query.
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        String url = "jdbc:mysql://localhost/javabase?user=root&password=d$7hF_r!9Y";
        String selectQuery = "SELECT id, name, value FROM maintable WHERE name = 'testnaam'";

        try {
            // Verkrijg de connectie.
            connection = DriverManager.getConnection(url);

            // Verkrijg de statement.
            statement = connection.createStatement();

            // Voer de SELECT query uit.
            resultSet = statement.executeQuery(selectQuery);

            // Verwerk de resultaten.
            while (resultSet.next()) {
                long id = resultSet.getLong("id");
                String name = resultSet.getString("name");
                int value = resultSet.getInt("value");
                System.out.println("ID=" + id + ",Name=" + name + ",Value=" + value);
            }

            // Klaar!
            System.out.println("Uitvoeren van de SELECT query is gelukt.");
        } catch (SQLException e) {
            // Foutje?
            System.err.println("Uitvoeren van de SELECT query is mislukt.");
            e.printStackTrace();
        } finally {
            // Sluit de resultset, statement en connectie. Doe dit altijd in de finally blok!
            SqlUtil.close(resultSet);
            SqlUtil.close(statement);
            SqlUtil.close(connection);
        }
    }

}

Laden van de driver is gelukt.

ID=1,Name=testnaam,Value=10

ID=2,Name=testnaam,Value=10

ID=3,Name=testnaam,Value=10

Uitvoeren van de SELECT query is gelukt.

De eerder beschreven INSERT query werd inderdaad eerst driemaal uitgevoerd ;) De ResultSet van de SELECT query kun je trouwens ook op de volgende manier verwerken, met kolomnummers in plaats van kolomnamen. Hierbij kun je gewoon de volgorde van de kolommen van de database aanhouden en het begint altijd met 1 (en dus niet met 0!).

            // Verwerk de resultaten.
            while (resultSet.next()) {
                long id = resultSet.getLong(1);
                String name = resultSet.getString(2);
                int value = resultSet.getInt(3);
                System.out.println("ID=" + id + ",Name=" + name + ",Value=" + value);
            }

Deze methode is fractioneel sneller dan het gebruik van volledige kolomnamen, het scheelt in geval van de betere JDBC drivers ongeveer een halve procent tot één procent van de tijd.

Als je er niet helemaal zeker van bent in welk soort datatype of object je de verkregen waarde moet stoppen en/of wanneer je ClassCastException foutmeldingen krijgt, dan kun je ook proefondervindelijk getObject() van de resultset gebruiken om het resultaat te upcasten naar een Object. Met Object#getClass() kun je tenslotte de geinstantieerde klasse opvragen en derhalve de 'automatisch' door de driver vertaalde klasse achterhalen:

            // Achterhaal het vertaalde object type van de ID veld.
            if (resultSet.next()) {
                Object id = resultSet.getObject("ID");
                System.out.println(id.getClass());
            }

class java.lang.Long

In dit geval wordt een MySQL BIGINT veld dus door de JDBC driver geconverteerd naar een Long. Je zou de waarde dus het beste in het primitieve datatype long of in de wrapper datatype object Long moeten stoppen.

Terug naar boven

Eenvoudige UPDATE

Hieronder volgt een compleet werkend voorbeeld van een eenvoudige UPDATE actie op de database.

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

    public static void main(String[] args) {

        // Laad eerst de driver.
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Laden van de driver is gelukt.");
        } catch (ClassNotFoundException e) {
            System.err.println("Kan de driver niet in de classpath vinden.");
            e.printStackTrace();
            return;
        }

        // Voorbereid de connectie, statement, URL en UPDATE query.
        Connection connection = null;
        Statement statement = null;
        String url = "jdbc:mysql://localhost/javabase?user=root&password=d$7hF_r!9Y";
        String updateQuery = "UPDATE maintable SET name = 'anderenaam' WHERE id = 1";

        try {
            // Verkrijg de connectie.
            connection = DriverManager.getConnection(url);

            // Verkrijg de statement.
            statement = connection.createStatement();

            // Voer de UPDATE query uit.
            int affectedRows = statement.executeUpdate(updateQuery);
            System.out.println("Aantal getroffen rijen: " + affectedRows);

            // Klaar!
            System.out.println("Uitvoeren van de UPDATE query is gelukt.");
        } catch (SQLException e) {
            // Foutje?
            System.err.println("Uitvoeren van de UPDATE query is mislukt.");
            e.printStackTrace();
        } finally {
            // Sluit de statement en connectie. Doe dit altijd in de finally blok!
            SqlUtil.close(statement);
            SqlUtil.close(connection);
        }
    }

}

Laden van de driver is gelukt.

Aantal getroffen rijen: 1

Uitvoeren van de UPDATE query is gelukt.

Terug naar boven

Eenvoudige DELETE

Hieronder volgt een compleet werkend voorbeeld van een eenvoudige DELETE actie op de database.

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

    public static void main(String[] args) {

        // Laad eerst de driver.
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Laden van de driver is gelukt.");
        } catch (ClassNotFoundException e) {
            System.err.println("Kan de driver niet in de classpath vinden.");
            e.printStackTrace();
            return;
        }

        // Voorbereid de connectie, statement, URL en DELETE query.
        Connection connection = null;
        Statement statement = null;
        String url = "jdbc:mysql://localhost/javabase?user=root&password=d$7hF_r!9Y";
        String deleteQuery = "DELETE FROM maintable WHERE name = 'anderenaam'";

        try {
            // Verkrijg de connectie.
            connection = DriverManager.getConnection(url);

            // Verkrijg de statement.
            statement = connection.createStatement();

            // Voer de DELETE query uit.
            int affectedRows = statement.executeUpdate(deleteQuery);
            System.out.println("Aantal getroffen rijen: " + affectedRows);

            // Klaar!
            System.out.println("Uitvoeren van de DELETE query is gelukt.");
        } catch (SQLException e) {
            // Foutje?
            System.err.println("Uitvoeren van de DELETE query is mislukt.");
            e.printStackTrace();
        } finally {
            // Sluit de statement en connectie. Doe dit altijd in de finally blok!
            SqlUtil.close(statement);
            SqlUtil.close(connection);
        }
    }

}

Laden van de driver is gelukt.

Aantal getroffen rijen: 1

Uitvoeren van de DELETE query is gelukt.

Terug naar boven

Voorbereide statementen

Met PreparedStatement kun je een statement voorbereiden, waarbij je de openstaande waarden vult met een vraagteken "?". Dit is niet alleen handig voor veelgebruikte statementen, maar ook om SQL injecties te voorkomen, bij de stringconversies binnen de PreparedStatement worden de aanhalingstekens namelijk automatisch opgevangen. Hieronder volgt een voorbeeld van een voorbereide INSERT statement. De waarden moet je in de volgorde toevoegen zoals de vraagtekens in de voorbereide statement staan en de index begint altijd met 1 (en dus niet met 0!).

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {

    public static void main(String[] args) {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Laden van de driver is gelukt.");
        } catch (ClassNotFoundException e) {
            System.err.println("Kan de driver niet in de classpath vinden.");
            e.printStackTrace();
            return;
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet generatedKeys = null;
        String url = "jdbc:mysql://localhost/javabase?user=root&password=d$7hF_r!9Y";

        // Voorbereid de INSERT query.
        String preparedInsertQuery = "INSERT INTO maintable (name, value) VALUES (?, ?)";

        try {
            connection = DriverManager.getConnection(url);

            // Verkrijg de voorbereide statement.
            preparedStatement = connection.prepareStatement(preparedInsertQuery);

            // Voeg de waarden toe.
            preparedStatement.setString(1, "testname");
            preparedStatement.setInt(2, 10);

            // Voer de INSERT query uit.
            preparedStatement.executeUpdate();

            generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
                long insertID = generatedKeys.getLong(1);
                System.out.println("Insert ID is: " + insertID);
            }

            System.out.println("Uitvoeren van de INSERT query is gelukt.");
        } catch (SQLException e) {
            System.err.println("Uitvoeren van de INSERT query is mislukt.");
            e.printStackTrace();
        } finally {
            SqlUtil.close(generatedKeys);
            SqlUtil.close(preparedStatement);
            SqlUtil.close(connection);
        }
    }

}

Laden van de driver is gelukt.

Insert ID is: 4

Uitvoeren van de INSERT query is gelukt.

Noot: de SqlUtil hoeft niet uitgebreid te worden met een nieuwe close() methode voor de PreparedStatement. Aangezien deze een subklasse is van Statement, wordt de close() methode daarvan gewoon gebruikt.

Terug naar boven

DTO's zijn een must

Het is een zeer goede practice om het verkregen ResultSet te omvertalen naar een lijst met DTO's (Data Transfer Objects). Een DTO moet in dit geval een volledige rij van een database tabel voorstellen. De DTO's zouden de Javabean specificatie moeten volgen: de velden (properties) worden private gemaakt en deze zijn alleen toegankelijk via public getters en setters (de accessors). Dit is overigens het schoolvoorbeeld van encapsulation. Hieronder volgt een voorbeeld van een DTO afspiegeling van de "maintable" tabel:

package test;

public class Maintable {

    // Properties -------------------------------------------------------------------------------

    private Long id;
    private String name;
    private Integer value;

    // Getters ----------------------------------------------------------------------------------

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Integer getValue() {
        return value;
    }

    // Setters ----------------------------------------------------------------------------------

    public void setId(Long id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setValue(Integer value) {
        this.value = value;
    }

    // Helpers ----------------------------------------------------------------------------------

    // Dit is niet verplicht, maar gewoon handig. Het overschrijft de
    // Object#toString() zodat je een mooie String representatie krijgt.
    public String toString() {
        return "[ID=" + id + ",Name=" + name + ",Value=" + value + "]";
    }

}

Het kan handig zijn om wrapper datatype objecten (Long, Integer, Boolean, etc) in plaats van primitieve datatypen (long, int, boolean, etc) voor de properties te gebruiken, met name omdat deze wrapper datatype objecten in tegenstelling tot primitieve datatypen ook null waarden kunnen bevatten, waarmee je zou kunnen aangeven dat het veld nooit is ingevuld. Daarnaast kunnen de "primitieve velden" van de database ook NULL waarden bevatten danwel accepteren die je op geen enkele manier naar een primitieve datatype kunt vertalen. Pas wanneer een database veld strikt als NOT NULL is gespecificeerd, dan kun je daarvoor wel gerust een primitieve datatype gebruiken. Zie verder ook Java Tutorial - Datatypen en Data conversies.

Deze DTO kun je in het voorbeeld van een voorbereide SELECT als volgt toepassen:

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Test {

    public static void main(String[] args) {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Laden van de driver is gelukt.");
        } catch (ClassNotFoundException e) {
            System.err.println("Kan de driver niet in de classpath vinden.");
            e.printStackTrace();
            return;
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String url = "jdbc:mysql://localhost/javabase?user=root&password=d$7hF_r!9Y";
        String preparedSelectQuery = "SELECT id, name, value FROM maintable WHERE name = ?";

        // Voorbereid de DTO lijst. Dit mag trouwens ook een Set zijn, wat je maar wil.
        // Een List is makkelijker sorteerbaar en verwerkbaar.
        List results = new ArrayList();

        try {
            connection = DriverManager.getConnection(url);
            preparedStatement = connection.prepareStatement(preparedSelectQuery);
            preparedStatement.setString(1, "testnaam");
            resultSet = preparedStatement.executeQuery();

            // Verwerk de resultaten in een DTO lijst.
            while (resultSet.next()) {
                Maintable maintable = new Maintable();
                maintable.setId(new Long(resultSet.getLong("id")));
                maintable.setName(resultSet.getString("name"));
                maintable.setValue(new Integer(resultSet.getInt("value")));
                results.add(maintable);
            }

            System.out.println("Uitvoeren van de SELECT query is gelukt.");
            System.out.println("Aantal gevonden resultaten: " + results.size());
        } catch (SQLException e) {
            System.err.println("Uitvoeren van de SELECT query is mislukt.");
            e.printStackTrace();
        } finally {
            SqlUtil.close(resultSet);
            SqlUtil.close(preparedStatement);
            SqlUtil.close(connection);
        }

        // Doorloop de DTO lijst.
        for (Maintable result : results) {
            System.out.println(result);
        }
    }

}

Laden van de driver is gelukt.

Uitvoeren van de SELECT query is gelukt.

Aantal gevonden resultaten: 3

[ID=2,Name=testnaam,Value=10]

[ID=3,Name=testnaam,Value=10]

[ID=4,Name=testnaam,Value=10]

Zo'n lijst met DTO's kun je dan verder gebruiken buiten de database-communicatie-laag van je applicatie.

Terug naar boven

Een universele DAO ontwerpen?

De lappen code hierboven zijn in principe erg basaal en veel ervan is hetzelfde. Het is de kunst om deze lappen code netjes te "refactoren" in aparte klassen en methoden, zodat geen enkel stukje code dubbel voorkomt. Deze klassen zouden dan tezamen dan een Data Access Layer vormen met een generieke DAO (Data Access Object) dat alle queries zou moeten kunnen afhandelen. We gaan hier in dit artikel niet verder op in, maar om een idee te geven staat hieronder een voorbeeld van een SELECT query met behulp van een uitgekiende DAO:

package test;

import net.balusc.dao.DaoException;
import net.balusc.dao.DaoSession;
import net.balusc.dao.DatabaseType;
import net.balusc.dto.DtoList;
import net.balusc.query.LoadQuery;
import net.balusc.testdata.Maintable;

public class Test {

    private static DaoSession daoSession = new DaoSession(DatabaseType.MYSQL);

    static {
        daoSession.setUrl("jdbc:mysql://localhost/javabase");
        daoSession.setUsername("root");
        daoSession.setPassword("d$7hF_r!9Y");
    }

    public static void main(String[] args) {

        Maintable example = new Maintable();
        example.setName("testnaam");
        LoadQuery loadQuery = new LoadQuery(example);
        
        try {
            daoSession.execute(loadQuery);
        } catch (DaoException e) {
            e.printStackTrace();
            return;
        }

        DtoList results = loadQuery.getOutput();

        for (Maintable result : results) {
            System.out.println(result);
        }
    }

}

[INFO] net.balusc.sql.PreparedQuery#executeSelectQuery: SELECT Maintable.ID AS Maintable_ID, Maintable.Name AS Maintable_Name, Maintable.Value AS Maintable_Value FROM Maintable WHERE Maintable.Name = ? [testnaam]

[ID=2,Name=testnaam,Value=10]

[ID=3,Name=testnaam,Value=10]

[ID=4,Name=testnaam,Value=10]

Een wat 'normalere' aanpak van een DAO kun je in deze Engelstalige DAO tutorial lezen: DAO tutorial - the data layer.

Er zijn ook gratis danwel commerciële ORM's (Object Relational Mappers) verkrijgbaar die het vanuit Java werken met databases moet vergemakkelijken. Het bekendste voorbeeld is wel Hibernate. Bij een ORM komt het zo ongeveer erop neer dat je de database objectmatig via diverse hulpklassen kunt benaderen zonder handgeschreven SQL queries. Hibernate heeft helaas echter wel een enorm hoge leercurve, met name het kennis van XML is vereist en het op de juiste wijze configureren van de XML configuratie bestanden kan nogal lastig worden. Maar het is beslist wel de moeite waard.

Terug naar boven

Copyright - Er is geen copyright op de code. Je kunt het naar believen overnemen, aanpassen danwel verspreiden.

(C) Maart 2007, BalusC

Source:http://balusc.blogspot.com/2007/03/databases.html