1. 一对一查询 数据库表
用户表user:id、username、birthday、sex、address
账户表account:id、uid(外键,参考user.id)、money
需求:查询所有账户信息,关联查询下单用户信息。
注意:因为一个账户信息只能供某个用户使用,所以从查询账户信息出发关联查询用户信息为一对一查询。如果从用户信息出发查询用户下的账户信息则为一对多查询,因为一个用户可以有多个账户。
1.1方式一 思想:定义专门的 po 类作为输出类型,其中定义了 sql 查询结果集所有的字段。
1.封装查询结果类:为了能够封装SQL 语句的查询结果,定义 AccountCustomer 类中要包含账户信息同时还要包含用户信息,所以我们要在定义 AccountUser 类时可以继承 Account类。
1 2 3 4 public class AccountUser extends Account implements Serializable { private String username; private String address; }
2.UserDao接口方法:
1 2 List<AccountUser> findAllAccountUser () ;
3.UserDao.xml配置文件
1 2 3 <select id ="findAllAccountUser" resultType ="AccountUser" > select a.*,u.username,u.address from user u,account a where a.uid = u.id </select >
1.2方式二(常用方式) 思想:从表实体应该包含一个主表实体的对象引用
通过面向对象的has a 的关系可知,一个账户被一个用户所拥有,所以可以在account类中添加一个user类的对象来表示这个账户的所属用户
account类
1 2 3 4 5 6 public class Account implements Serializable { private Integer id; private Integer uid; private Double money; private User user; }
UserDao.xml配置文件
resultMap参数说明:
id:指定查询列中的唯一标识
type:封装的全限定类名
id 标签:用于指定所在类的主键字段 result 标签:用于指定非主键字段
association参数说明:
1.property:关联查询的信息,本例中指account下的user属性。 2.javaType:关联的属性user的所属类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 <resultMap id ="accountUserMap" type ="account" > <id property ="id" column ="aid" > </id > <result property ="uid" column ="uid" > </result > <result property ="money" column ="money" > </result > <association property ="user" javaType ="User" > <id property ="id" column ="id" > </id > <result property ="username" column ="username" > </result > <result column ="sex" property ="sex" > </result > <result column ="address" property ="address" > </result > <result column ="birthday" property ="birthday" > </result > </association > </resultMap > <select id ="findAllAccountOfUser" resultMap ="accountUserMap" > select u.*,a.id as aid,a.money from user u,account a where a.uid = u.id </select >
2.一对多查询 数据库表
用户表user:id、username、birthday、sex、address
账户表account:id、uid(外键,参考user.id)、money
需求:查询所有用户,同时获取到用户下所有账户的信息
思想:一对多关系映射,主表实体应该包含从表实体的集合引用
User主表类
1 2 3 4 5 6 7 8 9 10 public class User1 { private Integer id; private String username; private Date birthday; private String sex; private String address; private List<Account1> accounts; }
UserDao接口方法
1 2 List<User1>findAllAccountsOfUser();
UserDao.xml配置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 <resultMap id ="userAccountsMap" type ="user1" > <id column ="id" property ="id" > </id > <result column ="username" property ="username" > </result > <result column ="sex" property ="sex" > </result > <result column ="address" property ="address" > </result > <result column ="birthday" property ="birthday" > </result > <collection property ="accounts" ofType ="Account1" > <id column ="aid" property ="id" > </id > <result property ="uid" column ="uid" > </result > <result property ="money" column ="money" > </result > </collection > </resultMap > <select id ="findAllAccountsOfUser" resultMap ="userAccountsMap" > select u.*,a.id as aid,a.MONEY from user u LEFT JOIN account a on u.id = a.uid; </select >
3.多对多查询 示例:用户和角色,一个用户可以有多个角色,一个角色可以赋予个多个用户
建立两张表:用户表、角色表、中间表
让用户表和角色表具有多对多的关系,需要使用中间表,中间表包含各自的主键,是外键
(1) 用户表user:id、username、birthday、sex、address
(2) 角色表role:id、role_name、role_desc(角色描述)
(3) 中间表user_role:uid、rid
建立实体类:用户实体类和角色实体类
体现多对多的关系:各自包含对方一个集合引用,使用Pojo对象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 public class rUser { private Integer id; private String username; private Date birthday; private String sex; private String address; } public class role { private Integer roleId; private String roleName; private String roleDesc; } public class queryUserRole { private rUser user; private List<role> roles; } public class queryRoleUser { private role role; private List<rUser> users; }
UserDao接口方法
1 2 3 4 5 List<queryUserRole> findUserRole () ;List<queryRoleUser> findRoleUser () ;
配置文件
查询用户时,可以得到用户所包含的角色信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 <resultMap id ="UserRolesMap" type ="queryUserRole" > <id column ="id" property ="user.id" > </id > <result column ="username" property ="user.username" > </result > <result column ="sex" property ="user.sex" > </result > <result column ="address" property ="user.address" > </result > <result column ="birthday" property ="user.birthday" > </result > <collection property ="roles" ofType ="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 ="findUserRole" resultMap ="UserRolesMap" > select u.*,r.id as rid,r.role_name,r.role_desc from user u left outer join user_role ur on u.id = ur.uid left outer join role r on r.id = ur.rid </select >
查询角色时可以得到角色的所赋予的用户信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 <resultMap id ="RoleUsersMap" type ="queryRoleUser" > <id property ="role.roleId" column ="rid" > </id > <result property ="role.roleName" column ="role_name" > </result > <result property ="role.roleDesc" column ="role_desc" > </result > <collection property ="users" ofType ="user" > <id column ="id" property ="id" > </id > <result column ="username" property ="username" > </result > <result column ="sex" property ="sex" > </result > <result column ="address" property ="address" > </result > <result column ="birthday" property ="birthday" > </result > </collection > </resultMap > <select id ="findRoleUser" resultMap ="RoleUsersMap" > select role.id as rid,role.role_name,role.role_desc,user.* from role LEFT OUTER JOIN user_role on role.id = user_role.rid LEFT outer JOIN user on user.id = user_role.uid </select >