栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

MyBatis练习3

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

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 findAllOrderWithUser();

}

OrderMapper.xml





    
    
        
        
        


        

        
    

    
        select * from  user where id = #{id}
    

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


        
        
        
        
        
        
        

        
    

    
        select * from orders where uid =#{uid}
    

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


        
        
        
        
        
        
        

        
    
    
    
        select * from sys_role r
                          join sys_user_role ur
                               on r.id = ur.roleid
        WHERe ur.userid = #{uid}
    


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

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/985310.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号