Eine Anleitung zum sql2o JDBC Wrapper

1. Einleitung

In diesem Tutorial werfen wir einen Blick auf Sql2o, eine kleine und schnelle Bibliothek für den relationalen Datenbankzugriff in idiomatischem Java.

Es ist erwähnenswert, dass Sql2o zwar Abfrageergebnisse POJOs (einfachen alten Java-Objekten) zuordnet, aber keine vollständige ORM-Lösung wie Hibernate ist.

2. Sql2o-Setup

Sql2o ist eine einzelne JAR-Datei, die wir problemlos zu den Abhängigkeiten unseres Projekts hinzufügen können:

 org.sql2o sql2o 1.6.0 

In unseren Beispielen verwenden wir auch HSQL, die eingebettete Datenbank. Um mitzumachen, können wir es auch einschließen:

 org.hsqldb hsqldb 2.4.0 test 

Maven Central hostet die neueste Version von sql2o und HSQLDB.

3. Herstellen einer Verbindung zur Datenbank

Um eine Verbindung herzustellen, gehen wir von einer Instanz der Sql2o- Klasse aus:

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

Hier geben wir die Verbindungs-URL, den Benutzernamen und das Kennwort als Konstruktorparameter an.

Das Sql2o- Objekt ist threadsicher und kann für die gesamte Anwendung freigegeben werden.

3.1. Verwenden einer DataSource

In den meisten Anwendungen möchten wir eine DataSource anstelle einer DriverManager-Rohverbindung verwenden , um möglicherweise einen Verbindungspool zu nutzen oder zusätzliche Verbindungsparameter anzugeben. Keine Sorge, Sql2o hat uns abgedeckt:

Sql2o sql2o = new Sql2o(datasource);

3.2. Arbeiten mit Verbindungen

Durch das bloße Instanziieren eines Sql2o- Objekts wird keine Verbindung zur Datenbank hergestellt.

Stattdessen verwenden wir die Methode open , um ein Verbindungsobjekt abzurufen (beachten Sie, dass es sich nicht um eine JDBC- Verbindung handelt ). Da Verbindung ist AutoCloseable, können wir es in einem Try-mit-Ressourcen - Block wickeln:

