Mybatis连接Oracle数据库一对多

 

create table clazz(
cid number(3) primary key,
name varchar2(20)
);

create table student(
sid number(3) primary key,
name varchar2(20),
sex char(3),
mobile varchar(11),
cid number(3) references clazz(cid)
);

create sequence clazz_seq;
create sequence student_seq;

insert into clazz values(clazz_seq.nextval,'魔法班');
insert into clazz values(clazz_seq.nextval,'修仙班');
insert into clazz values(clazz_seq.nextval,'王者班');



insert into student values(student_seq.nextval,'哈利波特','男','13588889800',1)；
insert into student values(student_seq.nextval,'泰勒','女','13588889801',1)；
insert into student values(student_seq.nextval,'艾薇儿','女','13588889802',1)；
insert into student values(student_seq.nextval,'托马斯','男','13588889803',2)；
insert into student values(student_seq.nextval,'雪诺','男','13588889804',2)；
insert into student values(student_seq.nextval,'瑟曦','女','13588889805',2)；
insert into student values(student_seq.nextval,'唐山','男','13588889806',3)；
insert into student values(student_seq.nextval,'火舞','女','13588889807',3)；

package com.baizhi.dao;

import java.util.List;

import com.baizhi.entity.Clazz;

public interface ClazzDao {
	List<Clazz> queryAll();
}

<?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.baizhi.dao.ClazzDao">
	
	<!-- 定义resultMap 结果集映射关系-->
	<resultMap type="Clazz" id="clazzResultMap">
		<id column="cid" property="cid"/>
		<result column="cname" property="name"/>
		<!-- 
			封装关系属性
			此时关系属性是一个list集合
			property 指定关系属性变量名
			ofType执行集合泛型类型的全限定名或别名
		 -->
		<collection property="students" ofType="Student">
			<id column="sid" property="sid"/>
			<result column="sname" property="name"/>
			<result column="sex" property="sex"/>
			<result column="smobile" property="mobile"/>
		</collection>
	</resultMap>
	<select id="queryAll" resultMap="clazzResultMap">
		select c.cid cid,c.name cname,
		s.sid sid,s.name sname,s.sex sex,s.mobile smobile
		from clazz c left join student s on c.cid=s.cid

	</select>
	
</mapper>

package com.baizhi.entity;

import java.io.Serializable;
import java.util.List;

public class Clazz implements Serializable{
	private Integer cid;
	private String name;
	//指定关系属性：是一个Student集合
	private List<Student> students;
	public Integer getCid() {
		return cid;
	}
	public void setCid(Integer cid) {
		this.cid = cid;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public List<Student> getStudents() {
		return students;
	}
	public void setStudents(List<Student> students) {
		this.students = students;
	}
	@Override
	public String toString() {
		return "Clazz [cid=" + cid + ", name=" + name + ", students=" + students + "]";
	}
	public Clazz(Integer cid, String name, List<Student> students) {
		super();
		this.cid = cid;
		this.name = name;
		this.students = students;
	}
	public Clazz() {
		super();
		// TODO Auto-generated constructor stub
	}
	
}

package com.baizhi.entity;

import java.io.Serializable;
import java.util.List;

public class Student implements Serializable{
	private Integer sid;
	private String name;
	private String sex;
	private String mobile;
	public Integer getSid() {
		return sid;
	}
	public void setSid(Integer sid) {
		this.sid = sid;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getMobile() {
		return mobile;
	}
	public void setMobile(String mobile) {
		this.mobile = mobile;
	}
	@Override
	public String toString() {
		return "Student [sid=" + sid + ", name=" + name + ", sex=" + sex + ", mobile=" + mobile + "]";
	}
	public Student(Integer sid, String name, String sex, String mobile) {
		super();
		this.sid = sid;
		this.name = name;
		this.sex = sex;
		this.mobile = mobile;
	}
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	
	
}

package com.baizhi.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.baizhi.dao.ClazzDao;
import com.baizhi.entity.Clazz;
import com.baizhi.entity.Student;
import com.baizhi.util.MybatisUtil;

public class TestClazzDao {
	@Test
	public void testQueryAll() {
		SqlSession sqlSession =MybatisUtil.opSession();
		ClazzDao clazzDao =sqlSession.getMapper(ClazzDao.class);
		List<Clazz> list = clazzDao.queryAll();
		for (Clazz clazz : list) {
			System.out.println(clazz.getName()+"班级有：");
			List<Student> students =clazz.getStudents();
			for (Student student : students) {
				System.out.println("\t"+student.getName());
			}
		}
	}
}

package com.baizhi.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;

import com.baizhi.dao.AccountDao;
import com.baizhi.entity.Account;

public class MybatisUtil {
	public static SqlSessionFactory factory;
	//静态初始代码块，书写只需要执行一次的操作，静态代码块的代码只在类加载的时候执行一次
	static {
		InputStream in = null;
		try {
			//读取核心配置文件mybatis-config.xml
			in = Resources.getResourceAsStream("mybatis-config.xml");
			//创建SqlSessionFactory
			factory = new SqlSessionFactoryBuilder().build(in);
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("初始化工厂失败");
		}finally {
			try {
				in.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	
	//创建一个线程绑定对象
	private static ThreadLocal<SqlSession> tol=new ThreadLocal<SqlSession>();
	//返回一个SqlSession
	public static SqlSession opSession() {
		//先到当前线程取一下
		SqlSession sqlSession =tol.get();
		if(sqlSession==null) {
			//创建SqlSession
			sqlSession = factory.openSession();
			//绑定到当前线程
			tol.set(sqlSession);
		}
		return sqlSession;
	}
	
	//释放资源
	public static void close(){
		SqlSession sqlSession=tol.get();
		if(sqlSession!=null) { 
			sqlSession.close();
			tol.remove();//从当前线程移除
		}
	}
	
	//提交事务
	public static void commit() {
		SqlSession sqlSession=tol.get();
		if(sqlSession!=null) { 
			sqlSession.commit();
		}
	}
	//回滚事务
	public static void rollback() {
		SqlSession sqlSession=tol.get();
		if(sqlSession!=null) { 
			sqlSession.rollback();
		}
	}
}

jdbc.propertities:

driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=hr
password=hr

<?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>
	<!-- 引入外部的小配置文件 -->
	<properties resource="jdbc.properties"></properties>
	<!-- 给实体类起别名 -->
	<typeAliases>
		<!-- type属性值为实体类全限定名
		alias 属性值为别名
		 -->
		<!--<typeAlias type="com.baizhi.entity.Account"  alias="Account"/>	  -->
		<!-- 起别名用第二种方式 给实体类起别名 -->
		<package name="com.baizhi.entity"/>
	</typeAliases>
<!-- 编写mybatis的运行环境 -->
	<environments default="oracle">
		<environment id="oracle">
		<!-- 指定事务采用JDBC的方式管理 -->
			<transactionManager type="JDBC"></transactionManager>
			<!-- 使用mybatis提供的POOLED连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="${driver}"/>
				<property name="url" value="${url}"/>
				<property name="username" value="${username}"/>
				<property name="password" value="${password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<!-- 注册mapper文件位置 -->
	<mappers>
		<!-- 对mapper标签注册一个mapper文件的位置：路径一定严格大小写 文件用斜杠，包用点 -->
		
		<mapper resource="com/baizhi/dao/ClazzDaoMapper.xml"></mapper>
	</mappers>
	
</configuration>