问题描述
我使用 spring 数据和 JpaSpecificationExecutor::findAll 方法来获取我的模型.调用此方法时如何使用查询提示?
上面的源代码工作正常,但我无法为我的 JPA 提供程序(在我的情况下为 EclipseLink)设置 QueryHint.
@Repository公共接口 ProductRepository 扩展了 JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product>{}@服务公共类产品服务{@自动连线私有产品存储库产品存储库;公开列表<产品>findByTitle(字符串语言环境,字符串 titleToSearch){return productRepository.findAll((Rootroot, CriteriaQuery> query, CriteriaBuilder builder) -> {return builder.equal(builder.function("jsonb_extract_path_text", String.class, root.<String>get("title"), builder.literal(locale)), titleToSearch);});}}
我使用spring-data使用Query Hints的方式如上,
@Repository公共接口 ProductRepository 扩展了 JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product>{@QueryHints(值 = {@QueryHint(name = org.eclipse.persistence.config.QueryHints.BATCH_TYPE, value = "JOIN"),@QueryHint(名称 = org.eclipse.persistence.config.QueryHints.BATCH,值 = p.productCategory"),@QueryHint(名称 = org.eclipse.persistence.config.QueryHints.BATCH,值 = p.productFileList")}, forCounting = false)@Query("从产品 p 中选择 p")公开列表<产品>寻找();}
我还发现 this 尚未解决.
当我想使用 spring-data 创建查询时,我遵循上述算法.
<块引用>1) CrudRepository、PagingAndSortingRepository、JpaRepository等spring-data现有接口是否已经提供查询代码> 等等?
示例:saveAndFlush 或 findAll 方法,更多内容请参见 文档.
产品 product = new Product();//二传手..productRepository.saveAndFlush();
<块引用>
2) 我可以在方法名称中使用关键字创建方法吗?
示例:计数,更多内容请参见 文档.
@Repository公共接口 ProductRepository 扩展了 JpaRepository<Product, Integer>{Long countByTitle(String title);列表<产品>findByTitleLikeAndVisible(字符串标题,布尔可见);}
<块引用>
3) 我可以创建自定义查询方法编写 JPQL 吗?
示例:文档.
在这种情况下,spring data 不会尝试使用方法名称中的关键字来创建查询,因此方法名称可以是任何你想要的.
@Repository公共接口 ProductRepository 扩展了 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")列表<产品>findByTitleLikeAndVisibleTrue(@Param("title") 字符串标题);}
<块引用>
4) 我想要变量列名还是变量where 条件?那么解决方案就是规范.
示例:docs,所以回答p>
@Repository公共接口 ProductRepository 扩展了 JpaRepository<Product, Integer>, JpaSpecificationExecutor<Product>{}@服务公共类产品服务{@自动连线私有产品存储库产品存储库;公开列表<产品>findByColumn(String columnName, Object value) {return productRepository.find((Root<Product> root, CriteriaQuery<?> 查询, CriteriaBuilder builder) -> {return builder.and(builder.equal(root.<String>get(columnName), value));});}}
<块引用>
5) 我想要更多吗? 解决方案是获取 EntityManager 并像我在没有 spring 数据库的情况下使用它一样使用它.(这是这个问题的答案)
示例:所以回答,更多在 文档
//创建一个接口并添加您希望与 EntityManger 一起使用的方法.公共接口 ProductRepositoryExt {公开列表<产品>findByTitle(字符串标题);}//实现你创建的接口.请注意,类名必须与附加了Impl"的 spring-data @Repository 接口相同.公共类 ProductRepositoryImpl 实现 ProductRepositoryExt {@PersistenceContext私人 EntityManager em;@覆盖公开列表<产品>findByTitle(字符串标题){//em.getTransaction().begin();String sql = "SELECT p FROM Product p WHERE p.title=:title')";TypedQuery<ProductCategory>查询 = em.createQuery(sql, Product.class);query.setParameter("title", title);//添加你想要的查询提示..query.setHint(org.eclipse.persistence.config.QueryHints.BATCH_TYPE, "JOIN");query.setHint(org.eclipse.persistence.config.QueryHints.BATCH, "p.productCategory");返回查询.getResultList();//em.getTransaction().commit();}}//从你的 spring-data @Repository 接口扩展这个接口.@Repository公共接口 ProductRepository 扩展 JpaRepository, ProductCategoryRepositoryExt {}
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.
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 { }