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

怼烎@ 2024-04-17 10:20 68阅读 0赞

![在这里插入图片描述][8d8e0d8a542946af858489a536162608.gif_pic_center]

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

> [![在这里插入图片描述][bc2f2b834d174541872b677cc2c36960.png_pic_center]][bc2f2b834d174541872b677cc2c36960.png_pic_center 1]

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

*  
 *   *  使用MyBatisX插件
     *  增删改查 练习案例
     *   *  1. 创建被操作的数据表tb\_brand
         *  2. 根据表数据设计实体类Brand
         *  3. 创建Mapper接口，设计操作数据库的方法
         *  4. 设置Mapper映射文件，利用MyBatisX自动生成statement，在statement中编写SQL
         *  5. 设置MyBatis核心配置文件，配置数据库连接，配置SQL映射文件位置
         *  6. 使用MyBatis操作数据库(测试)

--------------------

##  ##

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

### 使用MyBatisX插件 ###

`主要功能`：

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

> ![在这里插入图片描述][3b7a045e6df54c3980b7eac43028e750.png]

### 增删改查 练习案例 ###

#### 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">
    
    
    
    <mapper namespace="com.java.mapper.BrandMapper">
        
    
        
    
    
    
    
        
        <resultMap id="brandResultMap" type="com.java.pojo.Brand">
            <result property="brandName" column="brand_name" />
            <result property="companyName" column="company_name" />
        </resultMap>
    
        
        <select id="selectAll" resultMap="brandResultMap">
            select * from tb_brand;
        </select>
    
        
        
        
        <select id="selectById" resultMap="brandResultMap" parameterType="int">
            select * from tb_brand where id = #{id};
        </select>
    
    
        <select id="selectByCondition1" resultMap="brandResultMap">
            select * from tb_brand
            where
            status = #{status}
            and brand_name like #{brandName}
            and company_name like #{companyName}
        </select>
    
        
        <select id="selectByCondition2" resultMap="brandResultMap">
            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">
            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="selectByConditionSingle" resultMap="brandResultMap">
            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>
    
        
        
        <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>
    
    
        
        <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>
    
        
        
        <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&useUnicode=true&CharacterEncoding=utf-8"/>
                    <property name="username" value="root"/>
                    <property name="password" value="abc123"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            
    
    
            
            <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());
        }
    
    
    }

[![在这里插入图片描述][20a7f1b58dfb4660b75d7f021c157d57.png_pic_center]][20a7f1b58dfb4660b75d7f021c157d57.png_pic_center 1]

[8d8e0d8a542946af858489a536162608.gif_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/14/c81fdf48201242e380007b54a058f44d.gif
[.29.]: https://blog.csdn.net/ebb29bbe?spm=1018.2226.3001.5343
[Link 1]: https://bbs.csdn.net/forums/185241f773fb401b8ca7994dc3d02333?joinKey=cyru4ex9o7wq-mz6v1koj5x-1-add3ae8dc3827dbbd4360c79af92bbbe
[bc2f2b834d174541872b677cc2c36960.png_pic_center]: https://img-blog.csdnimg.cn/direct/bc2f2b834d174541872b677cc2c36960.png#pic_center
[bc2f2b834d174541872b677cc2c36960.png_pic_center 1]: https://blog.csdn.net/ebb29bbe/category_12007200.html?spm=1001.2014.3001.5482
[MyBatis]: https://mybatis.net.cn/index.html
[3b7a045e6df54c3980b7eac43028e750.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/14/3f7d957687aa42929e7f6e06f98b291c.png
[20a7f1b58dfb4660b75d7f021c157d57.png_pic_center]: https://img-blog.csdnimg.cn/20a7f1b58dfb4660b75d7f021c157d57.png#pic_center
[20a7f1b58dfb4660b75d7f021c157d57.png_pic_center 1]: https://blog.csdn.net/ebb29bbe?type=blog