Erilaiset näkymät tietoon

Edellisessä osassa tietoa tarkasteltiin sen tallennustavan kautta. Käsittelimme menetelmiä tallentaa ja lukea tietoa tiedostosta. Tietokannanhallintajärjestelmän toteutuksen ja tarjoaman käsittelyn näkökulmasta esimerkkimme liittyivät fyysiseen toteutukseen, eli miten ja missä muodossa tieto lopulta tallennetaan.

Tietokannanhallintajärjestelmistä ja tallennettavasta tiedosta puhuttaessa tietoa käsitellään tyypillisesti kolmella eri abstraktiotasolla: käsitteellisellä abstraktiotasolla, rakenteellisella abstraktiotasolla ja fyysisellä abstraktiotasolla.

Käsitteellinen abstraktiotaso (conceptual level) on kuvaus tietokantaan tallennettavista käsitteistä ja niiden yhteyksistä. Käsitteellisellä abstraktiotasolla kuvaus esiintyy esimerkiksi tekstidokumenteissa, puheissa, tai vaikkapa multimediaesityksissä.

Pankin tallennustoiminnallisuudesta kertova asiakas saattaa kertoa tileistä ja niiden omistajista seuraavaa: Tileillä on omistajia, joista jokainen yksilöidään henkilöturvatunnuksen avulla. Em. kuvaus sisältää käsitteet tili, omistaja ja henkilöturvatunnus. Tämän lisäksi käsitteisiin liittyy suhteita, jotka osittain ilmenevät kuvaksesta ja osittain vaativat aihealueen ymmärrystä. Esimerkiksi tili ja omistaja ovat erillisiä käsitteitä ja niiden välinen yhteys kuvaa omistajuutta. Omistajan ja henkilöturvatunnuksen välinen yhteys on taas riippuvainen olemassaolosta -- Omistajan henkilöturvatunnusta ei ole olemassa ilman omistajaa. Omistajalla on henkilöturvatunnus.

Käsitteiden nimeäminen kuvaavammiksi on myös mahdollista. Edeltävässä kuvauksessa ollut käsite omistaja kannattaisi todennäköisesti nimetä uudelleen henkilöksi.

Rakenteellinen abstraktiotaso (logical level, structural level) on kuvaus tietokantaan tallennettavan tiedon rakenteesta. Se voi olla esimerkiksi luokkakaavio, tietokantakaavio (palaamme näihin myöhemmin kurssilla) tai SQL-kielellä tehdyt tietokantataulujen luomislauseet.

Edeltävän esimerkin perusteella voisi puhua kahdesta taulusta: Taulu Tili ja Taulu Henkilo. Henkilöä kuvaavaan tauluun kuuluisi myös henkilöturvatunnus, jolloin taulu merkittäisiin esimerkiksi muodossa Taulu Henkilo(henkiloturvatunnus). Tämän lisäksi henkilön ja tilin suhdetta kuvattaisiin jollain tavalla -- esimerkiksi "Henkilöllä on yksi tai useampi tili".

Fyysinen abstraktiotaso (physical level, internal level) kuvaa konkreettista tiedon tallentamistapaa kiintolevylle. Tämä sisältää tiedon tietokantaa kuvaavan tai kuvaavien tiedostojen sijainnista, käytettävistä tietorakenteista, ymym. Fyysinen abstraktiotaso on tyypillisesti järjestelmäkohtainen ja riippuu myös ainakin osittain tallennettavan tiedon muodosta.

Structured Query Language (SQL)

Materiaalin ensimmäisen osan esimerkeissä tarkasteltiin tietokoneen kiintolevyllä sijaitsevan tiedon käsittelyä ohjelmallisesti -- käytännössä esimerkki käsitteli fyysistä näkymää tiedon tallennukseen. Esimerkeissä määritettiin tiedon rakenne sekä muuttujien maksimipituus: jos muuttujan todellinen arvo ei vastaa maksimipituutta, tyhjä tila täytetään välilyönneillä. Tällä tavoin ohjelmoija voi olettaa, että tiettyyn muuttujaan liittyvä arvo alkaa aina samasta kohtaa. Laajemmin ajatellen, ohjelmoija tietää myös, että samaisen muuttujan arvo on samassa kohdassa jokaiselle tallennetulle tietueelle (tai oliolle).

