MyBatis练习3

创建好数据库


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);
        }
    }

相关阅读:
Go-Excelize API源码阅读（二十）——SetDefinedName
K8S基础服务（apiserver、controller、scheduler、etcd）时区设置
 XML概念
 Redis持久化之RDB
机器学习---CNN（创建和训练一个卷积神经网络并评估其性能）下
 大厂秋招真题【DP】米哈游20230924秋招T2-米小游与魔法少女-奇运
 5G将如何改变我们的生活和工作方式？
数据库批处理
 新一代网络请求库：python-httpx库
 Dubbo入门案例
原文地址：https://blog.csdn.net/chaochaodayizhi/article/details/125415822