H2 Database Column Name "Group" Is a Reserved Word

How to map an entity field whose name is a reserved word in JPA

Had the same problem, but with a tablename called Transaction. If you set

hibernate.globally_quoted_identifiers=true

Then all database identifiers will be quoted.

Found my answer here
Special character in table name hibernate giving error

And found all available settings here
https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/appendices/Configurations.html

Could not find better docs for this though.

In my case the setting was in my Spring properties file. As mentioned in the comments, it could also be in other, hibernate related, configuration files.

org.h2.jdbc.JdbcSQLSyntaxErrorException after H2 version upgrade

Why do you have such definitions? Documentation of H2 1.4.200 doesn't allow any parameters for these data types.

INT(11) is allowed only in MySQL and MariaDB compatibility modes, but the specified precision is ignored by H2. This definition is rejected in all other compatibility modes in H2 2.0, you need to use INT or INTEGER.

BOOLEAN(1) is not allowed at all, if it worked in 1.4.200, it was a bug in the parser. You need to use BOOLEAN.

AUTO_INCREMENT clause also should normally be used only in MySQL and MariaDB compatibility modes, but it works in Regular mode too. The proper clause is GENERATED BY DEFAULT AS IDENTITY and explicit NOT NULL constraint isn't required for primary key and identity columns, you can remove it. Constraints also should normally be specified after all other clauses, NOT NULL before identity options is actually accepted by H2, but this wrong order of clauses isn't documented and isn't supported.

How to add reserved keywords in liquibase OracleDatabase?

Hmm in case of Oracle you have keywords and reserved words.

• reserved words can not be used as identifiers
• keywords can be used as identifiers but it is not recommened.

You can get list of them directly from database:

select KEYWORD, RESERVED from v$reserved_words;
...
1864 rows selected

What about using uppercase names everywhere in the source code?

It looks like Liqubase depends on some JDBC driver functionally - which does not work.

OracleDatabase.java:

public void setConnection(DatabaseConnection conn) {
    //noinspection HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,HardCodedStringLiteral,
    // HardCodedStringLiteral
    reservedWords.addAll(Arrays.asList("GROUP", "USER", "SESSION", "PASSWORD", "RESOURCE", "START", "SIZE", "UID", "DESC", "ORDER")); //more reserved words not returned by driver

    Connection sqlConn = null;
    if (!(conn instanceof OfflineConnection)) {
        try {
            /*
             * Don't try to call getWrappedConnection if the conn instance is
             * is not a JdbcConnection. This happens for OfflineConnection.
             * see https://liquibase.jira.com/browse/CORE-2192
             */
            if (conn instanceof JdbcConnection) {
                sqlConn = ((JdbcConnection) conn).getWrappedConnection();
            }
        } catch (Exception e) {
            throw new UnexpectedLiquibaseException(e);
        }

        if (sqlConn != null) {
            tryProxySession(conn.getURL(), sqlConn);

            try {
                //noinspection HardCodedStringLiteral
                reservedWords.addAll(Arrays.asList(sqlConn.getMetaData().getSQLKeywords().toUpperCase().split(",\\s*")));
            } catch (SQLException e) {
                //noinspection HardCodedStringLiteral
                Scope.getCurrentScope().getLog(getClass()).info("Could get sql keywords on OracleDatabase: " + e.getMessage());
                //can not get keywords. Continue on
            }

If Liquibase calls sqlConn.getMetaData().getSQLKeywords() and this does not return proper output, then your chances are limited. It might be a bug in JDBC drivers, or your application does not have SELECT_CATALOG_ROLE privilege and does not see v$reserved_words view (if JDBC queries this internally).

hibernate h2 embeddable list expected identifier

Turns out I was being dumb and named a column "Order". Wonder why H2 wasn't happy :upside_down:

Changed the variable name to something else and it worked!

Spring SQL: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement ; expected identifier , when using INSERT INTO

You named your table user which is a reserved keyword in H2. It's also a reserved keyword in the ANSI SQL-99 standard and often in other SQL implementations (sometimes it is a non-reserved keyword, for example in MySQL).

You can use reserved keywords as table names in SQL if you delimit them. H2 supports standard identifier delimiters, which are double-quotes.

I don't know if there's an easy way to make Spring delimit the identifiers in SQL statements. I recall it's pretty wonky. You have to define the entity with built-in double-quotes around its name, like this:

@Entity  
@Table(name = "\"user\"")

See https://www.chrouki.com/posts/escape-sql-reserved-keywords-jpa-hibernate/

It's easier if you can just avoid using reserved words for your table names (or other identifiers, including columns, procedures, views, indexes, partitions, etc.).

---

Related Topics