Ensimmäisen viikon esimerkeissä tiedon käsittelijän tai tiedon hakijan tulee tuntea käsite indeksi sekä osata hyödyntää indeksiä tiedon hakemisessa. Samalla, jos tiedon hakija haluaa vaikkapa useamman muuttujan arvon, tulee ohjelmaa sekä siinä käytettyjä indeksejä päivittää sopivasti.

Kun tietokantoja käytetään osana jokapäiväistä työtä, edellä kuvattu tiedon käsittely ei ole kovin mielekästä. Se vaatii käytännössä ohjelmointiosaamista sekä ymmärrystä tallennetusta tiedosta ja tiedon fyysisestä esitysmuodosta. Tiedon fyysinen esitysmuoto vaihtelee tallennettavan tiedon mukaan, joten tiedon käsittelyyn tarvitaan parempi ratkaisu. Tätä ongelmaa ja työläyttä ratkaisemaan on luotu useampia korkeamman abstraktiotason esitystapoja, joita käytetään tietokannassa olevan tiedon hakemiseen ja tiedon muokkaamiseen.

Tällä kurssilla keskitytään Structured Query Language (SQL) -kieleen. Structured Query Language (jatkossa SQL) on 1980-luvulla standardoitu kieli tietokantakyselyiden tekemiseen. SQL-kielen avulla voidaan määritellä tallennettavan tiedon muoto, luoda ja muokata tietokantatauluja, lisätä tietoa tietokantatauluihin, muokata tietokantatauluissa olevaa tietoa sekä hakea tietoa tietokannoista. Merkittävä osa tällä hetkellä käytössä olevista tietokannanhallintajärjestelmistä mahdollistaa SQL-kielellä tehtyjen kyselyiden käyttämisen tietokannanhallintajärjestelmässä olevien tietokantojen ja tietokantataulujen käsittelyyn. Voidaan ajatella, että SQL-kielellä tehdyt kyselyt ovat kuvattu käsitteellisellä abstraktiotasolla, eli SQL-kieltä käytettäessä tiedon lopulliseen esitysmuotoon kiintolevyllä ei oteta kantaa.

Vuosien mittaan standardista on julkaistu useita versioita, joista viimeisin on vuodelta 2016. Tietokannanhallintajärjestelmät ja niiden eri versiot noudattavat SQL-kielen standardeja vaihtelevasti. Yhtä tietokannanhallintajärjestelmää varten luodut kyselyt eivät ole aina suoraan siirrettävissä toiseen tietokannanhallintajärjestelmään. On siis syytä huomioida että tietokannanhallintajärjestelmästä toiseen siirryttäessä joudutaan usein myös tekemään SQL-kyselyihin (pieniä) muutoksia. Tyypillisimpiä tietotyyppejä, joiden käsittelytapa vaihtelee eri tietokannanhallintajärjestelmien välillä ovat päivämäärät.

Kurssin toisessa osassa tutustutaan yhden tietokantataulun käsittelyyn SQL-kielellä. Opettelemme luomaan tietokantataulun, lisäämään tietokantatauluun tietoa, hakemaan tietokantataulusta tietoa sekä päivittämään ja poistamaan tietokantataulussa olevaa tietoa.

Käytämme tässä osassa SQLite-nimistä tietokannanhallintajärjestelmää.

kayttaja@kone:~/kansio/$ sqlite3 tietokanta.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> SELECT 1;
1
sqlite>

Tietokantataulun luominen

Tietokantataulu luodaan SQL-kielen CREATE TABLE lauseella, jota seuraa luotavan taulun nimi, ja suluissa tietokantataulun sarakkeiden tiedot pilkulla eroteltuna.

