Mybatis学习笔记（三）之主键回填、一对一映射

 

1.主键回填

   在Mysql数据库中主键ID自增后，再次得到数据库生成的主键值。

使用方法如下：

使用keyProperty属性指定是哪个主键字段，同时使用useGeneratedKeys属性告诉这个主键是否使用数据库内置策略生成。

<?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.students.mapper.StudentsMapper">
 
<insert id="insertStudent" parameterType="Students" useGeneratedKeys="true"  keyProperty="stud_id">
  insert into students (name,email,dob)values(#{name},#{email},#{dob})
</insert>
</mapper>

2.一对一映射

  一个学生，对应一个成绩，表 Stu有一个id 列，是  score表的外键,学生和成绩之间存在一对一映射的关系，即一个学生对应成绩表中一个成绩。

学生和成绩表内容如下：

      

具体实现：

方法一：

step1：定义写两个bean  stu 、Score

package com.pojo;

public class Stu {
	private int id;
	private int clazzId;
	private String name;
	private String sex;
	private int age;
	
    private Score score;

package com.pojo;

public class Score {
	private int  id;
	private int  stuId;
	private int  math;
	private int  english;
	private int  pe;

step2:mybatis-config配置文件

<?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>
<typeAliases>
  <package name="com.pojo"/>
</typeAliases>
	 <environments default="development">
		 <environment id="development">
			 <transactionManager type="JDBC"/>
			 <dataSource type="POOLED">
				 <property name="driver" value="com.mysql.jdbc.Driver"/>
				 <property name="url" value="jdbc:mysql://localhost:3306/mybatis4?useSSL=true"/>
				 <property name="username" value="root"/>
				 <property name="password" value="mysql"/>
			 </dataSource>
		 </environment>
	
	 </environments>
	 <mappers>
	     <!-- 对像和数据表的关系 -->
	     <mapper resource="com/students/mapper/StudentsMapper.xml"/>
	 </mappers>
</configuration>

step3:新定义一个接口，来获得学生信息

public interface StudentsMapper {
   public Stu getStudentById(int id);
}

step4:新建一个学生信息的StudentsMapper.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.students.mapper.StudentsMapper">

<resultMap type="stu" id="stumap">
  <id property="id" column="id"/>
  <result property="clazzId" column="clazz_id" />
  <result property="name" column="name" />
  <result property="sex" column="sex" />
  <result property="age" column="age" />
  
  <!-- 接下来是成绩 -->
  <result property="score.math" column="math" />
  <result property="score.english" column="english" />
  <result property="score.pe" column="pe" />
</resultMap>

<select id="getStudentById" resultMap="stumap" parameterType="int">
   select s.id,s.clazz_id,s.name,s.sex,s.age,c.math,c.english,c.pe from stu s,score c where c.stu_id=s.id and s.id=#{id}
</select>

</mapper>

step5:测试

public class TestSelect {

	public static void main(String[] args) {
		 InputStream inputstream=TestSelect.class.getResourceAsStream("/mybatis-config.xml");
		  SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputstream);
		  SqlSession sqlSession=sqlSessionFactory.openSession();
		  StudentsMapper sm=sqlSession.getMapper(StudentsMapper.class);
		  Stu stu=sm.getStudentById(2);
		  System.out.println(stu);
	}
}

方法二：对StudentsMapper.xml做部分修改

<resultMap type="stu" id="stu">
  <id property="id" column="id"/>
  <result property="clazzId" column="clazz_id" />
  <result property="name" column="name" />
  <result property="sex" column="sex" />
  <result property="age" column="age" />
</resultMap> 
 
  <!-- 接下来是成绩 -->
 <resultMap type="stu" id="stumap" extends="stu">
  <result property="score.math" column="math" />
  <result property="score.english" column="english" />
  <result property="score.pe" column="pe" />
</resultMap>

这个方法其实就是把学生成绩和其他信息分别用两个 resultMap来对应，使用继承将两个 resultMap联系起来。

方法三：对StudentsMapper.xml做部分修改，使用<association>元素引用了另外的在同一个 XML 文件中定义的<resultMap>

<resultMap type="score" id="scoremap">
    <result property="math" column="math" />
	<result property="english" column="english" />
	<result property="pe" column="pe" />
</resultMap>

<resultMap type="stu" id="stumap">
  <id property="id" column="id"/>
  <result property="clazzId" column="clazz_id" />
  <result property="name" column="name" />
  <result property="sex" column="sex" />
  <result property="age" column="age" />
  
  <association property="score" resultMap="scoremap" ></association>
  
</resultMap>

方法四：对StudentsMapper.xml做部分修改，使用<association 定义内联的 resultMap。

<resultMap type="stu" id="stumap">
  <id property="id" column="id"/>
  <result property="clazzId" column="clazz_id" />
  <result property="name" column="name" />
  <result property="sex" column="sex" />
  <result property="age" column="age" />
  
  <association property="score" javaType="Score" >
	  <result property="math" column="math" />
	  <result property="english" column="english" />
	  <result property="pe" column="pe" />
  </association>
  
</resultMap>

方法五：使用嵌套 Select 语句

step1:在借口中新定义一个通过id获取学生成绩的方法getScoreById

public interface StudentsMapper {
   public Stu getStudentById(int id);
   public Stu getScoreById(int id);
}

step2:对StudentsMapper.xml做部分修改.

    在这种方式中，<aossication>元素的 select 属性被设置为 id 为getScoreById 的语句,用来触发单独的 SQL 查询加载成绩信息。id 这一列值将会作为输入参数传递给getScoreById 语句。

  fetchType="lazy"   延迟加载，即当要使用这条数据时才去加载这条SQL语句。

<resultMap type="Score" id="scoremap">
      <id property="id" column="id"/>
      <result property="math" column="math" />
	  <result property="english" column="english" />
	  <result property="pe" column="pe" />
</resultMap>

<resultMap type="stu" id="stumap">
  <id property="id" column="id"/>
  <result property="clazzId" column="clazz_id" />
  <result property="name" column="name" />
  <result property="sex" column="sex" />
  <result property="age" column="age" />
  
  <association property="score" select="getScoreById" column="id" fetchType="lazy"> </association>
  
</resultMap>

<select id="getScoreById" resultMap="scoremap" parameterType="int">
select math,english,pe from score where stu_id=#{id} 
</select>

<select id="getStudentById" resultMap="stumap" parameterType="int">
select * from stu where  id=#{id} 
</select>