springboot操作mysql数据库(单数据源+sql写在xml文件)

 

1.表结构

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 AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

 

2.项目结构

 

3.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.18</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>org.hxl</groupId>
  <artifactId>demo</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>demo</name>
  <description>mybatis测试</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>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
      <version>8.0.33</version> <!-- 适配 MySQL 8.x，5.x 可改用 5.1.49 -->
    </dependency>

    <!-- MyBatis 整合 Spring Boot 启动器 -->
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.3.1</version> <!-- 适配 Spring Boot 2.7 的版本 -->
    </dependency>

  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
        <!-- 关键：明确指定插件版本为 2.7.18，与 parent 版本一致 -->
        <version>2.7.18</version>
        <configuration>
          <excludes>
            <exclude>
              <groupId>org.projectlombok</groupId>
              <artifactId>lombok</artifactId>
            </exclude>
          </excludes>
        </configuration>
      </plugin>
    </plugins>
  </build>

</project>

 

4.配置文件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 配置
mybatis:
  # 实体类别名包路径（简化 Mapper.xml 中的类名引用）
  type-aliases-package: org.hxl.entity
  # Mapper.xml 文件位置（如果放在 resources/mapper 下）
  mapper-locations: classpath:mapper/*.xml
  # 开启驼峰命名自动转换（如数据库字段 user_name → 实体类属性 userName）
  configuration:
    map-underscore-to-camel-case: true
    # 显示 SQL 日志（方便调试）
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

 

5.各类文件

UserController.java

package org.hxl.controller;

import org.hxl.entity.User;
import org.hxl.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * User 控制层，提供 HTTP 接口
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    // 新增用户
    @PostMapping
    public String addUser(@RequestBody User user) {
        int result = userService.addUser(user);
        return result > 0 ? "新增成功" : "新增失败";
    }

    // 删除用户
    @DeleteMapping("/{id}")
    public String deleteUser(@PathVariable Long id) {
        int result = userService.deleteUser(id);
        return result > 0 ? "删除成功" : "删除失败";
    }

    // 更新用户
    @PutMapping
    public String updateUser(@RequestBody User user) {
        int result = userService.updateUser(user);
        return result > 0 ? "更新成功" : "更新失败";
    }

    // 根据ID查询
    @GetMapping("/{id}")
    public User getUserById(@PathVariable Long id) {
        return userService.getUserById(id);
    }

    // 查询所有
    @GetMapping
    public List<User> getAllUsers() {
        return userService.getAllUsers();
    }
}

 

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 org.hxl.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface UserMapper {
    int insert(User user);
    int deleteById(Long id);
    int update(User user);
    User selectById(Long id);
    List<User> selectAll();
}

 

UserService.java

package org.hxl.service;

import org.hxl.entity.User;
import org.hxl.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * User 业务层
 */
@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    // 新增
    public int addUser(User user) {
        return userMapper.insert(user);
    }

    // 删除
    public int deleteUser(Long id) {
        return userMapper.deleteById(id);
    }

    // 更新
    public int updateUser(User user) {
        return userMapper.update(user);
    }

    // 根据ID查询
    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }

    // 查询所有
    public List<User> getAllUsers() {
        return userMapper.selectAll();
    }
}

 

App.java

package org.hxl;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public class App
{
    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
        System.out.println("===== Spring Boot 启动成功 =====");
    }
}

 

6.sql配置文件

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace 必须对应 Mapper 接口的全类名 -->
<mapper namespace="org.hxl.mapper.UserMapper">

    <!-- 结果集映射（可选，驼峰转换已开启时可省略） -->
    <resultMap id="UserResultMap" type="User">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="age" property="age"/>
        <result column="email" property="email"/>
    </resultMap>

    <!-- 新增 -->
    <insert id="insert" parameterType="User">
        INSERT INTO user(username, age, email)
        VALUES(#{username}, #{age}, #{email})
    </insert>

    <!-- 删除 -->
    <delete id="deleteById" parameterType="Long">
        DELETE FROM user WHERE id = #{id}
    </delete>

    <!-- 更新 -->
    <update id="update" parameterType="User">
        UPDATE user
        SET username = #{username}, age = #{age}, email = #{email}
        WHERE id = #{id}
    </update>

    <!-- 根据ID查询 -->
    <select id="selectById" parameterType="Long" resultMap="UserResultMap">
        SELECT * FROM user WHERE id = #{id}
    </select>

    <!-- 查询所有 -->
    <select id="selectAll" resultMap="UserResultMap">
        SELECT * FROM user
    </select>
</mapper>