Mybatis实现增删改查

准备工作：建立项目，连接数据库MySQL，安装Mybatix插件

数据库代码准备：

-- 删除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);


SELECT * FROM tb_brand;

代码结构

刷新连接的数据库

记得在mybatis-config.xml写入连接数据库的信息和mapper代理的代码

正题开始

一.查询——查询所有数据

1.编写接口方法：

package com.itheima.mapper;

import com.itheima.pojo.Brand;

import java.util.List;

public interface BrandMapper {
    /*
    * 查询所有
    * */

    public List<Brand> selectAll();

}

2.编写SQL语句

<mapper namespace="com.itheima.mapper.BrandMapper">


    <select id="selectAll" resultType="brand">
        select *
        from tb_brand;
    </select>

</mapper>

3.执行方法——测试

package com.itheima.test;

import com.itheima.mapper.BrandMapper;
import com.itheima.pojo.Brand;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MyBatisTest {
    @Test
    public void testSelectAll() throws IOException {

        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

        //4.执行方法
        List<Brand> brands = brandMapper.selectAll();
        System.out.println(brands);
        //5. 释放资源
        sqlSession.close();
    }
}

运行结果：

遇到问题：
当数据表字段名称与javaweb项目中不一样时，可以采取下面方法：
方法一：sql片段封装

方法二：resultMap
1.定义标签
2.在标签中，使用resultMap属性替换 resultType属性

2.查询——查看详情

1.编写接口方法：Mapper接口
参数：id
结果：Brand

Brand selectById(int id);

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

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

3.执行方法：映射

  @Test
    public void testSelectAllById() throws IOException {
        int id = 1;
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

        //4.执行方法
        Brand brand = brandMapper.selectById(id);
        System.out.println(brand);
        //5. 释放资源
        sqlSession.close();
    }

细节问题：

3.查询——条件查询

1.多条件查询

• 编写接口方法：Mapper接口
  参数：所有查询条件
  结果：List

• 编写SQL语句：SQL映射文件

• 执行方法，测试

  /*
    * 条件查询
    * 参数接收：1.散装参数：如果方法中有多个参数，需要使用@Param("SQL参数")
    *         2.对象参数:对象的属性名称要与参数占位符名称一致
    *         3.map集合参数
    * */

测试代码：

 @Test
    public void testSelectByCondition() throws IOException {
        //接收参数
        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. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

        //4.执行方法
        //4.1
        //List<Brand> brands = brandMapper.selectByCondition(status,companyName,brandName);
        //4.2
        //List<Brand> brands = brandMapper.selectByCondition(brand);
        //4.3
        List<Brand> brands = brandMapper.selectByCondition(map);
        System.out.println(brands);
        //5. 释放资源
        sqlSession.close();
    }

BrandMapper

/*
* 条件查询
* 参数接收：1.散装参数：如果方法中有多个参数，需要使用@Param("SQL参数")
*         2.对象参数:对象的属性名称要与参数占位符名称一致
*         3.map集合参数
* */

方法1：//List<Brand> selectByCondition(@Param("status") int status,@Param("companyName") String companyName,@Param("brandName") String brandName);

方法2：//List<Brand> selectByCondition(Brand brand);

方法3：//List<Brand> selectByCondition(Map map);

BrandMapper.xml

  <!--
        条件查询
    -->
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        where status = #{status}
            and company_name like #{companyName}
            and brand_name like #{brandName}
    </select>

3.查询——动态条件查询

3.1多条件查询

brandMapper.xml

 <!--动态条件查询-->
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        where
              <if test="status != null">
                  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>

    </select>

测试代码：
注释掉其中一个map

 Map map = new HashMap();
        map.put("status",status);
        map.put("companyName",companyName);
        //map.put("brandName",brandName);

结果：只输出前两个条件

细节：
如果按上述方法，那么删除第二个就会有where and 的语法错误
解决方法： 恒等式 在where后面加1=1，每个条件都加上and
或者用

  <!--动态条件查询
         *if:条件查询
            *test：逻辑表达式
         *问题：
            *恒等式
    -->
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        where 1=1
              <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>

    </select>

