Java：SpringBoot整合JDBC实现对数据库的CURD增删改查

JDBC 是 Java数据库连接，是 Java Database Connectivity 的简称，是 Java 语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法

完整代码：https://github.com/mouday/spring-boot-demo/SpringBoot-JDBC

引入依赖

jdbc依赖

<!--spring jdbc  Spring 持久化层支持jar包-->
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency><!-- MySQL驱动 -->
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope>
</dependency><!-- 数据源 -->
<dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.15</version>
</dependency>

完整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.7.7</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.example</groupId><artifactId>demo</artifactId><version>0.0.1-SNAPSHOT</version><name>demo</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-devtools</artifactId><scope>runtime</scope><optional>true</optional></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- test --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><scope>test</scope></dependency><!--spring jdbc  Spring 持久化层支持jar包--><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jdbc</artifactId></dependency><!-- MySQL驱动 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><scope>runtime</scope></dependency><!-- 数据源 --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.15</version></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>

创建数据表

CREATE TABLE `t_user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL COMMENT '姓名',`age` int(11) DEFAULT NULL COMMENT '年龄',`sex` varchar(2) DEFAULT NULL COMMENT '性别',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

实体类对象

package com.example.demo.entity;import lombok.Data;@Data
public class User {private Integer id;private String name;private Integer age;private String sex;
}

数据库配置

application.yml

# DataSource Config
spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghaiusername: rootpassword: 123456

CURD测试

package com.example.demo;import com.example.demo.entity.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;import java.util.List;@SpringBootTest
class ApplicationTests {@Autowiredprivate JdbcTemplate jdbcTemplate;/*** 添加数据*/@Testvoid testAdd() {String sql = "insert into t_user (name, age, sex) values (?, ?, ?)";int update = jdbcTemplate.update(sql, "张三", 20, "男");System.out.println(update); // 1}/*** 更新数据*/@Testvoid testUpdate() {String sql = "update t_user set name = ? where id = ?";int update = jdbcTemplate.update(sql, "李四", 1);System.out.println(update); // 1}/*** 删除数据*/@Testvoid testDelete() {String sql = "delete from t_user where id = ?";int update = jdbcTemplate.update(sql, 1);System.out.println(update); // 1}/*** 查询单条数据*/@Testvoid testSelectOne() {String sql = "select * from t_user where id = ?";User user = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(User.class),1);System.out.println(user);// User(id=1, name=张三, age=20, sex=男)}/*** 查询多条数据*/@Testvoid testSelectList() {String sql = "select * from t_user where id = ?";List<User> user = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class),1);System.out.println(user);// [User(id=1, name=张三, age=20, sex=男)]}/*** 查询单个数据*/@Testvoid testSelectCount() {String sql = "select count(*) from t_user";Integer count = jdbcTemplate.queryForObject(sql, Integer.class);System.out.println(count);// 1}
}