Spring Boot Native Query using entity manager and Custom result mapper
There are multiple methods to work on native queries in spring boot,like using Query annotation @ResultSet mapping annotation to map non entity Dtos,
as query complexity increases code readability also decreases,
i have preferred to use simple Solution and to map result to Dto for native query, for better code readability
Custom Object Mapper
The main Objective of the custom object mapper is to convert Tuple.class Collection into HashSet of key value pair to json and to class object.
by using jackson object mapper.
public class CustomObjectMapper {
public static List<?> convertToEntity(List<Tuple> input, Class<?> dtoClass) {
List<Object> arrayList = new ArrayList<>();
input.stream().forEach(tuple -> {
Map<String, Object> temp = new HashMap<>();
tuple.getElements().
stream().
forEach(tupleElement ->
temp.put(tupleElement.getAlias().toLowerCase(),
tuple.get(tupleElement.getAlias())));
ObjectMapper map = new ObjectMapper();
map.configure(MapperFeature.ACCEPT_CASE_INSENSITIVE_PROPERTIES, true);
map.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
try {
//converting to json
String mapToString = map.writeValueAsString(temp);
//converting json to entity
arrayList.add(map.readValue(mapToString, dtoClass));
} catch (JsonProcessingException e) {
throw new RuntimeException(e.getMessage());
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
}
});
return arrayList;
}
}
Implemntation
class customRepo {
@PersistenceContext
private EntityManager em;
List<Object> getSomeblablabla() {
Query query =
em.createNativeQuery("Select id ,name From blabla", Tuple.class);
returnCustomObjectMapper.convertToEntity(queryObj.getResultList(), BlablaDTO.class);
}
}
Notice: Dto's variable name should be same as table column name or aliased...!!!