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
本项目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. 数据源配置
-
mysql数据源:
spring: datasource: url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai username: root password: your password
-
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. 关键代码
-
领域模型
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; }
-
启用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 { }
-
扩展继承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); }
-
使用
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); } }
-
数据库操作接口
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(); }
-
对外服务
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 {
}
}