创建好数据库
DROp TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ordertime` varchar(255) DEFAULT NULL,
`total` DOUBLE DEFAULT NULL,
`uid` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2022-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2022-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2022-12-12', '5000', '2');
-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`rolename` VARCHAr(255) DEFAULT NULL,
`roleDesc` VARCHAr(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_role
-- ----------------------------
INSERT INTO `sys_role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `sys_role` VALUES ('2', 'CEO', 'CEO');
-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
`userid` INT(11) NOT NULL,
`roleid` INT(11) NOT NULL,
PRIMARY KEY (`userid`,`roleid`),
KEY `roleid` (`roleid`),
CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`),
CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `sys_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
INSERT INTO `sys_user_role` VALUES ('1', '1');
INSERT INTO `sys_user_role` VALUES ('2', '1');
INSERT INTO `sys_user_role` VALUES ('1', '2');
INSERT INTO `sys_user_role` VALUES ('2', '2');
1.一对一
一对一查询模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
OrderMapper.java
public interface OrderMapper {
// 一对一: 查询所有订单 及 订单的用户信息
public List findAllOrderWithUser();
}
OrderMapper.xml
根据订单的外键uid 查询用户信息
property="user" : 实体类Order中的属性名
javaType="com.qiku.popo.User" : 属性的类型
column="uid" : 根据哪个字段 去查询用户信息
select="com.qiku.mapper.UserMapper.findUserById" : 将column指定的字段作为参数
一对一,多对一用association
UserMapper.xml
OrderMapperTest.java
public class OrderMapperTest {
@Test
public void findAllOrderWithUserTest() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List allOrderWithUser = mapper.findAllOrderWithUser();
for (Order o : allOrderWithUser){
System.out.println(o);
}
}
}
2.一对多
一对多查询模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单
UserMapper.java
// 查询所有用户 及其 订单信息
public List findAllUserWithOrder();
UserMapper.xml
+
一对多、多对多用collection
OrderMapper.java
// 根据用户id(uid)查询订单信息
public List findOrderByUid(Integer uid);
OrderMapper.xml
UserMapperTest.java
@Test
public void findAllUserWithOrderTest() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List allUserWithOrder = mapper.findAllUserWithOrder();
for (User u : allUserWithOrder){
System.out.println(u);
}
}
3.多对多
多对多查询的模型
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用
多对多查询的需求:查询用户同时查询出该用户的所有角色
UserMapper.java
// 查询所有用户及其角色信息
public List findAllUserWithRole();
UserMapper.xml
RoleMapper.java
public interface RoleMapper {
public List findRoleByUid(Integer userId);
}
RoleMapper.xml
UserMapperTest.java
@Test
public void findAllUserWithRoleTest() throws IOException {
InputStream resource = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resource);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List userList = mapper.findAllUserWithRole();
for (User u : userList){
System.out.println(u);
}
}



