JdbcClient使用

前言

随着Spring 6.1版本发布,新添加的JdbcClient接口，用于操作数据库，JdbcClient对JdbcTemplate进行了封装，采用了 fluent API 的风格，可以进行链式调用，也就是只有Springboot3.2.x版本以上的，才可以使用

JdbcClient使用

引入依赖

maven引入

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

gradle引入

implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'

新建数据库表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` bigint(20) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `author` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

SET FOREIGN_KEY_CHECKS = 1;

定义实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book {

    private Long id;

    private String name;

    private String author;
}

备注:本文采用lombok组件

注入JdbcClient

@Repository
@RequiredArgsConstructor
public class BookDao {

    private final JdbcClient jdbcClient;
}

或者

@Repository
public class BookDao {

    @Autowired
    private JdbcClient jdbcClient;
}

或者

@Repository
public class BookDao {

    @Resource
    private JdbcClient jdbcClient;
}

新增操作

新增一行数据

public Long addBook(Book book) {
    String sql = "INSERT INTO book (name , author) VALUES (:name, :author)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcClient.sql(sql).param("name", book.getName())
            .param("author", book.getAuthor())
            .update(keyHolder);
    return keyHolder.getKeyAs(BigInteger.class).longValue();
}

或者使用?

public Long addBook1(Book book) {
    String sql = "INSERT INTO book (name , author) VALUES (?, ?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcClient.sql(sql).param(1, book.getName())
            .param(2, book.getAuthor())
            .update(keyHolder);
    return keyHolder.getKeyAs(BigInteger.class).longValue();
}

更新操作

public int updateBook(Book book) {
    String sql = "UPDATE book SET name=?, author=? WHERE id=?";
    int count = jdbcClient.sql(sql)
            .param(1, book.getName())
            .param(2, book.getAuthor())
            .param(3, book.getId())
            .update();
    if (count == 0) {
        throw new RuntimeException("更新失败");
    }
    return count;
}

或者

public int updateBook1(Book book) {
    String sql = "UPDATE book SET name=:name, author=:author WHERE id=:id";
    int count = jdbcClient.sql(sql)
            .param("name", book.getName())
            .param("author", book.getAuthor())
            .param("id", book.getId())
            .update();
    if (count == 0) {
        throw new RuntimeException("更新失败");
    }
    return count;
}

查询操作

查询所有

public List<Book> getAllBooks() {
    String sql = "select * from book";
    return jdbcClient.sql(sql).query(Book.class).list();
}

查询单个

public Book findById1(Long id) {
    String sql = "select * from book where id = :id";
    return jdbcClient.sql(sql)
            .param("id", id).query(Book.class)
            .single();
}

备注: 但是要注意的是，single()在查询不到的时候，会报错

可以使用optional

public Optional<Book> findById(Long id) {
    String sql = "select id, title, url, created_at from bookmarks where id = :id";
    return jdbcClient.sql(sql).param("id", id).query(Book.class).optional();
}

或者

public Book findById2(Long id) {
    Map<String, Object> map = Map.of("id", id);
    String sql = "select * from book where id = :id";
    return jdbcClient.sql(sql)
            .params(map).query(Book.class)
            .single();
}

删除操作

public int deleteBookById(Long id) {
    String sql = "delete from book where id = ?";
    int count = jdbcClient.sql(sql).param(1, id).update();
    if (count == 0) {
        throw new RuntimeException("删除失败");
    }
    return count;
}

测试

@Slf4j
@RestController
@RequiredArgsConstructor
public class BookController {

    private final BookDao bookDao;


    @GetMapping("/test")
    public String test() {
        Book book = new Book();
        book.setName("aaa");
        book.setAuthor("aaa");
        Long id = bookDao.addBook1(book);
        log.info("id为:{{}}", id);
        return "success";
    }


    @GetMapping("/testUpdate")
    public String testUpdate() {
        Book book = new Book();
        book.setName("aaa1");
        book.setAuthor("aaa1");
        book.setId(4L);
        bookDao.updateBook1(book);
        return "success";
    }


    @GetMapping("/findById")
    public Book findById(@RequestParam("id") Long id) {
        Book book = bookDao.findById1(id);
        return book;
    }

}

总结

使用JdbcClient可以完成基本的数据库操作，而且采用Fluent风格，可以更好的阅读，当然，持久化数据库操作，有很多中方法，具体根据开发决定