mybatis 一对一,一对多,多对多关系映射查询操作
定义两个类(对应数据库内两张表) User ,Account,每个Account属于一个User
User类 及其 对应的IUserDao
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.itheima.domain; import java.io.Serializable; import java.util.Date; import java.util.List; public class User implements Serializable { private Integer id; private String username; private Date birthday; private String sex; private String address; //一对多关系映射,主表实体应该包含从表实体的集合引用 private List<Account> accounts; public List<Account> getAccounts() { return accounts; } public void setAccounts(List<Account> accounts) { this.accounts = accounts; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.itheima.dao; import com.itheima.domain.QueryVo; import com.itheima.domain.User; import java.util.List; public interface IUserDao { /* * 查询所有操作 * */ List<User> findAll(); /* * 保存一条记录并返回id(直接将id赋给对应的属性) * */ void saveUser(User user); /* * 更新用户 * */ void updateUser(User user); /* * 删除用户 * */ void deleteUser(Integer userId); /* * 根据Id查询User * */ User findById(Integer userId); /* * 模糊查询 * */ List<User> findByName(String username); /* * 查询User总数 * */ int findTotal(); /* * 根据queryVo查询中的条件查询用户 * */ List<User> findByVo(QueryVo vo); /* * 根据传入参数条件查询 * @param user 查询的条件:有可能有用户名,有可能有性别,也有可能有地址,可能啥都没有 * */ List<User> findUserByCondition(User user); /* * 根据queryVo中提供的id集合查询用户信息 * */ List<User> findUserInIds(QueryVo vo); }
Account类 及其对应的 IAccountDao
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.itheima.domain; import java.io.Serializable; public class Account implements Serializable { private Integer id; private Integer uid; private Double money; //从表实体应该包含一个主表实体的对像引用 private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getUid() { return uid; } public void setUid(Integer uid) { this.uid = uid; } public Double getMoney() { return money; } public void setMoney(Double money) { this.money = money; } @Override public String toString() { return "Account{" + "id=" + id + ", uid=" + uid + ", money=" + money + '}'; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.itheima.dao; import com.itheima.domain.Account; import com.itheima.domain.AccountUser; import java.util.List; public interface IAccountDao { /* * 查询所有Account * */ List<Account> findAll(); /* * 查询所有账户,并且带有用户名和地址 * */ List<AccountUser> findAllAccountUser(); }
一对一(多对一)的关系查询:查询Account时也要查询出所属User的信息
显然每个Account对应了一个User
//从表实体应该包含一个主表实体的对像引用 private User user;
下面是IAccountDao.xml 对于 findAll方法的配置
<?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.dao.IAccountDao"> <!--定义封装account和user的resultMap--> <resultMap id="accountUserMap" type="com.itheima.domain.Account"> <id property="id" column="aid"></id> <result property="uid" column="uid"></result> <result property="money" column="money"></result> <!--一对一关系映射,配置封装user的内容,column:外键,javaType:联系对象的全限定类名--> <association property="user" column="uid" javaType="com.itheima.domain.User"> <id property="id" column="id"></id> <result column="username" property="username"></result> <result column="address" property="address"></result> <result column="sex" property="sex"></result> <result column="birthday" property="birthday"></result> </association> </resultMap> <!--查询所有Account并获得对应User信息(多对一标准写法)--> <select id="findAll" resultMap="accountUserMap"> select u.*, a.id as aid, a.uid, a.money from account as a, user as u where u.id = a.uid </select> <!--查询所有Account,同时包含用户名和地址信息--> <select id="findAllAccountUser" resultType="com.itheima.domain.AccountUser"> select a.*,u.username,u.address from account as a, user as u where a.uid = u.id; </select> </mapper>
测试代码
/* * 查询所有Account并获得对应User信息(多对一标准写法) * */ @Test public void testFindAll(){ List<Account> accounts = accountDao.findAll(); for(Account account:accounts) System.out.println(account+account.getUser().toString()); }
一对多的关系查询:查询User时查询所有和其相关的Account集合
类似Account类内声明一个User的引用,User内也声明了一个Account类的集合
//一对多关系映射,主表实体应该包含从表实体的集合引用 private List<Account> accounts;
IUserDao.xml 关于 findAll方法的配置
<resultMap id="userAccountMap" type="com.itheima.domain.User"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> <!--配置user对象中accounts集合的映射,ofTpe:集合中元素的全限定类名--> <collection property="accounts" ofType="com.itheima.domain.Account"> <id property="id" column="aid"></id> <result property="uid" column="uid"></result> <result property="money" column="money"></result> </collection> </resultMap> <!--配置查询所有--> <select id="findAll" resultMap="userAccountMap"> select * from user as u left outer join account as a on u.id = a.uid </select>
测试方法
@Test public void testFindAll()throws Exception{ //5.使用代理对象执行方法 List<User>users = userDao.findAll(); for(User user:users) { System.out.println("---------每个用户的信息----------"); System.out.println(user); System.out.println(user.getAccounts()); } }
多对多关系的查询:User和Role之间多对多
首先定义Role实体类(数据库里还要一张user_role的对应关系表)
Role类及其Dao接口IRoleDao
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.itheima.domain; import java.io.Serializable; import java.util.List; 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; } @Override public String toString() { return "Role{" + "roleId=" + roleId + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } public Integer getRoleId() { return roleId; } public void setRoleId(Integer roleId) { this.roleId = roleId; } public String getRoleName() { return roleName; } public void setRoleName(String roleName) { this.roleName = roleName; } public String getRoleDesc() { return roleDesc; } public void setRoleDesc(String roleDesc) { this.roleDesc = roleDesc; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.itheima.dao; import com.itheima.domain.Role; import java.util.List; public interface IRoleDao { /* * 查询所有角色 * */ List<Role> findAll(); }
查询User以及和其相关的所有roles
首先User类中要有Role的一个集合
//多对多的关系映射:一个用户可以具备多个角色 private List<Role> roles;
IUserDao.xml里的配置实现
<!--定义User多对多查询roles的resultMap--> <resultMap id="userRolesMap" type="com.itheima.domain.User"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="address" column="address"></result> <result property="sex" column="sex"></result> <result property="birthday" column="birthday"></result> <!--配置角色集合的映射--> <collection property="roles" ofType="com.itheima.domain.Role"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> </collection> </resultMap> <!--配置查询所有--> <select id="findAll" resultMap="userRolesMap"> select u.*,r.id as rid,r.role_name,r.role_desc from user as u left outer join user_role as ur on u.id = ur.uid left outer join role as r on r.id = ur.rid </select>
测试代码
@Test public void testFindAll()throws Exception{ //5.使用代理对象执行方法 List<User>users = userDao.findAll(); for(User user:users) { System.out.println("---------每个用户的信息----------"); System.out.println(user); System.out.println(user.getRoles()); } }
查询Role以及和其相关的所有User类
首先Role里要有一个User类的集合
//多对多的关系映射:一个角色可以赋予多个用户 private List<User> users;
IRoleDao.xml 配置实现
<!--定义role表的resultMap--> <resultMap id="roleMap" type="com.itheima.domain.Role"> <id property="roleId" column="rid"></id> <result property="roleName" column="role_name"></result> <result property="roleDesc" column="role_desc"></result> <collection property="users" ofType="com.itheima.domain.User"> <id column="id" property="id"></id> <result column="birthday" property="birthday"></result> <result column="username" property="username"></result> <result column="address" property="address"></result> <result column="sex" property="sex"></result> </collection> </resultMap> <!--查询所有--> <select id="findAll" resultMap="roleMap"> select u.*, r.id as rid, r.role_name, r.role_desc from role as r left outer join user_role as ur on r.id = ur.rid left outer join user u on u.id = ur.uid </select>
测试代码
@Test public void testFindAll(){ List<Role>roles = roleDao.findAll(); for(Role role:roles) { System.out.println("---每个角色的信息---"); System.out.println(role); System.out.println(role.getUsers()); } }