Marco Valtas

This week I was writing a tool to extract reports from Mantis Bugtrack using JPA and EJB3. Since I´m not good in JPQL (sonething like a SQL for objects) the search for examples began. The problem was that all examples were too simple, here´s some notes one a more complex query and how was done.

Mantis is a bugtrack tool and allows a bug relationship and custom fields, my report will make use of both. Below is a simplified diagram of the database tables involved in this process.

The scenario was 2 projects. The first (I will call it A) will open bugs for the second (from now called B) the bugs will be related. Example: A operator opens a bug in project A, after that n other bugs are opened in project B, the relation between bugs is B are child of A. This relation is explicitly set using Mantis relationship features which will be reflected in our bug_relationship table. The last thing on this problem is that a custom field value should be retrieved too for a pivot transformation. Here's another diagram showing the relationship:

To not extend too much this post I will show a simplified version of the mapped entities. Gets, Sets, packages and imports were omitted for the sake of the reader.

@Entity
@Table(name="mantis_project_table")
public class Project implements Serializable {
 @Id
 private int id;

 private String name;

 @OneToMany(mappedBy="project")
 private Collection<bug> bugs;

    // gets and sets omitted.
}

@Entity
@Table(name="mantis_bug_table")
public class Bug implements Serializable {
 @Id
 private int id;

 @Column(name="project_id",insertable=false,updatable=false)
 private int projectId;

 @ManyToOne
 private Project project;

      //gets and sets omitted
}

public enum BugRelationType {
 DUPLICATED(0),
 RELATED(1),
 DEPENDANT(2);

 private final int relationship;

 BugRelationType(int rel) {
  this.relationship = rel;
 }

 public Integer toInteger() {
  return Integer.valueOf(this.relationship);
 }

}

@Entity
@Table(name="mantis_custom_field_table")
public class CustomField implements Serializable {

 @Id
 private int id;

 private String name;

      // gets and sets omitted

}

@Entity
@Table(name="mantis_custom_field_string_table")
public class CustomFieldString implements Serializable {

 @EmbeddedId
 private CustomFieldStringPK pk;

 private String value;
}

@Embeddable
public class CustomFieldStringPK implements Serializable {

 private static final long serialVersionUID = 1L;

 @Column(name="field_id")
 private Integer fieldId;

 @Column(name="bug_id")
 private Integer bugId;
}

Alright, with those entities in hand how we will compose a good JPQL without access the database more than one time? It's possible? Is possible to join all these entities? Those were my questions. The answer is affirmative, but to do so is not straight forward (at least for me) like when I'm writing SQL.

Here's is the final query:

    SELECT
        bt, cs.value
    FROM
        CustomFieldString cs,
        CustomField cf,
        Project ps, IN (ps.bugs) bs,
        Project pt, IN (pt.bugs) bt,
        BugRelationship rel
    WHERE
        ps = :projSource
        AND pt = :projTarget
        AND rel.sourceBugId = bs.id
        AND rel.destinationBugId = bt.id
        AND cs.pk.bugId   = bs.id
        AND cs.pk.fieldId = cf.id
        AND cf = :customField
        AND rel.relationshipType = :relType

Now just fill up the named parameters and you have it, a fairly complex JPQL query.

That's it.