mybatis中 一对多， 多对一 多对多 关系查询demo

demo中所用的表 如同
User表

public class User {

	//用户表
	Integer userId;
	Integer userType;
	//用户名
	String userName;
	//用户密码
	String userPass;
	//宿舍ID
	Integer dormId;
	//电话
	String phone;
	//宿舍对象
	Dorm dorm;

Dorm表

public class Dorm {
//宿舍ID
	private  Integer dormId;
	private  String dormNum;
	private Set<User> users =new HashSet<User>();
	}

**user表中dormId和Dorm表dormId 相连及外键 一个用户只能住在一个宿舍 一个宿舍可以有多个用户 **

注意：一般用mybatis 企业用的是接口映射 所以在创建实体类后需要创建对应的接口映射

配置的过程中可以使用注解，也可以使用配置文件 xxxxmapper.XML配置

个人觉得注解比较直观 但是听说 有的企业用的是XML配置。
首先进行一个最简单的单表查询

//测试类
public static void main(String[] args) {
		// TODO Auto-generated method stub
		selectAll();
	}
		private static void selectAll() {
		//这是有一个自己写的工具类就是获取mybatis 的SqlSession对象 （简单讲就是使用mybtis时候前面那几段代码  都是重复的  所以封装在一个类中   要用的时候直接调用即可）
		SqlSession session = MyBatisUtils.getsession();
		// getMapper 获取映射接口的代理对象
		UserMapper UserMapper = session.getMapper(UserMapper.class);
		//创建user 对象  给UserType赋值
		User user =new User();
		user.setUserType(1);
		//测试
		List<User> rs = UserMapper.selectALL(user);
		System.out.println(rs);
//提交事务 和关闭资源
		session.commit();
		session.close();
	}

UserMapper 接口

/*			
		
     * 这个是注解的使用方法   使用注解就可以不用UserMapper.xml文件  只需在mybatis的配置文件修改一些映射类型路径
	 * @Select("select * from user where userType = #{userType}")
	 * 
	 * @ResultType(User.class)
	 */
public interface UserMapper {
//selectALL方法名字对应的是配置文件上的select 中的id值
 	 public List<User> selectALL(User user);
 	 void insertUser(User user);}

UserMapper.xml

<!-- 
		id : 唯一的标识
		parameterType : 参数类型(用来为sql语句中的占位符赋值) 可选
		resultType 返回结果类型
	 -->
<select id="selectALL"  parameterType="UserAlias" resultType="UserAlias">
		select * from user where userType=#{userType}
	</select>

————————————————————————————————

一对多
测试类

public static void main(String[] args) {
		// TODO Auto-generated method stub
		selectkey();
	}
		private static void selectkey() {
		SqlSession session = MyBatisUtils.getsession();
		DormMapper DormMapper = session.getMapper(DormMapper.class);

		Dorm dorm = new Dorm();
		 Dorm rs = DormMapper.selectDormByid(1001);
		
		 System.out.println(rs);

	
		session.commit();
		session.close();
		
		
	}

UserMapper.java

 //一对多获取宿舍id查找表
 	// @Select("select * from user where dormId=#{dormId}")
 	List<User> selectBydormID(int dormId)

UserMapper.xml

<select id="selectBydormID"  parameterType="int" resultType="UserAlias">
		select * from user where dormId=#{dormId}
	</select>

DormMapper.xml（需要进行结果映射）

	 <!-- 一对多  --> 
	 <resultMap type="DormAlias" id="DormResultMap" >
	 <id property="dormId" column="dormId"></id>
	 <id property="dormNum" column="dormNum"></id>
	 <collection property="users" ofType="UserAlias" column="dormId"
	 select="com.chinasofti.mapper.UserMapper.selectBydormID"></collection>
	 </resultMap>
	 
	 
	<select id="selectDormByid"  parameterType="int" resultMap="DormResultMap">
	select *
	from dorm	
	where dormId=#{dormId}
	</select>

DormMapper.java

	//一对多根据id查表
	/* 注解
	 * @Select({ "select * from dorm where dormId=#{dormId}" })
	 * 
	 * @Results({
	 * 
	 * @Result(property="dormId" ,column="dormId"),
	 * 
	 * @Result(property="dormNum" ,column="dormNum"),
	 * 
	 * @Result(property="users", column="dormId",many=@Many( select =
	 * "com.chinasofti.mapper.UserMapper.selectBydormID")), })
	 */

	Dorm  selectDormByid(int DormId);

---

多对一
测试类

public static void main(String[] args) {
		// TODO Auto-generated method stub
		selectUser();
	} 
	//多对一测试
	private static void selectUser() {
		SqlSession session = MyBatisUtils.getsession();
		UserMapper UserMapper = session.getMapper(UserMapper.class);

	
		  List<User> rs = UserMapper.selectUserID(1001);
		for (User user : rs) {
			System.out.println(user);
		}
		 
		
		//List<User> rs = session.selectList("com.chinassofti.beans.UserMapper.select",user);
	
		session.commit();
		session.close();
		
		
	}

UserMapper.java

