位置：首页-资讯-后端开发

MyBatis中多条件查询商品的三种方法及区别

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

一、Sql语句设置多个参数有几种方式

1、散装参数:需要使用@Param标记Sql语句中占位符处的名称例如 #{name}

在这里插入图片描述

2、实体类封装参数

只需要保证Sql中的参数名和实体类属性名对应上，即可设置成功
BrandMapper.xml中的SQL语句不用动，把TestBrandMapper中的代码修改即可

在这里插入图片描述

3、Map集合

只需要保证Sql中的参数名和Map集合的键的名称对应上，即可设置成功。
BrandMapper.xml中Sql语句不用改

定义一个默认的map即可

在这里插入图片描述

二、代码附上

0、pom.xml中依赖

<dependencies>
        <!--mybatis 依赖-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>

        <!--mysql 驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>

        <!--junit 单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>

        <!-- 添加slf4j日志api -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.20</version>
        </dependency>
        <!-- 添加logback-classic依赖 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>
        <!-- 添加logback-core依赖 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>
    </dependencies>

1、数据库创建

-- 删除tb_brand表
drop table if exists tb_brand;
-- 创建tb_brand表
create table tb_brand
(
    -- id 主键
    id           int primary key auto_increment,
    -- 品牌名称
    brand_name   varchar(20),
    -- 企业名称
    company_name varchar(20),
    -- 排序字段
    ordered      int,
    -- 描述信息
    description  varchar(100),
    -- 状态：0：禁用  1：启用
    status       int
);
-- 添加数据
insert into tb_brand (brand_name, company_name, ordered, description, status)
values ('三只松鼠', '三只松鼠股份有限公司', 5, '好吃不上火', 0),
       ('华为', '华为技术有限公司', 100, '华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界', 1),
       ('小米', '小米科技有限公司', 50, 'are you ok', 1);

2、Brand实体类

package com.ligong.popj;

public class Brand {
    // id 主键
    private Integer id;
    // 品牌名称
    private String brandName;
    // 企业名称
    private String companyName;
    // 排序字段
    private Integer ordered;
    // 描述信息
    private String description;
    // 状态：0：禁用  1：启用
    private Integer status;

    public Brand() {
    }

    public Brand(Integer id, String brandName, String companyName, Integer ordered, String description, Integer status) {
        this.id = id;
        this.brandName = brandName;
        this.companyName = companyName;
        this.ordered = ordered;
        this.description = description;
        this.status = status;
    }

    
    public Integer getId() {
        return id;
    }

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

    
    public String getBrandName() {
        return brandName;
    }

    
    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    
    public String getCompanyName() {
        return companyName;
    }

    
    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    
    public Integer getOrdered() {
        return ordered;
    }

    
    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    
    public String getDescription() {
        return description;
    }

    
    public void setDescription(String description) {
        this.description = description;
    }

    
    public Integer getStatus() {
        return status;
    }

    
    public void setStatus(Integer status) {
        this.status = status;
    }

    public String toString() {
        return "Brand{id = " + id + ", brandName = " + brandName + ", companyName = " + companyName + ", ordered = " + ordered + ", description = " + description + ", status = " + status + "}";
    }

    //省略 setter and getter。自己写时要补全这部分代码
}

3、BrandMap接口

package com.ligong.mapper;

import com.ligong.popj.Brand;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface BrandMapper {



    List<Brand> selectByCondition1(
            @Param("status")int status,
            @Param("companyName") String companyName,
            @Param("brandName") String brandName);

    List<Brand> selectByCondition2(Brand brand);

    List<Brand> selectByCondition3(Map map);

}

4、BrandMapper.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.ligong.mapper.BrandMapper">

    <select id="selectByCondition1" resultType="com.itheima.popj.Brand">
 select * from
       tb_brand
 where status = #{status} and
       company_name like  #{companyName} and
       brand_name   like #{brandName};


    </select>

    <select id="selectByCondition2" resultType="com.itheima.popj.Brand">
        select * from
            tb_brand
        where status = #{status} and
            company_name like  #{companyName} and
            brand_name   like #{brandName};
    </select>

    <select id="selectByCondition3" resultType="com.itheima.popj.Brand">
        select * from
            tb_brand
        where status = #{status} and
            company_name like  #{companyName} and
            brand_name   like #{brandName};
    </select>

</mapper>

5、封装好的连接数据库的工具类

package com.ligong.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 SqlSessionUtils {
    private static SqlSession sqlSession=null;
    private SqlSessionUtils() {

    }
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
          sqlSession = sqlSessionFactory.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    public static SqlSession getSqlSession(){


        return sqlSession;

    }
}

6、Test类中的特使代码

package com.ligong.mapper;

import com.itheima.popj.Brand;
import com.itheima.popj.Dept;
import com.itheima.util.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TestBrandMapper {
    private static BrandMapper mapper = null;
    private static SqlSession sqlSession = null;

    @Before
    public void init() {
        sqlSession = SqlSessionUtils.getSqlSession();
        mapper = sqlSession.getMapper(BrandMapper.class);
    }

    @After
    public void finallyOp() {
        sqlSession.close();
    }

    @Test
    public void selectByCondition3() {
        int status = 1;
        String brand_name = "华";
        String company_name = "华";

        String brandName = "%" + brand_name + "%";
        String companyName = "%" + company_name + "%";

        Map map = new HashMap();
        map.put("status", status);
        map.put("brandName", companyName);
        map.put("companyName", brandName);

        List<Brand> brands = mapper.selectByCondition3(map);

        for (Brand brand1 : brands) {
            System.out.println(brand1);
        }
    }

    @Test
    public void selectByCondition2() {
        int status = 1;
        String brand_name = "华";
        String company_name = "华";

        String brandName = "%" + brand_name + "%";
        String companyName = "%" + company_name + "%";

        Brand brand = new Brand();
        brand.setStatus(1);
        brand.setBrandName(brandName);
        brand.setCompanyName(companyName);

        List<Brand> brands = mapper.selectByCondition2(brand);

        for (Brand brand1 : brands) {
            System.out.println(brand1);
        }
    }

    @Test
    public void selectByCondition1() {
        int status = 1;
        String brand_name = "华为";
        String company_name = "华";

        String brandName = "%" + brand_name + "%";
        String companyName = "%" + company_name + "%";

        List<Brand> brands = mapper.selectByCondition1(
                status,
                companyName,
                brandName);

        for (Brand brand : brands) {
            System.out.println(brand);
        }
    }

    @Test
    public void findAll() {
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        List<Dept> all = mapper.findAll();
        for (Dept dept : all) {
            System.out.println(dept);
        }
    }
}

到此这篇关于MyBatis中多条件查询商品的三种方法及区别的文章就介绍到这了,更多相关MyBatis 多条件查询内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