mybatis之编写动态sql

创建汽车表(t_car)

创建对应的实体类Car

import java.util.Objects;

public class Car {
    // 注意使用包装数据类型
    private Long id;
    private String carNum;
    private String brand;
    private Double guidePrice;
    private String produceTime;
    private String carType;
    public Car() {
    }

    public Car(Long id, String carNum, String brand, Double guidePrice, String produceTime, String carType) {
        this.id = id;
        this.carNum = carNum;
        this.brand = brand;
        this.guidePrice = guidePrice;
        this.produceTime = produceTime;
        this.carType = carType;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getCarNum() {
        return carNum;
    }

    public void setCarNum(String carNum) {
        this.carNum = carNum;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public Double getGuidePrice() {
        return guidePrice;
    }

    public void setGuidePrice(Double guidePrice) {
        this.guidePrice = guidePrice;
    }

    public String getProduceTime() {
        return produceTime;
    }

    public void setProduceTime(String produceTime) {
        this.produceTime = produceTime;
    }

    public String getCarType() {
        return carType;
    }

    public void setCarType(String carType) {
        this.carType = carType;
    }

    @Override
    public String toString() {
        return "Car{" +
                "id=" + id +
                ", carNum='" + carNum + '\'' +
                ", brand='" + brand + '\'' +
                ", guidePrice=" + guidePrice +
                ", produceTime='" + produceTime + '\'' +
                ", carType='" + carType + '\'' +
                '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Car car = (Car) o;
        return Objects.equals(getId(), car.getId()) && Objects.equals(getCarNum(), car.getCarNum()) && Objects.equals(getBrand(), car.getBrand()) && Objects.equals(getGuidePrice(), car.getGuidePrice()) && Objects.equals(getProduceTime(), car.getProduceTime()) && Objects.equals(getCarType(), car.getCarType());
    }

    @Override
    public int hashCode() {
        return Objects.hash(getId(), getCarNum(), getBrand(), getGuidePrice(), getProduceTime(), getCarType());
    }
}

编写mybatis配置文件和配置文件(自己提供即可)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <properties resource="jdbc.properties"/>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <package name="mybatis.pojo"/>
    </typeAliases>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="CarMapper.xml"/>
    </mappers>

</configuration>

提供mapper接口

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface CarMapper {
    /**
     *
     * @param brand 汽车品牌
     * @param guidePrice 指导价格
     * @param carType 汽车类型
     * @return
     */
    List<Car> selectByManyCondition(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);

    List<Car> selectByManyConditionWithWhere(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);

    List<Car> selectByManyConditionWithTrim(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);

    /**
     * 更新原数据库，其中只更新传进来的car中不为空的字段
     * @param car
     * @return
     */
    int updateByIdAndSet(Car car);

    /**
     * 使用choose when otherwise 实现：如果提供了品牌则按照品牌查询，否则如果提供了知道价格则按照指导价格查询，否则如果提供了类型，则按照类型来查找
     * @param brand
     * @param guidePrice
     * @param carType
     * @return
     */
    List<Car> selectByChoose(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);

    int deleteByIds(@Param("ids") Long[] ids);

    int insertByBatch(@Param("cars") List<Car> cars);
}

selectByManyCondition

这个方法中提供了三个参数，但是有可能某个为空，或者不提供，那么怎么动态的生成sql语句。

mapper文件中，可以使用if语句加判断：

    <select id="selectByManyCondition" resultType="car">
    <!--为什么加上1=1，因为如果三个参数都不提供的话，没有1=1会导致sql语句最后是where-->
    select * from t_car where 1 = 1

    <if test="brand != null and brand != ''">and brand like "%"#{brand}"%" </if>

    <if test="guidePrice != null and guidePrice != ''"> and guide_price > #{guidePrice} </if>

    <if test="carType != null and carType != ''"> and car_type= #{carType}</if>
    </select>

selectByManyConditionWithWhere方法

这个方法是使用where语句来完成与上一个语句相同的功能：

<select id="selectByManyConditionWithWhere" resultType="car">
        select * from t_car
        <where>
            <if test="brand != null and brand != ''">brand like "%"#{brand}"%" </if>

            <if test="guidePrice != null and guidePrice != ''"> and guide_price > #{guidePrice} </if>

            <if test="carType != null and carType != ''"> and car_type= #{carType}</if>
        </where>
        <!--where专门负责where语句的动态生成的， 当后面都不成立时不会加where， 也会自动去掉and 和or， 但是只能去掉语句前面的and-->
    </select>

selectByManyConditionWithTrim方法

这个方法是使用trim语句来完成与上一个语句相同的功能：

<select id="selectByManyConditionWithTrim" resultType="car">
        select * from t_car
            <!--prefix="where" 动态加where-->
        <trim prefix="where" suffixOverrides="and|or">
            <if test="brand != null and brand != ''">
                brand like "%"#{brand}"%"  and
            </if>

            <if test="guidePrice != null and guidePrice != ''">
                guide_price > #{guidePrice} and
            </if>

            <if test="carType != null and carType != ''">
                car_type= #{carType}
            </if>

        </trim>
<!--<trim prefix="加前缀" prefixOverrides="删除前缀" suffix="加后缀" suffixOverrides="删除后缀"/>-->
    </select>

selectByChoose方法

这个方法是使用choose语句来完成与上一个语句相同的功能：

<select id="selectByChoose" resultType="car">
     select * from t_car
     <where>
         <choose>
             <when test="brand != null and brand != ''"> brand like "%"#{brand}"%"</when>
             <when test="guidePrice != null and guidePrice != ''"> guide_price > #{guidePrice}</when>
             <when test="carType != null and carType != ''"> car_type = #{carType}</when>
             <otherwise></otherwise>
         </choose>
     </where>
 </select>

updateByIdAndSet方法：

通过id来更新数据库中对应的信息，但是有时只想更改提供的字段，而不更改其他字段的值
使用set和if语句：

<update id="updateByIdAndSet">
        update t_car
        <set>
            <if test="carNum!=null and carNum!=''">car_num = #{carNum},</if>
            <if test="brand!=null and brand!=''">brand = #{brand},</if>
            <if test="guidePrice!=null and guidePrice!=''">guide_price = #{guidePrice},</if>
            <if test="produceTime!=null and produceTime!=''">produce_time = #{produceTime},</if>
            <if test="carType!=null and carType!=''">car_type = #{carType},</if>
        </set>
        where id = #{id}
    </update>

deleteByIds方法

通过提供一个id的列表来删除数据库中对应的数据，使用foreach语句：

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

或者是：

<!--
       如果方法参数上加上了@param注解， collection可以写注解内的内容，否则需要写arg0...
       item 上的名字可以随便写，但是注意与语句内的#{}相匹配即可
       separator 是分割的符号，主要不要自己写，因为会导致最后多一个符号
        open 表示整个foreach的最开始是什么符号开头
        close表示整个foreach的最结尾是什么符号结束
-->
  <delete id="deleteByIds">
      delete  from t_car where id in
             <foreach collection="ids" item="id" separator="," open="(" close=")">
                 #{id}
             </foreach>
  </delete>

或者：

<delete id="deleteByIds">
       delete from t_car where
       <foreach collection="ids" item="id" separator="or">
           id=#{id}
       </foreach>

</delete>

insertByBatch方法

通过一个Car列表来批量添加数据，使用foreach语句：

    <insert id="insertByBatch">
        insert into t_car values
            <foreach collection="cars" item="car" separator=",">
                (null, #{car.carNum}, #{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
            </foreach>
    </insert>