Reading data from oracle database table and writing to file using java

I am reading data from database using JDBC. data is like below.

I have to insert this data into an application using Post API. for that I need to write data into file in the format API accepts.

so I will create OrgName.json file in the below format.

{
    "delete": [
        {
            "canonicalName": "TCS"    		
        }
    ],
	"update": [
	     {
		    "canonicalName": "Wipro tech"
			"synonyms": [
                "Wipro technology"
            ]
		 },
		 {
		    "canonicalName": "Infosys"
			"synonyms": [
                "Infosys tech"
            ]
		 }
	]	 	
    "add": [
        {
            "canonicalName": "Apple Computers",
            "synonyms": [
                "Apple"
            ]
        },
        {
            "canonicalName": "Google India",
            "synonyms": ["Google"]
        }
	]
}

So there would be 3 grouping. All the update values will come in update tag, add values in add tag based on OPERATION column and delete in delete tag. ORGNAME value of column will be canonicalName name in file. If synonyms is null then it will not be present else it will be there.

I know JDBC in java. I can read data using executeQuery of Preparedstatement and can display in console using sysout. But I am not able to write data into file in the above format.

Can anybody help me? I am database developer so not able to think how can I do it. Even minimal help would be a lot for me.

I want to convert Resultset into above file, which I am not able to do.

Since you have the information inside a DB, it is important to separate tasks.

1. Obtain the information. DONE
2. Convert the information from the DB into an Object
3. Separate delete, add, update objects
4. Print the separated objects.

Task 1 is DONE, you can access and retrieve the data.

For task 2 you need to create the Data.java class with the attributes orgName, synonyms and operation as a string type.
*Include getter and setter.

You need to see this class as one register of the Table.
Then you need to somehow generate a list of Data.java to 'replicate' the table.

This can be done like this:

List<Data> dataList = new ArrayList<>();
//Your database code
ResultSet rs = pstmt.executeQuery();
while (rs.next) {
            Data data = new Data();
            data.setOrgName(rs.getString("OrgName"));
            data.setSynonyms(rs.getString("Synonyms"));
            data.setOperation(rs.getString("Operation"));

            dataList.add(data);
        }

You can now print that dataList as objects.

Next two steps can be done together.
For each object inside the dataList (foreach) you can start to generate the OrgName.json file. It is important to group delete, add and modify.

This can be done like this:

    JSONObject obj = new JSONObject();
    JSONArray delete = new JSONArray();
    JSONArray modify = new JSONArray();
    JSONArray add = new JSONArray();
    for (Data data : dataList) {

        switch (data.getOperation()){
            case "delete":
                //This means that its is a delete object
                JSONObject obj = new JSONObject();
                obj.put("canonicalName", data.getOrgName());
                delete.add(obj);
                break;
            case "add":
                //This means that its is an add object
                // similar to delete
                break;
            case "modify":
                //This means that its is a modify object
                //Similar to delete
            default:
                break;
        }
    }

After that you can use FileWriter to generate the file.

Take a look to this link: write JSON object to file and execute query and iterate RS

Jackson is a very useful library for dealing with JSON.

Based on the example JSON in your question, you could create simple POJOs to model the JSON structure, and then take advantage of Jackson's ObjectMapper to map the POJO to a String containing the JSON you want to write to a File.

A very simple example follows, but you will have to update this example to appropriately deal with data hiding and error handling.

class OrganizationModificationsDto {

    public List<Organization> delete;
    public List<Organization> update;
    public List<Organization> add;

    public static String toJson( OrganizationModificationsDto dto ) throws JsonProcessingException {
        ObjectMapper mapper = new ObjectMapper();
        return mapper.writeValueAsString( dto );
    }

}

class Organization {
    public String canonicalName;
    public List<String> synonyms;
}

ObjectMapper also has a writeValue method that will allow you to write the JSON directly to a File, Writer, OutputStream, etc.

public void writeValue(File resultFile, Object value)

So once you had built up OrganizationModificationsDto, you could just directly write as JSON to a file.

objectMapper.writeValue( new File("OrgName.json"), dto );

And here is a useful tutorial.