Mybatis(三)-阿里云开发者社区

⑥. 表和表之间的关系

1>. 一对一

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

//1.实体类
public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    }
public class Orders {
    private int id;
    private String orderTime;
    private double total;
    /*当前订单属于哪一个用户*/
    private User user;
    }

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.OrderMapperday03">
    <!--一对一-->
    <resultMap id="orderMap" type="com.itheima.domain.Orders">
        <!--手动指定字段与实体属性的映射关系
        column:数据表的字段名称
        property:实体的属性名称
        -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="orderTime"/>
        <result column="total" property="total"/>
        <!--实现一对一
        property:当前实体(Orders)中的属性名称(private User user)
        javaType:当前实体(Orders)中属性的类型(User)
        -->
        <association property="user" javaType="com.itheima.domain.User">
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="birthday" property="birthday"/>
        </association>
    </resultMap>
    <select id="findAll" resultMap="orderMap">
        select o.id oid,o.ordertime,o.total,o.uid,u.*
        from orders o inner join user u
        on o.uid=u.id
    </select>
</mapper>

2>. 一对多

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

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Orders> orderList;
    }

    <!--一对多-->
    <resultMap id="userMap" type="com.itheima.domain.User">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="birthday" property="birthday"/>
        <!--一对多:配置集合信息
         property:当前实体(User)中的属性名称(private List<Orders> orderList)
         javaType:当前实体(User)中属性的类型(Orders)
        -->
        <collection property="orderList" ofType="com.itheima.domain.Orders">
            <id column="oid" property="id"></id>
            <result column="ordertime" property="orderTime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>
   <select id="findAll" resultMap="userMap">
        select u.*,o.id oid,o.ordertime,o.total,o.uid
        from user u inner join orders o
        on u.id=o.uid
   </select>

3>. 多对多

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

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Order> orderList;
    //代表当前用户具备哪些角色
    private List<Role> roleList;
}
public class Role {
    private int id;
    private String rolename;
}

<!--多对多-->
    <resultMap id="userRoleMap" type="com.itheima.domain.User">
        <id column="userId"  property="id"/>
        <result column="username" property="username" />
        <result column="password" property="password" />
        <result column="birthday" property="birthday" />
        <collection property="roleList" ofType="com.itheima.domain.Role">
            <id column="roleId" property="id"/>
            <result column="roleName" property="roleName" />
            <result column="roleDesc" property="roleDesc" />
        </collection>
    </resultMap>
    <!--多对多-->
    <select id="findUserRoleAll" resultMap="userRoleMap">
    select * from
    user u ,sys_user_role sur ,sys_role sr
    where u.id=sur.userid and sur.roleid=sr.id
    </select>

4>. 知识小结

①. 一对一配置：使用<resultMap>做配置
②. 一对多配置：使用<resultMap>+<collection>做配置
③. 多对多配置：使用<resultMap>+<collection>做配置

⑦. 注解开发

1>. 常用的注解

这几年来注解开发越来越流行，Mybatis也可以使用注解开发方式，这样我们就可以减少编写Mapper映射文件了。我们先围绕一些基本的CRUD来学习，再学习复杂映射多表操作

1.@Insert：实现新增
2.@Update：实现更新
3.@Delete：实现删除
4.@Select：实现查询
5.@Result：实现结果集封装
6.@Results：可以与@Result 一起使用，封装多个结果集
7.@One：实现一对一结果集封装
8.@Many：实现一对多结果集封装

2>. 增删改查方法

//注意这里的mappers--package
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--引入配置文件-->
    <properties resource="jdbc.properties"/>
         <environments default="developement">
             <environment id="developement">
                 <transactionManager type="JDBC"></transactionManager>
                 <dataSource type="POOLED">
                     <property name="driver" value="${jdbc.driver}"/>
                     <property name="url" value="${jdbc.url}"/>
                     <property name="username" value="${jdbc.username}"/>
                     <property name="password" value="${jdbc.password}"/>
                 </dataSource>
             </environment>
         </environments>
    <!--加载映射文件-->
    <mappers>
        <!--指定接口所在的包-->
        <package name="com.itheima.mapper"/>
    </mappers>
