Problem with Hibernate

Recently I wanted to execute a fairly straightforward series of SQL calls (column Ids masked) using JPA's EntityManager.createNativeQuery. Running the following sequence of calls:

entityManager.createNativeQuery("set @rank:=0;").executeUpdate();
entityManager.createNativeQuery("select rank from (select @rank:=@rank+1 as rank, XX, YY from ZZ order by YY DESC) as ranks where XX=a;").getResultList();

yielded an unpleasant exception.

java.lang.IllegalArgumentException: org.hibernate.QueryException: Space is not allowed after parameter prefix ':' 'set @rank:=0;'

A quick Google search revealed that this is actually an open issue in Hibernate.
I followed in the footsteps of one of the other posters and tried to create a stored procedure to run my two statements. The procedure worked fine in MySQL Query Browser and yielded the correct results. However, when I executed it as a JPA nativeQuery I got:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 2' at line 1

For some reason, "limit 2" was always being appended to my call to the stored procedure. The final solution was to create a new function instead of a procedure:

DELIMITER $$
DROP FUNCTION IF EXISTS `football`.`getRank` $$
CREATE FUNCTION `getRank`(p1 varchar(256)) RETURNS int(11)
BEGIN
    set @rank:=0;
    RETURN (select rank from (select @rank:=@rank+1 as rank, XX, YY from ZZ order by YY DESC) as ranks where XX=p1);
   END $$
DELIMITER ;

And call it from my DAO with:

Query query = entityManager.createNativeQuery( "select getRank(?1)" ).setParameter( 1, teamId );
return (Integer)query.getSingleResult();

IntelliJ Live Templates - For Each over a Collection

As a recent convert to IntelliJ, I'm doing my best to take full advantage of the IDE's time saving measures. I thought that IntelliJ was missing a Live Template that creates a for each control block over a given collection. Here is how I created this Live Template:

1. Open Settings (Command-,)
2. Select Live Templates and click the "Add" button
3. Complete the dialog fields

4. Click "Edit variables" and complete the dialog

To use this template, simply type Command-J in your editor and begin typing "feco" until the template name is highlighted in your list. Press TAB to accept the template and fill in the values that you need.

--Update--
It turns out that there is already a template to do this identified by "iter" (thanks yole). Fortunately the exercise was useful in getting started with writing my own templates. I've since written one for creating a standard TestNG test method that includes the "groups" parameter for the @Test annotation. My team organizes our tests into groups so that we can execute different tests in different levels of our builds. Tests in the group "unit" are run as part of our commit build, while tests in other groups are run as part of our integration test build.

Here's the template defintion:


And the variable assignments:
.