<?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"> <!-- The attribute of the if tag is required. The test tag in the if tag must be true. The sql statement in the if tag will be automatically spliced, but not vice versa --> <!-- The value of the test tag should be an expression, and the result is true or false --> <!-- If the param annotation is used, then the test tag writes the parameter name specified by param --> <!-- When this param annotation is not used, param1, param2....--> appear in the test. <!-- When using a POJO object, fill in the property name of the POJO --> <!-- & amp; & amp; cannot be used in dynamic SQL of mybatis, it can only be 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> <!-- The where tag is responsible for implementing the where clause. If we don’t have the where condition, it will not be generated again --> <!-- You can automatically remove the previous and or or, and then automatically splice out the sql statement --> <!-- The and and or at the end of the clause cannot be removed --> <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 removes the prefix suffix=adds the suffix suffixOverrides removes the suffix prefix adds the prefix --> <!-- suffixOverrides removes the suffix: it removes the redundant and or or in the SQL clause. Use | here to represent or --> <!-- If there is no sql clause, no prefix will be added (sql clause prefix)--> <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> <!-- Partial update, if the passed value is not empty, we will update the data --> <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> <!-- Go in first if you encounter it first, go to the last one if all are empty, similar to the if-else statement, pass in a null value--> <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> <!-- The attribute collection of the foreach tag = is used to specify an array or collection item = represents the element of the array or collection separator = the separator between loops --> <delete id="deleteByIds"> delete from t_car where id in <foreach collection="array" item="id" separator="," open="(" close=")"> #{id} </foreach> </delete> <!-- Insert information in batches, insert multiple statements at one time --> <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> </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"> <!-- The attribute of the if tag is required. The test tag in the if tag must be true. The sql statement in the if tag will be automatically spliced, but not vice versa --> <!-- The value of the test tag should be an expression, and the result is true or false --> <!-- If the param annotation is used, then the test tag writes the parameter name specified by param --> <!-- When this param annotation is not used, param1, param2....--> appear in the test. <!-- When using a POJO object, fill in the property name of the POJO --> <!-- & amp; & amp; cannot be used in dynamic SQL of mybatis, it can only be 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> <!-- The where tag is responsible for implementing the where clause. If we don’t have the where condition, it will not be generated again --> <!-- You can automatically remove the previous and or or, and then automatically splice out the sql statement --> <!-- The and and or at the end of the clause cannot be removed --> <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 removes the prefix suffix=adds the suffix suffixOverrides removes the suffix prefix adds the prefix --> <!-- suffixOverrides removes the suffix: it removes the redundant and or or in the SQL clause. Use | here to represent or --> <!-- If there is no sql clause, no prefix will be added (sql clause prefix)--> <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> <!-- Partial update, if the passed value is not empty, we will update the data --> <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> <!-- Go in first if you encounter it first, go to the last one if all are empty, similar to the if-else statement, pass in a null value--> <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> <!-- The attribute collection of the foreach tag = is used to specify an array or collection item = represents the element of the array or collection separator = the separator between loops --> <delete id="deleteByIds"> delete from t_car where id in <foreach collection="array" item="id" separator="," open="(" close=")"> #{id} </foreach> </delete> <!-- Insert information in batches, insert multiple statements at one time --> <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> </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 { //Batch insert, insert multiple car information at one time int insertBatch(@Param("cars") List<Car> cars); //Use foreach tag to delete in batches 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); //Use the where tag to make SQL clauses more intelligent List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType); //Multi-condition query, query based on car brand guide price and car type 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 { //Batch insert, insert multiple car information at one time int insertBatch(@Param("cars") List<Car> cars); //Use foreach tag to delete in batches 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); //Use the where tag to make SQL clauses more intelligent List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType); //Multi-condition query, query based on car brand guide price and car type 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("BYD",3.0,"Fuel Vehicle"); 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("Chang'an",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("BYD",3.0,"Fuel Vehicle"); 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("BYD",3.0,"Fuel Vehicle"); 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,"new energy"); 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,"new energy"); 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("number of entries" + 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","Great Wall",15.0,"2020-10-11","Fuel Car")); cars.add(new Car(null,"1200","Chery",15.0,"2020-10-11","fuel vehicle")); cars.add(new Car(null,"1200","JAC",15.0,"2020-10-11","fuel vehicle")); int count = mapper.insertBatch(cars); System.out.println(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.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("BYD",3.0,"Fuel Vehicle"); 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("Chang'an",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("BYD",3.0,"Fuel Vehicle"); 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("BYD",3.0,"Fuel Vehicle"); 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,"new energy"); 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,"new energy"); 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("number of entries" + 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","Great Wall",15.0,"2020-10-11","Fuel Car")); cars.add(new Car(null,"1200","Chery",15.0,"2020-10-11","fuel vehicle")); cars.add(new Car(null,"1200","JAC",15.0,"2020-10-11","fuel vehicle")); int count = mapper.insertBatch(cars); System.out.println(count); sqlSession.commit(); SqlSessionUtil.close(sqlSession); } }