MyBatis的关联映射和动态SQL

CREATE TABLE tb_card (
id INT PRIMARY KEY AUTO_INCREMENT,
CODE VARCHAR(18)
);
INSERT INTO tb_card (CODE) VALUES('432801198009191038');
CREATE TABLE tb_person (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18),
sex VARCHAR(18),
age INT,
card_id INT UNIQUE,
FOREIGN KEY (card_id) REFERENCES tb_card (id)
);
INSERT INTO tb_person (NAME,sex,age,card_id) VALUES('jack','男',23,1);

一对一关系推荐使用唯一主外键关联。

---

一对多

CREATE TABLE tb_clazz(
id INT PRIMARY KEY AUTO_INCREMENT,
CODE VARCHAR(18),
NAME VARCHAR(18)
);

INSERT INTO tb_clazz(CODE,NAME) VALUES('j1601','Java就业班');

CREATE TABLE tb_student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18),
sex VARCHAR(18),
age INT,
clazz_id INT,
FOREIGN KEY (clazz_id) REFERENCES tb_clazz(id)
);

INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('jack','男',23,1);
INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('rose','女',18,1);
INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('tom','男',21,1);
INSERT INTO tb_stuedent(NAME,sex,age,clazz_id) VALUES('alice','女',20,1);

---

多对多

书中有勘误

#创建用户表
CREATE TABLE tb_user_1(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(18),
loginname VARCHAR(18),
PASSWORD VARCHAR(18),
phone VARCHAR(18),
address VARCHAR(18)
);
#插入用户表测试数据
INSERT INTO tb_user_1(username,loginname,PASSWORD,phone,address)
VALUES ('杰克','jack','123456','13920001616','广州');

#创建商品表
 CREATE TABLE tb_article(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(21),
 price DOUBLE,
 remark VARCHAR(18)
 );
 #插入商品表测试数据
 INSERT INTO tb_article(NAME,price,remark)
 VALUES('疯狂Java讲义',108.9,'李刚老师经典著作');
 INSERT INTO tb_article(NAME,price,remark)
 VALUES('疯狂Android讲义',99.9,'李刚老师经典著作');
 INSERT INTO tb_article(NAME,price,remark)
 VALUES('疯狂iOS讲义',89.9,'李刚老师经典著作');
 INSERT INTO tb_article(NAME,price,remark)
 VALUES('SpringMVC+MyBatis企业开发',69.9,'肖文吉老师经典著作');
 SELECT * FROM tb_article;

 #创建订单表
 CREATE TABLE tb_order(
  id INT PRIMARY KEY AUTO_INCREMENT,
  CODE VARCHAR(32),
  total DOUBLE,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES tb_user_1(id)
  );
  #输入订单表测试数据
  INSERT INTO tb_order(CODE,total,user_id)
  VALUES('6aa3fa359ff14619b77fab599094a2d',388.6,1);
  INSERT INTO tb_order(CODE,total,user_id)
  VALUES('6aa3fa359ff14619b77fab599094b3c',217.8,1);

  #创建中间表
  CREATE TABLE tb_item(
  order_id INT,
  article_id INT,
  amount INT,
  PRIMARY KEY (order_id,article_id),
  FOREIGN KEY (order_id) REFERENCES tb_order(id),
  FOREIGN KEY (article_id) REFERENCES tb_article(id)
  );

#创建插入中间表数据
  INSERT INTO tb_item(order_id,article_id,amount)
  VALUES (1,1,1);
  INSERT INTO tb_item(order_id,article_id,amount)
  VALUES (1,2,1);
  INSERT INTO tb_item(order_id,article_id,amount)
  VALUES (1,3,2);
  INSERT INTO tb_item(order_id,article_id,amount)
  VALUES (2,4,2);
  INSERT INTO tb_item(order_id,article_id,amount)
  VALUES (2,1,1);