3.2 单条件动态查询

 <!--单条件-动态查询-->
    <select id="selectByConditionSingle" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <choose>
                <when test="status!=null">
                    status = #{status}
                </when>
                <when test="companyName!=null and companyName != ''">
                    company_name like #{companyName}
                </when>
                <when test="brandName!=null and brandName != '' ">
                    brand_name like #{brandName}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>

    </select>

测试代码：

 @Test
    public void testSelectByConditionSingle() throws IOException {
        //接收参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

        //处理参数
        companyName = "%" + companyName +"%";
        brandName = "%" + brandName + "%";

        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        //brand.setCompanyName(companyName);
        //brand.setBrandName(brandName);

        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

     
        List<Brand> brands = brandMapper.selectByConditionSingle(brand);
        System.out.println(brands);
        //5. 释放资源
        sqlSession.close();
    }

二、添加

BrandMapper

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

brandMapper.xml

  <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>

MybatisTest

@Test
    public void testAdd2() throws IOException {
        //接收参数
        int status = 1;
        String companyName = "波导手机";
        String brandName = "波导";
        String description="手机中的战斗机";
        int ordered = 100;


        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setDescription(description);
        brand.setOrdered(ordered);

        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行方法
        brandMapper.add(brand);
        Integer id = brand.getId();
        System.out.println(id);

        //提交事务
        sqlSession.commit();
        //5. 释放资源
        sqlSession.close();
    }
}

三.修改

3.1修改全部字段

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

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

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

3.执行方法，测试

   @Test
    public void update() throws IOException {
        //接收参数
        int status = 1;
        String companyName = "波导手机";
        String brandName = "波导";
        String description="波导手机，手机中的战斗机";
        int ordered = 200;
        int id = 5;

        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);
        brand.setDescription(description);
        brand.setOrdered(ordered);
        brand.setId(id);
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行方法
        int count = brandMapper.update(brand);

        System.out.println(count);

        //提交事务
        sqlSession.commit();
        //5. 释放资源
        sqlSession.close();
    }

3.2 修改动态字段

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

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

2.编写SQL语句

<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>

3.执行方法，测试

  @Test
    public void update() throws IOException {
        //接收参数
        int status = 1;
        String companyName = "波导手机";
        String brandName = "波导";
        String description="波导手机，手机中的战斗机";
        int ordered = 200;
        int id = 5;

        //封装对象
        Brand brand = new Brand();
        brand.setStatus(status);
        //brand.setCompanyName(companyName);
        //brand.setBrandName(brandName);
        //brand.setDescription(description);
        //brand.setOrdered(ordered);
        brand.setId(id);
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行方法
        int count = brandMapper.update(brand);

        System.out.println(count);

        //提交事务
        sqlSession.commit();
        //5. 释放资源
        sqlSession.close();
    }

四.删除

4.1删除一个

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

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

2.编写SQL语句

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

3.执行方法：测试

@Test
    public void testDelete() throws IOException {
        //接收参数
        int id = 6;

        //封装对象
        Brand brand = new Brand();
        brand.setId(id);
        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行方法
        brandMapper.deleteById(id);
        //提交事务
        sqlSession.commit();
        //5. 释放资源
        sqlSession.close();
    }

4.2 批量删除

1.编写接口方法：Mapper接口
参数：id数组
结果：void

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

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

 <delete id="deleteByIds">
        delete from tb_brand where id
        in (
            <foreach collection="ids" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
                                       );
    </delete>

3.执行方法，测试

 @Test
    public void testDeleteByIds() throws IOException {
        //接收参数
        int[] ids = {5,7,8};

        //封装对象

        //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        //3. 执行Mapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行方法
        brandMapper.deleteByIds(ids);
        //提交事务
        sqlSession.commit();
        //5. 释放资源
        sqlSession.close();
    }