Java基础-SSM之mybatis多对多关联

　　　　　　　　　　　　Java基础-SSM之mybatis多对多关联

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

版权声明：原创作品，谢绝转载！否则将追究法律责任。

一.准备测试环境（创建数据库表）

1>.创建teas，stus，links表

use yinzhengjie;

create table teas(id int primary key auto_increment , tname varchar(20)) ;

create table stus(id int primary key auto_increment , sname varchar(20)) ;

create table links(tid int , sid int) ;

alter table links add constraint fk_tid foreign key (tid) references teas(id) ;

alter table links add constraint fk_sid foreign key (sid) references stus(id) ;

2>.添加Maven依赖

 <?xml version="1.0" encoding="UTF-8"?>
 <project xmlns="http://maven.apache.org/POM/4.0.0"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <modelVersion>4.0.0</modelVersion>
     <groupId>cn.org.yinzhengjie</groupId>
     <artifactId>Mybatis</artifactId>
     <version>1.0-SNAPSHOT</version>
     <dependencies>
         <dependency>
             <groupId>junit</groupId>
             <artifactId>junit</artifactId>
             <version>4.11</version>
         </dependency>
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
             <version>5.1.17</version>
         </dependency>
         <dependency>
             <groupId>org.mybatis</groupId>
             <artifactId>mybatis</artifactId>
             <version>3.2.1</version>
         </dependency>
     </dependencies>
 </project>

3>.目录结构如下：

二.编写自定义类

1>.Stu.java 文件内容

 /*
 @author :yinzhengjie
 Blog:http://www.cnblogs.com/yinzhengjie/tag/Java%E5%9F%BA%E7%A1%80/
 EMAIL:[e-mail]
 */
 package cn.org.yinzhengjie.mybatis.domain.one2many;

 import java.util.ArrayList;
 import java.util.List;

 /**
  *    客户
  */
 public class Customer {
     private Integer id ;
     private String name ;
     private int age ;

     //建立从Customer到Order之间一对多关系,因为一个客户可能会有多个订单。我们将多个订单放在一个list中。
     private List<Order> orders = new ArrayList<Order>() ;

     public List<Order> getOrders() {
         return orders;
     }

     public void setOrders(List<Order> orders) {
         this.orders = orders;
     }

     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 int getAge() {
         return age;
     }

     public void setAge(int age) {
         this.age = age;
     }
 }

2>.Tea.java 文件内容

 /*
 @author :yinzhengjie
 Blog:http://www.cnblogs.com/yinzhengjie/tag/Java%E5%9F%BA%E7%A1%80/
 EMAIL:[e-mail]
 */
 package cn.org.yinzhengjie.mybatis.domain.many2many;

 import java.util.ArrayList;
 import java.util.List;

 public class Tea {
     private Integer id ;
     private String tname ;

     public Tea(String tname){
         this.tname = tname ;
     }
     public Tea(){
     }
     private List<Stu> stus = new ArrayList<Stu>() ;

     public Integer getId() {
         return id;
     }

     public void setId(Integer id) {
         this.id = id;
     }

     public String getTname() {
         return tname;
     }

     public void setTname(String tname) {
         this.tname = tname;
     }

     public List<Stu> getStus() {
         return stus;
     }

     public void setStus(List<Stu> stus) {
         this.stus = stus;
     }

     public void addStus(Stu...stus){
         for(Stu ss : stus){
             this.getStus().add(ss) ;
             ss.getTeas().add(this) ;
         }
     }
 }

三.编写配置文件

1>.mybatis-config.xml 文件内容（需要开启批处理模式）

 <?xml version="1.0" encoding="UTF-8" ?>

 <!DOCTYPE configuration
         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
         "http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
     <properties>
         <property name="driver" value="com.mysql.jdbc.Driver"/>
         <!--注意 : “?characterEncoding=utf-8&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;allowMultiQueries=true” 表示开启批处理模式-->
         <property name="url" value="jdbc:mysql://localhost:5200/yinzhengjie?characterEncoding=utf-8&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;allowMultiQueries=true"/>
         <property name="username" value="root"/>
         <property name="password" value="yinzhengjie"/>
     </properties>

     <!-- 我们使用typeAliases标签给我们自定义类起个别名。-->
     <typeAliases>
         <typeAlias type="cn.org.yinzhengjie.mybatis.domain.many2many.Tea" alias="_Tea" />
         <typeAlias type="cn.org.yinzhengjie.mybatis.domain.many2many.Stu" alias="_Stu" />
     </typeAliases>

     <environments default="development">
         <environment id="development">
             <transactionManager type="JDBC"/>
             <dataSource type="POOLED">
                 <property name="driver" value="${driver}"/>
                 <property name="url" value="${url}"/>
                 <property name="username" value="${username}"/>
                 <property name="password" value="${password}"/>
             </dataSource>
         </environment>
     </environments>
     <mappers>
         <!-- 我们使用mapper标签指定映射文件，使用resource指定具体的路径，如果没有写绝对路径，默认的根路径就在resources目录中-->
         <mapper resource="TeaMapper.xml"/>
         <mapper resource="StuMapper.xml"/>
     </mappers>
 </configuration>

