Describe how to use native queries in Quarkus

[GuentherJulian]

As described in the repository guide, native queries are currently not supported in Quarkus using @Query annotation. However, native queries are an important feature in real live projects. We should show an alternative way to implement native queries in Quarkus using createNativeQuery directly from the EntityManager. We should also show a way to avoid the fragment approach and use default methods for custom implementations directly in the repository interface.

This issue is a replacement of the original issue in the devonfw-microservices repository: As discussed in #37 with spring-data-jpa we do not have support for native queries inside quarkus.

From real life projects (even with quarkus) we have the feedback that native queries are also an important feature. As the OCX team told me they have it in all of their quarkus apps it must be supported by JPA/Hibernate in quarkus. If for some reason it is not (yet) supported by spring-data-jpa in quarkus we should clarify:

• can we trace this down to an issue, see if someone is working on it and there is a roadmap when support for it via @Query annotation will be possible`
• we should demonstrate an alternative solution how to implement it e.g. via a Fragment directly from the EntityManager API using createNativeQuery method.

Side node for the second bullet: The Fragment-Approach is kind of odd to me as you always need 3 types for one thing (repo interface, fragment interface, fragment implementation). The initial benefit of spring-data-jpa is to have just one thing which is the repository interface. Ideally you should be able to write default methods for custom implementations directly in the repo interface what already works. However, to avoid exposing the EntityManager via the Repository interface we can not access it from default methods in the interface.

[GuentherJulian]

Comments from the original issue:

Currently, the quarkus-spring-data-jpa extension only supports the value and countQuery attributes via @Query annotation. nativeQuery is not supported and will cause an IllegalArgumentException (see implementation)

[ERROR]         [error]: Build step io.quarkus.spring.data.deployment.SpringDataJPAProcessor#build threw an exception: java.lang.IllegalArgumentException: Attribute nativeQuery of @Query is currently not supported. Offending method is ... of Repository ...

There is an issue about native query support from November 2019 which is still open: https://github.com/quarkusio/quarkus/issues/5348

---

Native queries can direcly be implemented by using the EntityManager:

Query query = entityManager.createNativeQuery("SELECT * FROM Product WHERE title = :title", ProductEntity.class);
query.setParameter("title", searchCriteria.getTitle());
List<ProductEntity> products = query.getResultList();

Be sure to use the name of the table in the query if you use createNativeQuery, while you must use the entity name if you use createQuery.

---

To avoid the fragment approach, we can use a generic repository as used in the devon4j jpa-spring-data module.

public interface GenericRepository<E> {
  ...

  Query newNativeQuery(String query, Class resultClass);
}

public class GenericRepositoryImpl<E> implements GenericRepository<E> {

  @Inject
  EntityManager entityManager;

  ...

  @Override
  public Query newNativeQuery(String query, Class resultClass) {

    return this.entityManager.createNativeQuery(query, resultClass);
  }

}

Then the repository can extend the generic repository and we can use default methods in the repository to implement our query.

public interface ProductRepository extends JpaRepository<ProductEntity, Long>, GenericRepository<ProductEntity> {

  ...

  default Page<ProductEntity> findByTitle(ProductSearchCriteriaDto dto) {

    Query query = newNativeQuery("select * from Product where title = :title", ProductEntity.class);
    query.setParameter("title", dto.getTitle());
    List<ProductEntity> products = query.getResultList();
    return new PageImpl<>(products, PageRequest.of(dto.getPageNumber(), dto.getPageSize()), products.size());
  }
}