MyBatis實(shí)現(xiàn)多表查詢——案例：訂單

一、數(shù)據(jù)庫表創(chuàng)建

users用戶表、products商品表、types商品類型表炬转、orders訂單表、details訂單詳情表
表結(jié)構(gòu).png
details.png
orders.png
products.png
types.png
users.png

二算灸、使用MyBatis完成相應(yīng)的查詢功能

pom.xml 文件添加 junit扼劈，mysql， mybatis 依賴乎婿。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com</groupId>
    <artifactId>Mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
    </dependencies>
</project>

設(shè)置MyBatis的配置文件 mybatis.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">
        <property name="pass" value="123456"></property>
    </properties>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <typeAliases>
        <package name="com.pojo" />
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${pass}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/mapper/OrderMapper.xml"/>
        <mapper resource="com/mapper/UserMapper.xml"/>
        <mapper resource="com/mapper/DetailMapper.xml"/>
        <mapper resource="com/mapper/ProductMapper.xml"/>
        <mapper resource="com/mapper/TypeMapper.xml"/>
    </mappers>
</configuration>

db.properties數(shù)據(jù)庫的配置文件

mysql版本不同driver要修改

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3307/mysqls?serverTimezone=UTC
user=root
pass=123456

設(shè)計(jì) pojo 相關(guān)類

Users.java