CREATE TABLE TAULUN_NIMI (
    sarakkeen 1 tiedot,
    sarakkeen 2 tiedot,
    sarakkeen 3 tiedot
)

Tietotyypit

Tietokantaan säilöttävä tieto voi olla montaa eri muotoa, esimerkiksi merkkijonoja, numeroita, binäärimuodossa olevia tiedostoja sekä päivämääriä. Tietokannan suunnittelijan tehtävänä on päättää kunkin sarakkeen tiedon tyyppi.

Sarakkeen tiedon tyyppi määrää minkämuotoista tietoa sarakkeen arvoksi voi tallentaa. Käytännössä tietokantataulua luotaessa sarakkeen määrittelyssä annettavat tiedot kertovat tietokannanhallintajärjestelmälle siitä, että minkälaista tietoa sarakkeeseen voidaan lisätä, ja toisaalta samalla minkälaista tietoa sarakkeeseen ei voida lisätä. Sarakkeen tyyppi vaikuttaa myös asioihin, joita sarakkeen arvoilla voi tehdä -- esimerkiksi keskiarvon laskeminen merkkijonotyyppisiä arvoja sisältävästä sarakkeesta ei ole kovin järkevää.

Tyypillisesti käytetyn tietotyypit ovat seuraavat:

• varchar(n) korkeintaan n merkin pituinen merkkijono.
• integer kokonaisluku
• float liukuluku eli desimaaliluku
• date päivämäärä, tallentaa vuoden, kuukauden ja päivän
• timestamp aikaleima, tallentaa vuoden, kuukauden, päivän, tunnit, minuutit ja sekunnit -- mahdollisesti myös tarkempia arvoja

Sarakkeen tietojen määrittely

Jokaisesta sarakkeesta kerrotaan sarakkeen nimi, sarakkeeseen tulevan tiedon tyyppi sekä tarvittaessa tietotyypille varattavan tilan koko -- esimerkiksi merkkijonoja tallennettaessa kerrotaan tallennettavan merkkijonon maksimipituus. Jokaisella sarakkeella tulee olla nimi sekä tyyppi. Jos sarakkeen tyyppiä ei määritellä tietokantataulua luotaessa, jotkut tietokannanhallintajärjestelmät määrittelevät sen automaattisesti, toiset taas näyttävät virheilmoituksen.

Sarakkeen tiedot ovat muotoa sarakkeen_nimi sarakkeen_tietotyyppi. Esimerkiksi syntymävuosi määritellään seuraavasti.

syntymavuosi integer

Korkeintaan 200 merkkiä pitkä nimi määriteltäisiin taas seuraavasti.

nimi varchar(200)

Henkilön syntymävuoden ja nimen tallentamiseen tarkoitettu taulu määriteltäisiin seuraavasti. Alla on oletettu, että nimi ei ole koskaan yli 200 merkkiä pitkä.

CREATE TABLE Henkilo (
    syntymavuosi integer,
    nimi varchar(200)
)

Yllä luodun esimerkkitaulun sisältö voisi olla esimerkiksi seuraavanlainen.

Tietokantataulun poistaminen

Tietokantataulun poistaminen onnistuu DROP TABLE TAULUN_NIMI -lauseella, missä taulun nimi on poistettavan taulun nimi. Esimerkiksi edellä luodun Henkilo-taulun poistaminen onnistuisi seuraavasti.

DROP TABLE Henkilo

Huomaathan, että tietokantataulun poistaminen poistaa myös kaiken tietokantataulussa olevan datan.

SELECT * FROM sqlite_master

PRAGMA TABLE_INFO(Henkilo)

Tiedon lisääminen tietokantatauluun

Tiedon lisääminen tietokantatauluun tapahtuu INSERT INTO -lauseella. Lausetta INSERT INTO seuraa kohdetaulun nimi, jonka jälkeen määritellään sarakkeet, joihin arvot asetetaan. Näitä seuraa vielä konreettiset arvot.

