devbox
AllinToyou
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MyBatis实行多表查询几种实现方式_mybatis连表查询

作者：AllinToyou | 2024-04-09 09:25:26

赞

踩

mybatis连表查询

目录

实现多表联查的四种实现方式

本文以以下表举例：

方式一：视图

方式二：存储过程

方式三：配置文件

一对一

一对多

方式四：注解

一对多

一对一

四种方式对比

实现多表联查的四种实现方式

方案一：通过在数据库服务器端创建多表的view，java的dao层读取这个view；

方案二：通过在数据库服务器端创建多表的存储过程，java的dao层调用这个存储过程；

方案三：通过mabatis的xml配置文件方式进行多表联查，在这里需要手动配置映射关系（一对一、一对多）

方案四：通过mabatis的注解方式进行多表联查，在这里需要手动配置映射关系（一对一、一对多）

本文以以下表举例：

  

方式一：视图

        在数据库创建联表的视图，Java读取这个视图

1）数据库创建需要查询的视图

  1. CREATE VIEW student_score as 
  2. select student.*, score.cname,score.sc 
  3. from student 
  4. LEFT JOIN score on student.Sid = score.sid

2）pojo层创建映射类

  1. public class View_StudentScore {
  2. 	private int sid;
  3. 	private String sname;
  4. 	private Date birthday;
  5. 	private String ssex;
  6. 	private int classid;
  7. 	private String cname;
  8. 	private double sc;
  9.  
  10. 	public View_StudentScore() {
  11. 		super();
  12. 	}
  13.  
  14. 	public View_StudentScore(int sid, String sname, Date birthday, String ssex, int classid, String cname, double sc) {
  15. 		super();
  16. 		this.sid = sid;
  17. 		this.sname = sname;
  18. 		this.birthday = birthday;
  19. 		this.ssex = ssex;
  20. 		this.classid = classid;
  21. 		this.cname = cname;
  22. 		this.sc = sc;
  23. 	}
  24.  
  25. 	public int getSid() {
  26. 		return sid;
  27. 	}
  28.  
  29. 	public void setSid(int sid) {
  30. 		this.sid = sid;
  31. 	}
  32.  
  33. 	public String getSname() {
  34. 		return sname;
  35. 	}
  36.  
  37. 	public void setSname(String sname) {
  38. 		this.sname = sname;
  39. 	}
  40.  
  41. 	public Date getBirthday() {
  42. 		return birthday;
  43. 	}
  44.  
  45. 	public void setBirthday(Date birthday) {
  46. 		this.birthday = birthday;
  47. 	}
  48.  
  49. 	public String getSsex() {
  50. 		return ssex;
  51. 	}
  52.  
  53. 	public void setSsex(String ssex) {
  54. 		this.ssex = ssex;
  55. 	}
  56.  
  57. 	public int getClassid() {
  58. 		return classid;
  59. 	}
  60.  
  61. 	public void setClassid(int classid) {
  62. 		this.classid = classid;
  63. 	}
  64.  
  65. 	public String getCname() {
  66. 		return cname;
  67. 	}
  68.  
  69. 	public void setCname(String cname) {
  70. 		this.cname = cname;
  71. 	}
  72.  
  73. 	public double getSc() {
  74. 		return sc;
  75. 	}
  76.  
  77. 	public void setSc(double sc) {
  78. 		this.sc = sc;
  79. 	}
  80.  
  81. 	@Override
  82. 	public String toString() {
  83. 		return "View_StudentScore [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", ssex=" + ssex
  84. 				+ ", classid=" + classid + ", cname=" + cname + ", sc=" + sc + "]";
  85. 	}
  86.  
  87. }

3）mapper层

  1. public interface View_StudentScoreMapper {
  2. 	@Select("select * from View_StudentScore")
  3. 	public List<View_StudentScore> fundAll();
  4. }

