[SSM]MyBatis查询语句与动态SQL

十、MyBatis查询语句专题

10.1返回Car

10.2返回List

11.5choose when otherwise

十、MyBatis查询语句专题

10.1返回Car

当查询的结果有对应的实体类，并且查询结果只有一条时：

查询结果是一条的话可以使用List集合。

10.2返回List<Car>

当查询的记录条数是多条的时候，必须使用集合接收，如果使用单个实体类接收会出现异常。

10.3返回Map

当返回的数据没有合适的实体类对应的话，可以采用Map集合接收。字段名叫做key，字段值叫做value。查询如果可以保证只有一条数据，则返回一个Map集合即可。

CarMapper.selectByIdRetMap

/**
 * 通过id查询⼀条记录，返回Map集合
 * @param id
 * @return
 */
Map<String, Object> selectByIdRetMap(Long id);

CarMapper.xml

<select id="selectByIdRetMap" resultType="map">
 select * from t_car where id = #{id}
</select>

resultMap="map"，这是因为mubatis内置了很多别名。
使用map不需要 select as;

CarMapperTest.testSelectByIdRetMap

@Test
public void testSelectByIdRetMap(){
 CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.cla
ss);
 Map<String,Object> car = mapper.selectByIdRetMap(35L);
 System.out.println(car);
}

如果返回一个Map集合，可以将Map集合放到List集合中。
如果返回的不是一条记录，是多条记录的话，只采用单个Map集合接收，会出现异常：TooManyResultsException。

10.4返回List<Map>

查询结果条数⼤于等于1条数据，则可以返回⼀个存储Map集合的List集合。List<Map>等同于List<Car>。

CarMapperTest.testSelectAllRetListMap

@Test
public void testSelectAllRetListMap(){
 CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
 List<Map<String,Object>> cars = mapper.selectAllRetListMap();
 System.out.println(cars);
}

10.5返回Map<String,Map>

用Car的id做key，以后取出对应的Map集合时更方便。

CarMapperTest.testSelectAllRetMap

@Test
public void testSelectAllRetMap(){
 CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
 Map<Long,Map<String,Object>> cars = mapper.selectAllRetMap();
 System.out.println(cars);
}

执行结果

{
64={carType=燃油⻋, carNum=133, guidePrice=50.30, produceTime=2020-01-10, id=64, brand=丰⽥霸道},
66={carType=燃油⻋, carNum=133, guidePrice=50.30, produceTime=2020-01-10, id=66, brand=丰⽥霸道},
67={carType=燃油⻋, carNum=133, guidePrice=50.30, produceTime=2020-01-10, id=67, brand=丰⽥霸道},
69={carType=燃油⻋, carNum=133, guidePrice=50.30, produceTime=2020-01-10, id=69, brand=丰⽥霸道},
......
}

10.6resultMap结果映射

查询结果的列名和java对象的属性名对应不上怎么办？
- 第一种方式：as给列起别名
- 第二种方式：使用resultMap进行结果映射
- 第三种方式：是否开启驼峰命名自动映射（配置settings）

使用resultMap进行结果映射

 <!--
        专门定义一个结果映射，在这个结果映射当中指定数据库的字段名和Java类的的属性名的对应关系
        type属性：用来指定POJO类的类名
        id属性：指定resultType的唯一标识，这个id将来要在select标签中使用
    -->
    <resultMap id="carResultMap" type="Car">
        <!--如果有主键，建议这里配置一个id标签，不是必须的，但是配上可以提高mybatis的效率-->
        <!--property后面填写POJO类的属性名，column后面填写数据库表的字段名-->
        <id property="id" column="id"/>
        <result property="carNum" column="car_num" javaType="String" jdbcType="VARCHAR"/>
        <!--如果column和property是一样的，可以省略-->
        <!--<result property="brand" column="brand"/>-->
        <result property="guidePrice" column="guide_price"/>
        <result property="produceTime" column="produce_time"/>
        <result property="carType" column="car_type"/>
    </resultMap>

    <select id="selectAllByResultMap" resultMap="carResultMap">
        select *
        from t_car;
    </select>

是否开启驼峰命名自动映射

mybatis-config.xml

<!--放在properties标签后⾯-->
<!--开启驼峰命名自动映射-->
<settings>
 <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

CarMapper.xml

<select id="selectAllByMapUnderscoreToCamelCase" resultType="Car">
 select * from t_car
</select>

10.7返回总记录条数

需求：查询总记录条数

CarMapper接口

/**
 * 获取总记录条数
 * @return
 */
Long selectTotal();

CarMapper.xml

<!--long是别名，可参考mybatis开发⼿册。-->
<select id="selectTotal" resultType="long">
 select count(*) from t_car
</select>

CarMapperTest.testSelectTota

@Test
public void testSelectTotal(){
 CarMapper carMapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
 Long total = carMapper.selectTotal();
 System.out.println(total);
}

十一、动态SQL

11.1 if标签

需求：多条件查询
可能的条件包括：品牌（brand）、指导价格（guide_price）、汽⻋类型（car_type）

CarMapper接口

List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);

CarMapper.xml

<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>

if标签中test属性是必须的。
if标签中test属性的值是false或者true。
如果test是true，则if标签中的sql语句就会拼接。反之，则不会拼接。
test属性中可以使用的是：
- 当使用了@Param注解，那么test中要出现的是@Param注解指定的参数名。@Param("brand")，那么这里只能使用brand
- 当没有使用@Param注解，那么test中要出现的是：param1 param2 param3 arg0 arg1 arg2....
- 当使用了POJO，那么test中出现的是POJO类的属性名。
在mybatis的动态SQL当中，不能使用&&，只能使用and。

