多级表查询之业务装配方式

多表关联查询

表结构

学生表（s_student）字段有id、name、age、gender，cid

班级（t_class）,字段有id，name，room

业务装配方式实现多表查询（多对一）

mapper层只做单表查询操作，在service层进行手动装配，实现关联查询的结果。

实体类

创建班级类（Clazz）和学生类（Student），并在student中添加一个Clazz类型属性，用于表示学生的班级信息。

mapper层

提供StudentMapper和ClazzMapper，StudentMapper 查询所有学生的信息，ClazzMapper根据编号查询班级信息。

service层

调用mapper层，先查询所有学生，再根据每个学生的班级编号查询班级信息，手动进行组装，称之为业务装配。

测试代码

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   <select id="selAll" resultType="student">
 7       select * from t_student
 8   </select>
 9  
10  </mapper>
11

 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         ClazzMapper clsMapper = session.getMapper(ClazzMapper.class);
22         List<Student> list = stuMapper.selAll();
23         //为每一个student组装班级信息
24     for (Student student : list) {
25         student.setClazz(clsMapper.selById(student.getCid()));
26     }
27         session.close();
28         return list;
29     }
30 
31 }

 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&amp;characterEncoding=utf8&amp;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

posted @ 2019-01-07 15:24 wq9 阅读(279) 评论(0) 编辑收藏举报

刷新页面返回顶部

wq9