【MyBatis】对多的关联映射查询

数据库脚本：本地下载

案例

1.创建实体类

Goods.java

java

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
package com.bean;

public class Goods {
    private int goodsId;
    private String goodsName;
    private Double goodsPrice;
    private int goodsNum;
    private int goodsType;

    public Goods() {
    }

    public Goods(int goodsId, String goodsName, Double goodsPrice,
                 int goodsNum, int goodsType) {
        super();
        this.goodsId = goodsId;
        this.goodsName = goodsName;
        this.goodsPrice = goodsPrice;
        this.goodsNum = goodsNum;
        this.goodsType = goodsType;
    }

    public int getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(int goodsId) {
        this.goodsId = goodsId;
    }

    public String getGoodsName() {
        return goodsName;
    }

    public void setGoodsName(String goodsName) {
        this.goodsName = goodsName;
    }

    public Double getGoodsPrice() {
        return goodsPrice;
    }

    public void setGoodsPrice(Double goodsPrice) {
        this.goodsPrice = goodsPrice;
    }

    public int getGoodsNum() {
        return goodsNum;
    }

    public void setGoodsNum(int goodsNum) {
        this.goodsNum = goodsNum;
    }

    public int getGoodsType() {
        return goodsType;
    }

    public void setGoodsType(int goodsType) {
        this.goodsType = goodsType;
    }

    @Override
    public String toString() {
        return "Goods [goodsId=" + goodsId + ", goodsName=" + goodsName
                + ", goodsPrice=" + goodsPrice + ", goodsNum=" + goodsNum
                + ", goodsType=" + goodsType + "]";
    }
}
[/sourcecode]
        [sourcecode language=”java” title=”com.bean.GoodsType.java”]

        package com.bean;

import java.util.List;

public class GoodsType {
    private int typeId;
    private String typeName;
    private List<Goods> goodsList;

    public GoodsType() {
    }

    public GoodsType(int typeId, String typeName, List<Goods> goodsList) {
        super();
        this.typeId = typeId;
        this.typeName = typeName;
        this.goodsList = goodsList;
    }

    public int getTypeId() {
        return typeId;
    }

    public void setTypeId(int typeId) {
        this.typeId = typeId;
    }

    public String getTypeName() {
        return typeName;
    }

    public void setTypeName(String typeName) {
        this.typeName = typeName;
    }

    public List<Goods> getGoodsList() {
        return goodsList;
    }

    public void setGoodsList(List<Goods> goodsList) {
        this.goodsList = goodsList;
    }

    @Override
    public String toString() {
        return "GoodsType [typeId=" + typeId + ", typeName=" + typeName
                + ", goodsList=" + goodsList + "]";
    }
}

2.编写GoodsTypeMapper接口

GoodsTypeMapper.java

java

01
02
03
04
05
06
07
08
09
package com.mapper;

import java.util.List;

import com.bean.GoodsType;

public interface GoodsTypeMapper {
    public List<GoodsType> findAll();
}

3.编写XML文件

（1）第一种，连接查询所有商品信息

GoodsMapper.xml

xml

01
02
03
04
05
06
07
08
09
10
11
12
<?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.mapper.GoodsMapper">
<resultMap type="com.bean.Goods" id="resultGoods">
<id property="goodsId" column="goods_id" javaType="int" />
<result property="goodsName" column="goods_name" javaType="java.lang.String" />
<result property="goodsPrice" column="goods_price" javaType="double" />
<result property="goodsNum" column="goods_num" javaType="int" />
<result property="goodsType" column="goods_type" javaType="int" />
</resultMap>
</mapper>

GoodsTypeMapper.xml

xml

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
<?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.mapper.GoodsTypeMapper">
<select id="findAll" resultMap="resultGoodsType">
select * from goods_type
left join goods
on goods.goods_type = goods_type.typeId
</select>

<resultMap type="com.bean.GoodsType" id="baseGoodsType">
<id property="typeId" column="typeId" javaType="int" />
<result property="typeName" column="typeName" javaType="java.lang.String" />
</resultMap>
<resultMap type="com.bean.GoodsType" id="resultGoodsType" extends="baseGoodsType">
<collection property="goodsList" javaType="java.util.List"
ofType="com.bean.Goods" resultMap="com.mapper.GoodsMapper.resultGoods"/>
</resultMap>
</mapper>

（2）第二种，调用关联关系对象的Mapper的查询方法。

GoodsMapper.java

java

01
02
03
04
05
06
07
08
09
package com.mapper;

import java.util.List;

import com.bean.Goods;

public interface GoodsMapper {
    public List<Goods> findByType(int goodsTypeId);
}

GoodsMapper.xml

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
<?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.mapper.GoodsMapper">
<select id="findByType" parameterType="int" resultMap="resultGoods">
select * from goods where goods_type = #{id}
</select>
<resultMap type="com.bean.Goods" id="resultGoods">
<id property="goodsId" column="goods_id" javaType="int" />
<result property="goodsName" column="goods_name" javaType="java.lang.String" />
<result property="goodsPrice" column="goods_price" javaType="double" />
<result property="goodsNum" column="goods_num" javaType="int" />
<result property="goodsType" column="goods_type" javaType="int" />
</resultMap>
</mapper>

GoodsTypeMapper.xml

xml

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
<?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.mapper.GoodsTypeMapper">
<select id="findAll" resultMap="resultGoodsType">
select * from goods_type
</select>

<resultMap type="com.bean.GoodsType" id="baseGoodsType">
<id property="typeId" column="typeId" javaType="int" />
<result property="typeName" column="typeName" javaType="java.lang.String" />
</resultMap>
<resultMap type="com.bean.GoodsType" id="resultGoodsType" extends="baseGoodsType">
<collection property="goodsList" javaType="java.util.List"
ofType="com.bean.Goods" select="com.mapper.GoodsMapper.findByType" column="typeId" />
</resultMap>
</mapper>

4.编写mybatis.xml

mybatis.xml

xml

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
<?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://127.0.0.1:3306/spring?useUnicode=true&amp;characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/mapper/GoodsMapper.xml" />
<mapper resource="com/mapper/GoodsTypeMapper.xml" />
</mappers>
</configuration>

5.编写测试类

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package com.test;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

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 org.junit.Before;
import org.junit.Test;

import com.bean.GoodsType;
import com.mapper.GoodsTypeMapper;

public class test {

    private SqlSession sqlSession;

    public void test01() {
        try {
            GoodsTypeMapper goodsTypeMapper = this.sqlSession.getMapper(GoodsTypeMapper.class);
            List<GoodsType> GoodsTypeList = goodsTypeMapper.findAll();
            for (GoodsType goodsType : GoodsTypeList) {
                System.out.println(goodsType);
            }
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            this.sqlSession.close();
        }
    }

    @Test
    public void testF(){
        test01();
    }

    @Before
    public void before() {
        try {
            String resource = "mybatis.xml";
            Reader reader = Resources.getResourceAsReader(resource);
            SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
            this.sqlSession = ssf.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

如果觉得文章对你有用，请随意赞赏

MyBatis

【MyBatis】对多的关联映射查询

https://www.benzhu.xyz/archives/mybatis007

作者

小猪

发布于

2019-03-31

更新于

2024-11-24

许可协议

CC BY 4.0

案例

1.创建实体类

2.编写GoodsTypeMapper接口

3.编写XML文件

4.编写mybatis.xml

5.编写测试类

作者

发布于

更新于

许可协议

微信扫一扫：分享

评论