mybatis--一对一关联查询

(1)创建数据库mybatisonetoone

有两张表，老师表teacher和班级表class，一个class班级对应一个teacher，一个teacher对应一个class

需求是根据班级id查询班级信息(带老师的信息)，

创建teacher和class表：

CREATE DATABASE mybatisonetoone;
CREATE TABLE teacher (
    t_id INT PRIMARY KEY AUTO_INCREMENT,
    t_name VARCHAR(20)
);
CREATE TABLE class (
    c_id INT PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(20),
    teacher_id INT
);
 
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);
INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

(2)创建config/config.xml文件：

<?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="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/mybatisonetoone"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        </dataSource>
        </environment>
</environments>
 
<mappers>
        <!-- // power by http://www.yiibai.com -->
        <mapper resource="mybatis/bean/ClassMapper.xml" />
</mappers>
</configuration>

(3) 实现两个实体类Class.class和Teacher.class

Class.class

package mybatis.bean;
 
public class Class {
    private int id;
    private String name;
    private Teacher teacher;
 
    public Class() {
    }
 
    public Class(int id, String name, Teacher teacher) {
        super();
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }
 
    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 Teacher getTeacher() {
        return teacher;
    }
 
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
 
    @Override
    public String toString() {
        return "Class [id=" + id + ", name=" + name + ", teacher=" + teacher + "]";
    }
}

Teacher.java

package mybatis.bean;
 
public class Teacher {
 
     private int id;
     private String name;
 
     public Teacher() {
     }
 
     public Teacher(int id, String name) {
         super();
         this.id = id;
         this.name = name;
     }
 
     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;
     }
 
     @Override
     public String toString() {
         return "Teacher [id=" + id + ", name=" + name + "]";
     }
}

(4) 然后配置ClassMapper.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="mybatis.bean.classMapper">
<!--根据id查询到一个班级信息（带老师信息） -->
<!-- 嵌套结果 -->
<!--   
<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="mybatis.bean.Class" id="ClassResultMap">
    <id property="id" column="c_id" />
    <result property="name" column="c_name" />
    <association property="teacher" column="teacher_id" javaType="mybatis.bean.Teacher">
    <id property="id" column="t_id" />
    <result property="name" column="t_name" />
</association>
</resultMap> -->
<!--嵌套查询  -->
<select id="getClass" parameterType="int" resultMap="ClassResultMap">
    select * from class where c_id=#{id}
</select>
<resultMap type="mybatis.bean.Class" id="ClassResultMap">
    <id property="id" column="c_id" />
    <result property="name" column="c_name" />
    <association property="teacher" column="teacher_id" javaType="mybatis.bean.Teacher" select="getTeacher">
    </association>
</resultMap>
<!-- 使用了sql别名  -->
<select id="getTeacher" parameterType="int" resultType="mybatis.bean.Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
</mapper>

(5)最后配置Main.class执行查询操作

package Main;
import java.io.Reader;
import java.text.MessageFormat;
import java.util.List;
 
import mybatis.bean.Class;
 
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 
public class Main {
    private static SqlSessionFactory sqlSessionFactory;
    private static Reader reader;
 
    static {
        try {
            reader = Resources.getResourceAsReader("config/config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    public static SqlSessionFactory getSession() {
        return sqlSessionFactory;
    }
 
    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        SqlSession session = sqlSessionFactory.openSession();
        mybatis.bean.Class clazz = session.selectOne("mybatis.bean.classMapper.getClass", 2);
        System.out.println(clazz);
    }
}