Panduan untuk Jdbi

1. Perkenalan

Pada artikel ini, kita akan melihat bagaimana cara query database relasional dengan jdbi.

Jdbi adalah pustaka Java open source (lisensi Apache) yang menggunakan ekspresi dan refleksi lambda untuk menyediakan antarmuka tingkat yang lebih ramah dan lebih tinggi daripada JDBC untuk mengakses database.

Jdbi, bagaimanapun, bukanlah ORM; Meskipun memiliki modul pemetaan Objek SQL opsional, ia tidak memiliki sesi dengan objek terlampir, lapisan independensi basis data, dan lonceng dan peluit lain dari ORM tipikal.

2. Pengaturan Jdbi

Jdbi diatur menjadi inti dan beberapa modul opsional.

Untuk memulai, kita hanya perlu memasukkan modul inti ke dalam dependensi kita:

  org.jdbi jdbi3-core 3.1.0  

Di sepanjang artikel ini, kami akan menunjukkan contoh menggunakan database HSQL:

 org.hsqldb hsqldb 2.4.0 test 

Kami dapat menemukan versi terbaru jdbi3-core , HSQLDB, dan modul Jdbi lainnya di Maven Central.

3. Menghubungkan ke Database

Pertama, kita perlu terhubung ke database. Untuk melakukan itu, kita harus menentukan parameter koneksi.

Titik awalnya adalah kelas Jdbi :

Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", "");

Di sini, kami menentukan URL koneksi, nama pengguna, dan, tentu saja, kata sandi.

3.1. Parameter Tambahan

Jika kami perlu memberikan parameter lain, kami menggunakan metode kelebihan beban yang menerima objek Properties :

Properties properties = new Properties(); properties.setProperty("username", "sa"); properties.setProperty("password", ""); Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", properties);

Dalam contoh ini, kami telah menyimpan instance Jdbi di variabel lokal. Itu karena kami akan menggunakannya untuk mengirim pernyataan dan kueri ke database.

Faktanya, hanya memanggil buat tidak membuat koneksi apa pun ke DB. Ini hanya menyimpan parameter koneksi untuk nanti.

3.2. Menggunakan Sumber Data

Jika kita terhubung ke database menggunakan DataSource , seperti yang biasanya terjadi, kita dapat menggunakan create overload yang sesuai :

Jdbi jdbi = Jdbi.create(datasource);

3.3. Bekerja Dengan Menangani

Koneksi aktual ke database diwakili oleh instance kelas Handle .

Cara termudah untuk bekerja dengan tuas, dan menutupnya secara otomatis, adalah dengan menggunakan ekspresi lambda:

jdbi.useHandle(handle -> { doStuffWith(handle); });

Kami memanggil useHandle saat kami tidak harus mengembalikan nilai.

Jika tidak, kami menggunakan withHandle :

jdbi.withHandle(handle -> { return computeValue(handle); });

Mungkin juga, meskipun tidak disarankan, untuk membuka pegangan koneksi secara manual; dalam hal ini, kami harus menutupnya setelah selesai:

Jdbi jdbi = Jdbi.create("jdbc:hsqldb:mem:testDB", "sa", ""); try (Handle handle = jdbi.open()) { doStuffWith(handle); }

Untungnya, seperti yang bisa kita lihat, Handle mengimplementasikan Closeable , sehingga bisa digunakan dengan try-with-resources.

4. Pernyataan Sederhana

Sekarang kita tahu bagaimana mendapatkan koneksi, mari kita lihat bagaimana menggunakannya.

Di bagian ini, kita akan membuat tabel sederhana yang akan kita gunakan di seluruh artikel.

Untuk mengirim pernyataan seperti membuat tabel ke database, kami menggunakan metode eksekusi :

handle.execute( "create table project " + "(id integer identity, name varchar(50), url varchar(100))");

mengeksekusi mengembalikan jumlah baris yang dipengaruhi oleh pernyataan:

int updateCount = handle.execute( "insert into project values " + "(1, 'tutorials', 'github.com/eugenp/tutorials')"); assertEquals(1, updateCount);

Sebenarnya, mengeksekusi hanyalah metode kenyamanan.

Kita akan melihat kasus penggunaan yang lebih kompleks di bagian selanjutnya, tetapi sebelum melakukan itu, kita perlu mempelajari cara mengekstrak hasil dari database.

5. Menanyakan Database

Ekspresi paling langsung yang menghasilkan hasil dari DB adalah kueri SQL.

Untuk mengeluarkan query dengan Jdbi Handle, kita harus, setidaknya:

  1. buat kueri
  2. pilih bagaimana merepresentasikan setiap baris
  3. mengulangi hasil

Sekarang kita akan melihat masing-masing poin di atas.

5.1. Membuat Query

Tidak mengherankan, Jdbi merepresentasikan query sebagai instance dari kelas Query .

Kita bisa mendapatkannya dari pegangan:

Query query = handle.createQuery("select * from project");

5.2. Memetakan Hasil

Jdbi memisahkan diri dari JDBC ResultSet , yang memiliki API yang cukup rumit.

Therefore, it offers several possibilities to access the columns resulting from a query or some other statement that returns a result. We'll now see the simplest ones.

We can represent each row as a map:

query.mapToMap();

The keys of the map will be the selected column names.

Or, when a query returns a single column, we can map it to the desired Java type:

handle.createQuery("select name from project").mapTo(String.class);

