Mybatis-配置文件完成增删改查

Mybatis-配置文件完成增删改查

• 开发步骤

  在Mapper接口的配置文件中添加namespace

  <mapper namespace="...">
  

  • 编写接口方法

    • Mapper接口方法

      public interface BrandMapper {
          /*查询所有*/
          List<Brand selectAll();
          /*id查询*/
          Brand selectById(int id);
          /*条件查询*/
          List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
      
      
      }
      

    • pojo.Brand类

      package com.main.pojo;
      
      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 +
                      '}';
          }
      }
      
      

    • 编写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.main.mapper.BrandMapper">
      
          <resultMap id="brandResultMap" type="brand">
              <result column="brand_name" property="brandName"/>
              <result column="company_name" property="companyName"/>
          </resultMap>
          <!--    添加信息-->
          <insert id="add" useGeneratedKeys="true" keyProperty="id">
              insert into tb_brand(brand_name, company_name, ordered, description, status)
                  value (#{brandName},#{companyName},#{ordered},#{description},#{status});
          </insert>
          <!-- 修改信息-->
          <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>
          <!--    删除数据-->
          <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>
      
      
          <!--    查看所有数据-->
          <select id="selectAll" resultMap="brandResultMap">
              select *
              from tb_brand;
          </select>
          <!--    查看详情-->
          <select id="selectById" resultMap="brandResultMap">
              select *
              from tb_brand
              where id = #{id};
          </select>
          <!--    条件动态查询
                      * if:条件判断
                          * test：逻辑表达式
                      * 问题:如果第一个条件为空的情况，会造成where后面直接跟and
                          * <where>标签
          -->
          <select id="selectByCondition" resultMap="brandResultMap">
              select *
              from tb_brand
              <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>
          <!--   单条件查询 -->
          <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=#{companyName}
                      </when>
                      <when test="brandName !=null and brandName != ''">
                          brand_name=#{brandName}
                      </when>
      
      
                  </choose>
              </where>
          </select>
      
      
      </mapper>
      

    • 测试

• 小细节

  • 参数占位符

    • Mybatis里有两种参数占位符

      • #()

        #是将传入的值当成字符串形式

        很大程度上防止sql注入

      • $()

        $是将传入的数据直接显示生成sql语句

      • 使用场景

        • 参数传递，使用#{}
        • 对数据进行动态设置，用${}进行sql拼接

    • 例：id–>1

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

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

  • parameterType：用于设置参数类型，该参数可以省略
  • SQL语句中特殊字符处理
    • 使用转义字符
    • <! [CDATA [内容]]> ：输入的内容会被当初文本框
  • 添加数据到数据库是默认开启事务，需要手动提交事务