springboot操作mysql数据库

 

数据库表结构，需要提前创建好表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

 

1.项目结构

 

2.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>3.2.0</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>org.hxl</groupId>
  <artifactId>demo</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>demo</name>
  <description>SpringBoot参数绑定+统一返回示例</description>

  <dependencies>
    <!-- SpringWeb核心依赖 -->
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- Lombok（简化实体类） -->
    <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>

    <!-- MySQL 驱动 -->
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <scope>runtime</scope>
    </dependency>

    <!-- MyBatis-Plus 启动器 -->
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.5.5</version>
      <exclusions>
        <exclusion>
          <artifactId>mybatis-spring</artifactId>
          <groupId>org.mybatis</groupId>
        </exclusion>
      </exclusions>
    </dependency>

    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>3.0.3</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>

 

3.配置文件application.yml

spring:
  # 数据库配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.1.14:3306/db_test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: mysql
# MyBatis-Plus 配置
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true # 下划线转驼峰
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 打印 SQL 日志
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.demo.entity

 

4.各Java文件代码

Result.java

package org.hxl.common;

import lombok.Data;

/**
 * 全局统一返回结果
 * 规范接口返回格式：状态码 + 消息 + 数据
 */
@Data
public class Result<T> {
    // 状态码：200 成功，500 失败
    private Integer code;
    // 返回消息
    private String msg;
    // 返回数据
    private T data;

    // 成功响应（无数据）
    public static <T> Result<T> success() {
        Result<T> result = new Result<>();
        result.setCode(200);
        result.setMsg("操作成功");
        return result;
    }

    // 成功响应（有数据）
    public static <T> Result<T> success(T data) {
        Result<T> result = new Result<>();
        result.setCode(200);
        result.setMsg("操作成功");
        result.setData(data);
        return result;
    }

    // 失败响应
    public static <T> Result<T> error(String msg) {
        Result<T> result = new Result<>();
        result.setCode(500);
        result.setMsg(msg);
        return result;
    }
}

 

UserController.java

package org.hxl.controller;

import org.hxl.common.Result;
import org.hxl.entity.User;
import jakarta.servlet.http.Cookie;
import jakarta.servlet.http.HttpServletRequest;
import org.hxl.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 演示：请求映射、参数绑定、统一返回结果
 */
@RestController
@RequestMapping("/user") // 统一前缀
public class UserController {

    @Autowired
    private UserService userService;


    /**
     * 新增用户（数据库操作）
     */
    @PostMapping("/db/add")
    public Result<Boolean> addUserDb(@RequestBody User user) {
        boolean result = userService.addUser(user);
        return Result.success(result);
    }

    /**
     * 查询所有用户（数据库操作）
     */
    @GetMapping("/db/list")
    public Result<List<User>> getUserListDb() {
        List<User> list = userService.list();
        return Result.success(list);
    }
}

 

User.java

package org.hxl.entity;

import lombok.Data;

/**
 * 用户实体类（用于参数绑定示例）
 */
@Data
public class User {
    private Long id;
    private String username;
    private Integer age;
    private String email;
}

 

UserMapper.java

package org.hxl.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.hxl.entity.User;
import org.apache.ibatis.annotations.Mapper;

/**
 * BaseMapper 提供了 CRUD 基础方法，无需手动写 SQL
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

 

UserService.java

package org.hxl.service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.hxl.entity.User;
import org.hxl.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * ServiceImpl 封装了 BaseMapper 的方法，简化 Service 层开发
 */

@Service
public class UserService extends ServiceImpl<UserMapper, User> {

    /**
     * 事务控制：@Transactional 保证操作原子性
     */

    @Transactional(rollbackFor = Exception.class)
    public boolean addUser(User user) {
        // 新增用户
        boolean save = this.save(user);
        // 模拟异常：测试事务回滚（注释掉可正常插入）
        // int i = 1 / 0;
        return save;
    }
}

 

5.调用

http://localhost:8080/user/db/add

 

 

http://localhost:8080/user/db/list

 

说明:

idea工具需要安装lombok插件