HutoolKit

Origin

Database operations mainly involve four tasks: insert, delete, update, and query. In the Java world, due to the existence of JDBC, this task has become simple and easy to use, but it has not achieved simplification in usage. Therefore, frameworks such as JPA (Hibernate), MyBatis, Jfinal, BeetlSQL, etc. have emerged to solve problems such as multi-database differences or SQL maintenance. Hutool’s encapsulation of JDBC is mostly aimed at simplifying data processing in small projects, especially when only single-table operations are involved. Alright, let’s cut to the chase and take a look at a demo.

Usage

Let’s take MySQL as an example.

1. Add configuration file

In a Maven project, add the db.setting file in the src/main/resources directory (for non-Maven projects, add it to the ClassPath):

## db.setting file

url = jdbc:mysql://localhost:3306/test
user = root
pass = 123456

## Optional configurations
# Whether to display the executed SQL in the log
showSql = true
# Whether to format the displayed SQL
formatSql = false
# Whether to display SQL parameters
showParams = true
# Log level for printing SQL, default is debug, can be info, warn, error
sqlLevel = debug

    
  

2. Introduce MySQL JDBC driver jar

<!--mysql database driver -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.version}</version>
</dependency>

    
  

Note: Please refer to the official documentation to use the matching driver package version. The MySQL version is not defined here.

3. CRUD operations

Insert

Db.use().insert(
    Entity.create("user")
    .set("name", "unitTestUser")
    .set("age", 66)
);

    
  

Insert data and return the auto-incremented primary key:

Db.use().insertForGeneratedKey(
    Entity.create("user")
    .set("name", "unitTestUser")
    .set("age", 66)
);

    
  

Delete

Db.use().del(
    Entity.create("user").set("name", "unitTestUser") //where condition
);

Note: For safety reasons, using the del method is not allowed with an empty where condition to prevent deleting the entire table. If such an operation is needed, please call the execute method to execute SQL.

Update

java`Db.use().update(Entity.create().set("age", 88), // Data to be modified Entity.create("user").set("name", "unitTestUser")); // Where condition> Note: Besides using = for precise matching, the condition statement can also be used for range condition matching. For example, to express age < 12, you can construct the Entity like this: Entity.create("user").set("age", "< 12"). However, passing conditions through Entity does not currently support multiple conditions for the same field.

Query

Query all fields

// 'user' is the table name
Db.use().findAll("user");

Conditional query

Db.use().findAll(Entity.create("user").set("name", "unitTestUser"));

Fuzzy query

Db.use().findLike("user", "name", "Test", LikeType.Contains);

Or:

List<Entity> find = Db.use().find(Entity.create("user").set("name", "like 王%"));

Paginated query

// The Page object achieves pagination by passing the page number and the number of entries per page
PageResult<Entity> result = Db.use().page(Entity.create("user").set("age", "> 30"), new Page(10, 20));

Executing SQL statements

// Query
List<Entity> result = Db.use().query("select * from user where age < ?", 3);

// Fuzzy query
List<Entity> result = Db.use().query("select * from user where name like ?", "王%");

// Insert
Db.use().execute("insert into user values (?, ?, ?)", "张三", 17, 1);

// Delete
Db.use().execute("delete from user where name = ?", "张三");

// Update
Db.use().execute("update user set age = ? where name = ?", 3, "张三");

Transaction

Db.use().tx(new TxFunc() {
    @Override
    public void call(Db db) throws SQLException {
        db.insert(Entity.create("user").set("name", "unitTestUser"));
        db.update(Entity.create().set("age", 79), Entity.create("user").set("name", "unitTestUser"));
    }
});

    
  

In JDK8, you can use lambda expressions (since 5.x):

Db.use().tx(db -> {
    db.insert(Entity.create("user").set("name", "unitTestUser2"));
    db.update(Entity.create().set("age", 79), Entity.create("user").set("name", "unitTestUser2"));
});

Supports SQL execution with named placeholders

Sometimes using “?” placeholders can be cumbersome and error-prone in complex SQL, Hutool supports using named placeholders to execute SQL.

Map<String, Object> paramMap = MapUtil.builder("name1", (Object)"张三").put("age", 12).put("subName", "小豆豆").build();
Db.use().query("select * from table where id=@id and name = @name1 and nickName = @subName", paramMap);

In Hutool, placeholders support the following forms:

:name
?name
@name

IN Queries

When executing SQL similar to select * from user where id in 1,2,3, Hutool provides the following encapsulation:

List<Entity> results = db.findAll(
    Entity.create("user")
        .set("id", "in 1,2,3"));

Of course, you can also directly use:

List<Entity> results = db.findAll(
    Entity.create("user")
        .set("id", new long[]{1, 2, 3}));

FAQ