Panduan untuk Apache Commons DbUtils

1. Ikhtisar

Apache Commons DbUtils adalah pustaka kecil yang membuat bekerja dengan JDBC jauh lebih mudah.

Dalam artikel ini, kami akan menerapkan contoh untuk menunjukkan fitur dan kemampuannya.

2. Penyiapan

2.1. Dependensi Maven

Pertama, kita perlu menambahkan dependensi commons-dbutils dan h2 ke pom.xml kita :

 commons-dbutils commons-dbutils 1.6   com.h2database h2 1.4.196 

Anda dapat menemukan versi terbaru dari commons-dbutils dan h2 di Maven Central.

2.2. Uji Database

Dengan ketergantungan kita pada tempatnya, mari buat skrip untuk membuat tabel dan catatan yang akan kita gunakan:

CREATE TABLE employee( id int NOT NULL PRIMARY KEY auto_increment, firstname varchar(255), lastname varchar(255), salary double, hireddate date, ); CREATE TABLE email( id int NOT NULL PRIMARY KEY auto_increment, employeeid int, address varchar(255) ); INSERT INTO employee (firstname,lastname,salary,hireddate) VALUES ('John', 'Doe', 10000.10, to_date('01-01-2001','dd-mm-yyyy')); // ... INSERT INTO email (employeeid,address) VALUES (1, '[email protected]'); // ...

Semua contoh kasus uji dalam artikel ini akan menggunakan koneksi yang baru dibuat ke database dalam memori H2:

