streamalism.org

1. Inleiding

In deze tutorial gaan we kijken naar Sql2o, een kleine en snelle bibliotheek voor relationele databasetoegang in idiomatisch Java.

Het is de moeite waard om te vermelden dat, hoewel Sql2o werkt door queryresultaten toe te wijzen aan POJO's (gewone oude Java-objecten), het is geen complete ORM-oplossing zoals Hibernate.

2. SQL2o-instellingen

Sql2o is een enkel jar-bestand dat we gemakkelijk kunnen toevoegen aan de afhankelijkheden van ons project:

 org.sql2o sql2o 1.6.0 

We zullen ook HSQL, de embedded database, gebruiken in onze voorbeelden; om te volgen, kunnen we het ook opnemen:

 org.hsqldb hsqldb 2.4.0 test 

Maven Central host de nieuwste versie van sql2o en HSQLDB.

3. Verbinding maken met de database

Om een ​​verbinding tot stand te brengen, gaan we uit van een instantie van het SQL2o klasse:

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

Hier specificeren we de verbindings-URL, gebruikersnaam en wachtwoord als constructorparameters.

De SQL2o object is thread-safe en we kunnen het met de hele applicatie delen.

3.1. Gebruik maken van een Databron

In de meeste toepassingen willen we een Databronin plaats van een rauw DriverManager verbinding, misschien om gebruik te maken van een verbindingspool, of om aanvullende verbindingsparameters op te geven. Maak u geen zorgen, Sql2o heeft ons gedekt:

Sql2o sql2o = nieuwe Sql2o (gegevensbron);

3.2. Werken met verbindingen

Gewoon een instantiëren SQL2o object maakt geen verbinding met de database.

In plaats daarvan, wij gebruiken de Open methode om een Verbinding voorwerp (merk op dat het geen JDBC Verbinding). Sinds Verbinding is AutoCloseable, we kunnen het verpakken in een try-with-resources-blok:

