Panduan untuk sql2o JDBC Wrapper

1. Perkenalan

Dalam tutorial ini, kita akan melihat Sql2o, perpustakaan kecil dan cepat untuk akses database relasional di Java idiomatik.

Perlu disebutkan bahwa meskipun Sql2o bekerja dengan memetakan hasil kueri ke POJO (objek Java lama biasa), ini bukan solusi ORM lengkap seperti Hibernate.

2. Pengaturan Sql2o

Sql2o adalah satu file jar yang dapat kita tambahkan dengan mudah ke dependensi proyek kita:

 org.sql2o sql2o 1.6.0 

Kami juga akan menggunakan HSQL, database tertanam, dalam contoh kami; untuk mengikuti, kami juga dapat memasukkannya:

 org.hsqldb hsqldb 2.4.0 test 

Maven Central menghosting versi terbaru dari sql2o dan HSQLDB.

3. Menghubungkan ke Database

Untuk membuat koneksi, kita mulai dari turunan kelas Sql2o :

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

Di sini, kami menentukan URL koneksi, nama pengguna, dan kata sandi sebagai parameter konstruktor.

The Sql2o objek adalah benang-aman dan kita bisa berbagi di seluruh aplikasi.

3.1. Menggunakan Sumber Data

Di sebagian besar aplikasi, kita ingin menggunakan DataSource alih-alih koneksi DriverManager mentah , mungkin untuk memanfaatkan kumpulan koneksi, atau untuk menentukan parameter koneksi tambahan. Jangan khawatir, Sql2o telah membantu kami:

Sql2o sql2o = new Sql2o(datasource);

3.2. Bekerja Dengan Koneksi

Hanya membuat instance objek Sql2o tidak membuat koneksi apa pun ke database.

Sebagai gantinya, kami menggunakan metode terbuka untuk mendapatkan objek Connection (perhatikan bahwa ini bukan JDBC Connection ). Karena Connection is AutoCloseable, kita dapat membungkusnya dalam blok coba-dengan-sumber daya:

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

4. Sisipkan dan Perbarui Pernyataan

Sekarang mari kita buat database dan taruh beberapa data di dalamnya. Sepanjang tutorial, kita akan menggunakan tabel sederhana bernama proyek:

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

executeUpdate mengembalikan objek Connection sehingga kita dapat merantai banyak panggilan. Kemudian, jika kita ingin mengetahui jumlah baris yang terpengaruh, kita menggunakan getResult:

assertEquals(0, connection.getResult());

Kami akan menerapkan pola yang baru saja kita lihat - createQuery dan executeUpdate - untuk semua pernyataan DDL, INSERT dan UPDATE.

4.1. Mendapatkan Nilai Kunci yang Dihasilkan

Namun, dalam beberapa kasus, kami mungkin ingin mendapatkan kembali nilai kunci yang dihasilkan. Itu adalah nilai kolom kunci yang dihitung secara otomatis (seperti saat menggunakan kenaikan otomatis pada database tertentu).

Kami melakukannya dalam dua langkah. Pertama, dengan parameter tambahan untuk createQuery:

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

Kemudian, aktifkan getKey pada koneksi:

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

Jika kuncinya lebih dari satu, kami menggunakan getKeys , yang mengembalikan sebuah array:

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

5. Mengekstrak Data Dari Database

Let's now get to the core of the matter: SELECT queries and the mapping of result sets to Java objects.

First, we have to define a POJO class with getters and setters to represent our projects table:

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

Then, as before, we'll write our query:

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

However, this time we'll use a new method, executeAndFetch:

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

As we can see, the method takes the class of the results as a parameter, to which Sql2o will map the rows of the raw result set coming from the database.

5.1. Column Mapping

Sql2o maps columns to JavaBean properties by name, case-insensitive.

However, naming conventions differ between Java and relational databases. Suppose that we add a creation date property to our projects:

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.

Selain itu, penerapan semua contoh dan cuplikan kode ini dapat ditemukan di proyek GitHub, yang merupakan proyek Maven, jadi harus mudah untuk mengimpor dan menjalankan apa adanya.