Use QueryHint when using JpaSpecificationExecutor

Question

I use spring data and the JpaSpecificationExecutor::findAll method to fetch my models. How I could use query hints when calling this method?
The above source code works fine but I can't set QueryHint for my JPA provider (in my case EclipseLink).

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
}

@Service
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    public List<Product> findByTitle(String locale, String titleToSearch) {
        return productRepository.findAll((Root<ProductCategory> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
            return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(locale)), titleToSearch);
        });
    }
}

The way I use the Query Hints using spring-data is the above,

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {

    @QueryHints(value = {
        @QueryHint(name = org.eclipse.persistence.config.QueryHints.BATCH_TYPE, value = "JOIN"),
        @QueryHint(name = org.eclipse.persistence.config.QueryHints.BATCH, value = "p.productCategory"),
        @QueryHint(name = org.eclipse.persistence.config.QueryHints.BATCH, value = "p.productFileList")
    }, forCounting = false)
    @Query("SELECT p FROM Product p")
    public List<Product> find();
}

I 've also found this which is unresolved yet.

Carmody

have you tried to overwrite findAll in the ProductRepository interface and add the query hints? – Jarnagin

The ProductRepository interface is been implemented by spring data at runtime (spring byte code instrumentation). Spring data supports custom implementation using a procedure that is described in the spring data docs. I will post an answer because this is my solution. – Carmody

Answer 1

When I want to create a query using spring-data I follow the above algorithm.

1) Is the query already provided by the existing interfaces of spring-data like CrudRepository, PagingAndSortingRepository, JpaRepository etc?
Examples: saveAndFlush or findAll methods, more in docs.

Product product = new Product();
// Setters..
productRepository.saveAndFlush();

2) Can I create a method using keywords inside method names?
Examples: count, more in docs.

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer> {

    Long countByTitle(String title);

    List<Product> findByTitleLikeAndVisible(String title, boolean visible);
}

3) Can I create a custom query method writing JPQL?
Examples: docs.
In this case spring data does not try to create the query using keywords inside method names, so the method names can be whatever you wish.

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer> {

    @Query("SELECT COUNT(p) FROM Product p WHERE p.title=?1")
    Long countByTitle(String title);

    @Query("SELECT p FROM Product p WHERE p.title LIKE :title AND visible=true")
    List<Product> findByTitleLikeAndVisibleTrue(@Param("title") String title);
}

4) Do I want variable column names or variable where conditions? Then the solution is the Specification.
Example: docs, so answer

@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product> {
}


@Service
public class ProductService {

    @Autowired
    private ProductRepository productRepository;

    public List<Product> findByColumn(String columnName, Object value) {
        return productRepository.find((Root<Product> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
            return builder.and(builder.equal(root.<String>get(columnName), value));
        });
    }
}

5) Do I want more? The solution is to get the EntityManager and use it like I used it without the spring data library. (This is the answer to this so question)
Example: so answer, more in docs

// Create an interface and add the methods you wish to use with EntityManger.
public interface ProductRepositoryExt {
    public List<Product> findByTitle(String title);
}

// Implement the interface you created. Be careful the class name must be identical to the spring-data @Repository interface with the "Impl" appended.
public class ProductRepositoryImpl implements ProductRepositoryExt {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Product> findByTitle(String title) {
//        em.getTransaction().begin();
        String sql = "SELECT p FROM Product p WHERE p.title=:title')";
        TypedQuery<ProductCategory> query = em.createQuery(sql, Product.class);
        query.setParameter("title", title);
        //  Add the query hints you wish..
        query.setHint(org.eclipse.persistence.config.QueryHints.BATCH_TYPE, "JOIN");
        query.setHint(org.eclipse.persistence.config.QueryHints.BATCH, "p.productCategory");

        return query.getResultList();
//        em.getTransaction().commit();
    }
}

// Extend this interface from your spring-data @Repository interface.
@Repository
public interface ProductRepository extends JpaRepository<Product, Integer>, ProductCategoryRepositoryExt {
}

Carmody

Just a tip for nowadays in current Spring Data 2.2 about the solution 4): the method find is now findAll. – Snashall