probeer (Connection connection = sql2o.open ()) {// gebruik de verbinding}

4. Afschriften invoegen en bijwerken

Laten we nu een database maken en er wat gegevens in plaatsen. Tijdens de tutorial gebruiken we een eenvoudige tabel met de naam project:

connection.createQuery ("maak tabelproject" + "(id geheel getal identiteit, naam varchar (50), url varchar (100))"). executeUpdate ();

executeUpdate geeft de Verbinding object zodat we meerdere oproepen kunnen koppelen. Als we vervolgens het aantal betrokken rijen willen weten, gebruiken we getResult:

assertEquals (0, connection.getResult ());

We passen het patroon toe dat we zojuist hebben gezien - createQuery en uitvoerenUpdate -voor alle DDL-, INSERT- en UPDATE-instructies.

4.1. Gegenereerde sleutelwaarden krijgen

In sommige gevallen we willen misschien gegenereerde sleutelwaarden terugkrijgen. Dat zijn de waarden van sleutelkolommen die automatisch worden berekend (zoals bij gebruik van auto-increment in bepaalde databases).

Dat doen we in twee stappen. Ten eerste met een extra parameter voor createQuery:

Queryquery = connection.createQuery ("invoegen in project (naam, url)" + "waarden ('tutorials', 'github.com/eugenp/tutorials')", true);

Dan een beroep doen getKey over de verbinding:

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

Als de sleutels meer dan één zijn, gebruiken we getKeys in plaats daarvan, wat een array retourneert:

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

5. Gegevens uit de database halen

Laten we nu tot de kern van de zaak komen: SELECTEER query's en de toewijzing van resultaatsets aan Java-objecten.

Eerst moeten we een POJO-klasse definiëren met getters en setters om onze projecttabel weer te geven:

openbare klasse Project {lange id; private String naam; privé String url; // Standaard getters en setters}

Vervolgens zullen we, zoals eerder, onze vraag schrijven:

Queryquery = connection.createQuery ("selecteer * uit projectvolgorde op id");

Deze keer gebruiken we echter een nieuwe methode, executeAndFetch:

Lijstlijst = query.executeAndFetch (Project.class);

Zoals we kunnen zien, neemt de methode de klasse van de resultaten als een parameter, waaraan Sql2o de rijen van de onbewerkte resultatenset die uit de database komen, zal toewijzen.

5.1. Kolomtoewijzing

Sql2o wijst kolommen op naam toe aan JavaBean-eigenschappen, niet hoofdlettergevoelig.

Naamgevingsconventies verschillen echter tussen Java- en relationele databases. Stel dat we een aanmaakdatum-eigenschap aan onze projecten toevoegen:

openbare klasse Project {lange id; private String naam; privé String url; privé datum creationDate; // Standaard getters en setters}

In het databaseschema noemen we hoogstwaarschijnlijk dezelfde eigenschap Aanmaakdatum.

Natuurlijk kunnen we het in onze vragen aliassen:

Queryquery = connection.createQuery ("selecteer naam, url, aanmaakdatum als creationDate van project");

Het is echter vervelend en we verliezen de mogelijkheid om te gebruiken selecteer *.

Een andere optie is om Sql2o opdracht te geven om in kaart te brengen Aanmaakdatum naar Aanmaakdatum. Dat wil zeggen, we kunnen de vraag over de mapping vertellen:

connection.createQuery ("selecteer * uit project") .addColumnMapping ("creation_date", "creationDate");

Dit is fijn als we gebruik maken van Aanmaakdatum spaarzaam, in een handvol vragen; Wanneer het echter op grote schaal wordt gebruikt in een groter project, wordt het vervelend en vatbaar voor fouten om hetzelfde feit keer op keer te vertellen.

Gelukkig kunnen we dat ook globaal toewijzingen specificeren:

Kaarttoewijzingen = nieuwe HashMap (); mappings.put ("CREATION_DATE", "creationDate"); sql2o.setDefaultColumnMappings (toewijzingen);

Dit veroorzaakt natuurlijk elk exemplaar van Aanmaakdatum waaraan moet worden toegewezen Aanmaakdatum, dus dat is nog een reden om ernaar te streven namen consistent te houden in de definities van onze gegevens.

5.2. Scalaire resultaten

Soms willen we een enkel scalair resultaat uit een query halen. Bijvoorbeeld wanneer we het aantal records moeten tellen.

In die gevallen is het definiëren van een klasse en het herhalen van een lijst waarvan we weten dat deze een enkel element bevat, overdreven. Dus, SQL2o omvat de executeScalar methode:

Queryquery = connection.createQuery ("selecteer aantal (*) van project"); assertEquals (2, query.executeScalar (Integer.class));

Hier specificeren we het retourtype dat moet zijn Geheel getal. Dat is echter optioneel en we kunnen het onderliggende JDBC-stuurprogramma laten beslissen.

5.3. Complexe resultaten

Soms kunnen complexe query's (zoals voor rapportage) niet gemakkelijk worden toegewezen aan een Java-object. We kunnen ook besluiten dat we geen Java-klasse willen coderen om alleen in een enkele query te gebruiken.

Dus, Sql2o maakt ook een dynamische toewijzing op een lager niveau aan datastructuren in tabelvorm mogelijk. We krijgen daar toegang toe met behulp van de executeAndFetchTable methode:

Queryquery = connection.createQuery ("selecteer * uit projectvolgorde op id"); Tabel tabel = query.executeAndFetchTable ();

Vervolgens kunnen we een lijst met kaarten extraheren:

Lijst

Als alternatief kunnen we de gegevens toewijzen aan een lijst met Rij objecten, die toewijzingen zijn van kolomnamen aan waarden, vergelijkbaar met ResultSets:

Lijst rijen = table.rows (); assertEquals ("tutorials", rijen.get (0) .getString ("naam"));

6. Parameters voor bindende query's

Veel SQL-query's hebben een vaste structuur met een paar geparametriseerde gedeelten. We zouden naïef die gedeeltelijk dynamische query's kunnen schrijven met aaneenschakeling van tekenreeksen.

Sql2o staat echter geparametriseerde query's toe, zodat:

• We vermijden SQL-injectie-aanvallen
• We staan ​​de database toe om veelgebruikte zoekopdrachten in de cache op te slaan en de prestaties te verbeteren
• Ten slotte worden we bespaard van de noodzaak om complexe typen zoals datums en tijden te coderen

We kunnen dus benoemde parameters gebruiken met Sql2o om al het bovenstaande te bereiken. We introduceren parameters met een dubbele punt en we binden ze met de addParameter methode:

Queryquery = connection.createQuery ("insert into project (name, url) values ​​(: name,: url)") .addParameter ("name", "REST with Spring") .addParameter ("url", "github.com / eugenp / REST-With-Spring "); assertEquals (1, query.executeUpdate (). getResult ());

6.1. Binden vanuit een POJO

SQL2o biedt een alternatieve manier om parameters te binden: dat wil zeggen, door POJO's te gebruiken als de bron. Deze techniek is met name geschikt wanneer een query veel parameters heeft en ze allemaal naar dezelfde entiteit verwijzen. Dus laten we even voorstellen de binden methode:

Project project = nieuw project (); project.setName ("REST met lente"); project.setUrl ("github.com/eugenp/REST-With-Spring"); connection.createQuery ("invoegen in project (naam, url) waarden (: naam,: url)") .bind (project) .executeUpdate (); assertEquals (1, connection.getResult ());

7. Transacties en batchquery's

Met een transactie kunnen we meerdere SQL-instructies afgeven als een enkele bewerking die atomair is. Dat wil zeggen, het slaagt of het mislukt in bulk, zonder tussenliggende resultaten. Transacties zijn in feite een van de belangrijkste kenmerken van relationele databases.

Om een ​​transactie te openen, gebruiken we de beginTransaction methode in plaats van de Open methode die we tot nu toe hebben gebruikt:

probeer (Connection connection = sql2o.beginTransaction ()) {// hier is de transactie actief}

Wanneer de uitvoering het blok verlaat, SQL2o draait de transactie automatisch terug als het nog steeds actief is.

7.1. Handmatig vastleggen en terugdraaien

Echter, we kunnen de transactie expliciet vastleggen of terugdraaien met de juiste methoden:

probeer (Verbindingsverbinding = sql2o.beginTransaction ()) {boolean transactionSuccessful = false; // voer enkele bewerkingen uit if (transactionSuccessful) {connection.commit (); } anders {connection.rollback (); }}

Let daar op beide plegen en terugrollen beëindig de transactie. Volgende instructies worden uitgevoerd zonder een transactie, dus ze worden niet automatisch teruggedraaid aan het einde van het blok.

We kunnen de transactie echter vastleggen of terugdraaien zonder deze te beëindigen:

probeer (Verbinding verbinding = sql2o.beginTransaction ()) {Lijst lijst = verbinding.createQuery ("selecteer * uit project") .executeAndFetchTable () .asList (); assertEquals (0, list.size ()); // een gegevensverbinding invoegen of bijwerken. rollback (false); // voer een andere invoeg- of update-query's uit} // impliciete poging tot terugdraaien (Connection connection = sql2o.beginTransaction ()) {List list = connection.createQuery ("select * from project") .executeAndFetchTable () .asList (); assertEquals (0, list.size ()); }

7.2. Batchbewerkingen

Als het nodig is dezelfde verklaring meerdere keren afgeven met verschillende parameters, Als u ze in een batch uitvoert, levert dit een groot prestatievoordeel op.

Gelukkig is het door twee van de technieken die we tot nu toe hebben beschreven te combineren - geparametriseerde query's en transacties - eenvoudig genoeg om ze in batch uit te voeren:

• Ten eerste maken we de query slechts één keer
• Vervolgens binden we de parameters en bellen we addToBatch voor elk exemplaar van de query
• Eindelijk bellen we uitvoerenBatch:

probeer (Connection connection = sql2o.beginTransaction ()) {Query query = connection.createQuery ("invoegen in project (naam, url)" + "waarden (: naam,: url)"); for (int i = 0; i <1000; i ++) {query.addParameter ("naam", "tutorials" + i); query.addParameter ("url", "//github.com/eugenp/tutorials" + i); query.addToBatch (); } query.executeBatch (); connection.commit (); } probeer (Verbindingsverbinding = sql2o.beginTransaction ()) {assertEquals (1000L, connection.createQuery ("selecteer aantal (*) van project"). executeScalar ()); }

7.3. Lazy Fetch

Omgekeerd, wanneer een enkele zoekopdracht een groot aantal resultaten oplevert, vergt het veel geheugen om ze allemaal te converteren en op te slaan in een lijst.

Sql2o ondersteunt dus een luie modus, waarbij rijen één voor één worden geretourneerd en toegewezen:

Queryquery = connection.createQuery ("selecteer * uit project"); probeer (ResultSetIterable projects = query.executeAndFetchLazy (Project.class)) {for (Project p: projects) {// doe iets met het project}}

Let daar op ResultSetIterable is AutoCloseable en is bedoeld om mee te worden gebruikt probeer-met-middelen om het onderliggende te sluiten ResultSet wanneer je klaar bent.

8. Conclusies

In deze tutorial hebben we een overzicht gegeven van de Sql2o-bibliotheek en de meest voorkomende gebruikspatronen. Meer informatie is te vinden in de Sql20-wiki op GitHub.

De implementatie van al deze voorbeelden en codefragmenten is ook te vinden in het GitHub-project, dat een Maven-project is, dus het moet gemakkelijk te importeren en te draaien zijn zoals het is.