 //多对一
	/*
	 * @Select("select * from dorm,user  where user.dormId=#{dormId} and user.dormId=dorm.dormId"
	 * )
	 * 
	 * @Results({
	 * 
	 * @Result(property = "userId", column = "userId"),
	 * 
	 * @Result(property = "userType", column = "userType"),
	 * 
	 * @Result(property = "userName", column = "userName"),
	 * 
	 * @Result(property = "userPass", column = "userPass"),
	 * 
	 * @Result(property = "dormId", column = "dormId"),
	 * 
	 * @Result(property = "dorm", column = "dormId",one=@One(
	 * select="com.chinasofti.mapper.DormMapper.selectDormByDormid")),
	 * 
	 * })
	 */
 	List<User> selectUserID(int id);

UserMapper.xml

<!--多对一  -->
	
	<resultMap type="UserAlias" id="userResultMap">
	<result property="userId"  column = "userId"/>
	<result property="userType" column = "userType"/>
	<result property= "userName" column = "userName"/>
	<result property = "userPass" column = "userPass"/>
	<result property = "dormId"  column = "dormId"/>
	<association property = "dorm" column = "dormId" javaType="DormAlias"
	select="com.chinasofti.mapper.DormMapper.selectDormByDormid"/>
	
	</resultMap>
	
	<select id="selectUserID"  parameterType="int" resultMap="userResultMap">
		select * from dorm,user  where user.dormId=#{dormId} and user.dormId=dorm.dormId
	</select>
	

DormMapper.java

//多对一
	/* @Select({ "select * from dorm where dormId=#{dormId}" }) */
	Dorm  selectDormByDormid(int DormId);

DormMapper.xml

	<!--多对一  -->
	<select id="selectDormByDormid"  parameterType="int" resultType="DormAlias">
	select * from dorm where dormId=#{dormId}
	</select>

---

多对多

测试类

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		selectStudent();
	} 
private static void selectStudent() {
		SqlSession session = MyBatisUtils.getsession();
		StudentMapper StudentMapper = session.getMapper(StudentMapper.class);

	
		  List<Student> rs = StudentMapper.findStudentBycourseId(1);
		for (Student Students : rs) {
			System.out.println(Students);
		}
		 
		
		//List<User> rs = session.selectList("com.chinassofti.beans.UserMapper.select",user);
	
		session.commit();
		session.close();
		
		
	}

StudentMapper.java

public interface StudentMapper {
	List<Student> findStudentBycourseId (int id);
}

StudentMapper.xml

 <resultMap type="StudentAlias" id="studentResultMap" >
		<result property="id"  column = "id"/>
		<result property="name"  column = "name"/>
		<result property="gender"  column = "gender"/>
		<result property="phone"  column = "phone"/>
	 <collection property="courses" ofType="CourseAlias" column="id"
	 select="com.chinasofti.mapper.CourseMapper.findByid"></collection>
	 
	 </resultMap>
	 
	 <!--多对多 查询指定课程的所以学生  -->
	<select id="findStudentBycourseId"  parameterType="int" resultMap="studentResultMap">
	select student.*
	from student,	student_course
	where student_course.course_id=#{id} and student.id=student_course.stu_id
	</select>

CourseMapper.java

package com.chinasofti.mapper;

import com.chinassofti.beans.Course;

public interface CourseMapper {
	Course findByid(int id);

}

CourseMapper.xml

<mapper namespace="com.chinasofti.mapper.CourseMapper">
	<!-- 添加
		id : 唯一的标识
		parameterType : 参数类型(用来为sql语句中的占位符赋值) 可选
	 -->
	<!--  <resultMap type="CourseAlias" id="courseResultMap" >
	 <id property="id" column="id"></id>
	 <id property="name" column="name"></id>
	 <collection property="students" ofType="StudentAlias" column="id"
	 select="com.chinasofti.mapper.StudentMapper.findStudentBycourseId"></collection>
	 
	 </resultMap>
	  -->
	 
	<select id="findByid"  resultType="CourseAlias" parameterType="int">
	select  c.*
	from course c	, student_course sc
	where c.id=sc.course_id and sc.stu_id=#{id}
	</select>
	
</mapper>

总结：
1.个人比较倾向使用注解 减少了配置文件的存在看上去也比较清晰
当是配置文件也需要学会的呢
2。使用注解方法的时候 ， 结果映射多对一 实体类是中对应类型是一个对象则使用one=@One（）；对应配置文件的association（可以看前面多对一时候的代码）
一对多实体类中是个集合则结果映射的时候使用many=@Many（）对应配置文件的collection