mybatis中@results注解使用

目录

目录

用法一

用法二

用法三

@One和@Many使用

传递一个参数

传递map类型 

传递多个参数

a方法中传入一个带查询参数x,但是a方法的查询结果中不包含参数x,而子查询里也需要参数x,如何在子查询b中带入参数x

---

用法一

• 当数据库字段名与实体类对应的属性名不一致时，可以使用@Results映射来将其对应起来。column为数据库字段名，porperty为实体类属性名，jdbcType为数据库字段数据类型，id为是否为主键

@Results(id="groupWithUsers",
         value = { 
            @Result(property = "groupId", column = "group_id", id = true),
            @Result(property = "name", column = "name"), 
            @Result(property = "accountId", column = "account_id"),
            @Result(property = "deleteFlag", column = "delete_Flag"),
            @Result(property = "parentId", column = "parent_Id"), 
            @Result(property = "userList", javaType=List.class, many =@Many(select="selectUsersByGroupId"), column = "group_id")})
//查询
@Select({"select * from group where account_id=#{accountId} and delete_flag=0"})
List<Group> selectGroupWithUsers(@Param("accountId") String accountId);
 
 
@Select({"select u.* from user u",
		"inner join user_group ug on u.user_id = ug.user_id",
		"where ug.group_id=#{groupId} and u.delete_flag=0"
		})
List<User> selectUsersByGroupId(@Param("groupId") String groupId);

用法二

• 当@results这段代码需要在多个方法中用到时,为了提高代码复用性,可以为@results指定id,然后使用@resultMap注解来复用这段代码

@Select("select id, name, class_id from student")
@Results(id="studentMap",value={
    @Result(column=“id”, property=“id”, jdbcType=JdbcType.INTEGER, id=true),
    @Result(column=“name”, property=“name”, jdbcType=JdbcType.VARCHAR),
    @Result(column=“class_id”, property=“classId”, jdbcType=JdbcType.INTEGER)
})
List selectAll();
        
@Select("select id, name, class_id from student”
       "  where id = #{id}")
@resultMap("studentMap")
student getStudent (@param("id") long id)

用法三

• @Result中one和many用于关联查询。比如上面列子中提到的Group类中包含属性List<User> userList，这时集合类型数据，与Group是一对多的关系，需要用many配置，如果Group中需要对象类型数据User user，那么就需要使用one进行配置

@One和@Many使用

• @many表示一对多:Group类中包含属性userList类型为List<User>,查询User的方法查询出多个

  • @Many中常用的属性只有selet，用于指定关联查询的方法.

传递一个参数

• 但是存在many属性的@Result注解的使用就有些变化：如下面的这段代码中，column属性的值为"group_id"，是group表中的列，将父查询中group_id列每个值传递给子查询selectUsersByGroupId进行一对多查询

@Select("SELECT * " +
        "  FROM student " +
        " WHERE id =  #{id} ")
@Results(id=“studentMap”, value={
@Result(column=“id”, property=“id”,id=true),
@Result(column=“id”, property=“studentMate”, //将查询到结果映射到java属性studentMate
one=@One(select=“com.example.DemoDao.selectMetaById”))
})
//查询出自己信息的同时查询出同桌的信息
Student selectInfoAndMeta(@param("id") long id);
​

传递map类型 

•  如果select属性指定的方法需要的参数是一个对象，要如何传递参数？比如select属性绑定的是如下方法(需要的参数是一个Map对象)或者需要的参数有多个如下：
  • 这时column属性的值要设置下面这个样子：相当于我们在map中传入了两个值map.put("groupId",value) ，map.put("userName",value)，而column中，“groupId=group_id”，相当于将库中的列group_id映射到groupId中，user_name也一样。

column = "{groupId=group_id, userName=user_name}"

public List<User> selectUsers(Map<String,Object> map);


@Results(id="groupWithUsers",
         value = { 
            @Result(property = "groupId", column = "group_id", id = true),
            @Result(property = "name", column = "name"), 
            @Result(property = "accountId", column = "account_id"),
            @Result(property = "deleteFlag", column = "delete_Flag"),
            @Result(property = "parentId", column = "parent_Id"), 
            @Result(property = "userList", javaType=List.class, many 
//如果只传了groupId=group_id,则子查询的两个参数都是这个值,当然也可以直接传group_id和user_name
=@Many(select="selectUsersByGroupId"), column = "{groupId=group_id ,userName=user_name}")})

@Select({"select * from group where account_id=#{accountId} and delete_flag=0"})
List<Group> selectGroupWithUsers(@Param("accountId") String accountId);
 
 
@Select({"select u.* from user u",
		"inner join user_group ug on u.user_id = ug.user_id",
		"where ug.group_id=#{groupId} and u.delete_flag=0"
		})
List<User> selectUsersByGroupId(@Param("groupId") String groupId, @Param("groupId") String username);

传递多个参数

• 表示把group_id列和user_name列取出,group_id列值使用groupId,user_name列使用userName表示（类似别名，对应子查询参数,然后以这两个参数进行子查询

column = "{groupId=group_id, userName=user_name}"

public List<User> selectUsers(Map<String,Object> map);


@Results(id="groupWithUsers",
         value = { 
            @Result(property = "groupId", column = "group_id", id = true),
            @Result(property = "name", column = "name"), 
            @Result(property = "accountId", column = "account_id"),
            @Result(property = "deleteFlag", column = "delete_Flag"),
            @Result(property = "parentId", column = "parent_Id"), 
            @Result(property = "userList", javaType=List.class, many 
//如果只传了groupId=group_id,则子查询的两个参数都是这个值,当然也可以直接传group_id和user_name
=@Many(select="selectUsersByGroupId"), column = "{groupId=group_id ,userName=user_name}")})

@Select({"select * from group where account_id=#{accountId} and delete_flag=0"})
List<Group> selectGroupWithUsers(@Param("accountId") String accountId);
 
 
@Select({"select u.* from user u",
		"inner join user_group ug on u.user_id = ug.user_id",
		"where ug.group_id=#{groupId} and u.delete_flag=0"
		})
List<User> selectUsersByGroupId(Map(String, Object) map);

a方法中传入一个带查询参数x,但是a方法的查询结果中不包含参数x,而子查询里也需要参数x,如何在子查询b中带入参数x

   /**
     * 按顾客id查询其购物车（商家->商品 一对多查询）
     * @param consumerId 顾客id
     * @return 购物车商品列表
     */
    @Select("select distinct saler.id,saler.shopname,#{consumerId} as consumerId from shoppingcart \n" +
            "join saler on saler.id = shoppingcart.salerId")
    @Results(
            @Result(
                    property = "goods",
                    column = "{salerId = id,consumerId = consumerId}",
                    many = @Many(select = "cn.datacharm.springbootvuecli.dao.CartMapper.findGoodsBySalerId")
            )
    )
    //主查询参数为consumerId,但是查询结果不包含consumerId,所以需要在select中添加一个别名,将其传递给子查询
    List<Shop> findCartById(Integer consumerId);
 
    @Select("select \n" +
            "sid,consumerId,productName,price,photo,\n" +
            "shoppingcart.salerId,\n" +
            "shoppingcart.productId,\n" +
            "shoppingcart.amount\n" +
            "from shoppingcart\n" +
            "join saler_inventory on shoppingcart.salerId = saler_inventory.salerId\n" +
            "and shoppingcart.productId = saler_inventory.productId\n" +
            "where shoppingcart.salerId = #{salerId}\n"+
            "and consumerId = #{consumerId}" )
    List<Goods> findGoodsBySalerId(Integer salerId,Integer consumerId);