关于mybatis动态SQL语句的写法

由于mybatis是一个轻量级的框架,SQL语句shi自己写的,可以不断的优化SQL语句,提高查询的效率,近来用到了动态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.qf.dao.CarinfoMapper" >
  <resultMap id="BaseResultMap" type="com.qf.pojo.Carinfo" >  
    <id column="car_id" property="carId" jdbcType="BIGINT" />
    <result column="car_name" property="carName" jdbcType="VARCHAR" />
    <result column="car_color" property="carColor" jdbcType="VARCHAR" />
    <result column="car_price" property="carPrice" jdbcType="DOUBLE" />
    <result column="car_desc" property="carDesc" jdbcType="VARCHAR" />
  </resultMap>
  <sql id="Base_Column_List" >
    car_id, car_name, car_color, car_price, car_desc
  </sql>
  <!-- 动态SQL模糊查询,参数用map传递 -->
  <select id="findLikeCarinfo" parameterType="java.util.Map" resultMap="BaseResultMap">
          select 
          <include refid="Base_Column_List"></include>
          <!--include refid="Base_Column_List"参数不可以丢  -->
          from carinfo
          <where>
                 <if test="carName != null">
                        car_name like #{carName}
                 </if>
                 <if test="carColor != null">
                      and  car_color like #{carColor}
                 </if>
                 <if test="carDesc != null">
                       and car_desc like #{carDesc}
                 </if>
          </where>
  </select>
  <!-- 添加任意字段 -->
  <insert id="addTrimCarInfo" parameterType="java.util.Map">
         insert into carinfo
         <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="carName != null">
                         car_name,
                </if>
                <if test="carColor != null">
                         car_color,
                </if>
                <if test="carPrice != null">
                         car_price,
                </if>
                <if test="carDesc != null">
                         car_desc,
                </if>
         </trim>
         <!-- 关键点，在插入数据的时候，其 prefix="values("不能丢弃-->
         <trim prefix="values(" suffix=")" suffixOverrides=",">
                 <if test="carName != null">
                 #{carName},
                 </if>
                 <if test="carColor != null">
                 #{carColor},
                 </if>
                 <if test="carPrice != null">
                 #{carColor},
                 </if>
                  <if test="carDesc != null">
                 #{carDesc},
                 </if>
         
         </trim>
         
        <!--  (car_name,car_color,car_price,car_desc) values (#{carName},#{carColor},#{carPrice},#{carDesc}) -->
  
  </insert>
  <!-- 根据条件查询数据的时候,二选一 -->
  <select id="selectTiaoJian" parameterType="java.util.Map" resultMap="BaseResultMap">
            select 
            <include refid="Base_Column_List"></include>
            from carinfo where
            <choose>
              <when test="carColor != null">
                   car_color like #{carColor}
              </when>
              <otherwise>
                    car_price='23.4'
              </otherwise>
            </choose>
  </select>
  
  
  <!-- 根据多个参数去删除数据 -->
  <delete id="delMory" parameterType="com.qf.pojo.Carinfo">
        delete from carinfo where car_id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
               #{id}
               <!-- 此处的item是别名的意思，所以此处要使用别名来处理 -->
        </foreach>
  
  </delete>
  
  <!-- 修改任意字段 -->
<update id="updateCarINfo" parameterType="java.util.Map">
      update carinfo
    <set >
         <if test="carName != null">
                car_name=#{carName},
         </if>
         <if test="carColor != null">
               car_color=#{carColor},
         </if>
         <if test="carDesc != null">
               car_desc=#{carDesc},
         </if>
         <if test="carPrice != null">
               car_price=#{carPrice},
         </if>
    </set>
          where car_id = # {carId}
</update>  
  
  
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
    
    select 
    <include refid="Base_Column_List" />
    from carinfo
    where car_id = #{carId,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
    
    delete from carinfo
    where car_id = #{carId,jdbcType=BIGINT}
  </delete>
  <insert id="insert" parameterType="com.qf.pojo.Carinfo" >
    
    insert into carinfo (car_id, car_name, car_color, 
      car_price, car_desc)
    values (#{carId,jdbcType=BIGINT}, #{carName,jdbcType=VARCHAR}, #{carColor,jdbcType=VARCHAR}, 
      #{carPrice,jdbcType=DOUBLE}, #{carDesc,jdbcType=VARCHAR})
  </insert>
  <insert id="insertSelective" parameterType="com.qf.pojo.Carinfo" >
    
    insert into carinfo
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="carId != null" >
        car_id,
      </if>
      <if test="carName != null" >
        car_name,
      </if>
      <if test="carColor != null" >
        car_color,
      </if>
      <if test="carPrice != null" >
        car_price,
      </if>
      <if test="carDesc != null" >
        car_desc,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="carId != null" >
        #{carId,jdbcType=BIGINT},
      </if>
      <if test="carName != null" >
        #{carName,jdbcType=VARCHAR},
      </if>
      <if test="carColor != null" >
        #{carColor,jdbcType=VARCHAR},
      </if>
      <if test="carPrice != null" >
        #{carPrice,jdbcType=DOUBLE},
      </if>
      <if test="carDesc != null" >
        #{carDesc,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.qf.pojo.Carinfo" >
    
    update carinfo
    <set >
      <if test="carName != null" >
        car_name = #{carName,jdbcType=VARCHAR},
      </if>
      <if test="carColor != null" >
        car_color = #{carColor,jdbcType=VARCHAR},
      </if>
      <if test="carPrice != null" >
        car_price = #{carPrice,jdbcType=DOUBLE},
      </if>
      <if test="carDesc != null" >
        car_desc = #{carDesc,jdbcType=VARCHAR},
      </if>
    </set>
    where car_id = #{carId,jdbcType=BIGINT}
  </update>
  
  <update id="updateByPrimaryKey" parameterType="com.qf.pojo.Carinfo" >
    
    update carinfo
    set car_name = #{carName,jdbcType=VARCHAR},
      car_color = #{carColor,jdbcType=VARCHAR},
      car_price = #{carPrice,jdbcType=DOUBLE},
      car_desc = #{carDesc,jdbcType=VARCHAR}
    where car_id = #{carId,jdbcType=BIGINT}
  </update>
</mapper>