Mybatis通过forEach批量删除的自动实现-----Mybatis框架

<?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.powernode.mybatis.mappers.CarMapper">
<!--    if标签的属性是必须的,if标签中test标签必须为true,if标签内的sql语句就会自动拼接,反过来则不会-->
<!--    test标签的值应该是一个表达式,结果为true或者false即可-->
<!--    如果使用了param注解,那么test标签写param指定的参数名-->
<!--    当没有使用使用这个param注解,则test中出现的是param1,param2....-->
<!--    当使用的是一个POJO对象,则填入的则为POJO的属性名字-->
<!--    mybatis的动态SQL中不能使用&&只能是and-->
    <select id="selectByMultiCondition" resultType="Car">
        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>
<!--    where标签是负责实现where子句的,如果我们没有where的条件,就不会再生成了-->
<!--    可以自动的去掉前面的and或者or,然后自动拼接出sql语句-->
<!--    在子句的后面的and和or不能去掉-->
    <select id="selectByMultiConditionWithWhere" 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>
    </select>
<!--    prefixOverrides去掉前缀 suffix=加后缀 suffixOverrides去掉后缀 prefix加前缀-->
<!--    suffixOverrides去掉后缀:就是把SQL子句中多余的and或or去掉,这里用|就代表或者-->
<!--    没有任何sql子句的话,就不会添加前缀了(sql子句前缀)-->
    <select id="selectByMultiConditionWithTrim" resultType="Car">
        select * from t_car
        <trim suffixOverrides="and|or" prefix="where">
            <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} and
            </if>
        </trim>
    </select>
<!--    局部更新,如果传递的值不为空,我们才实现更新数据-->
    <update id="updateById">
        update t_car set
            car_num = #{carNum},
            brand = #{brand},
            guide_price = #{guidePrice},
            produce_time = #{produceTime},
            car_type = #{carType}
        where
            id = #{id};
    </update>
    <update id="updateBySet">
        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>
<!--    先遇到就先进去,全部为空就走最后一个,和if-else语句类似,传进去一个null值-->
    <select id="selectWith" 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>
                <otherwise>
                    car_type = #{carType}
                </otherwise>
            </choose>
        </where>
    </select>
<!--    foreach标签的属性collection=用于指定数组或者集合 item=代表数组或集合的元素 separator=循环之间的分隔符-->
    <delete id="deleteByIds">
        delete from t_car where id in (
            <foreach collection="array" item="id" separator=",">
                #{id}
            </foreach>
        );
    </delete>
</mapper>
<?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.powernode.mybatis.mappers.CarMapper">
<!--    if标签的属性是必须的,if标签中test标签必须为true,if标签内的sql语句就会自动拼接,反过来则不会-->
<!--    test标签的值应该是一个表达式,结果为true或者false即可-->
<!--    如果使用了param注解,那么test标签写param指定的参数名-->
<!--    当没有使用使用这个param注解,则test中出现的是param1,param2....-->
<!--    当使用的是一个POJO对象,则填入的则为POJO的属性名字-->
<!--    mybatis的动态SQL中不能使用&&只能是and-->
    <select id="selectByMultiCondition" resultType="Car">
        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>
<!--    where标签是负责实现where子句的,如果我们没有where的条件,就不会再生成了-->
<!--    可以自动的去掉前面的and或者or,然后自动拼接出sql语句-->
<!--    在子句的后面的and和or不能去掉-->
    <select id="selectByMultiConditionWithWhere" 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>
    </select>
<!--    prefixOverrides去掉前缀 suffix=加后缀 suffixOverrides去掉后缀 prefix加前缀-->
<!--    suffixOverrides去掉后缀:就是把SQL子句中多余的and或or去掉,这里用|就代表或者-->
<!--    没有任何sql子句的话,就不会添加前缀了(sql子句前缀)-->
    <select id="selectByMultiConditionWithTrim" resultType="Car">
        select * from t_car
        <trim suffixOverrides="and|or" prefix="where">
            <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} and
            </if>
        </trim>
    </select>
<!--    局部更新,如果传递的值不为空,我们才实现更新数据-->
    <update id="updateById">
        update t_car set
            car_num = #{carNum},
            brand = #{brand},
            guide_price = #{guidePrice},
            produce_time = #{produceTime},
            car_type = #{carType}
        where
            id = #{id};
    </update>
    <update id="updateBySet">
        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>
<!--    先遇到就先进去,全部为空就走最后一个,和if-else语句类似,传进去一个null值-->
    <select id="selectWith" 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>
                <otherwise>
                    car_type = #{carType}
                </otherwise>
            </choose>
        </where>
    </select>
<!--    foreach标签的属性collection=用于指定数组或者集合 item=代表数组或集合的元素 separator=循环之间的分隔符-->
    <delete id="deleteByIds">
        delete from t_car where id in (
            <foreach collection="array" item="id" separator=",">
                #{id}
            </foreach>
        );
    </delete>
