Unable to map JSON field from postgres DB to POJO class

Question

I have below code in place where I am trying to get only 1 column (JSON type) from postgres DB and map it to a POJO class. But, in the result I am getting correct count but with null values only, even though data is present. Any Help/Suggestion is appreciated.

POJO Class:

@JsonIgnoreProperties(ignoreUnknown = true)
public class EmployeePayload {

    private String empName;

    private String empID;

   //getters and setters
}

Query Execution Code ::

String query = "SELECT emp_payload FROM EmpDetails";
ResultSetHandler<List<EmployeePayload>> employeePayload = new BeanListHandler<EmployeePayload>(EmployeePayload.class);
        List<EmployeePayload> resultList = runner.query(dbConnection, query, employeePayload);

        log.info(":: resultList is :: " + resultList);

Data in DB ::

"{""empName"":""james"",""empID"":""008""}", "{""empName"":""bond"",""empID"":""007""}"

Result in Log ::

resultList is :: [EmployeePayload{empName='null', empID='null'}, EmployeePayload{empName='null', empID='null'}]

JensV · Accepted Answer · 2021-09-13 12:30:02Z

2

The BeanListHandler comes from apache dbutils (docs)

By looking at the docs / source, it's implemented by mapping single columns to single properties of the Bean. You are essentially trying to map a single column to multiple properties which does not work. Now there are two ways to go about this:

Writing your own RowProcessor
Rewriting the query to return multiple columns.

In this situation I would favor the second solution for it's simplicity as postgres has this functionality built-in for its json field types.

Your query could look like this:

String query = "SELECT emp_payload->'empName' AS empName, emp_payload->'empID' AS empID FROM EmpDetails";

(The AS ... might not be necessary but I don't know how psql generates the column names for extracted json values).

If you would execute this query directly in postgres, you would get a result set with column names empName and empID which is exactly what the BeanProcessor (the default processor for BeanListHandler) expects.

answered Sep 13, 2021 at 12:30

JensV

4,6692 gold badges22 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Balaji211 Over a year ago

I went with the second solution, Thanks.

Collectives™ on Stack Overflow

Unable to map JSON field from postgres DB to POJO class

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related