MyBatis案例

最新推荐文章于 2022-10-31 11:59:14 发布

原创最新推荐文章于 2022-10-31 11:59:14 发布 · 215 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#java #intellij-idea

本文介绍了MyBatis在数据库操作中的应用，包括环境准备、数据库表tb_brand、实体类、测试用例。重点讲解了MyBatisX插件的安装与主要功能，如XML与接口的相互跳转、动态条件查询、添加、修改和删除功能，并提供了详细的步骤和示例代码。

环境准备

1.数据库表tb_brand

2.实体类

public class Brand {
    private Integer id;
    private String brandName;
    private String companyName;
    private Integer ordered;
    private String description;
    private Integer status;

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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 Integer getOrdered() {
        return ordered;
    }

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

    public String getDescription() {
        return description;
    }

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

    public Integer getStatus() {
        return status;
    }

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

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

3.测试用例

在test-java目录下创建测试类

public class MyBatisTest {

}

4.安装MyBatisX插件

MyBatisX是一款基于IDEA的快速开发插件，为效率而生

主要功能：

1 XML和接口方法相互跳转

2 根据接口方法生成statement

安装步骤：

查询-查询所有-结果映射

1.编写接口方法：Mapper接口；

参数：无

结果：List<Brand>

public interface BrandMapper {
    /**
     * 查询所有
     */
  public   List<Brand> selectAll();
}

2.编写sql语句：SQL映射文件

<?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.itheima.mapper.BrandMapper">
<!--
数据库表的字段名称和实体类的属性名称不一样，则不能自动封装数据
*对sql语句的字段查询名称 起别名：
-->
<!--    <select id="selectAll" resultType="brand">-->
<!--        select *-->
<!--        from tb_brand;-->
<!--    </select>-->
    <select id="selectAll" resultType="brand">
        select id,brand_name as brandName,company_name as companyName,ordered,description,status
        from tb_brand;
    </select>
</mapper>

方式一:sql片段可以解决起别名的繁琐

id,brand_name as brandName,company_name as companyName,ordered,description,status;

</sql>

select

from tb_brand;

</select>

sql片段缺点：不灵活

方式二：resultMap：

<resultMap id="brandResultMap" type="brand">
    <!--id标签：完成主键字段的映射-->
    <!--result标签：完成一般字段的映射-->
    <!--column:列
        property：属性
     -->
    <result column="brand_name" property="brandName"/>
    <result column="company_name" property="companyName"/>
</resultMap>
<select id="selectAll" resultMap="brandResultMap">
    select *
    from tb_brand;
</select>

3.执行方法，测试

@org.junit.Test
public void selectAllTest() throws Exception {
    //1、获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //2、获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //3、获取Mapper接口的代理对象
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    //4、执行方法
    List<Brand> brands = brandMapper.selectAll();//学完Spring框架后就只剩下这一行
    System.out.println(brands);
    //5、释放资源
    sqlSession.close();
}

查询-查看详情（根据id查看某品牌详情）

1.编写接口方法：Mapper接口 Brand selectById(int id);

参数：id

返回结果：Brand

Brand selectByIdBrand(int id);

2.编写sql语句：sql映射文件

<select id="selectByIdBrand" resultMap="brandResultMap">
        select *
        from tb_brand
        where id = #{id};
 </select>

3.执行方法，测试

 /**
     * 查询详情：按id查询
     */
    @org.junit.Test
    public void selectByIdBrand() throws Exception {
        int id = 1;
        //1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3、获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4、执行方法
        Brand brands = brandMapper.selectByIdBrand(id);
        System.out.println(brands);
        //5、释放资源
        sqlSession.close();


    }

查询-条件查询

1.多条件查询

1.编写接口方法：Mapper接口

参数：所有查询条件

结果List<Brand>

2.编写sql语句：SQL映射文件

<select id="selectByCondition" resultMap="brandResultMap">
    select *
    from tb_brand
    where status=#{status}
            and company_name like #{companyName}
            and brand_name like #{brandName};
</select>

3.执行方法，测试

@org.junit.Test
    public void selectByCondition() throws Exception {
        //接收参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";
        //处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";
        //封装对象
//        Brand brand = new Brand();
//        brand.setStatus(status);
//        brand.setCompanyName(companyName);
//        brand.setBrandName(brandName);
        Map map=new HashMap();
        map.put("status",status);
        map.put("companyName",companyName);
        map.put("brandName",brandName);


        //1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3、获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4、执行方法
////        List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
//        List<Brand> brands = brandMapper.selectByCondition(brand);
        List<Brand> brands = brandMapper.selectByCondition(map);
        System.out.println(brands);
        //5、释放资源
        sqlSession.close();
    }

//这种开发是有bug的，用户在使用时只选择一个条件进行查询的话会出现bug，如何优化这种条件查询呢？

查询——动态条件查询

sql语句会随着用户的输入或外部t条件的变化而变化，我们成为动态sql

MyBatis对动态SQL有很强大的支撑：

choose(when,otherwise)

trim(where,set)

foreach

<!--动态条件查询-->
    <!--
    动态条件查询：
    *if：完成对应的条件判断
    *test：逻辑表达式
    *问题：
    解决方案：恒等式：1=1过度 笨方法
    *<where>标签替换where关键字
    -->
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
--         where 1=1
        <where>
        <if test="status != null">
          and  status=#{status}
        </if>
        <if test="companyName != null and companyName !=''">
            and company_name like #{companyName}
        </if>
        <if test="brandName != null and brandName !=''">
            and brand_name like #{brandName}
        </if>
        </where>
    </select>

查询-单条件动态条件查询

从多个条件中选择一个

choose(when,otherwise):选择，类似于Java中的switch语句

<select id="selectByConditionSingle" resultMap="brandResultMap">
        select *
        from tb_brand
      <where>
        <choose> <!--相当于swithch-->
            <when test="status != null">
                status = #{status}
            </when><!--相当于case-->
            <when test="companyName != null and companyName=''">
                company_name like #{companyName}
            </when><!--相当于case-->
            <when test="brandName!=null and brandName=''">
                brand_name like #{brandName}
            </when><!--相当于case-->

