Spring Data JDBC入门使用Demo

Spring Data JDBC is a simple, limited, opinionated ORM.

Spring Data JDBC特点：

• 轻量级ORM框架，与Spring Data JPA 类似，但更轻量简单
• 基本CRUD接口实现
• 根据Java方法名推导出SQL
• @Query支持原生SQL语句，以及自定义扩展
• 支持持久化事件
• 支持集成MyBatis

Spring Data JDBC源码

本项目Demo GitHub源码

本项目Demo特点：

• Spring Data JDBC常用功能示例：PagingAndSortingRepository、NamedParameterJdbcTemplate、@Query、@Modifying等
• 使用COLA应用架构
• 使用NamedParameterJdbcTemplate与FilterCondition扩展SpringJdbc功能，灵活构造SQL语句

1. maven引用

关键引用spring-boot-starter-data-jdbc

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </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>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>

2. 项目结构

3. Mysql建表

create table user
(
    id          bigint auto_increment,
    name   varchar(20)                         not null,
    remark    varchar(32)                         null,
    age         int                                 null,
    modify_time timestamp default CURRENT_TIMESTAMP not null,
    create_time timestamp default CURRENT_TIMESTAMP not null,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB;

4. 数据源配置

1. mysql数据源：

   spring:
     datasource:
       url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
       username: root
       password: your password
   

2. H2数据源（单测用）

   spring:
     datasource:
       url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;MODE=MYSQL;CASE_INSENSITIVE_IDENTIFIERS=TRUE;
       username: test
       schema: classpath*:schema.sql
   

5. 关键代码

1. 领域模型User定义

   package com.example.springjdbcdemo.domain.user;
   
   import lombok.AllArgsConstructor;
   import lombok.Builder;
   import lombok.Data;
   import lombok.NoArgsConstructor;
   import org.springframework.data.annotation.CreatedDate;
   import org.springframework.data.annotation.Id;
   import org.springframework.data.annotation.LastModifiedDate;
   import org.springframework.data.relational.core.mapping.Table;
   
   import java.time.Instant;
   
   /**
    * @author zuozhu.meng
    * @since 2020/12/9
    **/
   @Data
   @Builder
   @NoArgsConstructor
   @AllArgsConstructor
   @Table
   public class User {
       @Id
       private Long id;
       private String name;
       private String remark;
       private Integer age;
       @LastModifiedDate
       private Instant modifyTime;
       @CreatedDate
       private Instant createTime;
   
   }
   

2. 启用spring jdbc配置，使用注解：@EnableJdbcRepositories（必需）和@EnableJdbcAuditing（支持@LastModifiedDate与@CreatedDate）

   package com.example.springjdbcdemo.config;
   
   import org.springframework.context.annotation.Configuration;
   import org.springframework.data.jdbc.repository.config.EnableJdbcAuditing;
   import org.springframework.data.jdbc.repository.config.EnableJdbcRepositories;
   
   /**
    * @author zuozhu.meng
    * @since 2020/12/9
    **/
   @Configuration
   @EnableJdbcAuditing
   @EnableJdbcRepositories(basePackages = "com.example.springjdbcdemo.infrastruction")
   public class UserConfiguration {
   }
   

3. 扩展继承spring jdbc的数据库操作接口PagingAndSortingRepository，该接口支持基本CRUD、分页和排序查询

   package com.example.springjdbcdemo.infrastruction.user;
   
   import com.example.springjdbcdemo.domain.user.User;
   import com.example.springjdbcdemo.domain.user.UserRepository;
   import org.springframework.data.jdbc.repository.query.Modifying;
   import org.springframework.data.jdbc.repository.query.Query;
   import org.springframework.data.repository.PagingAndSortingRepository;
   
   import java.util.List;
   
   /**
    * The interface User jdbc repository.
    *
    * @author zuozhu.meng
    * @since 2020 /12/9
    */
   public interface UserJdbcRepository extends PagingAndSortingRepository<User, Long>, UserRepository,
           CustomUserRepository {
   
       /**
        * Batch save.
        *
        * @param entities the entities
        * @return the iterable
        */
       @Override
       default Iterable<User> batchSave(Iterable<User> entities) {
           return saveAll(entities);
       }
   
       /**
        * Find by age.
        * user @Query
        *
        * @param age the age
        * @return the list
        */
       @Query("SELECT * FROM user WHERE age = :age")
       @Override
       List<User> findByAge(Integer age);
   
       /**
        * Update age.
        * use @Query and @Modifying
        *
        * @param id  the id
        * @param age the age
        */
       @Override
       @Modifying
       @Query("update user set age = :age where id = :id")
       void updateAge(Long id, Integer age);
   
   }
   
   

4. 使用NamedParameterJdbcTemplate与FilterCondition扩展Spring jdbc功能，灵活构造SQL语句，自定义实现接口CustomUserRepository

   package com.example.springjdbcdemo.infrastruction.user;
   
   import com.example.springjdbcdemo.domain.user.User;
   import com.example.springjdbcdemo.domain.user.UserQuery;
   import lombok.RequiredArgsConstructor;
   import org.springframework.data.jdbc.core.convert.EntityRowMapper;
   import org.springframework.data.jdbc.core.convert.JdbcConverter;
   import org.springframework.data.relational.core.mapping.RelationalMappingContext;
   import org.springframework.data.relational.core.sql.*;
   import org.springframework.data.relational.core.sql.render.SqlRenderer;
   import org.springframework.jdbc.core.BeanPropertyRowMapper;
   import org.springframework.jdbc.core.RowMapper;
   import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
   import org.springframework.stereotype.Repository;
   
   import java.util.List;
   
   /**
    * @author zuozhu.meng
    * @since 2020/12/14
    **/
   @Repository
   @RequiredArgsConstructor
   public class CustomUserRepositoryImpl implements CustomUserRepository {
       private final SqlRenderer sqlRenderer = SqlRenderer.create();
       private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
       private final RelationalMappingContext context;
       private final JdbcConverter converter;
   
       @Override
       public List<User> customFind(UserQuery userQuery) {
           Table table = SQL.table("user");
           Column name = table.column("name");
           Column age = table.column("age");
           String userQueryName = userQuery.getName();
           Integer userQueryAge = userQuery.getAge();
           FilterCondition filterCondition = FilterCondition.create()
                   .and(name.isEqualTo(SQL.literalOf(userQueryName)), userQueryName != null)
                   .and(age.isEqualTo(SQL.literalOf(userQueryAge)), userQueryAge != null);
           Select select = StatementBuilder.select()
                   .select(name, age)
                   .from(table)
                   .where(filterCondition)
                   .build();
           return query(select);
       }
   
       @SuppressWarnings("unchecked")
       private List<User> query(Select select) {
           RowMapper<User> entityRowMapper = (RowMapper<User>) getEntityRowMapper(User.class);
           return namedParameterJdbcTemplate.query(sqlRenderer.render(select), entityRowMapper);
           // you can also use BeanPropertyRowMapper
           // return namedParameterJdbcTemplate.query(sqlRenderer.render(select), getBeanPropertyRowMapper(User.class));
       }
   
       private EntityRowMapper<?> getEntityRowMapper(Class<?> type) {
           return new EntityRowMapper<>(context.getRequiredPersistentEntity(type), converter);
       }
   
       private <T> BeanPropertyRowMapper<T> getBeanPropertyRowMapper(Class<T> mappedClass) {
           return BeanPropertyRowMapper.newInstance(mappedClass);
       }
   
   }
   
   

   自定义查询对象UserQuery：

   @Data
   @Builder
   @NoArgsConstructor
   @AllArgsConstructor
   public class UserQuery {
       private String name;
       private Integer age;
   
   }
   

   实现spring jdbc的Condition接口，以支持自定义条件构造SQL语句：

   package com.example.springjdbcdemo.infrastruction.user;
   
   import org.springframework.data.relational.core.sql.Condition;
   import org.springframework.data.relational.core.sql.TrueCondition;
   import org.springframework.data.relational.core.sql.Visitor;
   
   /**
    * The type Filter condition.
    *
    * @author zuozhu.meng
    * @since 2020 /12/15
    */
   public class FilterCondition implements Condition {
   
       private static final TrueCondition DEFAULT_FILTER = TrueCondition.INSTANCE;
       private Condition holder;
   
       /**
        * Instantiates a new Filter condition.
        */
       public FilterCondition() {
           this(DEFAULT_FILTER);
       }
   
       /**
        * Instantiates a new Filter condition.
        *
        * @param holder the holder
        */
       public FilterCondition(Condition holder) {
           this.holder = holder;
       }
   
       /**
        * Create.
        *
        * @return the filter condition
        */
       public static FilterCondition create() {
           return new FilterCondition();
       }
   
       /**
        * And.
        *
        * @param other  the other
        * @param filter the filter
        * @return the filter condition
        */
       public FilterCondition and(Condition other, boolean filter) {
           if (filter) {
               if (DEFAULT_FILTER.equals(holder)) {
                   holder = other;
               } else {
                   holder = holder.and(other);
               }
           }
           return this;
       }
   
       /**
        * Or.
        *
        * @param other  the other
        * @param filter the filter
        * @return the filter condition
        */
       public FilterCondition or(Condition other, boolean filter) {
           if (filter) {
               if (DEFAULT_FILTER.equals(holder)) {
                   holder = other;
               } else {
                   holder = holder.or(other);
               }
           }
           return this;
       }
   
       @Override
       public String toString() {
           return holder.toString();
       }
   
       @Override
       public void visit(Visitor visitor) {
           holder.visit(visitor);
       }
   }
   
   

5. 数据库操作接口UserRepository

   package com.example.springjdbcdemo.domain.user;
   
   import java.util.List;
   import java.util.Optional;
   
   /**
    * The interface User repository.
    *
    * @author zuozhu.meng
    * @since 2020 /12/14
    */
   public interface UserRepository {
       /**
        * Save.
        *
        * @param user the user
        * @return the user
        */
       User save(User user);
   
       /**
        * Batch save.
        *
        * @param entities the entities
        * @return the iterable
        */
       Iterable<User> batchSave(Iterable<User> entities);
   
       /**
        * Find by name.
        * JDBC处理成SQL语句：SELECT * FROM user WHERE name = :name
        *
        * @param name the first name
        * @return the list
        */
       List<User> findByName(String name);
   
       /**
        * Find by age.
        *
        * @param age the name
        * @return the list
        */
       List<User> findByAge(Integer age);
   
       /**
        * Find by id.
        *
        * @param id the id
        * @return the optional
        */
       Optional<User> findById(Long id);
   
       /**
        * Update age.
        *
        * @param id  the id
        * @param age the age
        */
       void updateAge(Long id, Integer age);
   
       /**
        * Custom find.
        *
        * @param userQuery the user query
        * @return the list
        */
       List<User> customFind(UserQuery userQuery);
   
       /**
        * Delete all.
        */
       void deleteAll();
   }
   
   

6. 对外服务UserApplication

   package com.example.springjdbcdemo.appliacation;
   
   import com.example.springjdbcdemo.domain.user.User;
   import com.example.springjdbcdemo.domain.user.UserQuery;
   import com.example.springjdbcdemo.domain.user.UserRepository;
   import lombok.RequiredArgsConstructor;
   import org.springframework.stereotype.Service;
   
   import java.util.List;
   import java.util.Optional;
   
   /**
    * The type User service.
    *
    * @author zuozhu.meng
    * @since 2020 /12/9
    */
   @Service
   @RequiredArgsConstructor
   public class UserApplication {
       private final UserRepository userJdbcRepository;
   
       /**
        * Find by id.
        *
        * @param id the id
        * @return the optional
        */
       public Optional<User> findById(Long id) {
           return userJdbcRepository.findById(id);
       }
   
       /**
        * Find by name.
        *
        * @param firstName the first name
        * @return the list
        */
       public List<User> findByName(String firstName) {
           return userJdbcRepository.findByName(firstName);
       }
   
       /**
        * Find by age.
        *
        * @param age the age
        * @return the list
        */
       public List<User> findByAge(Integer age) {
           return userJdbcRepository.findByAge(age);
       }
   
       /**
        * Update age.
        *
        * @param id  the id
        * @param age the age
        */
       public void updateAge(Long id, Integer age) {
           userJdbcRepository.updateAge(id, age);
       }
   
       /**
        * Save.
        *
        * @param user the user
        * @return the user
        */
       public User save(User user) {
           return userJdbcRepository.save(user);
       }
   
       /**
        * Save all.
        *
        * @param iterable the iterable
        * @return the iterable
        */
       public Iterable<User> saveAll(Iterable<User> iterable) {
           return userJdbcRepository.batchSave(iterable);
       }
   
       /**
        * Custom find.
        *
        * @param userQuery the user query
        * @return the list
        */
       public List<User> customFind(UserQuery userQuery) {
           return userJdbcRepository.customFind(userQuery);
       }
   
       /**
        * Delete all.
        */
       public void deleteAll() {
           userJdbcRepository.deleteAll();
       }
   
   }
   
   

6. 使用示例

单元测试

package com.example.springjdbcdemo.appliacation;

import com.example.springjdbcdemo.config.UserConfiguration;
import com.example.springjdbcdemo.domain.user.User;
import com.example.springjdbcdemo.domain.user.UserQuery;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import java.util.stream.StreamSupport;

/**
 * @author zuozhu.meng
 * @since 2020/12/9
 **/
@SpringBootTest
@ExtendWith(SpringExtension.class)
// @ActiveProfiles(value = "mysql")
@ActiveProfiles(value = "h2")
class UserApplicationTest {
    public static final int AGE = 24;
    public static final int BATCH_SIZE = 10;
    private static final String TEST = "test";
    @Autowired
    private UserApplication userApplication;

    @AfterEach
    void tearDown() {
        userApplication.deleteAll();
    }

    @Test
    void findById() {
        User user = userApplication.save(buildUser(TEST));
        Optional<User> result = userApplication.findById(user.getId());
        Assertions.assertTrue(result.isPresent());
    }

    @Test
    void findByName() {
        User user = userApplication.save(buildUser(TEST));
        List<User> users = userApplication.findByName(user.getName());
        Assertions.assertEquals(1, users.size());
        Assertions.assertEquals(user.getName(), users.get(0).getName());
    }

    @Test
    void findByAge() {
        User user = userApplication.save(buildUser(TEST));
        List<User> users = userApplication.findByAge(user.getAge());
        Assertions.assertEquals(1, users.size());
        Assertions.assertEquals(user.getAge(), users.get(0).getAge());
    }

    @Test
    void updateAge() {
        User user = userApplication.save(buildUser(TEST));
        int age = 26;
        userApplication.updateAge(user.getId(), age);
        Optional<User> result = userApplication.findById(user.getId());
        Assertions.assertTrue(result.isPresent());
        Assertions.assertEquals(age, result.map(User::getAge).get());
    }

    @Test
    void save() {
        User user = userApplication.save(buildUser(TEST));
        Assertions.assertNotNull(user);
    }

    @Test
    void saveAllTest() {
        Iterable<User> result = saveAll();
        Assertions.assertTrue(StreamSupport.stream(result.spliterator(), false)
                .map(User::getName)
                .allMatch(name -> name.startsWith(TEST))
        );
    }

    @Test
    void customFindShouldOne() {
        saveAll();
        List<User> users = userApplication.customFind(UserQuery.builder()
                .name(TEST + 0)
                .age(AGE)
                .build());
        Assertions.assertEquals(1, users.size());
    }

    @Test
    void customFindShouldAll() {
        saveAll();
        List<User> users = userApplication.customFind(UserQuery.builder()
                .build());
        Assertions.assertEquals(BATCH_SIZE, users.size());
    }

    private Iterable<User> saveAll() {
        List<User> users = IntStream.range(0, BATCH_SIZE)
                .mapToObj(i -> buildUser(TEST + i))
                .collect(Collectors.toList());
        return userApplication.saveAll(users);
    }

    private User buildUser(String name) {
        return User.builder()
                .name(name)
                .remark("spring jdbc demo")
                .age(AGE)
                .build();
    }

    @SpringBootApplication(scanBasePackageClasses = {UserApplication.class, UserConfiguration.class})
    static class InnerConfig {
    }

}

7. 参考文档

Spring Data JDBC源码

Spring Data JDBC官方文档

COLA应用架构