Mybatis

1
//id 是这个映射规则的名字后面查询语句可以通过它引用resultMap="CarResultMap"
2
//type 是要封装成哪个 Java 类型
3
<resultMap id="CarResultMap" type="com.example.car.domain.Car">
4
//<id> 专门用来标识主键字段,property 写 Java 属性名
5
//column 写 SQL 查询结果里的列名
6
//column 写的是“查询结果列名”，不一定是表字段原名或者as别名
7
//两个id字段名冲突时必须用别名
8
    <id property="id" column="id"/>
9
    <result property="carNum" column="car_num"/>
10

11
//Car 对象里有一个属性：private Brand brand;
12
    <association property="brand" javaType="com.example.car.domain.Brand">
13
        <id property="id" column="brand_id"/>
14
        <result property="name" column="brand_name"/>
15
    </association>
16
</resultMap>

1
<select id="selectById" resultMap="CarResultMap">
2
    select c.id,
3
           c.car_num,
4
           c.guide_price,
5
           c.produce_date,
6
           c.car_type,
7
           b.id as brand_id,
8
           b.name as brand_name
9
    from t_car c
10
    left join t_brand b on c.brand_id = b.id
11
    where c.id = #{id}
12
</select>
13

14
//c.car_num      -> car.carNum
15
//b.id           -> car.brand.id
16
//b.name         -> car.brand.name

1
<select id="selectByIdWithImages" resultMap="CarWithImagesResultMap">
2
    select c.id,
3
           c.car_num,
4
           i.id as image_id,
5
           i.url as image_url
6
    from t_car c
7
    left join t_car_image i on c.id = i.car_id
8
    where c.id = #{id}
9
</select>
10

11
//Car {
12
    id = 1,
13
    carNum = "C1001",
14
    images = [
15
        CarImage { id = 10, url = "a.jpg" },
16
        CarImage { id = 11, url = "b.jpg" },
17
        CarImage { id = 12, url = "c.jpg" }
18
    ]
19
}

1
<select id="search" resultMap="carResultMap">
2
    select id, car_num, brand, guide_price,
3
           produce_date, car_type, version
4
    from t_car
5
    <where>
6
        <if test="brand != null and brand != ''">
7
            brand like concat('%', #{brand}, '%')
8
        </if>
9
        <if test="minPrice != null">
10
            and guide_price &gt;= #{minPrice}
11
        </if>
12
        <if test="maxPrice != null">
13
            and guide_price &lt;= #{maxPrice}
14
        </if>
15
        <if test="carType != null and carType != ''">
16
            and car_type = #{carType}
17
        </if>
18
        <if test="producedAfter != null">
19
            and produce_date &gt;= #{producedAfter}
20
        </if>
21
    </where>
22
    order by id desc
23
</select>

1
>=  >=
2
&lt;=  <=
3
<delete id="deleteByCondition">
4
    delete from t_car
5
    <where>
6
        <if test="brand != null">brand = #{brand}</if>
7
    </where>
8
</delete>
9
//若未传入brand就会直接删表
10
//Service 层必须做条件校验
11
<delete id="deleteByBrand">
12
    delete from t_car
13
    where brand = #{brand}
14
</delete>
15
public int deleteByBrand(String brand) {
16
    if (brand == null || brand.isBlank()) {
17
        throw new IllegalArgumentException("brand 不能为空，禁止无条件删除");
18
    }
19
    return carMapper.deleteByBrand(brand);
20
}

1
<select id="searchByBestCondition" resultMap="carResultMap">
2
    select id, car_num, brand, guide_price,
3
           produce_date, car_type, version
4
    from t_car
5
    <where>
6
        <choose>
7
            <when test="id != null">
8
                id = #{id}
9
            </when>
10
            <when test="carNum != null and carNum != ''">
11
                car_num = #{carNum}
12
            </when>
13
            <when test="brand != null and brand != ''">
14
                brand like concat('%', #{brand}, '%')
15
            </when>
16
            <otherwise>
17
                1 = 0
18
            </otherwise>
19
        </choose>
20
    </where>
21
    order by id desc
22
</select>
23
//1 = 0表示没有任何有效条件时，不返回任何数据

1
int batchInsert(@Param("cars") List<Car> cars);
2

3
<insert id="batchInsert">
4
    insert into t_car (
5
        car_num, brand, guide_price, produce_date, car_type
6
    ) values
7
  //collection="cars" 对应 Mapper 参数名
8
  //item="car" 表示循环里的每一个元素叫 car
9
    <foreach collection="cars" item="car" separator=",">
10
        (
11
            #{car.carNum}, #{car.brand}, #{car.guidePrice},
12
            #{car.produceDate}, #{car.carType}
13
        )
14
    </foreach>
15
</insert>

1
//与in关键字结合
2
<select id="selectByIds" resultMap="carResultMap">
3
    select id, car_num, brand, guide_price,
4
           produce_date, car_type, version
5
    from t_car
6
    where id in
7
    <foreach collection="ids"
8
             item="id"
9
             open="("
10
             separator=","
11
             close=")">
12
        #{id}
13
    </foreach>
14
</select>
15

16
//Service层
17
public List<Car> findByIds(List<Long> ids) {
18
    if (ids == null || ids.isEmpty()) {
19
        return List.of();
20
    }
21
    return carMapper.selectByIds(ids);
22
}