try (Connection connection = sql2o.open()) { // use the connection }

4. Anweisungen einfügen und aktualisieren

Jetzt erstellen wir eine Datenbank und fügen einige Daten hinzu. Während des gesamten Tutorials verwenden wir eine einfache Tabelle namens project:

connection.createQuery( "create table project " + "(id integer identity, name varchar(50), url varchar(100))").executeUpdate();

executeUpdate gibt das Connection- Objekt zurück, sodass wir mehrere Aufrufe verketten können. Wenn wir dann die Anzahl der betroffenen Zeilen wissen möchten, verwenden wir getResult:

assertEquals(0, connection.getResult());

Wir wenden das Muster, das wir gerade gesehen haben - createQuery und executeUpdate - auf alle DDL-, INSERT- und UPDATE-Anweisungen an.

4.1. Generierte Schlüsselwerte abrufen

In einigen Fällen möchten wir jedoch möglicherweise generierte Schlüsselwerte zurückerhalten. Dies sind die Werte von Schlüsselspalten, die automatisch berechnet werden (wie bei Verwendung der automatischen Inkrementierung in bestimmten Datenbanken).

Wir machen das in zwei Schritten. Zunächst mit einem zusätzlichen Parameter zum Erstellen vonQuery:

Query query = connection.createQuery( "insert into project (name, url) " + "values ('tutorials', 'github.com/eugenp/tutorials')", true);

Rufen Sie dann getKey für die Verbindung auf:

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

Wenn die Schlüssel mehr als eins sind, verwenden wir stattdessen getKeys , das ein Array zurückgibt:

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

5. Extrahieren von Daten aus der Datenbank

Kommen wir nun zum Kern der Sache: SELECT- Abfragen und die Zuordnung von Ergebnismengen zu Java-Objekten.

Zuerst müssen wir eine POJO-Klasse mit Getter und Setter definieren, um unsere Projekttabelle darzustellen:

public class Project { long id; private String name; private String url; //Standard getters and setters }

Dann schreiben wir nach wie vor unsere Anfrage:

Query query = connection.createQuery("select * from project order by id");

Dieses Mal verwenden wir jedoch eine neue Methode, executeAndFetch:

List list = query.executeAndFetch(Project.class);

Wie wir sehen können, verwendet die Methode die Klasse der Ergebnisse als Parameter, dem Sql2o die Zeilen der Rohergebnismenge aus der Datenbank zuordnet.

5.1. Spaltenzuordnung

Sql2o ordnet Spalten JavaBean-Eigenschaften nach Namen zu, wobei die Groß- und Kleinschreibung nicht berücksichtigt wird .

Die Namenskonventionen unterscheiden sich jedoch zwischen Java und relationalen Datenbanken. Angenommen, wir fügen unseren Projekten eine Eigenschaft für das Erstellungsdatum hinzu:

public class Project { long id; private String name; private String url; private Date creationDate; //Standard getters and setters }

In the database schema, most probably we'll call the same property creation_date.

Of course, we can alias it in our queries:

Query query = connection.createQuery( "select name, url, creation_date as creationDate from project");

However, it's tedious and we lose the possibility to use select *.

Another option is to instruct Sql2o to map creation_date to creationDate. That is, we can tell the query about the mapping:

connection.createQuery("select * from project") .addColumnMapping("creation_date", "creationDate");

This is nice if we use creationDate sparingly, in a handful of queries; however, when used extensively in a larger project, it becomes tedious and error-prone to tell the same fact over and over.

Fortunately, we can also specify mappings globally:

Map mappings = new HashMap(); mappings.put("CREATION_DATE", "creationDate"); sql2o.setDefaultColumnMappings(mappings);

Of course, this will cause every instance of creation_date to be mapped to creationDate, so that's another reason for striving to keep names consistent across the definitions of our data.

5.2. Scalar Results

Sometimes, we want to extract a single scalar result from a query. For example, when we need to count the number of records.

In those cases, defining a class and iterating over a list that we know to contain a single element is overkill. Thus, Sql2o includes the executeScalar method:

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

Here, we're specifying the return type to be Integer. However, that's optional and we can let the underlying JDBC driver decide.

5.3. Complex Results

Sometimes instead, complex queries (such as for reporting) may not easily map onto a Java object. We might also decide that we don't want to code a Java class to use only in a single query.

Thus, Sql2o also allows a lower-level, dynamic mapping to tabular data structures. We get access to that using the executeAndFetchTable method:

Query query = connection.createQuery( "select * from project order by id"); Table table = query.executeAndFetchTable();

Then, we can extract a list of maps:

List list = table.asList(); assertEquals("tutorials", list.get(0).get("name"));

Alternatively, we can map the data onto a list of Row objects, that are mappings from column names to values, akin to ResultSets:

List rows = table.rows(); assertEquals("tutorials", rows.get(0).getString("name"));

6. Binding Query Parameters

Many SQL queries have a fixed structure with a few parameterized portions. We might naively write those partially dynamic queries with string concatenation.

However, Sql2o allows parameterized queries, so that:

  • We avoid SQL injection attacks
  • We allow the database to cache often-used queries and gain in performance
  • Finally, we are spared from the need to encode complex types such as dates and times

So, we can use named parameters with Sql2o to achieve all of the above. We introduce parameters with a colon and we bind them with the addParameter method:

Query query = 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. Binding From a POJO

Sql2o offers an alternative way of binding parameters: that is, by using POJOs as the source. This technique is particularly suitable when a query has many parameters and they all refer to the same entity. So, let's introduce the bind method:

Project project = new Project(); project.setName("REST with Spring"); project.setUrl("github.com/eugenp/REST-With-Spring"); connection.createQuery( "insert into project (name, url) values (:name, :url)") .bind(project) .executeUpdate(); assertEquals(1, connection.getResult());

7. Transactions and Batch Queries

With a transaction, we can issue multiple SQL statements as a single operation that is atomic. That is, either it succeeds or it fails in bulk, with no intermediate results. In fact, transactions are one of the key features of relational databases.

In order to open a transaction, we use the beginTransaction method instead of the open method that we've used so far:

try (Connection connection = sql2o.beginTransaction()) { // here, the transaction is active }

When execution leaves the block, Sql2o automatically rolls back the transaction if it's still active.

7.1. Manual Commit and Rollback

However, we can explicitly commit or rollback the transaction with the appropriate methods:

try (Connection connection = sql2o.beginTransaction()) { boolean transactionSuccessful = false; // perform some operations if(transactionSuccessful) { connection.commit(); } else { connection.rollback(); } }

Note that both commit and rollback end the transaction. Subsequent statements will run without a transaction, thus they won't be automatically rolled back at the end of the block.

However, we can commit or rollback the transaction without ending it:

try (Connection connection = sql2o.beginTransaction()) { List list = connection.createQuery("select * from project") .executeAndFetchTable() .asList(); assertEquals(0, list.size()); // insert or update some data connection.rollback(false); // perform some other insert or update queries } // implicit rollback try (Connection connection = sql2o.beginTransaction()) { List list = connection.createQuery("select * from project") .executeAndFetchTable() .asList(); assertEquals(0, list.size()); }

7.2. Batch Operations

When we need to issue the same statement many times with different parameters, running them in a batch provides a great performance benefit.

Fortunately, by combining two of the techniques that we've described so far – parameterized queries and transactions – it's easy enough to run them in batch:

  • First, we create the query only once
  • Then, we bind the parameters and call addToBatch for each instance of the query
  • Finally, we call executeBatch:
try (Connection connection = sql2o.beginTransaction()) { Query query = connection.createQuery( "insert into project (name, url) " + "values (:name, :url)"); for (int i = 0; i < 1000; i++) { query.addParameter("name", "tutorials" + i); query.addParameter("url", "//github.com/eugenp/tutorials" + i); query.addToBatch(); } query.executeBatch(); connection.commit(); } try (Connection connection = sql2o.beginTransaction()) { assertEquals( 1000L, connection.createQuery("select count(*) from project").executeScalar()); }

7.3. Lazy Fetch

Conversely, when a single query returns a great number of results, converting them all and storing them in a list is heavy on memory.

So, Sql2o supports a lazy mode, where rows are returned and mapped one at a time:

Query query = connection.createQuery("select * from project"); try (ResultSetIterable projects = query.executeAndFetchLazy(Project.class)) { for(Project p : projects) { // do something with the project } }

Note that ResultSetIterable is AutoCloseable and is meant to be used with try-with-resources to close the underlying ResultSet when finished.

8. Conclusions

In this tutorial, we've presented an overview of the Sql2o library and its most common usage patterns. Further information can be found in the Sql20 wiki on GitHub.

Die Implementierung all dieser Beispiele und Codefragmente finden Sie auch im GitHub-Projekt, einem Maven-Projekt. Daher sollte es einfach zu importieren und unverändert auszuführen sein.