1

I have a complex SQL query which may have different number of parameters in where section. So, query sql should be constructed manually. As a result, I get array of objects which contains 45 fields, each of them I will have to case, convert, etc. I can't use result set mapping because it requires a stable SQL which I should specify in annotation. So the question is is there a way to return pojo or at least map with columns names rather than access all objects by index?

String sql = "select col1 as column1, ...., columnN as columnN from table where col1=2 ";

if(param1!=null){
   sql+=" AND param1="+param1;
}

....

Query q = manager.createNativeQuery(sql);

//getting list on object arrays of 45 fields, would like to have POJO or at least map
List list = q.getResultList();
avalon
  • 2,231
  • 3
  • 24
  • 49
  • `sql+=" AND param1="+param1;` is not a good practice, try with named parameters – Arun Sudhakaran Nov 29 '21 at 11:48
  • Does this answer your question? [JPA : How to convert a native query result set to POJO class collection](https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection) – Arun Sudhakaran Nov 29 '21 at 11:52

1 Answers1

0

If the table from where you retrieve your data is mapped in a POJO, you can specify manually the POJO by doing the following:

Query query = em.createNativeQuery("SELECT * FROM table", MyPojoMappingTable.class);
@SuppressWarnings("unchecked")
List<MyPojoMappingTable> items = (List<MyPojoMappingTable>) query.getResultList();

On the contrary, if the table is not mapped in any POJO and you're in Spring, you could use jdbcTemplate to get at least a map:

@Autowired
NamedParameterJdbcTemplate jdbcTemplate;

String query;
Map<String, Object> queryParameters;

List<Map<String, Object>> rows = jdbcTemplate.queryForList(
    query,
    queryParameters
);
Pasquale
  • 389
  • 3
  • 11