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

目录

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

本文以以下表举例：

方式一：视图

方式二：存储过程

方式三：配置文件

一对一

一对多

方式四：注解

一对多

一对一

四种方式对比

---

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

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

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

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

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

本文以以下表举例：

  

方式一：视图

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

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

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

2）pojo层创建映射类

public class View_StudentScore {
	private int sid;
	private String sname;
	private Date birthday;
	private String ssex;
	private int classid;
	private String cname;
	private double sc;

	public View_StudentScore() {
		super();
	}

	public View_StudentScore(int sid, String sname, Date birthday, String ssex, int classid, String cname, double sc) {
		super();
		this.sid = sid;
		this.sname = sname;
		this.birthday = birthday;
		this.ssex = ssex;
		this.classid = classid;
		this.cname = cname;
		this.sc = sc;
	}

	public int getSid() {
		return sid;
	}

	public void setSid(int sid) {
		this.sid = sid;
	}

	public String getSname() {
		return sname;
	}

	public void setSname(String sname) {
		this.sname = sname;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public String getSsex() {
		return ssex;
	}

	public void setSsex(String ssex) {
		this.ssex = ssex;
	}

	public int getClassid() {
		return classid;
	}

	public void setClassid(int classid) {
		this.classid = classid;
	}

	public String getCname() {
		return cname;
	}

	public void setCname(String cname) {
		this.cname = cname;
	}

	public double getSc() {
		return sc;
	}

	public void setSc(double sc) {
		this.sc = sc;
	}

	@Override
	public String toString() {
		return "View_StudentScore [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", ssex=" + ssex
				+ ", classid=" + classid + ", cname=" + cname + ", sc=" + sc + "]";
	}

}

3）mapper层

public interface View_StudentScoreMapper {
	@Select("select * from View_StudentScore")
	public List<View_StudentScore> fundAll();
}

4)测试类

public class Test01 {
	public static void main(String[] args) {
		SqlSession sqlSession = DaoUtil.getSqlSession();
		View_StudentScoreMapper mapper = sqlSession.getMapper(View_StudentScoreMapper.class);
		List<View_StudentScore> viewList = mapper.fundAll();
		for (View_StudentScore view_StudentScore : viewList) {
			System.out.println(view_StudentScore);
		}
	}
}

5）结果

方式二：存储过程

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

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

DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `pgm`.`student_score`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		SELECT student.*, score.cname,score.sc 
		FROM student 
		LEFT JOIN score ON student.Sid = score.sid ;
	END$$
DELIMITER ;

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

3）mapper

public interface StoredProcedureMapper {

	public List<View_StudentScore> fundAllByStoredProcedure();

}

<?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="com.ape.mapper.StoredProcedureMapper">

	<select id="fundAllByStoredProcedure" resultType="com.ape.pojo.View_StudentScore">
		call student_score();
	</select>


</mapper>

4）测试类

public class Test01 {

	public static void main(String[] args) {
		SqlSession sqlSession = DaoUtil.getSqlSession();
		StoredProcedureMapper mapper = sqlSession.getMapper(StoredProcedureMapper.class);
		List<View_StudentScore> viewList = mapper.fundAllByStoredProcedure();
		for (View_StudentScore view_StudentScore : viewList) {
			System.out.println(view_StudentScore);
		}
	}
}

5）运行结果

方式三：配置文件

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

1）pojo

//成绩类
package com.ape.pojo;

public class Score {
	private int sid;
	private String cname;
	private double sc;
	private Student student; //一对一

	public Score() {
		super();
	}

	public Score(int sid, String cname, double sc, Student student) {
		super();
		this.sid = sid;
		this.cname = cname;
		this.sc = sc;
		this.student = student;
	}

	public int getSid() {
		return sid;
	}

	public void setSid(int sid) {
		this.sid = sid;
	}

	public String getCname() {
		return cname;
	}

	public void setCname(String cname) {
		this.cname = cname;
	}

	public double getSc() {
		return sc;
	}

