mybatis一对一关联查询
有两张表,学生表student和班级表classinfo,一个student学生对应一个班级classinfo
数据库代码:创建student表和classinfo表
#判断存在即删除数据库
drop database if exists mydb;
#创建数据库
create database mydb;
#使用数据库
use mydb;
create table classinfo
(
cid int primary key auto_increment,
cname varchar(20),
cinfo varchar(20)
);
insert into classinfo values(101,'一班','数学');
insert into classinfo values(102,'二班','历史');
insert into classinfo values(103,'三班','语文');
select * from classinfo;
create table student(
sid int primary key auto_increment,
sname varchar(20),
sex varchar(2),
phone varchar(11),
address varchar(50),
did int
);
insert into student(sname,sex,phone,address,did) values('小芬','女','15785642139','信阳',101);
insert into student(sname,sex,phone,address,did) values('小岚','女','15785642145','平顶山',102);
insert into student(sname,sex,phone,address,did) values('小华','男','15785642125','驻马店',103);
select * from student;
定义实体类
package com.chen.bean;
public class ClassInfo {
private Integer cid;
private String cname;
private String cinfo;
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public String getCinfo() {
return cinfo;
}
public void setCinfo(String cinfo) {
this.cinfo = cinfo;
}
@Override
public String toString() {
return "ClassInfo{" +
"cid=" + cid +
", cname='" + cname + '\'' +
", cinfo='" + cinfo + '\'' +
'}';
}
}
package com.chen.bean;
public class Student {
private Integer sid;
private String sname;
private String sex;
private String phone;
private String address;
private ClassInfo classInfo;
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public ClassInfo getClassInfo() {
return classInfo;
}
public void setClassInfo(ClassInfo classInfo) {
this.classInfo = classInfo;
}
@Override
public String toString() {
return "Student{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", sex='" + sex + '\'' +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
", classInfo=" + classInfo +
'}';
}
}
创建接口
public interface StudentDao {
List<Student> selectAll();
}
配置Student.xml
<?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.chen.dao.StudentDao">
<resultMap id="empMap" type="com.chen.bean.Student">
<id property="sid" column="sid" />
<result property="sname" column="sname" />
<result property="sex" column="sex" />
<result property="phone" column="phone" />
<result property="address" column="address" />
<association column="did" select="com.chen.dao.ClassInfoDao.selectBycid" property="classInfo">
</association>
</resultMap>
<select id="selectAll" resultMap="empMap">
select * from student
</select>
</mapper>
测试类
package com.chen.test;
import com.chen.bean.Student;
import com.chen.dao.StudentDao;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class StudentTest {
InputStream stream=null;
SqlSessionFactoryBuilder builder=null;
SqlSessionFactory factory=null;
SqlSession sqlSession=null;
StudentDao studentDao=null;
@Before
public void init() throws IOException {
stream= Resources.getResourceAsStream("mybatis.xml");
builder=new SqlSessionFactoryBuilder();
factory=builder.build(stream);
sqlSession=factory.openSession();
studentDao=sqlSession.getMapper(StudentDao.class);
}
@Test
public void testSelectAll(){
List<Student> studentList=studentDao.selectAll();
for (Student student : studentList) {
System.out.println(student);
}
}
@After
public void distroy() throws IOException {
sqlSession.commit();
sqlSession.close();
stream.close();
}
}
运行结果:
Student{sid=1, sname='小芬', sex='女', phone='15785642139', address='信阳', classInfo=ClassInfo{cid=101, cname='一班', cinfo='数学'}}
Student{sid=2, sname='小岚', sex='女', phone='15785642145', address='平顶山', classInfo=ClassInfo{cid=102, cname='二班', cinfo='历史'}}
Student{sid=3, sname='小华', sex='男', phone='15785642125', address='驻马店', classInfo=ClassInfo{cid=103, cname='三班', cinfo='语文'}}
一对多关联查询
数据库代码:创建dept表和emp表
create table dept
(
did int primary key,
dname varchar(20),
dinfo varchar(50)
);
insert into dept values(101,'市场部','负责公司市场的开拓');
insert into dept values(102,'产品部','负责公司产品的营销');
insert into dept values(103,'研发部','负责公司产品的研发');
insert into dept values(104,'测试部','负责公司产品的测试');
select * from dept;
#员工表
drop table if exists emp;
create table emp(
eid int primary key auto_increment,
ename varchar(20),
job varchar(20),
sal double,
phone varchar(11),
address varchar(50),
did int
);
insert into emp(ename,job,sal,phone,address,did) values('赵德保','市场专员',4600,'15785642139','信阳',101);
insert into emp(ename,job,sal,phone,address,did) values('刘瑞琳','市场经理',7600,'17956248563','商丘',101);
insert into emp(ename,job,sal,phone,address,did) values('张翠萍','产品设计师',5800,'15821563548','郑州',102);
insert into emp(ename,job,sal,phone,address,did) values('刘长河','产品经理',9800,'15821563548','洛阳',102);
insert into emp(ename,job,sal,phone,address,did) values('李耀菲','开发工程师',8800,'13658942168','许昌',103);
insert into emp(ename,job,sal,phone,address,did) values('王长林','项目经理',15800,'13954865721','南阳',103);
insert into emp(ename,job,sal,phone,address,did) values('陈清泰','测试工程师',5300,'13785463249','周口',104);
insert into emp(ename,job,sal,phone,address,did) values('杨国华','测试经理',9800,'13785463249','驻马店',104);
select * from emp;
实体类:dept类和emp类
package com.chen.bean;
import java.util.List;
public class Dept {
private Integer did;
private String dname;
private String dinfo;
//一个部门包含多个员工,一对多关系就是定义集合
List<Emp> emps;
public List<Emp> getEmps() {
return emps;
}
public void setEmps(List<Emp> emps) {
this.emps = emps;
}
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getDinfo() {
return dinfo;
}
public void setDinfo(String dinfo) {
this.dinfo = dinfo;
}
@Override
public String toString() {
return "Dept{" +
"did=" + did +
", dname='" + dname + '\'' +
", dinfo='" + dinfo + '\'' +
", emps=" + emps +
'}';
}
}
package com.chen.bean;
public class Emp {
private Integer eid;
private String ename;
private String job;
private Double sal;
private String phone;
private String address;
private Integer did;
public Integer getEid() {
return eid;
}
public void setEid(Integer eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getDid() {
return did;
}
public void setDid(Integer did) {
this.did = did;
}
@Override
public String toString() {
return "Emp{" +
"eid=" + eid +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", sal=" + sal +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
", did=" + did +
'}';
}
}
dao包接口和mapper文件
package com.chen.dao;
import com.chen.bean.Dept;
public interface DeptDao {
Dept selectByDid(int did);
}
package com.chen.dao;
import com.chen.bean.Emp;
import java.util.List;
public interface EmpDao {
Emp selectByEid(int eid);
List<Emp> selectByDid(int did);
}
<mapper namespace="com.chen.dao.DeptDao">
<resultMap id="deptMap" type="com.chen.bean.Dept">
<id property="did" column="did"/>
<result property="dname" column="dname"/>
<result property="dinfo" column="dinfo"/>
<!-- 配置一对多关系:自动根据did去关联查询emp表中该did的信息 -->
<collection column="did" select="com.chen.dao.EmpDao.selectByDid" property="emps"> </collection>
</resultMap>
<select id="selectByDid" parameterType="int" resultMap="deptMap"> select * from dept where did=#{did}; </select>
</mapper>
<mapper namespace="com.chen.dao.EmpDao">
<select id="selectByEid" parameterType="int" resultType="emp"> select * from emp where eid=#{eid} </select>
<select id="selectByDid" parameterType="int" resultType="emp"> select * from emp where did=#{did} </select>
</mapper>
测试类
package com.chen.test;
import com.chen.bean.Dept;
import com.chen.dao.DeptDao;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
public class DeptTest {
InputStream stream=null;
SqlSessionFactoryBuilder builder=null;
SqlSessionFactory factory=null;
SqlSession sqlSession=null;
DeptDao deptDao=null;
@Before
public void init() throws IOException {
stream= Resources.getResourceAsStream("mybatis.xml");
builder=new SqlSessionFactoryBuilder();
factory=builder.build(stream);
sqlSession=factory.openSession();
deptDao=sqlSession.getMapper(DeptDao.class);
}
@Test
public void testSelectByDid(){
Dept dept = deptDao.selectByDid(101);
System.out.println(dept);
}
@After
public void distroy() throws IOException {
sqlSession.commit();
sqlSession.close();
stream.close();
}
}
package com.chen.test;
import com.chen.bean.Emp;
import com.chen.dao.EmpDao;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class EmpTest {
InputStream stream=null;
SqlSessionFactoryBuilder builder=null;
SqlSessionFactory factory=null;
SqlSession sqlSession=null;
EmpDao empDao=null;
@Before
public void init() throws IOException {
stream= Resources.getResourceAsStream("mybatis.xml");
builder=new SqlSessionFactoryBuilder();
factory=builder.build(stream);
sqlSession=factory.openSession();
empDao=sqlSession.getMapper(EmpDao.class);
}
@Test
public void testSelectByEid(){
Emp emp = empDao.selectByEid(2);
System.out.println(emp);
}
@After
public void distroy() throws IOException {
sqlSession.commit();
sqlSession.close();
stream.close();
}
}