How do I do select in list with Ebean createSqlQuery

Question

I'm building a Play2 app with Ebean. I have created a service class with a method for getting venues by a list of ids:

public static List<Venue> getVenuesForIds(List<Long> list){          
    ArrayList<Venue> venues = new ArrayList<Venue>();
    String sql = "select c.id, c.name from Company c where in (:ids)";
    List<SqlRow> sqlRows =
                Ebean.createSqlQuery(sql).setParameter("ids", list).findList();        
    for(SqlRow row : sqlRows) {
        venues.add(new Venue(row.getLong("id"), row.getString("name")));
    }        
    return venues;
}

But I'm getting:

[PersistenceException: Query threw SQLException: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 'in (201639091,201637666)' at line 1 Query was: select c.id, c.name from Company c where in (?,?) ]

I have read through http://www.avaje.org/ebean/introquery.html but probably missed the correct syntax. I want to do this in raw sql. What have I missed?

Just curious: `Venue` is a model which table's name is `Company` or you are selecting with it objects of other model? — biesior, Dec 10 '12 at 16:45
Hello! I'm accessing the database from a new app where venue is more appropriate name for the entity. — jakob, Dec 10 '12 at 17:12

score 3 · Accepted Answer · answered Dec 10 '12 at 15:16

3

Your request does not seem correct.

What about :

 "select c.id, c.name from Company c where c.id in (:ids)";

answered Dec 10 '12 at 15:16

Arnaud Gourlay

4,646
1
29
35

score 3 · Answer 2 · answered Dec 10 '12 at 18:02

You don't need to perform such 'sophisticated' query, it will be good enough if you'll use common Finder<I,T> in your Venue model (once):

@Entity
@Table(name = "Company")
public class Venue extends Model {

    @Id
    public Long id;
    public String name;
    // other fields

    public static Finder<Long, Venue> find
            = new Finder<Long, Venue>(Long.class, Venue.class);
}

So then you can do the same task with... single line of code in your method:

 public static List<Venue> getVenuesForIds(List<Long> ids){          
     return Venue.find.select("id,name").where().idIn(ids).findList();
 }

or with similar expresion:

 public static List<Venue> getVenuesForIds(List<Long> ids){          
     return Venue.find.select("id,name").where().in("id",ids).findList();
 }

Thank you and your are of course correct! But in this case I want to use raw sql since I will perform joins later on and I do not want to create the entities for that in my code. — jakob, Dec 11 '12 at 07:50

How do I do select in list with Ebean createSqlQuery

2 Answers2