MyBatis学习笔记(三) 关联关系

时间:2023-02-04 18:17:35

首先给大家推荐几个网页:

http://blog.csdn.net/isea533/article/category/2092001 没事看看 - MyBatis工具:www.mybatis.tk

http://www.mybatis.org/mybatis-3/zh/getting-started.html 入门

http://www.mybatis.tk/

http://mbg.cndocs.tk/

http://edu.51cto.com/course/course_id-1354.html  mybatis视频教程

一、多对多关系。

一般我们在设置多对多关系的时候,都是建立第三张关系表。

例子:学生t_student与课程t_courses,一个学生可以对应学习多门课程,一门课程对应可以有多名学生学习。第三张关系表t_stu_cou。

1)关系整理好,接着建立数据库表,添加信息。

 create table t_student(
id int primary key auto_increment,
student_name varchar(20)
); create table t_courses(
id int primary key auto_increment,
courses_name varchar(20)
); create table t_stu_cou(
id int primary key auto_increment,
fk_stu_id int,
fk_cou_id int
);

添加数据。

 insert into t_student values (null,'米兰');
insert into t_student values (null,'凌雪');
insert into t_student values (null,'成成');
insert into t_student values (null,'睿懿');
insert into t_student values (null,'瑞瑞'); insert into t_courses values (null,'语文');
insert into t_courses values (null,'数学');
insert into t_courses values (null,'计算机');
insert into t_courses values (null,'java编程');
insert into t_courses values (null,'html'); insert into t_stu_cou values (null,1,1);
insert into t_stu_cou values (null,1,2);
insert into t_stu_cou values (null,2,3);
insert into t_stu_cou values (null,2,4);
insert into t_stu_cou values (null,3,1);
insert into t_stu_cou values (null,3,5);
insert into t_stu_cou values (null,4,4);
insert into t_stu_cou values (null,4,2);

 2)建立JavaBean。

CoursesBean.java

 package com.cy.mybatis.beans;

 import java.io.Serializable;
import java.util.List;
/**
* manyTOmany
* @author acer
*
*/
public class CoursesBean implements Serializable{ private static final long serialVersionUID = 1L;
private Integer id;
private String name;
// 使用 List<StudentBean>集合,是说明学习这门课程的所有学生
private List<StudentBean> student;
public CoursesBean() {
super();
}
public CoursesBean(Integer id, String name, List<StudentBean> student) {
super();
this.id = id;
this.name = name;
this.student = student;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<StudentBean> getStudent() {
return student;
}
public void setStudent(List<StudentBean> student) {
this.student = student;
}
@Override
public String toString() {
return "CoursesBean [id=" + id + ", name=" + name + ", student="
+ student + "]";
} }

StudentBean.java

 package com.cy.mybatis.beans;

 import java.io.Serializable;
import java.util.List;
/**
* manyTOmany
* @author acer
*
*/
public class StudentBean implements Serializable{ private static final long serialVersionUID = 1L; private Integer id;
private String name;
private List<CoursesBean> courses;
public StudentBean() {
super();
// TODO Auto-generated constructor stub
}
public StudentBean(Integer id, String name, List<CoursesBean> courses) {
super();
this.id = id;
this.name = name;
this.courses = courses;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<CoursesBean> getCourses() {
return courses;
}
public void setCourses(List<CoursesBean> courses) {
this.courses = courses;
}
@Override
public String toString() {
return "StudentBean [id=" + id + ", name=" + name + ", courses="
+ courses + "]";
} }

3) 定义接口  

CoursesMapper.java

 package com.cy.mybatis.mapper;

 import com.cy.mybatis.beans.CoursesBean;

 public interface CoursesMapper {

     /**
* 根据id查询课程
* @param id
* @return
*/
public CoursesBean findCouById(int id); /**
* 要求查课时,将选课的学生一并查出
* @param id
* @return
*/
public CoursesBean findCouAndStu(int id); }

StudentMapper.java

 package com.cy.mybatis.mapper;

