My Aggregated Life

Today I needed to write a way to calculate some statistics and group them by the age of the person who is attached to the data. In SQL it’s pretty simple…

   SELECT count(t.*)
        , extract('year' from age(s.birthday))
     FROM students s
     JOIN tests t ON (s.student_id = t.student_id)
    WHERE t.grade = 'A'
 GROUP BY extract('year' from age(s.birthday))

But, I wanted to do this as a criteria query. As far as I can tell, the only completely database agnostic way to do it is to do the calculations in code since the age and extract functions aren’t 100% supported. But, in my case, I’m not too concerned about that. So, here’s the same query in JPA2:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery query = builder.createQuery(Tuple.class);

Root root = query.from(Test.class);
Join students = root.join("student");

Expression ageExpression = builder.function("year", Integer.class, builder.function("age", String.class, students.get("birthday")));

query.select(builder.tuple(builder.count(root), ageExpression));
query.where(builder.equal(root.get("grade"), "A"));
query.groupBy(ageExpression);

List results = em.createQuery(query).getResultList();

for (Tuple result : results)
{
	Long count = result.get(0, Long.class);
	Integer age = result.get(1, Integer.class);
}

As you can see, it’s not too difficult. Using the builder.function() call you can call age and wrap that in this year extract. Simple, but not exactly intuitive.

Recently I ran into an issue where I had a parent-child relationship between to objects, something like this…

class Parent()
{
    @OneToMany(mappedBy="parent")
    private List children;
}

class Child()
{
    @ManyToOne
    @JoinColumn(name="parent_id", referencedColumnName="parent_id", insertable=true, updatable=true)
    private Parent parent;
}

Under most situations I could work with each of these beans independently or together without much trouble. However, I found that if I removed a child from the ORM layer then tried to save the parent…

public String save()
{
    for (Child child : parent.children())
    {
        if (childrenToRemove.contains(child))
        {
            childService.remove(child);
        }
    }

    parentService.save(parent);
}

…it would fail on the parent object save…

javax.persistence.EntityNotFoundException: Unable to find com.my.package.Parent with id 1234

Personally, I think this is ridiculous, because the relationship is owned by the child so why isn’t it smart enough to let the parent save without caring about the children? Anyway, as it turned out the solution was very very simple…

public String save()
{
    for (Child child : parent.children())
    {
        if (childrenToRemove.contains(child))
        {
            parent.getChildren().remove(child);
            childService.remove(child);
        }
    }

    parentService.save(parent);
}

Again, I don’t really understand this fails the way it does, but there you go. Too much code coupling for my tastes. :)

Does anyone know of a way to do this via config?

I spend many many hours, maybe most of my day, searching the web, consuming information from it, and basically contributing little to this little global information sharing thing we’ve created. It’s not a good thing. I have information I could share, we all do. So, my new plan is to post something, anything, once a day to this here little tumblr thing in hopes that maybe it will be of use (even if it’s just a simple chuckle) to someone out there in the world. We’ll see how long this lasts…