A library called Apache Commons DbUtils makes it easier to write DB access code than calling a JDBC API directly. Commons DbUtils is a small library rather than a large framework, so it has the advantage of being easy to deploy.
You can also download the Jar from the download page on the Official Site, but if you are using Maven you can see the following in POM It's OK if you write like (It seems that 1.7, the latest version for the first time in 3 years, has just been released).
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
I will introduce the basic method of calling Commons DbUtils.
First, create an instance of a class called QueryRunner. Simply new with no arguments, or if you're using a DataSource (for connection pooling, etc.), just take that as an argument and new.
QueryRunner queryRunner = new QueryRunner();
//If you have a DataSource by some means, click here.
DataSource dataSource = ...
QueryRunner queryRunner = new QueryRunner(dataSource);
Next, I will introduce a simple and easy-to-understand example from the methods that execute SQL statements. This is a replacement for the part that used PreparedStatement # executeUpdate () in the direct JDBC call. Specifically, this is all.
//An example of UPDATE one row of a table called Person
int rows = queryRunner.update(connection, "UPDATE Person SET Name = ? WHERE Id = ?", "Taro", 1);
By doing this, QueryRunner internally creates a PreparedStatement, sets the parameters, calls executeUpdate (), and executes a series of closing processes.
As for the return value, the return value of PreparedStatement # executeUpdate () is the same, that is, the changed number of lines is returned.
The first argument connection can be omitted if DataSource is passed when creating QueryRunner. If omitted, getConnection () is called and used internally for that DataSource. And it will be closed automatically.
SQL statement parameters are received as variadic arguments, so you can do the following, for example.
List<Object> params = new ArrayList<>();
params.add(...);
params.add(...);
queryRunner.update("...", params.toArray()); //You can pass it in the form of an array.
Next, I will introduce the execution of the SELECT statement. This is a replacement for the part that used PreparedStatement # executeQuery () in the direct JDBC call. Unlike before, a ResultSet is returned when the SQL statement is executed, so it is necessary to convert how to get the result in the desired form (type) from that ResultSet. To that end, Common DbUtils provides an interface called ResultSetHandler.
As a simple example, if you want to retrieve a string value (that is, a single String) for a particular column, for a particular record, for a particular table:
//Implement ResultSetHandler. In this example, it is implemented as an anonymous class.
//ResultSetHandler has a type parameter, and specify the type you want to return value here. This time String.
ResultSetHandler<String> personNameHandler = new ResultSetHandler<String>() {
@Override
public String handle(ResultSet rs) throws SQLException {
if (rs.next()) {
//Just return the value in the first column (although there is only one) as a String.
return rs.getString(1);
}
return null;
}
};
//ResultSetHandler implementation class<String>Since I implemented it as
//The type of the return value name is String.
String name = queryRunner.query(connection, "SELECT Name FROM Person WHERE Id = ?", personNameHandler, 1);
In this way, the conversion process "How to convert the ResultSet once it is obtained" is expressed as ResultSetHandler, and the method called QueryRunner # query () is called by including it in the argument. By doing this, QueryRunner internally calls PreparedStatement # executeQuery (), applies ResultSetHandler # handle () to the returned ResultSet, and executes a series of processing that returns the result. Arguments other than ResultSetHandler are the same as QueryRunner # update () (connection is omitted, parameters are passed as variadic arguments).
This time I implemented ResultSetHandler by myself, but the ResultSetHandler implementation class used in typical situations like this example is already prepared in Commons DbUtils. You'll need to read the Official Javadoc for more details, but here are some.
ScalarHandler can be used in situations where you want to retrieve a single value, as in this example.
//In the previous example, this was actually OK.
String name = queryRunner.query("...", new ScalarHandler<>(), ...);
MapHandler converts the result of SELECTing one row into a map. The column name is the key to the map.
Map<String, Object> map = queryRunner.query("SELECT * FROM Person WHERE Id = ?", new MapHandler(), 1);
//You can get a map like this.
// map.get("Id") -> 1
// map.get("Name") -> "Taro"
BeanHandler converts the result of SELECTing one row into JavaBean.
//Assuming that such a class is defined ...
public class Person {
private int id;
private String name;
//Below, getter/Definition of setter ...
}
//By doing this, Java fields will be set according to the result of SELECT.
Person person = queryRunner.query("SELECT * FROM Person WHERE Id = ?", new BeanHandler<>(Person.class), 1);
By default of BeanHandler, the column name of DB and the property name (field name) of Java must match. You can also give the correspondence between column names and property names as a map by doing the following.
//Assuming that such a class is defined ...
public class Person2 {
private int id;
private String fullName; //This does not match the DB column name.
//Below, getter/Definition of setter ...
//Prepare a map with the DB column name as the key and the Java property name as the value.
Map<String, String> columnToPeroperty = new HashMap<>();
columnToPeroperty.put("Id", "id");
columnToPeroperty.put("Name", "fullName");
//An object of type RowProcessor can be specified in the constructor argument of BeanHandler.
//In addition, the constructor argument of BasicRowProcessor, which is the implementation class of RowProcessor,
//You can specify an object of type BeanProcessor.
//Furthermore, you can pass the map as a constructor argument of BeanProcessor.
//If you make the above from the bottom up ...
BeanProcessor beanProcessor = new BeanProcessor(columnToPeroperty);
RowProcessor rowProcessor = new BasicRowProcessor(beanProcessor);
ResultSetHandler<Person2> beanHandler = new BeanHandler<>(Person2.class, rowProcessor);
That's all the ResultSetHandler implementation classes you're likely to use. In addition, in order to interpret the result of SELECTing multiple rows, there is a class that takes the result of one row as an element and returns a list or map of it.
Depending on the table, there will often be columns that are automatically numbered during INSERT. Insert () is useful instead of QueryRunner # update () when you want to get the value of such a column at the same time as doing an INSERT.
The usage is similar to the query () used when SELECTing, and the ResultSetHandler is specified as an argument. Then, QueryRunner internally calls PreparedStatement # executeUpdate (), then calls PreparedStatement # getGeneratedKeys () to get the ResultSet, applies ResultSetHandler to it, and returns the result. Will give you.
Here is a simple example.
//There is a column called Id in the Person table, and it is assumed that a serial number will be assigned when INSERT is performed.
//Here, I used ScalarHandler.
Integer taroId = queryRunner.insert("INSERT INTO Person(name) VALUES(?)", new ScalarHandler<>(), "Taro") ;
Recommended Posts