How to Escape Reserved Words in Hibernate's Hql

How to escape reserved words in Hibernate's HQL

You could achieve it by a workaround using your custom "alias to map" transformer, so your code would change to something like this

Query q = mySession.createQuery(
    "SELECT u.id AS id, u.name AS text, u AS obj FROM User u")
    .setResultTransformer(
        AliasToMapTransformer.renameAlias("obj", "object").build()
    );

And then using this class:

public class AliasToMapTransformer extends BasicTransformerAdapter {

    private Map<String, String> renameAliasMap;

    public AliasToMapTransformer(Map<String, String> renameAliasMap) {
        this.renameAliasMap = (renameAliasMap == null) ? Collections.<String, String>emptyMap() : renameAliasMap;
    }

    @Override
    public Object transformTuple(Object[] tuple, String[] aliases) {
        Map<String, Object> result = new HashMap<String, Object>(tuple.length);
        for (int i = 0; i < tuple.length; i++) {
            String alias = aliases[i];
            if (alias != null) {
                String newAlias = renameAliasMap.get(alias);

                result.put((newAlias != null) ? newAlias : alias, tuple[i]);
            }
        }
        return result;
    }

    public static Builder renameAlias(String alias, String newAlias) {
        return new Builder().renameAlias(alias, newAlias);
    }

    public static class Builder {

        private Map<String, String> aliasConversionMap = new HashMap<String, String>();

        public Builder renameAlias(String alias, String newAlias) {
            aliasConversionMap.put(alias, newAlias);
            return this;
        }

        public AliasToMapTransformer build() {
            return new AliasToMapTransformer(aliasConversionMap);
        }
    }
}

How to escape reseved words from HQL

Have you mapped the table to an entity that is also called Order? If so then I'd suggest renaming the entity as there as the Hibernate team appear to have rejected the request to add keyword escaping to HQL. If the problem is actually with the generated SQL then you can add backticks to the table name to escape it according to the database dialect that is in use.

How to escape reserved keywords which are Entity names in JPQL - Hibernate

You cannot use Order as an entity name because it's a reserved keyword in JPQL.

If you don't want to change the name of the class you can change the entity name like this, where "TheOrder" would be the name you want to use.

And you will also need to change the table name because Order is also a reserved keyword in SQL.

@Table(name = "TheOrder")
@Entity(name = "TheOrder")
public class Order

But now you have to use TheOrder instead of Order in your JPQL queries:

List<Tuple> list  = entityManager.createQuery(
            "select a " +
                    "from Table_A a " +
                    "join TheOrder o on a.orderID = o.orderID " +
                    "where o.status = :status ", Tuple.class)
            .setParameter("status", "Completed")
            .getResultList();

In my opinion this will lead to more confusion. So better change the name of the class.

How to escape the keyword on in HQL

@Column
private boolean deviceIsOn;

annotate the field with a renamed variable, and then my method would be isOn.

Bit hacky ... but you keep your nice friendly OO api, with out too much hassle.

Or even simpler, just call it off which isn't reserved

Automatic reserved word escaping for Hibernate tables and columns

AFAIK, Hibernate doesn't maintain a list of reserved keyword (per database) so I think you should look at database identifier escaping.

If you are using Hibernate 3.5+, try hibernate.globally_quoted_identifiers=true to quote all database identifiers (this is something they added for JPA 2.0, see the secion 2.13 Naming of Database Objects of the spec for the JPA way to activate this if you are using JPA).

Prior to version 3.5, Hibernate doesn't offer any configuration option for global escaping. Implementing a custom NamingStrategy to escape everything transparently would be the recommended way.

See also

• Database independant Column/Table name escaping?
• HHH-2578 - redesign SessionFactory building - my understanding is that fixing this issue would make automatic escaping of keywords (through dialects) possible.

Workaround for java hibernate to update row in mySQL tables with reserved words as column names

There are various workarounds, depending on the context:

• For XML mapping files, enclose the keyword with quotes¹.

• For Hibernate annotations, enclose the keyword with quotes².

• You can tell Hibernate to quote all SQL identifiers with:

  hibernate.globally_quoted_identifiers=true
  

  in the Hibernate settings file.

• In HQL, you can escape aliases via a custom transformer; see https://stackoverflow.com/a/5754720/139985 for an example.

References:

• http://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#mapping-quoted-identifiers
• https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/appendices/Configurations.html
• https://www.mkyong.com/hibernate/how-to-use-database-reserved-keyword-in-hibernate/
• How to escape reserved words in Hibernate's HQL

---

^{1 - Different sources say to use database-specific quotes or backticks which Hibernate supposedly knows how to translate. I am not in a position to check this. Both approaches could work.}

^{2 - Apparently double quotes (escaped) and square brackets can be used.}

---

Related Topics