INSERT INTO TAULUN_NIMI (sarake1, sarake2, sarake3)
    VALUES ('merkkijono hipsuissa','numero ei', 123)

Oletetaan, että käytössämme on edellisessä aliluvussa luotu taulu Henkilo, jossa on sarakkeet syntymavuosi ja nimi. Uuden henkilon lisääminen tapahtuu seuraavasti.

INSERT INTO Henkilo (syntymavuosi, nimi)
    VALUES (1923, 'Edgar Frank Codd')

Yllä olevassa esimerkissä tietokantatauluun Henkilo lisätään uusi rivi. Sarakkeeseen syntymävuosi tulee arvo 1923 ja sarakkeeseen nimi tulee merkkijono 'Edgar Frank Codd'.

Vastaavasti vuonna 1947 syntyneen Raymond Boycen lisääminen tietokantatauluun Henkilo onnistuu seuraavasti.

INSERT INTO Henkilo (syntymavuosi, nimi)
    VALUES (1947, 'Raymond Boyce')

Tiedon hakeminen tietokantataulusta

Tiedon hakeminen tietokantataulusta onnistuu SELECT-lauseella. Avainsanaa SELECT seuraa haettavat sarakkeet, tietokantataulun nimi sekä mahdollisesti rajausehto tai rajausehtoja.

Ilman rajausehtoa kyselyn rakenne on seuraava.

SELECT sarake1, sarake2 FROM TAULUN_NIMI

Rajausehdon kanssa kyselyn rakenne on seuraava.

SELECT sarake1, sarake2 FROM TAULUN_NIMI
    WHERE rajausehto

Oletetaan, että käytössämme on seuraava taulu, jonka nimi on Henkilo.

Jos haluamme listata kaikki taulun henkilöt, kysely olisi muotoa.

SELECT syntymavuosi, nimi FROM Henkilo

Valinta henkilöihin, jotka ovat syntyneet ennen vuotta 1950 onnistuu seuraavasti.

SELECT syntymavuosi, nimi FROM Henkilo WHERE syntymavuosi < 1950

SELECT syntymavuosi, nimi FROM Henkilo WHERE syntymavuosi < 1950

SELECT * FROM Henkilo WHERE syntymavuosi < 1950

Jos sarakkeen arvot ovat merkkijonoja, kuten nimi, voi hakuehdossa käyttää LIKE-operaatiota. Tämän avulla hakutuloksia voi rajata osittaisen merkkijonon avulla. Esimerkiksi kysely SELECT * FROM Henkilo WHERE nimi LIKE '%a%' hakee kaikki henkilöt, joiden nimessä esiintyy a-kirjain.

Tiedon päivittäminen

Tietokantataulussa olevan tiedon päivittäminen onnistuu UPDATE-lauseella. Komentoa UPDATE seuraa tietokantataulun nimi, avainsana SET, jota seuraa sarakekohtaiset uudet arvot. Lopuksi kyselyyn lisätään rajausehto, jonka perusteella rajataan muutettavia rivejä.

UPDATE TAULUN_NIMI
    SET sarake1='uusiarvo', sarake2=1234
    WHERE sarake3='rajausarvo'

Esimerkiksi 'Joni'-nimisen henkilön nimen päivittäminen muotoon 'Joni S' onnistuu seuraavasti.

UPDATE Henkilo
    SET nimi='Joni S'
    WHERE nimi='Joni'

Tiedon poistaminen tietokantataulusta

Tiedon poistaminen tietokantataulusta onnistuu DELETE FROM -lauseella. Lauseeseen määritellään lisäksi tietokantataulu, mistä tietoa poistetaan, ja mahdollisesti ehtoja, jotka rajaavat poistettavia rivejä. Yksinkertaisimmillaan komennolla poistetaan kaikki rivit annetusta taulusta seuraavasti.

DELETE FROM TAULUN_NIMI

