Is it possible to build customized query at run ti

问题:

This is what I am trying to do,

I have a entity,

@Entity
public class JobEntity {

    @Id
    @GeneratedValue
    private Long id;

    @Enumerated(EnumType.STRING)
    private Project project;

    @Enumerated(EnumType.STRING)
    private JobType jobType;

    @Enumerated(EnumType.STRING)
    private JobStatus jobStatus;

    private Date createTime;
}

I know I could customize one query in repository, but that is just one fixed query. I hope to export some RESTful api, like below,

/search?project=""&jobType=""&jobStatue=""&createTime=""

Those params should not be forcing required, and can easily use any of them to do the query, like

/search?createTime=""...

Is there an elegant way to implement this?

回答1:

You could work with the specification API of Spring, which is a wrapper to the JPA criteria API. Make sure your repository extends from JpaSpecificationExecutor<JobEntity>

An example specification would be:

public class JobEntitySpecifications {
    public static Specification<JobEntity> withProject(Project project) {
        if (project == null) {
            return null;
        } else {
            return (root, query, cb) -> cb.equal(root.get("project"), project);
        }
    }

    public static Specification<JobEntity> withJobType() { ... }
    public static Specification<JobEntity> withJobStatus() { ... }
    public static Specification<JobEntity> withCreateTime() { ... }
}

Make sure you return null when no project code/job type/... is given so that it will be ignored in your query.

Now you can use this:

repository.findAll(Specifications.where(JobEntitySpecifications.withJobType(jobType))
    .and(JobEntitySpecifications.withJobStatus(jobStatus))
    .and(JobEntitySpecifications.withProject(project))
    .and(JobEntitySpecifications.withCreateTime(createTime)));

You can make it look even better if you use static imports here.

回答2:

Though not sure about building the query dynamically, think that would eat up the time to build the query by different mechanisms.

There is Elastic Search which helps in building such.

One of the way is to default the query values. As rest of all the values are enums, you can add another enum literal to each of the enum as ALL

something like Project.ALL, JobType.ALL, JobStatus.ALL. Inner static methods in enum should be written as such, when ALL is queried, then add all the JobTypes/Project/JobStatus to a list.

A single standard query which can be written instead of building query on the fly. It is easy for the API in terms of caching the query result even.

Most of RESTful API for search like the one /search?project=""&jobType=""&jobStatus=""&createTime="" works on defaulting.