带有ORM，EntityManager，@Query的Spring Data Repository，处理自定义SQL查询的最优雅方式是什么？ [英] Spring Data Repository with ORM, EntityManager, @Query, what is the most elegant way to deal with custom SQL queries?

问题描述

根据我的研究，我最近一直在学习Spring Boot框架，我可以在maven依赖项中包含Hibernate JPA实现。 list，扩展 CrudRepository 并添加自定义方法

或

• 使用Hibernate的 EntityManager 执行自定义SQL查询

>

或

• 使用注释 @ org.springframework.data。 jpa.repository.Query

问题

• 他们是否缓存我查询的数据？




• 使用自定义SQL查询，最优雅的方法是哪一种？如果它们都不是，那么在使用Spring框架（不限于存储库模式）和Hibernate时是否有其他方式来处理自定义SQL查询？

1。存储库方法

  @Entity 
 @Table（name = Collection.TABLE_NAME）
 public class Collection {
 public final static String TABLE_NAME =collection; 
 ... 
} 
 
公共接口CollectionRepository扩展CrudRepository< Collection，Integer> {
} 
 
公共类CollectionRepositoryImpl {
 @Autowired 
私人CollectionRepository collectionRepository; 
 
公共清单<收集> findCollectionsForSeason（int season，int count）{
 List< Collection> results = new ArrayList<>（）; 
 
（Collection c：collectionRepository.findAll（））{
 if（c.getSeason（）== season）{
 results.add（c）; 
} 
} 
 
返回结果; 
} 
} 
  

2。 EntityManager方法

  public class xxx {
 private EntityManager em; 
 ... 
 
公共列表< Collection> findCollectionsForSeason（int season，int count）{
 String sqlString = String.format（SELECT * FROM`collection` WHERE`season` =％d LIMIT％d，season，count）; 
返回this.em.createNativeQuery（sqlString）.getResultList（）; 
} 
} 
  

3。 @Query 方法

注意：下面的这段代码错误，但我无法弄清楚如何正确地做到这一点。所以如果有人能够帮助我使用命名参数和限制子句（mysql）来实现本地查询，那将会很好。

  public interface CollectionRepository扩展了CrudRepository< Collection，Integer> {
 @Query（value =SELECT * FROM`collection` WHERE`season` =％d LIMIT％d，nativeQuery = true）
 List< Collection> findCollectionsForSeason（int season，int count）{
} 
} 
  

谢谢！

解决方案

不要做任何这些事情。而不是扩展 CrudRepository extend PagingAndSortingRepository 或（正如您使用的是JPA） JpaRepository 。如何使用以及如何限制结果在参考指南。

  public interface CollectionRepository extends PagingAndSortingRepository< Collection，Integer> {} 
  

然后添加一个查找器方法，它接受您的参数并设置 Pageable 参数。

  List< Collection> findByReason（字符串原因，Pageable页面）; 
  

现在在您的服务中，您可以执行类似于

 返回collectionRepository.findByReason（reason，new PageRequest（0，count））; 
  

如果您需要更多的查询灵活性，您总是可以将分页方法与规范。

  public interface CollectionRepository 
扩展CrudRepository< Collection，Integer> ;, 
 JpaSpecificationExecutor< Collection> {} 
  

然后在您的服务中

  return collectionRepository.findAll（new YourSpecification（），new PageRequest（0，count））; 
  

这允许非常灵活的查询生成（我们使用一个通用的实现来为大多数可搜索的数据表）。

基本上它归结为使用框架（并理解它）而不是解决它。

I've been learning Spring Boot framework recently, according to my research, I can either

• Include Hibernate JPA implementation in maven dependency list, extends CrudRepository and add custom methods

or

• Use EntityManager from Hibernate to execute custom SQL query

or

• Use annotation @org.springframework.data.jpa.repository.Query

Questions

• Do they cache data I queried?

• Do they have the same performance?

• Which one is the most elegant way to go with custom SQL queries? If none of them is, is there any other way to deal with custom SQL queries when using Spring framework (not limited to Repository pattern) and Hibernate?

1. Repository approach

@Entity
@Table(name = Collection.TABLE_NAME)
public class Collection {
    public final static String TABLE_NAME = "collection";
    ...
}

public interface CollectionRepository extends CrudRepository<Collection, Integer> {
}

public class CollectionRepositoryImpl {
    @Autowired
    private CollectionRepository collectionRepository;

    public List<Collection> findCollectionsForSeason(int season, int count) {
        List<Collection> results = new ArrayList<>();

        for (Collection c : collectionRepository.findAll()) {
            if (c.getSeason() == season) {
                results.add(c);
            }
        }

        return results;
    }
}

2. EntityManager approach

public class xxx {
    private EntityManager em;
    ...

    public List<Collection> findCollectionsForSeason(int season, int count) {
        String sqlString = String.format("SELECT * FROM `collection` WHERE `season`=%d LIMIT %d", season, count);
        return this.em.createNativeQuery(sqlString).getResultList();
    }
}

3. @Query approach

NOTE: this code snippet below is wrong, but I can't figure out how to do it correctly. So it would be nice if someone can help me to implement native query with named parameters and limit clause (mysql).

public interface CollectionRepository extends CrudRepository<Collection, Integer> {
    @Query(value = "SELECT * FROM `collection` WHERE `season`=%d LIMIT %d", nativeQuery = true)
    List<Collection> findCollectionsForSeason(int season, int count) {
    }
}

Thanks!

解决方案

Don't do any of those things. Instead of extending CrudRepository extend PagingAndSortingRepository or (as you are using JPA) JpaRepository. How to work with that and how to limit results is explained in the reference guide.

public interface CollectionRepository extends PagingAndSortingRepository <Collection, Integer> {}

Then add a finder method that takes your arguments and a Pageable argument.

List<Collection> findByReason(String reason, Pageable page);

Now in your service you can do something like

return collectionRepository.findByReason(reason, new PageRequest(0, count));

If you need more flexibility for your queries you can always combine the paging approach with a Specification.

public interface CollectionRepository 
    extends CrudRepository<Collection, Integer>,
            JpaSpecificationExecutor<Collection> {}

Then in your service

return collectionRepository.findAll(new YourSpecification(), new PageRequest(0, count));

That allows for very flexible query generation (we use a general implementation to serve most of our searchable data tables).

Basically it comes down to working with the framework (and understanding it) instead of working around it.

这篇关于带有ORM，EntityManager，@Query的Spring Data Repository，处理自定义SQL查询的最优雅方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！