#MyBatis关系映射
- OneToOneTest
- OneToManyTest
- ManyToManyTest
OneToOne
数据表
tb_card
CREATE TABLE tb_card(id INT PRIMARY KEY AUTO_INCREMENT,CODE VARCHAR(18));
INSERT INTO tb_card(CODE) VALUES('432801198009191038');
tb_person
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)
bean
Card
public class Card implements Serializable {
private Integer id; // 主键id
private String code; // 身份证编号
''''''
}
Person
public class Person implements Serializable {
private Integer id; // 主键id
private String name; // 姓名
private String sex; // 性别
private Integer age; // 年龄
// 人和身份证是一对一的关系,即一个人只有一个身份证
private Card card;
}
dao
PersonMapper
public interface PersonMapper {
/**
* 根据id查询Person
* 方法名和参数必须和XML文件中的<select.../>元素的id属性和parameterType属性一致
* @param id
* @return Person对象
* */
Person selectPersonById(Integer id);
}
mapper
CardMapper
<!-- 根据id查询Card,返回Card对象 -->
<select id="selectCardById" parameterType="int"resultType="org.fkit.domain.Card">
SELECT * from tb_card where id = #{id}
</select>
personMapper
<!-- 根据id查询Person,返回resultMap -->
<select id="selectPersonById" parameterType="int" resultMap="personMapper">
SELECT * from tb_person where id = #{id}
</select>
<!-- 映射Peson对象的resultMap -->
<resultMap type="org.fkit.domain.Person" id="personMapper">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<!-- 一对一关联映射:association -->
<association property="card" column="card_id" select="org.fkit.mapper.CardMapper.selectCardById" javaType="org.fkit.domain.Card"/>
</resultMap>
OneToMany
数据表
tb_clazz
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就业班');
tb_student
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_student(NAME,sex,age,clazz_id) VALUES('alice','女',20,1);
bean
Clazz
public class Clazz implements Serializable {
private Integer id; // 班级id,主键
private String code; // 班级编号
private String name; // 班级名称
// 班级和学生是一对多的关系,即一个班级可以有多个学生
private List<Student> students;
}
Student
public class Student implements Serializable {
private Integer id; // 学生id,主键
private String name; // 姓名
private String sex; // 性别
private Integer age; // 年龄
// 学生和班级是多对一的关系,即一个学生只属于一个班级
private Clazz clazz;
}
dao
ClazzMapper
public interface ClazzMapper {
// 根据id查询班级信息
Clazz selectClazzById(Integer id);
}
StudentMapper
public interface StudentMapper {
// 根据id查询学生信息
Student selectStudentById(Integer id);
}
mapper
ClazzMapper
<mapper namespace="org.fkit.mapper.ClazzMapper">
<!-- 根据id查询班级信息,返回resultMap -->
<select id="selectClazzById" parameterType="int" resultMap="clazzResultMap">
SELECT * FROM tb_clazz WHERE id = #{id}
</select>
<!-- 映射Clazz对象的resultMap -->
<resultMap type="org.fkit.domain.Clazz" id="clazzResultMap">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
<!-- 一对多关联映射:collection fetchType="lazy"表示懒加载 -->
<collection property="students" javaType="ArrayList" column="id" ofType="org.fkit.domain.Student"
select="org.fkit.mapper.StudentMapper.selectStudentByClazzId" fetchType="lazy">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
</collection>
</resultMap>
</mapper>
StudentMapper
<mapper namespace="org.fkit.mapper.StudentMapper">
<!-- 根据id查询学生信息,多表连接,返回resultMap -->
<select id="selectStudentById" parameterType="int" resultMap="studentResultMap">
SELECT * FROM tb_clazz c,tb_student s
WHERE c.id = s.clazz_id
AND s.id = #{id}
</select>
<!-- 根据班级id查询学生信息,返回resultMap -->
<select id="selectStudentByClazzId" parameterType="int" resultMap="studentResultMap">
SELECT * FROM tb_student WHERE clazz_id = #{id}
</select>
<!-- 映射Student对象的resultMap -->
<resultMap type="org.fkit.domain.Student" id="studentResultMap">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<!-- 多对一关联映射:association -->
<association property="clazz" javaType="org.fkit.domain.Clazz">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="name" column="name"/>
</association>
</resultMap>
</mapper>
ManyToMany
数据表
tb_article
CREATE TABLE tb_article(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(18),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,'肖文吉老师经典著作');
tb_order
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(id));
INSERT INTO tb_order(CODE,total,user_id) VALUES('6aa3fa359ff14619b77fab5990940a2d',388.6,1);
INSERT INTO tb_order(CODE,total,user_id) VALUES('6aa3fa359ff14619b77fab5990940b3c',217.8,1);
tb_user
CREATE TABLE tb_user(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(username,loginname,PASSWORD,phone,address) VALUES('杰克','jack','123456','13920001616','广州');
*tb_item
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);
bean
Article
public class Article implements Serializable {
private Integer id; // 商品id,主键
private String name; // 商品名称
private Double price; // 商品价格
private String remark; // 商品描述
// 商品和订单是多对多的关系,即一种商品可以包含在多个订单中
private List<Order> orders;
}
Order
public class Order implements Serializable {
private Integer id; // 订单id,主键
private String code; // 订单编号
private Double total; // 订单总金额
// 订单和用户是多对一的关系,即一个订单只属于一个用户
private User user;
// 订单和商品是多对多的关系,即一个订单可以包含多种商品
private List<Article> articles;
}
User
public class User implements Serializable{
private Integer id; // 用户id,主键
private String username; // 用户名
private String loginname; // 登录名
private String password; // 密码
private String phone; // 联系电话
private String address; // 收货地址
// 用户和订单是一对多的关系,即一个用户可以有多个订单
private List<Order> orders;
}
dao
OrderMapper
public interface OrderMapper {
Order selectOrderById(int id);
}
UserMapper
public interface UserMapper {
User selectUserById(int id);
}
mapper
ArticleMapper
<mapper namespace="org.fkit.mapper.ArticleMapper">
<select id="selectArticleByOrderId" parameterType="int" resultType="org.fkit.domain.Article">
SELECT * FROM tb_article WHERE id IN (
SELECT article_id FROM tb_item WHERE order_id = #{id}
)
</select>
</mapper>
OrderMapper
<mapper namespace="org.fkit.mapper.OrderMapper">
<resultMap type="org.fkit.domain.Order" id="orderResultMap">
<id property="id" column="oid"/>
<result property="code" column="code"/>
<result property="total" column="total"/>
<!-- 多对一关联映射:association -->
<association property="user" javaType="org.fkit.domain.User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="loginname" column="loginname"/>
<result property="password" column="password"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
</association>
<!-- 多对多映射的关键:collection -->
<collection property="articles" javaType="ArrayList"
column="oid" ofType="org.fkit.domain.Article"
select="org.fkit.mapper.ArticleMapper.selectArticleByOrderId"
fetchType="lazy">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="remark" column="remark"/>
</collection>
</resultMap>
<!-- 注意,如果查询出来的列同名,例如tb_user表的id和tb_order表的id都是id,同名,需要使用别名区分 -->
<select id="selectOrderById" parameterType="int" resultMap="orderResultMap">
SELECT u.*,o.id AS oid,CODE,total,user_id
FROM tb_user u,tb_order o
WHERE u.id = o.user_id
AND o.id = #{id}
</select>
<!-- 根据userid查询订单 -->
<select id="selectOrderByUserId" parameterType="int" resultType="org.fkit.domain.Order">
SELECT * FROM tb_order WHERE user_id = #{id}
</select>
</mapper>
UserMapper
<mapper namespace="org.fkit.mapper.UserMapper">
<resultMap type="org.fkit.domain.User" id="userResultMap">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="loginname" column="loginname"/>
<result property="password" column="password"/>
<result property="phone" column="phone"/>
<result property="address" column="address"/>
<!-- 一对多关联映射:collection -->
<collection property="orders" javaType="ArrayList"
column="id" ofType="org.fkit.domain.User"
select="org.fkit.mapper.OrderMapper.selectOrderByUserId"
fetchType="lazy">
<id property="id" column="id"/>
<result property="code" column="code"/>
<result property="total" column="total"/>
</collection>
</resultMap>
<select id="selectUserById" parameterType="int" resultMap="userResultMap">
SELECT * FROM tb_user WHERE id = #{id}
</select>
</mapper>