SpringBoot 整合 Spring Data JDBC

SpringBoot 整合 Spring Data JDBC

Spring Data JDBC 是 Spring 提供的轻量级持久化方案，无需 Hibernate 等 ORM 框架。它通过约定优于配置实现对象与数据库表的映射，支持@Table、@Id 等注解定义实体结构。开发者可通过继承 CrudRepository 接口获得基础 CRUD 能力，利用方法名关键字查询或@Query 注解编写自定义 SQL。相比 Spring Data JPA，它更简单且无复杂生命周期管理，适合简单场景。

Spring Data JDBC 功能

• DAO 接口只需继承 CrudRepository 或 PagingAndSortingRepository，Spring Data JDBC 能为 DAO 组件生成实现类。
• 支持方法名关键字查询。
• 支持用@Query 定义查询语句。
• 支持添加自定义的查询方法（通过额外的父接口及实现类）。
• 一般不支持样本查询和 Specification 查询。

Spring Data JDBC 映射规则

默认采用同名映射：

• User 对象对应 user 表。
• id 列自动映射到对象的 id 属性。

常用注解：

• @Table：映射自定义表名（非 JPA 注解）。
• @Column：映射自定义列名（非 JPA 注解）。
• @Id：修饰标识属性（非 JPA 注解）。
• @PersistenceConstructor：修饰主构造器，指定用于创建对象的构造器。

编程步骤

1. 定义映射类，为 Java 类添加@Table、@Column、@Id 和 @PersistenceConstructor。
2. 让 DAO 接口继承 CrudRepository 或 PagingAndSortingRepository。
3. 在 DAO 接口中定义方法名关键字查询、@Query 查询、完全自定义查询。

代码演示

User 类

package cn.ljh.app.domain;

import lombok.Data;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.PersistenceConstructor;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;

@Data
@Table("user_inf")
public class User {

    @Column(value = "user_id")
    @Id
    private Integer id;
    private String name;
    private String password;
    private int age;

    @PersistenceConstructor
    public User() {}

    public User(Integer id, String name, String password, int age) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", age=" + age +
                '}';
    }
}

UserDao 接口

package cn.ljh.app.dao;

import cn.ljh.app.domain.User;
import org.springframework.data.jdbc.repository.query.Modifying;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface UserDao extends CrudRepository<User,Integer>, CustomUserDao {
    // 根据名字模糊查询
    List<User> findByNameLike(String namePattern);
    // 根据年龄大小进行范围查询
    List<User> findByAgeGreaterThan(int startAge);
    List<User> findByAgeLessThan(int age);
    // 根据年龄区间进行范围查询
    List<User> findByAgeBetween(int startAge, int endAge);

    // 根据密码模糊查询
    @Query("select * from user_inf where password like :passwordPattern")
    List<User> findBySql(String passwordPattern);

    // 根据年龄范围修改名字
    @Query("update user_inf set name = :name where age between :startAge and :endAge")
    @Modifying
    int updateNameByAge(String name, int startAge, int endAge);
}

CustomUserDao 接口

package cn.ljh.app.dao;

import cn.ljh.app.domain.User;

import java.util.List;

public interface CustomUserDao {
    // 通过名字进行模糊查询，使用 dataSource
    List<User> customQueryUsingConnection(String namePattern);
    // 通过名字进行模糊查询，使用 jdbcTemplate
    List<User> customQueryUsingTemplate(String namePattern);
}

CustomUserDaoImpl 实现类

package cn.ljh.app.dao.impl;