	public void setSc(double sc) {
		this.sc = sc;
	}

	public Student getStudent() {
		return student;
	}

	public void setStudent(Student student) {
		this.student = student;
	}

	@Override
	public String toString() {
		return "Score [sid=" + sid + ", cname=" + cname + ", sc=" + sc + ", student=" + student + "]";
	}

}

//学生类
package com.ape.pojo;

public class Student {
	private int sid;
	private String sname;
	private Date birthday;
	private String ssex;
	private int classid;
	private List<Score> scList;//一对多

	public Student() {
		super();
	}

	public Student(int sid, String sname, Date birthday, String ssex, int classid, List<Score> scList) {
		super();
		this.sid = sid;
		this.sname = sname;
		this.birthday = birthday;
		this.ssex = ssex;
		this.classid = classid;
		this.scList = scList;
	}

	@Override
	public String toString() {
		return "Student [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", ssex=" + ssex + ", classid="
				+ classid + ", scList=" + scList + "]";
	}

	public int getSid() {
		return sid;
	}

	public void setSid(int sid) {
		this.sid = sid;
	}

	public String getSname() {
		return sname;
	}

	public void setSname(String sname) {
		this.sname = sname;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public String getSsex() {
		return ssex;
	}

	public void setSsex(String ssex) {
		this.ssex = ssex;
	}

	public int getClassid() {
		return classid;
	}

	public void setClassid(int classid) {
		this.classid = classid;
	}

	public List<Score> getScList() {
		return scList;
	}

	public void setScList(List<Score> scList) {
		this.scList = scList;
	}

}

一对一

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

2）mapper：

package com.ape.mapper;

import java.util.List;

import com.ape.pojo.Score;

public interface ScoreMapper {

	public List<Score> fundAllScore();

}

<?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="com.ape.mapper.ScoreMapper">

	<resultMap type="com.ape.pojo.Score" id="score_student_map">
		<result property="sid" column="sid" />
		<result property="cname" column="cname" />
		<result property="sc" column="sc" />
		<association property="student"
			javaType="com.ape.pojo.Student">
			<id column="sid" property="sid" />
			<result property="sname" column="sname" />
			<result property="birthday" column="birthday" />
			<result property="ssex" column="Ssex" />
			<result property="classid" column="classid" />
		</association>
	</resultMap>

	<select id="fundAllScore" resultMap="score_student_map">
		select
		score.*,student.sname,student.birthday,student.Ssex,student.classid
		from score LEFT JOIN student on student.Sid = score.sid
	</select>


</mapper>

3）测试类：

package com.ape.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.ape.dao.DaoUtil;
import com.ape.mapper.ScoreMapper;
import com.ape.pojo.Score;

public class Test02 {

	public static void main(String[] args) {
		SqlSession sqlSession = DaoUtil.getSqlSession();
		ScoreMapper mapper = sqlSession.getMapper(ScoreMapper.class);
		List<Score> fundAllScore = mapper.fundAllScore();
		for (Score Score : fundAllScore) {
			System.out.println(Score);
		}
	}
}

4）结果：

一对多

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

2）mapper：

<?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="com.ape.mapper.StudentMapper">

	<resultMap type="com.ape.pojo.Student" id="student_score_map">
		<result property="sid" column="sid" />
		<result property="sname" column="sname" />
		<result property="birthday" column="birthday" />
		<result property="ssex" column="ssex" />
		<result property="classid" column="classid" />
		<collection property="scList" ofType="com.ape.pojo.Score">
			<id property="sid" column="sid" />
			<result property="cname" column="cname" />
			<result property="sc" column="sc" />
		</collection>
	</resultMap>

	<select id="fundAllStudent" resultMap="student_score_map">
		select
		student.*,score.cname,score.sc from student
		LEFT JOIN score on
		student.Sid = score.sid;
	</select>

</mapper>

package com.ape.mapper;

import java.util.List;

import com.ape.pojo.Student;

public interface StudentMapper {