Poistettavien rivien rajaaminen tapahtuu WHERE-ehdolla, jota seuraa poistettavien arvojen rajaus. Esimerkiksi kaikki vuonna 1920 syntyneet henkilöt poistettaisiin tietokantataulusta Henkilo seuraavalla komennolla.

DELETE FROM Henkilo WHERE syntymavuosi = 1920

Poistoehtoon voi rakentaa loogisen lauseen, joka sisältää AND ja OR -määreitä. Näiden avulla poistorajausta voi tehdä laajemmin. Alla olevassa esimerkissä poistetaan henkilö (tai henkilöt), joiden syntymävuosi on 1947 ja joiden nimi on 'Raymond Boyce'.

DELETE FROM Henkilo WHERE syntymavuosi = 1947 AND nimi = 'Raymond Boyce'

Pienempi kuin ja suurempi kuin -vertailuoperaatiot sekä erisuuri kuin vertailuoperaatiot ovat myös mahdollisia. Alla olevassa esimerkissä poistetaan kaikki henkilöt, joiden syntymävuosi on pienempi kuin 2000 ja joiden nimi ei ole 'Boyce-Codd'.

DELETE FROM Henkilo WHERE syntymavuosi < 2000 AND nimi != 'Boyce-Codd'

Yhteenveto

CREATE TABLE Opiskelija (
    opiskelijanumero integer,
    nimi varchar(60),
    sahkopostiosoite varchar(40)
)

DROP TABLE Opiskelija

INSERT INTO
    Opiskelija (opiskelijanumero, nimi, sahkopostiosoite)
    VALUES (1008286, 'Ari', 'posti@osoite.net');

SELECT nimi FROM Opiskelija

UPDATE Opiskelija
    SET nimi='Ari V'
    WHERE opiskelijanumero=1008286

DELETE FROM Opiskelija
    WHERE opiskelijanumero=1008286

Ohutlevyseppien mediaaniansio: 0
Työtehtäviä, joiden mediaaniansio on alle 1900 euroa: 0
Työtehtäviä, joiden mediaaniansio on yli 7000 euroa: 0
Työtehtävä, jonka mediaaniansio on tasan 3300 euroa kuussa: ???

Tietokannan käsittely ohjelmallisesti

Lähes jokainen ohjelmointikieli tarjoaa jonkinlaisen rajapinnan tietokantakyselyiden tekemiseen. Nämä rajapinnat suoraviivaistavat kyselyiden tekemistä tietokantoihin ja tietokannanhallintajärjestelmien käyttöönoottoa, sillä rajapintaa noudattamalla yhteydenotto tietokannantallintajärjestelmään on lähes samankaltaista käytetystä tietokannanhallintajärjestelmästä riippumatta.

Java-kielessä tähän tehtävään on Java Database Connectivity (JDBC) -rajapinta. JDBC tarjoaa tuen tietokantayhteyden luomiseen sekä kyselyiden suorittamiseen tietokantayhteyden yli. Jotta JDBCn avulla voidaan ottaa yhteys tietokantaan, tulee käytössä olla tietokannanhallintajärjestelmäkohtainen ajuri, jonka vastuulla on tietokantayhteyden luomiseen liittyvät yksityiskohdat sekä tietokannanhallintajärjestelmän sisäisten kyselytulosten muuntaminen JDBC-rajapinnan mukaiseen muotoon.

Ohjelmallinen tietokantakysely kokonaisuudessaan

Oletetaan, että käytössämme on seuraava tietokantataulu Opiskelija:

JDBCn avulla kyselyn tekeminen tietokantatauluun tapahtuu seuraavasti -- olettaen, että käytössämme on sekä tietokanta, että tietokannan ajuri.

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