import cn.ljh.app.dao.CustomUserDao;
import cn.ljh.app.domain.User;
import lombok.SneakyThrows;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class CustomUserDaoImpl implements CustomUserDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public CustomUserDaoImpl(DataSource dataSource, JdbcTemplate jdbcTemplate) {
        this.dataSource = dataSource;
        this.jdbcTemplate = jdbcTemplate;
    }

    @SneakyThrows
    @Override
    public List<User> customQueryUsingConnection(String namePattern) {
        Connection connection = this.dataSource.getConnection();
        PreparedStatement pstmt = connection.prepareStatement("select * from user_inf where name like ?");
        pstmt.setString(1, namePattern);
        ResultSet rs = pstmt.executeQuery();
        List<User> userList = new ArrayList<>();
        while (rs.next()) {
            userList.add(new User(
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("password"),
                    rs.getInt("age")
            ));
        }
        return userList;
    }

    @Override
    public List<User> customQueryUsingTemplate(String namePattern) {
        List<User> userList = this.jdbcTemplate.query(
                "select user_id as id,name ,password,age from user_inf where name like ?",
                new BeanPropertyRowMapper<>(User.class),
                namePattern
        );
        return userList;
    }
}

UserDaoTest 测试类

package cn.ljh.app;

import cn.ljh.app.dao.UserDao;
import cn.ljh.app.domain.User;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.CsvSource;
import org.junit.jupiter.params.provider.ValueSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
public class UserDaoTest {
    @Autowired
    private UserDao userDao;

    @ParameterizedTest
    @CsvSource({"aa,xxx,2", "bb,xxx,3"})
    public void testSave(String name, String password, int age) {
        User user = userDao.save(new User(null, name, password, age));
        System.err.println(user);
    }

    @ParameterizedTest
    @CsvSource({"13,aaa,xxxx,22"})
    public void testUpdate(Integer id, String name, String password, int age) {
        User user = userDao.save(new User(id, name, password, age));
        System.err.println(user);
    }

    @ParameterizedTest
    @ValueSource(ints = {14})
    public void testDelete(Integer id) {
        userDao.deleteById(id);
    }

    @ParameterizedTest
    @ValueSource(ints = {1})
    public void testFindById(Integer id) {
        Optional<User> user = userDao.findById(id);
    }

    @ParameterizedTest
    @ValueSource(strings = {"孙%", "%精"})
    public void testFindByNameLike(String namePattern) {
        List<User> users = userDao.findByNameLike(namePattern);
        users.forEach(System.err::println);
    }

    @ParameterizedTest
    @ValueSource(ints = {500, 10})
    public void testFindByAgeGreaterThan(int startAge) {
        List<User> users = userDao.findByAgeGreaterThan(startAge);
        users.forEach(System.err::println);
    }

    @ParameterizedTest
    @ValueSource(ints = {20})
    public void testFindByAgeLessThan(int age) {
        List<User> users = userDao.findByAgeLessThan(age);
        users.forEach(System.err::println);
    }

    @ParameterizedTest
    @CsvSource({"15,20", "500,1000"})
    public void testFindByAgeBetween(int startAge, int endAge) {
        List<User> users = userDao.findByAgeBetween(startAge, endAge);
        users.forEach(System.err::println);
    }

    @ParameterizedTest
    @ValueSource(strings = {"niu%", "%3"})
    public void testFindBySql(String passwordPattern) {
        List<User> users = userDao.findBySql(passwordPattern);
        users.forEach(System.err::println);
    }

    @ParameterizedTest
    @CsvSource({"牛魔王 aa,800,1000"})
    @Transactional
    @Rollback(false)
    public void testUpdateNameByAge(String name, int startAge, int endAge) {
        int i = userDao.updateNameByAge(name, startAge, endAge);
    }

    @ParameterizedTest
    @ValueSource(strings = {"孙%"})
    public void testCustomQueryUsingConnection(String namePattern) {
        List<User> users = userDao.customQueryUsingConnection(namePattern);
        users.forEach(System.err::println);
    }

    @ParameterizedTest
    @ValueSource(strings = {"孙%"})
    public void testCustomQueryUsingTemplate(String namePattern) {
        List<User> users = userDao.customQueryUsingTemplate(namePattern);
        users.forEach(System.err::println);
    }
}

application.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/springboot?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=123456

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.5</version>
    </parent>
    <groupId>cn.ljh</groupId>
    <artifactId>Spring_Data_JDBC</artifactId>
    <version>1.0.0</version>
    <name>Spring_Data_JDBC</name>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>