Want to write table data to JSON file in Java? You need to modify your ‘SQL’ query. Add the ORDEr BY clause to come to your data in the arranged way.
1 2 | SELECT com_name,com_code,dept,cat_no,cat_desc,type_1,type_2,type_3 FROM json_data order by com_name,dept; |
And here the method to convert all table data to JSON.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | public static JSONArray convert(ResultSet rs) { Set<String> nameSet = new HashSet<>(); Set<String> deptSet = new HashSet<>(); JSONArray jsonArray = new JSONArray(); JSONArray jsonArray2 = null; JSONObject obj = null; JSONObject obj2 = null; try { while (rs.next()) { int total_rows = rs.getMetaData().getColumnCount(); if (nameSet.contains(rs.getString(1))) { if (deptSet.contains(rs.getString(3))) { obj2 = new JSONObject(); for (int j = 3; j < total_rows; j++) { obj2.put(rs.getMetaData().getColumnLabel(j + 1).toLowerCase(), rs.getObject(j + 1) != null ? rs.getObject(j + 1) : ""); } jsonArray2.put(obj2); } else { obj2 = new JSONObject(); jsonArray2 = new JSONArray(); deptSet.add(rs.getString(3)); for (int j = 3; j < total_rows; j++) { obj2.put(rs.getMetaData().getColumnLabel(j + 1).toLowerCase(), rs.getObject(j + 1) != null ? rs.getObject(j + 1) : ""); } jsonArray2.put(obj2); obj.put(rs.getString(3), jsonArray2); } } else { if (obj != null) { jsonArray.put(obj); } deptSet.removeAll(deptSet); obj = new JSONObject(); obj2 = new JSONObject(); jsonArray2 = new JSONArray(); nameSet.add(rs.getString(1)); for (int i = 0; i < 2; i++) { obj.put(rs.getMetaData().getColumnLabel(i + 1).toLowerCase(), rs.getObject(i + 1) != null ? rs.getObject(i + 1) : ""); } if (deptSet.contains(rs.getString(3))) { } else { deptSet.add(rs.getString(3)); for (int j = 3; j < total_rows; j++) { obj2.put(rs.getMetaData().getColumnLabel(j + 1).toLowerCase(), rs.getObject(j + 1) != null ? rs.getObject(j + 1) : ""); } } jsonArray2.put(obj2); obj.put(rs.getString(3), jsonArray2); } } jsonArray.put(obj); } catch (Exception e) { e.printStackTrace(); } System.out.println("Final JSON: " + jsonArray); return jsonArray; } |
Generated JSON from the above code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | [ { "MARKETING": [ { "type_3": "OK", "type_2": "OK", "type_1": "AZC", "cat_no": "M1", "cat_desc": "Required" } ], "com_code": 12, "IT": [ { "type_3": "", "type_2": "", "type_1": "a", "cat_no": "XYZ", "cat_desc": "World" }, { "type_3": "", "type_2": "fine", "type_1": "Right", "cat_no": "ABC", "cat_desc": "People" } ], "SALES": [ { "type_3": "", "type_2": "", "type_1": "", "cat_no": "SL01", "cat_desc": "Sell Better" } ], "com_name": "Google" }, { "PRODUCT": [ { "type_3": "INDIA", "type_2": "JAPAN", "type_1": "USA", "cat_no": "P01", "cat_desc": "Windows 10" } ], "com_code": 18, "com_name": "Microsoft" }, { "com_code": 14, "IT": [ { "type_3": "JS", "type_2": "JSON", "type_1": "Java", "cat_no": "QA", "cat_desc": "Question and Answer" } ], "SALES": [ { "type_3": "", "type_2": "DONE", "type_1": "", "cat_no": "S1", "cat_desc": "internet" } ], "com_name": "StackOverflow" } ] |
If you like this question & answer and want to contribute, then write your question & answer and email to freewebmentor[@]gmail.com. Your question and answer will appear on FreeWebMentor.com and help other developers.
