MyBatis中多條件查詢商品的三種方法及區別

Posted on by

一、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;
    }

    /**
     * 獲取
     * @return id
     */
    public Integer getId() {
        return id;
    }

    /**
     * 設置
     * @param id
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * 獲取
     * @return brandName
     */
    public String getBrandName() {
        return brandName;
    }

    /**
     * 設置
     * @param brandName
     */
    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    /**
     * 獲取
     * @return companyName
     */
    public String getCompanyName() {
        return companyName;
    }

    /**
     * 設置
     * @param companyName
     */
    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    /**
     * 獲取
     * @return ordered
     */
    public Integer getOrdered() {
        return ordered;
    }

    /**
     * 設置
     * @param ordered
     */
    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    /**
     * 獲取
     * @return description
     */
    public String getDescription() {
        return description;
    }

    /**
     * 設置
     * @param description
     */
    public void setDescription(String description) {
        this.description = description;
    }

    /**
     * 獲取
     * @return status
     */
    public Integer getStatus() {
        return status;
    }

    /**
     * 設置
     * @param 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 多條件查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：