public class Main {
    public static void main(String[] args) throws Exception {
        // luodaan yhteys jdbc:n yli sqlite-tietokantaan nimeltä "tietokanta.db"
        Connection connection = DriverManager.getConnection("jdbc:sqlite:tietokanta.db");

        // luodaan kyely "SELECT * FROM Opiskelija", jolla haetaan
        // kaikki tiedot Opiskelija-taulusta
        PreparedStatement statement = connection.prepareStatement("SELECT * FROM Opiskelija");

        // suoritetaan kysely -- tuloksena resultSet-olio
        ResultSet resultSet = statement.executeQuery();

        // käydään tuloksena saadussa oliossa olevat rivit läpi -- next-komento hakee
        // aina seuraavan rivin, ja palauttaa true jos rivi löytyi
        while(resultSet.next()) {
            // haetaan nykyiseltä riviltä opiskelijanumero int-muodossa
            Integer opNro = resultSet.getInt("opiskelijanumero");
            // haetaan nykyiseltä riviltä nimi String-muodossa
            String nimi = resultSet.getString("nimi");
            // haetaan nykyiseltä riviltä syntymävuosi int-muodossa
            Integer syntVuosi = resultSet.getInt("syntymävuosi");
            // haetaan nykyiseltä riviltä pääaine String-muodossa
            String paaAine = resultSet.getString("pääaine");

            // tulostetaan tiedot
            System.out.println(opNro + "\t" + nimi + "\t" + syntVuosi + "\t" + paaAine);
        }

        // suljetaan lopulta yhteys tietokantaan
        connection.close();
    }
}

Ohjelman suoritus tuottaa (esimerkiksi) seuraavanlaisen tulostuksen:

999999	Pihla	1997	Tietojenkäsittelytiede
999998	Joni	1993	Tietojenkäsittelytiede
999997	Anna	1991	Matematiikka
999996	Krista	1990	Tietojenkäsittelytiede
...

Ohjelman rakentaminen osissa

Tässä oletetaan, että projektiin on lisätty tarvittava JDBC-ajuri.

Avaa projektiin liittyvä Source Packages ja valitse (tai tarvittaessa luo) sopiva pakkaus. Oletetaan tässä, että käytössä on pakkaus tikape. Valitse tämän jälkeen New -> Java Class, jonka jälkeen avautuu valikko, missä voit antaa luokalle nimen. Anna luokan nimeksi Main.

Avaa tiedosto tuplaklikkaamalla sitä. Muokkaa tiedostoa vielä siten, että se on seuraavan näköinen:

package tikape;

public class Main {

    public static void main(String[] args) throws Exception {

    }
}

Tietokantayhteyden luominen

Lisää projektiin import-komento import java.sql.*;, joka hakee kaikki SQL-kyselyihin liittyvät Javan kirjastot.

package tikape;

import java.sql.*;

public class Main {

    public static void main(String[] args) throws Exception {

    }
}

Avataan seuraavaksi tietokantayhteys tietokantatiedostoon nimeltä testi.db ja tehdään kysely "SELECT 1", jolla pyydetään tietokantaa palauttamaan luku 1 -- käytämme tätä yhteyden testaamiseksi. Jos yhteyden luominen onnistuu, tulostetaan "Hei tietokantamaailma!", muulloin "Yhteyden muodostaminen epäonnistui".

package tikape;

import java.sql.*;

public class Main {

    public static void main(String[] args) throws Exception {
        Connection connection = DriverManager.getConnection("jdbc:sqlite:testi.db");

        PreparedStatement statement = connection.prepareStatement("SELECT 1");

        ResultSet resultSet = statement.executeQuery();

        if(resultSet.next()) {
            System.out.println("Hei tietokantamaailma!");
        } else {
            System.out.println("Yhteyden muodostaminen epäonnistui.");
        }
    }
}

Hei tietokantamaailma!

Kun suoritamme ohjelman ensimmäistä kertaa valitsemalla Run -> Run Project, puuttuvan tietokannan paikalle luodaan tietokanta (ainakin SQLiteä käyttäessä). Projektin kansiossa on nyt tiedosto testi.db, joka on tietokantamme.

Tietokantakyselyiden tekeminen

Osoitteessa vuokraamo.db löytyy kuvitteellisen moottoripyörävuokraamon tietokanta. Lataa se edellä tehdyn projektin juureen ja kokeile kyselyn tekemistä kyseiseen tietokantaan.

