MyBatis关系映射//TODO

#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>