My system required to add filters,and I'm wonder if there any query that like this

SELECT * 
FROM posts p
when byDate is not null then (where p.createAt BETWEEN :startDate AND :endDate)
when byType is not null then (where p.type = :type)

I knew that the query is not valid, but I want at one query to get the data wherever the request has (no filter or all filters or some of filters).

My goal is to create one query to achieve all cases.

CodePudding user response：

It's usually not a good idea to write a big SQL query when you can tell in advance the actual query you want to run.

If you want to run a different query based on conditions you know before running the query, there are different approaches in JPA or Spring that you can use

Spring

You can define the different queries using Spring Data query methods?

public class PostRepository implements JpaRepository<Post, Long> {
    List<Post> findByCreatedAtBetween(Date startDate, Date endDate);
    List<Post> findByTypeIs(String type);
}

And then somewhere in the code, you can:

List<Post> results = null;
if (byDate != null) {
   results = repository.findByCreatedAtBetween(startDate, endDate);
} else if (byType != null) {
   results = repository.findByTypeIs(type);
} else {
   results = repository.findAll();
}

Criteria

With criteria you can create a dynamic query at runtime and execute it:

public class PostRepository implements PostRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Post> findPosts(Filter filter) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> query = cb.createQuery(Post.class);
        Root<User> user = query.from(Post.class);

        if ((filter.getByDate() != null)) {
          // byDate is not null
          ParameterExpression<Date> startDate = builder.parameter( Date.class );
          ParameterExpression<Date> endDate = builder.parameter( Date.class );
          query.where(builder.between( b.get( "createdAt" ), startDate, endDate));
          return em.createQuery(query)
                   .setParameter(startDate, ...)
                   .setParameter(endDate, ...)
                   .getResultList();
        }
        
        if (filter.getByType() != null) {
          ParameterExpression<Date> typeParam = builder.parameter( Date.class );

          query.where(builder.and(root.get("type"), typeParam));
          return em.createQuery(query)
                   .setParameter(typeParam, ...)
                   .getResultList();

        }

        return entityManager.createQuery(query)
            .getResultList();
    }
}

Assuming that your entity has the fields type and createdAt. This approach works well if you don't know in advance what's your query looks like. For example, when you don't know how many conditions you will have to add to it.

But, if I know already which query I want to run, then I prefer to use HQL/JPQL.

HQL

If your queries don't change and you already know what they look like, I find it easier to define them with HQL:

public class PostRepository implements PostRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Post> findPosts(Filter filter) {
       if (filter.getByDate() != null) {
          return em.createQuery("from Post p where p.createdAt between :startDate and :endDate", Post.class)
                    .setParameter("startDate", ...)
                    .setParameter("endDate", ...)
                    .getResultList();
       }
       if (filter.getByType() != null) {
          return em.createQuery("from Post p where p.type =:type", Post.class)
                    .setParameter("type", ...)
                    .getResultList();
       }
       return em.createQuery("from Post", Post.class)
                    .getResultList();       
    }
}

You can refactor the code to make it more elegant, but it should give you an idea. Note that if you need to reuse the same queries in different services, it might be helpful to define them using the annotation @NamedQuery.

Filters

In Hibernate (not JPA) you can also define filters. They are SQL filter conditions that one can apply at runtime:

@Entity
@FilterDef(name = Post.BY_DATE, defaultCondition = "createdAt between :startDate and :endDate", parameters = {@ParamDef(name = "startDate", type = "date"), @ParamDef(name = "startDate", type = "date") })
@FilterDef(name = Post.BY_TYPE, defaultCondition = "type = :type", parameters = @ParamDef(name = "startDate", type = "date"))
@Filter(name = Post.BY_DATE)
@Filter(name = Post.BY_TYPE)
class Post {
   static final String BY_DATE = "Post.byDateFilter";
   static final String BY_TYPE = "Post.byFilter"

   private String type;
   private Date createdAt;
   ...
}

Then:

public class PostRepository implements PostRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<Post> findPosts(Filter filter) {
       enableFilters(em);
       return em.createQuery("from Post", Post.class).getResultList();
    }

    private void enableFilters(Filter filter, EntityManager em) {
       if (filter.getByDate() != null) {
          em.unwrap(Session.class)
            .enableFilter( Post.BY_DATE )
            .setParameter("startDate", ...)
            .setParameter("endDate", ...);
       } else if (filter.getByType() != null) {
          em.unwrap(Session.class)
            .enableFilter( Post.BY_TYPE )
            .setParameter("type", ...);
       }
    }
}