Generating json file in database instead of java

I am reading one table from database using resultset. later I am using this data to generate json file.

I want to generate json file in database and return that file to java because my java code is going to consume POST API.

code is something like this

 	String query="select * from dynamicentitygtt";
	PreparedStatement prepstatement=connection.prepareStatement(query);
	ResultSet rs=prepstatement.executeQuery()
		while rs.next()
        {
          //Load data to POJO
			
		}
      //call method to generate json file.
      //Inject file data to API

what I want.

String query="select * from dynamicentitygtt";
		PreparedStatement prepstatement=connection.prepareStatement(query);
		ResultSet rs=prepstatement.executeQuery()
        //here I want file from database.

My database should return json file. i can create procedure in database. i can also use callable. I can also create json file from table.
But i dont know how to return file from database.

forgive me if my understanding is incorrect.

Since you have noted that you are on Oracle 18c, you have access to the JSON_OBJECT, JSON_OBJECTAGG, JSON_ARRAY, and JSON_ARRAYAGG SQL functions. Using these functions, you should be able to create the JSON structure you need just with a SQL query instead of needing to generate a file and pass it back to your Java call. One item to note is that if you are generating large JSON objects, you will want to modify the RETURNING clause in any of the JSON function calls in the query or your JSON will not be valid.

Below are some examples on how to use the functions:

Query #1

WITH
    emp (employee_id,
         first_name,
         last_name,
         department_number)
    AS
        (SELECT 1, 'John', 'Doe', 100 FROM DUAL
         UNION ALL
         SELECT 2, 'Jane', 'Smith', 100 FROM DUAL
         UNION ALL
         SELECT 3, 'Robert', 'Jones', 200 FROM DUAL
         UNION ALL
         SELECT 4, 'Alan', 'Carter', 300 FROM DUAL)
SELECT json_arrayagg (json_object (*))
  FROM emp;

Result #1 (beautified)

[
  {
    "EMPLOYEE_ID": 1,
    "FIRST_NAME": "John",
    "LAST_NAME": "Doe",
    "DEPARTMENT_NUMBER": 100
  },
  {
    "EMPLOYEE_ID": 2,
    "FIRST_NAME": "Jane",
    "LAST_NAME": "Smith",
    "DEPARTMENT_NUMBER": 100
  },
  {
    "EMPLOYEE_ID": 3,
    "FIRST_NAME": "Robert",
    "LAST_NAME": "Jones",
    "DEPARTMENT_NUMBER": 200
  },
  {
    "EMPLOYEE_ID": 4,
    "FIRST_NAME": "Alan",
    "LAST_NAME": "Carter",
    "DEPARTMENT_NUMBER": 300
  }
]

Query #2

WITH
    emp (employee_id,
         first_name,
         last_name,
         department_number)
    AS
        (SELECT 1, 'John', 'Doe', 100 FROM DUAL
         UNION ALL
         SELECT 2, 'Jane', 'Smith', 100 FROM DUAL
         UNION ALL
         SELECT 3, 'Robert', 'Jones', 200 FROM DUAL
         UNION ALL
         SELECT 4, 'Alan', 'Carter', 300 FROM DUAL)
  SELECT json_arrayagg (
             json_object (
                     'department_number' VALUE department_number,
                     'employees' VALUE
                         json_arrayagg (
                             json_object ('first_name' VALUE first_name, 'last_name' VALUE last_name))))
    FROM emp
GROUP BY department_number;

Result #2 (beautified)

[
  {
    "department_number": 100,
    "employees": [
      { "first_name": "John", "last_name": "Doe" },
      { "first_name": "Jane", "last_name": "Smith" }
    ]
  },
  {
    "department_number": 200,
    "employees": [{ "first_name": "Robert", "last_name": "Jones" }]
  },
  {
    "department_number": 300,
    "employees": [{ "first_name": "Alan", "last_name": "Carter" }]
  }
]

In java using below method you can create List of Map<String,String>.

Where Map have key of columnName and value as value of that column.

public List<Map<String, String>> getList(ResultSet resultSet) throws SQLException {
        List<Map<String, String>> mapList = new ArrayList<>();
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int columnNumber = resultSetMetaData.getColumnCount();
        while (resultSet.next()) {
            Map<String, String> map = new HashMap<>();
            int count = 1;
            while (count <= columnNumber) {
                String columnName = resultSetMetaData.getColumnName(count);
                map.put(columnName, resultSet.getString(columnName));
                count++;
            }
            mapList.add(map);
        }
        return mapList;
    }

Once you are able to create Map, you can create any json string or json object from it using multiple library like this

This will help you.