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);
}
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;
}
}
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);
}
}
<?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>