12、动态SQL
什么是动态SQL？
动态 SQL 是 MyBatis 的强大特性之一。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
环境搭建：
依赖：
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.11</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
</dependencies>
1、创建sql
CREATE TABLE `t_car` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键自增', `car_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '汽车编号', `brand` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '汽车品牌', `guide_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '厂家指导价', `produce_time` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '生产日期', `car_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '汽车类型，包括：燃油车，电车，氢能源', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_car -- ---------------------------- INSERT INTO `t_car` VALUES (1, '100', '宝马520Li', 41.00, '2022-09-01', '燃油车'); INSERT INTO `t_car` VALUES (2, '101', '奔驰E300L', 54.00, '2022-08-01', '电车'); INSERT INTO `t_car` VALUES (5, '103', '丰田', 25.80, '2022-01-01', '燃油车'); INSERT INTO `t_car` VALUES (7, '104', '比亚迪汉', 10.00, '2022-11-17', '电车'); INSERT INTO `t_car` VALUES (8, '105', '奥迪', 35.50, '2022-11-11', '燃油车'); SET FOREIGN_KEY_CHECKS = 1;
2、Mybatis配置文件：resource/mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="db.properties"/> <!--日志--> <settings> <setting name="logImpl" value="STDOUT_LOGGING"/> </settings> <!--设置实体类别名--> <typeAliases> <package name="com.zhixi.pojo"/> </typeAliases> <!--数据连接池--> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <mappers> <mapper resource="mapper/CarMapper.xml"/> </mappers> </configuration>
3、数据库连接信息：resource/db.properties
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/bjpowernode-mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC jdbc.username=root jdbc.password=zhixi158
4、Mybatis工具类：com.zhixi.utils.MabatisUtil
package com.zhixi.utils; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; /** * @ClassName MybatisUtil * @Author zhangzhixi * @Description * @Date 2022-11-18 10:37 * @Version 1.0 */ public class MybatisUtil { /** * 线程工厂 */ private static SqlSessionFactory sqlSessionFactory; /** * 保证事务 */ private static final ThreadLocal<SqlSession> THREAD_LOCAL = new ThreadLocal<>(); static { try { sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml")); } catch (IOException e) { e.printStackTrace(); } } /** * 获取连接通道 * * @return sqlSession操作数据库 */ public static SqlSession openSession() { SqlSession sqlSession = THREAD_LOCAL.get(); if (sqlSession == null) { sqlSession = sqlSessionFactory.openSession(); THREAD_LOCAL.set(sqlSession); } return sqlSession; } /** * 关闭连接对象 * @param sqlSession SQL会话 */ public static void close(SqlSession sqlSession){ if (THREAD_LOCAL.get() != null) { sqlSession.close(); THREAD_LOCAL.remove(); } } }
5、实体类：com.zhixi.pojo.Car
package com.zhixi.pojo; import lombok.Builder; import java.io.Serializable; import java.math.BigDecimal; /** * * @TableName t_car */ @Builder public class Car implements Serializable { /** * 主键自增 */ private Long id; /** * 汽车编号 */ private String carNum; /** * 汽车品牌 */ private String brand; /** * 厂家指导价 */ private BigDecimal guidePrice; /** * 生产日期 */ private String produceTime; /** * 汽车类型，包括：燃油车，电车，氢能源 */ private String carType; private static final long serialVersionUID = 1L; /** * 主键自增 */ 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 BigDecimal getGuidePrice() { return guidePrice; } /** * 厂家指导价 */ public void setGuidePrice(BigDecimal 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 boolean equals(Object that) { if (this == that) { return true; } if (that == null) { return false; } if (getClass() != that.getClass()) { return false; } Car other = (Car) that; return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId())) && (this.getCarNum() == null ? other.getCarNum() == null : this.getCarNum().equals(other.getCarNum())) && (this.getBrand() == null ? other.getBrand() == null : this.getBrand().equals(other.getBrand())) && (this.getGuidePrice() == null ? other.getGuidePrice() == null : this.getGuidePrice().equals(other.getGuidePrice())) && (this.getProduceTime() == null ? other.getProduceTime() == null : this.getProduceTime().equals(other.getProduceTime())) && (this.getCarType() == null ? other.getCarType() == null : this.getCarType().equals(other.getCarType())); } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((getId() == null) ? 0 : getId().hashCode()); result = prime * result + ((getCarNum() == null) ? 0 : getCarNum().hashCode()); result = prime * result + ((getBrand() == null) ? 0 : getBrand().hashCode()); result = prime * result + ((getGuidePrice() == null) ? 0 : getGuidePrice().hashCode()); result = prime * result + ((getProduceTime() == null) ? 0 : getProduceTime().hashCode()); result = prime * result + ((getCarType() == null) ? 0 : getCarType().hashCode()); return result; } @Override public String toString() { StringBuilder sb = new StringBuilder(); sb.append(getClass().getSimpleName()); sb.append(" ["); sb.append("Hash = ").append(hashCode()); sb.append(", id=").append(id); sb.append(", carNum=").append(carNum); sb.append(", brand=").append(brand); sb.append(", guidePrice=").append(guidePrice); sb.append(", produceTime=").append(produceTime); sb.append(", carType=").append(carType); sb.append(", serialVersionUID=").append(serialVersionUID); sb.append("]"); return sb.toString(); } }
6、mapper接口：com.zhixi.mapper.CarMapper
package com.zhixi.mapper; import com.zhixi.pojo.Car; import org.apache.ibatis.annotations.MapKey; import org.apache.ibatis.annotations.Param; import java.math.BigDecimal; import java.util.List; import java.util.Map; /** * @author zhixi * @description 针对表【t_car】的数据库操作Mapper * @createDate 2022-11-19 22:36:54 * @Entity com.zhixi.pojo.Car */ public interface CarMapper { /** * 查询所有的记录 * * @return 汽车集合 */ List<Car> selectAllCar(); /** * 按照条件进行查询，最多只能够有一个条件成立 * * @param carNum 品牌编号 * @param brand 品牌名称 * @param carType 车辆类型 * @return 查询到的数据结果 */ List<Car> selectCarByChoose(@Param("carNum") String carNum, @Param("brand") String brand, @Param("carType") String carType); /** * 批量删除-in * * @param ids id数组 * @return 删除的记录条数 */ int deleteByIdsToIn(@Param("ids") Long[] ids); /** * 批量删除-or * * @param ids id数组 * @return 删除的记录条数 */ int deleteByIdsToOr(@Param("ids") Long[] ids); /** * 根据多条件查询Car * @param brand 品牌名称 * @param guidePrice 品牌价格 * @param carType 品牌类型 * @return 查询集合列表 */ List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") BigDecimal guidePrice, @Param("carType") String carType); /** * 插入汽车 * @param car 汽车记录 * @return =1表示插入成功，否则失败 */ int insertSelective(Car car); /** * 根据id更新car * @param car 汽车实体 * @return =1表示更新成功，否则失败 */ int updateByPrimaryKey(Car car); /** * 批量添加，使用foreach标签 * @param cars 汽车集合 * @return 插入条数 */ int insertBatchByForeach(@Param("cars") List<Car> cars); }
7、Mapper-SQL文件：resource/CarMapper.xml
<?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.zhixi.mapper.CarMapper"> <resultMap id="BaseResultMap" type="com.zhixi.pojo.Car"> <id property="id" column="id" jdbcType="BIGINT"/> <result property="carNum" column="car_num" jdbcType="VARCHAR"/> <result property="brand" column="brand" jdbcType="VARCHAR"/> <result property="guidePrice" column="guide_price" jdbcType="DECIMAL"/> <result property="produceTime" column="produce_time" jdbcType="CHAR"/> <result property="carType" column="car_type" jdbcType="VARCHAR"/> </resultMap> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from t_car where id = #{id,jdbcType=BIGINT} </select> <select id="selectAllByMap" resultType="java.util.Map"> select <include refid="Base_Column_List"/> from t_car </select> <sql id="Base_Column_List"> id,car_num,brand, guide_price,produce_time,car_type </sql> <select id="selectAllCar" resultType="com.zhixi.pojo.Car"> select <include refid="Base_Column_List"/> from t_car; </select> <select id="selectCarByChoose" resultType="com.zhixi.pojo.Car"> select <include refid="Base_Column_List"/> from t_car <where> <choose> <when test="carNum!=null and carNum!=''"> car_num = #{carNum,jdbcType=VARCHAR} </when> <when test="brand!=null and brand!=''"> brand = #{brand,jdbcType=VARCHAR} </when> <otherwise> car_type = #{carType,jdbcType=VARCHAR} </otherwise> </choose> </where> </select> <select id="selectByMultiCondition" resultType="com.zhixi.pojo.Car"> select <include refid="Base_Column_List"/> from t_car <where> <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> </where> </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from t_car where id = #{id,jdbcType=BIGINT} </delete> <delete id="deleteByIdsToIn"> delete from t_car where id in <foreach collection="ids" item="id" open="(" close=")" separator=","> #{id} </foreach> </delete> <delete id="deleteByIdsToOr"> delete from t_car where <foreach collection="ids" item="id" separator="or"> id = #{id} </foreach> </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.zhixi.pojo.Car" useGeneratedKeys="true"> insert into t_car ( id, car_num, brand , guide_price, produce_time, car_type) values ( #{id,jdbcType=BIGINT}, #{carNum,jdbcType=VARCHAR}, #{brand,jdbcType=VARCHAR} , #{guidePrice,jdbcType=DECIMAL}, #{produceTime,jdbcType=CHAR}, #{carType,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.zhixi.pojo.Car" useGeneratedKeys="true"> insert into t_car <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null">id,</if> <if test="carNum != null">car_num,</if> <if test="brand != null">brand,</if> <if test="guidePrice != null">guide_price,</if> <if test="produceTime != null">produce_time,</if> <if test="carType != null">car_type,</if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null">#{id,jdbcType=BIGINT},</if> <if test="carNum != null">#{carNum,jdbcType=VARCHAR},</if> <if test="brand != null">#{brand,jdbcType=VARCHAR},</if> <if test="guidePrice != null">#{guidePrice,jdbcType=DECIMAL},</if> <if test="produceTime != null">#{produceTime,jdbcType=CHAR},</if> <if test="carType != null">#{carType,jdbcType=VARCHAR},</if> </trim> </insert> <insert id="insertBatchByForeach"> insert into t_car values <foreach collection="cars" item="car" separator=","> (null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType}) </foreach> </insert> <update id="updateByPrimaryKey" parameterType="com.zhixi.pojo.Car"> update t_car <set> <if test="carNum != null"> car_num = #{carNum,jdbcType=VARCHAR}, </if> <if test="brand != null"> brand = #{brand,jdbcType=VARCHAR}, </if> <if test="guidePrice != null"> guide_price = #{guidePrice,jdbcType=DECIMAL}, </if> <if test="produceTime != null"> produce_time = #{produceTime,jdbcType=CHAR}, </if> <if test="carType != null"> car_type = #{carType,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT}; </update> </mapper>
标签的使用：
resultMap标签
查询结果的列名和java对象的属性名对应不上怎么办？
- 第一种方式：as 给列起别名
- 第二种方式：使用resultMap进行结果映射
- 第三种方式：是否开启驼峰命名自动映射（配置settings）
为什么会出现resultMap标签？
/** * 查询所有的记录 * * @return 汽车集合 */ List<Car> selectAllCar();
因为我们在查询数据的时候，可能遇到实体类的属性名跟数据库的列名不一致的情况，比如car_num与实体类属性carNum对不上的情况，这时候查询到的数据为null。
第一种解决办法：as起别名
<select id="selectAllCar" resultType="com.zhixi.pojo.Car">
select id,
car_num as carNum,
brand,
guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
from t_car;
</select>
第二种(推荐)：resultMap
就是将数据库属性与实体类属性做一一对应
<resultMap id="BaseResultMap" type="com.zhixi.pojo.Car">
<id property="id" column="id" jdbcType="BIGINT"/>
<result property="carNum" column="car_num" jdbcType="VARCHAR"/>
<result property="brand" column="brand" jdbcType="VARCHAR"/>
<result property="guidePrice" column="guide_price" jdbcType="DECIMAL"/>
<result property="produceTime" column="produce_time" jdbcType="CHAR"/>
<result property="carType" column="car_type" jdbcType="VARCHAR"/>
</resultMap>
然后将select标签中的resultType换成resultMap="BaseResultMap"即可
第三种：是否开启驼峰自动映射
使用这种方式的前提是：属性名遵循Java的命名规范，数据库表的列名遵循SQL的命名规范。
Java命名规范：首字母小写，后面每个单词首字母大写，遵循驼峰命名方式。
SQL命名规范：全部小写，单词之间采用下划线分割。
比如以下的对应关系：
如何启用该功能，在mybatis-config.xml文件中进行配置：
<!--放在properties标签后面--> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>
sql标签与include标签
sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句当中
作用：代码复用。易维护。
<sql id="Base_Column_List">
id,car_num,brand,
guide_price,produce_time,car_type
</sql>
<select id="selectAllCar" resultType="com.zhixi.pojo.Car">
select <include refid="Base_Column_List"/>
from t_car;
</select>
where if标签
提供了可选的查找文本功能。
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。
而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
需求：多条件查询。
可能的条件包括：品牌（brand）、指导价格（guide_price）、汽车类型（car_type）
/**
* 按照条件进行查询，最多只能够有一个条件成立
* @param carNum 品牌编号
* @param brand 品牌名称
* @param carType 车辆类型
* @return 查询到的数据结果
*/
List<Car> selectCarByChoose(@Param("carNum") String carNum, @Param("brand") String brand, @Param("carType") String carType);
/**
<select id="selectByMultiCondition" resultType="com.zhixi.pojo.Car">
select
<include refid="Base_Column_List"/>
from t_car
<where>
<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>
</where>
</select>
测试：
@Test
public void testSelectByMultiCondition() {
SqlSession sqlSession = MybatisUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> cars = mapper.selectByMultiCondition("宝马", new BigDecimal("25"), "燃油车");
cars.forEach(System.out::println);
}
trim标签
trim标签的属性：
- prefix：在trim标签中的语句前添加内容
- suffix：在trim标签中的语句后添加内容
- prefixOverrides：前缀覆盖掉（去掉）
- suffixOverrides：后缀覆盖掉（去掉）
/** * 插入汽车 * @param car 汽车记录 * @return =1表示插入成功，否则失败 */ int insertSelective(Car car);
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.zhixi.pojo.Car" useGeneratedKeys="true"> insert into t_car <trim prefix="(" suffix=")" suffixOverrides=","> <if test="id != null">id,</if> <if test="carNum != null">car_num,</if> <if test="brand != null">brand,</if> <if test="guidePrice != null">guide_price,</if> <if test="produceTime != null">produce_time,</if> <if test="carType != null">car_type,</if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null">#{id,jdbcType=BIGINT},</if> <if test="carNum != null">#{carNum,jdbcType=VARCHAR},</if> <if test="brand != null">#{brand,jdbcType=VARCHAR},</if> <if test="guidePrice != null">#{guidePrice,jdbcType=DECIMAL},</if> <if test="produceTime != null">#{produceTime,jdbcType=CHAR},</if> <if test="carType != null">#{carType,jdbcType=VARCHAR},</if> </trim> </insert>
测试：
@Test
public void testInsertSelective() {
SqlSession sqlSession = MybatisUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = Car.builder().
carNum("106")
.brand("奥迪A8L")
.guidePrice(new BigDecimal("85.00")).
produceTime("2018-08-01").carType("燃油车").
build();
int i = mapper.insertSelective(car);
System.out.println(i);
sqlSession.commit();
sqlSession.close();
}
set标签
主要使用在update语句当中，用来生成set关键字，同时去掉最后多余的“,”
比如我们只更新提交的不为空的字段，如果提交的数据是空或者""，那么这个字段我们将不更新。
/** * 根据id更新car * @param car 汽车实体 * @return =1表示更新成功，否则失败 */ int updateByPrimaryKey(Car car);
<update id="updateByPrimaryKey" parameterType="com.zhixi.pojo.Car">
update t_car
<set>
<if test="carNum != null">
car_num = #{carNum,jdbcType=VARCHAR},
</if>
<if test="brand != null">
brand = #{brand,jdbcType=VARCHAR},
</if>
<if test="guidePrice != null">
guide_price = #{guidePrice,jdbcType=DECIMAL},
</if>
<if test="produceTime != null">
produce_time = #{produceTime,jdbcType=CHAR},
</if>
<if test="carType != null">
car_type = #{carType,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=BIGINT};
</update>
测试：
@Test
public void testUpdateByPrimaryKey() {
SqlSession sqlSession = MybatisUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = Car.builder()
.id(9L)
.brand("奥迪A4L")
.guidePrice(new BigDecimal("35.00")).
build();
/*
执行的SQL：
==> Preparing: update t_car SET brand = ?, guide_price = ? where id = ?;
==> Parameters: 奥迪A4L(String), 35.00(BigDecimal), 9(Long)
<== Updates: 1
*/
int i = mapper.updateByPrimaryKey(car);
System.out.println(i);
sqlSession.commit();
sqlSession.close();
}
choose、when、otherwise
有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
匹配顺序是从第一个进行选择，第一个成立就执行第一个中的匹配，
如果第一个第二个数据都匹配的话，还是按第一个中的执行。
语法格式：
<choose> <when></when> <when></when> <when></when> <otherwise></otherwise> </choose>
需求：
先根据品牌查询，如果没有提供品牌，再根据指导价格查询，如果没有提供指导价格，就根据生产日期查询。
/**
* 按照条件进行查询，最多只能够有一个条件成立
*
* @param carNum 品牌编号
* @param brand 品牌名称
* @param carType 车辆类型
* @return 查询到的数据结果
*/
List<Car> selectCarByChoose(@Param("carNum") String carNum, @Param("brand") String brand, @Param("carType") String carType);
<select id="selectCarByChoose" resultType="com.zhixi.pojo.Car">
select
<include refid="Base_Column_List"/>
from t_car
<where>
<choose>
<when test="carNum!=null and carNum!=''">
car_num = #{carNum,jdbcType=VARCHAR}
</when>
<when test="brand!=null and brand!=''">
brand = #{brand,jdbcType=VARCHAR}
</when>
<otherwise>
car_type = #{carType,jdbcType=VARCHAR}
</otherwise>
</choose>
</where>
</select>
测试：
@Test
public void testSelectCarByChoose() {
SqlSession sqlSession = MybatisUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
/*
==> Preparing: select id,car_num,brand, guide_price,produce_time,car_type from t_car WHERE car_num = ?
==> Parameters: 103(String)
<== Columns: id, car_num, brand, guide_price, produce_time, car_type
<== Row: 5, 103, 丰田, 25.80, 2022-01-01, 燃油车
<== Total: 1
*/
List<Car> cars = mapper.selectCarByChoose("103", "丰田", null);
cars.forEach(System.out::println);
}
foreach标签
场景
批量删除：
delete from t_car where id in(1,2,3); delete from t_car where id = 1 or id = 2 or id = 3;
批量添加：
insert into t_car values (null,'1001','凯美瑞',35.0,'2010-10-11','燃油车'), (null,'1002','比亚迪唐',31.0,'2020-11-11','新能源'), (null,'1003','比亚迪宋',32.0,'2020-10-11','新能源')
批量删除：使用in
/**
* 批量删除
*
* @param ids id数组
* @return 删除的记录条数
*/
int deleteByIdsToIn(@Param("ids") Long[] ids);
<delete id="deleteByIdsToIn">
delete
from t_car
where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
测试：
@Test
public void testDeleteByIds() {
SqlSession sqlSession = MybatisUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids = {1L, 2L};
/*
==> Preparing: delete from t_car where id in ( ? , ? )
==> Parameters: 1(Long), 2(Long)
<== Updates: 2
*/
int delNum = mapper.deleteByIdsToIn(ids);
System.out.println(delNum);
sqlSession.commit();
sqlSession.close();
}
批量删除：使用or
/**
* 批量删除-or
*
* @param ids id数组
* @return 删除的记录条数
*/
int deleteByIdsToOr(@Param("ids") Long[] ids);
<delete id="deleteByIdsToOr">
delete
from t_car
where
<foreach collection="ids" item="id" separator="or">
id = #{id}
</foreach>
</delete>
测试：
@Test
public void testDeleteByIdsToOr() {
SqlSession sqlSession = MybatisUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids = {5L, 6L};
/*
==> Preparing: delete from t_car where id = ? or id = ?
==> Parameters: 5(Long), 6(Long)
<== Updates: 2
*/
int delNum = mapper.deleteByIdsToOr(ids);
System.out.println(delNum);
sqlSession.commit();
sqlSession.close();
}
批量添加：
/**
* 批量添加，使用foreach标签
* @param cars 汽车集合
* @return 插入条数
*/
int insertBatchByForeach(@Param("cars") List<Car> cars);
<insert id="insertBatchByForeach">
insert into t_car values
<foreach collection="cars" item="car" separator=",">
(null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
</foreach>
</insert>
测试：
@Test
public void testInsertBatchByForeach(){
CarMapper mapper = MybatisUtil.openSession().getMapper(CarMapper.class);
Car car1 = new Car(null, "2001", "兰博基尼", new BigDecimal("100.0"), "1998-10-11", "燃油车");
Car car2 = new Car(null, "2001", "兰博基尼", new BigDecimal("100.0"), "1998-10-11", "燃油车");
Car car3 = new Car(null, "2001", "兰博基尼", new BigDecimal("100.0"), "1998-10-11", "燃油车");
List<Car> cars = Arrays.asList(car1, car2, car3);
/*
==> Preparing: insert into t_car values (null,?,?,?,?,?) , (null,?,?,?,?,?) , (null,?,?,?,?,?)
==> Parameters: 2001(String), 兰博基尼(String), 100.0(BigDecimal), 1998-10-11(String), 燃油车(String), 2001(String), 兰博基尼(String), 100.0(BigDecimal), 1998-10-11(String), 燃油车(String), 2001(String), 兰博基尼(String), 100.0(BigDecimal), 1998-10-11(String), 燃油车(String)
<== Updates: 3
*/
int count = mapper.insertBatchByForeach(cars);
System.out.println("插入了几条记录" + count);
MybatisUtil.openSession().commit();
MybatisUtil.openSession().close();
}
