SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子

菜单

一、如何导入Lin-CMS
1.1下载源码及IDEA项目导入
1.2数据库导入
1.3修改配置文件
1.4运行

二、MyBatis查询例子
三、学习CRUD接口
- 3.1 Mapper CRUD 提供的方法
四、MyBatis插入例子
五、MyBatis注解方式

一、如何导入Lin-CMS

1.1下载源码及IDEA项目导入

首先git下载稳定版本

https://github.com/TaleLin/lin-cms-spring-boot/tree/v0.2.1-RELEASE

下载后解压缩，用IDEA工具导入项目

1.2数据库导入

数据库导入（main/resources/schema.sql）
IDEA连接mysql后，打开.sql文件，右键“RUN ***.sql”

1.3修改配置文件

修改配置文件，application-dev.properties
修改数据库url和用户密码

spring:
  # 数据源配置，请修改为你项目的实际配置
  datasource:
    username: "root"
    password: "123"
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/missyou?useSSL=false&serverTimezone=UTC&characterEncoding=UTF8

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图

设置 application-dev.properties ，打印sql

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #开启SQL语句打印

1.4运行

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图1 — 运行正常！

二、MyBatis查询例子

2.1、修改pom.xml，添加依赖mybatis和mysql依赖

注意：引入MyBatisPlus依赖也能实现MyBatis功能

<!--MySQL JDBC驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-generator</artifactId>
    <version>3.4.1</version>
</dependency>

2.2在application处添加包扫描

为了规范管理，在recoures下创建mapper，用于存放mybatis的xml

@MapperScan(basePackages = {"io.github.talelin.latticy.mapper"})

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图2

2.3在model下创建BannerDO

跟JPA一样，先新建一个model

package io.github.talelin.latticy.model;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
@TableName("banner")
public class BannerDO {
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    private String name;

    private String title;

    private String description;

    private String img;

    @JsonIgnore
    private Date createTime;

    @JsonIgnore
    private Date updateTime;

    @JsonIgnore
    @TableLogic
    private Date deleteTime;
}

mybatis plus 看这篇就够了，一发入魂 – 掘金 (juejin.cn)

关于MybatisBO的注解讲解

@TableName：注解在类上，指定类和数据库表的映射关系
@TableId：注解在实体类的某一字段上，表示这个字段对应数据库表的主键。
1. AUTO：数据库ID自增，依赖于数据库。
2. NONE：若在代码中没有手动设置主键，则会根据主键的全局策略自动生成（默认的主键全局策略是基于雪花算法的自增ID）
3. INPUT：需要手动设置主键，若不设置。插入操作生成SQL语句时，主键这一列的值会是null
4. ASSIGN_ID：当没有手动设置主键，即实体类中的主键属性为空时，才会自动填充，使用雪花算法
5. ASSIGN_UUID：当实体类的主键属性为空时，才会自动填充，使用UUID
@TableField：注解在某一字段上，指定Java实体类的字段和数据库表的列的映射关系。
- 排除非表字段：设置exist属性为false
@Version ：乐观锁注解
@TableLogic：逻辑删除注解

2.4在mapper处添加BannerMapper

类似于JPA创建Repository,记得打上@Repository注解

package io.github.talelin.latticy.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import io.github.talelin.latticy.model.BannerDO;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BannerMapper  extends BaseMapper<BannerDO> {
    List<BannerDO> getAllBanners();
}

2.5创建接口TestSleeveService和类TestSleeveImpl

2.5.1创建接口TestSleeveService

package io.github.talelin.latticy.service;

import io.github.talelin.latticy.model.BannerDO;
import java.util.List;

public interface TestSleeveService {
    public List<BannerDO> getBanners();
}

2.5.2接口实现类TestSleeveImpl

package io.github.talelin.latticy.service.impl;

import io.github.talelin.latticy.mapper.BannerMapper;
import io.github.talelin.latticy.model.BannerDO;
import io.github.talelin.latticy.service.TestSleeveService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class TestSleeveImpl implements TestSleeveService {
    /* 在mapper创建BannerMapper接口,类似于JPA的Repository*/
    @Autowired
    private BannerMapper bannerMapper;

    public List<BannerDO> getBanners() {
        return this.bannerMapper.getAllBanners();
    }
}

2.6创建BannerMapper.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 路径是需要匹配Repository -->
<mapper namespace="io.github.talelin.latticy.mapper.BannerMapper">
    <!-- type 填写对应的model -->
    <resultMap id="BaseResultMap" type="io.github.talelin.latticy.model.BannerDO">
        <id column="id" property="id"/>
        <result column="img" property="img"/>
        <result column="type" property="type"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="delete_time" property="deleteTime"/>
        <result column="title" property="title"/>
        <result column="name" property="name"/>
    </resultMap>
    
    <!-- id 填写service的方法名 -->
    <select id="getAllBanners" resultMap="BaseResultMap">
        SELECT *
        FROM banner
    </select>

