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

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

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( 2  id int primary key auto_increment, 3  student_name varchar(20) 4 ); 5  6 create table t_courses( 7 id  int primary key auto_increment, 8 courses_name varchar(20) 9 );10 11 create table t_stu_cou(12  id int primary key auto_increment,13  fk_stu_id int,14  fk_cou_id int 15 );

 添加数据。

insert into t_student values (null,'米兰'); 2 insert into t_student values (null,'凌雪'); 3 insert into t_student values (null,'成成'); 4 insert into t_student values (null,'睿懿'); 5 insert into t_student values (null,'瑞瑞'); 6  7 insert into t_courses values (null,'语文'); 8 insert into t_courses values (null,'数学'); 9 insert into t_courses values (null,'计算机');10 insert into t_courses values (null,'java编程');11 insert into t_courses values (null,'html');12 13 insert into t_stu_cou values (null,1,1);14 insert into t_stu_cou values (null,1,2);15 insert into t_stu_cou values (null,2,3);16 insert into t_stu_cou values (null,2,4);17 insert into t_stu_cou values (null,3,1);18 insert into t_stu_cou values (null,3,5);19 insert into t_stu_cou values (null,4,4);20 insert into t_stu_cou values (null,4,2);

 

 

 2）建立JavaBean。

 CoursesBean.java

