Spring Boot + MyBatis

POM

<?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 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>2.6.6</version>
        <relativePath/>
    </parent>

    <groupId>org.example</groupId>
    <artifactId>nemo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <!--mysql数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
</project>

application.properties

spring.datasource.url=jdbc:mysql://192.168.1.7:3306/nemo?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=nemo123
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

#开启SQL日志
mybatis.configuration.log-impl= org.apache.ibatis.logging.stdout.StdOutImpl
#mapper的xml扫描
mybatis.mapper-locations=classpath:mapper/*.xml
#开启驼峰命名
mybatis.configuration.mapUnderscoreToCamelCase=true

SQL语句

CREATE TABLE `user_info` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(128) NOT NULL DEFAULT '' COMMENT '用户名',
  `cert_no` varchar(64) NOT NULL DEFAULT '' COMMENT '身份证号',
  `gender` tinyint NOT NULL DEFAULT '0' COMMENT '性别0女1男',
  `active_date` date NOT NULL DEFAULT '1970-01-01' COMMENT '激活时间',
  `inactive_time` datetime NOT NULL DEFAULT '1970-01-01 08:00:01' COMMENT '失效时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_cert_no` (`cert_no`) USING BTREE,
  KEY `idx_username_gender` (`username`,`gender`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

代码

Controller

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping("getAllUser")
    public List<UserInfoEntity> getAllUser(){
        return userService.getAllUser();
    }

    @RequestMapping("addUser")
    public UserInfoEntity addUser(String userName, String certNo, Integer gender ){
        UserInfoEntity userInfo = new UserInfoEntity();
        userInfo.setUsername(userName);
        userInfo.setCertNo(certNo);
        userInfo.setGender(gender);
        return userService.addUser(userInfo);
    }
}

Service

public interface UserService {
    
    List<UserInfoEntity> getAllUser();

    UserInfoEntity addUser(UserInfoEntity userInfo);
}

ServiceImpl

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<UserInfoEntity> getAllUser() {
        List<UserInfoEntity> allUser = userMapper.getAllUser();
        return allUser;
    }

    @Override
    public UserInfoEntity addUser(UserInfoEntity userInfo) {
        userInfo.setActiveDate(new Date());
        userInfo.setInactiveTime(new Date());
        Long id = userMapper.addUser(userInfo);
        return userMapper.getById(id);
    }
}

Mapper

@Repository
@Mapper
public interface UserMapper {

    List<UserInfoEntity> getAllUser();

    UserInfoEntity getById(Long id);

    Long addUser(UserInfoEntity userInfo);
}

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.web.mapper.UserMapper">

    <sql id="Select_BaseSql">
        id,username,cert_no,gender,active_date,inactive_time,create_time,update_time
    </sql>

    <sql id="Insert_BaseSql">
        username,cert_no,gender,active_date,inactive_time
    </sql>

    <select id="getAllUser" resultType="com.web.entity.UserInfoEntity">
        select
        <include refid="Select_BaseSql"></include>
        from user_info;
    </select>

    <select id="getById" resultType="com.web.entity.UserInfoEntity">
        select
        <include refid="Select_BaseSql"></include>
        from user_info
        <where>
            id = #{id}
        </where>
    </select>

    <insert id="addUser" useGeneratedKeys="true" keyProperty="id">
        insert into user_info
        (<include refid="Insert_BaseSql"></include>)
        values
        (#{username},#{certNo},#{gender},#{activeDate},#{inactiveTime});
    </insert>

</mapper>

Entity

@Data
public class UserInfoEntity {

    private Long id;

    private String username;

    private String certNo;

    private Integer gender;

    private Date activeDate;

    private Date inactiveTime;

    private Date createTime;

    private Date updateTime;
}

测试