MyBatis-参数专题和查询专题

一、参数专题

1. 单个简单类型参数

单个参数的情况，#{}的参数没有要求，可以随意写

接口

/**
 * 根据id查询
 * @param id
 * @return
 */
Car selectById(Long id);

/**
 * 根据汽车类型查询
 * @param CarType
 * @return
 */
List<Car> selectByCarType(String CarType);

测试类

@Test
public void selectById() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    Car car = carMapper.selectById(35L);
    sqlSession.close();
    System.out.println(car);
}

@Test
public void selectByCarType() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars= carMapper.selectByCarType("氢能源");
    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

CarMapper.xml

<select id="selectByCarType" resultType="car">
    select *
    from t_car
    where car_type = #{carType}
</select>
<select id="selectById" resultType="car">
    select *
    from t_car
    where id = #{id}
</select>

2. Map参数

Map是以key-value成对出现，我们可以通过key获取到对应的value

所以在#{}中，我们只需要在写key

#{map集合的key}

接口

/**
 * 根据Map查询
 * @param paramMap
 * @return
 */
List<Car> selectByParamMap(Map<String,String> map);

测试类

@Test
public void selectByParamMap() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    Map<String,String> map = new HashMap<>();
    map.put("brand", "丰田");
    map.put("CarType", "氢能源");
    List<Car> cars= carMapper.selectByParamMap(map);
    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

CarMapper.xml

    <select id="selectByParamMap" resultType="car">
        select *
        from t_car
        where brand = #{brand} and car_type = #{CarType}
    </select>

3. 实体类参数

参数是实体类，#{}的参数就必须是实体类的属性名，不然编译器无法自动匹配

接口

/**
 * 通过实体类插入数据
 * @param car
 * @return
 */
int insert(Car car);

测试类

@Test
public void insert() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    Car car = new Car(null, "56665", "大众", 20000.00, "2022-12-12", "汽油车");
    int count = carMapper.insert(car);
    sqlSession.commit();
    sqlSession.close();
    if (count == 1) {
        System.out.println("插入成功");
    } else {
        System.out.println("插入失败");
    }
}

CarMapper.xml

<insert id="insert" keyProperty="id" useGeneratedKeys="true">
    insert into t_car
        (id, car_num, brand, guide_price, produce_time, car_type)
    values (#{id}, #{carNum}, #{brand}, #{guidePrice}, #{produceTime}, #{carType});
</insert>

4. 多参数

如果是一个参数，无论#{}写什么参数名，都能直接匹配

如果是多个参数，编译器就无法识别了，所以我们需要注明参数名

@Param注解：编译器通过@Param()中的参数名，自动匹配到CarMapper.xml中的对应的位置

接口

/**
 * 根据汽车品牌、汽车类型查询
 * @param Brand
 * @param CarType
 * @return
 */
List<Car> selectByBrandAndCarType(@Param("brand") String Brand,@Param("CarType") String CarType);

测试类

@Test
public void selectByBrandAndCarType() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars= carMapper.selectByBrandAndCarType("丰田","氢能源");
    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

CarMapper.xml

<select id="selectByBrandAndCarType" resultType="car">
    select *
    from t_car
    where brand = #{brand} and car_type = #{CarType}
</select>

二、查询专题

【后面的所有例子均使用自动映射，如果不满足条件的使用as和使用resultMap进行结果映射，具体操作看第三部分的查询】

1.返回Car

接口

/**
 * 根据id查询
 * @param id
 * @return
 */
Car selectById(Long id);

测试类

@Test
public void selectById() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    Car car = carMapper.selectById(36L);
    sqlSession.close();
    System.out.println(car);
}

CarMapper.xml

<select id="selectById" resultType="car">
    select *
    from t_car
    where id = #{id}
</select>

运行结果

2.返回Car集合

接口

List<Car> selectAll();

测试类

@Test
public void selectAll() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = carMapper.selectAll();
    cars.forEach((car)->{
        System.out.println(car);
    });
    sqlSession.close();
}

CarMapper.xml

<select id="selectAll" resultType="car">
    select *
    from t_car
</select>

运行结果

3.返回Map

当返回的数据，没有合适的实体类对应的话（多表联合查询之类的），可以采用Map集合接收。字段名做key，字段值做value。

查询如果可以保证只有一条数据，则返回一个Map 即可。

接口

Map<String, Object> selectByIdRetMap(Long id);

测试类

@Test
public void testSelectByIdRetMap(){
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    CarMapper carMapper = sqlSession.getMapper(CarMapper.class);
    Map<String,Object> car = carMapper.selectByIdRetMap(36L);
    System.out.println(car);
    sqlSession.close();
}

CarMapper.xml

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

运行结果

注意这儿是resultType=“map”

4.返回Map集合

查询结果条数大于等于1条数据，则可以返回一个存储Map集合的List集合。List

接口

List<Map<String,Object>> selectAllRetListMap();

测试类

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

CarMapper.xml

<select id="selectAllRetListMap" resultType="map">
    select *
    from t_car
</select>

运行结果

5.返回List<Long,Map>

为了方便我们后期取数据，我们可以选择拿Car的id做key

接口

@MapKey("id")
Map<Long,Map<String,Object>> selectAllRetMap();

测试类

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

CarMapper.xml

<select id="selectAllRetMap" resultType="map">
    select * from t_car
</select>

运行结果

使用@MapKey()注明使用什么作为对应的key

6.返回总记录条数

接口

Long selectTotal();

测试类

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

CarMapper.xml

<select id="selectTotal" resultType="long">
    select count(*) from t_car
</select>

运行结果