mybaits一对一和一对多

一、一对一查询

1. 需求

一个学生相对应一个专属班级，所以学生表对班级表是一对一的关系；

查询需求：查询一个学生，与此同时查询出该学生所属的班级信息

创建数据库classInfo表和student表

判断存在即删除数据库
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(cid,cname,cinfo) values(101,'移动应用开发','计算机');
insert into classInfo(cid,cname,cinfo) values(102,'新媒体','11111');
insert into classInfo(cid,cname,cinfo) 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),
cid int
);

insert into student(sname,sex,phone,address,cid ) values('张三','男','12345678901','南阳',101);
insert into student(sname,sex,phone,address,cid ) values('李四','男','12345678902','信阳',102);
insert into student(sname,sex,phone,address,cid ) values('小美','女','12345678903','郑州',103);

select * from student;

定义实体类

package com.zhang.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.zhang.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.zhang.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.zhang.dao.ClassInfoDao.selectBycid" property="classInfo">
        </association>
    </resultMap>
 
 
    <select id="selectAll" resultMap="empMap">
        select * from student
    </select>
</mapper>

创建测试类

package com.zhang.test;
 
 
import com.zhang.bean.Student;
import com.zhang.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();
    }
}

三、一对多查询

1. 需求

一个部门对应多个员工，所以部门表对员工表是一对多的关系；

查询需求：查询一个部门，与此同时查询出该部门所有的员工信息

数据库代码：创建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;

定义实体类

package com.zhang.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.zhang.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.zhang.dao;
 
import com.zhang.bean.Dept;
 
public interface DeptDao {
    Dept selectByDid(int did);
}
 
 
 
 
package com.zhang.dao;
 
import com.zhang.bean.Emp;
 
import java.util.List;
 
public interface EmpDao {
    Emp selectByEid(int eid);
 
    List<Emp> selectByDid(int did);
}

测试类

package com.zhang.test;
 
import com.zhang.bean.Dept;
import com.zhang.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.zhang.test;
 
import com.zhang.bean.Emp;
import com.zhang.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();
    }
}