</mapper>

2.7创建TestSleeveController

package io.github.talelin.latticy.controller.v1;

import io.github.talelin.latticy.model.BannerDO;

import io.github.talelin.latticy.service.TestSleeveService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RequestMapping("/v1/test")
@RestController
public class TestSleeveController {

    @Autowired
    private TestSleeveService testSleeveService;
    @GetMapping("/test1")

    public List<BannerDO> test1() {
        return testSleeveService.getBanners();
    }
}

运行测试！完成

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图3

三、学习CRUD接口

MyBatisplus提供了两种接口，可以让我们使用

Mapper CRUD：Mapper继承 BaseMapper
Service CRUD：Service层接口继承 IService

3.1 Mapper CRUD 提供的方法

insert(T entity) 插入一条记录
deleteById(Serializable id) 根据主键id删除一条记录
delete(Wrapper wrapper) 根据条件构造器wrapper进行删除
selectById(Serializable id) 根据主键id进行查找
selectBatchIds(Collection idList) 根据主键id进行批量查找
selectByMap(Map map) 根据map中指定的列名和列值进行等值匹配查找
selectMaps(Wrapper wrapper) 根据 wrapper 条件，查询记录，将查询结果封装为一个Map，Map的key为结果的列，value为值
selectList(Wrapper wrapper) 根据条件构造器 wrapper 进行查询
update(T entity, Wrapper wrapper) 根据条件构造器 wrapper 进行更新

下面主要讲解一些特殊的用法

以下的例子的Mapper都需要继承BaseMapper

3.1.1 selectMaps

3.1.1.1只查部分列

@Test
public void test3() {
	QueryWrapper<User> wrapper = new QueryWrapper<>();
	wrapper.select("manager_id", "age");
	List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
	maps.forEach(System.out::println);
}

3.1.1.2 进行数据统计

// 按照直属上级进行分组，查询每组的平均年龄，最大年龄，最小年龄
/**
select avg(age) avg_age ,min(age) min_age, max(age) max_age from user group by manager_id having sum(age) < 500;
**/

@Test
public void test3() {
	QueryWrapper<User> wrapper = new QueryWrapper<>();
	wrapper.select("manager_id", "avg(age) avg_age", "min(age) min_age", "max(age) max_age")
			.groupBy("manager_id").having("sum(age) < {0}", 500);
	List<Map<String, Object>> maps = userMapper.selectMaps(wrapper);
	maps.forEach(System.out::println);
}

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图4

3.1.2 selectObjs：只会返回第一个字段（第一列）的值，其他字段会被舍弃

	@Test
	public void test3() {
		QueryWrapper<User> wrapper = new QueryWrapper<>();
		wrapper.select("id", "name").like("name", "黄");
		List<Object> objects = userMapper.selectObjs(wrapper);
		objects.forEach(System.out::println);
	}

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图5

3.1.3selectCount 查询满足条件的总数

使用这个方法，不能调用QueryWrapper的select方法设置要查询的列了。这个方法会自动添加select count(1)

	@Test
	public void test3() {
		QueryWrapper<User> wrapper = new QueryWrapper<>();
		wrapper.like("name", "黄");

		Integer count = userMapper.selectCount(wrapper);
		System.out.println(count);
	}

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图6

四、MyBatis插入例子

4.1、创建BannerBO

4.2在mapper/BannerMapper追加方法

package io.github.talelin.latticy.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import io.github.talelin.latticy.model.BannerDO;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BannerMapper  extends BaseMapper<BannerDO> {
    List<BannerDO> getAllBanners();

    long insertBanner(BannerDO bannerDO);
}

4.3修改Service的接口和方法

4.3.1TestSleeveService接口

package io.github.talelin.latticy.service;

import io.github.talelin.latticy.model.BannerDO;
import java.util.List;

public interface TestSleeveService {
    public List<BannerDO> getBanners();

    public long insertBanner();
}

4.3.2TestSleeveImpl代码

package io.github.talelin.latticy.service.impl;

import io.github.talelin.latticy.mapper.BannerMapper;
import io.github.talelin.latticy.model.BannerDO;
import io.github.talelin.latticy.service.TestSleeveService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class TestSleeveImpl implements TestSleeveService {
    /* 在mapper创建BannerMapper接口,类似于JPA的Repository*/
    @Autowired
    private BannerMapper bannerMapper;

    public List<BannerDO> getBanners() {
        return this.bannerMapper.getAllBanners();
    }

    public long insertBanner() {
        BannerDO bannerDO = new BannerDO();
        bannerDO.setName("NewBanner");
        bannerDO.setTitle("NewBannerTitle");

        bannerMapper.insertBanner(bannerDO);
        return bannerDO.getId();
    }
}

