Mybatis系列之——Mybatis的多表操作和注解开发

1.12、Mybatis的多表操作

一对一查询模型：

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户。

一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

在这里插入图片描述

创建Order和User实体

在这里插入图片描述

public interface OrderMapper {
    
	List<Order> findAll();
}

OrderMapper.xml配置方法如下：

方法一：

<mapper namespace="com.spongebob.mapper.OrderMapper"> 
    <resultMap id="orderMap" type="com.spongebob.pojo.Order"> 
        <result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result> 
        <result column="password" property="user.password"></result> 
        <result column="birthday" property="user.birthday"></result>
    </resultMap> 
    <select id="findAll" resultMap="orderMap">
		select * from orders o,user u where o.uid=u.id
	</select>
</mapper>

方法二：

<resultMap id="orderMap" type="com.spongebob.pojo.Order"> 
    <result property="id" column="id"></result> 
    <result property="ordertime" column="ordertime"></result> 
    <result property="total" column="total"></result> 
    <association property="user" javaType="com.spongebob.pojo.User"> 
        <result column="uid" property="id"></result> 
        <result column="username" property="username"></result> 
        <result column="password" property="password"></result> 
        <result column="birthday" property="birthday"></result>
</association>
</resultMap>

一对多查询模型

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

在这里插入图片描述

public interface UserMapper {
    
    List<User> findAll();
}

OrderMapper.xml配置方法如下：

<mapper namespace="com.spongebob.pojo.UserMapper"> 
    <resultMap id="userMap" type="com.spongebob.pojo.User"> 
        <result column="id" property="id"></result> 
        <result column="username" property="username"></result> 
        <result column="password" property="password"></result> 
        <result column="birthday" property="birthday"></result> 
        <collection property="orderList" ofType="com.spongebob.pojo.Order"> 			<result column="oid" property="id"></result> 
            <result column="ordertime" property="ordertime"></result> 
            <result column="total" property="total"></result>
		</collection>
	</resultMap> 
    <select id="findAll" resultMap="userMap">
		select *,o.id oid from user u left join orders o on u.id=o.uid
	</select>
</mapper>

多对多查询模型

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用

多对多查询的需求：查询用户同时查询出该用户的所有角色

在这里插入图片描述

创建Role实体，修改User实体

在这里插入图片描述

public interface UserMapper {
    
    List<User> findAll();
    List<User> findAllUserAndRole();
}

<resultMap id="userRoleMap" type="com.spongebob.pojo.User"> 
    <result column="id" property="id"></result> 
    <result column="username" property="username"></result> 
    <result column="password" property="password"></result> 
    <result column="birthday" property="birthday"></result> 
    <collection property="roleList" ofType="com.spongebob.pojo.Role"> 
        <result column="rid" property="id"></result> 
        <result column="rolename" property="rolename"></result>
    </collection>
</resultMap> 

<select id="findAllUserAndRole" resultMap="userRoleMap">
	select u.*,r.*,r.id rid from user u left join user_role ur on 
u.id=ur.user_id inner join role r on ur.role_id=r.id
</select>

知识小结：

MyBatis多表配置方式：

一对一配置：使用<resultMap>做配置

一对多配置：使用<resultMap>+<collection>做配置

多对多配置：使用<resultMap>+<collection>做配置

1.13、Mybatis注解开发

1.13.1、 MyBatis的常用注解

@Insert：实现新增

@Update：实现更新

@Delete：实现删除

@Select：实现查询

@Result：实现结果集封装

@Results：可以与@Result 一起使用，封装多个结果集

@One：实现一对一结果集封装

@Many：实现一对多结果集封装

1.13.2、 MyBatis的增删改查

public interface UserMapper {

    @Insert("insert into user values(#{id},#{username},#{password},#{birthday})")
    public void save(User user);

    @Update("update user set username=#{username},password=#{password} where id=#{id}")
    public void update(User user);

    @Delete("delete from user where id=#{id}")
    public void delete(int id);

    @Select("select * from user")
    public List<User> findAll();

    @Select("select * from user where id = #{id}")
    public User findById(int id);

}

