MyBatis 级联查询一对一与一对多-白红宇

MyBatis 级联查询一对一与一对多

阅读量：5109 次

发布时间：2019-06-13

本文共 12849 字，大约阅读时间需要 42 分钟。

mybatis是通过映射sql语句把关系模型(数据库中的表)与领域模型(java中的实体类)关联起的

简单分析级联查询：
关系模型中：表与表只有主外键关联
领域模型中：实体类与实体类这间关联，只有一和多的关系。
一是指别一个实体类以对象属性存在当前实体类中。
多是指别一个实体类以集合对象属性存在当前实体类中
下面我以例子的形式给大家说明:
数据库脚本如下：
drop table student;

drop table classes;

drop table teacher;

create table student

(

stuId number(4) primary key,

stuName varchar2(20) not null,

stuSex varchar2(4),

stuBirthday date,

classId number(4)

);

create table classes

(

clsId number(4) primary key,

clsName varchar2(20) not null,

teacherId number(4)

);

create table teacher

(

teacherId number(4) primary key,

teacherName varchar2(20) not null,

workYear number(2),

professional varchar2(20)

);

insert into student values(1001, '张三', '男', to_date('1989-12-2', 'yyyy-mm-dd'), 3001);

insert into student values(1002, '王五', '女', to_date('1991-6-21', 'yyyy-mm-dd'), 3001);

insert into student values(1003, '赵六', '女', to_date('1990-1-15', 'yyyy-mm-dd'), 3001);

insert into student values(1004, '田七', '男', to_date('1992-11-25', 'yyyy-mm-dd'), 3002);

insert into classes values(3001, '计本1103', 5001);

insert into classes values(3002, '信本1102', 5002);

insert into teacher values(5001, '李四', 3, '计算机科学与技术');

insert into teacher values(5002, '王八', 5, '计算机信息');

commit;

mybatis.xml配制文件

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE configuration

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

" ">

</typeAliases>

</dataSource>

</environment>

</environments>

</mappers>

</configuration>

mybatis工具类

package com.yc.mybatis.util;

import java.io.IOException;

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;

public class MyBatisUtil

