Query syntax in URL when using Spring and @QuerydslPredicate

Question

How can I write the HTTP request URL in order to get a query similar to:

select *
from incidents i,
     jira_issues ji
where i.incident_id = ji.incident_id
  and ji.external_jira_issue_id = 'ABC-123'
  and ji.jira_server_id = '1'

I have the following classes:

@Entity(name = "incidents")
public class IncidentEntity {
  @OneToMany(
      mappedBy = "incident",
      cascade = CascadeType.ALL
  )
  @LazyCollection(LazyCollectionOption.FALSE)
  private List<JiraIssueEntity> jiraIssues;
...
}

@Entity(name = "jira_issues")
public class JiraIssueEntity {

  @EmbeddedId
  @EqualsAndHashCode.Include
  private JiraIssueId id;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "incident_id")
  @ToString.Exclude
  private IncidentEntity incident;
...
}

@Embeddable
public class JiraIssueId implements Serializable {

  @EqualsAndHashCode.Include
  private String externalJiraIssueId;
  @EqualsAndHashCode.Include
  private String jiraServerId;
}

This is my API method signature:

  @GetMapping("")
  public Page<Incident> listIncidents(
      @QuerydslPredicate(root = IncidentEntity.class) Predicate predicate
  );

I know that I can send something like:

/incidents/?jiraIssues.id.externalJiraIssueId=ABC-123&jiraIssues.id.jiraServerId=1"

This translates to the following query:

select *
from incidents incidenten0_
where (exists(select 1
              from jira_issues jiraissues1_
              where incidenten0_.incident_id = jiraissues1_.incident_id
                and (lower(jiraissues1_.external_jira_issue_id) like ? escape '!')))
  and (exists(select 1
              from jira_issues jiraissues2_
              where incidenten0_.incident_id = jiraissues2_.incident_id
                and (lower(jiraissues2_.jira_server_id) like ? escape '!')))

which is not so good.

I don't know how to:

Do equals and not contains (rows with externalJiraIssueId=ABC-1234 will return as well but I don't want that).
Check that same JiraIssue has externalJiraIssueId=ABC-123 and jiraIssues.id.jiraServerId=1 and not different JiraIssues that each matches one (something like jiraIssues.id=(ABC-123, 1)

Thank you.

nkoffman · Accepted Answer · 2021-05-09 18:29:45Z

1

regarding the first problem you can make your 'repository' interface extend QuerydslPredicateExecutor and QuerydslBinderCustomizer then you can override the 'customize' method with something like this:

    @Override
      default void customize(QuerydslBindings bindings, @NotNull QIncidentEntity root) 
      {
         bindings.bind(String.class)
            .first((SingleValueBinding<StringPath, String>) 
                StringExpression::equalsIgnoreCase);
      }

this will make the query check for equals (ignoring the case) and not contains.

answered May 9, 2021 at 18:29

nkoffman

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query syntax in URL when using Spring and @QuerydslPredicate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related