</configuration>

public interface UserMapper {
     @Select("select * from user where id=#{id}")
     User findUserById(Integer id);
     @Insert("insert into user(id,username,password,birthday)values(#{id},#{username},#{password},#{birthday})")
     void insertUser(User user);
     @Update("update user set username=#{username},password=#{password} where id=#{id}")
     void updateUser(User user);
     @Delete("delete from user where id=#{id}")
     void deleteById(int id);
     }

3>. MyBatis的注解实现复杂映射开发

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

1. 一对一

public interface OrdersMapper {
    @Select("select *,o.id oid from orders o inner join user u on o.uid=u.id")
    @Results({
            @Result(column="oid",property = "id"),
            @Result(column="ordertime",property = "ordertime"),
            @Result(column="total",property = "total"),
            @Result(column="uid",property = "user.id"),
            @Result(column="username",property = "user.username"),
            @Result(column="password",property = "user.password")
    })
    List<Orders> oneFaceOne();
}

public interface OrdersMapper {
    @Select("select * from orders")
    @Results({
            @Result(column="id",property = "id"),
            @Result(column="ordertime",property = "ordertime"),
            @Result(column="total",property = "total"),
            @Result(
                  property = "user",//要封装的属性名称
                  column = "uid",//根据哪个字段去查询user表的数据
                  javaType = User.class,//要封装的实体类型
                  //select属性,代表查询哪个接口的方法获得数据
                  one=@One(select="com.itheima.mapper.UserMapper.findById")
            )
    })
    List<Orders> oneFaceOne();
}

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

public class mybatisTest2 {
    @Test
    public void findUserAll(){
        SqlSession sqlSession = SqlSessionFactroyUtils.getSqlSession();
        OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> orders = mapper.oneFaceOne();
        System.out.println("orders = " + orders);
    }
}

2. 一对多查询的模型

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

②. 一对多查询的语句

select * from user;
select * from orders where uid=查询出用户的id;

③. 查询的结果如下：

④. 修改User实体

public class Order {
    private int id;
    private Date ordertime;
    private double total;
    //代表当前订单从属于哪一个客户
    private User user;
}
public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Order> orderList;
}

⑤. 使用注解配置Mapper

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.itheima.mapper.OrderMapper.findByUid"))
    })
    List<User> findAllUserAndOrder();
}
public interface OrderMapper {
    @Select("select * from orders where uid=#{uid}")
    List<Order> findByUid(int uid);
}

⑥. 测试结果

List<User> all = userMapper.findAllUserAndOrder();
for(User user : all){
    System.out.println(user.getUsername());
    List<Order> orderList = user.getOrderList();
    for(Order order : orderList){
        System.out.println(order);
    }
    System.out.println("-----------------------------");
}

3. 多对多查询

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

①. 多对多查询的语句

select * from user;
select * from role r,user_role ur where r.id=ur.role_id and ur.user_id=用户的id

②. 创建Role实体，修改User实体

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;
    //代表当前用户具备哪些订单
    private List<Order> orderList;
    //代表当前用户具备哪些角色
    private List<Role> roleList;
}
public class Role {
    private int id;
    private String rolename;
}

③. 使用注解配置Mapper

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.itheima.mapper.RoleMapper.findByUid"))
})
List<User> findAllUserAndRole();}
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);
}

④. 测试结果

UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> all = mapper.findAllUserAndRole();
for(User user : all){
    System.out.println(user.getUsername());
    List<Role> roleList = user.getRoleList();
    for(Role role : roleList){
        System.out.println(role);
    }
    System.out.println("----------------------------------");
}

Mybatis(三)

⑥. 表和表之间的关系

⑦. 注解开发

热门文章

最新文章

相关课程

相关电子书