多表操作

分类

一对一
一对多
多对一
多对多

一对多

建表
共二表
两张表之间是一对多的关系:在多的表中添加字段指向一的表的主键

建类
共需两个pojo类,两个mapper接口
在一的类中添加一个多类型的集合属性,例

一个用户可以有多个账户
账户类
public class Account {
    private Integer id;
    private Integer uId;
    private double money;
}
用户类
public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //  使用Account类型的集合来表示:用户有多个账户
    List<Account> accounts;
}

UserMapper.xml sql语句
查询

<!-- 自定义返回值映射 -->
<resultMap id="userAccount" type="user">
  <id property="id" column="id" ></id>
  <result property="username" column="username" ></result>
  <result property="birthday" column="birthday" ></result>
  <result property="sex" column="sex" ></result>
  <result property="address" column="address" ></result>
  <!--  collection集合  property为要映射的属性名  ofType集合中的元素类型  -->
  <collection property="accounts"  ofType="account" >
      <id property="id" column="aId" ></id>
      <result property="money" column="money" ></result>
  </collection>
</resultMap>
<!--查询所有用户及用户的账户信息  需要使用自定义的返回值映射-->
<select id="findUserAccount" resultMap="userAccount">
    SELECT u.id, u.username, u.birthday, u.sex, u.address, a.id aId, a.money
    FROM `user` u LEFT JOIN account a ON u.id = a.uId;
</select>

测试

public class TestUser {
  private InputStream inputStream;
  private SqlSession sqlSession;
  private UserMapper userMapper;
  private SqlSessionFactory sqlSessionFactory;

  @Before
  public void init() {
    try {
        inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    } catch (IOException e) {
        e.printStackTrace();
    }
    SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    //  这里的事务默认不开启
    sqlSession = sqlSessionFactory.openSession();
    userMapper = sqlSession.getMapper(UserMapper.class);
  }

  @After
  public void destory() {
    //  这里提交事务
    sqlSession.commit();
    try {
        inputStream.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
    sqlSession.close();
  }

  //  查询所有用户及用户的账户信息
  @Test
  public void testUserAccount() {
    List<User> userAccount = userMapper.findUserAccount();
    for (User user : userAccount) {
      System.out.println(user);
      for (Account account : user.getAccounts()) {
          System.out.println(account);
      }
      System.out.println("---------------------------------------");
    }
  }
}

多对一

建表
共二表
两张表之间是一对多的关系:在多的表中添加字段指向一的表的主键

建类
共需两个pojo类,两个mapper接口
在多的类中添加一类型的属性例

多个账户有同一个用户
账户类
public class Account {
    private Integer id;
    private Integer uId;
    private double money;
    //  使用User类型的属性来表示:多个账户有一个用户
    private  User user;
}
用户类
public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
}

SQL操作
查询

<!-- 返回值映射 -->
<resultMap id="accountUser" type="account" >
  <id property="id" column="id" ></id>
  <result property="money" column="money" ></result>
  <!-- association 映射user属性 -->
  <!--  property：要映射的属性名  column：列名   javaType：要映射的类型 -->
  <association property="user" javaType="User">
    <id property="id" column="uId" ></id>
    <result property="username" column="username" ></result>
    <result property="birthday" column="birthday" ></result>
    <result property="sex" column="sex" ></result>
    <result property="address" column="address" ></result>
  </association>
</resultMap>
<!--查询所有账户及账户的用户信息-->
<select id="findAccountUser" resultMap="accountUser">
  SELECT a.id, a.money, u.id uId, u.username, u.birthday, u.sex, u.address
  from account a LEFT JOIN user u ON a.UID = u.id;
</select>

测试

public class TestAccount {
  private InputStream inputStream;
  private SqlSession sqlSession;
  private AccountMapper accountMapper;
  private SqlSessionFactory sqlSessionFactory;

  @Before
  public void init() {
    try {
        inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
    } catch (IOException e) {
        e.printStackTrace();
    }
    SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
    //  这里的事务默认不开启
    sqlSession = sqlSessionFactory.openSession();
    accountMapper = sqlSession.getMapper(AccountMapper.class);
  }

