【SpringBoot整合Mybatis】

1、引入依赖

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>


<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.3.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

2、添加yml配置文件

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/my_uploader?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    username: root
    password: root

mybatis:
  mapper-locations: classpath:mapper/*.xml

3、创建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.task.test.mapper.StudentMapper">

    <resultMap id="student" type="com.task.test.entity.Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <result column="class_id" property="classId"></result>
        <result column="create_time" property="createTime"></result>
        <collection property="banJi" ofType="com.task.test.entity.BanJi">
            <id column="cid" property="id"></id>
            <result column="class_name" property="className"></result>
        </collection>
    </resultMap>

    <sql id="sqlVo">
        select a.id,a.`name`,a.create_time,b.id cid,b.class_name from student a LEFT JOIN banji b on a.class_id=b.id
    </sql>
    <select id="list" resultMap="student">
        <include refid="sqlVo"></include>
        <where>
            <if test="name != null and name!=''">and a.`name` like concat('%',#{name},'%')</if>
        </where>
    </select>

    <update id="update" >
        update student
        <trim prefix="set" suffixOverrides=",">
            <if test="name!=null and name!=''">`name` = #{name},</if>
            <if test="createTime !=null">create_time = date_format(#{createTime},'%Y-%m-%d %H:%i:%S'),</if>
            <if test="classId != null">class_id = #{classId},</if>
        </trim>
        <where>
            id = #{id}
        </where>
    </update>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        insert into student
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name!=null and name!=''">`name`,</if>
            <if test="createTime!=null">create_time,</if>
            <if test="classId!=null">class_id,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name!=null and name!=''">#{name},</if>
            <if test="createTime!=null">date_format(NOW(),'%Y-%m-%d %H:%i:%S'),</if>
            <if test="classId!=null">#{classId},</if>
        </trim>
    </insert>
<!--    <foreach collection="studentClassRelationList" item="item" open="(" separator="," close=")">-->
<!--        #{item.id}-->
<!--    </foreach>-->
    <delete id="delete">
        delete from student where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>
</mapper>

4、mapper接口类

@Mapper
public interface StudentMapper {

    List<Student> list(Student student);

    int update(Student student);

    int insert(Student student);

    int delete(@Param("ids") Long[] ids);
}

5、service接口类

public interface StudentService {

    PageInfo<Student> list(Student student);

    int update(Student student);

    int insert(Student student);

    int delete(Long[] ids);
}

6、service实现类

@Service
    public class StudentServiceImpl implements StudentService {

        @Autowired
        private StudentMapper studentMapper;
        @Override
        public PageInfo<Student> list(Student student) {
            PageHelper.startPage(student.getPageNum()==null?1:student.getPageNum(),student.getPageSize()==null?2:student.getPageSize());
            List<Student> list = studentMapper.list(student);
            PageInfo<Student> studentPageInfo = new PageInfo<>(list);
            //判断数据库数据是否存在
            Optional<Student> studentOptional=studentMapper.studentOptional(student);
            if (tbTemplate.isPresent()) {
                System.out.println("存在");
            }else{
                System.out.println("不存在");
            }
            return studentPageInfo;
        }

        @Override
        public int update(Student student) {
            return studentMapper.update(student);
        }

        @Override
        public int insert(Student student) {
            return studentMapper.insert(student);
        }

        @Override
        public int delete(Long[] ids) {
            return studentMapper.delete(ids);
        }
    }

7、controller类

@RestController
@RequestMapping("/crud")
public class CrudController {

    private static final Logger _log = LoggerFactory.getLogger(CrudController.class);
    @Autowired
    private StudentService studentService;
    @GetMapping("/list")
    public PageInfo<Student> list(Student student){
        _log.info("查询接口");
        PageInfo<Student> list = studentService.list(student);
        return list;
    }

    @PostMapping("update")
    public Integer update(@RequestBody Student student){
        int update = studentService.update(student);
        return update;
    }

    @PostMapping("insert")
    public Integer insert(@RequestBody Student student){
        int insert = studentService.insert(student);
        return insert;
    }

    @GetMapping("delete/{ids}")
    public Integer delete(@PathVariable Long[] ids){
        int delete = studentService.delete(ids);
        return delete;
    }

}

8、查询结果

{

    "total": 2,

    "list": [

        {

            "id": 49,

            "name": "小龙1",

            "createTime": "2022-12-15 14:45:40",

            "banJi": {

                "id": 1,

                "className": "初一2班"

            }

        }

    ],

    "pageNum": 2,

    "pageSize": 1,

    "size": 1,

    "startRow": 2,

    "endRow": 2,

    "pages": 2,

    "prePage": 1,

    "nextPage": 0,

    "isFirstPage": false,

    "isLastPage": true,

    "hasPreviousPage": true,

    "hasNextPage": false,

    "navigatePages": 8,

    "navigatepageNums": [

        1,

        2

    ],

    "navigateFirstPage": 1,

    "navigateLastPage": 2

}