- 首先创建一个用于测试的数据库,创建一张user表,建表语句如下:
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
- 创建一个spring boot项目,项目结构如下:
- 导入maven依赖,这里主要是jdbc和mysql连接的相关依赖:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
- 编写数据源连接配置文件:
spring:
datasource:
url: jdbc:mysql://localhost:3306/bg-learnsp?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
- 编写User实体类对象:
public class UserEntity {
private Long id;
private String name;
private Integer age;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "UserEntity{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
- 创建UserService接口:
public interface UserService {
/**
* 保存用户信息
*
* @param userEntity
* @return
*/
int saveUser(UserEntity userEntity);
/**
* 更新用户信息
*
* @param userEntity
* @return
*/
int updateUser(UserEntity userEntity);
/**
* 通过id查询用户信息
*
* @param id
* @return
*/
UserEntity getUserById(Long id);
/**
* 通过用户id删除用户信息
*
* @param id
* @return
*/
int removeUserById(Long id);
/**
* 查询所有用户信息
*
* @return
*/
List<UserEntity> listUsers();
/**
* 统计用户信息数量
*
* @return
*/
Long countUser();
}
- 创建接口实现类,使用JdbcTemplate实现业务逻辑:
@Service
public class UserServiceImpl implements UserService {
//注入JdbcTemplate
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int saveUser(UserEntity userEntity) {
return jdbcTemplate.update("insert user (id,name,age) values (?,?,?);", userEntity.getId(), userEntity.getName(), userEntity.getAge());
}
@Override
public int updateUser(UserEntity userEntity) {
return jdbcTemplate.update("update user set name=?,age=? where id=?", userEntity.getName(), userEntity.getAge(), userEntity.getId());
}
@Override
public UserEntity getUserById(Long id) {
return jdbcTemplate.queryForObject("select id, name, age from user where id = ? ", new RowMapper<UserEntity>() {
@Override
public UserEntity mapRow(ResultSet resultSet, int i) throws SQLException {
UserEntity user = new UserEntity();
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
return user;
}
}, id);
}
@Override
public int removeUserById(Long id) {
return jdbcTemplate.update("delete from user where id = ?", id);
}
@Override
public List<UserEntity> listUsers() {
return jdbcTemplate.query("select id, name, age from user", new RowMapper<UserEntity>() {
@Override
public UserEntity mapRow(ResultSet resultSet, int i) throws SQLException {
UserEntity user = new UserEntity();
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
return user;
}
});
}
@Override
public Long countUser() {
return jdbcTemplate.queryForObject("select count(id) from user", Long.class);
}
}
8.编写测试用例:
@SpringBootTest
class SpLearnJdbcTemplateApplicationTests {
@Autowired
private UserService userService;
/**
* 测试保存和查询用户
*/
@Test
public void testSaveAndQueryUser() {
UserEntity userEntity = new UserEntity();
userEntity.setId(1L);
userEntity.setName("法外狂徒-张三");
userEntity.setAge(32);
//保存用户信息
userService.saveUser(userEntity);
//查询刚刚保存的用户信息
System.out.println("保存用户信息:" + userService.getUserById(userEntity.getId()));
/*
运行结果:
保存用户信息:UserEntity{id=1, name='法外狂徒-张三', age=32}
*/
}
/**
* 测试更新和查询用户
*/
@Test
public void testUpdateAndQueryUser() {
//查询用户
UserEntity user = userService.getUserById(1L);
System.out.println("更新前:" + user);
user.setName("王麻子");
userService.updateUser(user);
user = userService.getUserById(1L);
System.out.println("更新后:" + user);
/*
运行结果:
更新前:UserEntity{id=1, name='法外狂徒-张三', age=32}
更新后:UserEntity{id=1, name='王麻子', age=32}
*/
}
/**
* 测试删除和查询用户信息
*/
@Test
public void testRemoveAndQuery() {
System.out.println("删除前数量:" + userService.countUser() + " ,信息:" + userService.listUsers());
//删除用户
userService.removeUserById(1L);
System.out.println("删除后数量:" + userService.countUser() + " ,信息:" + userService.listUsers());
/*
运行结果:
删除前数量:1 ,信息:[UserEntity{id=1, name='王麻子', age=32}]
删除后数量:0 ,信息:[]
*/
}
}
由上面的小例子可以看到,spring boot使用JdbcTemplate访问数据库十分简单,只需要引入相关maven依赖和编写好数据源配置文件即可,在需要使用的地方直接注入便可使用。