package com.cy.mybatis.beans; 2  3 import java.io.Serializable; 4 import java.util.List; 5 /** 6  * manyTOmany 7  * @author acer 8  * 9  */10 public class CoursesBean implements Serializable{11 12     13     private static final long serialVersionUID = 1L;14     private Integer id;15     private String name;16    // 使用 List<StudentBean>集合，是说明学习这门课程的所有学生17     private List<StudentBean> student;18     public CoursesBean() {19         super();20     }21     public CoursesBean(Integer id, String name, List<StudentBean> student) {22         super();23         this.id = id;24         this.name = name;25         this.student = student;26     }27     public Integer getId() {28         return id;29     }30     public void setId(Integer id) {31         this.id = id;32     }33     public String getName() {34         return name;35     }36     public void setName(String name) {37         this.name = name;38     }39     public List<StudentBean> getStudent() {40         return student;41     }42     public void setStudent(List<StudentBean> student) {43         this.student = student;44     }45     @Override46     public String toString() {47         return "CoursesBean [id=" + id + ", name=" + name + ", student="48                 + student + "]";49     }50     51 52 }

 

 StudentBean.java

package com.cy.mybatis.beans; 2  3 import java.io.Serializable; 4 import java.util.List; 5 /** 6  * manyTOmany 7  * @author acer 8  * 9  */10 public class StudentBean implements Serializable{11 12     private static final long serialVersionUID = 1L;13      14     private Integer id;15     private String name;    16     private List<CoursesBean> courses;17     public StudentBean() {18         super();19         // TODO Auto-generated constructor stub20     }21     public StudentBean(Integer id, String name, List<CoursesBean> courses) {22         super();23         this.id = id;24         this.name = name;25         this.courses = courses;26     }27     public Integer getId() {28         return id;29     }30     public void setId(Integer id) {31         this.id = id;32     }33     public String getName() {34         return name;35     }36     public void setName(String name) {37         this.name = name;38     }39     public List<CoursesBean> getCourses() {40         return courses;41     }42     public void setCourses(List<CoursesBean> courses) {43         this.courses = courses;44     }45     @Override46     public String toString() {47         return "StudentBean [id=" + id + ", name=" + name + ", courses="48                 + courses + "]";49     }50     51 52 }

 

 

3) 定义接口  

CoursesMapper.java

package com.cy.mybatis.mapper; 2  3 import com.cy.mybatis.beans.CoursesBean; 4  5 public interface CoursesMapper { 6      7     /** 8      * 根据id查询课程 9      * @param id10      * @return11      */12     public CoursesBean findCouById(int id);13     14     /**15      * 要求查课时，将选课的学生一并查出16      * @param id17      * @return18      */19     public CoursesBean findCouAndStu(int id);20 21 22     23 24 }

 

StudentMapper.java

package com.cy.mybatis.mapper; 2  3 import com.cy.mybatis.beans.StudentBean; 4  5 public interface StudentMapper { 6     /** 7      * 根据id值查询学生信息 8      * @param id 9      * @return10      */11     public StudentBean findStuById(int id);12     13     /**14      * 要求查询学生时，将学生选择的课程查出15      * @param id16      * @return17      */18     public StudentBean findStuAndCou(int id);19 20 }

 

 

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

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

 CoursesMapper.xml 

 

<?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.cy.mybatis.mapper.CoursesMapper"> 4  5  6  <resultMap type="CoursesBean" id="coursesMap"> 7    <!--   在默认情况下，mybatis会自动在TypeAliasRegistry初始化的时候挂在很多jdk常用类， 8                           所以javaType="java.lang.Integer"可以写成javaType="Integer"-->   9                            10     <id property="id" column="id" javaType="java.lang.Integer"/>11     <result property="name" column="courses_name" javaType="java.lang.String"/>12  </resultMap>13 14  <resultMap type="CoursesBean" id="couAndStu">15     <id property="id" column="id" javaType="java.lang.Integer"/>16     <result property="name" column="courses_name" javaType="java.lang.String"/>17     18     <!-- 对于一个属性的类型是一个集合，就使用collection19                       对于一个属性的类型是一个类，就使用association   -->20     <collection property="student" column="id" select="findStudentByCourses"></collection>21  </resultMap>22 23 24     25  <select id="findCouById" resultMap="coursesMap">26   select * from t_courses where id=#{id}27  </select>28  29  30  31  <!-- 有学生表，课程表这两张表都没有外键，我们就要使用第三张关联表。我们就要根据课程表的fk_cou_id，把学生的id值得到。32                对于多对多那么这个学生的id值就不可能是一个值 。在数据库里就要使用in-->33  <select id="findStudentByCourses" resultMap="com.cy.mybatis.mapper.StudentMapper.studentMap">34     select * from t_student where id in (select fk_stu_id from t_stu_cou where fk_cou_id=#{id})35  </select>36     37  <select id="findCouAndStu" resultMap="couAndStu">38  select * from t_courses where id=#{id}39  </select>40 </mapper>

 

 StudentMapper.xml

<?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.cy.mybatis.mapper.StudentMapper"> 4  5 <resultMap type="StudentBean" id="studentMap"> 6     <id property="id" column="id" javaType="java.lang.Integer"/> 7     <result property="name" column="student_name" javaType="java.lang.String"/> 8 </resultMap> 9 10 <resultMap type="StudentBean" id="studentAndCourses">11      <id property="id" column="id" javaType="java.lang.Integer"/>12      <result property="name" column="student_name" javaType="java.lang.String"/>13      <collection property="courses" column="id" select="findCoursesByStudent"></collection>14 </resultMap>15     <select id="findStudentById" resultMap="studentMap">16         select * from t_student where id = #{id}17     </select>18     19     <select id="findStuAndCou" resultMap="studentAndCourses">20         select * from t_student where id = #{id}21     </select>22     <select id="findCoursesByStudent" resultMap="com.cy.mybatis.mapper.CoursesMapper.coursesMap">23         select * from t_courses where id in (select fk_cou_id from t_stu_cou where fk_stu_id = #{id})24     </select>25     26 </mapper>

 

 5) 测试

package com.cy.mybatis.service; 2  3 import org.apache.ibatis.session.SqlSession; 4  5 import com.cy.mybatis.beans.CoursesBean; 6 import com.cy.mybatis.beans.StudentBean; 7 import com.cy.mybatis.mapper.CoursesMapper; 8 import com.cy.mybatis.mapper.StudentMapper; 9 import com.cy.mybatis.tools.DBTools;10 11 12 13 public class ManyToManyService {14     15     public static void main(String[] args) {16         17         findStudentByCourses();18         findCoursesByStudent();19     }20 21     22 23     private static void findCoursesByStudent() {24         SqlSession session = DBTools.getSession();25         StudentMapper sm=session.getMapper(StudentMapper.class);26         StudentBean sb=sm.findStuAndCou(1);27         System.out.println(sb);28         29     }30 31 32 33     private static void findStudentByCourses() {34         SqlSession session = DBTools.getSession();35         CoursesMapper cm=session.getMapper(CoursesMapper.class);36         CoursesBean cb=cm.findCouAndStu(2);37         System.out.println(cb);38     }39 40 }

 

 结果显示：

DEBUG 2016-02-27 09:56:53,852 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC Connection 2 DEBUG 2016-02-27 09:56:54,070 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 586269. 3 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] 4 DEBUG 2016-02-27 09:56:54,070 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select * from t_courses where id=?  5 DEBUG 2016-02-27 09:56:54,105 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 2(Integer) 6 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=?)  7 DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 2(Integer) 8 DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <====      Total: 2 9 DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 110 CoursesBean [id=2, name=数学, student=[StudentBean [id=1, name=米兰, courses=null], StudentBean [id=4, name=睿懿, courses=null]]]11 DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC Connection12 DEBUG 2016-02-27 09:56:54,136 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 23881129.13 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]14 DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select * from t_student where id = ? 15 DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 1(Integer)16 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 = ?) 17 DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ====> Parameters: 1(Integer)18 DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <====      Total: 219 DEBUG 2016-02-27 09:56:54,152 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 120 StudentBean [id=1, name=米兰, courses=[CoursesBean [id=1, name=语文, student=null], CoursesBean [id=2, name=数学, student=null]]]

 

 

