Friday, October 31, 2008

Conceptual Queries extension for JPAQL: JPAQL queries are too sensitive to model changes

When a query is written in JPAQL, it still depends to much on the domain model (I think JPAQL could be extended to support Conceptual Queries).

For example, lets say you want to:

Find each white cat that lives in a house with doors made of wood

A cat is of one color, it lives in one house, a house can have 1 door and the door can be made of 1 material

So, the query in JPAQL looks like this:

select c from Cat c where c.color=”white” and c.house.door.material.name = "wood"

But then, lets say our customer changes the requirements:

Find each white cat that lives in a house with doors made of wood

A cat is of one color, it lives in many houses, a each house has many doors and the door is made of one or more materials (for example half glass, half wood)

Since our relationships are now @OneToMany so we write their names in plural, and we are newbies in JPAQL we try doing this (and it, of course does not work):

select c from Cat c Cat c where c.color=”white” and c.houses.doors.materials.name = "wood"

Now, we can of course solve this problem using subqueries and the exists keyword, but that makes the resulting query way more complex, and even if the above worked, it still is a different query, but, in our english example, the query didn’t change:

Find each white cat that lives in a house with doors made of wood

So, why we can not write in JPAQL something like:

select c from Cat c where c with color= “white” and lives in House has Doors made of Material with name = "wood"

That way the query wouldn’t have to change even if the multiplicity of the relationships changed. Of course now the question is, from where do I get the “with”,“lives in”, “has”, “made of” and “with” well, simple:

  • The with operator is available for each non @Entity member of the class (for strings, integers, etc).
  • For relationships with other entities we just add the conceptual name of the relationship name as an attribute in the @OneToMany or @ManyToOne annotations:

Example:

Before:

public class Cat{

@Column

private String color;

@ManyToOne(conceptualName=”lives in ”)

private House houses;

}

After:

public class Cat{

@Column

private String color;

@OneToMany(mappedBy=”cat”, conceptualName=”lives in ”)

private Set<House> houses;

}

This way changes in the object model do not necessarily affect our queries. What do you think? Is this a good solution?

1 comment:

Rafael Ponte said...

It's very interesting!

I've never heard about it. But, have you extended JPAQL/Annotations to support that or it was just your thought?

Well, good post, congratulations.