Create json file from SQL Table, then be able to query the file

Question

I am trying to accomplish the following (using Netbeans 11.3):

1.) Get Data (String) From a Column in an SQL Table.

2.) Convert and save the Data as a JSONArray using Filewriter.

3.) Query the newly created JSON file (output.json) so the data can be used to populate text boxes.

I have accomplished (I think) 1 and 2; but am stuck on how to save the output.json file so that it can be accessed and queried. As a side note, from my research I think there are much better ways to accomplish these tasks (seeming to me like .php is better suited to these tasks than Java), but as a learning experience I'd like to try to finish this task.

Here is the code:

DatabasetoJSONScript.JS

import java.io.FileWriter; 
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;


public class DataBaseToJSon {

public static ResultSet RetrieveData() throws Exception {

    String host = "jdbc:derby://localhost:1527/dbname";
    String uName = "my Uname";
    String uPass = "my Pass";
    
    Connection con = DriverManager.getConnection(host, uName, uPass);
    Statement stmt = con.createStatement();
    

    ResultSet rs = stmt.executeQuery ("Select * from Table_Name");
    
    return rs;
}        

 public static void main (String args[])throws Exception {
                       
            JSONObject jsonObject = new JSONObject();
            JSONArray array = new JSONArray();
     
            ResultSet rs = RetrieveData();
            
            while (rs.next()) {
            
            JSONObject record = new JSONObject();
                record.put("TEXT", rs.getString("TEXT"));
                record.put("ANSWER", rs.getString("ANSWER"));
                array.add(record);
            }         
            jsonObject.put("RC1", array);
            
            try {
                try (FileWriter file = new FileWriter ("C:\\Users\\Owner\\Documents\\NetBeansProjects
                    \\HTML5Application\\public_html")) {
                    file.write (jsonObject.toJSONString());
                }
            } catch(IOException e){
            System.out.println("JSON flie created....");
         }
    }

The relevant part of controller.js

$(document).ready(function (){
    var questionBank=new Array;
    var currentQuestionNumber; 
    var currentAnswer;
    var numberOfQuestions;
    var gamePosition;
    var score;

 $.getJSON('output.json', function(output) {
   numberOfQuestions=output.RC1.length;
       for(i=0;i<output.RC1.length;i++){
           typeArray=[];
           typeArray[1]=output.RC1[i].TEXT;
           typeArray[0]=output.RC1[i].ANSWER;
           questionBank[i]=typeArray;
    }

Can you post your sample json? – Aparna Jul 08 '20 at 13:07 — Aparna, Jul 08 '20 at 13:07

score 0 · Answer 1 · answered Jul 08 '20 at 13:13

You can use JSONParser to parse the file content into a JSON and then cast it to a JSONObject or JSONArray according to your format.

    //JSON parser object to parse read file
    JSONParser jsonParser = new JSONParser();

    try (FileReader reader = new FileReader("/opt/MyWork/output.json")) {
        //Read JSON file
        Object obj = jsonParser.parse(reader);

        JSONObject jobj = (JSONObject) obj;
        JSONArray arr = (JSONArray) jobj.get("RC1");
        
        for(Object r : arr){
            JSONObject record = (JSONObject) r;
            System.out.println(record.get("TEXT"));
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ParseException e) {
        e.printStackTrace();
    }

score 0 · Answer 2 · answered Jul 16 '21 at 08:18

The task of translating JDBC result sets to JSON arrays of objects is quite a repetitive one, even if it works the same way for all the possible result sets. If using a third party library is an option, jOOQ and its SQL/JSON support might be of help. There are 2 options:

Use jOOQ's client side JSON formatting capabilitites

This even works with your JDBC ResultSet, so you don't need to use jOOQ to create a query, though you certainly can:

String json = DSL.using(con)
    .fetch(RetrieveData())
    .formatJSON(new JSONFormat().header(false).recordFormat(RecordFormat.OBJECT));

There are other ways to format your JSON output, see also the manual about exporting JSON

Use your database's SQL/JSON support

In standard SQL, you can always write:

select json_arrayagg(json_object(
  key 'col1' value col1,
  key 'col2' value col2
))
from t;

This will produce a JSON document directly in your database. The actual syntax varies a lot, so you'll have to adapt your query, or you could again use jOOQ that abstracts over this for you:

JSON json = ctx
    .select(jsonArrayAgg(jsonObject(
       key("col1").value(T.COL1),
       key("col2").value(T.COL2)
    )))
    .from(T)
    .fetchOne()
    .value1();

Disclaimer: I work for the company behind jOOQ.

Create json file from SQL Table, then be able to query the file

2 Answers2

Use jOOQ's client side JSON formatting capabilitites

Use your database's SQL/JSON support