En guide til sql2o JDBC Wrapper

1. Introduktion

I denne vejledning skal vi se på Sql2o, et lille og hurtigt bibliotek til relationel databaseadgang i idiomatisk Java.

Det er værd at nævne, at selvom Sql2o fungerer ved at kortlægge forespørgselsresultater til POJO'er (almindelige gamle Java-objekter), det er ikke en komplet ORM-løsning som dvale.

2. Sql2o opsætning

Sql2o er en enkelt jar-fil, som vi nemt kan tilføje til vores projekts afhængigheder:

 org.sql2o sql2o 1.6.0 

Vi bruger også HSQL, den integrerede database, i vores eksempler; For at følge med kan vi også inkludere det:

 org.hsqldb hsqldb 2.4.0 test 

Maven Central er vært for den nyeste version af sql2o og HSQLDB.

3. Oprette forbindelse til databasen

For at oprette en forbindelse starter vi fra en forekomst af Sql2o klasse:

Sql2o sql2o = ny Sql2o ("jdbc: hsqldb: mem: testDB", "sa", "");

Her specificerer vi forbindelses-URL'en, brugernavnet og adgangskoden som konstruktørparametre.

Det Sql2o objekt er trådsikkert, og vi kan dele det på tværs af applikationen.

3.1. Brug af en Datakilde

I de fleste applikationer vil vi gerne bruge en Datakildei stedet for en rå DriverManager forbindelse, måske for at udnytte en forbindelsespulje eller for at specificere yderligere forbindelsesparametre. Bekymre dig ikke, Sql2o har fået os dækket:

Sql2o sql2o = ny Sql2o (datakilde);

3.2. Arbejde med forbindelser

Blot instantiating a Sql2o objekt opretter ingen forbindelse til databasen.

I stedet, vi bruger åben metode til at få en Forbindelse objekt (bemærk, at det ikke er en JDBC Forbindelse). Siden Forbindelse er Kan lukkes automatisk, vi kan pakke det ind i en prøve-med-ressource-blok:

