Panduan untuk Pembungkus sql2o JDBC

1. Pengenalan

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

Perlu disebutkan bahawa walaupun Sql2o berfungsi dengan memetakan hasil pertanyaan kepada POJOs (objek Java lama biasa), itu bukan penyelesaian ORM yang lengkap seperti Hibernate.

2. Persediaan Sql2o

Sql2o adalah fail jar tunggal yang dapat kita tambahkan dengan mudah ke dalam kebergantungan projek kami:

 org.sql2o sql2o 1.6.0 

Kami juga akan menggunakan HSQL, pangkalan data terbenam, dalam contoh kami; untuk mengikuti, kita boleh memasukkannya juga:

 org.hsqldb hsqldb 2.4.0 test 

Maven Central menjadi tuan rumah versi terbaru sql2o dan HSQLDB.

3. Menyambung ke Pangkalan Data

Untuk menjalin sambungan, kita mulakan dari contoh kelas Sql2o :

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

Di sini, kami menentukan URL sambungan, nama pengguna, dan kata laluan sebagai parameter pembina.

The Sql2o objek adalah thread selamat dan kami boleh berkongsi seluruh permohonan.

3.1. Menggunakan Sumber Data

Di kebanyakan aplikasi, kami ingin menggunakan DataSource dan bukannya sambungan DriverManager mentah , mungkin untuk memanfaatkan kumpulan sambungan, atau untuk menentukan parameter sambungan tambahan. Jangan risau, Sql2o telah membuat kami dilindungi:

Sql2o sql2o = new Sql2o(datasource);

3.2. Bekerja Dengan Sambungan

Semata-mata mewujudkan kejadian yang Sql2o objek tidak menubuhkan apa-apa sambungan kepada pangkalan data.

Sebagai gantinya, kami menggunakan kaedah terbuka untuk mendapatkan objek Sambungan (perhatikan bahawa itu bukan Sambungan JDBC ). Sejak Connection adalah AutoCloseable, kita boleh balut dalam blok cuba-dengan-sumber:

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

4. Masukkan dan Kemas kini Penyata

Sekarang mari buat pangkalan data dan masukkan beberapa data di dalamnya. Sepanjang tutorial, kami akan menggunakan jadual ringkas yang disebut projek:

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

executeUpdate mengembalikan objek Connection supaya kita dapat merantai pelbagai panggilan. Kemudian, jika kita ingin mengetahui bilangan baris yang terjejas, kita menggunakan getResult:

assertEquals(0, connection.getResult());

Kami akan menerapkan corak yang baru sahaja kami lihat - createQuery dan executeUpdate - untuk semua pernyataan DDL, INSERT dan UPDATE.

4.1. Mendapatkan Nilai Utama yang Dihasilkan

Namun, dalam beberapa kes, kami mungkin ingin mendapatkan kembali nilai kunci yang dihasilkan. Itulah nilai lajur utama yang dihitung secara automatik (seperti ketika menggunakan kenaikan automatik pada pangkalan data tertentu).

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

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

Kemudian, memanggil getKey pada sambungan:

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

Sekiranya kunci lebih dari satu, kami menggunakan getKeys , yang mengembalikan array:

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

5. Mengekstrak Data Dari Pangkalan Data

Mari sekarang sampai ke inti masalah ini: PILIH pertanyaan dan pemetaan set hasil ke objek Java.

Pertama, kita mesti menentukan kelas POJO dengan pemula dan pengatur untuk mewakili jadual projek kita:

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

Kemudian, seperti sebelumnya, kami akan menulis pertanyaan kami:

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

Namun, kali ini kami akan menggunakan kaedah baru, executeAndFetch:

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

Seperti yang kita lihat, kaedah ini mengambil kelas hasil sebagai parameter, yang mana Sql2o akan memetakan baris set hasil mentah yang berasal dari pangkalan data.

5.1. Pemetaan Lajur

Sql2o memetakan lajur ke sifat JavaBean berdasarkan nama, tidak peka huruf besar kecil.

Walau bagaimanapun, konvensyen penamaan berbeza antara pangkalan data Java dan relasional. Anggaplah kita menambah harta tarikh pembuatan pada projek kita:

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.

Juga, pelaksanaan semua contoh dan potongan kode ini terdapat dalam projek GitHub, yang merupakan projek Maven, jadi mudah untuk diimport dan dijalankan sebagaimana adanya.