MyBatis练习3

kerodaisuki

已于 2022-06-22 20:43:09 修改

阅读量184

点赞数

文章标签： mybatis java 数据库

于 2022-06-22 20:39:47 首次发布

本文链接：https://blog.csdn.net/chaochaodayizhi/article/details/125415822

版权

创建好数据库

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