创建好数据库
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<Order> findAllOrderWithUser();
}
OrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qiku.mapper.OrderMapper">
<!-- 配置映射规则-->
<resultMap id="orderMap" type="com.qiku.popo.Order">
<id column="id" property="id"/>
<result column="ordertime" property="orderTime"/>
<result column="total" property="money"/>
<!-- 根据订单的外键uid 查询用户信息
property="user" : 实体类Order中的属性名
javaType="com.qiku.popo.User" : 属性的类型
column="uid" : 根据哪个字段 去查询用户信息
select="com.qiku.mapper.UserMapper.findUserById" : 将column指定的字段作为参数
-->
<association property="user" javaType="com.qiku.popo.User" column="uid" select="com.qiku.mapper.UserMapper.findUserById">
</association>
</resultMap>
<select id="findAllOrderWithUser" resultType="com.qiku.popo.Order" resultMap="orderMap">
select * from orders
</select>
</mapper>
<association property="user" javaType="com.qiku.popo.User" column="uid" select="com.qiku.mapper.UserMapper.findUserById"> </association> 根据订单的外键uid 查询用户信息 property="user" : 实体类Order中的属性名 javaType="com.qiku.popo.User" : 属性的类型 column="uid" : 根据哪个字段 去查询用户信息 select="com.qiku.mapper.UserMapper.findUserById" : 将column指定的字段作为参数 一对一,多对一用association UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qiku.mapper.UserMapper">
<select id="findUserById" parameterType="int" resultType="com.qiku.popo.User">
select * from user where id = #{id}
</select>
</mapper>
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<Order> allOrderWithUser = mapper.findAllOrderWithUser();
for (Order o : allOrderWithUser){
System.out.println(o);
}
}
}
2.一对多
一对多查询模型
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
一对多查询的需求:查询一个用户,与此同时查询出该用户具有的订单
UserMapper.java
// 查询所有用户 及其 订单信息
public List<User> findAllUserWithOrder();
UserMapper.xml
<resultMap id="userResultMap" type="com.qiku.popo.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 给用户的订单属性赋值 一对多
fetchType="lazy":懒加载
-->
<collection property="orderList" ofType="com.qiku.popo.Order" column="id" select="com.qiku.mapper.OrderMapper.findOrderByUid" >
</collection>
</resultMap>
<select id="findAllUserWithOrder" resultType="com.qiku.popo.User">
select * from user
</select>
+
一对多、多对多用collection
OrderMapper.java
// 根据用户id(uid)查询订单信息
public List<Order> findOrderByUid(Integer uid);
OrderMapper.xml
<resultMap id="findOrderByUidResultMap" type="com.qiku.popo.Order">
<id column="id" property="id"/>
<result column="ordertime" property="orderTime"/>
<result column="total" property="money"/>
</resultMap>
<select id="findOrderByUid" resultType="com.qiku.popo.Order" parameterType="int" resultMap="findOrderByUidResultMap">
select * from orders where uid =#{uid}
</select>
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<User> allUserWithOrder = mapper.findAllUserWithOrder();
for (User u : allUserWithOrder){
System.out.println(u);
}
}
3.多对多
多对多查询的模型
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用
多对多查询的需求:查询用户同时查询出该用户的所有角色
UserMapper.java
// 查询所有用户及其角色信息
public List<User> findAllUserWithRole();
UserMapper.xml
<resultMap id="findAllUserWithRoleResultMap" type="com.qiku.popo.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 给用户的角色属性赋值 一对多-->
<collection property="roleList" ofType="com.qiku.popo.Role" column="id" select="com.qiku.mapper.RoleMapper.findRoleByUid">
</collection>
</resultMap>
<!-- 查询所有用户及其角色-->
<select id="findAllUserWithRole" resultType="com.qiku.popo.User" resultMap="findAllUserWithRoleResultMap">
select * from user
</select>
RoleMapper.java
public interface RoleMapper {
public List<Role> findRoleByUid(Integer userId);
}
RoleMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qiku.mapper.RoleMapper">
<resultMap id="findRoleByUidResultMap" type="com.qiku.popo.Role">
<id column="id" property="id"/>
<result column="rolename" property="roleNmae"/>
<result column="roledesc" property="roleDesc"/>
</resultMap>
<select id="findRoleByUid" resultType="com.qiku.popo.Role" resultMap="findRoleByUidResultMap">
select * from sys_role r
join sys_user_role ur
on r.id = ur.roleid
WHERE ur.userid = #{uid}
</select>
</mapper>
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<User> userList = mapper.findAllUserWithRole();
for (User u : userList){
System.out.println(u);
}
}