4)测试类

  1. public class Test01 {
  2. 	public static void main(String[] args) {
  3. 		SqlSession sqlSession = DaoUtil.getSqlSession();
  4. 		View_StudentScoreMapper mapper = sqlSession.getMapper(View_StudentScoreMapper.class);
  5. 		List<View_StudentScore> viewList = mapper.fundAll();
  6. 		for (View_StudentScore view_StudentScore : viewList) {
  7. 			System.out.println(view_StudentScore);
  8. 		}
  9. 	}
  10. }

5）结果

方式二：存储过程

       在数据库创建联表的存储过程，Java读取这个存储过程

1）在数据库创建存储过程

  1. DELIMITER $$
  2. CREATE
  3.     /*[DEFINER = { user | CURRENT_USER }]*/
  4.     PROCEDURE `pgm`.`student_score`()
  5.     /*LANGUAGE SQL
  6.     | [NOT] DETERMINISTIC
  7.     | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  8.     | SQL SECURITY { DEFINER | INVOKER }
  9.     | COMMENT 'string'*/
  10. 	BEGIN
  11. 		SELECT student.*, score.cname,score.sc 
  12. 		FROM student 
  13. 		LEFT JOIN score ON student.Sid = score.sid ;
  14. 	END$$
  15. DELIMITER ;

2）创建pojo解析对象View_StudentScore（与上面相同，不再过多介绍）

3）mapper

  1. public interface StoredProcedureMapper {
  2.  
  3. 	public List<View_StudentScore> fundAllByStoredProcedure();
  4.  
  5. }
  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.  
  6. <mapper namespace="com.ape.mapper.StoredProcedureMapper">
  7.  
  8. 	<select id="fundAllByStoredProcedure" resultType="com.ape.pojo.View_StudentScore">
  9. 		call student_score();
  10. 	</select>
  11.  
  12.  
  13. </mapper>

4）测试类

  1. public class Test01 {
  2.  
  3. 	public static void main(String[] args) {
  4. 		SqlSession sqlSession = DaoUtil.getSqlSession();
  5. 		StoredProcedureMapper mapper = sqlSession.getMapper(StoredProcedureMapper.class);
  6. 		List<View_StudentScore> viewList = mapper.fundAllByStoredProcedure();
  7. 		for (View_StudentScore view_StudentScore : viewList) {
  8. 			System.out.println(view_StudentScore);
  9. 		}
  10. 	}
  11. }

5）运行结果

方式三：配置文件

        在Java端mybatis框架里写联查sql语句，通过orm模型解析，这里需要考虑一对一以及一对多的关系，按本文的student和score表举例，student对于score是一对多的关系，score对于student来说是一对一的关系；

