mybatis于xml方式和注解方式實(shí)現(xiàn)多表查詢的操作方法

更新時(shí)間：2023年12月04日 10:45:24 作者：憶夢(mèng)～留念

在數(shù)據(jù)庫中,單表的操作是最簡(jiǎn)單的,但是在實(shí)際業(yè)務(wù)中最少也有十幾張表,并且表與表之間常常相互間聯(lián)系,本文給大家介紹mybatis于xml方式和注解方式實(shí)現(xiàn)多表查詢的操作方法,感興趣的朋友一起看看吧

1、解釋

在數(shù)據(jù)庫中，單表的操作是最簡(jiǎn)單的，但是在實(shí)際業(yè)務(wù)中最少也有十幾張表，并且表與表之間常常相互間聯(lián)系；

一對(duì)一、一對(duì)多、多對(duì)多是表與表之間的常見的關(guān)系。

一對(duì)一：一張表A中的一條記錄只能對(duì)應(yīng)另一張表B中的一條記錄，另一張表B中的一條記錄也只能對(duì)應(yīng)一張表A中的一條記錄。如：一個(gè)學(xué)生只能對(duì)應(yīng)一張學(xué)生卡，一張學(xué)生卡只能對(duì)應(yīng)一個(gè)學(xué)生，那么學(xué)生和學(xué)生卡就是一對(duì)一的關(guān)系；
一對(duì)多：一張表A中的一條記錄可以對(duì)應(yīng)另一張表B中的多條記錄，另一張表B中的一條記錄只能對(duì)應(yīng)一張表A中的一條記錄。如：一個(gè)班級(jí)對(duì)應(yīng)多個(gè)學(xué)生，一個(gè)學(xué)生只能對(duì)應(yīng)一個(gè)班級(jí)，所以班級(jí)表中的一條記錄可以對(duì)應(yīng)學(xué)生表的多條數(shù)據(jù)，學(xué)生表中的一條記錄只能對(duì)應(yīng)班級(jí)表的一條數(shù)據(jù)；
多對(duì)多的意思是：一張表A中的一條記錄可以對(duì)應(yīng)另一張表B中的多條記錄，另一張表B中的一條記錄也可以對(duì)應(yīng)一張表A中的多條記錄。如：一個(gè)學(xué)生對(duì)應(yīng)多個(gè)課程，一個(gè)課程對(duì)應(yīng)多個(gè)學(xué)生。多對(duì)多需要設(shè)計(jì)三張表。比如這里出來學(xué)生表和課程表，還需要一張學(xué)生課程關(guān)聯(lián)表。學(xué)生和課程的關(guān)系就存在課程表中。

2、操作

1、數(shù)據(jù)庫中創(chuàng)建表格brand，并插入數(shù)據(jù)

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(11) COLLATE utf8mb4_general_ci NOT NULL,
  `money` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES ('1', 'messi', '1000.00');
