MyBatis 关联查询

一 ：一对一查询：

 

建立t_student表和t_card表：

create table T_CARD
(
  CARDID   NUMBER not null,
  CARDNAME VARCHAR2(10)
)；

alter table T_CARD
  add primary key (CARDID)；

create table T_STUDENT
(
  STUDENTID   NUMBER not null,
  STUDENTNAME VARCHAR2(10),
  CARDID      NUMBER
)；

alter table T_STUDENT
  add primary key (STUDENTID)；

alter table T_STUDENT
  add constraint CARDID foreign key (CARDID)
  references T_CARD (CARDID);

创建持久化类：

package com.web.entity;

public class AStudent {
	private Integer studentId;
	private String studentName;
	private ACard acard;
	public Integer getStudentId() {
		return studentId;
	}
	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}
	public String getStudentName() {
		return studentName;
	}
	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}
	public ACard getAcard() {
		return acard;
	}
	public void setAcard(ACard acard) {
		this.acard = acard;
	}
	
}

package com.web.entity;

public class ACard {
	private Integer cardId;
	private String cardName;
	public Integer getCardId() {
		return cardId;
	}
	public void setCardId(Integer cardId) {
		this.cardId = cardId;
	}
	public String getCardName() {
		return cardName;
	}
	public void setCardName(String cardName) {
		this.cardName = cardName;
	}
	
}

映射文件：

<?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.pb.dao.StudentDao">
	<resultMap type="AStudent" id="ts">
		<id property="studentId" column="studentId" />
		<result property="studentName" column="studentName" />
		<association property="acard" column="cardId" javaType="ACard">
			<id property="cardId" column="cardId" />
			<result property="cardName" column="cardName" />
		</association>
	</resultMap>
	<select id="selectonetoone" resultMap="ts" parameterType="AStudent">
		select
		s.studentid,s.studentname,c.cardname from t_student s
		left join t_card c
		on s.cardid=c.cardid
		where s.studentid=#{studentId}
	</select>
	
</mapper>

二：多对一关联查询：

学生表和班级表：

create table classes(
classesid number primary key,
classesname varchar2(10)
);

create table student(
studentid number primary key,
studentname varchar2(10),
classesid number,
CONSTRAINT classesid FOREIGN KEY (classesid) REFERENCES classes(classesid)
)

insert into classes values(1,'1班');
insert into classes values(2,'2班');

insert into student values(1,'aa',1);
insert into student values(2,'a',2);

持久化类：

package com.web.entity;

public class Student {
	private Integer studentId;
	private String studentName;
	private Classes classes;
	public Integer getStudentId() {
		return studentId;
	}
	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}
	public String getStudentName() {
		return studentName;
	}
	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}
	public Classes getClasses() {
		return classes;
	}
	public void setClasses(Classes classes) {
		this.classes = classes;
	}
	
}

package com.web.entity;

import java.util.HashSet;
import java.util.Set;

public class Classes {
	private Integer classesId;
	private String classesName;
	
	public Integer getClassesId() {
		return classesId;
	}
	public void setClassesId(Integer classesId) {
		this.classesId = classesId;
	}
	public String getClassesName() {
		return classesName;
	}
	public void setClassesName(String classesName) {
		this.classesName = classesName;
	}
	
	
}

映射文件：

<?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.pb.dao.StudentDao">
	<resultMap type="Student" id="ts">
		<id property="studentId" column="studentId" />
		<result property="studentName" column="studentName" />
		<association property="classes" column="classesId"
			javaType="Classes">
			<id property="classesId" column="classesId" />
			<result property="classesName" column="classesName" />
		</association>
	</resultMap>
	<select id="selectonetoone" resultMap="ts" parameterType="Student">
		select
		s.studentid,s.studentname,c.classesname
		from
		student s
		left join classes c
		on s.classesid=c.classesid
		where s.studentid=#{studentId}
	</select>

</mapper>

SqlSession:

package com.web.dao;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.web.entity.Student;

public class StudentDao {
	private int res=-1;
	private SqlSession sqlSession=null;
	public  SqlSession getSqlSession(){
		
		try{
			String resource="mybatis-config.xml";
			InputStream is=Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
			sqlSession=sqlSessionFactory.openSession(true);
			
			
		}catch(Exception e){
			System.out.println("出错");
			e.printStackTrace();
		}
		return sqlSession;
	}
	
	
	public Student findTStudentById(Integer id){
		sqlSession=getSqlSession();
		Student s=sqlSession.selectOne("selectonetoone", id);
		return s;
	}
}

测试：

package com.web.test;

import com.web.dao.StudentDao;
import com.web.entity.Student;


public class Test {

	public static void main(String[] args) {
		StudentDao sd=new StudentDao();
		Student a=sd.findTStudentById(1);
		System.out.println(a.getClasses().getClassesName());
	}

}