项目整理目录结构:
└── aiosms
├── pom.xml
└── src
├── main
│ ├── java
│ │ └── com
│ │ └── ivandu
│ │ └── aiosms
│ │ ├── Aiosms.java
│ │ ├── controller
│ │ │ └── UserController.java
│ │ ├── entity
│ │ │ └── User.java
│ │ ├── mapper
│ │ │ └── UserMapper.java
│ │ └── service
│ │ ├── UserService.java
│ │ └── impl
│ │ └── UserServiceImpl.java
│ └── resources
│ ├── application.properties
│ ├── mapper
│ │ └── UserMapper.xml
│ └── mybatis-config.xml
└── test
└── java
项目POM:
<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/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.10</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.ivandu.aiosms</groupId>
<artifactId>aiosms</artifactId>
<version>${revision}</version>
<packaging>jar</packaging>
<name>${project.artifactId}</name>
<properties>
<revision>1.0.0</revision>
<java.version>17</java.version>
<maven.compiler.source>${java.version}</maven.compiler.source>
<maven.compiler.target>${java.version}</maven.compiler.target>
<maven.compiler.compilerVersion>${java.version}</maven.compiler.compilerVersion>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven-compiler-plugin.version>3.13.0</maven-compiler-plugin.version>
<spring.boot.version>${project.parent.version}</spring.boot.version>
<spring.webmvc.version>6.1.13</spring.webmvc.version>
<mysql.version>8.4.0</mysql.version>
<mybatis.version>3.5.16</mybatis.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>${mysql.version}</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
<repositories>
<repository>
<id>huaweicloud</id>
<name>huawei</name>
<url>https://mirrors.huaweicloud.com/repository/maven/</url>
</repository>
<repository>
<id>aliyunmaven</id>
<name>aliyun</name>
<url>https://maven.aliyun.com/repository/public</url>
</repository>
</repositories>
</project>
实体类(有参构造函数及setter、getter省略):
package com.ivandu.aiosms.entity;
import java.io.Serial;
import java.io.Serializable;
import java.time.LocalDateTime;
public class User implements Serializable {
@Serial
private static final long serialVersionUID = 1L;
private Long userId;
private String userType;
private String fullName;
private String userName;
private String nickName;
private String password;
private Integer gender;
private String avatar;
private String department;
private String cellphone;
private String email;
private String address;
private Boolean status;
private Boolean isDeleted;
private LocalDateTime createTime;
private LocalDateTime updateTime;
private LocalDateTime loginTime;
private String loginIp;
private String remark;
public User() {
}
Mapper接口:
package com.ivandu.aiosms.mapper;
import com.ivandu.aiosms.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper {
User selectUserById(Long id);
}
UserService接口:
package com.ivandu.aiosms.service;
import com.ivandu.aiosms.entity.User;
public interface UserService {
User getUserById(Long id);
}
UserService实现类:
package com.ivandu.aiosms.service.impl;
import com.ivandu.aiosms.entity.User;
import com.ivandu.aiosms.mapper.UserMapper;
import com.ivandu.aiosms.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl implements UserService {
private UserMapper userMapper;
@Autowired
public void setUserMapper(UserMapper userMapper) {
this.userMapper = userMapper;
}
@Override
public User getUserById(Long id) {
return userMapper.selectUserById(id);
}
}
Controller类:
package com.ivandu.aiosms.controller;
import com.ivandu.aiosms.entity.User;
import com.ivandu.aiosms.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/user")
public class UserController {
private UserService userService;
@Autowired
public void setUserService(UserService userService) {
this.userService = userService;
}
@GetMapping("/{id}")
public User findUserById(@PathVariable Long id) {
return userService.getUserById(id);
}
}
启动类:
package com.ivandu.aiosms;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Aiosms {
public static void main(String[] args) {
SpringApplication.run(Aiosms.class, args);
}
}
配置文件properties:
server.port=10100
spring.main.banner-mode=off
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:23066/aiosms?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=cm_aiosms
spring.datasource.password=C*x#1a2b
mybatis.type-aliases-package=com.ivandu.aiosms.entity
mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.config-location=classpath:mybatis-config.xml
MyBatis配置mybatis-config.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 全局参数 -->
<settings>
<!-- 使全局的映射器启用或禁用缓存 -->
<setting name="cacheEnabled" value="true" />
<!-- 允许JDBC 支持自动生成主键 -->
<setting name="useGeneratedKeys" value="true" />
<!-- 配置默认的执行器.SIMPLE就是普通执行器;REUSE执行器会重用预处理语句(prepared statements);BATCH执行器将重用语句并执行批量更新 -->
<setting name="defaultExecutorType" value="SIMPLE" />
<!-- 指定 MyBatis 所用日志的具体实现 -->
<setting name="logImpl" value="SLF4J" />
<!-- 使用驼峰命名法转换字段 -->
<!-- <setting name="mapUnderscoreToCamelCase" value="true"/> -->
</settings>
</configuration>
Mapper文件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">
<mapper namespace="com.ivandu.aiosms.mapper.UserMapper">
<!-- 基础结果映射 -->
<resultMap id="UserResultMap" type="com.ivandu.aiosms.entity.User">
<id column="user_id" property="userId"/>
<result column="user_type" property="userType"/>
<result column="full_name" property="fullName"/>
<result column="user_name" property="userName"/>
<result column="nick_name" property="nickName"/>
<result column="password" property="password"/>
<result column="gender" property="gender"/>
<result column="avatar" property="avatar"/>
<result column="department" property="department"/>
<result column="cellphone" property="cellphone"/>
<result column="email" property="email"/>
<result column="address" property="address"/>
<result column="status" property="status"/>
<result column="is_deleted" property="isDeleted"/>
<result column="create_time" property="createTime"/>
<result column="update_time" property="updateTime"/>
<result column="login_time" property="loginTime"/>
<result column="login_ip" property="loginIp"/>
<result column="remark" property="remark"/>
</resultMap>
<!-- 通用查询结果列 -->
<sql id="selectUser">
select user_id,
user_type,
full_name,
user_name,
nick_name,
password,
gender,
avatar,
department,
cellphone,
email,
address,
status,
is_deleted,
create_time,
update_time,
login_time,
login_ip,
remark
from aiosms_user
</sql>
<!-- 根据ID查询用户 -->
<select id="selectUserById" resultMap="UserResultMap" parameterType="Long">
<include refid="selectUser"/>
where user_id = #{userId} and is_deleted = 0 and status = 1
</select>
<!-- 查询用户列表 -->
<select id="selectUserList" resultMap="UserResultMap">
<include refid="selectUser"/>
where is_deleted = 0
</select>
<!-- 插入用户 -->
<insert id="insert" parameterType="com.ivandu.aiosms.entity.User">
insert into aiosms_user
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="userType != null">user_type,</if>
<if test="fullName != null">full_name,</if>
<if test="userName != null">user_name,</if>
<if test="nickName != null">nick_name,</if>
<if test="password != null">password,</if>
<if test="gender != null">gender,</if>
<if test="avatar != null">avatar,</if>
<if test="department != null">department,</if>
<if test="cellphone != null">cellphone,</if>
<if test="email != null">email,</if>
<if test="address != null">address,</if>
<if test="status != null">status,</if>
<if test="remark != null">remark,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="userType != null">#{userType},</if>
<if test="fullName != null">#{fullName},</if>
<if test="userName != null">#{userName},</if>
<if test="nickName != null">#{nickName},</if>
<if test="password != null">#{password},</if>
<if test="gender != null">#{gender},</if>
<if test="avatar != null">#{avatar},</if>
<if test="department != null">#{department},</if>
<if test="cellphone != null">#{cellphone},</if>
<if test="email != null">#{email},</if>
<if test="address != null">#{address},</if>
<if test="status != null">#{status},</if>
<if test="remark != null">#{remark},</if>
</trim>
</insert>
<!-- 更新用户 -->
<update id="updateById" parameterType="com.ivandu.aiosms.entity.User">
update aiosms_user
<set>
<if test="userType != null">user_type = #{userType},</if>
<if test="fullName != null">full_name = #{fullName},</if>
<if test="userName != null">user_name = #{userName},</if>
<if test="nickName != null">nick_name = #{nickName},</if>
<if test="password != null">password = #{password},</if>
<if test="gender != null">gender = #{gender},</if>
<if test="avatar != null">avatar = #{avatar},</if>
<if test="department != null">department = #{department},</if>
<if test="cellphone != null">cellphone = #{cellphone},</if>
<if test="email != null">email = #{email},</if>
<if test="address != null">address = #{address},</if>
<if test="status != null">status = #{status},</if>
<if test="remark != null">remark = #{remark},</if>
</set>
where user_id = #{userId} and is_deleted = 0
</update>
<!-- 逻辑删除用户 -->
<update id="deleteById">
update aiosms_user
set is_deleted = 1
where user_id = #{userId}
and is_deleted = 0
</update>
</mapper>
MySQL建库、建表语句及示例数据:
DROP DATABASE IF EXISTS aiosms;
CREATE DATABASE aiosms;
-- CREATE USER 'cm_aiosms'@'10.1.1.3' IDENTIFIED BY 'C*x#1a2b';
CREATE USER 'cm_aiosms'@'%' IDENTIFIED BY 'C*x#1a2b';
-- GRANT ALL PRIVILEGES ON aiosms.* TO 'cm_aiosms'@'10.1.1.3';
-- FLUSH PRIVILEGES;
-- REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'cm_aiosms'@'10.1.1.3';
GRANT ALL PRIVILEGES ON aiosms.* TO 'cm_aiosms'@'%';
FLUSH PRIVILEGES;
-- 用户信息表
DROP TABLE IF EXISTS aiosms_user;
CREATE TABLE aiosms_user
(
user_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '唯一标识',
user_type CHAR(1) NOT NULL DEFAULT '0' COMMENT '用户类型,0普通用户,1管理员,2超级管理员',
full_name VARCHAR(128) NOT NULL COMMENT '用户姓名',
user_name VARCHAR(128) NOT NULL UNIQUE COMMENT '用户账号',
nick_name VARCHAR(64) NOT NULL COMMENT '用户昵称',
password VARCHAR(32) NOT NULL COMMENT '用户密码',
gender TINYINT NOT NULL DEFAULT 2 COMMENT '用户性别(0女 1男 2未知)',
avatar VARCHAR(128) NOT NULL DEFAULT 'images/avatar/0001.jpg' COMMENT '用户头像',
department VARCHAR(128) NOT NULL COMMENT '所属部门',
cellphone VARCHAR(15) NOT NULL UNIQUE COMMENT '手机号',
email VARCHAR(128) NOT NULL UNIQUE COMMENT '邮箱地址',
address VARCHAR(255) COMMENT '通信地址',
status TINYINT NOT NULL DEFAULT 1 COMMENT '当前状态,0禁用,1启用',
is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '注销状态,0正常,1注销',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '信息更新时间',
login_time DATETIME COMMENT '登录时间',
login_ip VARCHAR(45) COMMENT '登录IP',
remark VARCHAR(255) COMMENT '备注信息'
) ENGINE = InnoDB
AUTO_INCREMENT = 1000
DEFAULT CHARSET = utf8mb4 COMMENT ='用户信息表';
INSERT INTO aiosms_user (user_type, full_name, user_name, nick_name, password, gender, avatar, department, cellphone, email, address, status, is_deleted, login_time, login_ip, remark)
VALUES
('0', '张三', 'zhangsan', '张三', '123456', 1, 'images/avatar/0001.jpg', '技术部', '13800000001', 'zhangsan@example.com', '北京市海淀区', 1, 0, '2023-10-01 10:00:00', '192.168.1.1', '测试用户1'),
('1', '李四', 'lisi', '李四', '123456', 0, 'images/avatar/0002.jpg', '市场部', '13800000002', 'lisi@example.com', '上海市浦东新区', 1, 0, '2023-10-01 10:05:00', '192.168.1.2', '测试用户2'),
('2', '王五', 'wangwu', '王五', '123456', 2, 'images/avatar/0003.jpg', '销售部', '13800000003', 'wangwu@example.com', '广州市天河区', 1, 0, '2023-10-01 10:10:00', '192.168.1.3', '测试用户3'),
('0', '赵六', 'zhaoliu', '赵六', '123456', 1, 'images/avatar/0004.jpg', '财务部', '13800000004', 'zhaoliu@example.com', '深圳市南山区', 1, 0, '2023-10-01 10:15:00', '192.168.1.4', '测试用户4'),
('1', '孙七', 'sunqi', '孙七', '123456', 0, 'images/avatar/0005.jpg', '人力资源部', '13800000005', 'sunqi@example.com', '成都市武侯区', 1, 0, '2023-10-01 10:20:00', '192.168.1.5', '测试用户5'),
('2', '周八', 'zhouba', '周八', '123456', 2, 'images/avatar/0006.jpg', '技术部', '13800000006', 'zhouba@example.com', '杭州市西湖区', 1, 0, '2023-10-01 10:25:00', '192.168.1.6', '测试用户6'),
('0', '吴九', 'wujiu', '吴九', '123456', 1, 'images/avatar/0007.jpg', '市场部', '13800000007', 'wujiu@example.com', '南京市鼓楼区', 1, 0, '2023-10-01 10:30:00', '192.168.1.7', '测试用户7'),
('1', '郑十', 'zhengshi', '郑十', '123456', 0, 'images/avatar/0008.jpg', '销售部', '13800000008', 'zhengshi@example.com', '武汉市江汉区', 1, 0, '2023-10-01 10:35:00', '192.168.1.8', '测试用户8'),
('2', '钱十一', 'qianshiyi', '钱十一', '123456', 2, 'images/avatar/0009.jpg', '财务部', '13800000009', 'qianshiyi@example.com', '重庆市渝中区', 1, 0, '2023-10-01 10:40:00', '192.168.1.9', '测试用户9'),
('0', '孙十二', 'sunshier', '孙十二', '123456', 1, 'images/avatar/0010.jpg', '人力资源部', '13800000010', 'sunshier@example.com', '西安市雁塔区', 1, 0, '2023-10-01 10:45:00', '192.168.1.10', '测试用户10');
使用Thunder Client调用,返回结果:
{
"userId": 1001,
"userType": "1",
"fullName": "李四",
"userName": "lisi",
"nickName": "李四",
"password": "123456",
"gender": 0,
"avatar": "images/avatar/0002.jpg",
"department": "市场部",
"cellphone": "13800000002",
"email": "lisi@example.com",
"address": "上海市浦东新区",
"status": true,
"isDeleted": false,
"createTime": "2024-11-04T18:52:09",
"updateTime": "2024-11-04T18:52:09",
"loginTime": "2023-10-01T10:05:00",
"loginIp": "192.168.1.2",
"remark": "测试用户2"
}
部分调用日志:
2024-11-06T14:40:00.437+08:00 INFO 35732 --- [io-10100-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2024-11-06T14:40:00.604+08:00 INFO 35732 --- [io-10100-exec-1] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@254a87aa
2024-11-06T14:40:00.606+08:00 INFO 35732 --- [io-10100-exec-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
以上例子中使用纯MyBatis实现了Spring Boot3中的数据查询功能,其余更新、插入、删除部分已在XML mapper中完成定义,参考上述操作步骤稍加改动即可完成。