public class Users {
    private int uid;
    private String name;
    private String pass;
    private String phone;
    public int getUid() { return uid; }
    public void setUid(int uid) { this.uid = uid; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getPass() { return pass; }
    public void setPass(String pass) { this.pass = pass; }
    public String getPhone() { return phone; }
    public void setPhone(String phone) { this.phone = phone; }
    @Override
    public String toString() {
        final StringBuffer sb = new StringBuffer("Users{");
        sb.append("uid=").append(uid);
        sb.append(", name='").append(name).append('\'');
        sb.append(", pass='").append(pass).append('\'');
        sb.append(", phone='").append(phone).append('\'');
        sb.append('}');
        return sb.toString();
    }
}

Order.java

public class Order {
    private String oid;
    private double price;
    private String addr;
    private String payType;
    private Users u;
    private List<Detail> details;
    public String getOid() { return oid; }
    public void setOid(String oid) { this.oid = oid; }
    public double getPrice() { return price; }
    public void setPrice(double price) { this.price = price; }
    public String getAddr() { return addr; }
    public void setAddr(String addr) { this.addr = addr; }
    public String getPayType() { return payType; }
    public void setPayType(String payType) { this.payType = payType; }
    public Users getU() { return u; }
    public void setU(Users u) { this.u = u; }
    public List<Detail> getDetails() { return details; }
    public void setDetails(List<Detail> details) { this.details = details; }
    @Override
    public String toString() {
        final StringBuffer sb = new StringBuffer("Order{");
        sb.append("oid='").append(oid).append('\'');
        sb.append(", price=").append(price);
        sb.append(", addr='").append(addr).append('\'');
        sb.append(", payType='").append(payType).append('\'');
        sb.append(", u=").append(u);
        sb.append(", details=").append(details);
        sb.append('}');
        return sb.toString();
    }
}

Product.java

public class Product {
    private String pid;
    private String name;
    private String img;
    private double price;
    private Types t;
    public String getPid() { return pid; }
    public void setPid(String pid) { this.pid = pid; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getImg() { return img; }
    public void setImg(String img) { this.img = img; }
    public double getPrice() { return price; }
    public void setPrice(double price) { this.price = price; }
    public Types getT() { return t; }
    public void setT(Types t) { this.t = t; }
    @Override
    public String toString() {
        final StringBuffer sb = new StringBuffer("Product{");
        sb.append("pid='").append(pid).append('\'');
        sb.append(", name='").append(name).append('\'');
        sb.append(", img='").append(img).append('\'');
        sb.append(", price=").append(price);
        sb.append(", t=").append(t);
        sb.append('}');
        return sb.toString();
    }
}

Types.java

public class Types {
    private String tid;
    private String name;
    public String getTid() { return tid; }
    public void setTid(String tid) { this.tid = tid; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    @Override
    public String toString() {
        final StringBuffer sb = new StringBuffer("Types{");
        sb.append("tid='").append(tid).append('\'');
        sb.append(", name='").append(name).append('\'');
        sb.append('}');
        return sb.toString();
    }
}

Detail.java

public class Detail {
    private String did;
    private int count;
    private Product pro;
    public String getDid() { return did; }
    public void setDid(String did) { this.did = did; }
    public int getCount() { return count; }
    public void setCount(int count) { this.count = count; }
    public Product getPro() { return pro; }
    public void setPro(Product pro) { this.pro = pro; }
    @Override
    public String toString() {
        final StringBuffer sb = new StringBuffer("Detail{");
        sb.append("did='").append(did).append('\'');
        sb.append(", count=").append(count);
        sb.append(", pro=").append(pro);
        sb.append('}');
        return sb.toString();
    }
}

設(shè)置映射文件mapper

namespace和id組合唯一,可自定義
使用resultMap進(jìn)行聯(lián)合查詢测僵，對(duì)應(yīng)pojo內(nèi)的屬性名(property)和數(shù)據(jù)庫表中的列名相同,可省略不寫result標(biāo)簽
使用association標(biāo)簽進(jìn)行對(duì)一的表結(jié)構(gòu)聯(lián)合查詢
使用collection標(biāo)簽進(jìn)行對(duì)多的表結(jié)構(gòu)聯(lián)合查詢

UserMapper.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.pojo.UserMapper">
    <select id="getUserByUid" resultType="Users">
      select * from users where uid = #{uid}
    </select>
</mapper>

OrderMapper.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.pojo.OrderMapper">
    <select id="getOrderByOid" resultMap="orderMap">
      select * from orders where oid = #{oid}
    </select>
    <resultMap id="orderMap" type="com.pojo.Order">
        <id property="oid" column="oid" ></id>
        <result column="price" property="price" />
        <result column="addr" property="addr" />
        <result column="payType" property="payType" />
        <association property="u" column="uid" select="com.pojo.UserMapper.getUserByUid"></association>
        <collection property="details" column="oid" select="com.pojo.DetailMapper.getDetailsByOid" ofType="detail" />
    </resultMap>
</mapper>

DetailMapper.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.pojo.DetailMapper">
    <select id="getDetailsByOid" resultMap="detailMap">
      select * from details where oid = #{oid}
    </select>
    <resultMap id="detailMap" type="com.pojo.Detail">
        <id column="did" property="did"></id>
        <result property="count" column="count" />
        <association property="pro" column="pid" select="com.pojo.ProductMapper.getProductByPid" />
    </resultMap>
</mapper>

TypeMapper.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.pojo.TypeMapper">
    <select id="getTypesByTid" resultType="com.pojo.Types">
      select * from types where tid = #{tid}
    </select>
</mapper>

ProductMapper.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.pojo.ProductMapper">
    <select id="getProductByPid" resultMap="productMap">
      select * from products where pid = #{uid}
    </select>
    <resultMap id="productMap" type="com.pojo.Product">
        <id column="pid" property="pid"></id>
        <association property="t" column="tid" select="com.pojo.TypeMapper.getTypesByTid" />
    </resultMap>
</mapper>

測(cè)試類 Test.java

public class TestOrders {
    private SqlSessionFactory sf = null;
    private SqlSession session = null;
    @Before
    public void setUp(){
        try {
            sf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            session = sf.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    @After
    public void tearDown(){
        if(session != null){
            session.close();
            session = null;
        }
    }
    @Test
    public void testGetOrderByOid(){
        Order order = session.selectOne("com.pojo.OrderMapper.getOrderByOid", "bfa2974d62cc11eaa62b8cec4b26e06a");
        System.out.println(order);
    }
}

測(cè)試結(jié)果

Opening JDBC Connection
Created connection 1881561036.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@702657cc]
==>  Preparing: select * from orders where oid = ? 
==> Parameters: bfa2974d62cc11eaa62b8cec4b26e06a(String)
<==    Columns: oid, uid, price, payType, addr
<==        Row: bfa2974d62cc11eaa62b8cec4b26e06a, 1, 4799.0, AAA, 支付寶
====>  Preparing: select * from users where uid = ? 
====> Parameters: 1(Integer)
<====    Columns: uid, name, pass, phone
<====        Row: 1, LKT, 123456, 18111111111
<====      Total: 1
====>  Preparing: select * from details where oid = ? 
====> Parameters: bfa2974d62cc11eaa62b8cec4b26e06a(String)
<====    Columns: did, count, oid, pid
<====        Row: 304adbc162cd11eaa62b8cec4b26e06a, 2, bfa2974d62cc11eaa62b8cec4b26e06a, 4445ff1c62cc11eaa62b8cec4b26e06a
======>  Preparing: select * from products where pid = ? 
======> Parameters: 4445ff1c62cc11eaa62b8cec4b26e06a(String)
<======    Columns: pid, name, img, price, tid
<======        Row: 4445ff1c62cc11eaa62b8cec4b26e06a, BlackShark2pro, BlackShark.jpg, 3299.0, baa46abb62cb11eaa62b8cec4b26e06a
========>  Preparing: select * from types where tid = ? 
========> Parameters: baa46abb62cb11eaa62b8cec4b26e06a(String)
<========    Columns: tid, name
<========        Row: baa46abb62cb11eaa62b8cec4b26e06a, AAA
<========      Total: 1
<======      Total: 1
<====        Row: 304bca1b62cd11eaa62b8cec4b26e06a, 1, bfa2974d62cc11eaa62b8cec4b26e06a, 70106ee762cc11eaa62b8cec4b26e06a
======>  Preparing: select * from products where pid = ? 
======> Parameters: 70106ee762cc11eaa62b8cec4b26e06a(String)
<======    Columns: pid, name, img, price, tid
<======        Row: 70106ee762cc11eaa62b8cec4b26e06a, BlackShark3pro, BlackShark3pro.jpg, 4799.0, baa49b9762cb11eaa62b8cec4b26e06a
========>  Preparing: select * from types where tid = ? 
========> Parameters: baa49b9762cb11eaa62b8cec4b26e06a(String)
<========    Columns: tid, name
<========        Row: baa49b9762cb11eaa62b8cec4b26e06a, BBB
<========      Total: 1
<======      Total: 1
<====        Row: 88d56e6a62cd11eaa62b8cec4b26e06a, 1, bfa2974d62cc11eaa62b8cec4b26e06a, 70106ee762cc11eaa62b8cec4b26e06a
<====      Total: 3
<==      Total: 1
Order{oid='bfa2974d62cc11eaa62b8cec4b26e06a', price=4799.0, addr='支付寶', payType='AAA', u=Users{uid=1, name='LKT', pass='123456', phone='18111111111'}, details=[Detail{did='304adbc162cd11eaa62b8cec4b26e06a', count=2, pro=Product{pid='4445ff1c62cc11eaa62b8cec4b26e06a', name='BlackShark2pro', img='BlackShark.jpg', price=3299.0, t=Types{tid='baa46abb62cb11eaa62b8cec4b26e06a', name='AAA'}}}, Detail{did='304bca1b62cd11eaa62b8cec4b26e06a', count=1, pro=Product{pid='70106ee762cc11eaa62b8cec4b26e06a', name='BlackShark3pro', img='BlackShark3pro.jpg', price=4799.0, t=Types{tid='baa49b9762cb11eaa62b8cec4b26e06a', name='BBB'}}}, Detail{did='88d56e6a62cd11eaa62b8cec4b26e06a', count=1, pro=Product{pid='70106ee762cc11eaa62b8cec4b26e06a', name='BlackShark3pro', img='BlackShark3pro.jpg', price=4799.0, t=Types{tid='baa49b9762cb11eaa62b8cec4b26e06a', name='BBB'}}}]}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@702657cc]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@702657cc]
Returned connection 1881561036 to pool.

最后編輯于：2020.03.20 23:35:36

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子捍靠，更是在濱河造成了極大的恐慌沐旨，老刑警劉巖，帶你破解...
沈念sama閱讀 218,755評(píng)論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件榨婆，死亡現(xiàn)場(chǎng)離奇詭異磁携，居然都是意外死亡，警方通過查閱死者的電腦和手機(jī)良风，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,305評(píng)論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門谊迄，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人烟央，你說我怎么就攤上這事统诺。” “怎么了疑俭？”我有些...
開封第一講書人閱讀 165,138評(píng)論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵粮呢，是天一觀的道長。經(jīng)常有香客問我钞艇，道長啄寡，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,791評(píng)論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任哩照，我火速辦了婚禮挺物，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘飘弧。我一直安慰自己识藤，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 67,794評(píng)論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布眯牧。她就那樣靜靜地躺著蹋岩，像睡著了一般。火紅的嫁衣襯著肌膚如雪学少。梳的紋絲不亂的頭發(fā)上剪个，一...
開封第一講書人閱讀 51,631評(píng)論 1贊 305
城市分裂傳說
那天，我揣著相機(jī)與錄音版确，去河邊找鬼扣囊。笑死，一個(gè)胖子當(dāng)著我的面吹牛绒疗，可吹牛的內(nèi)容都是我干的侵歇。我是一名探鬼主播，決...
沈念sama閱讀 40,362評(píng)論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼吓蘑，長吁一口氣：“原來是場(chǎng)噩夢(mèng)啊……” “哼惕虑！你這毒婦竟也來了坟冲？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 39,264評(píng)論 0贊 276
萬榮殺人案實(shí)錄
序言：老撾萬榮一對(duì)情侶失蹤溃蔫，失蹤者是張志新（化名）和其女友劉穎健提，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體伟叛，經(jīng)...
沈念sama閱讀 45,724評(píng)論 1贊 315
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡私痹，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,900評(píng)論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了统刮。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片紊遵。...
茶點(diǎn)故事閱讀 40,040評(píng)論 1贊 350
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖侥蒙，靈堂內(nèi)的尸體忽然破棺而出暗膜，到底是詐尸還是另有隱情，我是刑警寧澤辉哥，帶...
沈念sama閱讀 35,742評(píng)論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布桦山，位于F島的核電站，受9級(jí)特大地震影響醋旦，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜会放，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,364評(píng)論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一饲齐、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧咧最，春花似錦捂人、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,944評(píng)論 0贊 22
一樁弒父案滥搭，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至捣鲸，卻和暖如春瑟匆，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背栽惶。一陣腳步聲響...
開封第一講書人閱讀 33,060評(píng)論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工愁溜，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人外厂。一個(gè)月前我還...
沈念sama閱讀 48,247評(píng)論 3贊 371
代替公主和親
正文我出身青樓冕象，卻偏偏與公主長得像，于是被迫代替她去往敵國和親汁蝶。傳聞我的和親對(duì)象是個(gè)殘疾皇子渐扮，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,979評(píng)論 2贊 355

MyBatis實(shí)現(xiàn)多表查詢——案例：訂單

一、數(shù)據(jù)庫表創(chuàng)建

二算灸、使用MyBatis完成相應(yīng)的查詢功能

推薦閱讀更多精彩內(nèi)容