title: What is the best way to query on a foreign key column?
body: <p>I'm using JPA and Querydsl to dynamically build queries based on criteria that are being passed in through a REST API. For the most part, that's working fine but I'm unsure as to what is the best way to query for entities based on a value in a foreign key column.</p>
  
  <p>To illustrate the scenario, consider the following (simplified) entities.</p>
  
  <pre><code>@Entity
  @Table(name = "division")
  public class Division {
      @Id
      private Long id;
  
      @Column(unique = true)
      private String uniqueKey;
  
      @OneToMany(mappedBy = "division")
      private List&lt;DivisionGroup&gt; groups;
  }
  
  @Entity
  @Table(name = "division_group")
  public class DivisionGroup {
      @ManyToOne
      @JoinColumn(referencedColumnName = "uniqueKey")
      private Division division;
  }
  </code></pre>
  
  <p>Note, that Division is not referenced by <code>id</code> but by <code>uniqueKey</code> instead. Let's just say I have my reasons for doing this.</p>
  
  <p>Now, I would like to run a query such as the following:</p>
  
  <pre><code>select * from division_group where division = 'someUniqueKey'
  </code></pre>
  
  <p>Seems simple enough but using JPA it requires a join and knowledge of which field contains the foreign key value (<code>uniqueKey</code> in this case). I imagine a JPA query would look similar to approach 1. </p>
  
  <p>Note, that I'm not using any generated Q-types because the types are determined dynamically based on the request.</p>
  
  <p><strong>Approach 1 - JPAQuery</strong></p>
  
  <pre><code>String divisionKey = "abc"; // the division's uniqueKey value from the request
  
  // entity classes would be determined dynamically; hardcoded for this example
  PathBuilder division = new PathBuilder&lt;&gt;(Division.class, "division");
  PathBuilder divisionGroup = new PathBuilder&lt;&gt;(DivisionGroup.class, "divisionGroup");
  
  JPAQuery query = new JPAQuery(entityManager)
      .from(divisionGroup)
      .innerJoin(divisionGroup.get("division"), division)
      .where(division.get("uniqueKey").eq(divisionKey))
      .list(divisionGroup);
  </code></pre>
  
  <p>I have the following problems with this approach:</p>
  
  <ul>
  <li>The join seems unnecessary (but I could live with that)</li>
  <li>It requires me to have knowledge of the fact that the <code>uniqueKey</code> field is used as join column. This is the real problem since the code should be able to handle all kinds of entities with various mappings.</li>
  <li>I'm somehow getting a <code>null=null</code> criteria mixed into the query. I'm guessing that might be unrelated though.</li>
  </ul>
  
  <p>Alternatively, I looked into using <code>JPASQLQuery</code> instead of <code>JPAQuery</code>. That looks similar to the following:</p>
  
  <p><strong>Approach 2 - JPASQLQuery</strong></p>
  
  <pre><code>String divisionKey = "abc"; // the division's uniqueKey value from the request
  
  MySQLTemplates templates = new MySQLTemplates();
  JPASQLQuery jpasqlQuery = new JPASQLQuery(entityManager, templates);
  
  CustomHibernateNamingStrategy namingStrategy = new CustomHibernateNamingStrategy();
  Class entityClass = DivisionGroup.class; // hardcoded for example
  String tableName;
  
  if (entityClass.isAnnotationPresent(Table.class)) {
      tableName = namingStrategy.tableName(((Table) entityClass.getAnnotation(Table.class)).name());
  } else {
      tableName = namingStrategy.classToTableName(entityClass.getSimpleName());
  }
  
  PathBuilder qDivisionGroup = new PathBuilder(entityClass, tableName);
  Path sDivisionGroup = new PathImpl(entityClass, tableName);
  
  List&lt;DivisionGroup&gt; groups = jpasqlQuery.from(sDivisionGroup)
      .where(Expressions.stringPath("division").eq(divisionKey))
      .list(qDivisionGroup);
  </code></pre>
  
  <p>This approach works and doesn't require any knowledge about how the <code>Division</code> entity looks like and that the <code>uniqueKey</code> field is used for the FK relationship. What I don't like about this approach:</p>
  
  <ul>
  <li>I have to figure out the table name. Would be nice if that could be done implicitly somehow since I'm using a JPA entity anyway.</li>
  <li>I have to provide <code>MySQLTemplates</code> which makes it harder to switch datasources.</li>
  </ul>
  
  <p>Is there a better way to achieve what I'm trying to do?</p>