1、一對一關聯查詢的案例

（1）需求

根據班級id查詢班級資訊（帶老師資訊）

（2）建立表和資料

建立一張教師表和班級表，這裡我們假設一個老師隻負責教一個班，那麼老師和班級之間的關系就是一種一對一的關系。

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('teacher1');  
INSERT INTO teacher(t_name) VALUES('teacher2');  
  
INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);  
INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

（3）建構實體

老師：

public class Teacher {  
  
    // 定義實體類的屬性，與teacher表中的字段對應  
    private int id; // id===>t_id  
    private String name; // name===>t_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 + "]";  
    }  
}

班級：

public class Classes {  
  
    // 定義實體類的屬性，與class表中的字段對應  
    private int id; // id===>c_id  
    private String name; // name===>c_name  
  
    /** 
     * class表中有一個teacher_id字段，是以在Classes類中定義一個teacher屬性， 
     * 用于維護teacher和class之間的一對一關系，通過這個teacher屬性就可以知道這個班級是由哪個老師負責的 
     */  
    private 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 "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher  
                + "]";  
    }  
}

（4）定義Mapper對象

public interface ClassMapper {  
    /** 
     * 用于測試，非懶加載的one2one的實作 
     *  
     * @param id 
     * @return 
     */  
    public Classes getClass1(int id);  
  
    /** 
     * 用于測試，懶加載的one2one的實作 
     *  
     * @param id 
     * @return 
     */  
    public Classes getClass2(int id);  
}

（5）定義sql映射檔案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="com.baowei.mapper.ClassMapper">  
  
    <!--   
                 根據班級id查詢班級資訊(帶老師的資訊)  
         ##1. 聯表查詢(非懶加載的實作)  
         SELECT * FROM class c,teacher t WHERE c.teacher_id=t.t_id AND c.c_id=1;  
           
         ##2. 執行兩次查詢(懶加載的實作)  
         SELECT * FROM class WHERE c_id=1;  //teacher_id=1  
         SELECT * FROM teacher WHERE t_id=1;//使用上面得到的teacher_id  
    -->  
  
     <!--   
                       方式一：(非懶加載的實作)  
                                     嵌套結果：使用嵌套結果映射來處理重複的聯合結果的子集  
                                     封裝聯表查詢的資料(去除重複的資料)  
             select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1  
     -->  
    <select id="getClass1" parameterType="int" resultMap="ClassResultMap">  
        select *  
        from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}  
    </select>  
    <!-- 使用resultMap映射實體類和字段之間的一一對應關系 -->  
    <resultMap type="com.baowei.entity.Classes" id="ClassResultMap">  
        <id property="id" column="c_id" />  
        <result property="name" column="c_name" />  
        <association property="teacher" javaType="com.baowei.entity.Teacher">  
            <id property="id" column="t_id" />  
            <result property="name" column="t_name" />  
        </association>  
    </resultMap>  
  
    <!--   
                  方式二：(懶加載的實作)  
                           嵌套查詢：通過執行另外一個SQL映射語句來傳回預期的複雜類型  
         SELECT * FROM class WHERE c_id=1;  
         SELECT * FROM teacher WHERE t_id=1   //1 是上一個查詢得到的teacher_id的值  
     -->  
    <select id="getClass2" parameterType="int" resultMap="ClassResultMap2">  
        select *  
        from class where c_id=#{id}  
    </select>  
    <!-- 使用resultMap映射實體類和字段之間的一一對應關系 -->  
    <resultMap type="com.baowei.entity.Classes" id="ClassResultMap2">  
        <id property="id" column="c_id" />  
        <result property="name" column="c_name" />  
        <association property="teacher" column="teacher_id"  
            select="getTeacher" />  
    </resultMap>  
    <select id="getTeacher" parameterType="int"  
        resultType="com.baowei.entity.Teacher">  
        SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}  
    </select>  
  
</mapper>

(6)SqlMapConfig.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>  
  
    <!--用于加載資料庫的連接配接配置 -->  
    <properties resource="db.properties" />  
    <!--用于設定懶加載的設定 -->  
    <settings>  
        <!-- 打開延遲加載的開關 -->  
        <setting name="lazyLoadingEnabled" value="true" />  
        <!-- 将積極加載改為消息加載即按需加載 -->  
        <setting name="aggressiveLazyLoading" value="false" />  
    </settings>  
    <!--資料庫的連接配接配置 -->  
    <environments default="development">  
        <environment id="development">  
            <!-- 使用jdbc事務管理，事務控制由mybatis -->  
            <transactionManager type="JDBC" />  
            <!-- 資料庫連接配接池，由mybatis管理 -->  
            <dataSource type="POOLED">  
                <property name="driver" value="${jdbc.driver}" />  
                <property name="url" value="${jdbc.url}" />  
                <property name="username" value="${jdbc.username}" />  
                <property name="password" value="${jdbc.password}" />  
            </dataSource>  
        </environment>  
    </environments>  
    <!-- 加載 映射檔案 -->  
    <mappers>  
        <package name="com.baowei.mapper" />  
    </mappers>  
  
