多表查询

一对一

  • 定义结果映射
    <resultMap id="accountMap" type="Account">
        <id column="account_id" property="id"/>
        <result column="UID" property="uid"/>
        <result column="MONEY" property="money"/>
        <association property="user" javaType="User">
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result property="address" column="address"/>
            <result property="birthday" column="birthday"/>
            <result property="sex" column="sex"/>
        </association>
    </resultMap>
  • 多表查询设置结果映射
   <select id="findAll" resultMap="accountMap">
        SELECT account.ID AS account_id,
        account.UID,
        account.MONEY,
        user.*
        FROM account,user
        WHERE account.UID = user.id
    </select>
  • 实体类
@Data
public class Account {

    private Integer id;

    private Integer uid;

    private Double money;

    private User user;
}

一对多

    <resultMap id="userMap" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result property="address" column="address"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <collection property="account" ofType="Account">
            <id column="account_id" property="id"/>
            <result column="uid" property="uid"/>
            <result column="money" property="money"/>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
       SELECT user.*,
        account.ID as account_id,
        account.uid,
        account.money
        FROM user LEFT OUTER JOIN account ON user.id = account.UID
    </select>
@Data
public class User {

    private Integer id;

    private String username;

    private LocalDate birthday;

    private String sex;

    private String address;

    private List<Account> account;
}

左外连接的使用

多对多

多对多的映射关系,可以拆分成两个一对多的关系

results matching " "

No results matching " "

results matching " "

No results matching " "