MyBatis review day03 multi table operation

MyBatis multi table operation

Multi table query means that when the query returns the result, it cannot automatically match the number. We will manually seal a Map for it and manually Map the data manually.

The following two tables are used as examples

orders table:

users table:

example: one to one query. One order corresponds to one user

    private int id;
    private Date ordertime;
    private double total;
    // Note that the foreign key private int uid is not directly used here
    // No problem in terms of function implementation, but java pays attention to object-oriented uid. Here is the foreign key connecting another table
    // The relationship between tables is achieved through the main foreign key
    // The relationship between entities is achieved through the reference of entities
    // The idea of object-oriented is to reference through entities
    // Which user does the current order belong to
    private User user;

<!--    Here type  Object data indicating who is encapsulated is encapsulated here order Physical
        Object data, so type by order-->
    <resultMap id="orderMap" type="order">
<!--        Manually specify the mapping relationship between fields and entity attributes
            column: Field name of the data table
            property:Attribute name of the entity
     -->
<!--        here property The properties of are order Properties of
            The result of the query is order of id
    -->
        <id column="oid" property="id"></id>
<!--        zhu key id The label of is id ´╝îOther common attributes are labeled result-->
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <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 *,o.id oid,u.id uid FROM orders o,USER u WHERE o.uid=u.id
    </select>

Here is to query the order table and the user table. The second method:

    <resultMap id="orderMap" type="order">
<!--        Manually specify the mapping relationship between fields and entity attributes
            column:Field name of the data table
            property:Attribute name of the entity
    -->
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <result column="uid" property="user.id"></result>

<!--
    property:Current entity( order)Attribute name in(private User user)
    javaType:Current entity( order)Type of property in(User)
    Lower part association Medium property Medium user by order Attributes in entities
-->
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
        </association>

    </resultMap>

example: one to many query. One user corresponds to multiple orders

   private int id;
    private String username;
    private String password;


    // Description of which orders exist
    private List<Order> orderList;



    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }
    <resultMap id="userMap" type="user">
            <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
<!--        Configure collection information
    property:The name of the collection
    ofType:Data types in the current collection
    -->
        <collection property="orderList" ofType="order">
<!--            encapsulation order Data-->
            <id column="oid" property="id"></id>
            <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,orders o WHERE u.id=o.uid
    </select>

example: many to many query (three tables), introducing an intermediate table

example:

Introduce a collection into the current user table

   //What roles does the current user have
    private List<Role> roleList;

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

Write the xml file:

    <resultMap id="userRoleMap" type="user">
        <id column="userId" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>
<!--        user Information-->
        <collection property="roleList" ofType="role">
            <id column="roleId" property="id"></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
<!--        user Internal roleList information-->
    </resultMap>

    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
    </select>

Knowledge summary:

  1. One to one configuration: (or + association tag)
  2. One to many configuration:+
  3. Many to many configuration:+

MyBatis annotation development

1.1MyBatis common notes

@Insert: add @ Update: Update @ Delete: Delete @ Select: query

@Result: implement result set encapsulation @ Results: can be used together with @ result to encapsulate multiple result sets

@One: implement one-to-one result set encapsulation @ Many: implement one to Many result set encapsulation

example:

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 where id = #{id}")
    public User findById(int id);

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

}

Using annotation development, you don't need the mapping file of the interface, and you don't need to load the mapping file. However, you still need to load the mapping relationship in the mybatis core configuration file, for example:

<!--    Load mapping relationship -->
    <mappers>
<!--    Specify the package where the interface is located-->
        <package name="com.lxz.mapper"></package>
    </mappers>
1.2MyBatis annotation to realize complex mapping development

Use @ Results @One @Many annotation combination to complete the configuration of complex relationships

example:

one-on-one:

One user corresponds to one order

    @Select("select * from orders o,user u where 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")
    })
    public List<Order> findAll();

The second way:

    @Select("select * from orders o,user u where o.uid=u.id")
    @Results({
            @Result(column="oid",property="id"),
            @Result(column="ordertime",property="ordertime"),
            @Result(column="total",property="total"),
            @Result(
                    property="user",//The name of the attribute to encapsulate
                    column="uid",//Query the data of user table according to that field
                    javaType = User.class, //Entity type to encapsulate
                    // The select attribute represents the method of querying that interface to obtain data
                    one = @One(select = "com.lxz.mapper.UserMapper.findById")//The nested query contains another query 
            )
    })
    public List<Order> findAll();

One to many:

One user has multiple orders, that is, one data in the above user table corresponds to multiple data in the order table.

    private int id;
    private String username;
    private String password;
    private Date birthday;

    // Describes the orders that the current user has
    private List<Order> orderList;
 @Select("select * from orders where uid=#{uid}")
 public List<Order> findByUid(int uid);

    @Select("select * from user")
    @Results({
            @Result(id=true,column="id",property="id"),
            @Result(column="username",property="username"),
            @Result(column="password",property="password"),
            @Result(
                    property = "orderList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select = "com.lxz.mapper.OrderMapper.findByUid")  //nested queries 
            )
})
    public List<User> findUserAndOrderAll();

Many to many:

A user has multiple roles, and a role can be used by multiple users.

Query requirements: when querying a user, find out multiple roles of the user at the same time.

user table:

sys_user_role table:

sys_role table:

SELECT * FROM USER" 
SELECT * FROM sys_user_role ur,sys_role r WHERE ur.roleId=r.id AND ur.userId=Last sentence sql Consequential id Value of
    private int id;
    private String username;
    private String password;
    private Date birthday;

    //What roles does the current user have
    private List<Role> roleList;
   @Select("SELECT * FROM sys_user_role ur,sys_role r WHERE ur.roleId=r.id AND ur.userId=#{uid}")
    public List<Role> findByUid(int uid);

    @Select("SELECT * FROM USER")
    @Results({
            @Result(id=true,column="id",property="id"),
            @Result(column = "username",property="username"),
            @Result(column = "password",property="password"),
            @Result(
                    property = "roleList",
                    column = "id",
                    javaType = List.class,
                    many = @Many(select="com.lxz.mapper.RoleMapper.findByUid")
            )
    })
    public List<User> findUserAndRoleAll();

Summary: I spent three days reviewing MyBatis I learned before
! Confucius said that we should review the old and know the new. So, guys, rush!

Keywords: Java linked list

Added by Journey44 on Sun, 06 Mar 2022 16:36:35 +0200