title: Spring Data JPA Specification using CriteriaBuilder with a one to many relationship
body: <p>I have a <code>User</code> entity, a <code>UserToApplication</code> entity, and an <code>Application</code> entity.</p>
  
  <p>A single <code>User</code> can have access to more than one <code>Application</code>. And a single <code>Application</code> can be used by more than one <code>User</code>.</p>
  
  <p>Here is the <code>User</code> entity.</p>
  
  <pre><code>@Entity
  @Table(name = "USER", schema = "UDB")
  public class User {
      private Long userId;
      private Collection&lt;Application&gt; applications;
      private String firstNm;
      private String lastNm;
      private String email;
  
      @SequenceGenerator(name = "generator", sequenceName = "UDB.USER_SEQ", initialValue = 1, allocationSize = 1)
      @Id
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "generator")
      @Column(name = "USER_ID", unique = true, nullable = false)
      public Long getUserId() {
          return userId;
      }
  
      public void setUserId(Long userId) {
          this.userId = userId;
      }
  
      @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
      public Collection&lt;Application&gt; getApplications() {
          return applications;
      }
  
      public void setApplications(Collection&lt;Application&gt; applications) {
          this.applications = applications;
      }
  
      /* Other getters and setters omitted for brevity */
  }
  </code></pre>
  
  <p>Here is the <code>UserToApplication</code> entity.</p>
  
  <pre><code>@Entity
  @Table(name = "USER_TO_APPLICATION", schema = "UDB")
  public class Application {
      private Long userToApplicationId;
      private User user;
      private Application application;
  
      @SequenceGenerator(name = "generator", sequenceName = "UDB.USER_TO_APP_SEQ", initialValue = 0, allocationSize = 1)
      @Id
      @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "generator")
      @Column(name = "USER_TO_APPLICATION_ID", unique = true, nullable = false)
      public Long getUserToApplicationId() {
          return userToApplicationId;
      }
  
      public void setUserToApplicationId(Long userToApplicationId) {
          this.userToApplicationId = userToApplicationId;
      }
  
      @ManyToOne
      @JoinColumn(name = "USER_ID", referencedColumnName = "USER_ID", nullable = false)
      public User getUser() {
          return user;
      }
  
      public void setUser(User user) {
          this.user = user;
      }
  
      @ManyToOne
      @JoinColumn(name = "APPLICATION_ID", nullable = false)
      public Application getApplication() {
          return application;
      }
  }
  </code></pre>
  
  <p>And here is the <code>Application</code> entity.</p>
  
  <pre><code>@Entity
  @Table(name = "APPLICATION", schema = "UDB")
  public class Application {
      private Long applicationId;
      private String name;
      private String code;
  
      /* Getters and setters omitted for brevity */
  }
  </code></pre>
  
  <p>I have the following <code>Specification</code> that I use to search for a <code>User</code> by <code>firstNm</code>, <code>lastNm</code>, and <code>email</code>.</p>
  
  <pre><code>public class UserSpecification {
  
      public static Specification&lt;User&gt; findByFirstNmLastNmEmail(String firstNm, String lastNm, String email) {
          return new Specification&lt;User&gt;() {
              @Override
              public Predicate toPredicate(Root&lt;User&gt; root, CriteriaQuery&lt;?&gt; query, CriteriaBuilder cb) {
                  final Predicate firstNmPredicate = null;
                  final Predicate lastNmPredicate = null;
                  final Predicate emailPredicate = null;
  
                  if (!StringUtils.isEmpty(firstNm)) {
                      firstNmPredicate = cb.like(cb.lower(root.get(User_.firstNm), firstNm));
                  }
                  if (!StringUtils.isEmpty(lastNm)) {
                      lastNmPredicate = cb.like(cb.lower(root.get(User_.lastNm), lastNm));
                  }
                  if (!StringUtils.isEmpty(email)) {
                      emailPredicate = cb.like(cb.lower(root.get(User_.email), email));
                  }
                  return cb.and(firstNmPredicate, lastNmPredicate, emailPredicate);
              }
          };
      }
  
  }
  </code></pre>
  
  <p>And here is the <code>User_</code> metamodel that I have so far.</p>
  
  <pre><code>@StaticMetamodel(User.class)
  public class User_ {
      public static volatile SingularAttribute&lt;User, String&gt; firstNm;
      public static volatile SingularAttribute&lt;User, String&gt; lastNm;
      public static volatile SingularAttribute&lt;User, String&gt; email;
  }
  </code></pre>
  
  <p>Now, I would like to also pass in a list of application IDs to the <code>Specification</code>, such that its method signature would be:</p>
  
  <pre><code>public static Specification&lt;User&gt; findByFirstNmLastNmEmailApp(String firstNm, String lastNm, String email, Collection&lt;Long&gt; appIds)
  </code></pre>
  
  <p>So, my question is, if I add the <code>@OneToMany</code> mapping to the <code>User_</code> metamodel for the <code>Collection&lt;Application&gt; applications</code> field of my <code>User</code> entity, then how would I reference it in the <code>Specification</code>?</p>
  
  <p>My current <code>Specification</code> would be similar to the following SQL query:</p>
  
  <pre><code>select * from user u
  where lower(first_nm) like '%firstNm%'
  and lower(last_nm) like '%lastNm%'
  and lower(email) like '%email%';
  </code></pre>
  
  <p>And what I would like to achieve in the new <code>Specification</code> would be something like this:</p>
  
  <pre><code>select * from user u
  join user_to_application uta on uta.user_id = u.user_id
  where lower(u.first_nm) like '%firstNm%'
  and lower(u.last_nm) like '%lastNm%'
  and lower(u.email) like '%email%'
  and uta.application_id in (appIds);
  </code></pre>
  
  <p>Is it possible to do this kind of mapping in the metamodel, and how could I achieve this result in my <code>Specification</code>?</p>