Jdbi has built-in mappers for many common classes. Those that are specific to some library or database system are provided in separate modules.

Of course, we can also define and register our mappers. We'll talk about it in a later section.

Finally, we can map rows to a bean or some other custom class. Again, we'll see the more advanced options in a dedicated section.

5.3. Iterating Over the Results

Once we've decided how to map the results by calling the appropriate method, we receive a ResultIterable object.

We can then use it to iterate over the results, one row at a time.

Here we'll look at the most common options.

We can merely accumulate the results in a list:

List results = query.mapToMap().list();

Or to another Collection type:

List results = query.mapTo(String.class).collect(Collectors.toSet());

Or we can iterate over the results as a stream:

query.mapTo(String.class).useStream((Stream stream) -> { doStuffWith(stream) });

Here, we explicitly typed the stream variable for clarity, but it's not necessary to do so.

5.4. Getting a Single Result

As a special case, when we expect or are interested in just one row, we have a couple of dedicated methods available.

If we want at most one result, we can use findFirst:

Optional first = query.mapToMap().findFirst();

As we can see, it returns an Optional value, which is only present if the query returns at least one result.

If the query returns more than one row, only the first is returned.

If instead, we want one and only one result, we use findOnly:

Date onlyResult = query.mapTo(Date.class).findOnly();

Finally, if there are zero results or more than one, findOnly throws an IllegalStateException.

6. Binding Parameters

Often, queries have a fixed portion and a parameterized portion. This has several advantages, including:

  • security: by avoiding string concatenation, we prevent SQL injection
  • ease: we don't have to remember the exact syntax of complex data types such as timestamps
  • performance: the static portion of the query can be parsed once and cached

Jdbi supports both positional and named parameters.

We insert positional parameters as question marks in a query or statement:

Query positionalParamsQuery = handle.createQuery("select * from project where name = ?");

Named parameters, instead, start with a colon:

Query namedParamsQuery = handle.createQuery("select * from project where url like :pattern");

In either case, to set the value of a parameter, we use one of the variants of the bind method:

positionalParamsQuery.bind(0, "tutorials"); namedParamsQuery.bind("pattern", "%github.com/eugenp/%");

Note that, unlike JDBC, indexes start at 0.

6.1. Binding Multiple Named Parameters at Once

We can also bind multiple named parameters together using an object.

Let's say we have this simple query:

Query query = handle.createQuery( "select id from project where name = :name and url = :url"); Map params = new HashMap(); params.put("name", "REST with Spring"); params.put("url", "github.com/eugenp/REST-With-Spring");

Then, for example, we can use a map:

query.bindMap(params);

Or we can use an object in various ways. Here, for example, we bind an object that follows the JavaBean convention:

query.bindBean(paramsBean);

But we could also bind an object's fields or methods; for all the supported options, see the Jdbi documentation.

7. Issuing More Complex Statements

Now that we've seen queries, values, and parameters, we can go back to statements and apply the same knowledge.

Recall that the execute method we saw earlier is just a handy shortcut.

In fact, similarly to queries, DDL and DML statements are represented as instances of the class Update.

We can obtain one by calling the method createUpdate on a handle:

Update update = handle.createUpdate( "INSERT INTO PROJECT (NAME, URL) VALUES (:name, :url)");

Then, on an Update we have all the binding methods that we have in a Query, so section 6. applies for updates as well.url

Statements are executed when we call, surprise, execute:

int rows = update.execute();

As we have already seen, it returns the number of affected rows.

7.1. Extracting Auto-Increment Column Values

As a special case, when we have an insert statement with auto-generated columns (typically auto-increment or sequences), we may want to obtain the generated values.

Then, we don't call execute, but executeAndReturnGeneratedKeys:

Update update = handle.createUpdate( "INSERT INTO PROJECT (NAME, URL) " + "VALUES ('tutorials', 'github.com/eugenp/tutorials')"); ResultBearing generatedKeys = update.executeAndReturnGeneratedKeys();

ResultBearing is the same interface implemented by the Query class that we've seen previously, so we already know how to use it:

generatedKeys.mapToMap() .findOnly().get("id");

8. Transactions

We need a transaction whenever we have to execute multiple statements as a single, atomic operation.

As with connection handles, we introduce a transaction by calling a method with a closure:

handle.useTransaction((Handle h) -> { haveFunWith(h); });

And, as with handles, the transaction is automatically closed when the closure returns.

However, we must commit or rollback the transaction before returning:

handle.useTransaction((Handle h) -> { h.execute("..."); h.commit(); });

If, however, an exception is thrown from the closure, Jdbi automatically rolls back the transaction.

As with handles, we have a dedicated method, inTransaction, if we want to return something from the closure:

handle.inTransaction((Handle h) -> { h.execute("..."); h.commit(); return true; });

8.1. Manual Transaction Management

Although in the general case it's not recommended, we can also begin and close a transaction manually:

handle.begin(); // ... handle.commit(); handle.close();

9. Conclusions and Further Reading

In this tutorial, we've introduced the core of Jdbi: queries, statements, and transactions.

We've left out some advanced features, like custom row and column mapping and batch processing.

We also haven't discussed any of the optional modules, most notably the SQL Object extension.

Everything is presented in detail in the Jdbi documentation.

Penerapan semua contoh dan cuplikan kode ini dapat ditemukan di proyek GitHub - ini adalah proyek Maven, jadi semestinya mudah untuk mengimpor dan menjalankan apa adanya.