4.4追加insert到BannerMapper.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 路径是需要匹配Repository -->
<mapper namespace="io.github.talelin.latticy.mapper.BannerMapper">
    <!-- type 填写对应的model -->
    <resultMap id="BaseResultMap" type="io.github.talelin.latticy.model.BannerDO">
        <id column="id" property="id"/>
        <result column="img" property="img"/>
        <result column="type" property="type"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="delete_time" property="deleteTime"/>
        <result column="title" property="title"/>
        <result column="name" property="name"/>
    </resultMap>

    <!-- id 填写service的方法名 -->
    <select id="getAllBanners" resultMap="BaseResultMap">
        SELECT *
        FROM banner
    </select>
    <!-- id 填写service的插入方法名 useGeneratedKeys设置插入后返回主键-->
    <insert id="insertBanner" useGeneratedKeys="true" keyProperty="id"
            parameterType="io.github.talelin.latticy.model.BannerDO">
        INSERT INTO banner(name, title)
        VALUES (#{name}, #{title})
    </insert>
</mapper>

4.5追加test方法在controller

package io.github.talelin.latticy.controller.v1;

import io.github.talelin.latticy.model.BannerDO;

import io.github.talelin.latticy.service.TestSleeveService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RequestMapping("/v1/test")
@RestController
public class TestSleeveController {

    @Autowired
    private TestSleeveService testSleeveService;
    @GetMapping("/test1")

    public List<BannerDO> test1() {
        return testSleeveService.getBanners();
    }

    @GetMapping("/test2")
    public long test2() {
        return testSleeveService.insertBanner();
    }
}

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图7

五、MyBatis注解方式

5.1@Select查看例子

5.1.1创建BannerBO

5.1.2修改接口BannerMapper

package io.github.talelin.latticy.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import io.github.talelin.latticy.model.BannerDO;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BannerMapper  extends BaseMapper<BannerDO> {
    List<BannerDO> getAllBanners();

    long insertBanner(BannerDO bannerDO);

    @Select("SELECT * FROM banner")
    List<BannerDO> getAllBanners1();
}

5.1.3修改Service

5.1.3.1TestSleeveService接口

package io.github.talelin.latticy.service;

import io.github.talelin.latticy.model.BannerDO;
import java.util.List;

public interface TestSleeveService {
    public List<BannerDO> getBanners();

    public long insertBanner();

    public List<BannerDO> getBanners2();
}

5.1.3.2接口实现类TestSleeveImpl，追加getBanners2

package io.github.talelin.latticy.service.impl;

import io.github.talelin.latticy.mapper.BannerMapper;
import io.github.talelin.latticy.model.BannerDO;
import io.github.talelin.latticy.service.TestSleeveService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class TestSleeveImpl implements TestSleeveService {
    /* 在mapper创建BannerMapper接口,类似于JPA的Repository*/
    @Autowired
    private BannerMapper bannerMapper;

    public List<BannerDO> getBanners() {
        return this.bannerMapper.getAllBanners();
    }

    public List<BannerDO> getBanners2() {
        return this.bannerMapper.getAllBanners1();
    }

    public long insertBanner() {
        BannerDO bannerDO = new BannerDO();
        bannerDO.setName("NewBanner");
        bannerDO.setTitle("NewBannerTitle");

        bannerMapper.insertBanner(bannerDO);
        return bannerDO.getId();
    }
}

5.1.4修改Controller,追加test3

package io.github.talelin.latticy.controller.v1;

import io.github.talelin.latticy.model.BannerDO;

import io.github.talelin.latticy.service.TestSleeveService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RequestMapping("/v1/test")
@RestController
public class TestSleeveController {

    @Autowired
    private TestSleeveService testSleeveService;
    @GetMapping("/test1")

    public List<BannerDO> test1() {
        return testSleeveService.getBanners();
    }

    @GetMapping("/test2")
    public long test2() {
        return testSleeveService.insertBanner();
    }

    @GetMapping("/test3")
    public List<BannerDO> test3() {
        return testSleeveService.getBanners2();
    }
}

5.2@Insert

5.2.1、自定义SQL插入单条

5.2.1.1创建BannerBO

5.2.1.2修改BannerMapper

package io.github.talelin.latticy.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import io.github.talelin.latticy.model.BannerDO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.boot.Banner;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BannerMapper extends BaseMapper<BannerDO> {
    List<BannerDO> getAllBanners();

    long insertBanner(BannerDO bannerDO);

    @Select("SELECT * FROM banner")
    List<BannerDO> getAllBanners1();


    @Insert("INSERT INTO banner(name, description) VALUES(#{bannerParam.name}, #{bannerParam.description})")
    @Options(useGeneratedKeys=true, keyProperty="bannerParam.id", keyColumn="id") //该注解用于返回主键
    int insertOneBanner(@Param("bannerParam") BannerDO bannerDO);
}

5.2.1.3修改Service

    @Autowired
    private BannerMapper bannerMapper;

    public Integer insertBannerNew(){
        BannerDO bannerDO = new BannerDO();
        bannerDO.setName("NewBanner");
        bannerDO.setTitle("NewBannerTitle");

        Integer BannerId = bannerMapper.insertOneBanner(bannerDO);
        return bannerDO.getId();
    }

5.2.1.4修改Controller

    @GetMapping("/testInsert")
    public Integer insertBannerNew() {
        return testSleeveService.insertBannerNew();
    }

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图8

5.2.2、自定义SQL实现批量插入

5.2.2.1创建BannerBO（和插入单次的一致）

5.2.2.2修改BannerMapper （和插入单次的一致）

5.2.2.3修改Service

    @Autowired
    private BannerMapper bannerMapper;
    @Autowired
    private SqlSessionFactory sqlSessionFactory;
public void insertBanners(){

        List<BannerDO> BannerList = new ArrayList<BannerDO>();

        BannerDO bannerDO1 = new BannerDO();
        bannerDO1.setName("NewBanner");
        bannerDO1.setTitle("NewBannerTitle");
        BannerList.add(bannerDO1);

        BannerDO bannerDO2 = new BannerDO();
        bannerDO2.setName("NewBanner2");
        bannerDO2.setTitle("NewBannerTitle2");
        BannerList.add(bannerDO2);

        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
//        BannerMapper mapper = session.getMapper(BannerMapper.class);
        
        for (int i = 0; i < BannerList.size(); i++) {
            bannerMapper.insertOneBanner(BannerList.get(i));
            System.out.println("Insert Primary Id"+BannerList.get(i).getId());
            if(i%1000==999){//每1000条提交一次防止内存溢出
                session.commit();
                session.clearCache();
            }
        }
        session.commit();
        session.clearCache();
    }

JAVA、基础技术、技术与框架SpringBoot系列（二十五）Lin-CMS导入及MyBatis例子插图9

5.3update更新操作

5.3.1 CRUD接口使用updateById方式

5.3.1.1修改Service

    @Autowired
    private BannerMapper bannerMapper;
    public void updateOne(){
        BannerDO bannerDO = bannerMapper.selectById(24);
        bannerDO.setName("testName");
        bannerMapper.updateById(bannerDO);
    }

注意
根据入参entity的 id（主键）进行更新，对于 entity 中非空的属性，会出现在UPDATE语句的SET后面，即 entity 中非空的属性，会被更新到数据库

BannerDO bannerDO = new BannerDO();
bannerDO.setId(24);
user.title("NewTitle");
userMapper.updateById(bannerDO);

这样最后也只会更新title字段

扩展学习update(T entity, Wrapper wrapper)

public void testUpdate2() {
    BannerDO bannerDO = new BannerDO();
    bannerDO.setName("王三蛋");
    LambdaUpdateWrapper<BannerDO> wrapper = new LambdaUpdateWrapper<>();
    wrapper.between(BannerDO::getId, 26,31).likeRight(User::getName,"fox");
    userMapper.update(user, wrapper);
}

5.3.2使用注解的方式

5.3.2.1修改bannerMapper

@Update("UPDATE banner SET name = #{name} WHERE id = #{id}")
void updateBanner( BannerDO bannerDO);

5.3.2.2修改Service

public void updateBanner(){
        BannerDO bannerDO = bannerMapper.selectById(24);
        bannerDO.setName("testName");
        bannerDO.setTitle("TestTitle");
        bannerMapper.updateBanner(bannerDO);
    }

5.3.2.3修改Controller

    @GetMapping("/testUpdate2")
    public String upDateBannerNew2() {
        testSleeveService.updateBanner();
        return "testUpdate";
    }

结果：虽然对象中加入了setTitle，但因为注解的updateSql没有更新，结果也没有更新。

六MyBatis删除操作

BaseMapper一共提供了如下几个用于删除的方法

deleteById 根据主键id进行删除
deleteBatchIds 根据主键id进行批量删除
deleteByMap 根据Map进行删除（Map中的key为列名，value为值，根据列和值进行等值匹配）
delete(Wrapper wrapper) 根据条件构造器Wrapper进行删除

具体的例子跟Select相似

https://blog.csdn.net/jiangyu1013/article/details/86519505

打赏赞