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

2023-08-23 12:19:10

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

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=哈士奇]]
上一篇：codeforces 22C System Administrator(构造水题)


下一篇：Mybatis学习笔记(三) —— DAO开发方法