2>.StuMapper.xml 文件内容

 <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <!-- 定义名字空间 -->
 <mapper namespace="stus">
     <insert id="insert" useGeneratedKeys="true" keyProperty="id">
         insert into stus(sname) values(#{sname}) ;
     </insert>
 </mapper>

3>.TeaMapper.xml 文件内容

 <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <!-- 定义名字空间 -->
 <mapper namespace="teas">
     <insert id="insert" useGeneratedKeys="true" keyProperty="id">
         insert into teas(tname) values(#{tname}) ;
     </insert>

     <update id="updateLinks">
       <foreach collection="stus" item="s">
         insert into links(tid,sid) values(#{id}, #{s.id}) ;
       </foreach>
     </update>

     <delete id="deleteOne">
       delete from links where tid = #{id} ;
       delete from teas where id = #{id}
     </delete>

     <update id="update">
         <!--先删除关系(主键约束)-->
       delete from links where tid = #{id} ;
         <!--更新teas表信息-->
       update teas set tname = #{tname} where id = #{id} ;
         <!--循环插入每个学生信息-->
         <foreach collection="stus" item="s">
             insert into links(tid,sid) values(#{id} , #{s.id}) ;
         </foreach>
     </update>

     <select id="selectOne" resultMap="rm_Tea">
         select
           t.id tid,
           t.tname ttname ,
           s.id sid ,
           s.sname ssname
         from
           teas t
           left outer join links l on l.tid = t.id
           left outer join stus s on s.id = l.sid
         where
           t.id = #{id}
     </select>

     <resultMap id="rm_Tea" type="_Tea">
         <id column="tid" property="id"/>
         <result column="ttname"  property="tname"/>
         <collection property="stus" column="tid" ofType="_Stu">
             <id column="sid" property="id"/>
             <result column="ssname" property="sname"/>
         </collection>
     </resultMap>
 </mapper>

四.编写测试代码

1>.测试多对多代码如下：

 /*
 @author :yinzhengjie
 Blog:http://www.cnblogs.com/yinzhengjie/tag/Java%E5%9F%BA%E7%A1%80/
 EMAIL:[e-mail]
 */
 package cn.org.yinzhengjie.mybatis.test;

 import cn.org.yinzhengjie.mybatis.domain.many2many.Stu;
 import cn.org.yinzhengjie.mybatis.domain.many2many.Tea;
 import org.apache.ibatis.io.Resources;
 import org.apache.ibatis.session.SqlSession;
 import org.apache.ibatis.session.SqlSessionFactory;
 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 import org.junit.Test;

 import java.io.InputStream;

 /**
  * 测试多对多
  */
 public class TestMany2Many {
     @Test
     public void testInsertCustomer() throws Exception {
         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
         SqlSession sess = sf.openSession();
         Tea t1 = new Tea("t1");
         Tea t2 = new Tea("t2");

         Stu s1 = new Stu("s1");
         Stu s2 = new Stu("s2");
         Stu s3 = new Stu("s3");
         Stu s4 = new Stu("s4");

         t1.addStus(s1 , s2 , s3);
         t2.addStus(s2 , s3 , s4);

         sess.insert("teas.insert" , t1);
         sess.insert("teas.insert" , t2);

         sess.insert("stus.insert" , s1);
         sess.insert("stus.insert" , s2);
         sess.insert("stus.insert" , s3);
         sess.insert("stus.insert" , s4);

         sess.update("teas.updateLinks" , t1);
         sess.update("teas.updateLinks" , t2);

         sess.commit();
         sess.close();
     }

     @Test
     public void testDeleteTea() throws Exception {
         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
         SqlSession sess = sf.openSession();
         sess.delete("teas.deleteOne" , 7) ;
         sess.commit();
         sess.close();
     }

     /**
      * 更新
      */
     @Test
     public void testUpdate() throws Exception {
         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
         SqlSession sess = sf.openSession();
         Tea t1 = new Tea("tttt") ;
         t1.setId(8);

         Stu s1 = new Stu();
         s1.setId(13);

         Stu s2 = new Stu();
         s2.setId(14);

         t1.addStus(s1,s2);

         sess.update("teas.update" , t1) ;
         sess.commit();
         sess.close();
     }

     @Test
     public void testSelectOne() throws Exception {
         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
         SqlSession sess = sf.openSession();
         Tea obj = (Tea)sess.selectOne("teas.selectOne", 7);

         System.out.println(obj.getTname());
         sess.commit();
         sess.close();
     }
 }

 2>.统计函数和分页查询

 <?xml version="1.0" encoding="UTF-8" ?>

 <!DOCTYPE configuration
         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
         "http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
     <properties>
         <property name="driver" value="com.mysql.jdbc.Driver"/>
         <!--注意 : “?characterEncoding=utf-8&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;allowMultiQueries=true” 表示开启批处理模式-->
         <property name="url" value="jdbc:mysql://localhost:5200/yinzhengjie?characterEncoding=utf-8&amp;autoReconnect=true&amp;failOverReadOnly=false&amp;allowMultiQueries=true"/>
         <property name="username" value="root"/>
         <property name="password" value="yinzhengjie"/>
     </properties>

     <!-- 我们使用typeAliases标签给我们自定义类起个别名。-->
     <typeAliases>
         <typeAlias type="cn.org.yinzhengjie.mybatis.domain.many2many.Stu"     alias="_Stu" />
         <typeAlias type="cn.org.yinzhengjie.mybatis.domain.many2many.Tea"        alias="_Tea" />
     </typeAliases>

     <environments default="development">
         <environment id="development">
             <transactionManager type="JDBC"/>
             <dataSource type="POOLED">
                 <property name="driver" value="${driver}"/>
                 <property name="url" value="${url}"/>
                 <property name="username" value="${username}"/>
                 <property name="password" value="${password}"/>
             </dataSource>
         </environment>
     </environments>
     <mappers>
         <!-- 我们使用mapper标签指定映射文件，使用resource指定具体的路径，如果没有写绝对路径，默认的根路径就在resources目录中-->
         <mapper resource="StatMapper.xml"/>
     </mappers>
 </configuration>

mybatis-config.xml 文件内容

 <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <!-- 定义名字空间 -->
 <mapper namespace="stats">
     <select id="countStus" resultType="int">
         select count(*) from stus
     </select>

     <select id="selectAllTeas" resultType="map">
       select
         t.id tid ,
         t.tname ttname ,
         s.id sid ,
         s.sname ssname
       from
         teas t
           left outer join links l on l.tid = t.id
           left outer join stus s on s.id = l.sid
     </select>

     <select id="selectStusPage" resultType="_Stu">
         select * from stus where id > #{id} limit #{offset} , #{limit}
     </select>
 </mapper>

StatMapper.xml 文件内容

 /*
 @author :yinzhengjie
 Blog:http://www.cnblogs.com/yinzhengjie/tag/Java%E5%9F%BA%E7%A1%80/
 EMAIL:[e-mail]
 */
 package cn.org.yinzhengjie.mybatis.test;

 import org.apache.ibatis.io.Resources;
 import org.apache.ibatis.session.SqlSession;
 import org.apache.ibatis.session.SqlSessionFactory;
 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 import org.junit.Test;

 import java.io.InputStream;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;

 /**
  * 测试统计
  */
 public class TestStat {
     @Test
     public void testInsert() throws Exception {
         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
         SqlSession sess = sf.openSession();
         int count = sess.selectOne("stats.countStus");
         List<Map<String,Object>> list = sess.selectList("stats.selectAllTeas");
         for(Map<String, Object> map : list){
             for(Map.Entry<String, Object> e : map.entrySet()){
                 System.out.println(e.getKey() + "\t" + e.getValue());
             }
             System.out.println("========================");
         }
         System.out.println(count);
         sess.commit();
         sess.close();
     }

     @Test
     public void testPage() throws Exception {
         InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
         SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
         SqlSession sess = sf.openSession();
         Map<String , Object> map = new HashMap<String,Object>() ;
         map.put("id" , 19) ;
         map.put("offset" , 0) ;
         map.put("limit" , 2) ;
 //        map.put("id" , 19) ;
 //        map.put("page" , new RowBounds(2, 2)) ;
         List<Object> objects = sess.selectList("stats.selectStusPage", map);
         System.out.println(objects);
         sess.commit();
         sess.close();

     }
 }

　　测试的目录结构如下：