JDBC与SpringBoot后端开发 完整学习笔记
课次21-25：从原生JDBC到SpringBoot + MyBatis-Plus的企业级开发演进
第一部分：MySQL安装与数据库设计（课次21）
一、MySQL 8.0 安装
Windows安装步骤
下载MySQL Community Server：https://dev.mysql.com/downloads/mysql/
解压到指定目录，如 D:\mysql-8.0.35
在根目录创建 my.ini 配置文件：
ini
[mysqld]
port=3306
basedir=D:\mysql-8.0.35
datadir=D:\mysql-8.0.35\data
character-set-server=utf8mb4
default-storage-engine=INNODB
max_connections=200
[client]
default-character-set=utf8mb4
以管理员身份运行CMD，执行以下命令：
bash
cd D:\mysql-8.0.35\bin
mysqld --initialize --console # 初始化，记录临时密码
mysqld --install MySQL # 安装服务
net start MySQL # 启动服务
mysql -u root -p # 登录
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; # 修改密码
FLUSH PRIVILEGES;
macOS/Linux安装
bash
macOS
brew install mysql && brew services start mysql
Ubuntu
sudo apt update && sudo apt install mysql-server
sudo systemctl start mysql
二、数据库与表结构
创建数据库
sql
CREATE DATABASE IF NOT EXISTS news_management
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE news_management;
user表（用户表）
sql
CREATE TABLE IF NOT EXISTS user (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
password VARCHAR(255) NOT NULL COMMENT '密码',
email VARCHAR(100) COMMENT '邮箱',
phone VARCHAR(20) COMMENT '手机号',
news_count INT DEFAULT 0 COMMENT '发布新闻数量',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT DEFAULT 0 COMMENT '逻辑删除(0-未删除,1-已删除)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
news表（新闻表）
sql
CREATE TABLE IF NOT EXISTS news (
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '新闻ID',
title VARCHAR(200) NOT NULL COMMENT '新闻标题',
content TEXT NOT NULL COMMENT '新闻内容',
author_id INT NOT NULL COMMENT '作者ID(关联user.id)',
author_name VARCHAR(50) COMMENT '作者姓名(冗余字段)',
view_count INT DEFAULT 0 COMMENT '浏览次数',
status TINYINT DEFAULT 1 COMMENT '状态: 0-草稿,1-已发布,2-已下架',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',
FOREIGN KEY (author_id) REFERENCES user(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='新闻表';
第二部分：JDBC原生开发（课次21-22）
三、JDBC核心接口
接口/类 作用 关键方法
DriverManager 管理JDBC驱动，获取连接 getConnection()
Connection 数据库连接对象 prepareStatement(), setAutoCommit(), commit(), rollback()
PreparedStatement 预编译SQL，防SQL注入 setXxx(), executeQuery(), executeUpdate()
ResultSet 查询结果集 next(), getXxx()
四、Maven依赖
xml
五、JDBC工具类
java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
private static final String URL =
"jdbc:mysql://localhost:3306/news_management?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8mb4";
private static final String USER = "root";
private static final String PASSWORD = "123456";
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException("MySQL驱动加载失败", e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
}
六、用户实体类
java
import java.time.LocalDateTime;
public class User {
private Integer id;
private String username;
private String password;
private String email;
private String phone;
private Integer newsCount;
private LocalDateTime createTime;
private LocalDateTime updateTime;
private Integer deleted;
// 无参构造、全参构造、Getter/Setter、toString()
}
七、注册功能（PreparedStatement防SQL注入）
java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserService {
/**
* 用户注册
* @return true-注册成功, false-用户名已存在
*/
public boolean register(User user) {
String checkSql = "SELECT id FROM user WHERE username = ? AND deleted = 0";
String insertSql = "INSERT INTO user(username, password, email, phone) VALUES(?, ?, ?, ?)";
try (Connection conn = DBUtil.getConnection()) {
// 1. 检查用户名是否已存在
try (PreparedStatement checkStmt = conn.prepareStatement(checkSql)) {
checkStmt.setString(1, user.getUsername());
try (ResultSet rs = checkStmt.executeQuery()) {
if (rs.next()) return false;
}
}
// 2. 插入新用户
try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
insertStmt.setString(1, user.getUsername());
insertStmt.setString(2, user.getPassword());
insertStmt.setString(3, user.getEmail());
insertStmt.setString(4, user.getPhone());
return insertStmt.executeUpdate() > 0;
}
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}
八、登录功能
java
public User login(String username, String password) {
String sql = "SELECT id, username, email, phone, news_count FROM user WHERE username = ? AND password = ? AND deleted = 0";
try (Connection conn = DBUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setString(1, username);
stmt.setString(2, password);
try (ResultSet rs = stmt.executeQuery()) {
if (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setEmail(rs.getString("email"));
user.setPhone(rs.getString("phone"));
user.setNewsCount(rs.getInt("news_count"));
return user;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
九、事务管理（发布新闻）
事务特性（ACID）
特性 说明
原子性 一组操作要么全部成功，要么全部失败
一致性 操作前后数据状态保持一致
隔离性 并发事务互不干扰
持久性 提交后数据永久保存
事务关键方法
java
Connection conn = DBUtil.getConnection();
conn.setAutoCommit(false); // 开启事务
conn.commit(); // 提交
conn.rollback(); // 回滚
发布新闻实现
java
public class NewsService {
public boolean publishNews(News news) {
String insertSql = "INSERT INTO news(title, content, author_id, author_name) VALUES(?, ?, ?, ?)";
String updateSql = "UPDATE user SET news_count = news_count + 1 WHERE id = ?";
Connection conn = null;
try {
conn = DBUtil.getConnection();
conn.setAutoCommit(false);
// 1. 插入新闻
try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
insertStmt.setString(1, news.getTitle());
insertStmt.setString(2, news.getContent());
insertStmt.setInt(3, news.getAuthorId());
insertStmt.setString(4, news.getAuthorName());
if (insertStmt.executeUpdate() == 0) {
throw new SQLException("新闻插入失败");
}
}
// 2. 更新用户news_count
try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
updateStmt.setInt(1, news.getAuthorId());
if (updateStmt.executeUpdate() == 0) {
throw new SQLException("用户更新失败");
}
}
conn.commit();
return true;
} catch (SQLException e) {
if (conn != null) {
try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); }
}
e.printStackTrace();
return false;
} finally {
if (conn != null) {
try { conn.setAutoCommit(true); conn.close(); } catch (SQLException e) { e.printStackTrace(); }
}
}
}
}
第三部分：SpringBoot + MyBatis-Plus（课次23-25）
十、项目创建
核心依赖（pom.xml）
xml
十一、配置文件（application.yml）
yaml
server:
port: 8080
servlet:
context-path: /api
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/news_management?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8mb4
username: root
password: 123456
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: Asia/Shanghai
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
map-underscore-to-camel-case: true
global-config:
db-config:
logic-delete-field: deleted
logic-delete-value: 1
logic-not-delete-value: 0
id-type: auto
十二、Lombok常用注解
注解 作用
@Data 生成Getter/Setter、toString、equals、hashCode
@NoArgsConstructor 生成无参构造
@AllArgsConstructor 生成全参构造
@Builder 建造者模式
@Slf4j 日志对象
十三、MyBatis-Plus核心注解
注解 作用
@TableName 指定数据库表名
@TableId(type = IdType.AUTO) 指定主键，自增
@TableField 指定字段映射
@TableField(fill = FieldFill.INSERT) 插入时自动填充
@TableField(fill = FieldFill.INSERT_UPDATE) 插入和更新时自动填充
@TableLogic 逻辑删除字段
@Mapper 标记Mapper接口
十四、实体类
User实体
java
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@TableName("user")
public class User {
@TableId(type = IdType.AUTO)
private Integer id;
private String username;
private String password;
private String email;
private String phone;
private Integer newsCount;
@TableField(value = "create_time", fill = FieldFill.INSERT)
private LocalDateTime createTime;
@TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
private LocalDateTime updateTime;
@TableLogic
private Integer deleted;
}
News实体
java
@Data
@TableName("news")
public class News {
@TableId(type = IdType.AUTO)
private Integer id;
private String title;
private String content;
private Integer authorId;
private String authorName;
private Integer viewCount;
private Integer status;
@TableField(value = "create_time", fill = FieldFill.INSERT)
private LocalDateTime createTime;
@TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
private LocalDateTime updateTime;
@TableLogic
private Integer deleted;
}
十五、自动填充处理器
java
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.time.LocalDateTime;
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now());
this.strictInsertFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());
}
@Override
public void updateFill(MetaObject metaObject) {
this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());
}
}
十六、Mapper接口
java
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper
// 继承BaseMapper，自动获得CRUD方法
}
@Mapper
public interface NewsMapper extends BaseMapper
// 继承BaseMapper，自动获得CRUD方法
}
BaseMapper常用方法
方法 说明
insert(T entity) 插入记录
deleteById(Serializable id) 根据ID删除（逻辑删除）
updateById(T entity) 根据ID更新
selectById(Serializable id) 根据ID查询
selectList(Wrapper
selectPage(Page
selectCount(Wrapper
selectOne(Wrapper
十七、条件构造器 LambdaQueryWrapper
常用方法
方法 说明 示例
eq 等于 wrapper.eq(User::getUsername, "张三")
ne 不等于 wrapper.ne(User::getStatus, 0)
gt / ge 大于 / 大于等于 wrapper.ge(User::getNewsCount, 5)
lt / le 小于 / 小于等于 wrapper.le(User::getNewsCount, 10)
like 模糊匹配 wrapper.like(User::getUsername, "张")
likeLeft %value wrapper.likeLeft(User::getUsername, "张")
likeRight value% wrapper.likeRight(User::getUsername, "张")
in IN查询 wrapper.in(User::getId, Arrays.asList(1,2,3))
between BETWEEN wrapper.between(User::getCreateTime, start, end)
isNull / isNotNull 空判断 wrapper.isNull(User::getPhone)
orderByAsc / orderByDesc 排序 wrapper.orderByDesc(User::getCreateTime)
查询示例
java
@Test
public void testLambdaQuery() {
// 单条件查询
LambdaQueryWrapper
wrapper.eq(User::getUsername, "张三")
.eq(User::getDeleted, 0);
User user = userMapper.selectOne(wrapper);
// 多条件组合 + 排序
LambdaQueryWrapper
wrapper2.like(User::getUsername, "张")
.ge(User::getNewsCount, 3)
.orderByDesc(User::getCreateTime);
List
// 条件判断（避免空值）
String keyword = "张";
LambdaQueryWrapper
wrapper3.like(keyword != null && !keyword.isEmpty(),
User::getUsername, keyword)
.eq(User::getDeleted, 0);
}
十八、分页插件配置与使用
分页插件配置
java
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
paginationInterceptor.setMaxLimit(100L);
interceptor.addInnerInterceptor(paginationInterceptor);
return interceptor;
}
}
分页查询示例
java
@Test
public void testPageQuery() {
// 1. 创建分页对象（当前页，每页大小）
Page
// 2. 构建查询条件
LambdaQueryWrapper
wrapper.eq(News::getStatus, 1)
.orderByDesc(News::getCreateTime);
// 3. 执行分页查询
Page
// 4. 获取分页数据
List
long total = result.getTotal(); // 总记录数
long pages = result.getPages(); // 总页数
long current = result.getCurrent(); // 当前页码
long size = result.getSize(); // 每页大小
}
分页结果封装
java
@Data
public class PageResult
private Long total;
private Long pages;
private Long current;
private Long size;
private List
public static
PageResult
result.setTotal(page.getTotal());
result.setPages(page.getPages());
result.setCurrent(page.getCurrent());
result.setSize(page.getSize());
result.setRecords(page.getRecords());
return result;
}
}
带条件的分页查询
java
@Test
public void testPageWithCondition() {
String keyword = "Spring";
Integer status = 1;
Page
LambdaQueryWrapper
wrapper.like(keyword != null && !keyword.isEmpty(), News::getTitle, keyword)
.eq(status != null, News::getStatus, status)
.orderByDesc(News::getCreateTime);
Page
PageResult
}
十九、启动类与测试Controller
启动类
java
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class NewsApplication {
public static void main(String[] args) {
SpringApplication.run(NewsApplication.class, args);
}
}
测试Controller
java
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.Map;
@RestController
public class HelloController {
@GetMapping("/hello")
public Map<String, String> hello() {
Map<String, String> result = new HashMap<>();
result.put("message", "SpringBoot 3.1.5 启动成功！");
result.put("time", java.time.LocalDateTime.now().toString());
return result;
}
}
访问：http://localhost:8080/api/hello
附录：快速参考
JDBC vs MyBatis-Plus 对比
特性 JDBC原生 MyBatis-Plus
SQL编写 手动编写完整SQL 自动生成CRUD，条件构造器
结果映射 手动rs.getXxx()赋值 自动映射到实体类
SQL注入防护 PreparedStatement 自动使用预编译
分页 手动拼接LIMIT 分页插件自动处理
事务管理 setAutoCommit(false) @Transactional注解
开发效率 低 高
事务管理对比
方式 代码示例 适用场景
JDBC原生 conn.setAutoCommit(false) + commit()/rollback() 学习原理
Spring注解 @Transactional 生产环境推荐
项目结构参考
text
src/main/java/com/example/news/
├── NewsApplication.java # 启动类
├── config/
│ ├── MybatisPlusConfig.java # MyBatis-Plus配置
│ └── MyMetaObjectHandler.java # 自动填充处理器
├── controller/
│ └── HelloController.java # 控制器
├── entity/
│ ├── User.java # 用户实体
│ └── News.java # 新闻实体
├── mapper/
│ ├── UserMapper.java # 用户Mapper
│ └── NewsMapper.java # 新闻Mapper
└── service/
├── UserService.java # 用户服务
└── NewsService.java # 新闻服务
浙公网安备 33010602011771号