Java学习路线分享mybatis映射,希望对大家有所帮助。 Mybatis 1对1关联 实现方式 1. 通过resultType方式 2. 通过级联属性的方式【resultType 和 resultMap方式】 3. 通过association关联的方式 4. 通过association的分步查询方式 5. 通过包装类的双association的关联方式 6. 通过association的嵌套定义方式 案例: 查询 订单 对应的用户信息 Sql建表语句用户表: -- ---------------------------- -- Table structure for `user` -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL COMMENT '用户名称', `birthday` date DEFAULT NULL COMMENT '生日', `sex` char(1) DEFAULT NULL COMMENT '性别', `address` varchar(256) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES ('1', '王五', '2019-08-30', '2', '杭州'); INSERT INTO `user` VALUES ('10', '张三', '2014-07-10', '1', '北京市'); INSERT INTO `user` VALUES ('16', '张小明', '2019-08-15', '1', '河南郑州'); INSERT INTO `user` VALUES ('22', '陈小明', '2019-08-08', '1', '河南郑州'); INSERT INTO `user` VALUES ('24', '张三丰', '2019-08-15', '1', '长沙'); INSERT INTO `user` VALUES ('25', '吴小明', '2019-08-08', '1', '河南郑州'); INSERT INTO `user` VALUES ('26', '王五', '2019-08-14', '2', '武汉'); |
订单表: -- ---------------------------- -- Table structure for `orders` -- ---------------------------- DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '下单用户id', `number` varchar(32) NOT NULL COMMENT '订单号', `createtime` datetime NOT NULL COMMENT '创建订单时间', `note` varchar(100) DEFAULT NULL COMMENT '备注', PRIMARY KEY (`id`), KEY `FK_orders_1` (`user_id`), CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of orders -- ---------------------------- INSERT INTO `orders` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null); INSERT INTO `orders` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null); INSERT INTO `orders` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null); INSERT INTO `orders` VALUES ('6', '10', '1000013', '2015-08-30 10:11:56', null); INSERT INTO `orders` VALUES ('7', '16', '100014', '2019-08-16 10:12:54', null); |
实体类: 用户:User public class User { private Integer id; private String name; private Date birthday; private String sex; private String address; //getter.. setter.. toString.. } |
订单:Orders public class Orders { private Integer id; private Integer userId; private String number; private Date createtime; private String note; //getter.. setter.. toString.. } |
方式一:resultType方式一个订单 对应 一个用户 对订单实体类进行扩展: 【只增加 对应的字段 ;例如案例中的 用户名称】 新建OrdersExtByColumn 的扩展类,继承 Orders, 并增加相应的字段 public class OrdersExtByColumn extends Orders { private String username; //getter.. setter.. toString.. } |
OrdersMapper映射文件中,写对应的sql语句 <select id="queryOrdersByType" parameterType="int" resultType="OrdersExtByColumn"> SELECT o.id,number,createtime,note,user_id userId,username FROM orders o ,user u WHERE o.id = u.id and o.id = #{id} </select> |
编写测试程序: public class TestAssociation { private SqlSession sqlSession; @Before public void initSqlSession(){ //1.读取核心配置文件 SqlMapConfig.xml InputStream resourceAsStream = TestMain.class.getClassLoader().getResourceAsStream("SqlMapConfig.xml"); //2. 产生 SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); //3. 产生 SqlSession sqlSession = sqlSessionFactory.openSession(); }
@Test public void testQryName(){ //找对应的sqlID String sqlID = "orders.queryOrdersByType"; //sqlSession 执行对应的数据库操作 OrdersExtByColumn orderInfo = sqlSession.selectOne(sqlID, 3); //对查询的结果进行处理 System.out.println(orderInfo); }
@After public void closeResource(){ sqlSession.close(); }
} |
方式二 : 级联属性的方式在orders类中, 增加 级联属性User user;
ResultType方式:
ResultMap方式:
方式三:association关联的方式
方式四:association的分步查询方式 分步查询:即多个sql查询 算出结果 此种方式,可以结合mybatis核心配置文件mybatisCfg.xml的配置项,产生延迟缓存;
mybatisCfg.xml 延迟加载相关设置项
方式五:包装类的双association的关联方式新建包装类: OrdersAndUser public class OrdersAndUser { private Orders orders; private User user ; //getter.. setter.. } |
方式六:内嵌association级联这种内嵌association会关联三张表; 需求简单变更为: 从某订单详情中,查询订单及对应 用户信息 追加一张订单详情表 -- ---------------------------- -- Table structure for `orderdetail` -- ---------------------------- DROP TABLE IF EXISTS `orderdetail`; CREATE TABLE `orderdetail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `orders_id` int(11) NOT NULL COMMENT '订单id', `items_id` int(11) NOT NULL COMMENT '商品id', `items_num` int(11) DEFAULT NULL COMMENT '商品购买数量', PRIMARY KEY (`id`), KEY `FK_orderdetail_1` (`orders_id`), KEY `FK_orderdetail_2` (`items_id`), CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of orderdetail -- ---------------------------- INSERT INTO `orderdetail` VALUES ('1', '3', '1', '1'); INSERT INTO `orderdetail` VALUES ('2', '3', '2', '3'); INSERT INTO `orderdetail` VALUES ('3', '4', '3', '4'); INSERT INTO `orderdetail` VALUES ('4', '4', '2', '3'); |
新增OrderDeatil类 【关联Orders属性】 public class OrderDetail { private Integer id; private Integer orderId; private Integer itemsId; private Integer itemsNum; private Orders orders; //setter.. getter.. } |
|