JavaWeb学习Day5——Mybatis案例

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第14天，点击查看活动详情

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

配置文件完成增删改查

实现思路——三个步骤

1.完成该业务需要使用什么sql语句？

2.需要什么参数？id?name?

3.返回结果？ List集合？ True or Flase？

1.查询

• 查询所有数据

第一步：

编写接口方法：

public interface BrandMapper {
  List<Brand> selectAll();
}

第二步：

编写SQL：

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


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

第三步：

执行方法：

public void test() throws Exception{
    //1.获取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.获取UserMapper代理对象
      BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
      List<Brand> brands = brandMapper.selectAll();
      System.out.println(brands);
      sqlSession.close();
}

数据库字段名称和实体类属性名称不一致（数据库中用_，类中用驼峰命名）解决方法：

1.起别名 （不方便）

2.映射（灵活）

<!--
    namespace:名称空间
    id:唯一表示，随便起
    type：映射的类型
    column：数据库中的字段名
    property：实体类中的属性名
-->

<mapper namespace="com.miles.mapper.BrandMapper">
<!--
id:主键的映射
result:普通列名的映射    
-->
    <resultMap id="BrandResultMap" type="brand">
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>

    <select id="selectAll" resultMap="BrandResultMap">
        select *
        from tb_brand;
    </select>
</mapper>

• 根据id查询数据

基本步骤同上：

BrandMapper.xml:

<select id="selectById" resultType="user">
    select *
    from tb_user where id = #{id};

</select>

相关知识点

参数占位符：

1.#{}:会将其替换为？，可以防止sql注入

2.${}：拼sql，会存在sql注入问题

使用场景：

1.参数传递时：#{}

2.表名或列名不确定时，需要通过拼sql传进

• 多条件查询

sql语句设置多个参数的三种方式：

以第二种方式为例：

public interface BrandMapper {
  List<Brand> selectAll();
  Brand selectById(int id);
  List<Brand> selectByCondition(Brand brand);
}

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

@Test
public void test2() throws Exception{
    int status = 1;
    String brandName = "华为";
    String companyName ="华为";
    companyName = "%"+companyName+"%";
    brandName = "%"+brandName+"%";


    Brand brand1 = new Brand();
    brand1.setStatus(status);
    brand1.setBrandName(brandName);
    brand1.setCompanyName(companyName);

    //1.获取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.获取UserMapper代理对象
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    List<Brand> brands = brandMapper.selectByCondition(brand1);
    System.out.println(brands);
    sqlSession.close();
}

• 多条件 动态条件 查询
  动态sql：sql语句会随着用户的输入或者外界的变化而变化

<!--
动态条件查询：
    *if: 条件判断
       *test:逻辑表达式
    *问题:如果status为空，就会报错
    解决方法：使用where标签
-->
<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>
    </where>


</select>

• 单条件 动态条件 查询

public interface BrandMapper {
 List<Brand> selectAll();
 Brand selectById(int id);
 List<Brand> selectByCondition(Brand brand);
 List<Brand> selectByConditionSingle(Brand brand);
}

where标签+choose标签，类似于switch语句

<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>
    </choose>
    </where>
</select>

2.添加

注：insert操作需要再commit一下或者openSession(true)自动提交事务

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

</insert>

    @Test
    public void test4() throws Exception{
        int status = 1;
        String brandName = "波导手机";
        String companyName ="波导";
        String description = "手机中的战斗机";
        int ordered = 100;
//        companyName = "%"+companyName+"%";
//        brandName = "%"+brandName+"%";

        Brand brand1 = new Brand();
        brand1.setStatus(status);
        brand1.setBrandName(brandName);
        brand1.setCompanyName(companyName);
        brand1.setDescription(description);
        brand1.setOrdered(ordered);

        //1.获取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.获取UserMapper代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行方法
        brandMapper.add(brand1);
        //5.提交事务
        //sqlSession.commit();
        sqlSession.close();
    }

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

Integer id = brand1.getId();

System.out.println(id);

3.修改

• 修改动态字段

set标签+if标签

<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>
    where id =#{id};
    </set>

</update>

 @Test
    public void test5() throws Exception{
        int status = 0;
        String brandName = "波导手机";
        String companyName ="波导";
        String description = "手机中的战斗机";
        int ordered = 100;
        int id=6;
        Brand brand1 = new Brand();
        brand1.setId(id);
        brand1.setStatus(status);
        brand1.setBrandName(brandName);
        brand1.setCompanyName(companyName);
//        brand1.setDescription(description);
//        brand1.setOrdered(ordered);

        //1.获取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.获取UserMapper代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        //4.执行方法
        int count = brandMapper.update(brand1);
        //5.提交事务
        //sqlSession.commit();
        System.out.println(count);
        sqlSession.close();
    }

4.删除

• 批量删除

void deleteByIds(int[] ids);

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

@Test
public void test6() throws Exception{
    int[] ids = {5,7,8};

    //1.获取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.获取UserMapper代理对象
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
    //4.执行方法
    brandMapper.deleteByIds(ids);
    //5.提交事务
    sqlSession.close();
}