All geek questions in one place

Problem with CRMS Search Engine

when trying to search on some objects in the CRUD module in Play. I get 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

It is strange that the search works for some objects, and not for others. For example, for the following object that adds any line to the search field, it 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:

 @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 is happening?

+4
source share
2 answers

My guess is that the database you are using does not look like you have a table called "user" that you get when you don't specify your name for your table. I know that Postgres does not allow a "user" table, because "user" is a keyword. I am not sure about MySQL. Try adding the javax.persistence.Table annotation after the @Entity annotation in the User class:

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

where you give him whatever name you want, not "user". Alternatively (but not verified), you can wrap the user name in quotation marks:

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

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"

This was my case, changing the user column to userId solved my problem

+1
source

Source: https://habr.com/ru/post/1347634/

More articles:


All Articles