mysql-How to assemble multi-layer nested json from the data read from the database

mysqljson algorithm data structure java

The database fields are as shown in the figure

Three primary keys day, hour, store, which means you can find out the specific sales of a store at a certain hour on a certain day,
day, hour, store The data can be repeated, but the data consisting of three fields is unique.
Requires java mysql jdbc (other database connection technologies are also acceptable) but the language must be java.
How to read the database to form json in the following format?
I use jdbc myself and then use multi-layer while loops to form this format, but it is very inefficient. When the amount of data is large, it will be very slow

Looking for an efficient solution

Multi-level nested format
{
2016-07-16:
                                                                                      sales_count:1000
profit ; sales_count:1001
profit:201
sales_value:10001
}
]
             
                                                                                                                                                  ; sales_count:1005
profit:206
sales_value:10007
}
; {
1:
[
nike: {
sales_count: 1008
propit: 208
sales_value: 10008

}}
adidas: {

sales_count: 1009
sales_value: 10009
}

]

             
2:
                                                                                                                                                                                     value:20004
                                                                                                                                                                                                adidas:       sales_count:1505
                                                                                                                                                                                                                                                            Sales_value: ...

}







Reply content:

Convert the data retrieved from the database into objects, and then use gson to directly convert the objects into json strings!

After your SQL statement is written, you just need to do a large traversal of the record set and add two judgment processes in it. Didn’t you say it was written? Post it and let it be modified.

Also, how big is this quantity? If you create a volume of several M or even tens or hundreds of M, the transmission alone will be slow enough.

You have a problem here, that is, the key of the sales data is the store name, which is not fixed, so the implementation will be very complicated.
It is recommended that you make a slight modification, the complexity will be reduced a lot, and the sub-array format inside is changed to:
[
{
store: nike
sales_count:2002
profit:204
sales_value:20004
},
{
sales_count:1505
profit:216
sales_value:10077
},
...
]

Then you can design JavaBean to receive data like this, and then use the tool Object to convert it to jsonString

class Result {
List ts;
}

class T {
String day;
int hour;
List stores;
}

class Store {
String store;
int sales_count;
int profit;

int sales_value;

}

For SQL queries, it is recommended not to use large SQL because there are too many subqueries. Too many subqueries will exponentially slow down the efficiency of large SQL. Use Java loops to execute simple SQL.

1. The first step is to query the basic data of the first layer and get List(T):

select distinct day, hour from Table;

1. The second step is to loop through List(T) and fill in the store information in a loop according to day + hour: select store, sales_count, profit, sales_value from T where day = ? and hour =?;

The logic is clear and the structure is not complicated, just two layers.