[Java知识库] SqlSugar 6.导航查询

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> SqlSugar 6.导航查询 -> 正文阅读

[Java知识库]SqlSugar 6.导航查询

前言

导航查询特点：

主要处理主对象里面有子对象这种层级关系查询
只需要配置特性和主键即可，不需要外键

1.导航查询用法 (关键字：Includes)

1.1 一对一

用到的实体类：

public class StudentA
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
        //新版本修改了命名
        [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一一 SchoolId是StudentA类里面的
        public SchoolA SchoolA { get; set; } //不能赋值只能是null
    }
    
    
    public class SchoolA
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
    }

代码：

List<StudentA> list = db.Queryable<StudentA>()
                .Includes(it => it.SchoolA)
                .ToList();

string jsonStr1 = JsonConvert.SerializeObject(list);
string Jsonstr2 = db.Utilities.SerializeObject(list);   //sugar提供的序列化方法

//转Json后 将内容粘贴到 https://www.json.cn/ 解析查看数据格式

1.2 一对多

用到的实体类：

public class StudentB
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(BookB.studenId))]  //BookB表中的studenId
        public List<BookB> Books { get; set; }  //注意禁止给Books手动赋值
    }
    
    
    public class BookB
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int BookId { get; set; }
        public string Name { get; set; }
        public int studenId { get; set; }
    }

1.2.1 简单用法

注意：

简单写法 .Includes(it => it.Books.ToList()) 中的 ToList() 可以省略

List<StudentB> list1 = db.Queryable<StudentB>()
                .Includes(it => it.Books)
                .ToList();

string jsonStr1 = JsonConvert.SerializeObject(list);
string Jsonstr2 = db.Utilities.SerializeObject(list);    //sugar提供的序列化方法

//转Json后 将内容粘贴到 https://www.json.cn/ 解析查看数据格式

1.2.2 对主主对象进行过滤

注意：

支持Any Count，对主对象进行过滤
Any 只支持一个层级，第二层级目前不支持

List<StudentB> list = db.Queryable<StudentB>()
                .Includes(it => it.Books)
                .Where(it => it.Name.Equals("B"))
                .ToList();

//提前对主对象进行 条件筛选过滤
List<StudentB> list = db.Queryable<StudentB>()
                .Includes(it => it.Books)
                .Where(it => it.Books.Any(x=>x.Name.Equals("三国")))
                .ToList();

1.2.3 对子对象进行排序和过滤

注意：

Includes 中结尾的 ToList() 方法一定不要少了
否则会报错 “need is ToList()”

List<StudentB> list = db.Queryable<StudentB>()
                .Includes(it => it.Books.Where(b => b.BookId > 3).OrderByDescending(b => b.BookId).ToList())
                .ToList();

1.2.4 子表加Select

注意：

子表 studenId 是必有字段，剩下字段看 Select 投影了哪个字段，就会在sql查询中显示哪个字段

var list2 = db.Queryable<StudentB>()
           .Includes(it => it.Books.Select(z => new BookB() { BookId = z.BookId }).ToList())
           .ToList();

// SELECT `BookId` AS `BookId` ,`studenId` FROM `BookB`  WHERE   `studenId` IN (1,2,3,4)

var list2 = db.Queryable<StudentB>()
           .Includes(it => it.Books.Select(z => new BookB() { Name = z.Name }).ToList())
           .ToList();

// SELECT `Name` AS `Name` ,`studenId` FROM `BookB`  WHERE   `studenId` IN(1, 2, 3, 4)

1.2.5 主表加Select

var list = db.Queryable<StudentB>()
                .Includes(x => x.Books.ToList())
                .ToList(it => new
                {
                    AAA = it.StudentId,
                    BBB = it.Name,
                    CCC = it.Books
                });
                
string Jsonstr = db.Utilities.SerializeObject(list);
//转Json后 将内容粘贴到 https://www.json.cn/ 解析查看数据格式

1.3 多对多

用到的实体类：