 import com.cy.mybatis.beans.StudentBean;

 public interface StudentMapper {
/**
* 根据id值查询学生信息
* @param id
* @return
*/
public StudentBean findStuById(int id); /**
* 要求查询学生时,将学生选择的课程查出
* @param id
* @return
*/
public StudentBean findStuAndCou(int id); }

 4) 定义xml文件。CoursesMapper.xml    StudentMapper.xml

 mybatis实际是对XML进行操作,我们所有的方法都直接定义在XML中,写个接口只是为了更好的符合我们3层的思想.XML中只要有方法,就可以使用,而调用的方式就是:namespace+方法名;

CoursesMapper.xml 

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cy.mybatis.mapper.CoursesMapper"> <resultMap type="CoursesBean" id="coursesMap">
<!-- 在默认情况下,mybatis会自动在TypeAliasRegistry初始化的时候挂在很多jdk常用类,
所以javaType="java.lang.Integer"可以写成javaType="Integer"--> <id property="id" column="id" javaType="java.lang.Integer"/>
<result property="name" column="courses_name" javaType="java.lang.String"/>
</resultMap> <resultMap type="CoursesBean" id="couAndStu">
<id property="id" column="id" javaType="java.lang.Integer"/>
<result property="name" column="courses_name" javaType="java.lang.String"/> <!-- 对于一个属性的类型是一个集合,就使用collection
对于一个属性的类型是一个类,就使用association -->
<collection property="student" column="id" select="findStudentByCourses"></collection>
</resultMap> <select id="findCouById" resultMap="coursesMap">
select * from t_courses where id=#{id}
</select> <!-- 有学生表,课程表这两张表都没有外键,我们就要使用第三张关联表。我们就要根据课程表的fk_cou_id,把学生的id值得到。
对于多对多那么这个学生的id值就不可能是一个值 。在数据库里就要使用in-->
<select id="findStudentByCourses" resultMap="com.cy.mybatis.mapper.StudentMapper.studentMap">
select * from t_student where id in (select fk_stu_id from t_stu_cou where fk_cou_id=#{id})
</select> <select id="findCouAndStu" resultMap="couAndStu">
select * from t_courses where id=#{id}
</select>
</mapper>

StudentMapper.xml

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cy.mybatis.mapper.StudentMapper"> <resultMap type="StudentBean" id="studentMap">
<id property="id" column="id" javaType="java.lang.Integer"/>
<result property="name" column="student_name" javaType="java.lang.String"/>
</resultMap> <resultMap type="StudentBean" id="studentAndCourses">
<id property="id" column="id" javaType="java.lang.Integer"/>
<result property="name" column="student_name" javaType="java.lang.String"/>
<collection property="courses" column="id" select="findCoursesByStudent"></collection>
</resultMap>
<select id="findStudentById" resultMap="studentMap">
select * from t_student where id = #{id}
</select> <select id="findStuAndCou" resultMap="studentAndCourses">
select * from t_student where id = #{id}
</select>
<select id="findCoursesByStudent" resultMap="com.cy.mybatis.mapper.CoursesMapper.coursesMap">
select * from t_courses where id in (select fk_cou_id from t_stu_cou where fk_stu_id = #{id})
</select> </mapper>

 5) 测试

 package com.cy.mybatis.service;

 import org.apache.ibatis.session.SqlSession;

 import com.cy.mybatis.beans.CoursesBean;
import com.cy.mybatis.beans.StudentBean;
import com.cy.mybatis.mapper.CoursesMapper;
import com.cy.mybatis.mapper.StudentMapper;
import com.cy.mybatis.tools.DBTools; public class ManyToManyService { public static void main(String[] args) { findStudentByCourses();
findCoursesByStudent();
} private static void findCoursesByStudent() {
SqlSession session = DBTools.getSession();
StudentMapper sm=session.getMapper(StudentMapper.class);
StudentBean sb=sm.findStuAndCou(1);
System.out.println(sb); } private static void findStudentByCourses() {
SqlSession session = DBTools.getSession();
CoursesMapper cm=session.getMapper(CoursesMapper.class);
CoursesBean cb=cm.findCouAndStu(2);
System.out.println(cb);
} }

结果显示:

 DEBUG 2016-02-27 09:56:53,852 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC Connection
DEBUG 2016-02-27 09:56:54,070 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 586269.
DEBUG 2016-02-27 09:56:54,070 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@8f21d]
DEBUG 2016-02-27 09:56:54,070 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from t_courses where id=?
DEBUG 2016-02-27 09:56:54,105 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 2(Integer)
DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Preparing: select * from t_student where id in (select fk_stu_id from t_stu_cou where fk_cou_id=?)
DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 2(Integer)
DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==== Total: 2
DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 1
CoursesBean [id=2, name=数学, student=[StudentBean [id=1, name=米兰, courses=null], StudentBean [id=4, name=睿懿, courses=null]]]
DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC Connection
DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 23881129.
DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@16c65a9]
DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from t_student where id = ?
DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)
DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Preparing: select * from t_courses where id in (select fk_cou_id from t_stu_cou where fk_stu_id = ?)
DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 1(Integer)
DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==== Total: 2
DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 1
StudentBean [id=1, name=米兰, courses=[CoursesBean [id=1, name=语文, student=null], CoursesBean [id=2, name=数学, student=null]]]

