案例:查询所有订单信息,关联查询下单用户信息。
注意:因为一个订单信息只会是一个人下的订单,所以从查询订单信息出发关联查询用户信息为一对一查询。
使用resultType,定义订单信息po类,此po类中包括了订单信息和用户信息:
SELECT * from `order` a,user b WHERE a.user_id = b.id; |
---|
SELECT a.*,b.id uid,b.username,b.birthday,b.sex,b.address from `order` a,`user` b WHERE a.user_id = b.id; |
---|
public class CustomerOrder extends Orders { private int uid; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; }} |
---|
xml version="1.0" encoding="UTF-8"?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> << span="">mapper namespace="com.shop.mapper.CustomerOrderMapper"> << span="">select id="selectOrderAndUserList" resultType="com.shop.domain.CustomerOrder"> SELECT a.*,b.id as uid,b.username,b.birthday,b.sex,b.address from `order` a,`user` bwhere a.user_id = b.id; select> mapper> |
---|
package com.shop.mapper;import com.shop.domain.CustomerOrder;import java.util.List;public interface CustomerOrderMapper { ListselectOrderAndUserList();} |
---|
@Testpublic void test3() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); CustomerOrderMapper customerOrderMapper = sqlSession.getMapper(CustomerOrderMapper.class); Listlist = customerOrderMapper.selectOrderAndUserList(); for (CustomerOrder customerOrder : list) { System.out.println(customerOrder); }} |
---|
观察打印结果
思考:如果使用user对象呢?
分析: 上面使用自动映射方式存在什么问题?
1.需要单独建立 实体类 、mapper接口、mapper.xml文件
2.实体的字段名和数据表的列名不一致,只能自己手动修改表或者实体字段名称
3.实体类中不能使用对象作为 属性(User)
xml version="1.0" encoding="UTF-8"?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> << span="">mapper namespace="com.shop.mapper.OrderMapper"> << span="">resultMap id="aaa" type="com.shop.domain.Orders"> << span="">id property="id" column="id">id> << span="">result property="userId" column="user_id">result> << span="">result property="number" column="number">result> << span="">result property="createtime" column="createtime">result> << span="">association property="user" javaType="com.shop.domain.User"> << span="">id property="id" column="uid">id> << span="">result property="username" column="username">result> << span="">result property="sex" column="sex">result> << span="">result property="birthday" column="birthday">result> << span="">result property="address" column="address">result> association> resultMap> << span="">select id="selectOrderAndUserList" resultMap="aaa">SELECT a.*,b.id as uid,b.username,b.birthday,b.sex,b.address from `order` a,`user` bwhere a.user_id = b.id; select> mapper> |
---|
Order:
public class Orders { private Integer id; private Integer userId; private String number; private Date createtime; private String note; // 一个订单只有有一个用户 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 getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getNumber() { return number; } public void setNumber(String number) { this.number = number == null ? null : number.trim(); } public Date getCreatetime() { return createtime; } public void setCreatetime(Date createtime) { this.createtime = createtime; } public String getNote() { return note; } public void setNote(String note) { this.note = note == null ? null : note.trim(); } } |
---|
@Testpublic void test2() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); Listlist = orderMapper.selectOrderAndUserList(); for (Orders orders : list) { System.out.println(orders); }} |
---|
注意:
如果从用户信息出发查询用户下的 订单信息 则为一对多查询
因为一个用户可以下多个订单。
从用户角度看订单 :一对多关系
SELECT * from `user` a ,`order` b WHERE a.id = b.user_id; |
---|
SELECT a.* ,b.id oid,b.user_id,b.number,b.createtime from `user`a,`order` b WHERE a.id = b.user_id; |
---|
public class User { private int id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 Listorders = new ArrayList(); public ListgetOrders() { return orders; } public void setOrders(Listorders) { this.orders = orders; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "User [id=" + id + ", username=" + username + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address + "]"; } } |
---|
xml version="1.0" encoding="UTF-8" ?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><< span="">mapper namespace="com.shop.mapper.UserMapper"> << span="">resultMap id="aaa" type="com.shop.domain.User"> << span="">id property="id" column="id">id> << span="">result property="username" column="username">result> << span="">result property="sex" column="sex">result> << span="">result property="birthday" column="birthday">result> << span="">result property="address" column="address">result> << span="">collection property="orders" ofType="com.shop.domain.Orders"> << span="">id property="id" column="oid">id> << span="">result property="userId" column="user_id">result> << span="">result property="number" column="number">result> << span="">result property="createtime" column="createtime">result> << span="">result property="note" column="note">result> collection> resultMap><< span="">select id="selectUserOrderList" resultMap="aaa"> SELECT a.*,b.id oid,b.user_id,b.number,b.note from user a,`order` b where a.id = b.user_id;select>mapper> |
---|
@Testpublic void test3() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); Listlist = userMapper.selectUserOrderList(); for (User user : list) { System.out.println(user); }} |
---|
一个人可以拥有多个角色
一个角色也可以有多个用户
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.uidleft OUTER join role r on r.id = ur.rid |
---|
角色表和 用户角色表关联查询
SELECT * from role r left JOIN user_role ur on r.id = ur.rid; |
---|
角色表 和 用户角色表 以及用户表关联查询
SELECT * from role r left join user_role ur on r.id = ur.rid left JOIN `user` u on u.id = ur.UID; |
---|
去掉中间表的数据 以及处理 role 表的id 别名问题
select u.*,r.id as rid,r.role_name,r.role_desc from role r left join user_role ur on r.ID = ur.rid left join user u on u.id = ur.uid; |
---|
基本格式
public class User { private Integer id; private String username; private Date birthday; private char sex; private String 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 char getSex() { return sex; } public void setSex(char sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; }} |
---|
引用关系设置
public class User { private Integer id; private String username; private Date birthday; private char sex; private String address; Listroles =new ArrayList(); public ListgetRoles() { return roles; } public void setRoles(Listroles) { this.roles = roles; } 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; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", birthday=" + birthday + ", sex=" + sex + ", address='" + address + '\'' + ", roles=" + roles + '}'; } public char getSex() { return sex; } public void setSex(char sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; }} |
---|
xml version="1.0" encoding="UTF-8" ?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><< span="">mapper namespace="com.shop.mapper.UserMapper"> << span="">resultMap id="aaa" type="com.shop.domain.User"> << span="">id property="id" column="id">id> << span="">result property="username" column="username">result> << span="">result property="sex" column="sex">result> << span="">result property="birthday" column="birthday">result> << span="">result property="address" column="address">result> << span="">collection property="roles" ofType="com.shop.domain.Role"> << span="">id property="id" column="rid">id> << span="">result property="role_name" column="role_name">result> << span="">result property="role_desc" column="role_desc">result> collection> resultMap><< span="">select id="selectUserRoleList" resultMap="aaa"> 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.uidleft OUTER join role r on r.id = ur.ridselect>mapper> |
---|
@Testpublic void test1() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper userMapper = sqlSession.getMapper(UserMapper.class); Listlist = userMapper.selectUserRoleList(); for (User user : list) { System.out.println(user); System.out.println("------------"); System.out.println(user.getRoles()); }} |
---|
package com.shop.domain;import java.util.ArrayList;import java.util.List;public class Role { private Integer id; private String role_name; private String role_desc; private Listusers = new ArrayList(); public ListgetUsers() { return users; } public void setUsers(Listusers) { this.users = users; } @Override public String toString() { return "Role{" + "id=" + id + ", role_name='" + role_name + '\'' + ", role_desc='" + role_desc + '\'' + ", users=" + users + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getRole_name() { return role_name; } public void setRole_name(String role_name) { this.role_name = role_name; } public String getRole_desc() { return role_desc; } public void setRole_desc(String role_desc) { this.role_desc = role_desc; }} |
---|
xml version="1.0" encoding="UTF-8" ?> mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><< span="">mapper namespace="com.shop.mapper.RoleMapper"> << span="">resultMap id="aaa" type="com.shop.domain.Role"> << span="">id property="id" column="rid">id> << span="">result property="role_name" column="role_name">result> << span="">result property="role_desc" column="role_desc">result> << span="">collection property="users" ofType="com.shop.domain.User"> << span="">id property="id" column="id">id> << span="">result property="username" column="username">result> << span="">result property="birthday" column="birthday">result> << span="">result property="sex" column="sex">result> << span="">result property="address" column="address">result> collection> resultMap><< span="">select id="selectRoleUserList" resultMap="aaa">select u.*,r.id as rid,r.role_name,r.role_desc from role r left join user_role ur on r.ID = ur.rid left join user u on u.id = ur.uid;select>mapper> |
---|
@Testpublic void test2() throws IOException { String config = "mybatis-config.xml"; InputStream file = Resources.getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(file); SqlSession sqlSession = sqlSessionFactory.openSession(); RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); Listlist = roleMapper.selectRoleUserList(); for (Role role : list) { System.out.println(role); }} |
---|