prøv (Forbindelsesforbindelse = sql2o.open ()) {// brug forbindelsen}

4. Indsæt og opdater erklæringer

Lad os nu oprette en database og lægge nogle data i den. Gennem hele tutorialen bruger vi en simpel tabel kaldet projekt:

connection.createQuery ("opret tabelprojekt" + "(id-helhedsidentitet, navn varchar (50), url varchar (100))"). executeUpdate ();

executeUpdate returnerer Forbindelse modstand, så vi kan kæde flere opkald. Derefter, hvis vi vil vide antallet af berørte rækker, bruger vi getResult:

assertEquals (0, connection.getResult ());

Vi anvender det mønster, som vi lige har set - createQuery og executeUpdate -for alle DDL-, INSERT- og UPDATE-udsagn.

4.1. Sådan genereres nøgleværdier

I nogle tilfælde er vi vil muligvis få genererede nøgleværdier tilbage. Det er værdierne for nøglekolonner, der automatisk beregnes (som når du bruger automatisk stigning i bestemte databaser).

Vi gør det i to trin. Først med en ekstra parameter til createQuery:

Query query = connection.createQuery ("indsæt i projekt (navn, url)" + "værdier ('tutorials', 'github.com/eugenp/tutorials')", true);

Så påberåbt getKey på forbindelsen:

assertEquals (0, query.executeUpdate (). getKey ());

Hvis tasterne er mere end én, bruger vi getKeys i stedet, som returnerer en matrix:

assertEquals (1, query.executeUpdate (). getKeys () [0]);

5. Uddrag af data fra databasen

Lad os nu komme til kernen i sagen: VÆLG forespørgsler og kortlægning af resultatsæt til Java-objekter.

Først skal vi definere en POJO-klasse med getters og settere til at repræsentere vores projekttabel:

offentligt klasseprojekt {lang id; privat strengnavn; privat String url; // Standard getters og setters}

Så som før skriver vi vores forespørgsel:

Query query = connection.createQuery ("vælg * fra projektordre efter id");

Denne gang bruger vi dog en ny metode, executeAndFetch:

Liste liste = query.executeAndFetch (Project.class);

Som vi kan se, tager metoden klassen af ​​resultaterne som en parameter, hvortil Sql2o vil kortlægge rækkerne i det rå resultatsæt, der kommer fra databasen.

5.1. Kolonnekortlægning

SQL2o kortlægger kolonner til JavaBean-egenskaber efter navn, store og små bogstaver

Navngivningskonventioner adskiller sig imidlertid mellem Java og relationsdatabaser. Antag at vi tilføjer en egenskab til oprettelsesdato til vores projekter:

offentligt klasseprojekt {lang id; privat strengnavn; privat String url; privat Date creationDate; // Standard getters og setters}

I databaseskemaet kalder vi sandsynligvis den samme egenskab Oprettelsesdato.

Selvfølgelig kan vi alias det i vores forespørgsler:

Query query = connection.createQuery ("vælg navn, url, creation_date som creationDate fra projekt");

Det er dog kedeligt, og vi mister muligheden for at bruge Vælg *.

En anden mulighed er at instruere Sql2o om at kortlægge Oprettelsesdato til Oprettelsesdato. Det vil sige, vi kan fortælle forespørgslen om kortlægningen:

connection.createQuery ("vælg * fra projekt") .addColumnMapping ("creation_date", "creationDate");

Dette er rart, hvis vi bruger Oprettelsesdato sparsomt i en håndfuld forespørgsler; når det anvendes i vid udstrækning i et større projekt, bliver det imidlertid kedeligt og fejlbehæftet at fortælle den samme kendsgerning igen og igen.

Heldigvis kan vi også angiv kortlægninger globalt:

Kortkortlægninger = ny HashMap (); mappings.put ("CREATION_DATE", "creationDate"); sql2o.setDefaultColumnMappings (mappings);

Selvfølgelig vil dette forårsage enhver forekomst af Oprettelsesdato der skal kortlægges til Oprettelsesdato, så det er en anden grund til at stræbe efter at holde navne konsistente på tværs af definitionerne af vores data.

5.2. Skalarresultater

Nogle gange vil vi udtrække et enkelt skalaresultat fra en forespørgsel. For eksempel når vi skal tælle antallet af poster.

I disse tilfælde er det overdrevent at definere en klasse og gentage en liste, som vi ved, indeholder et enkelt element. Dermed, SQL2O inkluderer executeScalar metode:

Query query = connection.createQuery ("select count (*) from project"); assertEquals (2, query.executeScalar (Integer.class));

Her specificerer vi den returtype, der skal være Heltal. Det er dog valgfrit, og vi kan lade den underliggende JDBC-driver beslutte.

5.3. Komplekse resultater

I nogle tilfælde kan komplekse forespørgsler (f.eks. Til rapportering) muligvis ikke kortlægges på et Java-objekt. Vi beslutter muligvis også, at vi ikke ønsker at kode en Java-klasse, der kun skal bruges i en enkelt forespørgsel.

Dermed, SQL2o tillader også en lavere niveau, dynamisk kortlægning til datastrukturer i tabelform. Vi får adgang til det ved hjælp af executeAndFetchTable metode:

Query query = connection.createQuery ("vælg * fra projektordre efter id"); Tabel tabel = forespørgsel.executeAndFetchTable ();

Derefter kan vi udtrække en liste over kort:

Liste liste = tabel.asListe (); assertEquals ("tutorials", list.get (0) .get ("name"));

Alternativt kan vi kortlægge dataene på en liste over Række objekter, der er kortlægninger fra kolonnenavne til værdier, der ligner ResultSets:

Liste rækker = tabel.rækker (); assertEquals ("tutorials", rows.get (0) .getString ("name"));

6. Bindende forespørgselsparametre

Mange SQL-forespørgsler har en fast struktur med et par parametriserede dele. Vi skriver muligvis naivt disse delvist dynamiske forespørgsler med streng sammenkædning.

Sql2o tillader dog parametrerede forespørgsler, så:

  • Vi undgår SQL-injektionsangreb
  • Vi giver databasen mulighed for at cache ofte anvendte forespørgsler og få ydeevne
  • Endelig er vi skånet fra behovet for at kode komplekse typer som datoer og tidspunkter

Så vi kan bruge navngivne parametre med Sql2o for at opnå alle ovenstående. Vi introducerer parametre med et kolon, og vi binder dem med addParameter metode:

Query query = connection.createQuery ("indsæt i projektværdier (navn, url) værdier (: navn,: url)") .addParameter ("navn", "REST med forår") .addParameter ("url", "github.com / eugenp / REST-With-Spring "); assertEquals (1, query.executeUpdate (). getResult ());

6.1. Binding fra en POJO

Sql2o tilbyder en alternativ måde at binde parametre på: det vil sige ved hjælp af POJO'er som kilde. Denne teknik er især velegnet, når en forespørgsel har mange parametre, og de refererer alle til den samme enhed. Så lad os introducere det binde metode:

Projektprojekt = nyt projekt (); project.setName ("HVIL med foråret"); project.setUrl ("github.com/eugenp/REST-With-Spring"); connection.createQuery ("indsæt i projektværdier (navn, url) (: navn,: url)") .bind (projekt) .executeUpdate (); assertEquals (1, connection.getResult ());

7. Transaktioner og batchforespørgsler

Med en transaktion kan vi udstede flere SQL-udsagn som en enkelt operation, der er atomisk. Det vil sige, enten lykkes det, eller mislykkes det i bulk uden mellemliggende resultater. Faktisk er transaktioner et af nøglefunktionerne i relationsdatabaser.

For at åbne en transaktion bruger vi beginTransaktion metode i stedet for åben metode, som vi hidtil har brugt:

prøv (Forbindelsesforbindelse = sql2o.beginTransaction ()) {// her er transaktionen aktiv}

Når henrettelse forlader blokken, SQL2o ruller automatisk transaktionen tilbage hvis det stadig er aktivt.

7.1. Manuel forpligtelse og tilbageførsel

Imidlertid, Vi kan udtrykkeligt begå eller tilbageføre transaktionen med de relevante metoder:

prøv (Forbindelsesforbindelse = sql2o.beginTransaction ()) {boolean transactionSuccessful = false; // udføre nogle operationer, hvis (transactionSuccessful) {connection.commit (); } andet {connection.rollback (); }}

Noter det begge begå og tilbageførsel afslutte transaktionen. Efterfølgende udsagn kører uden en transaktion, og de rulles således ikke automatisk tilbage i slutningen af ​​blokken.

Vi kan dog begå eller tilbageføre transaktionen uden at afslutte den:

prøv (Connection connection = sql2o.beginTransaction ()) {List list = connection.createQuery ("select * from project") .executeAndFetchTable () .asList (); assertEquals (0, list.size ()); // indsæt eller opdater en dataforbindelse. rollback (falsk); // udfør nogle andre indsæt eller opdater forespørgsler} // implicit tilbagekaldelsesforsøg (Connection connection = sql2o.beginTransaction ()) {List list = connection.createQuery ("select * from project") .executeAndFetchTable () .asList (); assertEquals (0, list.size ()); }

7.2. Batchoperationer

Når vi har brug for det udsender den samme erklæring mange gange med forskellige parametre, at køre dem i en batch giver en stor ydelsesfordel.

Heldigvis er det let nok at køre dem i batch ved at kombinere to af de teknikker, vi hidtil har beskrevet - parametriserede forespørgsler og transaktioner:

  • Først opretter vi forespørgslen kun en gang
  • Derefter binder vi parametrene og kalder addToBatch for hver forekomst af forespørgslen
  • Endelig ringer vi executeBatch:
prøv (Connection connection = sql2o.beginTransaction ()) {Query query = connection.createQuery ("insert in project (name, url)" + "values ​​(: name,: url)"); for (int i = 0; i <1000; i ++) {query.addParameter ("navn", "tutorials" + i); query.addParameter ("url", "//github.com/eugenp/tutorials" + i); query.addToBatch (); } forespørgsel.executeBatch (); forbindelse.forpligtelse (); } prøv (Connection connection = sql2o.beginTransaction ()) {assertEquals (1000L, connection.createQuery ("select count (*) from project"). executeScalar ()); }

7.3. Lazy Fetch

Omvendt når en enkelt forespørgsel returnerer et stort antal resultater, er det meget hukommelse at konvertere dem alle og gemme dem på en liste.

Så Sql2o understøtter en doven tilstand, hvor rækker returneres og kortlægges en ad gangen:

Query query = connection.createQuery ("vælg * fra projekt"); prøv (ResultSetIterable projects = query.executeAndFetchLazy (Project.class)) {for (Project p: projects) {// gør noget med projektet}}

Noter det ResultSetIterable er Kan lukkes automatisk og er beregnet til at blive brugt sammen med prøv med ressourcer for at lukke det underliggende ResultSet når du er færdig.

8. Konklusioner

I denne vejledning har vi præsenteret en oversigt over Sql2o-biblioteket og dets mest almindelige brugsmønstre. Yderligere oplysninger kan findes i Sql20 wiki på GitHub.

Implementeringen af ​​alle disse eksempler og kodestykker findes også i GitHub-projektet, som er et Maven-projekt, så det skal være let at importere og køre som det er.