專注Java教育14年
全國咨詢/投訴熱線：400-8080-105
更新時間：2021-09-14 10:08:43 來源：動力節(jié)點 瀏覽1903次
注意:在一對多關(guān)系中,數(shù)據(jù)庫建表的時候外鍵一定是在多的那一方建立.
建表語句:
drop table courses;
drop table tutors;
如果需要可以使用 cascade constraints;
create table tutors(tutor_id number primary key, name varchar2(50) not null,email varchar2(50),phone varchar2(15), addr_id number(11) references addresses (addr_id));
create table courses(course_id number primary key,name varchar2(100) not null,description varchar2(512),start_date date ,end_date date ,tutor_id number references tutors (tutor_id));
tutors 表的樣例數(shù)據(jù)如下：
insert into tutors(tutor_id,name,email,phone,addr_id)values(1,'zs','zs@briup.com','123-456-7890',1);
insert into tutors(tutor_id,name,email,phone,addr_id)values(2,'ls','ls@briup.com','111-222-3333',2);
course 表的樣例數(shù)據(jù)如下：
insert into courses(course_id,name,description,start_date,end_date,tutor_id) values(1,'JavaSE','JavaSE',to_date('2015-09-10','yyyy-mm-dd'),to_date('2016-02-10','yyyy-mm-dd'),1);
insert into courses(course_id,name,description,start_date,end_date,tutor_id) values(2,'JavaEE','JavaEE',to_date('2015-09-10','yyyy-mm-dd'),to_date('2016-03-10','yyyy-mm-dd'),2);
insert into courses(course_id,name,description,start_date,end_date,tutor_id) values(3,'MyBatis','MyBatis',to_date('2015-09-10','yyyy-mm-dd'),to_date('2016-02-20','yyyy-mm-dd'),1);
在上述的表數(shù)據(jù)中，zs 講師教授一個課程，而 ls 講師教授兩個課程
Tutor類
package com.mybatis.pojo;
import java.util.List;
public class Tutor{
private Integer tutorId;
private String name;
private String email;
private PhoneNumber phone;
private Address address;
private List<Course> courses;
public Tutor(Integer tutorId, String name, String email,
PhoneNumber phone, Address address, List<Course> courses) {
super();
this.tutorId = tutorId;
this.name = name;
this.email = email;
this.phone = phone;
this.address = address;
this.courses = courses;
}
public Tutor() {
super();
}
@Override
public String toString() {
return "Tutor [tutorId=" + tutorId + ", name=" + name
+ ", email=" + email + ", phone=" + phone
+ ", address=" + address + ", courses=" + courses + "]";
}
public Integer getTutorId() {
return tutorId;
}
public void setTutorId(Integer tutorId) {
this.tutorId = tutorId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public PhoneNumber getPhone() {
return phone;
}
public void setPhone(PhoneNumber phone) {
this.phone = phone;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public List<Course> getCourses() {
return courses;
}
public void setCourses(List<Course> courses) {
this.courses = courses;
}
}
Course類實現(xiàn)：
package com.mybatis.pojo;
import java.util.Date;
public class Course{
private Integer courseId;
private String name;
private String description;
private Date startDate;
private Date endDate;
public Course(Integer courseId, String name, String description,
Date startDate, Date endDate) {
super();
this.courseId = courseId;
this.name = name;
this.description = description;
this.startDate = startDate;
this.endDate = endDate;
}
public Course() {
super();
}
@Override
public String toString() {
return "Course [courseId=" + courseId + ", name=" + name
+ ", description=" + description + ", startDate="
+ startDate + ", endDate=" + endDate + "]";
}
public Integer getCourseId() {
return courseId;
}
public void setCourseId(Integer courseId) {
this.courseId = courseId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Date getStartDate() {
return startDate;
}
public void setStartDate(Date startDate) {
this.startDate = startDate;
}
public Date getEndDate() {
return endDate;
}
public void setEndDate(Date endDate) {
this.endDate = endDate;
}
}
配置完以上內(nèi)容之后，我們需要進行配置文件中注冊必要的config文件：
<?xml version="1.0" encoding="UTF-8"?>
<!-- 進行dtd約束，其中-//mybatis.org//DTD Config 3.0//EN為公共約束，
http://mybatis.org/dtd/mybatis-3-config.dtd為獲取網(wǎng)絡(luò)中提供的dtd約束 -->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<!-- 給pojo類起別名 -->
<typeAlias type="com.mybatis.pojo.Address" alias="Address" />
<typeAlias type="com.mybatis.pojo.Course" alias="Course" />
<typeAlias type="com.mybatis.pojo.Tutor" alias="Tutor" />
</typeAliases>
<typeHandlers>
<typeHandler handler="com.mybatis.handlers.PhoneNumberHandlers"/>
</typeHandlers>
<!-- 配置數(shù)據(jù)庫環(huán)境其中development為默認的數(shù)據(jù)庫名稱事務(wù)管理器transactionManager類型為JDBC類型，數(shù)據(jù)源dataSource使用連接池的方式 -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<!-- 配置數(shù)據(jù)庫信息這里使用oracle數(shù)據(jù)庫 -->
<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl" />
<property name="username" value="briup" />
<property name="password" value="briup" />
</dataSource>
</environment>
</environments>
<!-- 配置xml文件映射路徑，在這里可以進行sql的操作 -->
<mappers>
<mapper resource="com/mybatis/mappers/One2ManyMapper.xml" />
</mappers>
</configuration>
<collection>元素被用來將多行課程結(jié)果映射成一個課程Course對象的一個集合。和一對一映射一樣，我們可以使用【嵌套結(jié)果ResultMap】和【嵌套查詢Select】語句兩種方式映射實現(xiàn)一對多映射。
(1)使用內(nèi)嵌結(jié)果 ResultMap 實現(xiàn)一對多映射
我們可以使用嵌套結(jié)果resultMap方式獲得課程信息，代碼如下：
<?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">
<!-- com.mybatis.mappers.StudentMapper是我們定義接口的全限定名字 這樣就可以使用接口調(diào)用映射的SQL語句了 這個名字一定要和接口對應(yīng)上 -->
<mapper namespace="com.mybatis.mappers.One2ManyMapper">
<resultMap type="Address" id="AddressResult">
<id property="addrId" column="addr_id" />
<result property="street" column="street" />
<result property="city" column="city" />
<result property="state" column="state" />
<result property="zip" column="zip" />
<result property="country" column="country" />
</resultMap>
<resultMap type="Course" id="CourseResult">
<id property="courseId" column="course_id"/>
<result property="name" column="name"/>
<result property="description" column="description"/>
<result property="startDate" column="start_Date"/>
<result property="endDate" column="end_Date"/>
</resultMap>
<resultMap type="Tutor" id="TutorWithCoursesResult">
<id property="tutorId" column="tutor_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<association property="address" resultMap="AddressResult"/>
<collection property="courses" resultMap="CourseResult"/>
</resultMap>
<select id="selectTutorWithCourses" parameterType="int" resultMap="TutorWithCoursesResult">
select t.tutor_id,t.name,t.email,t.phone,a.addr_id,a.street,a.city,a.state,a.zip,a.country,c.course_id,c.name,c.description,c.start_Date,c.end_Date
from tutors t left outer join addresses a on t.addr_id=a.addr_id
left outer join courses c on t.tutor_id=c.tutor_id
where t.tutor_id=#{id}
</select>
</mapper>
這里我們使用了一個簡單的使用了JOINS連接的Select語句獲取課程信息。<collection>元素的resultMap屬性設(shè)置成了CourseResult，CourseResult包含了Course對象屬性與表列名之間的映射。
如果同時也要查詢到Address相關(guān)信息,可以按照上面一對一的方式,在配置中加入<association>即可
實現(xiàn)接口：
package com.mybatis.mappers;
import com.mybatis.pojo.Tutor;
public interface One2ManyMapper {
Tutor selectTutorWithCourses(Integer id);
}
測試類實現(xiàn)：
package com.mybatis.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.mybatis.mappers.One2ManyMapper;
import com.mybatis.pojo.Tutor;
import com.mybatis.utils.MyBatisSqlSessionFactory;
public class One2ManyMapperTest {
@Test
public void test_selectTutorWithCourses()
{
SqlSession session=null;
session = MyBatisSqlSessionFactory.openSession();//使用封裝之后的類
// 使用sqlsession獲得映射接口的實現(xiàn)類對象,接口的引用指向?qū)崿F(xiàn)類的對象
One2ManyMapper mapper = session.getMapper(One2ManyMapper.class);
Tutor tutor = mapper.selectTutorWithCourses(1);
System.out.println(tutor);
System.out.println("執(zhí)行完畢");
}
}
測試結(jié)果：
2016-10-23 14:14:26,785 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - ==> Preparing: select t.tutor_id,t.name,t.email,t.phone,a.addr_id,a.street,a.city,a.state,a.zip,a.country,c.course_id,c.name,c.description,c.start_Date,c.end_Date from tutors t left outer join addresses a on t.addr_id=a.addr_id left outer join courses c on t.tutor_id=c.tutor_id where t.tutor_id=?
2016-10-23 14:14:27,007 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - ==> Parameters: 1(Integer)
2016-10-23 14:14:27,165 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - <== Total: 2
Tutor [tutorId=1, name=zs, email=zs@briup.com, phone=123-456-7890, address=Address [addrId=1, street=redSt, city=kunshan, state=W, zip=12345, country=china], courses=[Course [courseId=1, name=zs, description=JavaSE, startDate=Thu Sep 10 00:00:00 CST 2015, endDate=Wed Feb 10 00:00:00 CST 2016], Course [courseId=3, name=zs, description=MyBatis, startDate=Thu Sep 10 00:00:00 CST 2015, endDate=Sat Feb 20 00:00:00 CST 2016]]]
執(zhí)行完畢
(2)使用嵌套Select語句實現(xiàn)一對多映射
我們可以使用嵌套Select語句方式獲得課程信息，代碼如下：
<mapper namespace="com.mybatis.mappers.One2ManyMapper">
<resultMap type="Address" id="AddressResult">
<id property="addrId" column="addr_id" />
<result property="street" column="street" />
<result property="city" column="city" />
<result property="state" column="state" />
<result property="zip" column="zip" />
<result property="country" column="country" />
</resultMap>
<select id="selectAddress" parameterType="int" resultMap="AddressResult">
select * from addresses where addr_id=#{id}
</select>
<resultMap type="Course" id="CourseResult">
<id property="courseId" column="course_id"/>
<result property="name" column="name"/>
<result property="description" column="description"/>
<result property="startDate" column="start_Date"/>
<result property="endDate" column="end_Date"/>
</resultMap>
<select id="selectCourse" parameterType="int" resultMap="CourseResult">
select * from courses where tutor_id=#{id}
</select>
<resultMap type="Tutor" id="TutorWithCoursesResult">
<id property="tutorId" column="tutor_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<association property="address" column="addr_id" select="selectAddress"/>
<!-- 這里要注意:是把當前tutor_id表中列的值當做參數(shù)去執(zhí)行selectCourse這個查詢語句,最后把查詢結(jié)果封裝到Tutor類中的courses屬性中 -->
<collection property="courses" column="tutor_id" select="selectCourse"/>
</resultMap>
<select id="selectTutorWithCourses" parameterType="int" resultMap="TutorWithCoursesResult">
select t.tutor_id,t.name,t.email,t.phone,t.addr_id
from tutors t
where t.tutor_id=#{id}
</select>
</mapper>
在這種方式中，<assoication>元素的select屬性被設(shè)置為id為selectAddress的語句，用來觸發(fā)單獨的SQL查詢加載課程信息。tutor_id這一列值將會作為輸入?yún)?shù)傳遞給selectCourse語句。
mapper接口和程序調(diào)用與resultMap中的方法一致，查詢后結(jié)果顯示如下所示：
2016-10-23 15:38:53,709 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - ==> Preparing: select t.tutor_id,t.name,t.email,t.phone,t.addr_id from tutors t where t.tutor_id=?
2016-10-23 15:38:54,029 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - ==> Parameters: 1(Integer)
2016-10-23 15:38:54,187 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectAddress - ====> Preparing: select * from addresses where addr_id=?
2016-10-23 15:38:54,188 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectAddress - ====> Parameters: 1(Integer)
2016-10-23 15:38:54,193 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectAddress - <==== Total: 1
2016-10-23 15:38:54,202 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectCourse - ====> Preparing: select * from courses where tutor_id=?
2016-10-23 15:38:54,203 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectCourse - ====> Parameters: 1(Integer)
2016-10-23 15:38:54,224 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectCourse - <==== Total: 2
2016-10-23 15:38:54,225 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - <== Total: 1
Tutor [tutorId=1, name=zs, email=zs@briup.com, phone=123-456-7890, address=Address [addrId=1, street=redSt, city=kunshan, state=W, zip=12345, country=china], courses=[Course [courseId=1, name=JavaSE, description=JavaSE, startDate=Thu Sep 10 00:00:00 CST 2015, endDate=Wed Feb 10 00:00:00 CST 2016], Course [courseId=3, name=MyBatis, description=MyBatis, startDate=Thu Sep 10 00:00:00 CST 2015, endDate=Sat Feb 20 00:00:00 CST 2016]]]
執(zhí)行完畢
【注意】嵌套查詢Select語句查詢會導(dǎo)致1+N問題。首先，主查詢將會執(zhí)行(1 次)，對于主查詢返回的每一行，另外一個查詢將會被執(zhí)行(主查詢 N 行，則此查詢 N 次)。對于大量數(shù)據(jù)而言，這會導(dǎo)致很差的性能問題。
通過以上介紹相信大家對"Mybatis一對多映射詳解"已經(jīng)有所了解，如果有朋友想了解更多Java相關(guān)知識，可以關(guān)注動力節(jié)點的Java視頻頁面，里面的視頻教程都是可以免費下載學(xué)習(xí)的，希望對大家能夠有所幫助。
官方微信
官方抖音
Java實驗班
Java就業(yè)班
Java夜校直播班
Java在職加薪班
Java架構(gòu)師班
京公網(wǎng)安備 11030102010736號