Mybaits总结--一对一查询

一、表结构

CREATE TABLE `class` (
  `c_id` int(2) NOT NULL AUTO_INCREMENT,
  `c_name` varchar(30) DEFAULT NULL,
  `teacher_id` int(2) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id_fk` (`teacher_id`),
  CONSTRAINT `t_id_fk` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `teacher` (
  `t_id` int(2) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

二、表对应的实体

class表和teacher表是一对一的关系，所以，在class实体中存在teacher的实体，或者在teacher实体中存在一个class的实体

class实体

public class Classes {
    //省去getter和setter
	private Integer id;
	private String name;
	private Teacher teacher;
}

teacher

public class Teacher {
    //省去getter和setter
	private Integer id;
	private String name;
}

三、映射文件

  1、使用<association>标签代表对应的实体，指定实体表的字段，Mybaits会将查询出来的结果填充到实体中，一条Sql

<select id="getClass" parameterType="int" resultMap="ClassResultMap">
	  select * from class c, teacher t where c.teacher_id=t.t_id and  c.c_id=#{id}
</select>
<resultMap type="Classes" id="ClassResultMap">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		
		<association property="teacher" javaType="Teacher">
			<id property="id" column="t_id"/>
			<result property="name" column="t_name"/>
		</association>
</resultMap>
	

2、使用<association>标签代表对应的实体，并且指定对应实体的查询sql，2条sql，若查询的是一对一的集合则是1+n条sql

<select id="getClass2" parameterType="int" resultMap="ClassResultMap2"> 
	    select * from class where c_id = #{id}
	</select>
	<resultMap type="Classes" id="ClassResultMap2">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		
		<association property="teacher" select="getTeacher" column="teacher_id">
			
		</association>
	</resultMap>
	
	<select id="getTeacher" resultType="Teacher" parameterType="int">
	  select t_id id, t_name name from teacher where t_id = #{id}
	</select>

四、调用

@Test
	public void test2(){
		SqlSessionFactory factory = MybaitsUtils.getFactory();
		SqlSession session = factory.openSession();
		
		String statement = "zhuojing.mybaits.onetoone.getclassMapper.getClass";
		Classes classes  = session.selectOne(statement,1);
		System.out.println(classes);
		
		session.close();
	}