SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for tb_item-- ----------------------------DROPTABLEIFEXISTS`tb_item`;CREATETABLE`tb_item`(`id`int(11)NOTNULLAUTO_INCREMENT,`item_name`varchar(32)NOTNULLCOMMENT'商品名称',`item_price`float(6,1)NOTNULLCOMMENT'商品价格',`item_detail`textCOMMENT'商品描述',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=utf8;-- ------------------------------ Records of tb_item-- ----------------------------INSERTINTO`tb_item`VALUES('1','iPhone 6','5288.0','苹果公司新发布的手机产品。');INSERTINTO`tb_item`VALUES('2','iPhone 6 plus','6288.0','苹果公司发布的新大屏手机。');-- ------------------------------ Table structure for tb_order-- ----------------------------DROPTABLEIFEXISTS`tb_order`;CREATETABLE`tb_order`(`id`int(11)NOTNULLAUTO_INCREMENT,`user_id`bigint(20)NOTNULL,`order_number`varchar(20)NOTNULLCOMMENT'订单号',PRIMARYKEY(`id`),KEY`FK_orders_1`(`user_id`),CONSTRAINT`FK_orders_1`FOREIGNKEY(`user_id`)REFERENCES`tb_user`(`id`)ONDELETENOACTIONONUPDATENOACTION)ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8;-- ------------------------------ Records of tb_order-- ----------------------------INSERTINTO`tb_order`VALUES('1','1','20140921001');INSERTINTO`tb_order`VALUES('2','2','20140921002');INSERTINTO`tb_order`VALUES('3','1','20140921003');-- ------------------------------ Table structure for tb_orderdetail-- ----------------------------DROPTABLEIFEXISTS`tb_orderdetail`;CREATETABLE`tb_orderdetail`(`id`int(11)NOTNULLAUTO_INCREMENT,`order_id`int(32)DEFAULTNULLCOMMENT'订单号',`item_id`int(32)DEFAULTNULLCOMMENT'商品id',`total_price`double(20,0)DEFAULTNULLCOMMENT'商品总价',`status`int(11)DEFAULTNULLCOMMENT'状态',PRIMARYKEY(`id`),KEY`FK_orderdetail_1`(`order_id`),KEY`FK_orderdetail_2`(`item_id`),CONSTRAINT`FK_orderdetail_1`FOREIGNKEY(`order_id`)REFERENCES`tb_order`(`id`),CONSTRAINT`FK_orderdetail_2`FOREIGNKEY(`item_id`)REFERENCES`tb_item`(`id`))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8;-- ------------------------------ Records of tb_orderdetail-- ----------------------------INSERTINTO`tb_orderdetail`VALUES('1','1','1','5288','1');INSERTINTO`tb_orderdetail`VALUES('2','1','2','6288','1');INSERTINTO`tb_orderdetail`VALUES('3','2','2','6288','1');INSERTINTO`tb_orderdetail`VALUES('4','3','1','5288','1');-- ------------------------------ Table structure for tb_user-- ----------------------------DROPTABLEIFEXISTS`tb_user`;CREATETABLE`tb_user`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`user_name`varchar(100)DEFAULTNULLCOMMENT'用户名',`password`varchar(100)DEFAULTNULLCOMMENT'密码',`name`varchar(100)DEFAULTNULLCOMMENT'姓名',`age`int(10)DEFAULTNULLCOMMENT'年龄',`sex`int(11)DEFAULTNULLCOMMENT'0-女 1-男',PRIMARYKEY(`id`),UNIQUEKEY`username`(`user_name`))ENGINE=InnoDBAUTO_INCREMENT=11DEFAULTCHARSET=utf8;-- ------------------------------ Records of tb_user-- ----------------------------INSERTINTO`tb_user`VALUES('1','zhangsan','123456','张三','30','1');INSERTINTO`tb_user`VALUES('2','lisi','123456','李四','21','0');INSERTINTO`tb_user`VALUES('3','wangwu','123456','王五','22','1');INSERTINTO`tb_user`VALUES('4','zhangwei','123456','张伟','20','1');INSERTINTO`tb_user`VALUES('5','lina','123456','李娜','28','0');INSERTINTO`tb_user`VALUES('6','蔡徐坤','123','小菜','18','1');
package com.it.test;import com.it.dao.UserDao;import com.it.pojo.User;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.BeforeClass;import org.junit.Test;import java.io.IOException;import java.io.InputStream;publicclassTestAll{privatestatic UserDao userDao;@BeforeClasspublicstaticvoidinit(){//1.加载核心配置文件,构建会话工厂
InputStream in = null;try{
in = Resources.getResourceAsStream("mybatis-config.xml");}catch(IOException e){
e.printStackTrace();}
SqlSessionFactory sessionFactory =newSqlSessionFactoryBuilder().build(in);//获取会话对象
SqlSession session = sessionFactory.openSession();//获取代理对象
userDao = session.getMapper(UserDao.class);}@Testpublicvoidtest1(){
User user = userDao.findById(1l);
System.out.println(user.toString());}}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
运行结果如下则说明搭建成功:
二、一对一查询
需求:通过订单编号20140921003查询出
订单信息
,并查询出
下单人信息
。
说明:一个订单编号对应一个订单,一个订单只能属于一个人。所以上述需求实现是一对一的实现
1. 需求分析
步骤:
(1)书写查询的sql
(2)订单实体添加属性映射
(3)添加order接口及方法
(4)创建order映射文件,编写SQL
(5)测试
2. 代码实现
(1)书写查询的sql
代码如下:
-- 分步查询-- 1.查询订单信息select*from tb_order where order_number='20140921003';-- user_id=1-- 2.根据用户ID查询用户信息select*from tb_user where id=1;-- 一步查询select*from tb_order as tor,tb_user as tus where tor.user_id=tus.id
and tor.order_number='20140921003';
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
(2)订单实体添加属性映射
在Order类中添加属性:
private User tuser;private List<Orderdetail> orderdetails;
1
2
1
2
(3)添加Order接口及方法:
package com.it.dao;import com.it.pojo.Order;import org.apache.ibatis.annotations.Param;publicinterfaceOrderDao{//根据订单编号查询订单信息,包含用户信息
Order findByOrderNumber(@Param("orderNumber") String orderNumber);}
1
2
3
4
5
6
7
8
9
10
1
2
3
4
5
6
7
8
9
10
(4)创建order映射文件,编写SQL
<?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:命名空间,全局唯一--><mapper namespace="com.it.dao.OrderDao"><resultMap id="orderMap"type="order" autoMapping="true"><id column="order_id" property="id"/><result column="order_number" property="orderNumber"/><!--说明:
association用于一对一映射
property="tuser":表示关联订单类中的User变量
javaType="user":表示关联的变量类型
--><association property="tuser" javaType="user" autoMapping="true"><id column="id" property="id"/><result column="user_name" property="userName"/></association></resultMap><select id="findByOrderNumber" resultMap="orderMap">select tus.*,tor.id as order_id,tor.order_number
from tb_order as tor,
tb_user as tus
where tor.user_id = tus.id
and tor.order_number =#{orderNumber}</select></mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
注意此时的核心配置文件:
(5)测试
代码如下:
package com.it.test;import com.it.dao.OrderDao;import com.it.dao.UserDao;import com.it.pojo.Order;import com.it.pojo.User;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.junit.BeforeClass;import org.junit.Test;import java.io.IOException;import java.io.InputStream;publicclassTestAll{privatestatic UserDao userDao;privatestatic OrderDao orderDao;@BeforeClasspublicstaticvoidinit(){//1.加载核心配置文件,构建会话工厂
InputStream in = null;try{
in = Resources.getResourceAsStream("mybatis-config.xml");}catch(IOException e){
e.printStackTrace();}
SqlSessionFactory sessionFactory =newSqlSessionFactoryBuilder().build(in);//获取会话对象
SqlSession session = sessionFactory.openSession();//获取代理对象
userDao = session.getMapper(UserDao.class);
orderDao=session.getMapper(OrderDao.class);}@Testpublicvoidtest1(){
User user = userDao.findById(1l);
System.out.println(user.toString());}@Testpublicvoidtest2(){
Order order = orderDao.findByOrderNumber("20140921003");
System.out.println(order);}}
-- 1.分步查询-- 1.1 查询用户信息select*from tb_user where id=1;-- 1.2 根据用户id查询订单集合select*from tb_order where user_id=1;-- 2.一次性获取select
tus.*,
tor.id as order_id,
tor.order_number
from tb_user as tus,
tb_order as tor
where tus.id = tor.user_id
and tus.id =1;
<resultMapid="userOrderMap"type="user"autoMapping="true"><idcolumn="id"property="id"/><resultcolumn="user_name"property="userName"/><collectionproperty="orders"javaType="list"ofType="order"autoMapping="true"><idcolumn="order_id"property="id"/><resultcolumn="order_number"property="orderNumber"/></collection></resultMap><selectid="findUserAndOrderById"resultMap="userOrderMap">
select
tus.*,
tor.id as order_id,
tor.order_number
from tb_user as tus,
tb_order as tor
where tus.id = tor.user_id
and tus.id = #{id}
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
5、测试
@Testpublicvoidtest3(){
User user = userDao.findUserAndOrderById(1l);
System.out.println(user);}