Mybatis通过forEach批量删除数据的两种实现方法-----Mybatis框架

ゞ浴缸里的玫瑰 2024-03-03 08:34 106阅读 0赞

>     <?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">
>     
>     
>     
>     
>     
>     
>         <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>
>     
>     
>     
>         <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>
>     
>     
>     
>         <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>
>     
>         <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>
>     
>         <delete id="deleteByIds">
>             delete from t_car where id in
>             <foreach collection="array" item="id" separator="," open="(" close=")">
>                 #{id}
>             </foreach>
>         </delete>
>     
>         <insert id="insertBatch">
>             insert into t_car values
>             <foreach collection="cars" item="car" separator=",">
>                 (null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
>             </foreach>
>         </insert>
>         <delete id="deleteBatchById">
>             delete from t_car where
>             <foreach collection="ids" separator="or" item="id">
>                 id = #{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">
>     
>     
>     
>     
>     
>     
>         <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>
>     
>     
>     
>         <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>
>     
>     
>     
>         <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>
>     
>         <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>
>     
>         <delete id="deleteByIds">
>             delete from t_car where id in
>             <foreach collection="array" item="id" separator="," open="(" close=")">
>                 #{id}
>             </foreach>
>         </delete>
>     
>         <insert id="insertBatch">
>             insert into t_car values
>             <foreach collection="cars" item="car" separator=",">
>                 (null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
>             </foreach>
>         </insert>
>         <delete id="deleteBatchById">
>             delete from t_car where
>             <foreach collection="ids" separator="or" item="id">
>                 id = #{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
>     {
>         int deleteBatchById(@Param("ids")Long[] ids);
>         //批量插入,一次插入多条car信息
>         int insertBatch(@Param("cars") List<Car> cars);
>         //使用foreach标签批量删除
>         int deleteByIds(@Param("ids") 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
>     {
>         int deleteBatchById(@Param("ids")Long[] ids);
>         //批量插入,一次插入多条car信息
>         int insertBatch(@Param("cars") List<Car> cars);
>         //使用foreach标签批量删除
>         int deleteByIds(@Param("ids") 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.ArrayList;
>     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 = {48L,16L,17L};
>             int count = mapper.deleteByIds(ids);
>             logger.info("条数" + count);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>         @Test
>         public void TestinsertBatch()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             CarMapper mapper = sqlSession.getMapper(CarMapper.class);
>             List<Car> cars = new ArrayList<>();
>             cars.add(new Car(null,"1200","长城",15.0,"2020-10-11","燃油车"));
>             cars.add(new Car(null,"1200","奇瑞",15.0,"2020-10-11","燃油车"));
>             cars.add(new Car(null,"1200","江淮",15.0,"2020-10-11","燃油车"));
>             int count = mapper.insertBatch(cars);
>             System.out.println(count);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>         @Test
>         public void TestdeleteBatchById()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             CarMapper mapper = sqlSession.getMapper(CarMapper.class);
>             Long[] ids = {4L};
>             mapper.deleteBatchById(ids);
>             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.ArrayList;
>     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 = {48L,16L,17L};
>             int count = mapper.deleteByIds(ids);
>             logger.info("条数" + count);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>         @Test
>         public void TestinsertBatch()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             CarMapper mapper = sqlSession.getMapper(CarMapper.class);
>             List<Car> cars = new ArrayList<>();
>             cars.add(new Car(null,"1200","长城",15.0,"2020-10-11","燃油车"));
>             cars.add(new Car(null,"1200","奇瑞",15.0,"2020-10-11","燃油车"));
>             cars.add(new Car(null,"1200","江淮",15.0,"2020-10-11","燃油车"));
>             int count = mapper.insertBatch(cars);
>             System.out.println(count);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>         @Test
>         public void TestdeleteBatchById()
>         {
>             SqlSession sqlSession = SqlSessionUtil.openSession();
>             CarMapper mapper = sqlSession.getMapper(CarMapper.class);
>             Long[] ids = {4L};
>             mapper.deleteBatchById(ids);
>             sqlSession.commit();
>             SqlSessionUtil.close(sqlSession);
>         }
>     }