mybatis多表关联查询之resultMap单个对象
resultMap的n+1方式实现多表查询(多对一)
实体类
创建班级类(Clazz)和学生类(Student),并在Student中添加一个Clazz类型的属性,用于表示学生的班级信息。
mapper层
提供studentMapper和ClazzMapper,studentMapper查询所有学生的信息,ClazzMapper根据 编号查询班级信息。再StudentMapper中使用<association>配置装配。
<association> 用于关联一个对象
- property:用于关联一个对象。
- select:设定要继续引用的查询,namespace+id
- column:查询时需要传递的列
service层
由于装配已经完成,serivce层只需调用mapper即可,不需要再进行装配了。
测试代码
多表关联查询_resultMap_单个对象_N+1方式实现
1 package com.bjsxt.mapper; 2 3 import com.bjsxt.pojo.Clazz; 4 5 public interface ClazzMapper { 6 7 Clazz selById(int id); 8 }
1 package com.bjsxt.mapper; 2 3 import java.util.List; 4 5 import com.bjsxt.pojo.Student; 6 7 public interface StudentMapper { 8 List<Student> selAll(); 9 }
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-|3-mapper.dtd"> 5 <mapper namespace="com.bjsxt.mapper.ClazzMapper"> 6 <select id="selById" resultType="clazz" parameterType="int"> 7 select * from t_class where id=#{0} 8 </select> 9 10 </mapper> 11
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-|3-mapper.dtd"> 5 <mapper namespace="com.bjsxt.mapper.StudentMapper"> 6 <resultMap type="student" id="smap"> 7 <id property="id" column="id"/> 8 <result property="name" column="name"/> 9 <result property="age" column="age"/> 10 <result property="gender" column="gender"/> 11 <result property="cid" column="cid"/> 12 <!-- 用于关联一个对象 --> 13 <association property="clazz" select="com.bjsxt.mapper.ClazzMapper.selById" column="cid"></association> 14 </resultMap> 15 <select id="selAll" resultMap="smap"> 16 select * from t_student 17 </select> 18 19 </mapper> 20
1 package com.bjsxt.pojo; 2 3 import java.io.Serializable; 4 5 public class Clazz implements Serializable{ 6 private int id; 7 private String name; 8 private String room; 9 10 public int hashCode() { 11 final int prime = 31; 12 int result = 1; 13 result = prime * result + id; 14 result = prime * result + ((name == null) ? 0 : name.hashCode()); 15 result = prime * result + ((room == null) ? 0 : room.hashCode()); 16 return result; 17 } 18 public boolean equals(Object obj) { 19 if (this == obj) 20 return true; 21 if (obj == null) 22 return false; 23 if (getClass() != obj.getClass()) 24 return false; 25 Clazz other = (Clazz) obj; 26 if (id != other.id) 27 return false; 28 if (name == null) { 29 if (other.name != null) 30 return false; 31 } else if (!name.equals(other.name)) 32 return false; 33 if (room == null) { 34 if (other.room != null) 35 return false; 36 } else if (!room.equals(other.room)) 37 return false; 38 return true; 39 } 40 public String toString() { 41 return "Clazz [id=" + id + ", name=" + name + ", room=" + room + "]"; 42 } 43 public Clazz() { 44 super(); 45 // TODO Auto-generated constructor stub 46 } 47 public Clazz(int id, String name, String room) { 48 super(); 49 this.id = id; 50 this.name = name; 51 this.room = room; 52 } 53 public int getId() { 54 return id; 55 } 56 public void setId(int id) { 57 this.id = id; 58 } 59 public String getName() { 60 return name; 61 } 62 public void setName(String name) { 63 this.name = name; 64 } 65 public String getRoom() { 66 return room; 67 } 68 public void setRoom(String room) { 69 this.room = room; 70 } 71 72 }
1 package com.bjsxt.pojo; 2 3 import java.io.Serializable; 4 5 public class Student implements Serializable{ 6 private int id; 7 private String name; 8 private int age; 9 private String gender; 10 private int cid; 11 private Clazz clazz;//用于封装班级信息 12 public int hashCode() { 13 final int prime = 31; 14 int result = 1; 15 result = prime * result + age; 16 result = prime * result + cid; 17 result = prime * result + ((clazz == null) ? 0 : clazz.hashCode()); 18 result = prime * result + ((gender == null) ? 0 : gender.hashCode()); 19 result = prime * result + id; 20 result = prime * result + ((name == null) ? 0 : name.hashCode()); 21 return result; 22 } 23 public boolean equals(Object obj) { 24 if (this == obj) 25 return true; 26 if (obj == null) 27 return false; 28 if (getClass() != obj.getClass()) 29 return false; 30 Student other = (Student) obj; 31 if (age != other.age) 32 return false; 33 if (cid != other.cid) 34 return false; 35 if (clazz == null) { 36 if (other.clazz != null) 37 return false; 38 } else if (!clazz.equals(other.clazz)) 39 return false; 40 if (gender == null) { 41 if (other.gender != null) 42 return false; 43 } else if (!gender.equals(other.gender)) 44 return false; 45 if (id != other.id) 46 return false; 47 if (name == null) { 48 if (other.name != null) 49 return false; 50 } else if (!name.equals(other.name)) 51 return false; 52 return true; 53 } 54 public String toString() { 55 return "Student [id=" + id + ", name=" + name + ", age=" + age 56 + ", gender=" + gender + ", cid=" + cid + ", clazz=" + clazz + "]"; 57 } 58 public Student(int id, String name, int age, String gender, int cid, Clazz clazz) { 59 super(); 60 this.id = id; 61 this.name = name; 62 this.age = age; 63 this.gender = gender; 64 this.cid = cid; 65 this.clazz = clazz; 66 } 67 public Student() { 68 super(); 69 // TODO Auto-generated constructor stub 70 } 71 public int getId() { 72 return id; 73 } 74 public void setId(int id) { 75 this.id = id; 76 } 77 public String getName() { 78 return name; 79 } 80 public void setName(String name) { 81 this.name = name; 82 } 83 public int getAge() { 84 return age; 85 } 86 public void setAge(int age) { 87 this.age = age; 88 } 89 public String getGender() { 90 return gender; 91 } 92 public void setGender(String gender) { 93 this.gender = gender; 94 } 95 public int getCid() { 96 return cid; 97 } 98 public void setCid(int cid) { 99 this.cid = cid; 100 } 101 public Clazz getClazz() { 102 return clazz; 103 } 104 public void setClazz(Clazz clazz) { 105 this.clazz = clazz; 106 } 107 108 }
1 package com.bjsxt.service; 2 3 import java.util.List; 4 5 import com.bjsxt.pojo.Student; 6 7 public interface StudentService { 8 List<Student> selAll(); 9 10 }
1 package com.bjsxt.service.impl; 2 3 import java.util.List; 4 5 import org.apache.ibatis.session.SqlSession; 6 7 import com.bjsxt.mapper.ClazzMapper; 8 import com.bjsxt.mapper.StudentMapper; 9 import com.bjsxt.pojo.Clazz; 10 import com.bjsxt.pojo.Student; 11 import com.bjsxt.service.StudentService; 12 import com.bjsxt.util.MyBatisUtil; 13 14 public class StudentServiceImpl implements StudentService{ 15 16 @Override 17 public List<Student> selAll() { 18 SqlSession session =MyBatisUtil.getSession(); 19 20 StudentMapper stuMapper = session.getMapper(StudentMapper.class); 21 List<Student> list = stuMapper.selAll(); 22 23 session.close(); 24 return list; 25 } 26 27 }
1 package com.bjsxt.test; 2 3 import java.util.List; 4 5 import com.bjsxt.pojo.Student; 6 import com.bjsxt.service.StudentService; 7 import com.bjsxt.service.impl.StudentServiceImpl; 8 9 public class TestQuery { 10 11 public static void main(String[] args) { 12 StudentService ss=new StudentServiceImpl(); 13 List<Student> list = ss.selAll(); 14 for (Student student : list) { 15 System.out.println(student); 16 } 17 18 19 } 20 21 }
1 package com.bjsxt.util; 2 3 import java.io.IOException; 4 import java.io.InputStream; 5 6 import org.apache.ibatis.io.Resources; 7 import org.apache.ibatis.session.SqlSession; 8 import org.apache.ibatis.session.SqlSessionFactory; 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 10 11 public class MyBatisUtil { 12 13 private static SqlSessionFactory factory=null; 14 static{ 15 16 try { 17 InputStream is=Resources.getResourceAsStream("mybatis.xml"); 18 factory=new SqlSessionFactoryBuilder().build(is); 19 } catch (IOException e) { 20 // TODO Auto-generated catch block 21 e.printStackTrace(); 22 } 23 } 24 25 public static SqlSession getSession(){ 26 SqlSession session =null; 27 if(factory!=null){ 28 //true表示开启 29 session= factory.openSession(true); 30 } 31 return session; 32 } 33 }
1 jdbc.driver=com.mysql.jdbc.Driver 2 jdbc.url=jdbc:mysql://localhost:3306/java505?useSSL=true&characterEncoding=utf8&useSSL=true 3 jdbc.username=root 4 jdbc.password=root
1 # Set root category priority to INFO and its only appender to CONSOLE. 2 log4j.rootCategory=ERROR, CONSOLE 3 # log4j.rootCategory=DEBUG, CONSOLE, LOGFILE 4 5 # 单独设置SQL语句的输出级别为DEBUG级别 6 log4j.logger.com.bjsxt.mapper=DEBUG 7 8 # CONSOLE is set to be a ConsoleAppender using a PatternLayout. 9 log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender 10 log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout 11 log4j.appender.CONSOLE.layout.ConversionPattern=- %m%n 12 13 # LOGFILE is set to be a File appender using a PatternLayout. 14 log4j.appender.LOGFILE=org.apache.log4j.FileAppender 15 log4j.appender.LOGFILE.File=d:/test.log 16 log4j.appender.LOGFILE.Append=true 17 log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout 18 log4j.appender.LOGFILE.layout.ConversionPattern=- %m %l%n
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE configuration 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 <configuration> 6 <properties resource="db.properties"/> 7 <settings> 8 <!-- 设置MyBatis使用log4j日志支持 --> 9 <setting name="logImpl" value="LOG4J"/> 10 </settings> 11 <!-- typeAliases给类型取别名 --> 12 <typeAliases> 13 <!-- 给user类取别名 --> 14 <!-- <typeAlias type="com.bjsxt.pojo.User" alias="u"/> --> 15 <!-- 别名都是类的名字 --> 16 <package name="com.bjsxt.pojo"/> 17 </typeAliases> 18 <!-- 用于指定使用哪个开发 19 用于指定使用的环境id 20 --> 21 <environments default="dev"> 22 <!-- 用于配置开发环境 23 id:环境的唯一识别码 24 --> 25 <environment id="dev"> 26 <!-- 事务管理器 27 type:用于设定mybatis采用什么方式管理事务 28 JDBC表示和JDBC一样事务的管理方式 29 --> 30 <transactionManager type="JDBC"/> 31 <!-- 数据源/连接池 32 用于配置链接池和数据库链接的参数 33 type:用于设置mybatis是否采用链接池技术 34 连接池:用来存数据库链接的,减少数据库的频繁开关 35 POOLED表示mybatis采用连接池技术 36 --> 37 <dataSource type="POOLED"> 38 <property name="driver" value="${jdbc.driver}"/> 39 <property name="url" value="${jdbc.url}"/> 40 <property name="username" value="${jdbc.username}"/> 41 <property name="password" value="${jdbc.password}"/> 42 </dataSource> 43 </environment> 44 </environments> 45 <!-- 扫描mapper文件 --> 46 <!-- 文件的全限制路径要用/ --> 47 <mappers> 48 <!-- <mapper class="com.bjsxt.mapper.UserMapper"/> --> 49 <package name="com.bjsxt.mapper"/> 50 51 </mappers> 52 </configuration>
多表关联查询_resultMap_单个对象_关联方式实现
package com.bjsxt.mapper; import java.util.List; import com.bjsxt.pojo.Student; public interface StudentMapper { List<Student> selAll(); }
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 <mapper namespace="com.bjsxt.mapper.StudentMapper"> 6 <resultMap type="student" id="smap"> 7 <id property="id" column="sid" /> 8 <result property="name" column="sname" /> 9 <result property="age" column="age" /> 10 <result property="gender" column="gender" /> 11 <result property="cid" column="cid" /> 12 <association property="clazz" javaType="clazz"> 13 <id property="id" column="cid" /> 14 <result property="name" column="cname" /> 15 <result property="room" column="room" /> 16 </association> 17 </resultMap> 18 <select id="selAll" resultMap="smap"> 19 select s.id sid, s.name sname, s.age, s.gender, c.id cid, c.name cname, c.room 20 from t_student s 21 left join t_class c 22 on s.cid=c.id 23 </select> 24 </mapper>
1 package com.bjsxt.pojo; 2 3 import java.io.Serializable; 4 5 public class Clazz implements Serializable { 6 7 private int id; 8 private String name; 9 private String room; 10 11 public Clazz() { 12 super(); 13 } 14 15 public int getId() { 16 return id; 17 } 18 19 public void setId(int id) { 20 this.id = id; 21 } 22 23 public String getName() { 24 return name; 25 } 26 27 public void setName(String name) { 28 this.name = name; 29 } 30 31 public String getRoom() { 32 return room; 33 } 34 35 public void setRoom(String room) { 36 this.room = room; 37 } 38 39 @Override 40 public int hashCode() { 41 final int prime = 31; 42 int result = 1; 43 result = prime * result + id; 44 result = prime * result + ((name == null) ? 0 : name.hashCode()); 45 result = prime * result + ((room == null) ? 0 : room.hashCode()); 46 return result; 47 } 48 49 @Override 50 public boolean equals(Object obj) { 51 if (this == obj) 52 return true; 53 if (obj == null) 54 return false; 55 if (getClass() != obj.getClass()) 56 return false; 57 Clazz other = (Clazz) obj; 58 if (id != other.id) 59 return false; 60 if (name == null) { 61 if (other.name != null) 62 return false; 63 } else if (!name.equals(other.name)) 64 return false; 65 if (room == null) { 66 if (other.room != null) 67 return false; 68 } else if (!room.equals(other.room)) 69 return false; 70 return true; 71 } 72 73 @Override 74 public String toString() { 75 return "Clazz [id=" + id + ", name=" + name + ", room=" + room + "]"; 76 } 77 }
1 package com.bjsxt.pojo; 2 3 import java.io.Serializable; 4 5 public class Student implements Serializable { 6 7 private int id; 8 private String name; 9 private int age; 10 private String gender; 11 private int cid; 12 private Clazz clazz;// 用于封装班级信息 13 14 public Student() { 15 super(); 16 } 17 18 public int getId() { 19 return id; 20 } 21 22 public void setId(int id) { 23 this.id = id; 24 } 25 26 public String getName() { 27 return name; 28 } 29 30 public void setName(String name) { 31 this.name = name; 32 } 33 34 public int getAge() { 35 return age; 36 } 37 38 public void setAge(int age) { 39 this.age = age; 40 } 41 42 public String getGender() { 43 return gender; 44 } 45 46 public void setGender(String gender) { 47 this.gender = gender; 48 } 49 50 public int getCid() { 51 return cid; 52 } 53 54 public void setCid(int cid) { 55 this.cid = cid; 56 } 57 58 public Clazz getClazz() { 59 return clazz; 60 } 61 62 public void setClazz(Clazz clazz) { 63 this.clazz = clazz; 64 } 65 66 @Override 67 public int hashCode() { 68 final int prime = 31; 69 int result = 1; 70 result = prime * result + age; 71 result = prime * result + cid; 72 result = prime * result + ((clazz == null) ? 0 : clazz.hashCode()); 73 result = prime * result + ((gender == null) ? 0 : gender.hashCode()); 74 result = prime * result + id; 75 result = prime * result + ((name == null) ? 0 : name.hashCode()); 76 return result; 77 } 78 79 @Override 80 public boolean equals(Object obj) { 81 if (this == obj) 82 return true; 83 if (obj == null) 84 return false; 85 if (getClass() != obj.getClass()) 86 return false; 87 Student other = (Student) obj; 88 if (age != other.age) 89 return false; 90 if (cid != other.cid) 91 return false; 92 if (clazz == null) { 93 if (other.clazz != null) 94 return false; 95 } else if (!clazz.equals(other.clazz)) 96 return false; 97 if (gender == null) { 98 if (other.gender != null) 99 return false; 100 } else if (!gender.equals(other.gender)) 101 return false; 102 if (id != other.id) 103 return false; 104 if (name == null) { 105 if (other.name != null) 106 return false; 107 } else if (!name.equals(other.name)) 108 return false; 109 return true; 110 } 111 112 @Override 113 public String toString() { 114 return "Student [id=" + id + ", name=" + name + ", age=" + age + ", gender=" + gender + ", cid=" + cid 115 + ", clazz=" + clazz + "]"; 116 } 117 }
1 package com.bjsxt.service; 2 3 import java.util.List; 4 5 import com.bjsxt.pojo.Student; 6 7 public interface StudentService { 8 9 List<Student> selAll(); 10 }
1 package com.bjsxt.service.impl; 2 3 import java.util.List; 4 5 import org.apache.ibatis.session.SqlSession; 6 7 import com.bjsxt.mapper.StudentMapper; 8 import com.bjsxt.pojo.Student; 9 import com.bjsxt.service.StudentService; 10 import com.bjsxt.util.MyBatisUtil; 11 12 public class StudentServiceImpl implements StudentService { 13 14 public List<Student> selAll() { 15 SqlSession session = MyBatisUtil.getSession(); 16 17 // 学生mapper 18 StudentMapper stuMapper = session.getMapper(StudentMapper.class); 19 20 List<Student> list = stuMapper.selAll(); 21 22 session.close(); 23 return list; 24 } 25 26 }
1 package com.bjsxt.test; 2 3 import java.util.List; 4 5 import com.bjsxt.pojo.Student; 6 import com.bjsxt.service.StudentService; 7 import com.bjsxt.service.impl.StudentServiceImpl; 8 9 public class TestQuery { 10 11 public static void main(String[] args) { 12 StudentService ss = new StudentServiceImpl(); 13 List<Student> list = ss.selAll(); 14 for (Student student : list) { 15 System.out.println(student); 16 } 17 } 18 19 }
1 package com.bjsxt.util; 2 3 import java.io.IOException; 4 import java.io.InputStream; 5 6 import org.apache.ibatis.io.Resources; 7 import org.apache.ibatis.session.SqlSession; 8 import org.apache.ibatis.session.SqlSessionFactory; 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 10 11 public class MyBatisUtil { 12 13 private static SqlSessionFactory factory = null; 14 15 static { 16 try { 17 InputStream is = Resources.getResourceAsStream("mybatis-cfg.xml"); 18 factory = new SqlSessionFactoryBuilder().build(is); 19 } catch (IOException e) { 20 e.printStackTrace(); 21 } 22 } 23 24 public static SqlSession getSession() { 25 SqlSession session = null; 26 if (factory != null) { 27 // true表示开启自动提交 28 // session = factory.openSession(true); 29 session = factory.openSession(); 30 } 31 return session; 32 } 33 }
1 jdbc.driver=com.mysql.jdbc.Driver 2 jdbc.url=jdbc:mysql://localhost:3306/java505 3 jdbc.username=root 4 jdbc.password=root
1 # Set root category priority to INFO and its only appender to CONSOLE. 2 log4j.rootCategory=ERROR, CONSOLE 3 # log4j.rootCategory=DEBUG, CONSOLE, LOGFILE 4 5 # 单独设置SQL语句的输出级别为DEBUG级别 6 log4j.logger.com.bjsxt.mapper=DEBUG 7 8 # CONSOLE is set to be a ConsoleAppender using a PatternLayout. 9 log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender 10 log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout 11 log4j.appender.CONSOLE.layout.ConversionPattern=- %m%n 12 13 # LOGFILE is set to be a File appender using a PatternLayout. 14 log4j.appender.LOGFILE=org.apache.log4j.FileAppender 15 log4j.appender.LOGFILE.File=d:/test.log 16 log4j.appender.LOGFILE.Append=true 17 log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout 18 log4j.appender.LOGFILE.layout.ConversionPattern=- %m %l%n
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration 3 PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 5 <configuration> 6 <!-- properties加载外部文件 --> 7 <properties resource="db.properties" /> 8 <!-- settings标签 --> 9 <settings> 10 <!-- 设置MyBatis使用log4j日志支持 --> 11 <setting name="logImpl" value="LOG4J"/> 12 </settings> 13 <!-- typeAliases给类型起别名 --> 14 <typeAliases> 15 <package name="com.bjsxt.pojo" /> 16 </typeAliases> 17 <environments default="dev"> 18 <environment id="dev"> 19 <transactionManager type="JDBC" /> 20 <dataSource type="POOLED"> 21 <property name="driver" value="${jdbc.driver}"/> 22 <property name="url" value="${jdbc.url}"/> 23 <property name="username" value="${jdbc.username}"/> 24 <property name="password" value="${jdbc.password}"/> 25 </dataSource> 26 </environment> 27 </environments> 28 <mappers> 29 <package name="com.bjsxt.mapper" /> 30 </mappers> 31 </configuration>
个人学习笔记,记录日常学习,便于查阅及加深,仅为方便个人使用。