一、一对一关系
两张表:
-- 公民表
-- 身份证表
-- 表间关系:一对一
-- 一个公民只有一个身份证
-- 一个身份证只属于一个公民
-- 身份证表
DROP TABLE IF EXISTS t_card;
CREATE TABLE t_card(
cid INT PRIMARY KEY auto_increment,
cnum VARCHAR(20)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO t_card VALUES(NULL, '123456-1');
INSERT INTO t_card VALUES(NULL, '123456-2');
INSERT INTO t_card VALUES(NULL, '123456-3');
-- 公民表
DROP TABLE IF EXISTS t_person;
CREATE TABLE t_person(
pid INT PRIMARY KEY auto_increment,
name VARCHAR(10),
sex VARCHAR(1),
age INT,
birthday date,
cid INT,
FOREIGN KEY t_person(cid) REFERENCES t_card(cid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO t_person VALUES(NULL, 'Person1', '男', 11, '1997-6-1', 1);
INSERT INTO t_person VALUES(NULL, 'Person2', '女', 12, '1997-6-2', 2);
INSERT INTO t_person VALUES(NULL, 'Person3', '女', 13, '1997-6-3', 3);
实体类:
公民:Person
package com.seven.entity;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class Person implements Serializable {
private Integer pid;
private String name;
private String sex;
private Integer age;
private Date birthday;
private Integer cid;
// 一对一
private Card card;
@Override
public String toString() {
return "Person{" +
"pid=" + pid +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", age=" + age +
", birthday=" + birthday +
", cid=" + cid +
", card=" + card +
'}';
}
public Card getCard() {
return card;
}
public void setCard(Card card) {
this.card = card;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
}
身份证:Card
package com.seven.entity;
import java.io.Serializable;
public class Card implements Serializable {
private Integer cid;
private String cnum;
@Override
public String toString() {
return "Card{" +
"cid=" + cid +
", cnum='" + cnum + '\'' +
'}';
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCnum() {
return cnum;
}
public void setCnum(String cnum) {
this.cnum = cnum;
}
}
映射文件
PersonMapper.xml文件:
<resultMap id="personMap" type="com.seven.entity.Person">
<id property="pid" column="pid"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<result property="birthday" column="birthday"/>
<result property="cid" column="cid"/>
</resultMap>
<!-- 这个resultMap继承了上面的id为personMap的resultMap -->
<resultMap id="pcMap" type="com.seven.entity.Person" extends="personMap">
<!-- 配置一对一 -->
<association property="card" column="cid" javaType="com.seven.entity.Card">
<id property="cid" column="cid"/>
<result property="cnum" column="cnum"/>
</association>
</resultMap>
<select id="findDetailed" resultMap="pcMap">
select p.*, c.cnum from t_person p, t_card c where p.cid=c.cid
</select>
映射接口
PersonMapper接口:
package com.seven.mapper;
import com.seven.entity.Person;
import java.util.List;
public interface PersonMapper {
List<Person> findAll();
/**
* 演示一对一关系
* @return
*/
List<Person> findDetailed();
}
测试类
TestPerson测试类:
import com.seven.entity.Person;
import com.seven.mapper.PersonMapper;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestPerson {
private InputStream in;
private SqlSession sqlSession;
private PersonMapper mapper;
@Before
public void init() {
try {
in = Resources.getResourceAsStream("mybatis.config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
mapper = sqlSession.getMapper(PersonMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@After
public void close() throws IOException {
sqlSession.close();
in.close();
}
@Test
public void testFindAll() {
List<Person> all = mapper.findAll();
for (Person person : all) {
System.out.println(person);
}
}
/**
* 测试一对一
*/
@Test
public void testFindDetailed() {
List<Person> persons = mapper.findDetailed();
for (Person person : persons) {
System.out.println(person);
}
}
}
结果
二、一对多关系
两张表
-- 公民表:在一对一中创建
-- 订单表
-- 表间关系:一对多
-- 一个公民可以有多个订单
-- 一个订单只能属于一个公民
-- 订单表
DROP TABLE IF EXISTS t_order;
CREATE TABLE t_order(
oid INT PRIMARY KEY auto_increment,
pid INT,
odate TIMESTAMP,
price DOUBLE,
FOREIGN KEY t_order(pid) REFERENCES t_person(pid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
DELETE FROM t_order;
INSERT INTO t_order VALUES(NULL, 1, NOW(), 11.5);
INSERT INTO t_order VALUES(NULL, 1, NOW(), 12.5);
INSERT INTO t_order VALUES(NULL, 1, NOW(), 13.5);
INSERT INTO t_order VALUES(NULL, 2, NOW(), 24.5);
INSERT INTO t_order VALUES(NULL, 2, NOW(), 25.5);
实体类
订单:Order
package com.seven.entity;
import java.io.Serializable;
import java.util.Date;
public class Order implements Serializable {
private Integer oid;
private Integer pid;
private Date odate;
private Double price;
public Integer getOid() {
return oid;
}
public void setOid(Integer oid) {
this.oid = oid;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public Date getOdate() {
return odate;
}
public void setOdate(Date odate) {
this.odate = odate;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
@Override
public String toString() {
return "Order{" +
"oid=" + oid +
", pid=" + pid +
", odate=" + odate +
", price=" + price +
'}';
}
}
公民:Person
添加属性和getter,setter方法
// 一对多
private List<Order> orders;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
映射文件
PersonMapper.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.seven.mapper.PersonMapper">
<resultMap id="personMap" type="com.seven.entity.Person">
<id property="pid" column="pid"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<result property="birthday" column="birthday"/>
<result property="cid" column="cid"/>
</resultMap>
<resultMap id="pcMap" type="com.seven.entity.Person" extends="personMap">
<association property="card" column="cid" javaType="com.seven.entity.Card">
<id property="cid" column="cid"/>
<result property="cnum" column="cnum"/>
</association>
</resultMap>
<!-- 配置一对多的resultMap -->
<resultMap id="poMap" type="com.seven.entity.Person" extends="personMap">
<!-- property对应实体类的属性,ofType对应该属性的实体类 -->
<collection property="orders" ofType="com.seven.entity.Order">
<id property="oid" column="oid"/>
<result property="pid" column="pid"/>
<result property="odate" column="odate"/>
<result property="price" column="price"/>
</collection>
</resultMap>
<select id="findAll" resultMap="personMap">
select * from t_person
</select>
<select id="findDetailed" resultMap="pcMap">
select p.*, c.cnum from t_person p, t_card c where p.cid=c.cid
</select>
<!-- 左连接查询person和order的结果 -->
<select id="findPersonAndOrder" resultMap="poMap">
SELECT p.*, o.oid, o.odate, o.price
FROM t_person p LEFT JOIN t_order o ON (p.pid=o.pid);
</select>
</mapper>
映射接口
PersonMapper接口:
package com.seven.mapper;
import com.seven.entity.Person;
import java.util.List;
public interface PersonMapper {
List<Person> findAll();
/**
* 一对一关系
* @return
*/
List<Person> findDetailed();
/**
* 一对多关系
* @return
*/
List<Person> findPersonAndOrder();
}
测试类
TestPerson测试类:
import com.seven.entity.Person;
import com.seven.mapper.PersonMapper;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestPerson {
private InputStream in;
private SqlSession sqlSession;
private PersonMapper mapper;
@Before
public void init() {
try {
in = Resources.getResourceAsStream("mybatis.config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
mapper = sqlSession.getMapper(PersonMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@After
public void close() throws IOException {
sqlSession.close();
in.close();
}
@Test
public void testFindAll() {
List<Person> all = mapper.findAll();
for (Person person : all) {
System.out.println(person);
}
}
/**
* 测试一对一
*/
@Test
public void testFindDetailed() {
List<Person> persons = mapper.findDetailed();
for (Person person : persons) {
System.out.println(person);
}
}
/**
* 测试一对多
*/
@Test
public void testFindPersonAndOrder() {
List<Person> persons = mapper.findPersonAndOrder();
for (Person person : persons) {
System.out.println("-----------------------------------------------------------------------");
System.out.println(person);
System.out.println(person.getOrders());
}
}
}
结果
三、多对多关系
三张表
-- 订单表:在一对多中创建了
-- 商品表
-- 中间表
-- 表间关系:多对多
-- 一个订单下可以有多个商品
-- 一个商品可以属于多个订单
-- 商品表
DROP TABLE IF EXISTS t_goods;
CREATE TABLE t_goods(
gid VARCHAR(20),
gname VARCHAR(100),
gprice DOUBLE DEFAULT 0,
gdesc VARCHAR(200),
gstatus INT DEFAULT 1,
CONSTRAINT goods_pk PRIMARY KEY (gid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO t_goods VALUES('wy-1', '卫衣1', 11.9, '卫衣1-desc', 1);
INSERT INTO t_goods VALUES('wy-2', '卫衣2', 22.9, '卫衣2-desc', 1);
INSERT INTO t_goods VALUES('wy-3', '卫衣3', 33.9, '卫衣3-desc', 1);
INSERT INTO t_goods VALUES('nzk-1', '牛仔裤1', 111.9, '牛仔裤1-desc', 1);
INSERT INTO t_goods VALUES('nzk-2', '牛仔裤2', 222.9, '牛仔裤2-desc', 1);
INSERT INTO t_goods VALUES('nzk-3', '牛仔裤3', 333.9, '牛仔裤3-desc', 1);
INSERT INTO t_goods VALUES('xz-1', '鞋子1', 230.9, '鞋子1-desc', 1);
INSERT INTO t_goods VALUES('xz-2', '鞋子2', 120.9, '鞋子2-desc', 1);
INSERT INTO t_goods VALUES('xz-3', '鞋子3', 389.9, '鞋子3-desc', 1);
INSERT INTO t_goods VALUES('wz-1', '袜子1', 9.9, '袜子1-desc', 1);
INSERT INTO t_goods VALUES('wz-2', '袜子2', 5.9, '袜子2-desc', 1);
INSERT INTO t_goods VALUES('wz-3', '袜子3', 11.9, '袜子3-desc', 1);
INSERT INTO t_goods VALUES('yj-1', '眼镜1', 199.9, '眼镜1-desc', 1);
INSERT INTO t_goods VALUES('yj-2', '眼镜2', 150.9, '眼镜2-desc', 1);
INSERT INTO t_goods VALUES('yj-3', '眼镜3', 299.9, '眼镜3-desc', 1);
INSERT INTO t_goods VALUES('mf-1', '棉服1', 350.9, '棉服1-desc', 1);
INSERT INTO t_goods VALUES('mf-2', '棉服2', 359.9, '棉服2-desc', 1);
INSERT INTO t_goods VALUES('mf-3', '棉服3', 266.9, '棉服3-desc', 1);
INSERT INTO t_goods VALUES('jk-1', '夹克1', 159.9, '夹克1-desc', 1);
INSERT INTO t_goods VALUES('jk-2', '夹克2', 179.9, '夹克2-desc', 1);
INSERT INTO t_goods VALUES('jk-3', '夹克3', 199.9, '夹克3-desc', 1);
INSERT INTO t_goods VALUES('xxk-1', '休闲裤1', 119.9, '休闲裤1-desc', 1);
INSERT INTO t_goods VALUES('xxk-2', '休闲裤2', 189.9, '休闲裤2-desc', 1);
INSERT INTO t_goods VALUES('xxk-3', '休闲裤3', 129.9, '休闲裤3-desc', 1);
-- 中间表
DROP TABLE IF EXISTS t_order_goods;
CREATE TABLE t_order_goods(
oid INT,
gid VARCHAR(20),
CONSTRAINT order_goods_pk PRIMARY KEY (oid, gid),
CONSTRAINT og_o_fk FOREIGN KEY t_order_goods(oid) REFERENCES t_order(oid),
CONSTRAINT og_f_fk FOREIGN KEY t_order_goods(gid) REFERENCES t_goods(gid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO t_order_goods VALUES(1, 'wy-1');
INSERT INTO t_order_goods VALUES(1, 'wy-2');
INSERT INTO t_order_goods VALUES(1, 'xxk-3');
INSERT INTO t_order_goods VALUES(2, 'jk-1');
INSERT INTO t_order_goods VALUES(2, 'wy-1');
INSERT INTO t_order_goods VALUES(3, 'wz-1');
INSERT INTO t_order_goods VALUES(3, 'wz-2');
实现:
1.查询商品,可以得到购买过它的订单信息
2.查询订单,可以得到购买的商品信息
1.查询商品,可以得到购买过它的订单信息
实体类
商品:Goods
package com.seven.entity;
import java.io.Serializable;
import java.util.List;
public class Goods implements Serializable {
private String gid;
private String gname;
private Double gprice;
private String gdesc;
private Integer gstatus;
//多对多
private List<Order> orders;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
@Override
public String toString() {
return "Goods{" +
"gid='" + gid + '\'' +
", gname='" + gname + '\'' +
", gprice=" + gprice +
", gdesc='" + gdesc + '\'' +
", gstatus=" + gstatus +
'}';
}
public String getGid() {
return gid;
}
public void setGid(String gid) {
this.gid = gid;
}
public String getGname() {
return gname;
}
public void setGname(String gname) {
this.gname = gname;
}
public Double getGprice() {
return gprice;
}
public void setGprice(Double gprice) {
this.gprice = gprice;
}
public String getGdesc() {
return gdesc;
}
public void setGdesc(String gdesc) {
this.gdesc = gdesc;
}
public Integer getGstatus() {
return gstatus;
}
public void setGstatus(Integer gstatus) {
this.gstatus = gstatus;
}
}
映射文件
<?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.seven.mapper.GoodsMapper">
<resultMap id="goodsMap" type="com.seven.entity.Goods">
<id property="gid" column="gid"/>
<result property="gname" column="gname"/>
<result property="gprice" column="gprice"/>
<result property="gdesc" column="gdesc"/>
<result property="gstatus" column="gstatus"/>
</resultMap>
<resultMap id="goMap" type="com.seven.entity.Goods" extends="goodsMap">
<collection property="orders" ofType="com.seven.entity.Order">
<id property="oid" column="oid"/>
<result property="pid" column="pid"/>
<result property="odate" column="odate"/>
<result property="price" column="price"/>
</collection>
</resultMap>
<select id="findAll" resultMap="goodsMap">
select * from t_goods
</select>
<!-- 三张表查询,以商品表为主表的左连接查询 -->
<select id="findGoodsOrder" resultMap="goMap">
SELECT g.*, o.* FROM t_goods g
LEFT JOIN t_order_goods og ON og.gid=g.gid
LEFT JOIN t_order o ON og.oid=o.oid
</select>
</mapper>
映射接口
package com.seven.mapper;
import com.seven.entity.Goods;
import java.util.List;
public interface GoodsMapper {
List<Goods> findAll();
List<Goods> findGoodsOrder();
}
测试类
GoodsTest测试类:
import com.seven.entity.Goods;
import com.seven.entity.Order;
import com.seven.mapper.GoodsMapper;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestGoods {
private InputStream in;
private SqlSession sqlSession;
private GoodsMapper mapper;
@Before
public void init() {
try {
in = Resources.getResourceAsStream("mybatis.config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
mapper = sqlSession.getMapper(GoodsMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@After
public void close() throws IOException {
sqlSession.close();
in.close();
}
@Test
public void testFindAll() {
List<Goods> all = mapper.findAll();
for (Goods goods : all) {
System.out.println(goods);
}
}
@Test
public void testFindGoodsOrder() {
List<Goods> goodsOrder = mapper.findGoodsOrder();
for (Goods goods : goodsOrder) {
System.out.println("-------------------------------------");
System.out.println(goods);
for (Order order : goods.getOrders()) {
System.out.println(order);
}
}
}
}
结果
2.查询订单,可以得到购买的商品信息
实体类
Order:
package com.seven.entity;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class Order implements Serializable {
private Integer oid;
private Integer pid;
private Date odate;
private Double price;
//多对多
private List<Goods> goods;
public List<Goods> getGoods() {
return goods;
}
public void setGoods(List<Goods> goods) {
this.goods = goods;
}
public Integer getOid() {
return oid;
}
public void setOid(Integer oid) {
this.oid = oid;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public Date getOdate() {
return odate;
}
public void setOdate(Date odate) {
this.odate = odate;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
@Override
public String toString() {
return "Order{" +
"oid=" + oid +
", pid=" + pid +
", odate=" + odate +
", price=" + price +
'}';
}
}
映射文件
<?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.seven.mapper.OrderMapper">
<resultMap id="orderMap" type="com.seven.entity.Order">
<id property="oid" column="oid"/>
<result property="pid" column="pid"/>
<result property="odate" column="odate"/>
<result property="price" column="price"/>
</resultMap>
<resultMap id="ogMap" type="com.seven.entity.Order" extends="orderMap">
<collection property="goods" ofType="com.seven.entity.Goods">
<id property="gid" column="gid"/>
<result property="gname" column="gname"/>
<result property="gprice" column="gprice"/>
<result property="gdesc" column="gdesc"/>
<result property="gstatus" column="gstatus"/>
</collection>
</resultMap>
<select id="findAll" resultMap="orderMap">
select * from t_order
</select>
<!-- 三张表查询,以订单表为主表的左连接查询 -->
<select id="findOrderGoods" resultMap="ogMap">
SELECT o.*, g.* FROM t_order o
LEFT JOIN t_order_goods og ON og.oid=o.oid
LEFT JOIN t_goods g ON og.gid=g.gid;
</select>
</mapper>
映射接口
package com.seven.mapper;
import com.seven.entity.Order;
import java.util.List;
public interface OrderMapper {
List<Order> findAll();
List<Order> findOrderGoods();
}
测试类
import com.seven.entity.Goods;
import com.seven.entity.Order;
import com.seven.mapper.OrderMapper;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestOrder {
private InputStream in;
private SqlSession sqlSession;
private OrderMapper mapper;
@Before
public void init() {
try {
in = Resources.getResourceAsStream("mybatis.config.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
sqlSession = factory.openSession();
mapper = sqlSession.getMapper(OrderMapper.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@After
public void close() throws IOException {
sqlSession.close();
in.close();
}
@Test
public void findAll() {
List<Order> all = mapper.findAll();
for (Order order : all) {
System.out.println(order);
}
}
@Test
public void findOrderGoods() {
List<Order> orders = mapper.findOrderGoods();
for (Order order : orders) {
System.out.println("-----------------------------------------------------------------");
System.out.println(order);
for (Goods good : order.getGoods()) {
System.out.println(good);
}
}
}
}
结果
更新时间:2020-1-15