title: JPA LIKE and and IN clause
body: <p>New to JPA, I have two entity and a joining table like below -</p>
  <pre><code>@Entity
  @Table(name = &quot;classes&quot;)
  public class ClassDao {
  
      @Id
      @GeneratedValue
      private Long id;
  
      @Column(nullable = false)
      private String title;
  
      @Column(length = 5120)
      private String description;
  
     
      @ManyToMany
      @ToStringExclude
      @EqualsAndHashCode.Exclude
      @JoinTable(name = &quot;db_joint_table__class_activities&quot;,
              joinColumns =
                      { @JoinColumn(name = &quot;class_id&quot;, referencedColumnName = &quot;id&quot;) },
              inverseJoinColumns =
                      { @JoinColumn(name = &quot;activity_id&quot;, referencedColumnName = &quot;id&quot;) })
      private Set&lt;ActivityDao&gt; activities;
  }
  </code></pre>
  <p>and</p>
  <pre><code>@Entity
  @Table(name = &quot;activity&quot;)
  public class ActivityDao {
  
      @Id
      @GeneratedValue
      private Long id;
  
      @Column(nullable = false, unique = true)
      private String name;
  
  
      @ManyToMany
      @ToStringExclude
      @EqualsAndHashCode.Exclude
      @JoinTable(name = &quot;db_joint_table__class_activities&quot;,
              joinColumns =
                      { @JoinColumn(name = &quot;activity_id&quot;, referencedColumnName = &quot;id&quot;) },
              inverseJoinColumns =
                      { @JoinColumn(name = &quot;class_id&quot;, referencedColumnName = &quot;id&quot;) })
      private Set&lt;ClassDao&gt; classes;
  }
  </code></pre>
  <p>and two repositories like -</p>
  <pre><code>public interface ClassRepo extends JpaRepository&lt;ClassDao, Long&gt; {
  }
  
  public interface ActivityRepo extends JpaRepository&lt;ActivityDao, Long&gt; {
  }
  </code></pre>
  <p>I like to make a SQL like query(<strong>with pagination</strong> ) -</p>
  <pre><code>SELECT * FROM classes WHERE (classes.name LIKE %SEARCH_TERM% OR classes.description LIKE %SEARCH_TERM%) 
  AND QUERY_ACTIVITY IN classes.activities;
  </code></pre>
  <p>SEARCH_TERM = dynamic input to search query.
  QUERY_ACTIVITY = an Activity, dynamic input to search query.</p>
  <p><strong>Expected</strong> - search will return classes which contains SEARCH_TERM in it's <code>name</code> or <code>description</code> and contains the QUERY_ACTIVITY in it's <code>activities</code> attribute.</p>
  <p>How to do it?</p>
  <p>If I try like bellow -</p>
  <pre><code>@Query(value = &quot;SELECT * FROM classes WHERE (lower(title) LIKE %:titlePhrase% OR lower(description)&quot; +
          &quot; LIKE %:descriptionPhrase%) AND :activityDao IN activities&quot;, nativeQuery = true)
  Page&lt;ClassDao&gt; findClassWithActivityNew(@Param(&quot;titlePhrase&quot;) String titlePhrase, @Param(&quot;descriptionPhrase&quot;)
  String descriptionPhrase, @Param(&quot;activityDao&quot;) ActivityDao activityDao, Pageable pageable);
  </code></pre>
  <p>I am getting bellow error -</p>
  <pre><code>2022-08-10 10:16:06,945 WARN  [http-nio-8080-exec-1] org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: 42601
  2022-08-10 10:16:06,945 ERROR [http-nio-8080-exec-1] org.hibernate.engine.jdbc.spi.SqlExceptionHelper: ERROR: syntax error at or near &quot;activities&quot;
    Position: 92
  2022-08-10 10:16:06,956 DEBUG [http-nio-8080-exec-1] com.markany.anyfit.server.rest.BaseController: handleException():
  org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
  </code></pre>