Tietokantakyselyiden tulosten rajaaminen ja järjestäminen

Tietokantakyselyn tulokset halutaan usein tietynlaisessa järjestyksessä tai niin, että kysely palauttaa vain rajatun joukon kaikista tuloksista. Jos järjestäminen tai rajaus toteutetaan web-sovelluksessa (eli ei tietokannassa), sovelluksessa tehdään juuri se työ, mihin tietokannat on tarkoitettu. Lisäksi, jos tietokannan tieto noudetaan sovellukseen järjestämistä tai rajausta varten, käytetään tiedon kopiointiin paikasta toiseen turhaan aikaa ja resursseja.

Kriteerien lisääminen tietokantakyselyihin

Tietokantakyselyn tulokset halutaan usein hakea tietyn kriteerin mukaan — esimerkiksi pankkijärjestelmässämme käyttäjä saattaisi haluta hakea konttoreita osoitteen perusteella. Tarkastellaan tässä miten tietokantakyselyihin voi lisätä ehtoja.

Kyselyiden muokkaaminen tapahtuu lisäämällä uusia metodeja rajapinnan JpaRepository perivään luokkaan. Voimme lisätä metodeja, jotka hakevat arvoja tietyn attribuutin tai attribuuttiyhdistelmän perusteella. Tällaiset metodit kirjoitetaan muodossa findBy*Attribuutti*(*AttribuutinTyyppi* arvo). Esimerkiksi konttorin hakeminen osoitteen perusteella toteutetaan seuraavalla tavalla.

public interface KonttoriRepository extends JpaRepository<Konttori, Long> {

    List<Konttori> findByOsoite(String osoite);
}

Spring Data JPA luo yllä olevasta metodista automaattisesti muotoa SELECT * FROM Konttori WHERE osoite = '?' olevan kyselyn, johon parametrina annettava merkkijono osoite asetetaan.

Vastaavasti tietyn pankin tietyssä osoitteessa oleva konttori haetaan seuraavalla tavalla.

public interface KonttoriRepository extends JpaRepository<Konttori, Long> {

    List<Konttori> findByOsoite(String osoite);
    Konttori findByOsoiteAndPankki(String osoite, Pankki pankki);
}

Metodi findByOsoiteAndPankki muuntuu muotoon SELECT * FROM Konttori WHERE osoite = '?' AND pankki_id = '?' — parametrit asetetaan metodikutsun yhteydessä annetuista arvoista.

Tietokantakyselyiden tulosten rajaaminen ja järjestäminen

Alla olevalla PageRequest-oliolla ilmoitamme haluavamme ensimmäiset 10 hakutulosta attribuutin nimi mukaan käänteisessä järjestyksessä.

Pageable pageable = PageRequest.of(0, 10, Sort.by("nimi").descending());

Pageable-olion voi antaa parametrina suurelle osalle tietokantarajapinnan valmiista metodeista. Esimerkiksi listattavien pankkien rajaus findAll-metodin kautta näyttäisi seuraavalta.

@Controller
public class PankkiController {

    @Autowired
    private PankkiRepository pankkiRepository;

    // ...

    @GetMapping("/pankit")
    public String list(Model model) {
        Pageable pageable = PageRequest.of(0, 10, Sort.by("nimi").descending());

        model.addAttribute("pankit", pankkiRepository.findAll(pageable));
        return "pankit";
    }
    // ...

}

Yllä olevassa luokan PankkiController metodissa list tehdään tietokantakysely, joka hakee tietokannasta ensimmäiset 10 pankkia nimen perusteella järjestettynä. Mikäli haluaisimme seuraavat kymmenen pankkia, muokkaisimme PageRequest.of-pyynnön ensimmäistä parametria.

Pageable pageable = PageRequest.of(0, 10, Sort.by("nimi").descending());

Ensimmäinen parametri kertoo "sivun" numeron, toinen sivulla olevien tulosten lukumäärän, ja kolmas määrittelee miten tulokset tulee järjestää. Tätä voisi käyttää myös tulosten rajattuun tarkasteluun — voisimme esimerkiksi määritellä metodille pyyntöparametrin sivu, joka määrittelee mitä sivua tarkastellaan.

@GetMapping("/pankit")
public String list(Model model, @RequestParam(defaultValue = "0") Integer sivu) {
    Pageable pageable = PageRequest.of(sivu, 10, Sort.by("nimi").descending());

    model.addAttribute("pankit", pankkiRepository.findAll(pageable));
    return "pankit";
}

Nyt tehdessämme pyynnön polkuun /pankit näemme ensimmäiset 10 pankkia. Pyyntö polkuun /pankit?sivu=1 näyttäisi seuraavat 10 pankkia, jne.

Tässä on oleellista huomata että tulosten hakeminen ja rajaaminen tapahtuu tietokannassa. Tämän johdosta sovellus toimii paljon tehokkaammin jos toteutustapaa vertaa naiiviin lähestymistapaan, missä sovellukseen haetaan kaikki tiedot tietokannasta ja tiedon järjestäminen ja rajaaminen tapahtuu vasta sovelluksessa.