{

private static SqlSessionFactory sqlSessionFactory;

static

{

try

{

InputStream in = Resources.getResourceAsStream("mybatis.xml");

sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

}

catch (IOException e)

{

throw new RuntimeException("读取mybatis.xml配制失败", e);

}

public static SqlSession getSession()

{

SqlSession session = null;

if (sqlSessionFactory != null)

{

session = sqlSessionFactory.openSession();

}

return session;

}

public static SqlSession getSession(boolean isAuto)

{

SqlSession session = null;

if (sqlSessionFactory != null)

{

session = sqlSessionFactory.openSession(isAuto);

}

return session;

}

public static void close(SqlSession session)

{

if (session != null)

{

session.close();

}

MyBatis 级联查询一对一实体类(Classes与Teacher):

Classes类

package com.yc.mybatis.entity;

public class Classes

{

private String clsId;

private String clsName;

private Teacher teacher;

public String getClsId()

{

return clsId;

}

public void setClsId(String clsId)

{

this.clsId = clsId;

}

public String getClsName()

{

return clsName;

}

public void setClsName(String clsName)

{

this.clsName = clsName;

}

public Teacher getTeacher()

{

return teacher;

}

public void setTeacher(Teacher teacher)

{

this.teacher = teacher;

}

@Override

public String toString()

{

return "Classes [clsId=" + clsId + ", clsName=" + clsName + ", teacher=" + teacher + "]";

}

Teacher类

package com.yc.mybatis.entity;

public class Teacher

{

private Integer teacherId;

private String teacherName;

private Integer workYear;

private String professional;

public Integer getTeacherId()

{

return teacherId;

}

public void setTeacherId(Integer teacherId)

{

this.teacherId = teacherId;

}

public String getTeacherName()

{

return teacherName;

}

public void setTeacherName(String teacherName)

{

this.teacherName = teacherName;

}

public Integer getWorkYear()

{

return workYear;

}

public void setWorkYear(Integer workYear)

{

this.workYear = workYear;

}

public String getProfessional()

{

return professional;

}

public void setProfessional(String professional)

{

this.professional = professional;

}

@Override

public String toString()

{

return "Teacher [teacherId=" + teacherId + ", teacherName=" + teacherName + ", workYear=" + workYear + ", professional=" + professional + "]";

}

MyBatis 级联查询一对一映射sql语句第一种

ClassesMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

" ">

<!-- 嵌套查询

select * from classes where clsId = 3001; 通过clsId = 3001,获得teacherId = 5001

select * from teacher where teacherId = 5001; 然后通过查询到的eacherId = 5001查询

-->

<association property="teacher" column="teacherId"

select="com.yc.mybatis.mapper.TeacherMapper.getTeacherById"/>

</resultMap>

select * from classes where clsId = #{id}

</select>

</mapper>

TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

" ">

select * from teacher where teacherId = #{teacherId}

</select>

</mapper>

MyBatis 级联查询一对一映射sql语句第二种

ClassesMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

" ">

<!-- 嵌套结果连接查询: 一次查询出所有数据

select * from classes c join teacher t on c.clsId = 3001 and c.teacherId = t.teacherId -->

<!-- 第一种方法直接映射字段

</association> -->

<association property="teacher"

resultMap="com.yc.mybatis.mapper.TeacherMapper.TeacherMap"/>

</resultMap>

select * from classes c join teacher t on c.clsId = #{id} and c.teacherId = t.teacherId

</select>

</mapper>

TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

" ">

</resultMap>

</mapper>

MyBatis 级联查询一对一映射接口

package com.yc.mybatis.mapper;

import com.yc.mybatis.entity.Classes;

public interface ClassesMapper

{

Classes getTeacherByClasses(int id);

}

MyBatis 级联查询一对一测试

package com.yc.mybatis.mapper;

import static org.junit.Assert.*;

import org.apache.ibatis.session.SqlSession;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import com.yc.mybatis.entity.Classes;

import com.yc.mybatis.util.MyBatisUtil;

public class ClassesMapperTest

{

private SqlSession session;

@Before

public void setUp() throws Exception

{

session = MyBatisUtil.getSession();

}

@After

public void tearDown() throws Exception

{

MyBatisUtil.close(session);

}

@Test

public void testGetTeacherByClasses()

{

ClassesMapper cm = session.getMapper(ClassesMapper.class);

Classes c = cm.getTeacherByClasses(3001);

System.out.println(c);

assertNotNull(c);

}

MyBatis 级联查询一对多实体类(Classes与Student):

Classes类

package com.yc.mybatis.entity;

import java.util.List;

public class Classes

{

private String clsId;

private String clsName;

private List<Student> stus;

public String getClsId()

{

return clsId;

}

public void setClsId(String clsId)

{

this.clsId = clsId;

}

public String getClsName()

{

return clsName;

}

public void setClsName(String clsName)

{

this.clsName = clsName;

}

public List<Student> getStus()

{

return stus;

}

public void setStus(List<Student> stus)

{

this.stus = stus;

}

@Override

public String toString()

{

return "Classes [clsId=" + clsId + ", clsName=" + clsName + ", stus=" + stus + "]";

}

Student类

package com.yc.mybatis.entity;

import java.io.Serializable;

public class Student implements Serializable

{

private int stuId;

private String stuName;

private String stuSex;

private String stuBirthday;

public int getStuId()

{

return stuId;

}

public void setStuId(int stuId)

{

this.stuId = stuId;

}

public String getStuName()

{

return stuName;

}

public void setStuName(String stuName)

{

this.stuName = stuName;

}

public String getStuSex()

{

return stuSex;

}

public void setStuSex(String stuSex)

{

this.stuSex = stuSex;

}

public String getStuBirthday()

{

return stuBirthday;

}

public void setStuBirthday(String stuBirthday)

{

this.stuBirthday = stuBirthday;

}

@Override

public String toString()

{

return "Student [stuId=" + stuId + ", stuName=" + stuName + ", stuSex=" + stuSex + ", stuBirthday=" + stuBirthday + "]";

}

MyBatis 级联查询一对多映射sql语句第一种

ClassesMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

" ">

<!-- 一对多嵌套查询两条sql语句

select * from classes where clsId = 3001;

select * from student where classId = 3001;-->

<collection property="stus" column="clsId"

select="com.yc.mybatis.mapper.StudentMapper.getStusById"/>

</resultMap>

select * from classes where clsId = #{id}

</select>

</mapper>

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

" ">

select * from student where classId = #{clsId}

</select>

</mapper>

MyBatis 级联查询一对多映射sql语句第二种

ClassesMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

" ">

<!-- 嵌套结果连接查询

select * from classes c join student s on c.clsId = 3001 and c.clsId = s.classId-->

<!-- 第一种方法直接映射字段

</collection> -->

<collection property="stus" ofType="Student"

resultMap="com.yc.mybatis.mapper.StudentMapper.StudentMap"/>

</resultMap>

select * from classes c join student s on c.clsId = #{id} and c.clsId = s.classId

</select>

</mapper>

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

" ">

</resultMap>

</mapper>

MyBatis 级联查询一对多映射接口

package com.yc.mybatis.mapper;

import com.yc.mybatis.entity.Classes;

public interface ClassesMapper

{

Classes getStusByClasses(int id);

}

MyBatis 级联查询一对多测试

package com.yc.mybatis.mapper;

import static org.junit.Assert.*;

import org.apache.ibatis.session.SqlSession;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import com.yc.mybatis.entity.Classes;

import com.yc.mybatis.util.MyBatisUtil;

public class ClassesMapperTest

{

private SqlSession session;

@Before

public void setUp() throws Exception

{

session = MyBatisUtil.getSession();

}

@After

public void tearDown() throws Exception

{

MyBatisUtil.close(session);

}

@Test

public void testGetStusByClasses()

{

ClassesMapper cm = session.getMapper(ClassesMapper.class);

Classes c = cm.getStusByClasses(3001);

System.out.println(c);

assertNotNull(c);

}

转载于:https://www.cnblogs.com/xiaocunchu/p/4822037.html

你可能感兴趣的文章

Jsp抓取页面内容

查看>>

大三上学期软件工程作业之点餐系统（网页版）的一些心得

查看>>

可选参数的函数还可以这样设计！

查看>>

[你必须知道的.NET]第二十一回：认识全面的null

收集WebDriver的执行命令和参数信息

查看>>

数据结构与算法（三）-线性表之静态链表

查看>>

mac下的mysql报错：ERROR 1045(28000)和ERROR 2002 (HY000)的解决办法

Spring MVC @ResponseBody返回中文字符串乱码问题

查看>>

用户空间与内核空间，进程上下文与中断上下文[总结]

查看>>

JS 中的跨域请求

查看>>