二 、继承

最简单的例子，宠物。

           数据库

create TABLE t_pet(2 id int  primary key auto_increment,3 name varchar(20),4 type varchar(20),5 bone int,6 fish int7 );

 

   

 

 JavaBean

PetBean：

 

 

 

CatBean：

 

 

DogBean：

 

 

 

 

   接口

PetMapper.java

package com.cy.mybatis.mapper; 2  3 import java.util.List; 4  5 import org.apache.ibatis.annotations.Param; 6  7 import com.cy.mybatis.beans.CatBean; 8 import com.cy.mybatis.beans.DogBean; 9 import com.cy.mybatis.beans.PetBean;10 11 public interface PetMapper {12     /**13      * 添加宠物猫14      * @param cat15      * @return16      */17     public int saveCat(@Param("c")CatBean cat);18     19     /**20      * 添加宠物狗21      * @param dog22      * @return23      */24     public int saveDog(@Param("d")DogBean dog);25     26     /**27      * 查询所有的宠物28      * @return29      */30     public List<PetBean> findAllPet();31     32     /**33      * 查询所有的宠物猫34      * @return35      */36     public List<CatBean> findAllCat();37     38     39 }

 

 

 

 XML

<?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 3 <mapper namespace="com.cy.mybatis.mapper.PetMapper"> 4     <resultMap id="petMap" type="PetBean"> 5         <id property="id" column="id" javaType="java.lang.Integer"/> 6         <result property="name" column="name" javaType="java.lang.String"/> 7         <!--discriminator：根据结果值决定使用哪个resultMap 8             case：判断条件   9                           它的表现很像 Java 语言中的 switch 语句。10                           定义鉴别器指定了 column 和 javaType 属性      11             -->12         <discriminator javaType="java.lang.String" column="type">13             <case value="cat" resultType="CatBean">14                 <result property="fish" column="fish" javaType="java.lang.Integer"/>15             </case>16             <case value="dog" resultType="DogBean">17                 <result property="bone" column="bone" javaType="java.lang.Integer"/>18             </case>19         </discriminator>20     </resultMap>21     22     <insert id="saveCat">23         insert into t_pet(name,type,fish) values (#{c.name},'cat',#{c.fish})24     </insert>25     <insert id="saveDog">26         insert into t_pet(name,type,bone) values (#{d.name},'dog',#{d.bone})27     </insert>28     29     <select id="findAllPet" resultMap="petMap"> 30         select * from t_pet31     </select>32     <select id="findAllCat" resultMap="petMap"> 33         select * from t_pet where type = 'cat'34     </select>35 36 </mapper>

 

 

 

测试：

package com.cy.mybatis.service; 2  3 import java.util.List; 4  5 import org.apache.ibatis.session.SqlSession; 6  7 import com.cy.mybatis.beans.CatBean; 8 import com.cy.mybatis.beans.DogBean; 9 import com.cy.mybatis.beans.PetBean;10 import com.cy.mybatis.mapper.PetMapper;11 import com.cy.mybatis.tools.DBTools;12 13 14 15 public class ExtendsService {16     public static void main(String[] args) {17 //        saveCat();18 //        saveDog();19         findAllCat();20         findAllPet();21     }22 23     private static void findAllCat() {24         // TODO Auto-generated method stub25         SqlSession session = DBTools.getSession();26         PetMapper pm = session.getMapper(PetMapper.class);27         List<CatBean> cats = pm.findAllCat();28         for (CatBean catBean : cats) {29             System.out.println(catBean);30         }31     }32 33     private static void findAllPet() {34         // TODO Auto-generated method stub35         SqlSession session = DBTools.getSession();36         PetMapper pm = session.getMapper(PetMapper.class);37         List<PetBean> pets = pm.findAllPet();38         for (PetBean petBean : pets) {39             System.out.println(petBean);40         }41     }42 43     private static void saveDog() {44         // TODO Auto-generated method stub45         SqlSession session = DBTools.getSession();46         PetMapper pm = session.getMapper(PetMapper.class);47         DogBean dog = new DogBean(null,"哈士奇");48         dog.setBone(10);49         pm.saveDog(dog);50         session.commit();51     }52 53     private static void saveCat() {54         // TODO Auto-generated method stub55         SqlSession session = DBTools.getSession();56         PetMapper pm = session.getMapper(PetMapper.class);57         CatBean cat = new CatBean(null,"大脸猫");58         cat.setFish(10);        59         pm.saveCat(cat);60         session.commit();61     }62 }

 

 

 

结果显示

DEBUG 2016-02-27 11:51:56,857 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC Connection 2 DEBUG 2016-02-27 11:51:57,073 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 8768896. 3 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] 4 DEBUG 2016-02-27 11:51:57,076 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select * from t_pet where type = 'cat'  5 DEBUG 2016-02-27 11:51:57,107 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters:  6 DEBUG 2016-02-27 11:51:57,121 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 1 7 CatBean [fish=10, toString()=PetBean [id=1, name=大脸猫]] 8 DEBUG 2016-02-27 11:51:57,122 org.apache.ibatis.transaction.jdbc.JdbcTransaction: Opening JDBC Connection 9 DEBUG 2016-02-27 11:51:57,133 org.apache.ibatis.datasource.pooled.PooledDataSource: Created connection 28157774.10 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]11 DEBUG 2016-02-27 11:51:57,134 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==>  Preparing: select * from t_pet 12 DEBUG 2016-02-27 11:51:57,134 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: ==> Parameters: 13 DEBUG 2016-02-27 11:51:57,135 org.apache.ibatis.logging.jdbc.BaseJdbcLogger: <==      Total: 214 CatBean [fish=10, toString()=PetBean [id=1, name=大脸猫]]15 DogBean [bone=10, toString()=PetBean [id=2, name=哈士奇]]