Play Framework CRUD search issue

Question

when trying a search on some entities in the CRUD module of Play I'm getting this exception:

    play.exceptions.JavaExecutionException: org.hibernate.exception.SQLGrammarException: could not execute query
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:290)
    at Invocation.HTTP Request(Play!)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:250)
    at play.db.jpa.JPAPlugin$JPAModelLoader.fetch(JPAPlugin.java:431)
    at controllers.CRUD$ObjectType.findPage(CRUD.java:253)
    at controllers.CRUD.list(CRUD.java:36)
    at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:413)
    at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:408)
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:182)
    ... 1 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2452)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192)
    at org.hibernate.loader.Loader.list(Loader.java:2187)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:241)
    ... 7 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'CHARSET' in 'where clause'
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
    at org.hibernate.loader.Loader.doQuery(Loader.java:718)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.doList(Loader.java:2449)
    ... 15 more

The odd thing is that search works for some entities and not for others. For example, for the following entity adding any string in the search box works:

   @Entity
public class Act extends Model {

    @Transient
    private static final int PAGE_SIZE = Integer.parseInt(Play.configuration.getProperty("pagination.size","10"));

    @Required(message = "act.name.required")
    public String name;

    @Required(message = "act.description.required")
    @Lob
    @MaxSize(value=500, message="act.description.maxsize")
    public String description;

    public Blob image;

    public boolean showInClosedMode;

    @Temporal(TemporalType.TIMESTAMP)
    public Date updated;

    @Required(message = "act.theatre.required")
    @ManyToOne
    public Theatre theatre;

    public boolean disabled;

    @OneToMany(mappedBy="act")
    public List<Offer> offers;

    @ManyToMany(cascade=CascadeType.PERSIST)
    public Set<Tag> tags;
    @Transient
    public String tagListSupport;

    [... some methods ...]
    }

But not for this one:

@Entity
public class User extends Model {

    @Required(message = "user.name.required")
    public String name;
    @Email(message = "user.email.invalid")
    public String email;
    public String prefLang;
    public Long prefCity;
    public int credits;
    public Date lastLogin;
    @NoBinding("profile")
    public boolean disabled;
    @NoBinding("profile")
    public boolean admin;
    @NoBinding("profile")
    public boolean anonymous;
    @OneToMany(mappedBy = "owner")
    public List<Ticket> tickets;
    @ManyToMany
    public List<Theatre> theatres;
    public String googleId;
    public String yahooId;
    public String facebookId;
    public String twitterId;
    public String twitter_token;
    public String twitter_secret;
    public String username;
    public String password;
    @OneToMany(mappedBy = "user")
    public List<Event> history;

    [...Methods...]
}

Any idea why this happens?

Answer 1

Be careful with column names.

2012-08-08T12:58:29+00:00 app[web.1]: 12:58:29,513 ERROR ~ Unsuccessful: create table users (id int8 not null, email varchar(255), enabled bool not null, name varchar(255), user varchar(255), primary key (id))
2012-08-08T12:58:29+00:00 app[web.1]: 12:58:29,514 ERROR ~ ERROR: syntax error at or near "user"

That was my case, changing column "user" to "userId" solved my issue

Answer 2

My guess is the database you are using does not like you having a table named "user" which is what you get when you don't provide a specific name for your table. I know that Postgres does not allow for a "user" table because "user" is a keyword. I'm not sure for MySQL. Try adding the javax.persistence.Table annotation after your @Entity annotation in the User class:

@Entity
@Table(name = "my_user")
public class User extends Model {
    ...
}

where you give it a whatever name you want that is not "user". Alternatively (but not tested) you may be able wrap the "user" name in quotes:

@Entity
@Table(name = "\"user\"")
public class User extends Model {
    ...
}