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

目录

十、MyBatis查询语句专题

10.1返回Car

10.2返回List

10.3返回Map

10.4返回List

10.5返回Map,map>

10.6resultMap结果映射

使用resultMap进行结果映射

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

10.7返回总记录条数

十一、动态SQL

11.1 if标签

11.2 where标签

11.3 trim标签

11.4 set标签

11.5choose when otherwise

11.6 foreach标签

批量删除

批量添加

11.7sql标签与include标签


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