<!--加载映射关系-->
<!--扫描使用注解的类   或者 扫描注解类所在的包-->

<mappers>
    <!--<mapper class="com.spongebob.mapper.UserMapper"></mapper>-->
	<package name="com.spongebob.mapper"></package>
</mappers>

注意：在xml文件中尽量不要使用单闭合标签，每次报错都是使用了单闭合，导致浪费大量时间。

测试：

@org.junit.Test
public void test3() throws IOException {
    User user = new User(3,"lgb","1234",null);
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = build.openSession(true);
    UserMapper mapper3 = sqlSession.getMapper(UserMapper.class);
    mapper3.save(user);

    sqlSession.commit();
    sqlSession.close();
}

@org.junit.Test
public void testUpdate() throws IOException {
    User user = new User(5,"tutu@qq.com","23456");
    InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper1 = sqlSession.getMapper(UserMapper.class);
    mapper1.update(user);
    sqlSession.commit();
    sqlSession.close();

}

@org.junit.Test
public void testDelete() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper2 = sqlSession.getMapper(UserMapper.class);
    mapper2.delete(3);
    sqlSession.commit();
    sqlSession.close();

}

1.13.3、MyBatis的注解实现复杂映射开发

实现复杂关系映射之前我们可以在映射文件中通过配置<resultMap>来实现，使用注解开发后，我们可以使用@Results注解、@Result注解、@One注解、@Many注解组合完成复杂关系的配置。

注解	说明
@Results	代替的是标签`<resultMap>`该注解中可以使用单个@Result注解，也可以使用@Result集合。使用格式：@Results（{@Result()，@Result()})或@Results(@Result())
@Result	代替了`<id>`标签和`<result>`标签；@Result中属性介绍：column：数据库的列名；property：需要装配的属性名；one：需要使用的@One 注解(@Result(one=@One)()))；many：需要使用的@Many 注解(@Result(many=@many)()))
@One(一对一)	代替了`<assocation>`标签，是多表查询的关键，在注解中用来指定子查询返回单一对象。@One注解属性介绍：select指定用来多表查询;使用格式@Result(column="",property="",one=@One(select=""))
@Many(多对一)	代替了`<collection>`标签, 是是多表查询的关键，在注解中用来指定子查询返回对象集合。使用格式：@Result(property="",column="",many=@Many(select=""))

一对一查询模型:

在这里插入图片描述

public interface UserMapper {
    
    @Select("select * from user where id = #{id}")
    public User findById(int id);
}

public interface OrderMapper {
    @Select("select * from orders")
    @Results({
            @Result(id=true,property = "id",column = "id"),
            @Result(property = "ordertime",column = "ordertime"),
            @Result(property = "total",column = "total"),
            @Result(property = "user",column = "uid", javaType = User.class,
                    one = @One(select ="com.spongebob.mapper.UserMapper.findById"))
    })
    List<Order> findAll();
}

一对多查询模型：

在这里插入图片描述

public interface OrderMapper {

    @Select("select * from orders where uid=#{uid}")
    List<Order> findByUid(int uid);
}

public interface UserMapper {
    
    @Select("select * from user")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "orderList",column = "id", javaType = List.class,
                    many = @Many(select = "com.spongebob.mapper.OrderMapper.findByUid"))
        })
    List<User> findAllUserAndOrder();
}

多对多查询模型：

在这里插入图片描述

public interface RoleMapper {
    
    @Select("select * from role r user_role ur where r.id=ur.role_id and ur.user_id=#{uid}")
    List<Role> findByUid(int uid);
}

public interface UserMapper {
    
    @Select("select * from user")
    @Results({
            @Result(id = true,property = "id",column = "id"),
            @Result(property = "username",column = "username"),
            @Result(property = "password",column = "password"),
            @Result(property = "birthday",column = "birthday"),
            @Result(property = "roleList",column = "id", javaType = List.class,
                    many = @Many(select = "com.spongebob.mapper.RoleMapper.findByUid"))
    })
    List<User> findAllUserAndRole();
    
}

原文链接：https://blog.csdn.net/Good_omen/article/details/121305913