对1关联查询
一种业务需要,既要显示订单表的信息同时还需要下订单的人的信息,这就是多表信息,由于一条订单只会对应一个用户,这是一对一关联查询
第二种1.将原来体类中增加一个新的属性
(1)在实体类中增加对应另外一张表的实体类属性getset,toString
(2)输出映射给为resultMap手动去映射对应的属性
(3)Association(一对一关联)property原来orders中的user属性,JavaType user剩下的要手动映射
只要不手动映射,它都不会帮你获得数据库中的值,即使名字一样
如果名字重复了,它自己会自动增加一个1,但是不能映射。你没有起名字它是不会帮你映射的
新建一个实体类
业务的需要往往那个不需要订单人的全部信息只需要要部分信息,也可能不需要订单的全部信息。根据业务建立一个新的实体类,在这个实体类中查询处的所有数据要和数据库中的数据保持一致
<!--orderView--> <select id="selectView" resultType="cn.hd.query.OrderView"> SELECT oders.id, oders.orderId, oders.eatTime, oders.orderTime, mbs.`name`, mbs.address, mbs.sex FROM mbs LEFT JOIN oders ON mbs.id = oders.id
</select> |
public class OrderView{ private String sex; private String name; private String address; private Integer id; private Integer orderId; private String orderTime; private String eatTime;
public OrderView() { }
public OrderView(String sex, String name, String address, Integer id, Integer orderId, String orderTime, String eatTime) { this.sex = sex; this.name = name; this.address = address; this.id = id; this.orderId = orderId; this.orderTime = orderTime; this.eatTime = eatTime; }
public String getSex() { return sex; }
public void setSex(String sex) { this.sex = sex; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public String getAddress() { return address; }
public void setAddress(String address) { this.address = address; }
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public Integer getOrderId() { return orderId; }
public void setOrderId(Integer orderId) { this.orderId = orderId; }
public String getOrderTime() { return orderTime; }
public void setOrderTime(String orderTime) { this.orderTime = orderTime; }
public String getEatTime() { return eatTime; }
public void setEatTime(String eatTime) { this.eatTime = eatTime; }
@Override public String toString() { return "OrderView{" + "sex='" + sex + '\'' + ", name='" + name + '\'' + ", address='" + address + '\'' + ", id=" + id + ", orderId=" + orderId + ", orderTime='" + orderTime + '\'' + ", eatTime='" + eatTime + '\'' + '}'; } }
|
List<OrderView>selectView();
|
//orderView查询 @Test public void fun8() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("cn/hd/query/sqlMapConfig.xml"); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = build.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<OrderView> orderViews = mapper.selectView(); sqlSession.commit(); sqlSession.close(); System.out.println(orderViews); //System.out.println(userByIds1); } |
一对多关联查询
结果映射中的resultType不好用
建议使用resultMap
<!--orderView--> <resultMap id="orderView" type="cn.hd.query.Orders"> <id property="id" column="orderId"></id> <result property="orderTime" column="orderTime"></result> <result property="eatTime" column="eatTime"></result> <association property="user" javaType="cn.hd.query.User"> <id property="id" column="id1"></id> <result property="name" column="name"></result> <result property="address" column="address"></result> </association> </resultMap> <select id="selectView1" resultMap="orderView"> SELECT oders.id, oders.orderId, oders.eatTime, oders.orderTime, mbs.`name`, mbs.address, mbs.sex FROM mbs LEFT JOIN oders ON mbs.id = oders.id
</select>
</select>
collection对应是一对多关系,property要和user中的集合保持一致,JavaType对应的集合的类型ofType集合中的元素类型 注意事项和一对一查询一致 |
public class User { // private Integer stuId; private Integer id; private String name; private String sex; private String address; private Integer balance; private List<Orders> orders;
public List<Orders> getOrders() { return orders; }
public void setOrders(List<Orders> orders) { this.orders = orders; }
public User() { }
public User(Integer id, String name, String sex, String address, Integer balance) { this.id = id; this.name = name; this.sex = sex; this.address = address; this.balance = balance; }
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
/* public User(Integer stuId, String name, String sex, String address, Integer balance) { this.stuId = stuId; this.name = name; this.sex = sex; this.address = address; this.balance = balance; } public Integer getStuId() { return stuId; }
public void setStuId(Integer stuId) { this.stuId = stuId; }*/
public String getName() { return name; }
public void setName(String name) { this.name = name; }
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; }
public Integer getBalance() { return balance; }
public void setBalance(Integer balance) { this.balance = balance; }
@Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", address='" + address + '\'' + ", balance=" + balance + ", orders=" + orders + '}'; }
/*@Override public String toString() { return "User{" + "stuId=" + stuId + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", address='" + address + '\'' + ", balance=" + balance + '}'; }*/ }
|
public class Orders { private Integer id; private Integer orderId; private String orderTime; private String eatTime; private User user;
public User getUser() { return user; }
public void setUser(User user) { this.user = user; }
public Orders(){
} public Orders(Integer id, Integer orderId, String orderTime, String eatTime, User user) { this.id = id; this.orderId = orderId; this.orderTime = orderTime; this.eatTime = eatTime; this.user = user; }
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public Integer getOrderId() { return orderId; }
public void setOrderId(Integer orderId) { this.orderId = orderId; }
public String getOrderTime() { return orderTime; }
public void setOrderTime(String orderTime) { this.orderTime = orderTime; }
public String getEatTime() { return eatTime; }
public void setEatTime(String eatTime) { this.eatTime = eatTime; }
@Override public String toString() { return "Orders{" + "id=" + id + ", orderId=" + orderId + ", orderTime='" + orderTime + '\'' + ", eatTime='" + eatTime + '\'' + ", user=" + user + '}'; } }
|
//selectView1查询 @Test public void fun9() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("cn/hd/query/sqlMapConfig.xml"); SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = build.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<Orders> orders = mapper.selectView1(); sqlSession.commit(); sqlSession.close(); System.out.println(orders); //System.out.println(userByIds1); } |
List<Orders>selectView1();
|