public class ABMapping1
{
    [SugarColumn(IsPrimaryKey = true)]  //中间表可以不是主键
    public int AId { get; set; }
    [SugarColumn(IsPrimaryKey = true)]  //中间表可以不是主键
    public int BId { get; set; }
}


public class A1
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    [Navigate(typeof(ABMapping1), nameof(ABMapping1.AId), nameof(ABMapping1.BId))]  //注意顺序
    public List<B1> BList { get; set; }
}


public class B1
{
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }
    public string Name { get; set; }
    [Navigat(typeof(ABMapping1), nameof(ABMapping1.BId), nameof(ABMapping1.AId))]   //注意顺序
    public List<A1> AList { get; set; }
}

1.3.1 简单用法

直接填充子对象B集合，前提是配置好特性

List<A1> list = db.Queryable<A1>()
                .Includes(x => x.BList)
                .ToList();
string Jsonstr = db.Utilities.SerializeObject(list);

1.3.2 子对象的过滤和排序

支持 WhereIF

List<A1> list = db.Queryable<A1>()
                .Includes(it => it.BList.Where(x=>x.Id>3).ToList())
                .ToList();
string Jsonstr = db.Utilities.SerializeObject(list);

1.3.3 主对象过滤

List<A1> list = db.Queryable<A1>()
                .Includes(a1 => a1.BList)
                .Where(a2 => a2.BList.Any(b => b.Id>2))
                .ToList();

string Jsonstr = db.Utilities.SerializeObject(list);

1.4 手动

用到的实体类：

public class StudentC
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        [Navigate(NavigateType.Dynamic, null)] //自定义关系映射
        public List<BookB> Books { get; set; }
    }

代码：

List<StudentC> list = db.Queryable<StudentC>()
                .Includes(it => it.Books.MappingField(z => z.studenId, () => it.StudentId).ToList())
                .ToList();
                
string Jsonstr = db.Utilities.SerializeObject(list);

1.5 多级导航

1.5.1 三层以下的写法

注意：

一对多多对多一对多只要配好了都可以多层级使用

用到的实体类：

public class StudentB
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
        [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一
        public SchoolB SchoolB { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(BookB.studenId))]//BookB表中的studenId 一对多
        public List<BookB> Books { get; set; }//注意禁止给Books手动赋值
    }
    
public class SchoolB
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(RoomB.SchoolId))]//一对多
        public List<RoomB> RoomList { get; set; }
    }
    
public class RoomB
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int RoomId { get; set; }
        public string RoomName { get; set; }
        public int SchoolId { get; set; }
    }
    
public class BookB
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int BookId { get; set; }
        public string Name { get; set; }
        public int studenId { get; set; }
    }

代码：

var list = db.Queryable<StudentB>()
                .Includes(st => st.Books)//一级
                .Includes(st => st.SchoolB, sch => sch.RoomList)// 一级 + 两级
                .ToList();

string Jsonstr = db.Utilities.SerializeObject(list);

1.5.2 三层以上的写法

注意：

超过3个层级需要.AsNavQueryable()
缺点VS提示会消失，直接写不要在乎意提示不出来，VS关掉在开就行了，只要不改这个代码提示就不会有问题
示例： db.Queryable().AsNavQueryable().Includes(it=>it.1,it=>it.2,it=>it.3,it=>it.4，it=>it.5…)
.AsNavQueryable()能不用尽量不要用，正常Includes(+3)重载完全够用了

用到的实体类：

    //省份
    public class Province
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Pid { get; set; }
        public string Pname { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(City.pid))]
        public List<City> cityList { get; set; }
    }


    //市级
    public class City
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Cid { get; set; }
        public string Cname { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(District.cid))]
        public List<District> districtList { get; set; }
        public int pid { get; set; }
    }


    //区级
    public class District
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Did { get; set; }
        public string Dname { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(Town.did))]
        public List<Town> townList { get; set; }
        public int cid { get; set; }
    }


    //镇
    public class Town
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Tid { get; set; }
        public string Tname { get; set; }
        [Navigate(NavigateType.OneToMany, nameof(Village.tid))]
        public List<Village> villageList { get; set; }
        public int did { get; set; }
    }


    //村
    public class Village
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Vid { get; set; }
        public string Vname { get; set; }
        public int tid { get; set; }
    }