二 、继承

最简单的例子,宠物。

数据库

 create TABLE t_pet(
id int primary key auto_increment,
name varchar(20),
type varchar(20),
bone int,
fish int
);

 JavaBean

PetBean:

 package com.cy.mybatis.beans;

 import java.io.Serializable;

 public class PetBean implements Serializable {

     private static final long serialVersionUID = 8920733441991237426L;

     private Integer id;
private String name;
public PetBean() {
super();
}
public PetBean(Integer id, String name) {
super();
this.id = id;
this.name = name; }
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
} @Override
public String toString() {
return "PetBean [id=" + id + ", name=" + name + "]";
} }

CatBean:

 package com.cy.mybatis.beans;

 public class CatBean extends PetBean {

     private static final long serialVersionUID = 1978574561040340989L;

     private Integer fish;

     public CatBean() {
super();
// TODO Auto-generated constructor stub
} public CatBean(Integer id, String name) {
super(id, name);
// TODO Auto-generated constructor stub
} public Integer getFish() {
return fish;
}
public void setFish(Integer fish) {
this.fish = fish;
} @Override
public String toString() {
return "CatBean [fish=" + fish + ", toString()=" + super.toString()
+ "]";
}
}

DogBean:

 package com.cy.mybatis.beans;

 public class DogBean extends PetBean {

     private static final long serialVersionUID = -9020056420879737672L;

     private Integer bone;

     public DogBean() {
super();
} public DogBean(Integer id, String name) {
super(id, name);
} public Integer getBone() {
return bone;
} public void setBone(Integer bone) {
this.bone = bone;
} @Override
public String toString() {
return "DogBean [bone=" + bone + ", toString()=" + super.toString()
+ "]";
} }

 

   接口

PetMapper.java

 package com.cy.mybatis.mapper;

 import java.util.List;

 import org.apache.ibatis.annotations.Param;

 import com.cy.mybatis.beans.CatBean;
import com.cy.mybatis.beans.DogBean;
import com.cy.mybatis.beans.PetBean; public interface PetMapper {
/**
* 添加宠物猫
* @param cat
* @return
*/
public int saveCat(@Param("c")CatBean cat); /**
* 添加宠物狗
* @param dog
* @return
*/
public int saveDog(@Param("d")DogBean dog); /**
* 查询所有的宠物
* @return
*/
public List<PetBean> findAllPet(); /**
* 查询所有的宠物猫
* @return
*/
public List<CatBean> findAllCat(); }

 XML

 <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cy.mybatis.mapper.PetMapper">
