java怎么写一个serlvet对数据库表返回的json数据按表的某一个字段分类

Question

处理前

 处理前[{"Group":1,"Groupheader":"质量管理","Leftimg":"","Left":"","Min":"","Right":"","Rightimg":""},  
{"Group":1,"Groupheader":"","Leftimg":"","Left":"","Min":"质量巡检","Right":"","Rightimg":""},  
{"Group":2,"Groupheader":"设备管理","Leftimg":"","Left":"","Min":"","Right":"","Rightimg":""},  
{"Group":2,"Groupheader":"","Leftimg":"","Left":"","Min":"设备专业点检","Right":"","Rightimg":""},  
{"Group":2,"Groupheader":"","Leftimg":"","Left":"","Min":"设备日检","Right":"","Rightimg":""},  
{"Group":2,"Groupheader":"","Leftimg":"","Left":"","Min":"设备周检","Right":"","Rightimg":""},  
{"Group":2,"Groupheader":"","Leftimg":"","Left":"","Min":"设备月检","Right":"","R
ightimg":""}];

处理后

 [  
    {  
        "Group": 1,  
        "data": [  
            {  
                "Group": 1,  
                "Groupheader": "质量管理",  
                "Leftimg": "",  
                "Left": "",  
                "Min": "",  
                "Right": "",  
                "Rightimg": ""  
            },  
            {  
                "Group": 1,  
                "Groupheader": "",  
                "Leftimg": "",  
                "Left": "",  
                "Min": "质量巡检",  
                "Right": "",  
                "Rightimg": ""  
            }  
        ]  
    },  
    {  
        "Group": 2,  
        "data": [  
            {  
                "Group": 2,  
                "Groupheader": "设备管理",  
                "Leftimg": "",  
                "Left": "",  
                "Min": "",  
                "Right": "",  
                "Rightimg": ""  
            },  
            {  
                "Group": 2,  
                "Groupheader": "",  
                "Leftimg": "",  
                "Left": "",  
                "Min": "设备专业点检",  
                "Right": "",  
                "Rightimg": ""  
            },  
            {  
                "Group": 2,  
                "Groupheader": "",  
                "Leftimg": "",  
                "Left": "",  
                "Min": "设备日检",  
                "Right": "",  
                "Rightimg": ""  
            },  
            {  
                "Group": 2,  
                "Groupheader": "",  
                "Leftimg": "",  
                "Left": "",  
                "Min": "设备周检",  
                "Right": "",  
                "Rightimg": ""  
            },  
            {  
                "Group": 2,  
                "Groupheader": "",  
                "Leftimg": "",  
                "Left": "",  
                "Min": "设备月检",  
                "Right": "",  
                "Rightimg": ""  
            }  
        ]  
    }  
]  

返回处理前JSON数据的代码

                    Statement stmt = connect.createStatement(); //创建Statement对象
                    String sql;

                    sql = "SELECT * FROM autobrand order by FirstLetter";
                    ResultSet rs = stmt.executeQuery(sql);
                    JSONArray jsonarray = new JSONArray();  
                    JSONObject jsonobj = new JSONObject(); 
                    // 展开结果集数据库
                    while(rs.next()){
                        // 通过字段检索 
                        jsonobj.put("BrandId", rs.getString("BrandId"));  
                        jsonobj.put("FirstLetter", rs.getString("FirstLetter"));  
                        jsonobj.put("Brand", rs.getString("Brand"));
                        jsonobj.put("Logo", rs.getString("Logo"));  

                        jsonarray.add(jsonobj);             
                      }
                    // 输出数据


                    out = response.getWriter();

                    out.println(jsonarray);
                    // 完成后关闭
                    rs.close();   
                    stmt.close();   
                    connect.close();                  
                 }catch (Exception e) { 
                      out.print("get data error!"); 
                      e.printStackTrace(); }

求怎么写一个方法让json数据按FirstLetter分类，写具体代码

Answer 1

select FirstLetter,data='['+stuff((select ','+'{"BrandId":"'+t2.BrandId+'","FirstLetter":"'+t2.FirstLetter+'","Brand":"'+t2.Brand+'","Logo":"'+t2.Logo+'"}' from tb t2 where t1.FirstLetter=t2.FirstLetter for xml path('')),1,1,'')+']' from tb t1 group by FirstLetter