插入数据：

INSERT INTO `province` VALUES (1, '山东省');

INSERT INTO `city` VALUES (1, '烟台市', 1);
INSERT INTO `city` VALUES (2, '青岛市', 1);
INSERT INTO `city` VALUES (3, '威海市', 1);

INSERT INTO `district` VALUES (1, '开发区', 1);
INSERT INTO `district` VALUES (2, '莱山区', 1);
INSERT INTO `district` VALUES (3, '崂山区', 2);

INSERT INTO `town` VALUES (1, '大新店镇', 1);
INSERT INTO `town` VALUES (2, '刘家沟镇', 1);
INSERT INTO `town` VALUES (3, '北沟镇', 1);
INSERT INTO `town` VALUES (4, '解甲庄', 2);

INSERT INTO `village` VALUES (1, '大呼家村', 1);
INSERT INTO `village` VALUES (2, '回家村', 1);
INSERT INTO `village` VALUES (3, '刘家沟村', 2);
INSERT INTO `village` VALUES (4, '安乡刘家村', 2);
INSERT INTO `village` VALUES (5, '北沟一村', 3);
INSERT INTO `village` VALUES (6, '北沟二村', 3);

代码：

//普通查询，三层
 var list1 = db.Queryable<Province>()
                .Includes(p => p.cityList, c => c.districtList, d => d.townList)
                .ToList();
string Jsonstr1 = db.Utilities.SerializeObject(list1);


//三层以上
var list2 = db.Queryable<Province>()
                .AsNavQueryable()
                .Includes(p => p.cityList, c => c.districtList, d => d.townList, t => t.villageList)
                .ToList();

string Jsonstr2 = db.Utilities.SerializeObject(list2);

1.6 大数据导航

暂时没有可测试的环境
文档参考：分批处理

2.内存对象导航 (关键字：SetContext)

手动映射适合没有主键或者复杂的一些操作

用到的实体类：

    public class StudentX
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }
        [SugarColumn(IsIgnore = true)]
        public SchoolX SchoolX { get; set; }
    }

    public class SchoolX
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<RoomX> RoomList { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<TeacherX> TeacherList { get; set; }
    }
    
    public class TeacherX
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Id { get; set; }
        public int SchoolId { get; set; }
        public string Name { get; set; }
    }
    
    public class RoomX
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int RoomId { get; set; }
        public string RoomName { get; set; }
        public int SchoolId { get; set; }
    }

插入数据：

            db.Insertable(new RoomX() { RoomId = 1, RoomName = "北大001室", SchoolId = 1 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 2, RoomName = "北大002室", SchoolId = 1 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 3, RoomName = "北大003室", SchoolId = 1 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 4, RoomName = "清华001厅", SchoolId = 2 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 5, RoomName = "清华002厅", SchoolId = 2 }).ExecuteCommand();

            db.Insertable(new RoomX() { RoomId = 6, RoomName = "清华003厅", SchoolId = 2 }).ExecuteCommand();


            db.Insertable(new SchoolX() { SchoolId = 1, SchoolName = "北大" }).ExecuteCommand();

            db.Insertable(new SchoolX() { SchoolId = 2, SchoolName = "清华" }).ExecuteCommand();


            db.Insertable(new StudentX() { StudentId = 1, SchoolId = 1, Name = "北大jack" }).ExecuteCommand();

            db.Insertable(new StudentX() { StudentId = 2, SchoolId = 1, Name = "北大tom" }).ExecuteCommand();

            db.Insertable(new StudentX() { StudentId = 3, SchoolId = 2, Name = "清华jack" }).ExecuteCommand();

            db.Insertable(new StudentX() { StudentId = 4, SchoolId = 2, Name = "清华tom" }).ExecuteCommand();


            db.Insertable(new TeacherX() { SchoolId = 1, Id = 1, Name = "北大老师01" }).ExecuteCommand();

            db.Insertable(new TeacherX() { SchoolId = 1, Id = 2, Name = "北大老师02" }).ExecuteCommand();

            db.Insertable(new TeacherX() { SchoolId = 2, Id = 3, Name = "清华老师01" }).ExecuteCommand();

            db.Insertable(new TeacherX() { SchoolId = 2, Id = 4, Name = "清华老师02" }).ExecuteCommand();