<resultMap id="petMap" type="PetBean">
<id property="id" column="id" javaType="java.lang.Integer"/>
<result property="name" column="name" javaType="java.lang.String"/>
<!--discriminator:根据结果值决定使用哪个resultMap
case:判断条件
它的表现很像 Java 语言中的 switch 语句。
定义鉴别器指定了 column 和 javaType 属性
-->
<discriminator javaType="java.lang.String" column="type">
<case value="cat" resultType="CatBean">
<result property="fish" column="fish" javaType="java.lang.Integer"/>
</case>
<case value="dog" resultType="DogBean">
<result property="bone" column="bone" javaType="java.lang.Integer"/>
</case>
</discriminator>
</resultMap> <insert id="saveCat">
insert into t_pet(name,type,fish) values (#{c.name},'cat',#{c.fish})
</insert>
<insert id="saveDog">
insert into t_pet(name,type,bone) values (#{d.name},'dog',#{d.bone})
</insert> <select id="findAllPet" resultMap="petMap">
select * from t_pet
</select>
<select id="findAllCat" resultMap="petMap">
select * from t_pet where type = 'cat'
</select> </mapper>

测试:

 package com.cy.mybatis.service;

 import java.util.List;

 import org.apache.ibatis.session.SqlSession;

 import com.cy.mybatis.beans.CatBean;
import com.cy.mybatis.beans.DogBean;
import com.cy.mybatis.beans.PetBean;
import com.cy.mybatis.mapper.PetMapper;
import com.cy.mybatis.tools.DBTools; public class ExtendsService {
public static void main(String[] args) {
// saveCat();
// saveDog();
findAllCat();
findAllPet();
} private static void findAllCat() {
// TODO Auto-generated method stub
SqlSession session = DBTools.getSession();
PetMapper pm = session.getMapper(PetMapper.class);
List<CatBean> cats = pm.findAllCat();
for (CatBean catBean : cats) {
System.out.println(catBean);
}
} private static void findAllPet() {
// TODO Auto-generated method stub
SqlSession session = DBTools.getSession();
PetMapper pm = session.getMapper(PetMapper.class);
List<PetBean> pets = pm.findAllPet();
for (PetBean petBean : pets) {
System.out.println(petBean);
}
} private static void saveDog() {
// TODO Auto-generated method stub
SqlSession session = DBTools.getSession();
PetMapper pm = session.getMapper(PetMapper.class);
DogBean dog = new DogBean(null,"哈士奇");
dog.setBone(10);
pm.saveDog(dog);
session.commit();
} private static void saveCat() {
// TODO Auto-generated method stub
SqlSession session = DBTools.getSession();
PetMapper pm = session.getMapper(PetMapper.class);
CatBean cat = new CatBean(null,"大脸猫");
cat.setFish(10);
pm.saveCat(cat);
session.commit();
}
}

结果显示

 DEBUG 2016-02-27 11:51:56,857 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC Connection
DEBUG 2016-02-27 11:51:57,073 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 8768896.
DEBUG 2016-02-27 11:51:57,074 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@85cd80]
DEBUG 2016-02-27 11:51:57,076 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from t_pet where type = 'cat'
DEBUG 2016-02-27 11:51:57,107 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters:
DEBUG 2016-02-27 11:51:57,121 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 1
CatBean [fish=10, toString()=PetBean [id=1, name=大脸猫]]
DEBUG 2016-02-27 11:51:57,122 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC Connection
DEBUG 2016-02-27 11:51:57,133 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 28157774.
DEBUG 2016-02-27 11:51:57,134 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1ada74e]
DEBUG 2016-02-27 11:51:57,134 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Preparing: select * from t_pet
DEBUG 2016-02-27 11:51:57,134 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters:
DEBUG 2016-02-27 11:51:57,135 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <== Total: 2
CatBean [fish=10, toString()=PetBean [id=1, name=大脸猫]]
DogBean [bone=10, toString()=PetBean [id=2, name=哈士奇]]