合肥移动网站建设,长沙智能建站方案,wordpress指定目录文章,html书店网站建设目录#xff1a; 一 、实验目的#xff1a;
熟练掌握实体之间的各种映射关系。
二 、预习要求#xff1a;
预习数据库原理中所讲过的一对一、一对多和多对多关系
三、实验内容#xff1a; 1. 查询所有订单信息#xff0c;关联查询下单用户信息(注意#xff1a;因为一…目录 一 、实验目的
熟练掌握实体之间的各种映射关系。
二 、预习要求
预习数据库原理中所讲过的一对一、一对多和多对多关系
三、实验内容 1. 查询所有订单信息关联查询下单用户信息(注意因为一个订单信息只会是一个人 下的订单所以从查询订单信息出发关联查询用户信息为一对一查询。 如果从用户信息 出发查询用户下的订单信息则为一对多查询因为一个用户可以下多个订单。) 2. 查询所有用户信息及用户关联的订单信息(用户信息和订单信息为一对多关系)。 四、实验方法和步骤
实验前准备
创建数据库user表order表
CREATE DATABASE experience03;
USE experience03;DROP TABLE IF EXISTS user;
CREATE TABLE user (id int(0) NOT NULL AUTO_INCREMENT,username varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,birthday date NULL DEFAULT NULL,sex char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,address varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,PRIMARY KEY (id) USING BTREE
) ;INSERT INTO user VALUES (1, 关羽, 2024-09-29, 男, 蜀国);
INSERT INTO user VALUES (2, 梁王孙, 2024-09-11, 男, 北京市);
INSERT INTO user VALUES (3, 陈长生, 2024-05-14, 女, 京都西庙);
INSERT INTO user VALUES (4, 王婆, 2024-09-05, 女, 快远);
INSERT INTO user VALUES (5, 黄忠, 2016-07-24, 1, 三国);
INSERT INTO user VALUES (6, 张飞, 2024-10-16, 女, 三国);
INSERT INTO user VALUES (7, lucky, 2021-10-08, 男, 三国);
INSERT INTO user VALUES (14, 关羽, 2024-12-02, 男, 蜀国); 创建User
package com.hasut.pojo;import java.util.Date;public class User {private int id;private String username;private Date birthday;private String sex;private String address;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 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;}Overridepublic String toString() {return User{ id id , username username \ , birthday birthday , sex sex \ , address address \ };}
}创建Order
package com.hasut.pojo;import java.util.Date;public class Order {private int id;private String number;private Date createtime;private String note;private User user;public int getId() {return id;}public void setId(int id) {this.id id;}public String getNumber() {return number;}public void setNumber(String number) {this.number number;}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;}public User getUser() {return user;}public void setUser(User user) {this.user user;}Overridepublic String toString() {return Order{ id id , number number \ , createtime createtime , note note \ , user user };}
}1. 查询所有订单信息从订单出发一对一 关联查询下单用户信息(注意因为一个订单信息只会是一个人 下的订单所以从查询订单信息出发关联查询用户信息为一对一查询。 如果从用户信息 出发查询用户下的订单信息则为一对多查询因为一个用户可以下多个订单。) sql 语句
SELECT o.id, o.user_id userId, o.number, o.createtime, o.note, u.username, u.address FROM order o LEFT JOIN user u ON o.user_id u.id
或者
SELECT o.id, o.number,o.createtime,o.note,o.userId,u.username,u.birthday,u.sex,u.address
FROM user u, order o
WHERE u.id o.userId
使用 resultMap使用 resultMap定义专门的 resultMap 用于映射一对一查询结 果。
①改造 pojo 类 在 Order 类中加入 User 属性user 属性中用于存储关联查询的用户信息因为订单关 联查询用户是一对一关系所以这里使用单个 User 对象存储关联查询的用户信息。改造
Order 如下图
package com.hasut.pojo;import java.util.Date;public class Order {private int id;private String number;private Date createtime;private String note;private User user;public int getId() {return id;}public void setId(int id) {this.id id;}public String getNumber() {return number;}public void setNumber(String number) {this.number number;}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;}public User getUser() {return user;}public void setUser(User user) {this.user user;}Overridepublic String toString() {return Order{ id id , number number \ , createtime createtime , note note \ , user user };}
}②Mapper.xml 这里 resultMap 指定 orderUserResultMap如下
resultMap idfindOrderWithUserResultMap typecom.haust.pojo.Ordersid propertyid columnid/result propertynumber columnnumber/result propertycreatetime columncreatetime/result propertynote columnnote/association propertyuser javaTypeUserid propertyid columnid/result propertyusername columnusername/result propertybirthday columnbirthday/result propertysex columnsex/result propertyaddress columnaddress//association/resultMapselect idfindOrderAndUserById parameterTypeint resultMapfindOrderWithUserResultMapSELECT o.id, o.number,o.createtime,o.note,o.userId,u.username,u.birthday,u.sex,u.addressFROM user u, order oWHERE u.id o.userId/select
③Mapper 接口
编写 UserMapper 如下图
ListOrders findOrderAndUserById(int id);
④测试方法
Testpublic void test09() throws IOException {String resources mybatis-config.xml;InputStream inputStream Resources.getResourceAsStream(resources);SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession sqlSessionFactory.openSession();OrdersMapper ordersMapper sqlSession.getMapper(OrdersMapper.class);ListOrders list ordersMapper.findOrderAndUserById(1);for (Orders o : list){System.out.println(list);}sqlSession.commit();sqlSession.close();}
⑤实验效果
测试效果如下图 2. 查询所有用户信息及用户关联的订单信息(从用户出发为一对多关系)。
用户信息和订单信息为一对多关系
sql 语句
SELECT u.id, u.username, u.birthday, u.sex, u.address, o.id oid, o.number, o.createtime, o.note FROM user u LEFT JOIN order o ON u.id o.user_id
SELECT u.id,u.username,u.birthday,u.sex,u.address,o.id oid,o.number,o.createtime,o.note
FROM user u, order o
WHERE u.id o.userId
①修改 pojo 类
在 User 类中加入 List orders 属性,如下图
package com.haust.pojo;import java.util.Date;
import java.util.List;public class User {private int id;private String username;private Date birthday;private String sex;private String address;private ListOrders ordersList;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 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;}public ListOrders getOrdersList() {return ordersList;}public void setOrdersList(ListOrders ordersList) {this.ordersList ordersList;}Overridepublic String toString() {return User{ id id , username username \ , birthday birthday , sex sex \ , address address \ , ordersList ordersList };}
}②UserMapper.xml
在 UserMapper.xml 添加 sql如下
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.haust.mapper.UserMapperresultMap idfindOrderWithUserResultMap2 typecom.haust.pojo.Userid propertyid columnid/result propertyusername columnusername/result propertybirthday columnbirthday/result propertysex columnsex/result propertyaddress columnaddress/collection propertyordersList ofTypecom.haust.pojo.Ordersid propertyid columnid/result propertynumber columnnumber/result propertycreatetime columncreatetime/result propertynote columnnote//collection/resultMapselect idfindOrderAndUserById2 parameterTypeint resultMapfindOrderWithUserResultMap2SELECT u.id,u.username,u.birthday,u.sex,u.address,o.id oid,o.number,o.createtime,o.noteFROM user u, order oWHERE u.id o.userIdand u.id #{id}/select
/mapper
③Mapper 接口
编写 UserMapper 接口如下图
public interface UserMapper {ListUser findOrderAndUserById2(int id);
}
④测试方法
在 UserMapperTest 增加测试方法如下
Testpublic void test10() throws IOException {String resources mybatis-config.xml;InputStream inputStream Resources.getResourceAsStream(resources);SqlSessionFactory sqlSessionFactory new SqlSessionFactoryBuilder().build(inputStream);SqlSession sqlSession sqlSessionFactory.openSession();UserMapper userMapper sqlSession.getMapper(UserMapper.class);ListUser list userMapper.findOrderAndUserById2(2);for (User o : list){System.out.println(o);}sqlSession.commit();sqlSession.close();}
⑤效果
测试效果如下图