	public List<Student> fundAllStudent();

}

3）测试类：

package com.ape.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.ape.dao.DaoUtil;
import com.ape.mapper.StudentMapper;
import com.ape.pojo.Student;

public class Test03 {

	public static void main(String[] args) {
		SqlSession sqlSession = DaoUtil.getSqlSession();
		StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
		List<Student> AllStudent = mapper.fundAllStudent();
		for (Student student : AllStudent) {
			System.out.println(student);
		}
	}
}

4）结果

方式四：注解

1）依旧使用方式三的pojo

一对多

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

2）mapper

package com.ape.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import com.ape.pojo.Score;
import com.ape.pojo.Student;

public interface StudentAnnotationMapper {
	
	@Select("select * from student")
	@Results({
		@Result(column = "sid",property = "sid"),
		@Result(column = "sname",property = "sname"),
		@Result(column = "birthday",property = "birthday"),
		@Result(column = "ssex",property = "ssex"),
		@Result(column = "classid",property = "classid"),
		@Result(property = "scList",column ="sid",many = @Many(select = "com.ape.mapper.StudentAnnotationMapper.fundScoreById") )
	})
	public List<Student> fundAllStudent();

	@Select("select * from score where sid = #{sid}")
	public Score fundScoreById(@Param("sid")int sid);

}

3）测试类：

package com.ape.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.ape.dao.DaoUtil;
import com.ape.mapper.StudentAnnotationMapper;
import com.ape.mapper.StudentMapper;
import com.ape.pojo.Student;

public class Test04 {

	public static void main(String[] args) {
		SqlSession sqlSession = DaoUtil.getSqlSession();
		StudentAnnotationMapper mapper = sqlSession.getMapper(StudentAnnotationMapper.class);
		List<Student> AllStudent = mapper.fundAllStudent();
		for (Student student : AllStudent) {
			System.out.println(student);
		}
	}
}

4）结果

一对一

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

2）mapper

package com.ape.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import com.ape.pojo.Score;
import com.ape.pojo.Student;

public interface ScoreAnnotationMapper {
	
	@Select("select * from score")
	@Results({
		@Result(column = "sid",property = "sid"),
		@Result(column = "cname",property = "cname"),
		@Result(column = "sc",property = "sc"),
		@Result(property = "student",column ="sid",one = @One(select = "com.ape.mapper.ScoreAnnotationMapper.fundStudentById") )
	})
	public List<Score> fundAllScore();

	@Select("select * from student where sid = #{sid}")
	public Student fundStudentById(@Param("sid")int sid);

}

3）测试类：

package com.ape.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.ape.dao.DaoUtil;
import com.ape.mapper.ScoreAnnotationMapper;
import com.ape.pojo.Score;

public class Test05 {

	public static void main(String[] args) {
		SqlSession sqlSession = DaoUtil.getSqlSession();
		ScoreAnnotationMapper mapper = sqlSession.getMapper(ScoreAnnotationMapper.class);
		List<Score> allScore = mapper.fundAllScore();
		for (Score score : allScore) {
			System.out.println(score);
		}
	}
}

4）结果

四种方式对比

	数据库创建视图方式	数据库创建存储过程方式	mybatis配置文件方式	mybatis注解方式
动态SQL的处理	Java层处理	动态SQL的处理需要到数据库服务器处理	Java层处理	Java层处理
模糊查询处理	Java层处理	数据库服务器处理	Java层处理	Java层处理
性能	Java层读取一次	如果存储过程含有逻辑，这部分逻辑会分担到数据库服务器，增加服务器负担；只读取一次	Java层只读取一次，并且需要理映射问题	多条数据的情况下，会产生java端多次访问数据库服务器，性能最低 （极不推荐）
优缺点	不需要考虑级联带来的映射问题（一对一，一对多） 需要增加pojo类	不需要考虑级联带来的映射问题（一对一，一对多） 需要增加pojo类	需要考虑级联的问题，不用增加pojo类	作者没看出来这个存在优点......