1

I try to build a findAll query for a class with name User. With Hibernate's SessionFactory it is working fine, but with Hibernate's EntityManager I get an error.

The reason seems to be the escaping (with brackets [and ]) of the MS SQL Server keyword user, but with EntityManager#persist(Object) and EntityManager#find(Class, Object) I have no problems.

Stacktrace:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: [ near line 1, column 29 [select generatedAlias0 from [user] as generatedAlias0]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:131)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:93)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1836)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:568)
    ... 103 more

Code:

@Entity(name = "[user]")
public class User {
    // some properties
}

public class UserDao

    @PersistenceContext
    private EntityManager entityManager;

    public List<User> findAll() {
        CriteriaQuery<User> criteriaQuery = entityManager.getCriteriaBuilder().createQuery(User.class);
        Root<User> root = criteriaQuery.from(User.class);
        criteriaQuery.select(root);
        return entityManager.createQuery(criteriaQuery).getResultList();
    }
}

Research:

These solutions are not working, too. Is there any way to use table name [user] in a JPA query with Hibernate?

Community
  • 1
  • 1
dur
  • 15,689
  • 25
  • 79
  • 125

3 Answers3

2

I found a solution (https://stackoverflow.com/a/3217488/5277820) based on JPA 2 spec with @Table:

2.13 Naming of Database Objects

[...]

To specify delimited identifiers, one of the following approaches must be used:

  • It is possible to specify that all database identifiers in use for a persistence unit be treated as delimited identifiers by specifying the <delimited-identifiers/> element within the persistence-unit-defaults element of the object/relational xml mapping file. If the <delimited-identifiers/> element is specified, it cannot be overridden.

  • It is possible to specify on a per-name basis that a name for a database object is to be interpreted as a delimited identifier as follows:

    • Using annotations, a name is specified as a delimited identifier by enclosing the name within double quotes, whereby the inner quotes are escaped, e.g., @Table(name="\"customer\"").
    • When using XML, a name is specified as a delimited identifier by use of double quotes, e.g., <table name="&quot;customer&quot;"/>

Code:

@Entity
@Table(name = "[user]")
public class User {
    // some properties
}
Community
  • 1
  • 1
dur
  • 15,689
  • 25
  • 79
  • 125
1

The table name (@Table) is the name of the table in the database. The characters which are allowed in the name depends on the database (SQL Server, Oracle, MySQL, etc.).

The entity name (@Entity) is used by JPA and Hibernate to refer to the entity. The name must comply with the allowed characters for Java identifiers, so it cannot contain [ or ]. When Hibernate tries to translate the HQL query into SQL, the parser fails with the unexpected token error because it doesn't support [ or ] in an entity name.

The Java EE 6 Tutorial explains what is allowed or not in an entity name.

For the reason why it doesn't fail for the EntityManager methods, it's because Hibernate doesn't use a regular HQL query when the EntityManager methods are called. The SQL for persist and find is generated when Hibernate is started, so the HQL parser is not used for these operations. I suppose that a validation is missing, Hibernate should give an error when it starts if an entity is invalid instead of failing at runtime in specific cases.

Marc-André
  • 846
  • 6
  • 8
-1

Please try this instead

@Entity(name = "'user'")

or

@Entity("\"user\"")
suulisin
  • 1,414
  • 1
  • 10
  • 17