CarMapperTest.testSelectByMultiCondition

  @Test
    public void testSelectByMultiCondition() {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);

        // 假设三个条件都不是空
        List<Car> cars = mapper.selectByMultiCondition("比亚迪", 2.0, "新能源");

        // 假设三个条件都是空
        //List<Car> cars = mapper.selectByMultiCondition("", null, "");

        // 假设后两个条件不为空，第一个条件为空
        //List<Car> cars = mapper.selectByMultiCondition("", 2.0, "新能源");

        // 假设第一个条件不是空，后两个条件是空
        //List<Car> cars = mapper.selectByMultiCondition("比亚迪", null, "");

        cars.forEach(car -> System.out.println(car));
        sqlSession.close();
    }

11.2 where标签

where标签的作用：
- 所有的条件为空时，where标签保证不会生成where子句。
- 自动去除某些条件前面多余的and或or。

CarMapper.xml

  <select id="selectByMultiConditionWithWhere" resultType="car">
        select * from t_car
        <!--where标签专门负责where子句动态生成-->
        <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>

11.3 trim标签

trim标签的属性：
- prefix：在trim标签中的语句前添加内容
- suffix：在trim标签中的语句后添加内容
- prefixOverrides：前缀覆盖掉（去掉）
- suffixOverrides：后缀覆盖掉（去掉）

CarMapper.xml

 <select id="selectByMultiConditionWithTrim" resultType="car">
        select * from t_car
            <!--prefix="where" 是在trim标签所有内容的前面添加where-->
            <!--suffixOverrides="and|or" 把trim标签中内容的后缀and或or去掉-->
        <trim prefix="where" suffixOverrides="and|or">
            <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}
            </if>
        </trim>
    </select>

11.4 set标签

主要使用在update语句当中，用来生成set关键字，同时去掉最后多余的","
比如我们只更新提交的部位空的字段，如果提交的数据是空或者""，那么这个字段我们将不更新。

CarMapper.xml

 <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>

11.5choose when otherwise

这三个标签是在一起使用的：

<choose>
 <when></when>
 <when></when>
 <when></when>
 <otherwise></otherwise>
</choose>

等同于：

if(){
 
}else if(){
 
}else if(){
 
}else if(){
 
}else{
}

只会被一个分支选择！！！！
需求：先根据品牌查询，如果没有提供品牌，再根据指导价格查询，如果没有提供指导价格，就根据⽣产⽇期查询。

CarMapper.xml

<select id="selectWithChoose" 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>
 produce_time >= #{produceTime}
 </otherwise>
 </choose>
 </where>
</select>

当brand、guidePrice、produceTime均为空时

11.6 foreach标签

循环数组或集合，动态生成sql，比如这样的SQL：

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','新能源')

批量删除

CarMapper接口

int deleteBatchByForeach(@Param("ids") Long[] ids);

CarMapper.xml

<delete id="deleteBatchByForeach">
 delete from t_car where id in
 <foreach collection="ids" item="id" separator="," open="(" close=")">
 #{id}
 </foreach>
</delete>
或
    <insert id="deleteByIds2">
        delete from t_car where
        <foreach collection="ids" item="id" separator="or">
            id=#{id}
        </foreach>
    </insert>

指定集合或者数组，必须是@Param("")中的元素
item：代表数组或集合中的元素
separator：循环之间的分割符
open：foreach标签中所有内容的开始
close：foreach标签中所有内容的结束

CarMapperTest.testDeleteBatchByForeach

@Test
public void testDeleteBatchByForeach(){
 CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
 int count = mapper.deleteBatchByForeach(new Long[]{40L, 41L, 42L});
 System.out.println("删除了⼏条记录：" + count);
 SqlSessionUtil.openSession().commit();
}

批量添加

CarMapper接口

int insertBatchByForeach(@Param("cars") List<Car> cars);

CarMapper.xml

<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>

CarMapperTest.testInsertBatchByForeach

  @Test
    public void testInsertBatchByForeach() {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        CarMapper mapper = sqlSession.getMapper(CarMapper.class);
        Car car1 = new Car(null, "1111", "宝马1", 50.00, "2016-08-26", "燃油车");
        Car car2 = new Car(null, "1112", "宝马2", 50.00, "2016-08-26", "燃油车");
        Car car3 = new Car(null, "1113", "宝马3", 50.00, "2016-08-26", "燃油车");
        ArrayList<Car> cars = new ArrayList<>();
        cars.add(car1);
        cars.add(car2);
        cars.add(car3);
        mapper.insertBatch(cars);
        sqlSession.commit();
        sqlSession.close();
    }

11.7sql标签与include标签

sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句当中
作用：代码复用，易维护

测试

<sql id="carCols">id,car_num carNum,brand,guide_price guidePrice,produce_t
ime produceTime,car_type carType</sql>
<select id="selectAllRetMap" resultType="map">
 select <include refid="carCols"/> from t_car
</select>
<select id="selectAllRetListMap" resultType="map">
 select <include refid="carCols"/> carType from t_car
</select>
<select id="selectByIdRetMap" resultType="map">
 select <include refid="carCols"/> from t_car where id = #{id}
</select>