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

Question

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.

@PhilipWrage thanks. Philip please don't mark every question duplicate. requirement is different and I am struggling. — Shruti sharma
– Shruti sharma, Commented Jul 23, 2020 at 22:20

farftech19 · Accepted Answer · 2020-07-23 23:17:39Z

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

Obtain the information. DONE
Convert the information from the DB into an Object
Separate delete, add, update objects
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

thank you @luisfa .its really awesome...let me try and modify the same.

Philip Wrage · Accepted Answer · 2020-07-23 23:29:54Z

1

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.

edited Jul 23, 2020 at 23:29

answered Jul 23, 2020 at 22:19

Philip Wrage

1,5591 gold badge16 silver badges25 bronze badges

4 Comments

Shruti sharma Over a year ago

Thanks Philip. is there anyway to write data in that format?

Philip Wrage Over a year ago

ObjectMapper also has a writeValue method that allows you to write directly to a File, OutputStream, Writer, etc.

Shruti sharma Over a year ago

I can write data in file. I want to know logic.

Philip Wrage Over a year ago

@ShwetaMishra I'm not sure what you mean by logic. You said you know how to get data out of the database, so I assumed you knew how to transform that into other Java objects and only needed help on the JSON transformation. You would iterate over your ResultSet and build up the lists of Organization objects for add, delete, update operations within the OrganizationModificationsDto. Then you would transform the DTO to JSON and write it to a File.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related