③【MyBatis】操作数据库，进行增删改查，MyBatisX插件简化开发

个人简介：Java领域新星创作者；阿里云技术博主、星级博主、专家博主；正在Java学习的路上摸爬滚打，记录学习的过程~
个人主页：.29.的博客
学习社区：进去逛一逛~

---

具体使用可参考MyBatis文档：MyBatis中文网

使用MyBatisX插件

主要功能：

• 实现SQL映射文件与对应Mapper接口方法的相互跳转（点击代码左侧的小鸟图标即可实现跳转）；
• 可根据Mapper接口方法生成SQL映射文件中的statement（生成后直接在statement中编写sql语句即可，简化流程提升效率）；

增删改查 练习案例

1. 创建被操作的数据表tb_brand

create database mybatis;

use mybatis;

-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建tb_brand表
create table tb_brand
(
    -- id 主键
    id           int primary key auto_increment,
    -- 品牌名称
    brand_name   varchar(20),
    -- 企业名称
    company_name varchar(20),
    -- 排序字段
    ordered      int,
    -- 描述信息
    description  varchar(100),
    -- 状态：0：禁用  1：启用
    status       int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
       ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
       ('小米', '小米科技有限公司', 50, 'are you ok', 1);

2. 根据表数据设计实体类Brand

/**
 * @author .29.
 * @create 2024-03-12 23:53
 */
public class Brand {

    private int id;//主键
    private String brandName;//品牌名称
    private String companyName;//公司名称
    private int ordered;//排序字段
    private String description;//描述信息
    private int status;//状态：1启用，0禁用

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public int getOrdered() {
        return ordered;
    }

    public void setOrdered(int ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}

3. 创建Mapper接口，设计操作数据库的方法

/**
 * @author .29.
 * @create 2024-03-12 23:59
 */
public interface BrandMapper {
    /**
     * 查询所有数据
     */
    List<Brand> selectAll();

    /**
     * 根据id查询单条数据
     */
    Brand selectById(@Param("id") int id);

    /**
     * 根据条件查询
     * 方式一：接收独立的参数
     */
    List<Brand> selectByCondition1(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);

    /**
     * 根据条件查询
     * 方式二：接收对象参数
     */
    List<Brand> selectByCondition2(Brand brand);

    /**
     * 根据条件查询
     * 方式三：接收Map集合参数
     */
    List<Brand> selectByCondition(Map map);

    /**
     * 单条件动态查询
     */
    List<Brand> selectByConditionSingle(Brand brand);

    /**
     * 添加数据
     */
    void add(Brand brand);

    /**
     * 修改数据
     */
    void update(Brand brand);

    /**
     * 删除数据
     */
    void deleteById(int id);

    /**
     * 批量删除
     */
    void deleteByIds(int[] ids);

}

4. 设置Mapper映射文件，利用MyBatisX自动生成statement，在statement中编写SQL

设置Mapper映射文件，其名字与Mapper接口一致，与Mapper接口放在同一目录下：

<?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="com.java.mapper.BrandMapper">
    <!--SQL语句的id与mapper接口中的方法名保持一致-->

    <!--查询功能的标签，必须设置 resultType 或 resultMap
        - resultType： 设置默认的映射关系
        - resultMap： 设置自定义的映射关系-->




    <!--自定义映射关系，例子：数据库表brand_Name ==> 实体类brandName-->
    <resultMap id="brandResultMap" type="com.java.pojo.Brand">
        <result property="brandName" column="brand_name" />
        <result property="companyName" column="company_name" />
    </resultMap>

    <!--需要使用自定义映射，用resultMap替代resultType属性-->
    <select id="selectAll" resultMap="brandResultMap">
        select * from tb_brand;<!--查询所有数据-->
    </select>

    <!--需要使用自定义映射，用resultMap替代resultType属性-->
    <!--    参数占位符:
            1. #{}:会将其替换成？占位符，可防止SQL注入
            2. ${}:会直接进行字符串拼接，会出现SQL注入风险-->
    <!--parameterType="int"设置传入参数类型（可以省略不写）-->
    <select id="selectById" resultMap="brandResultMap" parameterType="int">
        select * from tb_brand where id = #{id};<!--根据id查询数据-->
    </select>


    <select id="selectByCondition1" resultMap="brandResultMap"><!--静态SQL条件查询，三个条件都必须存在-->
        select * from tb_brand
        where
        status = #{status}
        and brand_name like #{brandName}
        and company_name like #{companyName}
    </select>

    <!--where标签可以帮助你解决拼接问题，不用担心多出来的and-->
    <select id="selectByCondition2" resultMap="brandResultMap"><!--动态SQL条件查询-->
        select * from tb_brand
        <where>
            <if test="status != null and status != '' ">
                and status = #{status}
            </if>
            <if test="brandName != null and brandName != '' ">
                and brand_name like #{brandName}
            </if>
            <if test="companyName != null and companyName != ''">
                and company_name like #{companyName}
            </if>
        </where>
    </select>

    <select id="selectByCondition3" resultMap="brandResultMap"><!--动态SQL条件查询-->
        select * from tb_brand
        <where>
            <if test="status != null and status != ''">
                and status = #{status}
            </if>
            <if test="brandName != null and brandName != '' ">
                and brand_name like #{brandName}
            </if>
            <if test="companyName != null and companyName != ''">
                and company_name like #{companyName}
            </if>
        </where>
    </select>

    <!--choose、when标签，相当于switch、case语句-->
    <select id="selectByConditionSingle" resultMap="brandResultMap"><!--单条件SQL动态查询，即多个条件选一个进行查询-->
        select * from tb_brand
        <where>
            <choose>
                <when test="status != null and status != ''">
                    status = #{status}
                </when>
                <when test="brandName != null and brandName != ''">
                    brand_name like #{brandName}
                </when>
                <when test="companyName != null and companyName != ''">
                    company_name like #{companyName}
                </when>
            </choose>
        </where>
    </select>

    <!--如果需要返回添加数据主键，使用属性：useGeneratedKey和keyProperty-->
    <!--或者直接在sql中增添主键字段，下述sql中主键为自增所以没有设置主键字段-->
    <insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into tb_brand(brand_name, company_name, ordered, description, status)
        values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
    </insert>


    <!--使用set标签搭配if标签，可以动态修改字段，也不用担心逗号的问题-->
    <update id="update">
        update tb_brand
        <set>
            <if test="status != null and status != ''">
                status = #{status},
            </if>
            <if test="brandName != null and brandName != '' ">
                brand_name = #{brandName},
            </if>
            <if test="companyName != null and companyName != ''">
                company_name = #{companyName},
            </if>
            <if test="description != null and description != ''">
                description = #{description},
            </if>
            <if test="ordered != null and ordered != ''">
                ordered = #{ordered},
            </if>
        </set>
            where id = #{id}
    </update>

    <delete id="deleteById">
        delete from tb_brand where id = #{id}
    </delete>

    <!--使用foreach标签遍历集合参数，collection属性默认为"array",这里再Mapper集合方法中用@Param("ids")设置成了ids -->
    <!--分别用separator，open，close属性设置分隔符、起始符、终止符，这里模拟了in(id1,id2,id3,...)-->
    <delete id="deleteByIds">
        delete from tb_brand where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

</mapper>

5. 设置MyBatis核心配置文件，配置数据库连接，配置SQL映射文件位置

<?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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--数据库连接信息-->
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&amp;useUnicode=true&amp;CharacterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="abc123"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--配置映射文件,通常都是Mapper结尾-->
<!--        <mapper resource="com/java/mapper/UserMapper.xml"/>-->

        <!--Mapper代理方式，简化后 直接指定映射文件所在目录即可-->
        <package name ="com.java.mapper" />
    </mappers>
</configuration>

6. 使用MyBatis操作数据库(测试)

测试类，需要Junit api依赖

/**
 * @author .29.
 * @create 2024-03-13 0:34
 */
public class test {
    //1. 加载mybatis配置文件，获取SqlSessionFactory对象
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    //2. 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);//传入true，自动提交事务

    public test() throws IOException {
    }

    /**
     * 测试 查询所有数据
     */
    @Test
    public void selectAll(){
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        List<Brand> brands = mapper.selectAll();
        System.out.println(brands);
    }

    /**
     * 测试 根据id查询数据
     */
    @Test
    public void selectById(){
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        Brand brand = mapper.selectById(3);
        System.out.println(brand);
    }

    /**
     *测试 条件查询数据
     */
    @Test
    public void selectByCondition(){
        //模拟接收参数1
        int status = 1;
        String brandName = "华为";
        String companyName = "华为";

        //模拟处理数据
        brandName = "%" + brandName + "%";
        companyName = "%" + companyName + "%";

        //模拟接收参数2
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);

        //模拟接收参数3
        HashMap map = new HashMap<>();
        map.put("status", status);
        map.put("brandName", brandName);
        map.put("companyName", companyName);

        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        List<Brand> brands1 = mapper.selectByCondition1(status, companyName, brandName);
        List<Brand> brands2 = mapper.selectByCondition2(brand);
        List<Brand> brands3 = mapper.selectByCondition3(map);
        System.out.println(brands1);
        System.out.println(brands2);
        System.out.println(brands3);
    }

    /**
     * 测试 单条件动态查询
     */
    @Test
    public void selectByConditionSingle(){
        //模拟接收参数
        int status = 1;
        String brandName = "华为";
        String companyName = "华为";

        //模拟处理数据
        brandName = "%" + brandName + "%";
        companyName = "%" + companyName + "%";

        //任选一个条件去设置
        Brand brand = new Brand();
        brand.setStatus(status);

        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        List<Brand> brands = mapper.selectByConditionSingle(brand);
        System.out.println(brands);
    }

    @Test
    public void add(){
        //设置新增数据的参数
        int status = 0;
        String brandName = "饿了吗11";
        String companyName = "饿了吗外卖集团";
        String description = "饿了就找饿了吗！";
        int ordered = 100;
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setDescription(description);
        brand.setOrdered(ordered);

        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        mapper.add(brand);
        //获取id
        int id = brand.getId();
        System.out.println(id);

        //如果在SqlSessionFactory().openSession()方法中设置true参数，则需要调用sqlSession.commit()提交事务
        //sqlSession.commit();
    }

    @Test
    public void update(){
        //设置修改的参数
        int id = 6;
        String brandName = "11111";
        String companyName = "11111";

        Brand brand = new Brand();
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);
        brand.setId(id);

        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        mapper.update(brand);

    }

    @Test
    public void deleteById(){
        int id = 1;
        int[] ids = new int[]{5, 6};
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        mapper.deleteById(id);
        mapper.deleteByIds(ids);

        System.out.println(mapper.selectAll());
    }

    @Test
    public void deleteByIds(){
        int[] ids = new int[]{5, 6};
        BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
        mapper.deleteByIds(ids);

        System.out.println(mapper.selectAll());
    }


}