1）pojo

  1. //成绩类
  2. package com.ape.pojo;
  3.  
  4. public class Score {
  5. 	private int sid;
  6. 	private String cname;
  7. 	private double sc;
  8. 	private Student student; //一对一
  9.  
  10. 	public Score() {
  11. 		super();
  12. 	}
  13.  
  14. 	public Score(int sid, String cname, double sc, Student student) {
  15. 		super();
  16. 		this.sid = sid;
  17. 		this.cname = cname;
  18. 		this.sc = sc;
  19. 		this.student = student;
  20. 	}
  21.  
  22. 	public int getSid() {
  23. 		return sid;
  24. 	}
  25.  
  26. 	public void setSid(int sid) {
  27. 		this.sid = sid;
  28. 	}
  29.  
  30. 	public String getCname() {
  31. 		return cname;
  32. 	}
  33.  
  34. 	public void setCname(String cname) {
  35. 		this.cname = cname;
  36. 	}
  37.  
  38. 	public double getSc() {
  39. 		return sc;
  40. 	}
  41.  
  42. 	public void setSc(double sc) {
  43. 		this.sc = sc;
  44. 	}
  45.  
  46. 	public Student getStudent() {
  47. 		return student;
  48. 	}
  49.  
  50. 	public void setStudent(Student student) {
  51. 		this.student = student;
  52. 	}
  53.  
  54. 	@Override
  55. 	public String toString() {
  56. 		return "Score [sid=" + sid + ", cname=" + cname + ", sc=" + sc + ", student=" + student + "]";
  57. 	}
  58.  
  59. }
  60.  
  61. //学生类
  62. package com.ape.pojo;
  63.  
  64. public class Student {
  65. 	private int sid;
  66. 	private String sname;
  67. 	private Date birthday;
  68. 	private String ssex;
  69. 	private int classid;
  70. 	private List<Score> scList;//一对多
  71.  
  72. 	public Student() {
  73. 		super();
  74. 	}
  75.  
  76. 	public Student(int sid, String sname, Date birthday, String ssex, int classid, List<Score> scList) {
  77. 		super();
  78. 		this.sid = sid;
  79. 		this.sname = sname;
  80. 		this.birthday = birthday;
  81. 		this.ssex = ssex;
  82. 		this.classid = classid;
  83. 		this.scList = scList;
  84. 	}
  85.  
  86. 	@Override
  87. 	public String toString() {
  88. 		return "Student [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", ssex=" + ssex + ", classid="
  89. 				+ classid + ", scList=" + scList + "]";
  90. 	}
  91.  
  92. 	public int getSid() {
  93. 		return sid;
  94. 	}
  95.  
  96. 	public void setSid(int sid) {
  97. 		this.sid = sid;
  98. 	}
  99.  
  100. 	public String getSname() {
  101. 		return sname;
  102. 	}
  103.  
  104. 	public void setSname(String sname) {
  105. 		this.sname = sname;
  106. 	}
  107.  
  108. 	public Date getBirthday() {
  109. 		return birthday;
  110. 	}
  111.  
  112. 	public void setBirthday(Date birthday) {
  113. 		this.birthday = birthday;
  114. 	}
  115.  
  116. 	public String getSsex() {
  117. 		return ssex;
  118. 	}
  119.  
  120. 	public void setSsex(String ssex) {
  121. 		this.ssex = ssex;
  122. 	}
  123.  
  124. 	public int getClassid() {
  125. 		return classid;
  126. 	}
  127.  
  128. 	public void setClassid(int classid) {
  129. 		this.classid = classid;
  130. 	}
  131.  
  132. 	public List<Score> getScList() {
  133. 		return scList;
  134. 	}
  135.  
  136. 	public void setScList(List<Score> scList) {
  137. 		this.scList = scList;
  138. 	}
  139.  
  140. }
  141.

一对一

score作为主表，student作为子表，存在一对一关系映射，使用association标签

