Java @queryhint,使用JpaSpecificationExecutor时使用QueryHint

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, JpaSpecificationExecutor {

}

@Service

public class ProductService {

@Autowired

private ProductRepository productRepository;

public List findByTitle(String locale, String titleToSearch) {

return productRepository.findAll((Root root, CriteriaQuery> query, CriteriaBuilder builder) -> {

return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.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, JpaSpecificationExecutor {

@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 find();

}

I 've also found this which is unresolved yet.

解决方案

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 {

Long countByTitle(String title);

List 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 {

@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 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, JpaSpecificationExecutor {

}

@Service

public class ProductService {

@Autowired

private ProductRepository productRepository;

public List findByColumn(String columnName, Object value) {

return productRepository.find((Root root, CriteriaQuery> query, CriteriaBuilder builder) -> {

return builder.and(builder.equal(root.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 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 findByTitle(String title) {

// em.getTransaction().begin();

String sql = "SELECT p FROM Product p WHERE p.title=:title')";

TypedQuery 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, ProductCategoryRepositoryExt {

}