1、多表查询
当我们使用两张或者两张以上的表联合查询时,就会产生四种关系,一对一、一对多、多对一、多对多。前面的文章曾经用xml配置文件的形式讲解了这几种关系。在这里我们将会用注解的方式简述一下如何实现多表联合查询,项目的目录与前几个文章的项目是一样的不一样的地方就是IUserDao和IAccountDao接口多了注解,xml配置文件消失。
2、一对一查询
项目结构参考https://blog.csdn.net/weixin_44588495/article/details/91556447
在xml文件配置中,这些配置信息就是主体部分,每个标签是干嘛的这里不在过多强调,前面的文章说的很详细。
<resultMap id="accountUserMap" type="com.mybatis.domain.Account">
<id property="id" column="aid"></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
<!-- 一对一关系映射,配置封装user的内容-->
<association property="user" column="uid">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
</association>
</resultMap>
<!-- 查询所有-->
<select id="findAll" resultMap="accountUserMap">
select u.*,a.id as aid,a.uid,a.money from account a,users u where u.id = a.uid
</select>
基于注解开发,在IAccountDao接口中。这里等同于上面的xml文件配置。最后一段one的作用是告诉程序这是只查询出一个相关的结果这是一对一。对应的IUserDao接口中注解较简单就不多说了。其中最后一行fetchType属性是配置延迟加载和缓存的。
@Select("select * from account")
@Results(id="accountMap",value = {
@Result(id=true,column = "id",property = "id"),
@Result(column = "uid",property = "uid"),
@Result(column = "money",property = "money"),
@Result(property = "user",column = "uid",one=@One(select="com.mybatis.IUserDao.findById",fetchType = FetchType.EAGER))
})
3、一对多查询
项目结构参考https://blog.csdn.net/weixin_44588495/article/details/91566686
<resultMap id="userAccountMap" type="com.mybatis.domain.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="address" column="address"></result>
<result property="sex" column="sex"></result>
<result property="birthday" column="birthday"></result>
<collection property="accounts" ofType="com.mybatis.domain.Account">
<id property="id" column="aid" ></id>
<result property="uid" column="uid"></result>
<result property="money" column="money"></result>
</collection>
</resultMap>
<!-- 查询所有-->
<select id="findAll" resultMap="userAccountMap">
select * from users u left outer join account a on u.id = a.uid
</select>
基于注解开发,在IUserDao接口中。对应的IAccountDao接口中注解较简单就不多说了。
@Select("select * from user")
@Results(id="accountMap",value = {
@Result(id=true,column = "userId",property = "id"),
@Result(id=true,column = "username",property = "userName"),
@Result(id=true,column = "address",property = "userAddress"),
@Result(id=true,column = "birthday",property = "userBirthday"),
@Result(id=true,column = "sex",property = "userSex"),
@Result(property = "accounts",column = "id",
many=@Many(select="com.mybatis.IAccountDao.findAccountByUid", = FetchType.LAZY))
})