</configuration>

（7）.MyBatisUtil.java工具

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;  
  
public class MyBatisUtil {  
  
    /** 
     * 擷取SqlSessionFactory 
     *  
     * @return SqlSessionFactory 
     */  
    public static SqlSessionFactory getSqlSessionFactory() {  
        String resource = "SqlMapConfig.xml";  
        InputStream inputStream = null;  
        SqlSessionFactory sqlSessionFactory = null;  
        try {  
            inputStream = Resources.getResourceAsStream(resource);  
            sqlSessionFactory = new SqlSessionFactoryBuilder()  
                    .build(inputStream);  
  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        return sqlSessionFactory;  
    }  
  
    /** 
     * 擷取SqlSession 
     *  
     * @return SqlSession 
     */  
    public static SqlSession getSqlSession() {  
        return getSqlSessionFactory().openSession();  
    }  
  
    /** 
     * 擷取SqlSession 
     *  
     * @param isAutoCommit 
     *            true 表示建立的SqlSession對象在執行完SQL之後會自動送出事務 false 
     *            表示建立的SqlSession對象在執行完SQL之後不會自動送出事務 
     *            ，這時就需要我們手動調用sqlSession.commit()送出事務 
     * @return SqlSession 
     */  
    public static SqlSession getSqlSession(boolean isAutoCommit) {  
        return getSqlSessionFactory().openSession(isAutoCommit);  
    }  
  
    public static void main(String[] args) {  
        System.out.println(getSqlSessionFactory());  
    }  
}

（8）測試代碼

import org.apache.ibatis.session.SqlSession;  
import org.junit.Test;  
  
import com.baowei.entity.Classes;  
import com.baowei.mapper.ClassMapper;  
import com.baowei.utils.MyBatisUtil;  
  
public class TestOne2One {  
  
    /** 
     * 非懶加載的one2one的測試 
     */  
    @Test  
    public void testGetClass() {  
        SqlSession sqlSession = MyBatisUtil.getSqlSession();  
  
        ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);  
        Classes clazz = mapper.getClass1(1);  
  
        // 使用SqlSession執行完SQL之後需要關閉SqlSession  
        sqlSession.close();  
        System.out.println(clazz.getId());  
    }  
  
    /** 
     * 懶加載的one2one的測試 
     */  
    @Test  
    public void testGetClass2() {  
        SqlSession sqlSession = MyBatisUtil.getSqlSession();  
  
        ClassMapper mapper = sqlSession.getMapper(ClassMapper.class);  
        Classes clazz = mapper.getClass2(1);  
  
        // 使用SqlSession執行完SQL之後需要關閉SqlSession  
        sqlSession.close();  
        System.out.println(clazz.getId());  
        // 可用于測試懶加載(檢視日志的sql輸出結果，就可以發現使用了懶加載)  
        //System.out.println(clazz.getTeacher());  
  
    }  
}

2.一對多/多對一查詢案例

（1）需求

本執行個體使用顧客和訂單的例子做說明: 一個顧客可以有多個訂單, 一個訂單隻對應一個顧客。

（2）建立表和資料

CREATE DATABASE test;    
  
USE test;  
  
CREATE TABLE person(  
  personId VARCHAR(36) PRIMARY KEY,  
  personName VARCHAR(64),  
  personAddress VARCHAR(128),  
  personTel VARCHAR(11)  
);  
  
CREATE TABLE orders(  
  orderId VARCHAR(36) PRIMARY KEY,  
  orderNumber VARCHAR(20),  
  orderPrice INT,  
  pid VARCHAR(36)  
);  
  
INSERT INTO person VALUES('001', 'Jack', 'Wuhan', '1234567');  
INSERT INTO orders VALUES('O_00001', '00001', 100, '001');  
INSERT INTO orders VALUES('O_00002', '00002', 200, '001');  
  
SELECT p.*, o.*  
FROM person p   
  JOIN orders o ON (p.personId=o.pid)  
WHERE p.personId = '001'

（3）建構實體

客戶：

public class Person {  
  
    private String id;  
    private String name;  
    private String address;  
    private String tel;  
      
    private List<Order> orders;  
      
    @Override  
    public String toString() {  
        return "{id: " + id + ", name: " + name + ", address: " + address + ", tel: " + tel + "}";  
    }  
}

訂單：

public class Order {  
  
