JPA/Hibernate generating wrong SQL in Spring Roo finder method

Question

I'm developing a Spring web application whose persistence layer consists in Spring Roo generated JPA entities, with Hibernate as persistence provider and MySql as underlying DB.

Among my entities I have a class Detection with a tstamp java.util.Date field generated in Roo as follows:

entity jpa --class ~.data.Detection
...
field date --fieldName tstamp --type java.util.Date
...
finder add findDetectionsByTstampBetween

(the finder method was of course chosen after executing finder list)

In my controller code, at a point I invoke:

List<Detection> detections = Detection.findDetectionsByTstampBetween(from, to).getResultList();

Where from and to are two valid java.util.Date(s). When testing sample data though (after ensuring that for a given choice of from, to the returned list shouldn't be empty), I got an empty list and investigated the reasons.

I found in tomcat logs that Hibernate was generating the following SQL:

Hibernate: select detection0_.id as id1_3_, ...etc..., detection0_.tstamp as tstamp4_3_ from detection detection0_ where detection0_.tstamp>=?

I would expect the where clause should contain a trailing "AND detection0_.tstamp<=?", checking the other date range limit. I took a look at the generated Detection.findDetectionsByTstampBetween(Date minTstamp, Date maxTstamp) method in Detection_Roo_Finder.aj and actually the "AND" is present in the invocation to createQuery.

public static TypedQuery<Detection> Detection.findDetectionsByTstampBetween(Date minTstamp, Date maxTstamp) {
        if (minTstamp == null) throw new IllegalArgumentException("The minTstamp argument is required");
        if (maxTstamp == null) throw new IllegalArgumentException("The maxTstamp argument is required");
        EntityManager em = Detection.entityManager();
        TypedQuery<Detection> q = em.createQuery("SELECT o FROM Detection AS o WHERE o.tstamp BETWEEN :minTstamp AND :maxTstamp", Detection.class);
        q.setParameter("minTstamp", minTstamp);
        q.setParameter("maxTstamp", maxTstamp);
        return q;
}

Any idea what could cause the problem?

score 0 · Accepted Answer · answered Apr 15 '15 at 13:03

I've finally found the solution to the riddle and, as it turned out, the issue had nothing to do with JPA.

The problem was that the call to the persistence layer was inserted inside a Rest service controller with the following mapping:

@ResponseBody
@RequestMapping(value="/detections", method=RequestMethod.GET, params="from, to" )
public Object getDetectionsInRange(
        @RequestParam(required=true) @DateTimeFormat(pattern="yyyy-MM-dd HH:mm") final Date from,
        @RequestParam(required=true) @DateTimeFormat(pattern="yyyy-MM-dd HH:mm") final Date to
        ) 
{
    ...
    List<Detection> detections = Detection.findDetectionsByTstampBetween(from, to).getResultList(); 
    ...
}

The error was in the definition of the params= argument in @RequestMapping, the correct format being as follows:

@RequestMapping(value="/detections", method=RequestMethod.GET, params={"from", "to"}  )

This error caused another version of the controller method for /detections. In this second version I called a different finder method, which appeared to generate the wrong SQL in Hibernate.

@ResponseBody
@RequestMapping(value="/detections", method=RequestMethod.GET  )
public Object getDetections(
        @RequestParam(required=false, defaultValue="0") int days,
        @RequestParam(required=false, defaultValue="0") int hours,
        @RequestParam(required=false, defaultValue="0") int minutes
        ) 
{
    ...
    List<Detection> detections = Detection.findDetectionsByTstampGreaterThanEquals( ... ).getResultList(); 
    ...
}

JPA/Hibernate generating wrong SQL in Spring Roo finder method

1 Answers1