连接查询和子查询

一般对分类列表的实现：

连接查询：一次性查询三级分类

优点：只需要一次查询，根据一级分类显示二级分类时相应速度较快

缺点：数据库查询效率低，页面首次加载的速度也相对较慢

子查询：先只查询一级分类，用户点击/鼠标移动到一级分类，动态加载二级分类

优点：数据库查询效率提高，页面首次加载速度提高

缺点：需要多次连接数据库

数据表结构：

封装Category为CategoryVO：（为list集合）

package com.qfedu.fmmall.entity;

import javax.persistence.Column;
import java.util.List;


public class CategoryVO {

    @Override
    public String toString() {
        return "CategoryVO{" +
                "categoryId=" + categoryId +
                ", categoryName='" + categoryName + ''' +
                ", categoryLevel=" + categoryLevel +
                ", parentId=" + parentId +
                ", categoryIcon='" + categoryIcon + ''' +
                ", categorySlogan='" + categorySlogan + ''' +
                ", categoryPic='" + categoryPic + ''' +
                ", categoryBgColor='" + categoryBgColor +
                '}';
    }

    
    @Column(name = "category_id")
    private Integer categoryId;

    
    @Column(name = "category_name")
    private String categoryName;

    
    @Column(name = "category_level")
    private Integer categoryLevel;

    
    @Column(name = "parent_id")
    private Integer parentId;

    
    @Column(name = "category_icon")
    private String categoryIcon;

    
    @Column(name = "category_slogan")
    private String categorySlogan;

    
    @Column(name = "category_pic")
    private String categoryPic;

    
    @Column(name = "category_bg_color")
    private String categoryBgColor;


    private List categories;

    public List getCategories() {
        return categories;
    }

    public void setCategories(List categories) {
        this.categories = categories;
    }

    
    public Integer getCategoryId() {
        return categoryId;
    }

    
    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }

    
    public String getCategoryName() {
        return categoryName;
    }

    
    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    
    public Integer getCategoryLevel() {
        return categoryLevel;
    }

    
    public void setCategoryLevel(Integer categoryLevel) {
        this.categoryLevel = categoryLevel;
    }

    
    public Integer getParentId() {
        return parentId;
    }

    
    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    
    public String getCategoryIcon() {
        return categoryIcon;
    }

    
    public void setCategoryIcon(String categoryIcon) {
        this.categoryIcon = categoryIcon;
    }

    
    public String getCategorySlogan() {
        return categorySlogan;
    }

    
    public void setCategorySlogan(String categorySlogan) {
        this.categorySlogan = categorySlogan;
    }

    
    public String getCategoryPic() {
        return categoryPic;
    }

    
    public void setCategoryPic(String categoryPic) {
        this.categoryPic = categoryPic;
    }

    
    public String getCategoryBgColor() {
        return categoryBgColor;
    }

    
    public void setCategoryBgColor(String categoryBgColor) {
        this.categoryBgColor = categoryBgColor;
    }
}

连接查询：（演示三级查询）

categoryMapper接口：

package com.qfedu.fmmall.dao;

import com.qfedu.fmmall.entity.Category;
import com.qfedu.fmmall.entity.CategoryVO;
import com.qfedu.fmmall.general.GeneralDAO;
import org.springframework.stereotype.Repository;

import java.util.List;
@Repository
public interface CategoryMapper extends GeneralDAO {
    //1.使用连接查询实现分类查询：3级查询
    public List selectAllCategories();

    //2.子查询：根据parentId查询子分类
    public List selectAllCategories2(int parentId);
}

Mapper文件：


    
    
    
    
    
    
    
    
    
      
      
      
      
      
        
        
        
        
      
    
 

    select
      category_id,
      category_name,
      category_level,
      parent_id,
      category_icon,
      category_slogan,
      category_pic,
      category_bg_color
    from category
    where parent_id=#{parentId}

测试：

package com.qfedu;

import com.qfedu.fmmall.dao.CategoryMapper;
import com.qfedu.fmmall.entity.CategoryVO;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = ApiApplication.class)
class ApiApplicationTests {

    @Autowired
    private CategoryMapper categoryMapper;
    @Test
    void contextLoads() {
        List categoryVOS = categoryMapper.selectAllCategories2(0);
        for(CategoryVO c1 : categoryVOS){
            System.out.println(c1);
            for(CategoryVO c2 : c1.getCategories()){
                System.out.println("t" + c2);
                for(CategoryVO c3 : c2.getCategories()){
                    System.out.println("tt" + c3);
                }
            }
        }
    }

}

测试结果：

总结：

数据大的用子查询较好，对数据库的查询效率高；数据较少时用连续查询较好，可一次性查询所有数据。

连续查询和子查询的作用一样，都是先从一级查询入手，从而进行查询，但是实战效果不一样，两种方法可相互借鉴。

连接查询和子查询

Java相关栏目本月热门文章