Panduan untuk Jdbi

1. Pengenalan

Dalam artikel ini, kita akan melihat bagaimana membuat pertanyaan pangkalan data hubungan dengan jdbi.

Jdbi adalah perpustakaan Java sumber terbuka (lesen Apache) yang menggunakan ungkapan dan refleksi lambda untuk menyediakan antara muka tahap yang lebih mesra daripada JDBC untuk mengakses pangkalan data.

Jdbi, bagaimanapun, bukan ORM; walaupun memiliki modul pemetaan Objek SQL pilihan, ia tidak mempunyai sesi dengan objek terpasang, lapisan kebebasan pangkalan data, dan lonceng dan peluit lain dari ORM khas.

2. Persediaan Jdbi

Jdbi disusun menjadi teras dan beberapa modul pilihan.

Untuk memulakan, kita hanya perlu memasukkan modul teras dalam kebergantungan kita:

  org.jdbi jdbi3-core 3.1.0  

Sepanjang artikel ini, kami akan menunjukkan contoh menggunakan pangkalan data HSQL:

 org.hsqldb hsqldb 2.4.0 test 

Kita boleh mendapatkan versi terbaru jdbi3-core , HSQLDB dan modul Jdbi yang lain di Maven Central.

3. Menyambung ke Pangkalan Data

Pertama, kita perlu menyambung ke pangkalan data. Untuk melakukan itu, kita harus menentukan parameter sambungan.

Titik permulaannya ialah kelas Jdbi :

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

Di sini, kami menentukan URL sambungan, nama pengguna, dan, tentu saja, kata laluan.

3.1. Parameter Tambahan

Sekiranya kita perlu memberikan parameter lain, kita menggunakan kaedah yang berlebihan untuk 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 contoh Jdbi dalam pemboleh ubah tempatan. Ini kerana kami akan menggunakannya untuk menghantar penyataan dan pertanyaan ke pangkalan data.

Sebenarnya, hanya membuat panggilan tidak menjalin hubungan dengan DB. Ia hanya menyimpan parameter sambungan untuk kemudian.

3.2. Menggunakan Sumber Data

Jika kita menyambung kepada pangkalan data menggunakan sumber data , seperti biasanya kes itu, kita boleh menggunakan sesuai yang membuat beban:

Jdbi jdbi = Jdbi.create(datasource);

3.3. Bekerja Dengan Pemegang

Sambungan sebenar ke pangkalan data ditunjukkan oleh contoh kelas Handle .

Cara termudah untuk bekerja dengan pemegang, dan menutupnya secara automatik, adalah dengan menggunakan ungkapan lambda:

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

Kami memanggil useHandle apabila kami tidak perlu mengembalikan nilai.

Jika tidak, kami menggunakan denganHandle :

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

Mungkin juga, walaupun tidak digalakkan, membuka pemegang sambungan secara manual; sekiranya demikian, kita mesti menutupnya apabila kita selesai:

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

Nasib baik, seperti yang kita lihat, Handle mengimplementasikan Closeable , sehingga dapat digunakan dengan sumber daya yang dicuba.

4. Pernyataan Ringkas

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

Di bahagian ini, kami akan membuat jadual ringkas yang akan kami gunakan sepanjang artikel.

Untuk menghantar pernyataan seperti membuat jadual ke pangkalan data, kami menggunakan kaedah pelaksanaan :

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

execute mengembalikan bilangan baris yang dipengaruhi oleh pernyataan:

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

Sebenarnya, pelaksanaan hanyalah kaedah kemudahan.

Kami akan melihat kes penggunaan yang lebih kompleks di bahagian kemudian, tetapi sebelum melakukannya, kita perlu belajar bagaimana mengekstrak hasil dari pangkalan data.

5. Meminta Pangkalan Data

Ungkapan paling mudah yang menghasilkan hasil dari DB adalah pertanyaan SQL.

Untuk mengeluarkan pertanyaan dengan Jdbi Handle, kita harus, sekurang-kurangnya:

  1. buat pertanyaan
  2. pilih cara mewakili setiap baris
  3. mengulangi keputusan

Kita sekarang akan melihat setiap perkara di atas.

5.1. Membuat Pertanyaan

Tidak menghairankan, Jdbi mewakili pertanyaan sebagai contoh kelas Pertanyaan .

Kita boleh mendapatkannya dari pemegang:

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

5.2. Memetakan Hasilnya

Jdbi menjauhkan diri dari JDBC ResultSet , yang mempunyai API yang agak membebankan.

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.

Pelaksanaan semua contoh dan coretan kod ini boleh didapati di projek GitHub - ini adalah projek Maven, jadi mudah untuk diimport dan dijalankan sebagaimana adanya.