• SSM33：MYBatis注解开发

      目录

    单条件查询

    数据库表（tb_brand）及数据准备

    实体类 Brand

    编写测试用例

    下载MybatisX插件

    查询所有数据思路

    创建名为 BrandMapper.java接口

    编写测试方法MybatisTest 类

    编写BrandMapper.xml

    运行结果

    Mybatis 针对 CURD 操作都提供了对应的注解，已经做到见名知意。如下：

    • 查询 ：@Select

    • 添加 ：@Insert

    • 修改 ：@Update

    • 删除 ：@Delete

    接下来我们做一个案例来使用 Mybatis 的注解开发

    针对上述的需要，Mybatis对动态SQL有很强大的支撑：

    • if

    • choose (when, otherwise)

    • trim (where, set)

    • foreach

    单条件查询

    如上图所示，在查询时只能选择 品牌名称、当前状态、企业名称 这三个条件中的一个，但是用户到底选择哪儿一个，我们并不能确定。这种就属于单个条件的动态SQL语句。

    这种需求需要使用到 choose（when，otherwise）标签 实现， 而 choose 标签类似于Java 中的switch语句。

    通过一个案例来使用这些标签

    数据库表（tb_brand）及数据准备

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

    实体类 Brand

    在 com.itheima.pojo 包下创建 Brand 实体类。

    1. public class Brand {
    2.     // id 主键
    3.     private Integer id;
    4.     // 品牌名称
    5.     private String brandName;
    6.     // 企业名称
    7.     private String companyName;
    8.     // 排序字段
    9.     private Integer ordered;
    10.     // 描述信息
    11.     private String description;
    12.     // 状态：0：禁用  1：启用
    13.     private Integer status;
    14.     
    15.     //省略 setter and getter。自己写时要补全这部分代码
    16. }

    编写测试用例

    测试代码需要在 test/java 目录下创建包及测试用例。项目结构如下：

    1. package com.itheima.test;
    2.  
    3. public class MybatisTest {
    4. }

    下载MybatisX插件

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

    • 主要功能

      • XML映射配置文件 和 接口方法 间相互跳转

      • 根据接口方法生成 statement

    • 安装方式

    重启一下

     

     红色头绳的表示映射配置文件，蓝色头绳的表示mapper接口。在mapper接口点击红色头绳的小鸟图标会自动跳转到对应的映射配置文件，在映射配置文件中点击蓝色头绳的小鸟图标会自动跳转到对应的mapper接口。也可以在mapper接口中定义方法，自动生成映射配置文件中的 statement

    查询所有数据思路

    • 编写接口方法：Mapper接口

      • 参数：无

        查询所有数据功能是不需要根据任何条件进行查询的，所以此方法不需要参数。

      • 结果：List

        我们会将查询出来的每一条数据封装成一个 Brand 对象，而多条数据封装多个 Brand 对象，需要将这些对象封装到List集合中返回。

      • 执行方法、测试

    创建名为 BrandMapper.java接口

    在 com.itheima.mapper 包写创建名为 BrandMapper 的接口。并在该接口中定义 List selectAll() 方法。

    1. package com.itheima.mapper;
    2.  
    3. import com.itheima.pojo.Brand;
    4. import org.apache.ibatis.annotations.Param;
    5. import org.apache.ibatis.annotations.Select;
    6.  
    7. import java.util.List;
    8. import java.util.Map;
    9.  
    10. public interface BrandMapper {
    11.     //查询所有的方法
    12.     @Select("select * from tb_brand")
    13.     public List selectAll();
    14.     //按照id查找信息
    15.     Brand selectById(int id);
    16.  
    17.     //有条件查询三种方法
    18.     List selectByCondition(@Param("status") int status,
    19.                                                  @Param("companyName") String companyName,
    20.                                                  @Param("brandName") String brandName);
    21.  
    22. //    List selectByCondition(Brand brand);
    23.  
    24.     List selectByCondition(Map map);
    25. //单条件查询
    26.     List selectByConditionSingle(Brand brand);
    27.     //添加功能·
    28.     void add(Brand brand);
    29.     //动态修改
    30.     int update(Brand brand);
    31.     //删除一条记录
    32.     void deleteById(int id);
    33.     //批量删除
    34.     void deleteByIds(@Param("ids")int[] ids);
    35. }

    编写测试方法MybatisTest 类

    1. package com.itheima.test;
    2.  
    3. import com.itheima.mapper.BrandMapper;
    4. import com.itheima.pojo.Brand;
    5. import org.apache.ibatis.io.Resources;
    6. import org.apache.ibatis.session.SqlSession;
    7. import org.apache.ibatis.session.SqlSessionFactory;
    8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    9. import org.junit.Test;
    10.  
    11. import java.io.IOException;
    12. import java.io.InputStream;
    13. import java.util.HashMap;
    14. import java.util.List;
    15. import java.util.Map;
    16.  
    17. public class MybatisTest {
    18.     //1.查询所有
    19.     @Test
    20.     public void testSelecctAll() throws IOException {
    21.         String resource = "mybatis-config.xml";
    22.         InputStream inputStream = Resources.getResourceAsStream(resource);
    23.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    24.         SqlSession sqlSession = sqlSessionFactory.openSession();
    25.         BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    26.         List brands = brandMapper.selectAll();
    27.         System.out.println(brands);
    28.         sqlSession.close();
    29.     }
    30.  
    31.     //2.查询详细
    32.  
    33.     @Test
    34.     public void testSelecctByIdl() throws IOException {
    35.         int id = 1;
    36.         String resource = "mybatis-config.xml";
    37.         InputStream inputStream = Resources.getResourceAsStream(resource);
    38.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    39.         SqlSession sqlSession = sqlSessionFactory.openSession();
    40.         BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    41.         //改变sql语句
    42.         Brand brand = brandMapper.selectById(id);
    43.         System.out.println(brand);
    44.         sqlSession.close();
    45.     }
    46.  
    47.     //3.多条件查询
    48.     @Test
    49.     public void testselectByCondition() throws IOException {
    50.         int status = 1;
    51.         String companyName = "华为";
    52.         String brandName = "华为";
    53.         //数据处理
    54.         companyName = "%" + companyName + "%";
    55.         brandName = "%" + brandName + "%";
    56.  
    57.         /* Brand brand = new Brand();
    58.         brand.setStatus(status);
    59.         brand.setCompanyName(companyName);
    60.         brand.setBrandName(brandName);*/
    61.  
    62.         //List brands = brandMapper.selectByCondition(brand);
    63.         String resource = "mybatis-config.xml";
    64.         InputStream inputStream = Resources.getResourceAsStream(resource);
    65.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    66.         SqlSession sqlSession = sqlSessionFactory.openSession();
    67.         BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    68.         //方式三 ：接口方法参数是 map集合对象 方式调用的方法
    69.         Map map = new HashMap();
    70. //        map.put("status" , status);
    71.         map.put("companyName", companyName);
    72. //        map.put("brandName" , brandName);
    73.         //改变sql语句
    74.         List brands = brandMapper.selectByCondition(map);
    75.         System.out.println(brands);
    76.  
    77.  
    78. //        List brands = brandMapper.selectByCondition(status, companyName, brandName);
    79.         System.out.println(brands);
    80.         sqlSession.close();
    81.     }
    82.  
    83.     //4.单条件查询
    84.     @Test
    85.     public void testselectByConditionSingle() throws IOException {
    86.         int status = 1;
    87.         String companyName = "华为";
    88.         String brandName = "华为";
    89.         //数据处理
    90.         companyName = "%" + companyName + "%";
    91.         brandName = "%" + brandName + "%";
    92.  
    93.         Brand brand = new Brand();
    94.         brand.setStatus(status);
    95. //        brand.setCompanyName(companyName);
    96. //        brand.setBrandName(brandName);
    97.  
    98.         //List brands = brandMapper.selectByCondition(brand);
    99.         String resource = "mybatis-config.xml";
    100.         InputStream inputStream = Resources.getResourceAsStream(resource);
    101.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    102.         SqlSession sqlSession = sqlSessionFactory.openSession();
    103.         BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    104.  
    105.         //改变sql语句
    106.         List brands = brandMapper.selectByConditionSingle(brand);
    107.         System.out.println(brands);
    108.         sqlSession.close();
    109.     }
    110.     //5.普通添加功能
    111.  
    112.     @Test
    113.     public void testadd() throws IOException {
    114.         int status = 1;
    115.         String companyName = "刘德华品牌手机";
    116.         String brandName = "红米";
    117.         String description = "号的中国制造";
    118.         int order = 100;
    119.         //数据处理
    120. //        companyName="%"+companyName+"%";
    121. //        brandName="%"+brandName+"%";
    122.  
    123.         Brand brand = new Brand();
    124.         brand.setStatus(status);
    125.         brand.setCompanyName(companyName);
    126.         brand.setBrandName(brandName);
    127.         brand.setDescription(description);
    128.         brand.setOrdered(order);
    129.  
    130.         //List brands = brandMapper.selectByCondition(brand);
    131.         String resource = "mybatis-config.xml";
    132.         InputStream inputStream = Resources.getResourceAsStream(resource);
    133.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    134.         SqlSession sqlSession = sqlSessionFactory.openSession(true);//代表提交事务
    135.         BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    136.  
    137.         //改变sql语句
    138.         brandMapper.add(brand);
    139.         Integer id = brand.getId();
    140.         System.out.println(id);
    141.         //提交事务
    142. //        sqlSession.commit();
    143.         //关闭资源
    144.  
    145.         sqlSession.close();
    146.     }
    147.             //6.动态修改功能
    148.             @Test
    149.             public void testupdate() throws IOException {
    150.                 int status = 1;
    151.                 String companyName = "kk";
    152.                 String brandName = "kk";
    153.                 String description = "号的中国制造";
    154.                 int order = 100;
    155.                 int id=5;
    156.                 //数据处理
    157. //        companyName="%"+companyName+"%";
    158. //        brandName="%"+brandName+"%";
    159.  
    160.                 Brand brand = new Brand();
    161.                 brand.setStatus(status);
    162.                 brand.setCompanyName(companyName);
    163.                 brand.setBrandName(brandName);
    164. //                brand.setDescription(description);
    165. //                brand.setOrdered(order);
    166.                 brand.setId(id);
    167.  
    168.                 //List brands = brandMapper.selectByCondition(brand);
    169.                 String resource = "mybatis-config.xml";
    170.                 InputStream inputStream = Resources.getResourceAsStream(resource);
    171.                 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    172.                 SqlSession sqlSession = sqlSessionFactory.openSession(true);//代表提交事务
    173.                 BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    174.  
    175.                 //改变sql语句
    176.                 int count = brandMapper.update(brand);
    177.                 System.out.println(count);
    178.                 //提交事务
    179. //        sqlSession.commit();
    180.                 //关闭资源
    181.  
    182.                 sqlSession.close();
    183.             }
    184.             //删除一条记录
    185.             @Test
    186.             public void testdeleteById() throws IOException {
    187.  
    188.                 int id=5;
    189.                 //数据处理
    190. //        companyName="%"+companyName+"%";
    191. //        brandName="%"+brandName+"%";
    192.  
    193.  
    194.  
    195.                 //List brands = brandMapper.selectByCondition(brand);
    196.                 String resource = "mybatis-config.xml";
    197.                 InputStream inputStream = Resources.getResourceAsStream(resource);
    198.                 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    199.                 SqlSession sqlSession = sqlSessionFactory.openSession(true);//代表提交事务
    200.                 BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    201.  
    202.                 //改变sql语句
    203.               brandMapper.deleteById(id);
    204.                 //提交事务
    205. //        sqlSession.commit();
    206.                 //关闭资源
    207.  
    208.                 sqlSession.close();
    209.             }
    210.             //批量删除
    211.     @Test
    212.     public void testdeleteByIds() throws IOException {
    213.  
    214.         int[] ids={6,7,8};
    215.         //数据处理
    216. //        companyName="%"+companyName+"%";
    217. //        brandName="%"+brandName+"%";
    218.  
    219.  
    220.  
    221.         //List brands = brandMapper.selectByCondition(brand);
    222.         String resource = "mybatis-config.xml";
    223.         InputStream inputStream = Resources.getResourceAsStream(resource);
    224.         SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    225.         SqlSession sqlSession = sqlSessionFactory.openSession(true);//代表提交事务
    226.         BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    227.  
    228.         //改变sql语句
    229.         brandMapper.deleteByIds(ids);
    230.         //提交事务
    231. //        sqlSession.commit();
    232.         //关闭资源
    233.  
    234.         sqlSession.close();
    235.     }
    236. }

    编写BrandMapper.xml

    1. "1.0" encoding="UTF-8" ?>
    2. mapper
    3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    4.         "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
    8. <mapper namespace="com.itheima.mapper.BrandMapper">
    9.  
    10.     
    11.     <resultMap id="brandresultMapper" type="brand">
    12.  
    13.         <result column="brand_Name" property="brandName"/>
    14.         <result column="company_Name" property="companyName"/>
    15.     resultMap>
    16.  
    17.  
    18.     <select id="selectAll" resultMap="brandresultMapper">
    19.         select * from tb_brand;
    20.         
    21.     select>
    22.  
    23.  
    24.     
    25.     
    26.     <select id="selectById" resultMap="brandresultMapper">
    27.         select *
    28.         from tb_brand
    29.         where id = #{id};
    30.     select>
    31.  
    32.     
    33.     <select id="selectByCondition" resultMap="brandresultMapper">
    34.         select *
    35.         from tb_brand
    36.         
    37.         <where>
    38.             <if test="status!=null">
    39.                 and status = #{status}
    40.  
    41.             if>
    42.             <if test="company_name!=null and company_name !='' ">
    43.                 and company_name like #{companyName}
    44.  
    45.             if>
    46.             <if test="brandName!=null and brandName!='' ">
    47.                 and brand_name like #{brandName}
    48.             if>
    49.  
    50.         where>
    51.  
    52.  
    53.     select>
    54.     
    55.     <select id="selectByConditionSingle" resultMap="brandresultMapper">
    56.         select *
    57.         from tb_brand
    58.         <where>
    59.             <choose>
    60.                 <when test="status != null">
    61.                     status = #{status}
    62.                 when>
    63.                 <when test="companyName != null and companyName != '' ">
    64.                     company_name like #{companyName}
    65.                 when>
    66.                 <when test="brandName != null and brandName != ''">
    67.                     brand_name like #{brandName}
    68.                 when>
    69.             choose>
    70.         where>
    71.     select>
    72.  
    73.     
    74.     <insert id="add" useGeneratedKeys="true" keyProperty="id">
    75.         insert into tb_brand (brand_name, company_name, ordered, description, status)
    76.         values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
    77.  
    78.     insert>
    79.     
    80.     <update id="update">
    81.         update tb_brand
    82.         <set>
    83.             <if test="brandName != null and brandName != ''">
    84.                 brand_name = #{brandName},
    85.             if>
    86.             <if test="companyName != null and companyName != ''">
    87.                 company_name = #{companyName},
    88.             if>
    89.             <if test="ordered != null">
    90.                 ordered = #{ordered},
    91.             if>
    92.             <if test="description != null and description != ''">
    93.                 description = #{description},
    94.             if>
    95.             <if test="status != null">
    96.                 status = #{status}
    97.             if>
    98.         set>
    99.         where id = #{id};
    100.     update>
    101.     
    102.     <delete id="deleteById">
    103.  
    104.         delete
    105.         from tb_brand
    106.         where id = #{id};
    107.     delete>
    109.     <delete id="deleteByIds">
    110.         delete
    111.         from tb_brand
    112.         where id in
    113.  
    114.             <foreach collection="ids" item="id" separator="," open="(" close=")">
    115.                 #{id}
    116.             foreach>
    117.             ;
    118.     delete>
    119.  
    120. mapper>

    运行结果

    数据库记录也删除成功 

  • 相关阅读:
    Docker 01 概述
    Debian12系统下LAMP环境中Nubuilder4.5的安装
    算法小记【1】
    使用ssl_certificate_by_lua指令动态加载证书
    【Mongodb数据库】的介绍和安装（windows下和ubuntu16.04下安装及启动）
    京鸿鑫源元宇宙革新探索：开启未来零售新纪元
    J2EE基础：MySQL01
    《进阶篇第9章》学习vuex知识点后练习：求和案例_纯vue版代码
    日志门面技术
    LeetCode 0086.分隔链表
  • 原文地址：https://blog.csdn.net/weixin_51330376/article/details/127541980