这个问题是查询解决还是转换的时候解决

Question

两个表

create table sub(
id NUMBER(5) not null,
subid NUMBER(5) not null,
subcata VARCHAR(50) not null
);

create table mainsub(
id NUMBER(5) not null,
maincata VARCHAR(50) not null
);

insert into mainsub values('01','sales');
insert into mainsub values('02','vegetable');
insert into sub values('01','01','sales1');
insert into sub values('01','02','sales2');
insert into sub values('01','03','sales3');
insert into sub values('02','01','vegetable1');

查询语句
select A.maincata,B.subcata from mainsub A,sub B where A.id = B.id

但我想要这样的结果

[
    {"maincata":"sales",
    "sub":[
        {"subcata":"sales2"}, 
        {"subcata":"sales4"},
        {"subcata":"sales3"},
        {"subcata":"sales6"},
        {"subcata":"sales1"},
        {"subcata":"sales5"},
        {"subcata":"sales7"}
        ]
    }
    {"maincata":"vegetable",
    "sub":[
        {"subcata":"vegetable1"}
        ]
    }
]

实际经过我写的toString转换后是这样的

[{"maincata":"sales","sub":[{"subcata":"sales2"}]}, {"maincata":"sales","sub":[{"subcata":"sales4"}]}, {"maincata":"sales","sub":[{"subcata":"sales3"}]}, {"maincata":"sales","sub":[{"subcata":"sales6"}]}, {"maincata":"vegetable","sub":[{"subcata":"vegetable1"}]}, {"maincata":"sales","sub":[{"subcata":"sales1"}]}, {"maincata":"sales","sub":[{"subcata":"sales5"}]}, {"maincata":"sales","sub":[{"subcata":"sales7"}]}]

toString:

    public String toString() {
        return "{" +
            "\"maincata\":\""+maincata+"\","+
            "\"sub\":["+
                "{"+"\"subcata\":\""+subcata+"\""+"}"+"]"+
       "}";
    }

应该改SQL还是toString，怎么改才好，求助。。

Answer 1

https://blog.csdn.net/weixin_43766753/article/details/100162180