    private String id;  
    private String number;  
    private int price;  
      
    private Person person;   
      
    @Override  
    public String toString() {  
        return "{id: " + id + ", number: " + number + ", price: " + price + "}";  
    }  
  
}

（4）一對多實體配置: Person.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.zdp.domain.Person">  
  
    <resultMap type="Person" id="personBean">  
        <id column="personId" property="id"/>  
        <result column="personName" property="name"/>  
        <result column="personAddress" property="address"/>  
        <result column="personTel" property="tel"/>  
          
        <!-- 一對多的關系 -->  
        <!-- property: 指的是集合屬性的值, ofType：指的是集合中元素的類型 -->  
        <collection property="orders" ofType="Order">  
            <id column="orderId" property="id"/>  
            <result column="orderNumber" property="number"/>  
            <result column="orderPrice" property="price"/>  
        </collection>  
    </resultMap>  
      
    <!-- 根據id查詢Person, 關聯将Orders查詢出來 -->  
    <select id="selectPersonById" parameterType="string" resultMap="personBean">  
        select p.*, o.* from person p, orders o where p.personId  = o.pid and p.personId = #{id}  
    </select>  
      
</mapper>

（5）多對一實體配置:order.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.zdp.domain.Order">  
    <resultMap type="Order" id="orderBean">  
        <id column="orderId" property="id"/>  
        <result column="orderNumber" property="number"/>  
        <result column="orderPrice" property="price"/>  
          
        <!-- 多對一的關系 -->  
        <!-- property: 指的是屬性的值, javaType：指的是屬性的類型-->  
        <association property="person" javaType="Person">  
            <id column="personId" property="id"/>  
            <result column="personName" property="name"/>  
            <result column="personAddress" property="address"/>  
            <result column="personTel" property="tel"/>  
        </association>  
    </resultMap>  
      
    <!-- 根據id查詢Order, 關聯将Person查詢出來 -->  
    <select id="selectOrderById" parameterType="string" resultMap="orderBean">  
        select p.*, o.* from person p, orders o where p.personId  = o.pid and o.orderId = #{id}  
    </select>  
      
</mapper>

(6)SqlMapConfig.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>  
    <typeAliases>  
        <typeAlias type="com.zdp.domain.Person" alias="Person"/>    
        <typeAlias type="com.zdp.domain.Order" alias="Order"/>    
    </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/test" />  
                <property name="username" value="root" />  
                <property name="password" value="root" />  
            </dataSource>  
        </environment>  
    </environments>  
    <mappers>  
        <!-- 映射檔案的位置 -->  
        <mapper resource="com/zdp/domain/Person.xml" />  
        <mapper resource="com/zdp/domain/Order.xml" />  
    </mappers>  
</configuration>

(7)測試代碼

/** 
 * 測試一對多和多對一 
 */  
public class MybatisTest {  
  
    private SqlSessionFactory ssf;   
  
    @Before  
    public void initSF() throws Exception {  
        String resource = "sqlMapConfig.xml";  
        InputStream inputStream = Resources.getResourceAsStream(resource);  
        ssf = new SqlSessionFactoryBuilder().build(inputStream);  
    }  
  
    @Test//一對多關聯查詢  
    public void selectPersonById()throws Exception{  
        SqlSession session = ssf.openSession();  
        Person person = session.selectOne("com.zdp.domain.Person.selectPersonById", "001");  
        System.out.println(person.getOrders());  
    }  
      
    @Test//多對一關聯查詢  
    public void selectOrderById()throws Exception{  
        SqlSession session = ssf.openSession();  
        Order order = session.selectOne("com.zdp.domain.Order.selectOrderById", "O_00001");  
        System.out.println(order.getPerson().getName());  
    }  
}

3.參考博文

(1)http://www.cnblogs.com/xdp-gacl/p/4264440.html

(2)http://blog.csdn.net/jkxiaoxing/article/details/52199386

MyBatis一對一，一對多，多對一關聯查詢1、一對一關聯查詢的案例2.一對多/多對一查詢案例3.參考博文

1、一對一關聯查詢的案例

（1）需求

（2）建立表和資料

（3）建構實體

（4）定義Mapper對象

(6)SqlMapConfig.xml配置

（7）.MyBatisUtil.java工具

（8）測試代碼

2.一對多/多對一查詢案例

（1）需求

（2）建立表和資料

（3）建構實體

（4）一對多實體配置: Person.xml

（5）多對一實體配置:order.xml

(6)SqlMapConfig.xml配置

(7)測試代碼

3.參考博文

繼續閱讀

nginx location中斜線的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

用mybatis的generator插件在項目中自動生成dao及entity

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

從mybatis到mybatis plus(mp)

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method