</mapper>
package com.powernode.mybatis.mappers;

import com.powernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface CarMapper
{
    //使用foreach标签批量删除
    int deleteByIds(Long[] ids);
    List<Car> selectWith(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
    int updateBySet(Car car);
    int updateById(Car car);
    List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
    //使用where标签让SQL子句更加智能
    List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
    //多条件查询,根据汽车品牌指导价和汽车类型实现查询
    List<Car> selectByMultiCondition(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);

}
package com.powernode.mybatis.mappers;

import com.powernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface CarMapper
{
    //使用foreach标签批量删除
    int deleteByIds(Long[] ids);
    List<Car> selectWith(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
    int updateBySet(Car car);
    int updateById(Car car);
    List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
    //使用where标签让SQL子句更加智能
    List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
    //多条件查询,根据汽车品牌指导价和汽车类型实现查询
    List<Car> selectByMultiCondition(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);

}

package com.powernode.mybatis.Test;

import com.powernode.mybatis.mappers.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

public class TestCarMapper
{
    private static final Logger logger = LoggerFactory.getLogger(TestCarMapper.class);
    @Test
    public void TestSelectByMultiCondition()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiCondition("比亚迪",3.0,"燃油车");
        carList.forEach(car -> {
            logger.info(car.toString());
        });
        SqlSessionUtil.close(sqlSession);
    }
    @Test
    public void TestNull()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiCondition(null,null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestNum()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiCondition("长安",null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestSelectByMultiConditionWithWhere()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithWhere("比亚迪",3.0,"燃油车");
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestNullFor()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithWhere(null,null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestNullOne()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithWhere(null,3.0,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestSelectByMultiConditionWithTrim()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithTrim("比亚迪",3.0,"燃油车");
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestSelectByMultiNull()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithTrim(null,null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestupdateById()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(5L,null,null,null,null,"新能源");
        int count = mapper.updateById(car);
        sqlSession.commit();
        SqlSessionUtil.close(sqlSession);
    }
    @Test
    public void TestupdateBySet()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(49L,null,null,null,null,"新能源");
        int count = mapper.updateBySet(car);
        logger.info("" + count);
        sqlSession.commit();
        SqlSessionUtil.close(sqlSession);
    }
    @Test
    public void TestselectWith()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectWith(null,null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
        SqlSessionUtil.close(sqlSession);
    }
    @Test
    public void TestdeleteByIds()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long[] ids = {5L,47L};
        int count = mapper.deleteByIds(ids);
        logger.info("条数" + count);
        sqlSession.commit();
        SqlSessionUtil.close(sqlSession);
    }
}
package com.powernode.mybatis.Test;

import com.powernode.mybatis.mappers.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

public class TestCarMapper
{
    private static final Logger logger = LoggerFactory.getLogger(TestCarMapper.class);
    @Test
    public void TestSelectByMultiCondition()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiCondition("比亚迪",3.0,"燃油车");
        carList.forEach(car -> {
            logger.info(car.toString());
        });
        SqlSessionUtil.close(sqlSession);
    }
    @Test
    public void TestNull()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiCondition(null,null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestNum()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiCondition("长安",null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestSelectByMultiConditionWithWhere()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithWhere("比亚迪",3.0,"燃油车");
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestNullFor()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithWhere(null,null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestNullOne()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithWhere(null,3.0,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestSelectByMultiConditionWithTrim()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithTrim("比亚迪",3.0,"燃油车");
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestSelectByMultiNull()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectByMultiConditionWithTrim(null,null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
    }
    @Test
    public void TestupdateById()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(5L,null,null,null,null,"新能源");
        int count = mapper.updateById(car);
        sqlSession.commit();
        SqlSessionUtil.close(sqlSession);
    }
    @Test
    public void TestupdateBySet()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car = new Car(49L,null,null,null,null,"新能源");
        int count = mapper.updateBySet(car);
        logger.info("" + count);
        sqlSession.commit();
        SqlSessionUtil.close(sqlSession);
    }
    @Test
    public void TestselectWith()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        List<Car> carList = mapper.selectWith(null,null,null);
        carList.forEach(car -> {
            logger.info(car.toString());
        });
        SqlSessionUtil.close(sqlSession);
    }
    @Test
    public void TestdeleteByIds()
    {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Long[] ids = {5L,47L};
        int count = mapper.deleteByIds(ids);
        logger.info("条数" + count);
        sqlSession.commit();
        SqlSessionUtil.close(sqlSession);
    }
}
<!--    foreach标签的属性collection=用于指定数组或者集合 item=代表数组或集合的元素 separator=循环之间的分隔符-->
    <delete id="deleteByIds">
        delete from t_car where id in
        <foreach collection="array" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
<!--    foreach标签的属性collection=用于指定数组或者集合 item=代表数组或集合的元素 separator=循环之间的分隔符-->
    <delete id="deleteByIds">
        delete from t_car where id in
        <foreach collection="array" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

旧约Alatus

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值