Tietokannassa on tietokantataulu Pyora, jolla on sarakkeet rekisterinumero ja merkki. Jokaisen pyörän rekisterinumeron ja merkin tulostaminen tapahtuu seuraavasti -- huomaa myös, että olemme vaihtaneet käytössä olevaa tietokantaa.

Connection connection = DriverManager.getConnection("jdbc:sqlite:vuokraamo.db");

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM Pyora");
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
    String rekisterinumero = rs.getString("rekisterinumero");
    String merkki = rs.getString("merkki");

    System.out.println(rekisterinumero + " " + merkki);
}

stmt.close();
rs.close();

connection.close();

Käydään ylläoleva ohjelmakoodi läpi askeleittain.

1. Luomme ensin JDBC-yhteyden tietokantaan vuokraamo.db.

   Connection connection = DriverManager.getConnection("jdbc:sqlite:vuokraamo.db");
   

2. Kysely luodaan antamalla yhteydelle merkkijono, jossa on kysely. Yhteys palauttaa PreparedStatement-olion, jota käytetään kyselyn suorittamiseen ja tulosten pyytämiseen. Metodi executeQuery suorittaa SQL-kyselyn ja palauttaa tulokset sisältävän ResultSet-olion.

   PreparedStatement statement = connection.prepareStatement("SELECT * FROM Pyora");
   ResultSet resultSet = statement.executeQuery();
   

3. Tämän jälkeen ResultSet-oliossa olevat tulokset käydään läpi. Metodia next() kutsumalla siirrytään kyselyn palauttamissa tulosriveissä eteenpäin. Kultakin riviltä voi kysyä sarakeotsikon perusteella solun arvoa. Esimerkiksi kutsu getString("rekisterinumero") palauttaa kyseisellä rivillä olevan sarakkeen "rekisterinumero" arvon String-tyyppisenä.

   while(resultSet.next()) {
       String rekisterinumero = rs.getString("rekisterinumero");
       String merkki = rs.getString("merkki");
   
       System.out.println(rekisterinumero + " " + merkki);
   }
   

4. Kun kyselyn vastauksena saadut rivit on käyty läpi, eikä niitä enää tarvita, vapautetaan niihin liittyvät resurssit.

   stmt.close();
   rs.close();
   

5. Lopulta tietokantayhteys suljetaan.

   connection.close();
   

Parametrien lisääminen kyselyyn

Kyselyihin halutaan usein antaa rajausehtoja. Ohjelmallisesti tämä tapahtuu lisäämällä kyselyä muodostaessa rajausehtoihin kohtia, joihin asetetaan arvot. Alla olevassa esimerkissä kyselyyn lisätään merkkijono.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Pyora WHERE merkki = ?");
statement.setString(1, "Royal Enfield");

ResultSet resultSet = statement.executeQuery();

Kyselyiden paikat indeksoidaan kohdasta 1 alkaen. Alla olevassa esimerkissä haetaan Henkilo-taulusta henkilöitä, joiden syntymävuosi on 1952.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Henkilo WHERE syntymavuosi  = ?");
statement.setInt(1, 1952);

ResultSet resultSet = statement.executeQuery();

Ohjelma voi toimia myös siten, että rajausehdot kysytään ohjelman käyttäjältä.

Scanner lukija = new Scanner(System.in);
System.out.println("Minkä vuoden opiskelijat tulostetaan?");
int vuosi = Integer.parseInt(lukija.nextLine());

// ...

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Henkilo WHERE syntymavuosi  = ?");
statement.setInt(1, vuosi);

ResultSet resultSet = statement.executeQuery();

// ...

Päivityskyselyiden tekeminen

Myös päivityskyselyiden kuten rivien lisäämisten ja rivien poistamisten tekeminen onnistuu ohjelmallisesti. Tällöin tuloksessa ei ole erillistä ResultSet-oliota, vaan luku, joka kertoo muuttuneiden rivien määrän. Allaoleva ohjelmakoodi lisää pyöriä sisältävään tietokantaan uuden pyörän.

