I am calling postgreSQL function from Java using SimpleJdbcCall and extracting the output in ResultSet, while the function returns data when run in postgre but in Java it is giving null result.
Here is my postgre function that I am calling:
CREATE OR REPLACE FUNCTION package.getdemojob(create_user_id character varying)
RETURNS TABLE(batch_id bigint, content_id character varying, start_date timestamp without time zone,
end_date timestamp without time zone)
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN query
SELECT MY_TABLE.BATCH_ID, MY_TABLE.CONTENT_ID, MY_TABLE.START_DATE, MY_TABLE.END_DATE
FROM MY_TABLE
WHERE MY_TABLE.CREATE_USER = create_user_id
ORDER BY BATCH_ID;
END;
$function$
;
Java code that I am using to call this function is:
this.demoGetJob = new SimpleJdbcCall(jdbcTemplate)
.withCatalogName("package")
.withProcedureName("getdemojob")
.withoutProcedureColumnMetaDataAccess()
.useInParameterNames("create_user_id")
.declareParameters(
new SqlParameter("create_user_id", Types.VARCHAR)
);
SqlParameterSource in = new MapSqlParameterSource().addValue("create_user_id", userId);
ResultSet rs = demoGetJob.executeFunction(ResultSet.class, in);
while(rs.next()){
System.out.println(rs.getInt("batch_id"));
}
On line rs.next() I am getting nullPointerException though there is data present in MY_TABLE with the given create_user_id
What wrong am I doing? Thanks in advance.