        </choose>
      </where>
    </select>

测试类：

  /**
     * 单条件动态查询
     */
    @org.junit.Test
    public void selectByConditionSingle() throws Exception {
        //接收参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";
        //处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";
        //封装对象
        Brand brand = new Brand();
//        brand.setStatus(status);
        //brand.setCompanyName(companyName);
        //brand.setBrandName(brandName);
//        Map map=new HashMap();
//        map.put("status",status);
//        map.put("companyName",companyName);
//        map.put("brandName",brandName);


        //1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3、获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4、执行方法
////        List<Brand> brands = brandMapper.selectByCondition(status, companyName, brandName);
//        List<Brand> brands = brandMapper.selectByCondition(brand);
        List<Brand> brands = brandMapper.selectByConditionSingle(brand);
        System.out.println(brands);
        //5、释放资源
        sqlSession.close();
    }

添加功能

添加

1 编写接口方法：Mapper接口 void brandAdd(Brand brand);

参数：除了id之外的所有数据

返回结构：void

2.编写sql语句：Sql映射文件

<insert id="brandAdd">
        insert into tb_brand (brand_name, company_name, ordered, description, status)
        values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});

    </insert>

3.执行方法，测试

 @org.junit.Test
    public void brandAdd() throws Exception {
        //接收参数
        int status = 1;
        String companyName = "苹果科技有限公司";
        String brandName = "红k40";
        int ordered = 20;
        String description = "为发烧而生";
        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setOrdered(ordered);
        brand.setDescription(description);
//        Map map=new HashMap();
//        map.put("status",status);
//        map.put("companyName",companyName);
//        map.put("brandName",brandName);


        //1、获取SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2、获取SqlSession对象
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动提交事务 false:手动提交事务
        //3、获取Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4、执行方法
        brandMapper.brandAdd(brand);
        //5、释放资
        sqlSession.close();
    }

添加-主键返回

//4、执行方法
brandMapper.brandAdd(brand);
Integer id = brand.getId();
System.out.println(id);

映射文件

修改

1.修改全部字段

编写接口方法：

/**
 * 修改
 */
int update(Brand brand);

编写映射文件sql

<!--修改-->
<update id="update">
    update tb_brand
    set brand_name = #{brandName},
        company_name = #{companyName},
        ordered = #{ordered},
         description = #{description},
         status = #{status}
    where id=#{id};

</update>

编写测试类

@org.junit.Test
public void updateTest() throws Exception {
    //接收参数
    int status = 1;
    String companyName = "波导手机";
    String brandName = "波导";
    int ordered = 200;
    String description = "波导手机，手机中的战斗机";
    int id = 5;
    //封装对象
    Brand brand = new Brand();
    brand.setStatus(status);
    brand.setCompanyName(companyName);
    brand.setBrandName(brandName);
    brand.setOrdered(ordered);
    brand.setDescription(description);
    brand.setId(id);


    //1、获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //2、获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);
    //3、获取Mapper接口的代理对象
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    //4、执行方法
    int count = brandMapper.update(brand);
    System.out.println(count);
    //5、释放资
    sqlSession.close();
}

2.修改动态字段

<!--动态修改-->
<update id="update">
    update tb_brand
 <set>
    <if test="brandName!=null and  brandName!=''">
        brand_name=#{brandName},
    </if>
    <if test="companyName!=null and companyName!=''">
        company_name=#{companyName},
    </if>
    <if test="ordered!=null">
        ordered=#{ordered},
    </if>
    <if test="description!=null and description!=''">
        description=#{description},
    </if>
    <if test="status != null">
        status=#{status}
    </if>
 </set>
    where id=#{id};


</update>

删除功能

根据id删除：

1.编写接口方法：Mapper接口 void deleteById(int id);

参数 id

结果 void

2.编写sql语句：sql映射文件

<!--删除一个-->
<delete id="deleteById">
    delete from tb_brand where id=#{id};
</delete>

3.执行方法测试

/**
 * 根据id删除
 */
@org.junit.Test
public void deleteTest() throws Exception {
    //接收参数
    int id = 5;
    //1、获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //2、获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);
    //3、获取Mapper接口的代理对象
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    //4、执行方法
    brandMapper.deleteById(id);

    //5、释放资
    sqlSession.close();
}

批量删除

1.编写接口方法：

Mapper接口：void deleteByIds(@param("ids" int[] ids));

参数：id

结果：void

2.编写sql语句：sql映射文件

 <!--批量删除-->
    <!--
    mybatis会将数组参数，封装为一个Map集合
    *默认：array=数组
    *使用@Param注解改变map集合的默认Key的名称
    -->
    <delete id="deleteByIds">
        delete
        from tb_brand
        where id in
        <foreach collection="array" item="id" separator="," open="(" close=")">
<!--        <foreach collection="ids" item="id" separator="," open="(" close=")">-->
            #{id}
        </foreach>
        ;
    </delete>

3.执行测试方法

@org.junit.Test
public void deleteIdsTest() throws Exception {
    //接收参数
    int[] ids={6,7};

    //1、获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //2、获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);
    //3、获取Mapper接口的代理对象
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    //4、执行方法
    brandMapper.deleteByIds(ids);


    //5、释放资
    sqlSession.close();
}