INSERT INTO `account` VALUES ('2', 'pep', '1000.00');
select `position`,count(`position`) amount from users group by `position`
select p.product_name productName,sum(p_r.quantity) prquantity,sum(s_r.quantity) srquantity from products p left join purchase_records p_r on p.product_id=p_r.product_id left join sales_records s_r on p.product_id=s_r.product_id where p.state=1 group by p.product_id 
order by srquantity desc limit 0,5;
DROP TABLE IF EXISTS `brand`;
CREATE TABLE `brand` (
  `brand_id` int NOT NULL AUTO_INCREMENT COMMENT '品牌編號(hào)',
  `brand_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '品牌名稱',
  `company_name` varchar(255) COLLATE utf8mb4_general_ci NOT NULL COMMENT '所屬公司名稱',
  `brand_idea` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '品牌理念',
  PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of brand
-- ----------------------------
INSERT INTO `brand` VALUES ('1', '華為手機(jī)', '華為技術(shù)有限公司', 'Make It Possible');
INSERT INTO `brand` VALUES ('2', '榮耀', '深圳市智信新信息技術(shù)有限公司', 'GO BEYOND');
INSERT INTO `brand` VALUES ('3', '紅米', '小米科技有限責(zé)任公司', '不顧一切的熱愛');

2、com.entity中創(chuàng)建實(shí)體類Brand

 
@Data
public class Brand {
    //品牌編號(hào)
    private Integer brandId;
    //品牌名稱
    private String brandName;
    //品牌所屬公司
    private String companyName;
    //品牌理念
    private String brandIdea;
    //旗下手機(jī)列表
    private List<String> phoneList;
}

3、com.entity中修改實(shí)體類Phone

@Data
public class Phone {
//    手機(jī)編號(hào)
    private Integer phoneId;
//    品牌編號(hào)
//    private Integer brandId;
    //手機(jī)品牌
    private Brand brand;
//    手機(jī)型號(hào)
    private String modelNumber;
//    手機(jī)容量
    private Integer capacity;
}

4、實(shí)現(xiàn)查詢手機(jī)信息及其所屬品牌信息

xml方式：修改方法selectAll對(duì)應(yīng)的xml

寫法一：

<resultMap id="phoneMap" type="phone">
        <id column="phone_id" property="phoneId"></id>
        <id column="model_number" property="modelNumber"></id>
        <id column="capacity" property="capacity"></id>
        <id column="brand_id" property="brand.brandId"></id>
        <id column="brand_name" property="brand.brandName"></id>
        <id column="company_name" property="brand.companyName"></id>
        <id column="brand_idea" property="brand.brandIdea"></id>
    </resultMap>
    <select id="selectAll" resultMap="phoneMap">
        SELECT * FROM phone p,brand b
        where p.brand_id = b.brand_id
    </select>

寫法二：

<resultMap id="phoneMap" type="phone">
                <id column="phone_id" property="phoneId"></id>
                <id column="model_number" property="modelNumber"></id>
                <id column="capacity" property="capacity"></id>
        <!--        property:當(dāng)前實(shí)體（Hero）中的屬性名稱(private Hero hero)-->
        <!--        javaType:當(dāng)前實(shí)體（Hero）中的屬性類型(com.cqgcxy.entity.Hero)-->
        <association property="brand" javaType="com.cqgcxy.entity.Brand">
            <id column="brand_id" property="brandId"></id>
            <id column="brand_name" property="brandName"></id>
            <id column="company_name" property="companyName"></id>
            <id column="brand_idea" property="brandIdea"></id>
        </association>
    </resultMap>
    <select id="selectAll" resultMap="phoneMap">
        SELECT * FROM phone p,brand b
        where p.brand_id = b.brand_id
    </select>

注解方式：

注意：注解方式的mapper配置可以不用在配置mapper文件地址，而是只配置包的路徑。

寫法一：修改PhoneMapper接口中selectAll方法

@Select("SELECT * FROM phone p,brand b where p.brand_id = b.brand_id")
    @Results({
            @Result(column = "phone_id",property = "phoneId"),
            @Result(column = "model_number",property = "modelNumber"),
            @Result(column = "capacity",property = "capacity"),
            @Result(column = "brand_id",property = "brand.brandId"),
            @Result(column = "brand_name",property = "brand.brandName"),
            @Result(column = "company_name",property = "brand.companyName"),
            @Result(column = "brand_idea",property = "brand.brandIdea")
    })
    List<Phone> selectAll();

寫法二：

創(chuàng)建并編寫B(tài)randMapper接口

public interface BrandMapper {
    @Select("SELECT * FROM brand WHERE brand_id = #{brandId}")
    Brand selectById(Long brandId);
}

修改PhoneMapper接口中selectAll方法

@Select("SELECT * FROM phone")
@Results({
        @Result(column = "phone_id",property = "phoneId"),
        @Result(column = "model_number",property = "modelNumber"),
        @Result(column = "capacity",property = "capacity"),
        @Result(column = "brand_id",
                property = "brand",
                javaType = Brand.class,
                one = @One(select="com.dao.BrandMapper.selectById")
        )
})
List<Phone> selectAll();

調(diào)用測(cè)試類中MybatisMapperTest中selectAllTest方法測(cè)試

5、實(shí)現(xiàn)查詢品牌信息及其旗下的手機(jī)信息

BrandMapper接口中添加抽象方法selectAll

List<Brand> selectAll();

xml方式：mapper文件夾中創(chuàng)建BrandMapper.xml并編寫selectAll方法對(duì)應(yīng)的sql

<?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.dao.BrandMapper">
    <resultMap id="brandMap" type="com.entity.Brand">
        <id column="brand_id" property="brandId"></id>
        <result column="brand_name" property="brandName"></result>
        <result column="company_name" property="companyName"></result>
        <result column="brand_idea" property="brandIdea"></result>
        <collection property="phoneList" ofType="com.entity.Phone">
            <result column="phone_id" property="phoneId"></result>
            <result column="model_number" property="modelNumber"></result>
            <result column="capacity" property="capacity"></result>
        </collection>
    </resultMap>
    <select id="selectAll" resultMap="brandMap">
        SELECT * FROM brand b LEFT JOIN phone p
                                        ON p.brand_id=b.brand_id
    </select>
</mapper>

測(cè)試

public class BrandMapperTest {
    @Test
    public void selectAll() throws IOException {
        InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqLSessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlsession = sqLSessionFactory.openSession();
        BrandMapper brandMapper = sqlsession.getMapper(BrandMapper.class);
        List<Brand> brands = brandMapper.selectAll();
        brands.forEach(s->{
            System.out.println(s);
        });
        sqlsession.close();
    }
}

為了方便可以

注解方式：

修改PhoneMapper接口中添加selectByBrandId方法

@Select("SELECT * FROM phone WHERE brand_id = #{brandId}")
    Phone selectByBrandId(Integer brandId);

修改BrandMapper接口中selectAll方法

    @Select("SELECT * FROM brand")
    @Results({
            @Result(column = "brand_id",property = "brandId"),
            @Result(column = "brand_name",property = "brandName"),
            @Result(column = "company_name",property = "companyName"),
            @Result(column = "brand_idea",property = "brandIdea"),
            @Result(column = "brand_id",
                    property = "phoneList",
                    javaType = List.class,
                    many = @Many(select = "com.dao.PhoneMapper.selectByBrandId")
            )
    })
    List<Brand> selectAll();