1

I want to map one column, without using a column name.

I am using a count entity, and the want to use mulple different queries with the same entity : 

@Entity
public class CountDTO extends Number {

    @Id
    // below causes an error in my test, hsql not same syntax
    @Column(name = 'COUNT') 
    private Long count;

In my prod (oracle) database I can just do select count() as COUNT from ... however, the same syntax doesn't work using hypersql in-memory db ?

Is their an oracle/hsql compatible way to map a single column alias in HQL ?

NimChimpsky
  • 46,453
  • 60
  • 198
  • 311

1 Answers1

3

Your issue is that COUNT is a reserved keyword for HSQL, but not for Oracle.

According to the HSQL documentation, it might still be possible to use COUNT as identifier, if you either

  • Mask it as described in the Hibernate documentation or in the JPA spec (cf. chapter 2.13 from the JPA 2 spec; you'll need to accept their license agreement). Note that the JPA spec speaks of double quotes whereas the Hibernate documentation mentions backticks (which will be converted to the appropriate character according to the database dialect in use).

    From the hibernate documentation:

    You can force Hibernate to quote an identifier in the generated SQL by enclosing the table or column name in backticks in the mapping document. Hibernate will use the correct quotation style for the SQL Dialect. This is usually double quotes, but the SQL Server uses brackets and MySQL uses backticks.

    From the JPA 2 spec:

    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\"").

  • Configure HSQL to allow it by executing SET DATABASE SQL NAMES FALSE (however, this should already be the default setting and it will only allow "the use of most keywords", not all - edit: COUNT will still be disallowed as per documentation)

My recommendation would be to avoid using identifiers if possible as you never know what problems may arise elsewhere (e.g. one might think Hibernate would be able to mask keywords itself) and use something like COUNT1 instead as column name.

The above part of the JPA spec also explains why Hibernate does not mask the name itself:

By default, the names of database objects must be treated as undelimited identifiers and passed to the database as such.

The JPA spec also mentions a <delimited-identifiers/> option "to specify that all database identifiers in use for a persistence unit be treated as delimited identifiers", but this seems to be only usable with an XML mapping file.

Marvin
  • 13,325
  • 3
  • 51
  • 57
  • 1
    How to mask keywords as column name is defined in JPA (this applies not only to Hibernate): `@Column(name = "\"COUNT\"")` – Tobias Liefke Jul 06 '15 at 19:47
  • As I read your citation of the spec, the correct mask seems to be ``@Column(name = "`COUNT`")`` - `"\"COUNT\""` always worked for me, because I've never used that with MySQL or MS SQL. – Tobias Liefke Jul 07 '15 at 08:09
  • @TobiasLiefke: The JPA 2 spec says "double quotes", the Hibernate documentation says "backticks, which are converted to the appropriate database specific character". So I think your first comment already was correct regarding the JPA. Where exactly did you find anything regarding backticks in the spec? – Marvin Jul 07 '15 at 08:45
  • I just read your citation - but didn't read the full update. I didn't know that there is a difference between Hibernate and JPA here. Thanks for pointing that out. – Tobias Liefke Jul 07 '15 at 08:49
  • @TobiasLiefke: I see, this was a bit misleading. I updated my answer again and tried to make it (especially the difference) more clear. – Marvin Jul 07 '15 at 09:07