Using COALESCE and logging SQL string in Play Framework 2.x

For future reference…after spending quite some time figuring it out 😛

package models;

import com.avaje.ebean.Model;
import com.avaje.ebean.Query;

import play.Logger;
import play.data.validation.Constraints;

import java.util.Date;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "my_table")
public class MyModel extends Model {
    @Id
    public Long id;

    @Column(name = "name")
    public String name;

    @Constraints.Required
    @Column(name = "created")
    public Date created;

    private static Finder<Long, MyModel> myFinder = new Finder<Long, MyModel>(MyModel.class);

    public static MyModel findByIdName(Long id, String name) {
        Query myQuery = myFinder.where().eq("id", id).raw("COALESCE(name, '') = ?", name).query();
        List myList = myQuery.findList(); // query must be executed first for getGeneratedSql() to work
        Logger.log("SQL: " + myQuery.getGeneratedSql());

        if (myList.size() != 0) {
            return myList.get(0);
        }

        return null;
    }
}