2.1 实现两层

注意：

结构：StudentX → SchoolX
如果没有SetContext那么这个查询将会循环
db.ConextId外面和里面需要是同一个

var list = db.Queryable<StudentX>().Where(it=>it.StudentId==2 || it.StudentId==3).ToList();
            db.ThenMapper(list, stu =>
            {
                stu.SchoolX = db.Queryable<SchoolX>()
                .Where(scl => scl.SchoolId > 1)
                .SetContext(scl => scl.SchoolId, () => stu.SchoolId, stu).FirstOrDefault();
            });

string Jsonstr = db.Utilities.SerializeObject(list);

2.2 实现无线层

用到的实体类：

    //省份
    public class Province
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Pid { get; set; }
        public string Pname { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<City> cityList { get; set; }
    }

    //市级
    public class City
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Cid { get; set; }
        public string Cname { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<District> districtList { get; set; }
        public int pid { get; set; }
    }

    //区级
    public class District
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Did { get; set; }
        public string Dname { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<Town> townList { get; set; }
        public int cid { get; set; }
    }

    //镇
    public class Town
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Tid { get; set; }
        public string Tname { get; set; }
        [SugarColumn(IsIgnore = true)]
        public List<Village> villageList { get; set; }
        public int did { get; set; }
    }

    //村
    public class Village
    {
        [SugarColumn(IsPrimaryKey = true)]
        public int Vid { get; set; }
        public string Vname { get; set; }
        public int tid { get; set; }
    }

插入数据：

NSERT INTO `province` VALUES (1, '山东省');

INSERT INTO `city` VALUES (1, '烟台市', 1);
INSERT INTO `city` VALUES (2, '青岛市', 1);
INSERT INTO `city` VALUES (3, '威海市', 1);

INSERT INTO `district` VALUES (1, '开发区', 1);
INSERT INTO `district` VALUES (2, '莱山区', 1);
INSERT INTO `district` VALUES (3, '崂山区', 2);

INSERT INTO `town` VALUES (1, '大新店镇', 1);
INSERT INTO `town` VALUES (2, '刘家沟镇', 1);
INSERT INTO `town` VALUES (3, '北沟镇', 1);
INSERT INTO `town` VALUES (4, '解甲庄', 2);

INSERT INTO `village` VALUES (1, '大呼家村', 1);
INSERT INTO `village` VALUES (2, '回家村', 1);
INSERT INTO `village` VALUES (3, '刘家沟村', 2);
INSERT INTO `village` VALUES (4, '安乡刘家村', 2);
INSERT INTO `village` VALUES (5, '北沟一村', 3);
INSERT INTO `village` VALUES (6, '北沟二村', 3);

代码：

var list = db.Queryable<Province>().ToList();
db.ThenMapper(list, pro =>
{
    pro.cityList = db.Queryable<City>().SetContext(c => c.pid, () => pro.Pid, pro).ToList();
});
db.ThenMapper(list.SelectMany(it => it.cityList), city =>
{
    city.districtList = db.Queryable<District>().SetContext(d => d.cid, () => city.Cid, city).ToList();
});
db.ThenMapper(list.SelectMany(it => it.cityList).SelectMany(it => it.districtList), dis =>
{
    dis.townList = db.Queryable<Town>().SetContext(t => t.did, () => dis.Did, dis).ToList();
});
db.ThenMapper(list.SelectMany(it => it.cityList).SelectMany(it => it.districtList).SelectMany(it => it.townList), tow =>
{
    tow.villageList = db.Queryable<Village>().SetContext(v => v.tid, () => tow.Tid, tow).ToList();
});

string Jsonstr = db.Utilities.SerializeObject(list);

文档参考：导航查询