2）mapper：

  1. package com.ape.mapper;
  2.  
  3. import java.util.List;
  4.  
  5. import com.ape.pojo.Score;
  6.  
  7. public interface ScoreMapper {
  8.  
  9. 	public List<Score> fundAllScore();
  10.  
  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.  
  6. <mapper namespace="com.ape.mapper.ScoreMapper">
  7.  
  8. 	<resultMap type="com.ape.pojo.Score" id="score_student_map">
  9. 		<result property="sid" column="sid" />
  10. 		<result property="cname" column="cname" />
  11. 		<result property="sc" column="sc" />
  12. 		<association property="student"
  13. 			javaType="com.ape.pojo.Student">
  14. 			<id column="sid" property="sid" />
  15. 			<result property="sname" column="sname" />
  16. 			<result property="birthday" column="birthday" />
  17. 			<result property="ssex" column="Ssex" />
  18. 			<result property="classid" column="classid" />
  19. 		</association>
  20. 	</resultMap>
  21.  
  22. 	<select id="fundAllScore" resultMap="score_student_map">
  23. 		select
  24. 		score.*,student.sname,student.birthday,student.Ssex,student.classid
  25. 		from score LEFT JOIN student on student.Sid = score.sid
  26. 	</select>
  27.  
  28.  
  29. </mapper>

3）测试类：

  1. package com.ape.test;
  2.  
  3. import java.util.List;
  4.  
  5. import org.apache.ibatis.session.SqlSession;
  6.  
  7. import com.ape.dao.DaoUtil;
  8. import com.ape.mapper.ScoreMapper;
  9. import com.ape.pojo.Score;
  10.  
  11. public class Test02 {
  12.  
  13. 	public static void main(String[] args) {
  14. 		SqlSession sqlSession = DaoUtil.getSqlSession();
  15. 		ScoreMapper mapper = sqlSession.getMapper(ScoreMapper.class);
  16. 		List<Score> fundAllScore = mapper.fundAllScore();
  17. 		for (Score Score : fundAllScore) {
  18. 			System.out.println(Score);
  19. 		}
  20. 	}
  21. }

4）结果：

一对多

student作为主表，score作为子表，存在一对多关系映射，使用collection标签

2）mapper：

  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.  
  6. <mapper namespace="com.ape.mapper.StudentMapper">
  7.  
  8. 	<resultMap type="com.ape.pojo.Student" id="student_score_map">
  9. 		<result property="sid" column="sid" />
  10. 		<result property="sname" column="sname" />
  11. 		<result property="birthday" column="birthday" />
  12. 		<result property="ssex" column="ssex" />
  13. 		<result property="classid" column="classid" />
  14. 		<collection property="scList" ofType="com.ape.pojo.Score">
  15. 			<id property="sid" column="sid" />
  16. 			<result property="cname" column="cname" />
  17. 			<result property="sc" column="sc" />
  18. 		</collection>
  19. 	</resultMap>
  20.  
  21. 	<select id="fundAllStudent" resultMap="student_score_map">
  22. 		select
  23. 		student.*,score.cname,score.sc from student
  24. 		LEFT JOIN score on
  25. 		student.Sid = score.sid;
  26. 	</select>
  27.  
  28. </mapper>
  1. package com.ape.mapper;
  2.  
  3. import java.util.List;
  4.  
  5. import com.ape.pojo.Student;
  6.  
  7. public interface StudentMapper {
  8.  
  9. 	public List<Student> fundAllStudent();
  10.  
  11. }

3）测试类：

  1. package com.ape.test;
  2.  
  3. import java.util.List;
  4.  
  5. import org.apache.ibatis.session.SqlSession;
  6.  
  7. import com.ape.dao.DaoUtil;
  8. import com.ape.mapper.StudentMapper;
  9. import com.ape.pojo.Student;
  10.  
  11. public class Test03 {
  12.  
  13. 	public static void main(String[] args) {
  14. 		SqlSession sqlSession = DaoUtil.getSqlSession();
  15. 		StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
  16. 		List<Student> AllStudent = mapper.fundAllStudent();
  17. 		for (Student student : AllStudent) {
  18. 			System.out.println(student);
  19. 		}
  20. 	}
  21. }

4）结果

方式四：注解

1）依旧使用方式三的pojo

一对多

student为主表时，存在一对多关系，使用@Many注解

2）mapper

  1. package com.ape.mapper;
  2.  
  3. import java.util.List;
  4.  
  5. import org.apache.ibatis.annotations.Many;
  6. import org.apache.ibatis.annotations.Param;
  7. import org.apache.ibatis.annotations.Result;
  8. import org.apache.ibatis.annotations.Results;
  9. import org.apache.ibatis.annotations.Select;
  10.  
  11. import com.ape.pojo.Score;
  12. import com.ape.pojo.Student;
  13.  
  14. public interface StudentAnnotationMapper {
  15. 	
  16. 	@Select("select * from student")
  17. 	@Results({
  18. 		@Result(column = "sid",property = "sid"),
  19. 		@Result(column = "sname",property = "sname"),
  20. 		@Result(column = "birthday",property = "birthday"),
  21. 		@Result(column = "ssex",property = "ssex"),
  22. 		@Result(column = "classid",property = "classid"),
  23. 		@Result(property = "scList",column ="sid",many = @Many(select = "com.ape.mapper.StudentAnnotationMapper.fundScoreById") )
  24. 	})
  25. 	public List<Student> fundAllStudent();
  26.  
  27. 	@Select("select * from score where sid = #{sid}")
  28. 	public Score fundScoreById(@Param("sid")int sid);
  29.  
  30. }
  31.

3）测试类：

  1. package com.ape.test;
  2.  
  3. import java.util.List;
  4.  
  5. import org.apache.ibatis.session.SqlSession;
  6.  
  7. import com.ape.dao.DaoUtil;
  8. import com.ape.mapper.StudentAnnotationMapper;
  9. import com.ape.mapper.StudentMapper;
  10. import com.ape.pojo.Student;
  11.  
  12. public class Test04 {
  13.  
  14. 	public static void main(String[] args) {
  15. 		SqlSession sqlSession = DaoUtil.getSqlSession();
  16. 		StudentAnnotationMapper mapper = sqlSession.getMapper(StudentAnnotationMapper.class);
  17. 		List<Student> AllStudent = mapper.fundAllStudent();
  18. 		for (Student student : AllStudent) {
  19. 			System.out.println(student);
  20. 		}
  21. 	}
  22. }

4）结果

一对一

score为主表时，存在一对一关系，使用@One注解

2）mapper

  1. package com.ape.mapper;
  2.  
  3. import java.util.List;
  4.  
  5. import org.apache.ibatis.annotations.Many;
  6. import org.apache.ibatis.annotations.Param;
  7. import org.apache.ibatis.annotations.Result;
  8. import org.apache.ibatis.annotations.Results;
  9. import org.apache.ibatis.annotations.Select;
  10.  
  11. import com.ape.pojo.Score;
  12. import com.ape.pojo.Student;
  13.  
  14. public interface ScoreAnnotationMapper {
  15. 	
  16. 	@Select("select * from score")
  17. 	@Results({
  18. 		@Result(column = "sid",property = "sid"),
  19. 		@Result(column = "cname",property = "cname"),
  20. 		@Result(column = "sc",property = "sc"),
  21. 		@Result(property = "student",column ="sid",one = @One(select = "com.ape.mapper.ScoreAnnotationMapper.fundStudentById") )
  22. 	})
  23. 	public List<Score> fundAllScore();
  24.  
  25. 	@Select("select * from student where sid = #{sid}")
  26. 	public Student fundStudentById(@Param("sid")int sid);
  27.  
  28. }
  29.

3）测试类：

  1. package com.ape.test;
  2.  
  3. import java.util.List;
  4.  
  5. import org.apache.ibatis.session.SqlSession;
  6.  
  7. import com.ape.dao.DaoUtil;
  8. import com.ape.mapper.ScoreAnnotationMapper;
  9. import com.ape.pojo.Score;
  10.  
  11. public class Test05 {
  12.  
  13. 	public static void main(String[] args) {
  14. 		SqlSession sqlSession = DaoUtil.getSqlSession();
  15. 		ScoreAnnotationMapper mapper = sqlSession.getMapper(ScoreAnnotationMapper.class);
  16. 		List<Score> allScore = mapper.fundAllScore();
  17. 		for (Score score : allScore) {
  18. 			System.out.println(score);
  19. 		}
  20. 	}
  21. }

4）结果

四种方式对比

数据库创建视图方式数据库创建存储过程方式mybatis配置文件方式mybatis注解方式
动态SQL的处理Java层处理动态SQL的处理需要到数据库服务器处理Java层处理Java层处理
模糊查询处理Java层处理数据库服务器处理Java层处理Java层处理
性能Java层读取一次如果存储过程含有逻辑，这部分逻辑会分担到数据库服务器，增加服务器负担；只读取一次Java层只读取一次，并且需要理映射问题

多条数据的情况下，会产生java端多次访问数据库服务器，性能最低

（极不推荐）

优缺点     

不需要考虑级联带来的映射问题（一对一，一对多）

需要增加pojo类

不需要考虑级联带来的映射问题（一对一，一对多）

需要增加pojo类

需要考虑级联的问题，不用增加pojo类作者没看出来这个存在优点......
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/AllinToyou/article/detail/391773
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号