MyBatis多表查询

<mapper namespace="com.idea.mapper.AccountMapper">
   <select id="findAll" resultType="Account">
      select * from Account
   </select>
</mapper>

1.单表查询

1.1首先创建对应Account实体类

public class Account implements Serializable{
   private Integer id;
   private Integer uid;
   private Double money;
   
   //省略set get方法
}

1.2在AccountMapper接口中添加对应的方法

public interface AccountMapper{
   //查询所有账户
   List<Account> findAll();
}

1.3增加映射文件

<mapper namespace="com.idea.mapper.AccountMapper">
   <select id="findAll" resultType="Account">
      select * from Account
   </select>
</mapper>

1.4测试

public void findAll(){
   List<Account> accounts = accountMapper.findAll();
   for(Account account : accounts){
      System.out.println(account);
   }
}

2.Account一对一查询

查询到Account中信息的同时,根据用户id将对应的数据显示出来.

2.1创建User类

public classs User{
   private String username;
   private String addrress;

   //省略set get
}

2.2修改Account类

public class Account implements Serializable{
   private Integer id;
   private Integer uid;
   private Double money;
   
   //省略set get方法
   
   private User user;
   
   public User getUser(){
      return user;
   }
   public void setUser(User user){
      this.user = user;
   }
}

2.3修改AccountMapper.xml

<mapper namespace="com.idea.mapper.AccountMapper">
   <!--建立对应关系-->
   <resultMap type="account" id="accountMap">
      <id column="aid" property="id">
      <result column="uid" property="uid">
      <result column="money" property="money">
      <!--他是用于从表方的引用实体属性-->
      <association property="user" javaType="user">
         <!--column指定数据库列名,property指定实体类属性-->
         <id column="id" property="id">
         <result column="username" property="username">
         <result column="address" property="address">
      </association>
   </resultMap>
   <select id="findAll" resultType="Account">
      select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid = u.id;
   </select>
</mapper>

2.4AccountMapper接口的方法

public interface AccountMapper{
   //查询所有账户
   List<Account> findAll();
}

2.5测试

public void findAll(){
   List<Account> accounts = accountMapper.findAll();
   for(Account account : accounts){
      System.out.println(account);
   }
}

3一对多查询

3.1User类加入List<Account>

public classs User{
   private String username;
   private String addrress;

   //省略set get

   private List<Account> accounts;
   
   public List<Account> getAccounts(){
      return accounts;
   }
   
   pubnlic void setAccounts(List<Account> accounts){
      this.accounts = accounts;
   }
}

3.2Mapper接口中加入查询方法

List<User> findAll()

3.3Mapper映射文件配置

<mapper namespace="com.idea.mapper.UserMapper">
   <resultMap type="user" id="userMap">
      <id column="id" property="id">
      <result column="username" property="username"/>
      <result column="address" property="address">

      <!--collection是用于建立一对多集合属性的对应关系，ofType用于指定集合元素的数据类型-->

      <collection property="accounts" ofType="Account">
         <id column="aid" property="id">
         <result column="uid" property="uid">
         <result column="money" property="money">
      </collection>
   </resultMap>
   
   <select id="findAll" resultMap="userMap">
      select u.*,a.id as aid,a.uid,a.money from user u left outer join account a on u.id=a.uid
   </select>
   
</mapper>

#collection:定义了用户关联的账户信息。表示关联查询结果集

#property:关联查询结果集存储在User对象上的哪个属性

#ofType:关联查询的结果集中的对象类型即list中的对象类型。

4实现Role到User多对多

4.1编写实体类

public class Role implements Serializable{
   private Integer roleId;
   private String roleName;
   private String roleDesc;
   
   //多对多关系映射，一个角色可以赋值多个用户
   private List<User> users;

   public List<User> getUsers(){
      return users;
   }

   public void setUsers(List<User> users){
    
      this.users = users;
   }
   
   //省略set get
}

4.2编写Role的持久层接口

public interface RoleMapper{
   List<Role> findAll();
}

4.3编写映射文件

<mapper namespace="com.idea.mapper.RoleMapper">
   <resultMap id="roleMap" type="Role">
      <id column="roleId" property="roleId">
      <result column="roleName" property="roleName">
      <result column="roleDesc" property="roleDesc">
      <collection property="users" type="User">
         <id column="id" property="id">
         <result column="username" property="username">
         <result column="address" property="address">
      </collection>
   </resultMap>
   <select id=""findAll resultType="roleMap">
      ...
   </select> 
</mapper>