  @After
  public void destory() {
    //  这里提交事务
    sqlSession.commit();
    try {
        inputStream.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
    sqlSession.close();
  }

  //  查询所有账户及账户的用户信息
  @Test
  public  void testAccountUser(){
    List<Account> accounts = accountMapper.findAccountUser();
    for (Account account : accounts) {
        System.out.println(account);
        System.out.println(account.getUser());
        System.out.println("--------------------------------------------");
    }
  }
}

多对多

建表
共三表
两张表之间是多对多的关系:需要添加一个中间表
中间表的两个字段分别关联两张表的主键
建类
共需两个pojo类,两个mapper接口
在类中添加另一类型的集合属性

多个账户有同一个用户
角色类
public class Role {
    private Integer id;
    private String roleName;
    private String roleDesc;
    //  指向另一表
    private List<User> users;
}
用户类
public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //  指向另一表
    private List<Role> roles;
}

SQL操作
查询

UserMapper.xml

<resultMap id="userRole" type="user">
    <id property="id" column="id"></id>
    <result property="username" column="username"></result>
    <result property="birthday" column="birthday"></result>
    <result property="sex" column="sex"></result>
    <result property="address" column="address"></result>
    <collection property="roles" ofType="role">
        <id property="id" column="rId"></id>
        <result property="roleDesc" column="roleDesc"></result>
        <result property="roleName" column="roleName"></result>
    </collection>
</resultMap>
<!--查询所有用户,用户包含拥有的角色-->
<select id="findUserRole" resultMap="userRole">

    SELECT u.id,
            u.username,
            u.birthday,
            u.sex,
            u.address,
            r.ID rId,
            r.ROLE_DESC roleDesc,
            r.ROLE_NAME roleName
    FROM `user` u
              LEFT JOIN user_role ur ON u.id = ur.UID
              LEFT JOIN role r ON ur.RID = r.ID

</select>

RoleMapper.xml

<resultMap id="roleUser" type="role">
    <id property="id" column="rId"></id>
    <result property="roleDesc" column="roleDesc"></result>
    <result property="roleName" column="roleName"></result>
    <collection property="users" ofType="user">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
    </collection>
</resultMap>
<!--查询所有用户,用户包含拥有的角色-->
<select id="findRoleUser" resultMap="roleUser">

    SELECT u.id,
            u.username,
            u.birthday,
            u.sex,
            u.address,
            r.ID        rId,
            r.ROLE_DESC roleDesc,
            r.ROLE_NAME roleName
    FROM `role` r
              LEFT JOIN user_role ur ON r.id = ur.RID
              LEFT JOIN user u ON ur.UID = u.id

</select>

测试
TestUser.java

public class TestUser {
  private InputStream inputStream;
  private SqlSession sqlSession;
  private UserMapper userMapper;
  private SqlSessionFactory sqlSessionFactory;

  @Before
  public void init() {
      try {
          inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
      } catch (IOException e) {
          e.printStackTrace();
      }
      SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
      sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
      //  这里的事务默认不开启
      sqlSession = sqlSessionFactory.openSession();
      userMapper = sqlSession.getMapper(UserMapper.class);
  }

  @After
  public void destory() {
      //  这里提交事务
      sqlSession.commit();
      try {
          inputStream.close();
      } catch (IOException e) {
          e.printStackTrace();
      }
      sqlSession.close();
  }

  @Test
  public void testUserRole() {
      List<User> userRole = userMapper.findUserRole();
      for (User user : userRole) {
          System.out.println(user);
          System.out.println(user.getRoles());
          System.out.println("--------------------------");
      }
  }
}

TestRole.java

public class TestRole {
  private InputStream inputStream;
  private SqlSession sqlSession;
  private RoleMapper roleMapper;
  private SqlSessionFactory sqlSessionFactory;

  @Before
  public void init() {
      try {
          inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
      } catch (IOException e) {
          e.printStackTrace();
      }
      SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
      sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
      //  这里的事务默认不开启
      sqlSession = sqlSessionFactory.openSession();
      roleMapper = sqlSession.getMapper(RoleMapper.class);
  }

  @After
  public void destory() {
      //  这里提交事务
      sqlSession.commit();
      try {
          inputStream.close();
      } catch (IOException e) {
          e.printStackTrace();
      }
      sqlSession.close();
  }

  @Test
  public void testUserRole() {
      List<Role> roleUser = roleMapper.findRoleUser();
      for (Role role : roleUser) {
          System.out.println(role);
          System.out.println(role.getUsers());
          System.out.println("--------------------------");
      }
  }
}