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依赖
1 <?xml version="1.0" encoding="UTF-8"?> 2 <project xmlns="http://maven.apache.org/POM/4.0.0" 3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 5 <modelVersion>4.0.0</modelVersion> 6 <groupId>cn.org.yinzhengjie</groupId> 7 <artifactId>Mybatis</artifactId> 8 <version>1.0-SNAPSHOT</version> 9 <dependencies> 10 <dependency> 11 <groupId>junit</groupId> 12 <artifactId>junit</artifactId> 13 <version>4.11</version> 14 </dependency> 15 <dependency> 16 <groupId>mysql</groupId> 17 <artifactId>mysql-connector-java</artifactId> 18 <version>5.1.17</version> 19 </dependency> 20 <dependency> 21 <groupId>org.mybatis</groupId> 22 <artifactId>mybatis</artifactId> 23 <version>3.2.1</version> 24 </dependency> 25 </dependencies> 26 </project>
3>.目录结构如下：
二.编写自定义类
1>.Stu.java 文件内容
1 /* 2 @author :yinzhengjie 3 Blog:http://www.cnblogs.com/yinzhengjie/tag/Java%E5%9F%BA%E7%A1%80/ 4 EMAIL:y1053419035@qq.com 5 */ 6 package cn.org.yinzhengjie.mybatis.domain.one2many; 7 8 import java.util.ArrayList; 9 import java.util.List; 10 11 /** 12 * 客户 13 */ 14 public class Customer { 15 private Integer id ; 16 private String name ; 17 private int age ; 18 19 //建立从Customer到Order之间一对多关系,因为一个客户可能会有多个订单。我们将多个订单放在一个list中。 20 private List<Order> orders = new ArrayList<Order>() ; 21 22 public List<Order> getOrders() { 23 return orders; 24 } 25 26 public void setOrders(List<Order> orders) { 27 this.orders = orders; 28 } 29 30 public Integer getId() { 31 return id; 32 } 33 34 public void setId(Integer id) { 35 this.id = id; 36 } 37 38 public String getName() { 39 return name; 40 } 41 42 public void setName(String name) { 43 this.name = name; 44 } 45 46 public int getAge() { 47 return age; 48 } 49 50 public void setAge(int age) { 51 this.age = age; 52 } 53 }
2>.Tea.java 文件内容
1 /* 2 @author :yinzhengjie 3 Blog:http://www.cnblogs.com/yinzhengjie/tag/Java%E5%9F%BA%E7%A1%80/ 4 EMAIL:y1053419035@qq.com 5 */ 6 package cn.org.yinzhengjie.mybatis.domain.many2many; 7 8 import java.util.ArrayList; 9 import java.util.List; 10 11 public class Tea { 12 private Integer id ; 13 private String tname ; 14 15 public Tea(String tname){ 16 this.tname = tname ; 17 } 18 public Tea(){ 19 } 20 private List<Stu> stus = new ArrayList<Stu>() ; 21 22 public Integer getId() { 23 return id; 24 } 25 26 public void setId(Integer id) { 27 this.id = id; 28 } 29 30 public String getTname() { 31 return tname; 32 } 33 34 public void setTname(String tname) { 35 this.tname = tname; 36 } 37 38 public List<Stu> getStus() { 39 return stus; 40 } 41 42 public void setStus(List<Stu> stus) { 43 this.stus = stus; 44 } 45 46 public void addStus(Stu...stus){ 47 for(Stu ss : stus){ 48 this.getStus().add(ss) ; 49 ss.getTeas().add(this) ; 50 } 51 } 52 }
三.编写配置文件
1>.mybatis-config.xml 文件内容（需要开启批处理模式）
1 <?xml version="1.0" encoding="UTF-8" ?> 2 3 <!DOCTYPE configuration 4 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 5 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 6 <configuration> 7 <properties> 8 <property name="driver" value="com.mysql.jdbc.Driver"/> 9 <!--注意 : “?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true” 表示开启批处理模式--> 10 <property name="url" value="jdbc:mysql://localhost:5200/yinzhengjie?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true"/> 11 <property name="username" value="root"/> 12 <property name="password" value="yinzhengjie"/> 13 </properties> 14 15 <!-- 我们使用typeAliases标签给我们自定义类起个别名。--> 16 <typeAliases> 17 <typeAlias type="cn.org.yinzhengjie.mybatis.domain.many2many.Tea" alias="_Tea" /> 18 <typeAlias type="cn.org.yinzhengjie.mybatis.domain.many2many.Stu" alias="_Stu" /> 19 </typeAliases> 20 21 <environments default="development"> 22 <environment id="development"> 23 <transactionManager type="JDBC"/> 24 <dataSource type="POOLED"> 25 <property name="driver" value="${driver}"/> 26 <property name="url" value="${url}"/> 27 <property name="username" value="${username}"/> 28 <property name="password" value="${password}"/> 29 </dataSource> 30 </environment> 31 </environments> 32 <mappers> 33 <!-- 我们使用mapper标签指定映射文件，使用resource指定具体的路径，如果没有写绝对路径，默认的根路径就在resources目录中--> 34 <mapper resource="TeaMapper.xml"/> 35 <mapper resource="StuMapper.xml"/> 36 </mappers> 37 </configuration>
2>.StuMapper.xml 文件内容
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 4 <!-- 定义名字空间 --> 5 <mapper namespace="stus"> 6 <insert id="insert" useGeneratedKeys="true" keyProperty="id"> 7 insert into stus(sname) values(#{sname}) ; 8 </insert> 9 </mapper>
3>.TeaMapper.xml 文件内容
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 4 <!-- 定义名字空间 --> 5 <mapper namespace="teas"> 6 <insert id="insert" useGeneratedKeys="true" keyProperty="id"> 7 insert into teas(tname) values(#{tname}) ; 8 </insert> 9 10 <update id="updateLinks"> 11 <foreach collection="stus" item="s"> 12 insert into links(tid,sid) values(#{id}, #{s.id}) ; 13 </foreach> 14 </update> 15 16 <delete id="deleteOne"> 17 delete from links where tid = #{id} ; 18 delete from teas where id = #{id} 19 </delete> 20 21 22 23 <update id="update"> 24 <!--先删除关系(主键约束)--> 25 delete from links where tid = #{id} ; 26 <!--更新teas表信息--> 27 update teas set tname = #{tname} where id = #{id} ; 28 <!--循环插入每个学生信息--> 29 <foreach collection="stus" item="s"> 30 insert into links(tid,sid) values(#{id} , #{s.id}) ; 31 </foreach> 32 </update> 33 34 <select id="selectOne" resultMap="rm_Tea"> 35 select 36 t.id tid, 37 t.tname ttname , 38 s.id sid , 39 s.sname ssname 40 from 41 teas t 42 left outer join links l on l.tid = t.id 43 left outer join stus s on s.id = l.sid 44 where 45 t.id = #{id} 46 </select> 47 48 <resultMap id="rm_Tea" type="_Tea"> 49 <id column="tid" property="id"/> 50 <result column="ttname" property="tname"/> 51 <collection property="stus" column="tid" ofType="_Stu"> 52 <id column="sid" property="id"/> 53 <result column="ssname" property="sname"/> 54 </collection> 55 </resultMap> 56 </mapper>
四.编写测试代码
1>.测试多对多代码如下：
1 /* 2 @author :yinzhengjie 3 Blog:http://www.cnblogs.com/yinzhengjie/tag/Java%E5%9F%BA%E7%A1%80/ 4 EMAIL:y1053419035@qq.com 5 */ 6 package cn.org.yinzhengjie.mybatis.test; 7 8 import cn.org.yinzhengjie.mybatis.domain.many2many.Stu; 9 import cn.org.yinzhengjie.mybatis.domain.many2many.Tea; 10 import org.apache.ibatis.io.Resources; 11 import org.apache.ibatis.session.SqlSession; 12 import org.apache.ibatis.session.SqlSessionFactory; 13 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 14 import org.junit.Test; 15 16 import java.io.InputStream; 17 18 /** 19 * 测试多对多 20 */ 21 public class TestMany2Many { 22 @Test 23 public void testInsertCustomer() throws Exception { 24 InputStream in = Resources.getResourceAsStream("mybatis-config.xml"); 25 SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in); 26 SqlSession sess = sf.openSession(); 27 Tea t1 = new Tea("t1"); 28 Tea t2 = new Tea("t2"); 29 30 Stu s1 = new Stu("s1"); 31 Stu s2 = new Stu("s2"); 32 Stu s3 = new Stu("s3"); 33 Stu s4 = new Stu("s4"); 34 35 t1.addStus(s1 , s2 , s3); 36 t2.addStus(s2 , s3 , s4); 37 38 sess.insert("teas.insert" , t1); 39 sess.insert("teas.insert" , t2); 40 41 sess.insert("stus.insert" , s1); 42 sess.insert("stus.insert" , s2); 43 sess.insert("stus.insert" , s3); 44 sess.insert("stus.insert" , s4); 45 46 sess.update("teas.updateLinks" , t1); 47 sess.update("teas.updateLinks" , t2); 48 49 sess.commit(); 50 sess.close(); 51 } 52 53 @Test 54 public void testDeleteTea() throws Exception { 55 InputStream in = Resources.getResourceAsStream("mybatis-config.xml"); 56 SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in); 57 SqlSession sess = sf.openSession(); 58 sess.delete("teas.deleteOne" , 7) ; 59 sess.commit(); 60 sess.close(); 61 } 62 63 /** 64 * 更新 65 */ 66 @Test 67 public void testUpdate() throws Exception { 68 InputStream in = Resources.getResourceAsStream("mybatis-config.xml"); 69 SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in); 70 SqlSession sess = sf.openSession(); 71 Tea t1 = new Tea("tttt") ; 72 t1.setId(8); 73 74 Stu s1 = new Stu(); 75 s1.setId(13); 76 77 Stu s2 = new Stu(); 78 s2.setId(14); 79 80 t1.addStus(s1,s2); 81 82 sess.update("teas.update" , t1) ; 83 sess.commit(); 84 sess.close(); 85 } 86 87 @Test 88 public void testSelectOne() throws Exception { 89 InputStream in = Resources.getResourceAsStream("mybatis-config.xml"); 90 SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in); 91 SqlSession sess = sf.openSession(); 92 Tea obj = (Tea)sess.selectOne("teas.selectOne", 7); 93 94 System.out.println(obj.getTname()); 95 sess.commit(); 96 sess.close(); 97 } 98 }
2>.统计函数和分页查询
1 <?xml version="1.0" encoding="UTF-8" ?> 2 3 <!DOCTYPE configuration 4 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 5 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 6 <configuration> 7 <properties> 8 <property name="driver" value="com.mysql.jdbc.Driver"/> 9 <!--注意 : “?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true” 表示开启批处理模式--> 10 <property name="url" value="jdbc:mysql://localhost:5200/yinzhengjie?characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true"/> 11 <property name="username" value="root"/> 12 <property name="password" value="yinzhengjie"/> 13 </properties> 14 15 <!-- 我们使用typeAliases标签给我们自定义类起个别名。--> 16 <typeAliases> 17 <typeAlias type="cn.org.yinzhengjie.mybatis.domain.many2many.Stu" alias="_Stu" /> 18 <typeAlias type="cn.org.yinzhengjie.mybatis.domain.many2many.Tea" alias="_Tea" /> 19 </typeAliases> 20 21 <environments default="development"> 22 <environment id="development"> 23 <transactionManager type="JDBC"/> 24 <dataSource type="POOLED"> 25 <property name="driver" value="${driver}"/> 26 <property name="url" value="${url}"/> 27 <property name="username" value="${username}"/> 28 <property name="password" value="${password}"/> 29 </dataSource> 30 </environment> 31 </environments> 32 <mappers> 33 <!-- 我们使用mapper标签指定映射文件，使用resource指定具体的路径，如果没有写绝对路径，默认的根路径就在resources目录中--> 34 <mapper resource="StatMapper.xml"/> 35 </mappers> 36 </configuration>
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 4 <!-- 定义名字空间 --> 5 <mapper namespace="stats"> 6 <select id="countStus" resultType="int"> 7 select count(*) from stus 8 </select> 9 10 11 <select id="selectAllTeas" resultType="map"> 12 select 13 t.id tid , 14 t.tname ttname , 15 s.id sid , 16 s.sname ssname 17 from 18 teas t 19 left outer join links l on l.tid = t.id 20 left outer join stus s on s.id = l.sid 21 </select> 22 23 <select id="selectStusPage" resultType="_Stu"> 24 select * from stus where id > #{id} limit #{offset} , #{limit} 25 </select> 26 </mapper>