Connection connection = DriverManager.getConnection("jdbc:sqlite:vuokraamo.db");

PreparedStatement stmt = connection.prepareStatement("INSERT INTO Pyora (rekisterinumero, merkki) VALUES (?, ?)");
stmt.setString(1, "RIP-34");
stmt.setString(2, "Jopo");

int changes = stmt.executeUpdate();

System.out.println("Kyselyn vaikuttamia rivejä: " + changes);
stmt.close();

connection.close();

SQL-injektiot

Suurin osa olemassaolevista sovelluksista käyttää tietokannanhallintajärjestelmiä jollain tavalla: tietoa haetaan tietokannasta, tietokannassa olevaa tietoa muokataan, ja tietokantaan tallennetaan tietoa. Tyypillisesti sovelluksiin on myös käyttöliittymä, minkä kautta sovelluksen käyttäjät pääsevät vaikuttamaan kyselyiden sisältöön.

SQL-injektioiden tekeminen onnistuu jos ohjelmoija jättää tietokantaa käsitteleviin kyselyihin ns. käyttäjän mentävän aukon. Tämä onnistuu Javalla siten, että kyselyt luodaan niin, että kyselyihin lisättävät parametrit syötetään kyselyyn suoraan merkkijonona.

Alla on esimerkki, missä käyttäjältä kysytään haettavan pyörän merkkiä.

Scanner lukija = new Scanner(System.in);
System.out.println("Minkä merkkiset pyörät tulostetaan?");
String merkki = lukija.nextLine();

// ...

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Pyora WHERE merkki = ?");
statement.setString(1, merkki);

// ...

ResultSet resultSet = statement.executeQuery();

Kysely on turvallinen, sillä merkki asetetaan metodin setString avulla. Metodi tarkastaa myös, ettei kyselyssä ole ylimääräistä sisältöä.

Kyselystä saa helposti erittäin turvattoman. Seuraavassa esimerkissä on mahdollisuus SQL-injektioon.

Scanner lukija = new Scanner(System.in);
System.out.println("Minkä merkkiset pyörät tulostetaan?");
String merkki = lukija.nextLine();

// ...

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Pyora WHERE merkki = '" + merkki + "'");

// ...

ResultSet resultSet = statement.executeQuery();

Kun käyttäjän syöttämä merkkijono lisätään suoraan osaksi kyselyä, voi käyttäjä syöttää SQL-lauseita komentoonsa. Jos käyttäjä syöttää ohjelmaan esimerkiksi merkkijono a' OR 'a'='a, on suoritettava SQL-lause lopulta muotoa:

SELECT * FROM Pyora WHERE merkki = 'a' OR 'a'='a'

Edellinen lause on aina totta, sillä tarkastus 'a'='a' on totta.

Myös muunlaisten SQL-lauseiden suoritus olisi em. tapauksessa mahdollista. Web-sarjakuva xkcd kuvastaa tätä ilmiötä hyvin Exploits of a Mom-sarjakuvallaan.

Tervetuloa!
Komennot:
1: Listaa kappaleet
2: Lisää kappale
3: Sulje
    
> 1
(ei kappaleita)

> 2
Nimi: The Days you Didn't Notice
Artisti: Rendezvous Park
Levytysvuosi: 2010
Pituus: 605

> 2
Nimi: Closer To Being Here - Reprise
Artisti: Rendezvous Park
Levytysvuosi: 2012
Pituus: 363

> 1
Rendezvous Park, Closer to Being Here - Reprise (363 s), 2012
Rendezvous Park, The Days you DIdn't Notice (605 s), 2010

> 3

Tervetuloa!
Komennot:
1: Listaa kappaleet
2: Lisää kappale
3: Sulje
    
> 1
Rendezvous Park, Closer to Being Here - Reprise (363 s), 2012
Rendezvous Park, The Days you DIdn't Notice (605 s), 2010

> 3