mybatis+oracle实现一对多，多对一查询

首先创建表

学生表

create table stu(
       id number(11) primary key,
       name varchar2(255),
       age number(11),
       cid number(11)
);

班级表

create table cls(
  id number(11) primary key,
  cname varchar2(255)
);

创建序列

create sequence seq_stu;

create sequence seq_cls;

添加数据

insert into stu values(seq_stu.nextval,'小乔',16,1);
insert into stu values(seq_stu.nextval,'干将',88,1);
insert into stu values(seq_stu.nextval,'鲁班',18,1);
insert into stu values(seq_stu.nextval,'婉儿',22,1);
insert into stu values(seq_stu.nextval,'艾琳',23,2);
insert into stu values(seq_stu.nextval,'安其拉',14,2);
insert into stu values(seq_stu.nextval,'牛魔',50,2);
insert into stu values(seq_stu.nextval,'猴子',66,2);
insert into stu values(seq_stu.nextval,'李白',26,1);
insert into stu values(seq_stu.nextval,'韩信',32,1);

insert into cls values (seq_cls.nextval,'QY75');
insert into cls values (seq_cls.nextval,'QY97');

创建工具类

package com.aaa.mybatis.util;

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 java.io.IOException;
import java.io.Reader;

public class SqlSessionFactoryUtil {
    private static SqlSessionFactory sessionFactory;
    static {
        Reader reader =null;
        try {
            reader= Resources.getResourceAsReader("mybatis.xml");
            /*sessionFactory =new SqlSessionFactoryBuilder().build(reader,"mysql");*/
            sessionFactory =new SqlSessionFactoryBuilder().build(reader,"myOracle");
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                reader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    public  static SqlSession getSession(){
        return  sessionFactory.openSession();
    }
}

创建mybatis主配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="myOracle">
        <environment id="myOracle">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"></property>
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"></property>
                <property name="username" value="scott"></property>
                <property name="password" value="tiger"></property>
            </dataSource>
        </environment>
　　　　　<--连接MySQL用下面这个,工具类加着注释-->
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"></property>
                <property name="url" value="jdbc:mysql://localhost:3306/test"></property>
                <property name="username" value="root"></property>
                <property name="password" value="admin"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/aaa/mybatis/dao/StuDaoMapper.xml"></mapper>
        <mapper resource="com/aaa/mybatis/dao/ClsDaoMapper.xml"></mapper>
    </mappers>
</configuration>

创建Stu,Cls表实体

package com.aaa.mybatis.entity;
import java.util.*;

public class Cls {
    private int cid;
    private String cname;
    private List<Stu> stu;

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public List<Stu> getStu() {
        return stu;
    }

    public void setStu(List<Stu> stu) {
        this.stu = stu;
    }
}

package com.aaa.mybatis.entity;
public class Stu {
    private int id;
    private String name;
    private int age;
    private Cls cls;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Cls getCls() {
        return cls;
    }

    public void setCls(Cls cls) {
        this.cls = cls;
    }
}

创建Stu接口

package com.aaa.mybatis.dao;

import com.aaa.mybatis.entity.Stu;
import org.apache.ibatis.annotations.Param;

import java.util.*;

public interface StuDao {
    /**
     * 根据学生编号查询学生信息和班级信息
     * @param id
     * @return
     */
  
}
创建Cls接口

package com.aaa.mybatis.dao;

import com.aaa.mybatis.entity.Cls;

import java.util.*;

public interface ClsDao {
    /**
     * 根据班级编号查询该班级信息和所有学生信息
     */
    public List<Cls> allClsAndStuByCid();
}


一对多mapper根据学生编号查询学生信息和班级信息

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aaa.mybatis.dao.StuDao">
    <resultMap id="stuMap" type="com.aaa.mybatis.entity.Stu" autoMapping="true">
        <association property="cls" javaType="com.aaa.mybatis.entity.Cls" autoMapping="true"><--autoMapping如果属性名与实体一致可以省略里面的字段-->

        </association>
    </resultMap>
    <select id="allstuByStuId" resultMap="stuMap">
        select * from stu a join cls b on a.cid=b.cid where a.id=#{id}
    </select>
</mapper>



多对一mapper查询所有班级信息和每个班的学生信息

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aaa.mybatis.dao.ClsDao">
    <resultMap id="clsMap" type="com.aaa.mybatis.entity.Cls" autoMapping="true">
        <id column="cid" property="cid"></id><--加上autoMapping可以省略实体里字段名与属性名相同的信息，但是多对一必须在这里加ID标签-->
        <collection property="stu" ofType="com.aaa.mybatis.entity.Stu" autoMapping="true"></collection>
    </resultMap>
    <select id="allClsAndStuByCid" resultMap="clsMap">
        select * from cls a join stu b on a.cid=b.cid
    </select>
</mapper>

测试一对多

package com.aaa.mybatis.text;

import com.aaa.mybatis.dao.StuDao;
import com.aaa.mybatis.entity.Stu;
import com.aaa.mybatis.util.SqlSessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;

public class text01 {
    public static void main(String[] args) {
        SqlSession session= SqlSessionFactoryUtil.getSession();
        StuDao mapper = session.getMapper(StuDao.class);
        Stu stu = mapper.allstuByStuId(6);
        System.out.println("学生姓名:"+stu.getName());
        System.out.println("学生编号:"+stu.getId());
        System.out.println("学生年龄:"+stu.getAge());
        System.out.println("班级编号:"+stu.getCls().getCid());
        System.out.println("班级名称:"+stu.getCls().getCname());
    }
}
测试结果
学生姓名:艾琳
学生编号:6
学生年龄:23
班级编号:2
班级名称:QY97

Process finished with exit code 0


测试多对一

package com.aaa.mybatis.text;

import com.aaa.mybatis.dao.ClsDao;
import com.aaa.mybatis.dao.StuDao;
import com.aaa.mybatis.entity.Cls;
import com.aaa.mybatis.entity.Stu;
import com.aaa.mybatis.util.SqlSessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class text02 {
    public static void main(String[] args) {
        SqlSession session= SqlSessionFactoryUtil.getSession();
        ClsDao mapper = session.getMapper(ClsDao.class);
        List<Cls> cls = mapper.allClsAndStuByCid();
        for(int i=0;i<cls.size();i++){
            System.out.println("班级编号:"+cls.get(i).getCid());
            System.out.println("班级名称:"+cls.get(i).getCname());
            System.out.println("------------------");
            List<Stu> e = cls.get(i).getStu();
            for (Stu stu : e) {
                System.out.println("学生姓名:"+stu.getName());
                System.out.println("学生编号:"+stu.getId());
                System.out.println("学生年龄:"+stu.getAge());
                System.out.println("**********************");
            }
        }

    }
}
测试结果
班级编号:1
班级名称:QY75
------------------
学生姓名:小乔
学生编号:2
学生年龄:16
**********************
学生姓名:干将
学生编号:3
学生年龄:88
**********************
学生姓名:鲁班
学生编号:4
学生年龄:18
**********************
学生姓名:婉儿
学生编号:5
学生年龄:22
**********************
学生姓名:李白
学生编号:10
学生年龄:26
**********************
学生姓名:韩信
学生编号:11
学生年龄:32
**********************
班级编号:2
班级名称:QY97
------------------
学生姓名:艾琳
学生编号:6
学生年龄:23
**********************
学生姓名:安其拉
学生编号:7
学生年龄:14
**********************
学生姓名:牛魔
学生编号:8
学生年龄:50
**********************
学生姓名:猴子
学生编号:9
学生年龄:66
**********************

Process finished with exit code 0