序
最近搜了好多资源，都没找到详细的、简单的Java连接MySQL实现增删改查操作的，故本篇诞生，跟着操作初学者也可以轻松搞定。
环境
JDK: 1.8.0_202
IDEA: 2023.3.4
MySQL: 5.7+
数据库可视化工具：Navicat 16
SpringBoot: 2.4.5
创建空白项目
新建项目，填写基本信息，最后点击“Create”按钮创建
稍等构建完成就会是这样：删除resources下所有文件夹（用不到的）
依赖导入
在pom.xml中声明导入依赖
<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 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.only.hww.todo</groupId>
<artifactId>demo_mysql</artifactId>
<version>1.0-SNAPSHOT</version>
<name>Archetype - demo_mysql</name>
<url>http://maven.apache.org</url>
<!-- SpringBoot父工程依赖 -->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.5</version>
<relativePath/>
</parent>
<dependencies>
<!-- SpringBootWeb依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- MySQL依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
</dependencies>
</project>
创建数据库
创建数据库的sql
/*
Navicat Premium Data Transfer
Source Server : xampp
Source Server Type : MySQL
Source Server Version : 100428
Source Host : pig-mysql:3306
Source Schema : test_db
Target Server Type : MySQL
Target Server Version : 100428
File Encoding : 65001
Date: 28/02/2024 14:25:44
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', '123456');
INSERT INTO `user` VALUES (2, '李二', '123456Abc');
INSERT INTO `user` VALUES (3, '王四', 'Abc!789');
SET FOREIGN_KEY_CHECKS = 1;
创建工程代码
新建Java资源文件夹：
创建cn.only.hww.demo包
在cn.only.hww.demo包下创建SpringBoot的入口启动类：Application.java
package cn.only.hww.demo;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* date created : Created in 2024/2/28 11:43
* description : SpringBoot 启动类
* class name : Application
*/
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
新建Controller、Service、ServiceImpl；注释写的特别详细，每一行都有
TestController
package cn.only.hww.demo.controller;
import cn.only.hww.demo.service.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
import java.util.Map;
/**
* date created : Created in 2024/2/28 11:49
* description : Controller
* class name : TestController
*/
@RestController
public class TestController {
// 注入Service
@Autowired
TestService service;
/**
* description : 查询所有数据的方法
* method name : get
* param : []
* return : java.util.List<java.lang.Object>
*/
@GetMapping("/list")
public List<Object> get(){
return service.list();
}
/**
* description : 根据id查询数据的方法
* method name : get
* param : []
* return : java.lang.Object
*/
@GetMapping("/list/{id}")
public Object get(@PathVariable("id") String id){
return service.listById(id);
}
/**
* description : 添加一条数据的方法
* method name : insert
* param : [data]
* return : java.lang.Object
*/
@GetMapping("/insert")
public Object insert(@RequestBody Map<String, String> data){
return service.insert(data);
}
/**
* description : 更新数据的方法
* method name : update
* param : [data]
* return : java.lang.Object
*/
@GetMapping("/update")
public Object update(@RequestBody Map<String, String> data){
return service.update(data);
}
/**
* description : 通过id删除数据的方法
* method name : delete
* param : [id]
* return : java.lang.Object
*/
@GetMapping("/delete/{id}")
public Object delete(@PathVariable("id") String id){
return service.deleteById(id);
}
}
TestService
package cn.only.hww.demo.service;
import java.util.List;
import java.util.Map;
/**
* date created : Created in 2024/2/28 11:50
* description : Service
* class name : TestService
*/
public interface TestService {
/**
* description : 获取用户信息列表
* method name : list
* param : []
* return : java.util.List<java.lang.Object>
*/
List<Object> list();
/**
* description : 通过id查询用户信息
* method name : listById
* param : [id]
* return : java.lang.Object
*/
Object listById(String id);
/**
* description : 向用户表中插入数据
* method name : insert
* param : [data]
* return : java.lang.Object
*/
Object insert(Map<String, String> data);
/**
* description : 更新用户信息
* method name : update
* param : [data]
* return : java.lang.Object
*/
Object update(Map<String, String> data);
/**
* description : 通过id删除用户信息
* method name : deleteById
* param : [id]
* return : java.lang.Object
*/
Object deleteById(String id);
}
TestServiceImpl
package cn.only.hww.demo.service.impl;
import cn.only.hww.demo.service.TestService;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* date created : Created in 2024/2/28 11:50
* description : ServiceImpl
* class name : TestServiceImpl
*/
@Service
public class TestServiceImpl implements TestService {
// 修改为你的数据库的地址（localhost）、端口（3306）、数据库名（test_db）
private static final String JDBC_URL = "jdbc:mysql://localhost:3306/test_db";
// 修改为你的数据库的用户名
private static final String USERNAME = "root";
// 修改为你的数据库的密码
private static final String PASSWORD = "123456";
/**
* description : 获取数据库连接的方法
* method name : getConnection
* param : []
* return : Connection
*/
private Connection getConnection() throws SQLException {
return DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
}
/**
* description : 获取用户信息列表
* method name : list
* param : []
* return : java.util.List<java.lang.Object>
*/
@Override
public List<Object> list() {
// 创建一个ArrayList对象，用于存储查询结果
List<Object> result = new ArrayList<>();
try (
// 获取数据库连接
Connection connection = getConnection();
// 准备SQL语句
PreparedStatement statement = connection.prepareStatement("SELECT * FROM user");
// 执行SQL查询，并获取结果集
ResultSet resultSet = statement.executeQuery()) {
// 遍历结果集中的每一行数据
while (resultSet.next()) {
// 创建一个HashMap对象，用于存储每一行数据的列名和值
Map<String, Object> data = new HashMap<>();
// 将id列的值存入HashMap
data.put("id", resultSet.getString("id"));
// 将name列的值存入HashMap
data.put("name", resultSet.getString("name"));
// 将password列的值存入HashMap
data.put("password", resultSet.getString("password"));
// 可以根据实际表结构进行修改和添加其他列的值
// 将包含一行数据的HashMap对象存入结果集
result.add(data);
}
// 捕获可能抛出的SQLException异常
} catch (SQLException throwables) {
// 打印异常信息
throwables.printStackTrace();
}
// 返回查询结果集合
return result;
}
/**
* description : 通过id查询用户信息
* method name : listById
* param : [id]
* return : java.lang.Object
*/
@Override
public Object listById(String id) {
try (
// 获取数据库连接
Connection connection = getConnection();
// 准备查询语句，根据id查询用户信息
PreparedStatement statement = connection.prepareStatement("SELECT * FROM user WHERE id = ?");
) {
// 设置查询语句中的参数，即用户id
statement.setString(1, id);
// 执行查询语句，获取结果集
try (ResultSet resultSet = statement.executeQuery()) {
// 如果结果集中有数据
if (resultSet.next()) {
// 创建一个Map对象，用于存储查询结果
Map<String, Object> data = new HashMap<>();
// 将id列的值存入Map
data.put("id", resultSet.getString("id"));
// 将name列的值存入Map
data.put("name", resultSet.getString("name"));
// 将password列的值存入Map
data.put("password", resultSet.getString("password"));
// 可以根据实际表结构进行修改和添加其他列的值
// 返回查询结果
return data;
}
}
} catch (SQLException throwables) {
// 捕获可能抛出的SQLException异常
// 打印异常信息
throwables.printStackTrace();
}
// 如果没有查询到数据，返回null
return null;
}
/**
* description : 向用户表中插入数据
* method name : insert
* param : [data]
* return : java.lang.Object
*/
@Override
public Object insert(Map<String, String> data) {
try (
// 获取数据库连接
Connection connection = getConnection();
// 准备插入语句，插入用户的姓名和密码
PreparedStatement statement = connection.prepareStatement("INSERT INTO user (name, password) VALUES (?, ?)");
) {
// 设置参数，即要插入的姓名和密码
// 设置姓名
statement.setString(1, data.get("name"));
// 设置密码
statement.setString(2, data.get("password"));
// 设置其他参数，以此类推
// 执行插入操作，获取受影响的行数
int rowsAffected = statement.executeUpdate();
// 如果有行受影响（即插入成功）
if (rowsAffected > 0) {
// 返回插入成功的消息
return "Insert successful";
}
} catch (SQLException throwables) {
// 捕获可能抛出的SQLException异常
// 打印异常信息
throwables.printStackTrace();
}
// 如果插入失败，返回插入失败的消息
return "Insert failed";
}
/**
* description : 更新用户信息
* method name : update
* param : [data]
* return : java.lang.Object
*/
@Override
public Object update(Map<String, String> data) {
try (
// 获取数据库连接
Connection connection = getConnection();
// 准备更新语句，更新用户的姓名和密码
PreparedStatement statement = connection.prepareStatement("UPDATE user SET name = ?, password = ? WHERE id = ?");
) {
// 设置参数，即要更新的姓名、密码和用户id
// 设置姓名
statement.setString(1, data.get("name"));
// 设置密码
statement.setString(2, data.get("password"));
// 设置用户id
statement.setString(3, data.get("id"));
// 执行更新操作，获取受影响的行数
int rowsAffected = statement.executeUpdate();
// 如果有行受影响（即更新成功）
if (rowsAffected > 0) {
// 返回更新成功的消息
return "Update successful";
}
} catch (SQLException throwables) {
// 捕获可能抛出的SQLException异常
// 打印异常信息
throwables.printStackTrace();
}
// 如果更新失败，返回更新失败的消息
return "Update failed";
}
/**
* description : 通过id删除用户信息
* method name : deleteById
* param : [id]
* return : java.lang.Object
*/
@Override
public Object deleteById(String id) {
try (
// 获取数据库连接
Connection connection = getConnection();
// 准备删除语句，根据用户id删除用户信息
PreparedStatement statement = connection.prepareStatement("DELETE FROM user WHERE id = ?");
) {
// 设置参数，即要删除的用户id
// 设置用户id
statement.setString(1, id);
// 执行删除操作，获取受影响的行数
int rowsAffected = statement.executeUpdate();
// 如果有行受影响（即删除成功）
if (rowsAffected > 0) {
// 返回删除成功的消息
return "Delete successful";
}
} catch (SQLException throwables) {
// 捕获可能抛出的SQLException异常
// 打印异常信息
throwables.printStackTrace();
}
// 如果删除失败，返回删除失败的消息
return "Delete failed";
}
}
最后的结构是这样：
测试
获取用户信息列表,可以看到获取到了所有用户信息
别的就不一一展示了，这是ApiFox的导入语句，新建一个josn后缀的文件，复制进去导入即可：
{
"apifoxProject": "1.0.0",
"$schema": {
"app": "apifox",
"type": "project",
"version": "1.2.0"
},
"info": {
"name": "test",
"description": "",
"mockRule": {
"rules": [],
"enableSystemRule": true
}
},
"apiCollection": [{
"name": "根目录",
"auth": {},
"parentId": 0,
"serverId": "",
"description": "",
"identityPattern": {
"httpApi": {
"type": "methodAndPath",
"bodyType": "",
"fields": []
}
},
"preProcessors": [{
"id": "inheritProcessors",
"type": "inheritProcessors",
"data": {}
}],
"postProcessors": [{
"id": "inheritProcessors",
"type": "inheritProcessors",
"data": {}
}],
"inheritPostProcessors": {},
"inheritPreProcessors": {},
"items": [{
"name": "获取所有用户信息",
"api": {
"id": "150758376",
"method": "get",
"path": "/list",
"parameters": {},
"auth": {},
"commonParameters": {
"query": [],
"body": [],
"cookie": [],
"header": []
},
"responses": [{
"id": "404759356",
"name": "成功",
"code": 200,
"contentType": "json",
"jsonSchema": {
"type": "array",
"items": {
"type": "object",
"properties": {
"password": {
"type": "string"
},
"name": {
"type": "string"
},
"id": {
"type": "string"
}
},
"required": ["password", "name", "id"]
}
}
}],
"responseExamples": [{
"name": "成功示例",
"data": "[\r\n {\r\n \"password\": \"123456\",\r\n \"name\": \"张三\",\r\n \"id\": \"1\"\r\n },\r\n {\r\n \"password\": \"123456Abc\",\r\n \"name\": \"李二\",\r\n \"id\": \"2\"\r\n },\r\n {\r\n \"password\": \"Abc!789\",\r\n \"name\": \"王四\",\r\n \"id\": \"3\"\r\n }\r\n]",
"responseId": 404759356,
"ordering": 1
}],
"requestBody": {
"type": "none",
"parameters": []
},
"description": "",
"tags": [],
"status": "developing",
"serverId": "",
"operationId": "",
"sourceUrl": "",
"ordering": 10,
"cases": [],
"mocks": [],
"customApiFields": "{}",
"advancedSettings": {
"disabledSystemHeaders": {}
},
"mockScript": {},
"codeSamples": [],
"commonResponseStatus": {},
"responseChildren": ["BLANK.404759356"],
"preProcessors": [],
"postProcessors": [],
"inheritPostProcessors": {},
"inheritPreProcessors": {}
}
}, {
"name": "通过id获取用户信息",
"api": {
"id": "150759092",
"method": "get",
"path": "/list/{id}",
"parameters": {
"path": [{
"id": "id#0",
"name": "id",
"required": true,
"description": "",
"type": "string",
"enable": true,
"example": "1"
}]
},
"auth": {},
"commonParameters": {
"query": [],
"body": [],
"cookie": [],
"header": []
},
"responses": [{
"id": "404761145",
"name": "成功",
"code": 200,
"contentType": "json",
"jsonSchema": {
"type": "object",
"properties": {
"password": {
"type": "string"
},
"name": {
"type": "string"
},
"id": {
"type": "string"
}
},
"required": ["password", "name", "id"]
}
}],
"responseExamples": [{
"name": "成功示例",
"data": "{\r\n \"password\": \"123456\",\r\n \"name\": \"张三\",\r\n \"id\": \"1\"\r\n}",
"responseId": 404761145,
"ordering": 1
}],
"requestBody": {
"type": "none",
"parameters": []
},
"description": "",
"tags": [],
"status": "developing",
"serverId": "",
"operationId": "",
"sourceUrl": "",
"ordering": 20,
"cases": [],
"mocks": [],
"customApiFields": "{}",
"advancedSettings": {
"disabledSystemHeaders": {},
"isDefaultUrlEncoding": 1
},
"mockScript": {},
"codeSamples": [],
"commonResponseStatus": {},
"responseChildren": ["BLANK.404761145"],
"preProcessors": [],
"postProcessors": [],
"inheritPostProcessors": {},
"inheritPreProcessors": {}
}
}, {
"name": "添加一条用户数据",
"api": {
"id": "150760844",
"method": "get",
"path": "/insert",
"parameters": {},
"auth": {},
"commonParameters": {
"query": [],
"body": [],
"cookie": [],
"header": []
},
"responses": [{
"id": "404721926",
"name": "成功",
"code": 200,
"contentType": "raw",
"jsonSchema": {
"type": "object",
"properties": {}
}
}],
"responseExamples": [],
"requestBody": {
"type": "application/json",
"parameters": [],
"jsonSchema": {
"type": "object",
"properties": {
"name": {
"type": "string"
},
"password": {
"type": "string"
}
},
"required": ["name", "password"],
"x-apifox-orders": ["name", "password"]
},
"example": "{\r\n \"name\": \"王麻子\",\r\n \"password\": \"mazi123\"\r\n}"
},
"description": "",
"tags": [],
"status": "developing",
"serverId": "",
"operationId": "",
"sourceUrl": "",
"ordering": 30,
"cases": [],
"mocks": [],
"customApiFields": "{}",
"advancedSettings": {
"disabledSystemHeaders": {},
"isDefaultUrlEncoding": 1
},
"mockScript": {},
"codeSamples": [],
"commonResponseStatus": {},
"responseChildren": ["BLANK.404721926"],
"preProcessors": [],
"postProcessors": [],
"inheritPostProcessors": {},
"inheritPreProcessors": {}
}
}, {
"name": "更新用户数据",
"api": {
"id": "150761060",
"method": "get",
"path": "/update",
"parameters": {},
"auth": {},
"commonParameters": {
"query": [],
"body": [],
"cookie": [],
"header": []
},
"responses": [{
"id": "404724452",
"name": "成功",
"code": 200,
"contentType": "raw",
"jsonSchema": {
"type": "object",
"properties": {}
}
}],
"responseExamples": [],
"requestBody": {
"type": "application/json",
"parameters": [],
"jsonSchema": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"password": {
"type": "string"
}
},
"required": ["id", "name", "password"],
"x-apifox-orders": ["id", "name", "password"]
},
"example": "{\r\n \"id\":\"4\",\r\n \"name\": \"王麻子\",\r\n \"password\": \"mazi124\"\r\n}"
},
"description": "",
"tags": [],
"status": "developing",
"serverId": "",
"operationId": "",
"sourceUrl": "",
"ordering": 40,
"cases": [],
"mocks": [],
"customApiFields": "{}",
"advancedSettings": {
"disabledSystemHeaders": {},
"isDefaultUrlEncoding": 1
},
"mockScript": {},
"codeSamples": [],
"commonResponseStatus": {},
"responseChildren": ["BLANK.404724452"],
"preProcessors": [],
"postProcessors": [],
"inheritPostProcessors": {},
"inheritPreProcessors": {}
}
}, {
"name": "通过id删除用户数据",
"api": {
"id": "150762960",
"method": "get",
"path": "/delete/{id}",
"parameters": {
"path": [{
"id": "id#0",
"name": "id",
"required": true,
"description": "",
"type": "string",
"enable": true,
"example": "4"
}]
},
"auth": {},
"commonParameters": {
"query": [],
"body": [],
"cookie": [],
"header": []
},
"responses": [{
"id": "404724695",
"name": "成功",
"code": 200,
"contentType": "json",
"jsonSchema": {
"type": "object",
"properties": {}
}
}],
"responseExamples": [],
"requestBody": {
"type": "none",
"parameters": []
},
"description": "",
"tags": [],
"status": "developing",
"serverId": "",
"operationId": "",
"sourceUrl": "",
"ordering": 50,
"cases": [],
"mocks": [],
"customApiFields": "{}",
"advancedSettings": {
"disabledSystemHeaders": {}
},
"mockScript": {},
"codeSamples": [],
"commonResponseStatus": {},
"responseChildren": ["BLANK.404724695"],
"preProcessors": [],
"postProcessors": [],
"inheritPostProcessors": {},
"inheritPreProcessors": {}
}
}]
}],
"socketCollection": [],
"docCollection": [],
"schemaCollection": [],
"responseCollection": [{
"id": 4079914,
"createdAt": "2024-02-28T05:56:20.000Z",
"updatedAt": "2024-02-28T05:56:20.000Z",
"deletedAt": null,
"name": "根目录",
"type": "root",
"description": "",
"children": [],
"auth": {},
"projectId": 4075410,
"projectBranchId": 0,
"parentId": 0,
"createdById": 1733184,
"updatedById": 1733184,
"items": []
}],
"apiTestCaseCollection": [{
"name": "根目录",
"children": [],
"items": []
}],
"testCaseReferences": [],
"environments": [],
"commonScripts": [],
"databaseConnections": [],
"globalVariables": [],
"commonParameters": null,
"projectSetting": {
"id": "3510269",
"auth": {},
"servers": [{
"id": "default",
"name": "默认服务"
}],
"gateway": [],
"language": "zh-CN",
"apiStatuses": ["developing", "testing", "released", "deprecated"],
"mockSettings": {},
"preProcessors": [],
"postProcessors": [],
"advancedSettings": {},
"initialDisabledMockIds": [],
"cloudMock": {
"security": "free",
"enable": false,
"tokenKey": "apifoxToken"
}
},
"projectAssociations": []
}
最后
最大化的减少代码，连实体类都没用添加，自己可以试着将数据交互换成User实体，可以以此为基础去完成注册、登录接口的实现。
1218
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
