Mybatis 多表關聯查詢（1） one-to-one關係

推薦有mysql基礎和java基礎的夥伴閱讀。
需要的jar包為
mysql-jdbc.jar(mysql資料包)
mybatis-3.2.1.jar(mybatis核心包)

基本檔案

• MybatisUtil.java （工具類）

package com.adoken.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory = null;
    private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();


    /*讀取mybatis-config.xml檔案*/
    static {
        try {
            InputStream resources = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(resources);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 禁止外部建立
     */
    private MybatisUtil() {
    }


    public static SqlSession getSession() {
        SqlSession session = threadLocal.get();
        if (session == null) {
            session = sqlSessionFactory.openSession();
        }
        return session;
    }


    public static void closeSession(SqlSession session) {
        if (session != null) {
            session.close();
        }
    }

    /*測試連線*/
    public static void main(String[] args) {
        SqlSession session = MybatisUtil.getSession();
        MybatisUtil.closeSession(session);
    }

}

• db.properties （資料庫配置資訊檔案）

username=root
password=root
url=jdbc:mysql://127.0.0.1:3306/un?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
driver=com.mysql.jdbc.Driver

• mybatis- config.xml （mybatis配置資訊檔案）

<?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"/>
    <!-- 環境配置 -->
    <environments default="mysql_development">
        <environment id="mysql_development">
            <!-- type="JDBC" 代表使用JDBC的提交和回滾來管理事務 -->
            <transactionManager type="JDBC"/>
            <!-- mybatis提供了3種資料來源型別，分別是：POOLED,UNPOOLED,JNDI -->
            <!-- POOLED 表示支援JDBC資料來源連線池 -->
            <!-- UNPOOLED 表示不支援資料來源連線池 -->
            <!-- JNDI 表示支援外部資料來源連線池 -->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!-- 告知對映檔案方式1，一個一個的配置  -->
        <mapper resource="com/adoken/mapper/PeopleMapper.xml"/>
        <mapper resource="com/adoken/mapper/CardMapper.xml"/>
    </mappers>
</configuration>

• one2one.sql （mysql指令碼）
  一個人一個身份證號碼

DROP TABLE IF EXISTS PEOPLE;
DROP TABLE IF EXISTS CARD;

CREATE TABLE CARD(
  ID INT PRIMARY  KEY AUTO_INCREMENT,
  NUM VARCHAR(20) NOT NULL
);
INSERT INTO CARD (NUM) VALUES ("123456");
INSERT INTO CARD (NUM) VALUES ("456789");

CREATE TABLE PEOPLE(
 ID INT PRIMARY  KEY AUTO_INCREMENT,
 NAME VARCHAR(20) NOT NULL,
 C_ID INT ,
 FOREIGN KEY(C_ID) REFERENCES CARD(ID)
);

INSERT INTO PEOPLE (NAME,C_ID) VALUES ("張三",1);
INSERT INTO PEOPLE (NAME,C_ID) VALUES ("李四",2);

需求:查詢張三的身份證號碼

• 建立bean(實體類)

package com.adoken.bean;

/**
 * 身份證類
 */
public class Card {
    private Integer id;
    private String num;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNum() {
        return num;
    }

    public void setNum(String num) {
        this.num = num;
    }
}

package com.adoken.bean;

/**
 * 人類
 */
public class People {
    private Integer id;
    private String name;
    private Card card;//關聯屬性

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Card getCard() {
        return card;
    }

    public void setCard(Card card) {
        this.card = card;
    }
}

• mapper.xml檔案

Cardmapper.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.adoken.mapper.CardMapper">
    <resultMap id="cardMap" type="com.adoken.bean.Card">
        <id property="id" column="id"/>
        <result property="num" column="num"/>
    </resultMap>


</mapper>

PeopleMapper.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.adoken.mapper.PeopleMapper">


    <resultMap id="PeopleMap" type="com.adoken.bean.People">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="card" resultMap="com.adoken.mapper.CardMapper.cardMap"/>
    </resultMap>
    <select id="findCardNum" resultMap="PeopleMap">
      select p.name,c.num
      from people p inner join card c
      where p.c_id=c.id
      and p.name=#{name}
    </select>
</mapper>

---

寫一個測試類

public class PeopleDaoImpl extends BaseDaoImpl implements PeopleDao {
    public static void main(String[] args) {
        //需求通過查詢張三同學的身份證資訊，列印出姓名，身份證號
        People p = findCardNum("張三");
        System.out.println(p.getName() + "的身份證號碼：" + p.getCard().getNum());
    }

    public static People findCardNum(String name) {
        SqlSession session = null;
        try {
            session = MybatisUtil.getSession();
            return session.selectOne("com.adoken.mapper.PeopleMapper.findCardNum", name);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            MybatisUtil.closeSession(session);
        }
    }

說明

1，首先我的需求是單向的，僅是通過人的資訊查詢身份證號碼。所以資料庫中people表有外來鍵到card表
2，People類中建立Card實體屬性（關聯屬性）
3，核心是（PeopleMapper.xml中的ResultMap）

 <resultMap id="PeopleMap" type="com.adoken.bean.People">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!-- 引入CardMapper.xml檔案中id= cardMap 的resultMap -->
        <!--com.adoken.mapper.CardMapper為CardMapper.xml的namespace（別名）-->
        <association property="card" resultMap="com.adoken.mapper.CardMapper.cardMap"/>
    </resultMap>

---