public class DbUtilsUnitTest { private Connection connection; @Before public void setupDB() throws Exception { Class.forName("org.h2.Driver"); String db = "jdbc:h2:mem:;INIT=runscript from 'classpath:/employees.sql'"; connection = DriverManager.getConnection(db); } @After public void closeBD() { DbUtils.closeQuietly(connection); } // ... }

2.3. POJO

Terakhir, kita membutuhkan dua kelas sederhana:

public class Employee { private Integer id; private String firstName; private String lastName; private Double salary; private Date hiredDate; // standard constructors, getters, and setters } public class Email { private Integer id; private Integer employeeId; private String address; // standard constructors, getters, and setters }

3. Pendahuluan

The DbUtils perpustakaan menyediakan yang QueryRunner kelas sebagai entry point utama untuk sebagian besar fungsi yang tersedia.

Kelas ini bekerja dengan menerima koneksi ke database, pernyataan SQL yang akan dijalankan, dan daftar parameter opsional untuk menyediakan nilai untuk placeholder kueri.

Seperti yang akan kita lihat nanti, beberapa metode juga menerima implementasi ResultSetHandler - yang bertanggung jawab untuk mengubah instance ResultSet menjadi objek yang diharapkan aplikasi kita.

Tentu saja, library tersebut sudah menyediakan beberapa implementasi yang menangani transformasi paling umum, seperti list, maps, dan JavaBeans.

4. Menanyakan Data

Sekarang setelah kita mengetahui dasar-dasarnya, kita siap untuk menanyakan database kita.

Mari kita mulai dengan contoh cepat untuk mendapatkan semua catatan dalam database sebagai daftar peta menggunakan MapListHandler :

@Test public void givenResultHandler_whenExecutingQuery_thenExpectedList() throws SQLException { MapListHandler beanListHandler = new MapListHandler(); QueryRunner runner = new QueryRunner(); List list = runner.query(connection, "SELECT * FROM employee", beanListHandler); assertEquals(list.size(), 5); assertEquals(list.get(0).get("firstname"), "John"); assertEquals(list.get(4).get("firstname"), "Christian"); }

Berikutnya, berikut adalah contoh menggunakan BeanListHandler untuk mengubah hasil menjadi instance Karyawan :

@Test public void givenResultHandler_whenExecutingQuery_thenEmployeeList() throws SQLException { BeanListHandler beanListHandler = new BeanListHandler(Employee.class); QueryRunner runner = new QueryRunner(); List employeeList = runner.query(connection, "SELECT * FROM employee", beanListHandler); assertEquals(employeeList.size(), 5); assertEquals(employeeList.get(0).getFirstName(), "John"); assertEquals(employeeList.get(4).getFirstName(), "Christian"); }

Untuk kueri yang mengembalikan satu nilai, kita bisa menggunakan ScalarHandler :

@Test public void givenResultHandler_whenExecutingQuery_thenExpectedScalar() throws SQLException { ScalarHandler scalarHandler = new ScalarHandler(); QueryRunner runner = new QueryRunner(); String query = "SELECT COUNT(*) FROM employee"; long count = runner.query(connection, query, scalarHandler); assertEquals(count, 5); }

Untuk mempelajari semua implementasi ResultSerHandler , Anda bisa merujuk ke dokumentasi ResultSetHandler .

4.1. Penangan Kustom

Kita juga dapat membuat penangan khusus untuk diteruskan ke metode QueryRunner saat kita membutuhkan lebih banyak kontrol tentang bagaimana hasil akan diubah menjadi objek.

Ini bisa dilakukan dengan mengimplementasikan antarmuka ResultSetHandler atau memperluas salah satu implementasi yang ada yang disediakan oleh pustaka.

Mari kita lihat bagaimana pendekatan kedua terlihat. Pertama, mari tambahkan bidang lain ke kelas Karyawan kami :

public class Employee { private List emails; // ... }

Sekarang, mari buat kelas yang memperluas tipe BeanListHandler dan menyetel daftar email untuk setiap karyawan:

public class EmployeeHandler extends BeanListHandler { private Connection connection; public EmployeeHandler(Connection con) { super(Employee.class); this.connection = con; } @Override public List handle(ResultSet rs) throws SQLException { List employees = super.handle(rs); QueryRunner runner = new QueryRunner(); BeanListHandler handler = new BeanListHandler(Email.class); String query = "SELECT * FROM email WHERE employeeid = ?"; for (Employee employee : employees) { List emails = runner.query(connection, query, handler, employee.getId()); employee.setEmails(emails); } return employees; } }

Perhatikan bahwa kami mengharapkan objek Connection dalam konstruktor sehingga kami dapat menjalankan kueri untuk mendapatkan email.

Terakhir, mari kita uji kode kita untuk melihat apakah semuanya berfungsi seperti yang diharapkan:

@Test public void givenResultHandler_whenExecutingQuery_thenEmailsSetted() throws SQLException { EmployeeHandler employeeHandler = new EmployeeHandler(connection); QueryRunner runner = new QueryRunner(); List employees = runner.query(connection, "SELECT * FROM employee", employeeHandler); assertEquals(employees.get(0).getEmails().size(), 2); assertEquals(employees.get(2).getEmails().size(), 3); }

4.2. Prosesor Baris Kustom

In our examples, the column names of the employee table match the field names of our Employee class (the matching is case insensitive). However, that's not always the case – for instance when column names use underscores to separate compound words.

In these situations, we can take advantage of the RowProcessor interface and its implementations to map the column names to the appropriate fields in our classes.

Let's see how this looks like. First, let's create another table and insert some records into it:

CREATE TABLE employee_legacy ( id int NOT NULL PRIMARY KEY auto_increment, first_name varchar(255), last_name varchar(255), salary double, hired_date date, ); INSERT INTO employee_legacy (first_name,last_name,salary,hired_date) VALUES ('John', 'Doe', 10000.10, to_date('01-01-2001','dd-mm-yyyy')); // ...

Now, let's modify our EmployeeHandler class:

public class EmployeeHandler extends BeanListHandler { // ... public EmployeeHandler(Connection con) { super(Employee.class, new BasicRowProcessor(new BeanProcessor(getColumnsToFieldsMap()))); // ... } public static Map getColumnsToFieldsMap() { Map columnsToFieldsMap = new HashMap(); columnsToFieldsMap.put("FIRST_NAME", "firstName"); columnsToFieldsMap.put("LAST_NAME", "lastName"); columnsToFieldsMap.put("HIRED_DATE", "hiredDate"); return columnsToFieldsMap; } // ... }

Notice we are using a BeanProcessor to do the actual mapping of columns to fields and only for those that need to be addressed.

Finally, let's test everything is ok:

@Test public void givenResultHandler_whenExecutingQuery_thenAllPropertiesSetted() throws SQLException { EmployeeHandler employeeHandler = new EmployeeHandler(connection); QueryRunner runner = new QueryRunner(); String query = "SELECT * FROM employee_legacy"; List employees = runner.query(connection, query, employeeHandler); assertEquals((int) employees.get(0).getId(), 1); assertEquals(employees.get(0).getFirstName(), "John"); }

5. Inserting Records

The QueryRunner class provides two approaches to creating records in a database.

The first one is to use the update() method and pass the SQL statement and an optional list of replacement parameters. The method returns the number of inserted records:

@Test public void whenInserting_thenInserted() throws SQLException { QueryRunner runner = new QueryRunner(); String insertSQL = "INSERT INTO employee (firstname,lastname,salary, hireddate) " + "VALUES (?, ?, ?, ?)"; int numRowsInserted = runner.update( connection, insertSQL, "Leia", "Kane", 60000.60, new Date()); assertEquals(numRowsInserted, 1); }

The second one is to use the insert() method that, in addition to the SQL statement and replacement parameters, needs a ResultSetHandler to transform the resulting auto-generated keys. The return value will be what the handler returns:

@Test public void givenHandler_whenInserting_thenExpectedId() throws SQLException { ScalarHandler scalarHandler = new ScalarHandler(); QueryRunner runner = new QueryRunner(); String insertSQL = "INSERT INTO employee (firstname,lastname,salary, hireddate) " + "VALUES (?, ?, ?, ?)"; int newId = runner.insert( connection, insertSQL, scalarHandler, "Jenny", "Medici", 60000.60, new Date()); assertEquals(newId, 6); }

6. Updating and Deleting

The update() method of the QueryRunner class can also be used to modify and erase records from our database.

Its usage is trivial. Here's an example of how to update an employee's salary:

@Test public void givenSalary_whenUpdating_thenUpdated() throws SQLException { double salary = 35000; QueryRunner runner = new QueryRunner(); String updateSQL = "UPDATE employee SET salary = salary * 1.1 WHERE salary <= ?"; int numRowsUpdated = runner.update(connection, updateSQL, salary); assertEquals(numRowsUpdated, 3); }

And here's another to delete an employee with the given id:

@Test public void whenDeletingRecord_thenDeleted() throws SQLException { QueryRunner runner = new QueryRunner(); String deleteSQL = "DELETE FROM employee WHERE id = ?"; int numRowsDeleted = runner.update(connection, deleteSQL, 3); assertEquals(numRowsDeleted, 1); }

7. Asynchronous Operations

DbUtils provides the AsyncQueryRunner class to execute operations asynchronously. The methods on this class have a correspondence with those of QueryRunner class, except that they return a Future instance.

Here's an example to obtain all employees in the database, waiting up to 10 seconds to get the results:

@Test public void givenAsyncRunner_whenExecutingQuery_thenExpectedList() throws Exception { AsyncQueryRunner runner = new AsyncQueryRunner(Executors.newCachedThreadPool()); EmployeeHandler employeeHandler = new EmployeeHandler(connection); String query = "SELECT * FROM employee"; Future
    
      future = runner.query(connection, query, employeeHandler); List employeeList = future.get(10, TimeUnit.SECONDS); assertEquals(employeeList.size(), 5); }
    

8. Conclusion

In this tutorial, we explored the most notable features of the Apache Commons DbUtils library.

Kami menanyakan data dan mengubahnya menjadi tipe objek yang berbeda, memasukkan catatan yang mendapatkan kunci utama yang dihasilkan dan memperbarui dan menghapus data berdasarkan kriteria yang diberikan. Kami juga memanfaatkan kelas AsyncQueryRunner untuk menjalankan operasi kueri secara asinkron.

Dan, seperti biasa, kode sumber lengkap untuk artikel ini dapat ditemukan di Github.