Syntax error in sql statement expected identifier sql statement - Исправление ошибок и поиск оптимальных решений проблем

Содержание

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement «Create. «; expected «identifier»; #3363
Comments
Cannot use «TABLE» as a column identifier anymore in CREATE TABLE #1719
Comments

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement «Create. «; expected «identifier»; #3363

This problem only shows up in 2.0.x versions

We are using «spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect».
When upgrading h2 version to 2.0.206 from maven repo, we got the following exception from h2 engine. If we take the same sql statement, it can be executed successfully in our MySQL DB.

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement «CREATE TABLE XYZ (ID BIGINT NOT NULL AUTO_INCREMENT, JOB_ID BIGINT, MACHINE_NAME VARCHAR(255), NAMESPACE_ID BIGINT, RESERVE_TIMESTAMP DATETIME, RETURN_TIMESTAMP DATETIME, USER[*] VARCHAR(255), PRIMARY KEY (ID)) ENGINE=MYISAM»; expected «identifier»; SQL statement:
create table xyz (id bigint not null auto_increment, job_id bigint, machine_name varchar(255), namespace_id bigint, reserve_timestamp datetime, return_timestamp datetime, user varchar(255), primary key (id)) engine=MyISAM [42001-206]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:521)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
at org.h2.message.DbException.getSyntaxError(DbException.java:265)
at org.h2.command.Parser.readIdentifier(Parser.java:6080)
at org.h2.command.Parser.parseTableColumnDefinition(Parser.java:10400)
at org.h2.command.Parser.parseCreateTable(Parser.java:10343)
at org.h2.command.Parser.parseCreate(Parser.java:7811)
at org.h2.command.Parser.parsePrepared(Parser.java:1140)
at org.h2.command.Parser.parse(Parser.java:1068)
at org.h2.command.Parser.parse(Parser.java:1037)
at org.h2.command.Parser.prepareCommand(Parser.java:965)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:624)
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:562)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:237)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
. 99 common frames omitted

Later on, we tried with “spring.jpa.database-platform=org.hibernate.dialect.H2Dialect” . It generates a different sql statement but the error stack is identical.

create table xyz (id bigint generated by default as identity, job_id bigint, machine_name varchar(255), namespace_id bigint, reserve_timestamp timestamp, return_timestamp timestamp, user varchar(255), primary key (id))
[42001-206]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:521)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:496)
at org.h2.message.DbException.getSyntaxError(DbException.java:265)
at org.h2.command.Parser.readIdentifier(Parser.java:6080)
at org.h2.command.Parser.parseTableColumnDefinition(Parser.java:10400)
at org.h2.command.Parser.parseCreateTable(Parser.java:10343)
at org.h2.command.Parser.parseCreate(Parser.java:7811)
at org.h2.command.Parser.parsePrepared(Parser.java:1140)
at org.h2.command.Parser.parse(Parser.java:1068)
at org.h2.command.Parser.parse(Parser.java:1037)
at org.h2.command.Parser.prepareCommand(Parser.java:965)
at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:624)
at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:562)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:237)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:223)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
. 99 common frames omitted

All of our unit tests failed the same way. Anything we can do to get around this failure? Thank you!

The text was updated successfully, but these errors were encountered:

Источник

Cannot use «TABLE» as a column identifier anymore in CREATE TABLE #1719

This works with H2 1.4.197:

It no longer works on 1.4.198 as built from github (11f0020). I’m now getting:

The workaround is obviously to quote the identifier:

But it shouldn’t be needed, IMO

The text was updated successfully, but these errors were encountered:

The same is true for VALUES :

There are some new keywords. The whole list is here:

Lines 478 to 485 in 11f0020

ALL, ARRAY, CASE, CHECK, CONSTRAINT, CROSS, CURRENT_DATE,

CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, DISTINCT, EXCEPT,

EXISTS, FALSE, FETCH, FOR, FOREIGN, FROM, FULL, GROUP, HAVING,

IF, INNER, INTERSECT, INTERSECTS, INTERVAL, IS, JOIN, LIKE,

LIMIT, LOCALTIME, LOCALTIMESTAMP, MINUS, NATURAL, NOT, NULL,

OFFSET, ON, ORDER, PRIMARY, QUALIFY, ROW, ROWNUM, SELECT,

SYSDATE, SYSTIME, SYSTIMESTAMP, TABLE, TODAY, TOP, TRUE, UNION,

UNIQUE, VALUES, WHERE, WINDOW, WITH

TABLE keyword was introduced with support of explicit table statement.
VALUES keyword was introduced with better support of table value constructor.

They both are needed to ensure correct parsing of such constructions. It’s very hard to avoid them. Both TABLE and VALUES are reserved words in the SQL Standard. I don’t see any good reason to introduce a lot of tricks in Parser for their usage as identifiers.

H2 accepts keywords as identifiers only in metadata of existing tables that were created by older versions of H2.

Hmm, there are different ways on how to go about this. Certain tokens («keywords») can appear only in certain locations. Forbidding general usage of keywords is the easy solution for parser maintainers, as well as for specs authors. But in terms of interoperability between databases, it may be useful to accept keywords as identifiers where they cannot be a keyword. For example, in DDL, most of the listed keywords can really only be identifiers.

RDBMS all behave very differently here, but I think it would be a useful feature for the H2 parser to be as lenient as possible in this area. Having written a parser myself, I found it not too difficult to give precedence to keywords where they apply, and otherwise interpret them as identifiers.

It’s a tradeoff, of course.

Because both TABLE and VALUES can be used as a queries and there are some places when parser need to perform forward searches for queries in not yet parsed data I don’t see a good way to avoid such keywords.

I haven’t studied your implementation in detail, but if a parser is aware of context, then it might know the next token must be an identifier, because that’s the only thing that is allowed. For example, in DDL: The thing that can be located where the column identifier is located can only be an identifier.

We can allow any or almost any identifiers only in some places. But some people may decide that some reserved word is a valid identifier everywhere.

Even in our DDL names of tables and columns are located in places where some other constructions can be used, such as IF NOT EXISTS , CONSTRAINT , may be something else.

This change breaks stuff for us as well starting in release .198. Why aren’t you using semantic versioning? There are lots of breaking changes in patch releases in H2.

It’s not a patch release, it’s a big release with many changes in different areas. Releases of H2 are numbered sequentially. Transitions from 1.X.*** to 1.Y.*** usually mean incompatible changes in on-disk storage format. However, 1.4.*** releases still support old PageStore backend, but it’s not the default one.

Do not use 1.4.198, BTW, use 1.4.199, it contains fixes for different regressions in 1.4.198.

Источник

Я работаю над веб-приложением Spring.
Я использую базу данных mysql, но для своих модульных тестов я хочу запускать их в базе данных H2.

Протестируйте определенные свойства приложения:

#Specific spring boot configuration for tests
 spring.main.banner-mode=off
 spring.datasource.driverClassName=org.h2.Driver
 spring.datasource.url=jdbc:h2:mem:skel;MODE=MYSQL
 spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
 spring.datasource.user=sa
 spring.datasource.password=
 spring.jpa.hibernate.ddl-auto=create
 spring.jpa.defer-datasource-initialization=true
 endpoints.enabled=false
 # enable test-profile
 spring.profiles.active=test

Как видите, моя база данных находится в режиме MODE=MYSQL, поскольку мой файл data.sql написан на диалекте MySQL.

Но во время инициализации data.sql я получаю эту ошибку:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement 
"INSERT IGNORE INTO [*]user (username, password, first_name, last_name, enabled, 
news_cycle, created_at, updated_at) VALUES('admin@example.com', 
'$2a$10$BFNo8gUTorQMtikcFbYVEeAPyX5iCn5BpKglp.eJ2DrFs.bNeXgEu', 'Admin', 'Adminoso', 
'TRUE', 'NEVER', '2016-01-01 00:00:00', '2016-01-01 00:00:00')"; expected 
"identifier"; SQL statement:
 INSERT IGNORE INTO user (username, password, first_name, last_name, enabled, 
 news_cycle, created_at, updated_at) VALUES('admin@example.com', 
 '$2a$10$BFNo8gUTorQMtikcFbYVEeAPyX5iCn5BpKglp.eJ2DrFs.bNeXgEu', 'Admin', 'Adminoso', 
 'TRUE', 'NEVER', '2016-01-01 00:00:00', '2016-01-01 00:00:00') [42001-212]

Я предполагаю, что из этой ошибки вы можете увидеть оператор SQL, который вызывает проблему, другие операторы в data.sql, похоже, не вызывают проблем. Например:

INSERT IGNORE INTO department (department_id, department_name, created_at, 
updated_at) VALUES (1, 'Marketing', '2016-01-01 00:00:00', '2016-01-01 00:00:00');

Моя пользовательская сущность:


import at.qe.skeleton.model.facility.Department;
import at.qe.skeleton.model.facility.Room;
import lombok.Getter;
import lombok.Setter;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.time.LocalDateTime;
import java.util.Objects;
import java.util.Set;

/**
 * Entity representing users.
 *
 * This class is part of the skeleton project provided for students of the
 * courses "Software Architecture" and "Software Engineering" offered by the
 * University of Innsbruck.
 */
@Getter
@Setter
@Entity
@EntityListeners(AuditingEntityListener.class)
public class User implements Comparable<User>{

    @Id
    @Column(name = "username", length = 255)
    private String username;

    @Column(name = "password",nullable = false,length = 255)
    private String password;

    @Column(name = "first_Name",nullable = false,length = 255)
    private String firstName;

    @Column(name = "last_Name",nullable = false,length = 255)
    private String lastName;

    @Column(name = "enabled", nullable = false)
    private boolean enabled;

    @Column(name = "news_cycle", nullable = false)
    @Enumerated(EnumType.STRING)
    private NewsletterCycle newsCycle;

    @ElementCollection(targetClass = UserRole.class, fetch = FetchType.EAGER)
    @CollectionTable(name = "user_roles")
    @Enumerated(EnumType.STRING)
    private Set<UserRole> roles;

    @ManyToOne
    @JoinColumn(name = "department_id")
    private Department department;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "manages_department_id", referencedColumnName = "department_id")
    private Department managingDepartment;

    @ManyToOne
    @JoinColumn(name = "assigned_room_id")
    private Room assignedRoom;

    @OneToMany(mappedBy = "user", cascade = CascadeType.REMOVE, fetch = FetchType.EAGER)
    private Set<Absence> absences;

    @CreatedDate
    @Column(name = "created_at", nullable = false)
    private LocalDateTime created;

    @LastModifiedDate
    @Column(name = "updated_at", nullable = false)
    private LocalDateTime updated;

    @Override
    public int compareTo(User o) {
        return this.username.compareTo(o.getUsername());
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 59 * hash + Objects.hashCode(this.getUsername());
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        }
        if (!(obj instanceof User)) {
            return false;
        }
        final User other = (User) obj;
        return Objects.equals(this.username, other.getUsername());
    }

    @Override
    public String toString() {
        return "username: " + getUsername() + " name: " + getFirstName() + " " + getLastName();
    }
}

Что означает эта синтаксическая ошибка SQL «ожидаемый идентификатор» в данном случае, я не могу понять.

Я попытался сделать все поля, кроме имени пользователя, в user nullable, а затем попробовал тот же оператор, только вставив имя пользователя, подозревая, что, возможно, проблема была в каком-то типе данных временной метки, но это ничего не изменило.

Я надеюсь, что кто-то может помочь спасибо!

Источник

H2 DB is an open source lightweight Database written in Java. It ships in JBoss EAP and WildFly application server to speed up developing and testing Java applications. Let’s have a look to the main configuration options and how to create an example application using the H2 DB.

What is H2 database?

Firstly, let’s see which are the main features of this Database:

It is an Opensource Java Database
It has an extremely fast DB engine.
Supports standard SQL and JDBC API.
You can use it in embedded mode, within in your Java Process, or in Server mode.
Supports clustering and multi-version concurrency.

H2 Database can be started as standalone Java process or it can be run embedded in an existing Java Process. You would typically use it in embedded mode on application servers like WildFly / JBoss EAP.

The latest version of H2 Database (February 2023) is the following one: 2.1.214

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version></version>
    <scope>test</scope>
</dependency>

You can run the H2 Database in three different modes:

1. Server mode:

jdbc:h2:tcp://localhost/~/test

When using H2 db in server mode (also known as client/server mode) all data is transferred over TCP/IP. Before application can use H2 Database in server mode, you need to start the H2 DB within the same or another machine.

2. Embedded mode:

jdbc:h2:~/test

H2 db in embedded mode will be faster but the downside of it is that no other process can access the Database. In the above connection string, the Data will be saved into the ‘test’ folder, under the user’s home directory.

3. Mixed mode:

The mixed mode combines some features of the embedded and the server mode. The first application connecting to the H2 db does that in embedded mode, but at the same time it starts a server so that other applications can concurrently access the same data, even from different processes.

jdbc:h2:/data/test;AUTO_SERVER=TRUE

When using automatic mixed mode, you can share the JDBC URL for all applications using the DB. By default the server uses any free TCP port. The port can be set manually using AUTO_SERVER_PORT=9090.

Running H2 DB in Server mode

To run H2 Database in Server Mode you need the JAR file containing the DB Classes. You can download it from http://www.h2database.com/html/download.html

You can then Start the DB in Server mode by executing the H2 DB Runnable JAR file:

$ java -jar h2-.jar -webAllowOthers -tcpAllowOthers

You will see in the Console the following log:

Web Console server running at http://10.5.126.52:8082 (others can connect)
TCP server running at tcp://10.5.126.52:9092 (others can connect)
PG server running at pg://10.5.126.52:5435 (only local connections)

You can now connect to the H2 database from an external process by pointing to the TCP Address indicated in the log.

Please note, if you are exposing the Web Console to remote servers there's a severe security issue. The issue impacts H2 Releases between 1.1.100 and 2.0.204. Read more here: What you need to know about CVE-2021-42392

At the same time, the H2 DB console will show up in the browser. If using a local machine, simply connect to localhost:8082 to see the Web console.

To see how to monitor H2 Database using the Web Console, check the section “Monitoring H2 Database“.

What if you need to run multiple H2 databases on your machine ? then simply use a different tcpPort and webPort of the database. For example:

$ java -jar h2-2.1.214.jar -tcpPort 9101 -webAllowOthers -tcpAllowOthers -webPort 8888

Starting H2 DB in Server mode programmatically

As an alternative, you can start also H2 DB in Server Mode programmatically, with one line of code:

import org.h2.tools.Server;
...
// start the H2 DB TCP Server
Server server = Server.createTcpServer("-tcpPort", "9092", "-tcpAllowOthers").start();
...
// stop the H2 DB TCP Server
server.stop();

Running H2 Database in Embedded mode

The H2 Database is generally run in embedded mode on the top of application server such as WildFly or JBoss EAP. Here is the default Datasource configuration of WildFly which uses H2 database as in-memory database:

<datasources>
	<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
	    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
	    <driver>h2</driver>
	    <security>
		<user-name>sa</user-name>
		<password>sa</password>
	    </security>
	</datasource>
	<drivers>
	    <driver name="h2" module="com.h2database.h2">
		<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
	    </driver>
	</drivers>
</datasources>

Please notice the DB_CLOSE_DELAY=-1 parameter in the database URL. By default, when the last connection is closed, the embedded h2 DB will shutdown. To keep H2 DB alive as long as the virtual machine is alive, we set DB_CLOSE_DELAY to -1.

That is enough for very basic application tests, however it does not persists data on a physical storage, so the data won’t survive a server restart. Additionally, in-memory database are local to the JVM thus accessing the same database using this URL only works within the same virtual machine and class loader environment.

Thus, if you want to get the best from H2 DB and also if you want to monitor the H2 DB you need server mode database, which actually exposes TCP/IP socket for other processes. So let’s modify the Datasource URL String as follows:

<datasources>
	<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true" statistics-enabled="${wildfly.datasources.statistics-enabled:${wildfly.statistics-enabled:false}}">
	    <connection-url>jdbc:h2:tcp://localhost/~/test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
	    <driver>h2</driver>
	    <security>
		<user-name>sa</user-name>
		<password>sa</password>
	    </security>
	</datasource>
	<drivers>
	    <driver name="h2" module="com.h2database.h2">
		<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
	    </driver>
	</drivers>
</datasources>

Before starting the application server, start the H2 Database in server mode as indicated in the previous section.

H2 Database example application (JPA)

As an example, we will show a basic JPA applications which persists a sets of key/values in the H2 DB. Here is the persistence.xml file which is adapted for H2 database:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0"
    xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="persistenceUnit"
        transaction-type="JTA">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <class>com.sample.model.Property</class>

        <jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source>

        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" />
            <property name="hibernate.max_fetch_depth" value="3" />
            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.show_sql" value="true" />
        </properties>
    </persistence-unit>
</persistence>

This is part of a simple Web application which adds up some Properties using a Web interface:

You can find the source code of this example application at: https://github.com/fmarchioni/mastertheboss/tree/master/h2/h2-demo

Executing SQL Scripts when the JDBC Connection is active

Many times, you require to execute some SQL Initialization DDL, before the application start. For example, if you are using a Database Realm which secures your application, it is required to have the Database tables created. The trick is to include an “INIT” parameter in the JDBC Connection String which contains a “RUNSCRIPT FROM” command as in this example:

jdbc:h2:tcp://localhost/mem:elytron_jdbc_test;DB_CLOSE_DELAY=-1;DB_CLOSE_DELAY=-1;INIT=RUNSCRIPT FROM 'classpath:import.sql

You can place the import.sql script wherever it is accessible in the classpath. For example into the resources folder of a Maven project.

Now let’s see how you can monitor the H2 Database, once that our Data is persistent.

Proper Database shutdown

According to the documentation, H2 database connections are closed automatically when the virtual machine exits normally. Therefore, you don’t need to perform any extra action as a shutdown hook, which is a mechanism for closing resources, is already there.

In some cases, you can choose to disable automatic closing of the database by adding the following parameter to the database URL:

"jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE"

Finally, you can issue the SHUTDOWN command, which will make H2 free all resources related to the connection immediately:

SHUTDOWN

Starting and Stopping H2 DB from Maven

It is worth mentioning that you can use the exec-maven-plugin to trigger H2 Database Start and Stop as part of your Integration Tests. Simply define two classes:

A StartServer class which starts up the H2 DB as discussed before
A StopServer class which stops the H2 DB as discussed before

<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>exec-maven-plugin</artifactId>
  <version>3.1.0</version>
  <executions>
    <execution>
      <!-- start H2 DB before integration tests -->
      <id>start</id>
      <phase>pre-integration-test</phase>
      <goals>
        <goal>java</goal>
      </goals>
      <configuration>
        <mainClass>com.mastertheboss.StartServer</mainClass>
      </configuration>
     </execution>
     <execution>
      <!-- stop H2 DB after integration tests -->
      <id>stop</id>
      <phase>post-integration-test</phase>
      <goals>
        <goal>java</goal>
      </goals>
      <configuration>
        <mainClass>com.mastertheboss.StopServer</mainClass>
      </configuration>
     </execution>
   </executions>
 </plugin>

Monitoring H2 Database

There are several options for monitoring the H2 database. Obviously if you are running H2 in the in-memory mode you should launch a SQL tool running in the same JVM where WildFly is running. On the other hand, if you are running server mode you can monitor externally the Database, just like any other commercial DB.

Using the built-in H2 DB Web console

Double click the h2*.jar which is located in the modulescomh2databaseh2main of your JBoss/Wildfly distribution.

Also, running the java -jar [H2DatabaseJar] will do.

The H2 web console will prompt. The H2 Console application lets you access a database using a browser at the address http://localhost:8082

There you can monitor your H2 DB or execute SQL. (By the way this web application has also a nice built-in auto completion for your DB objects)

Eclipse H2 database viewer

If you are using Eclipse or JBoss Developer Studio for developing your applications then you can create as well a Datasource view to the H2 Database. Start by creating a new Generic JDBC Connection, including reference to the JDBC Driver file and JDBC Properties:

Next, move to the Data Source explorer view, where you will be able to explore the selected Database and eventually open an SQL ScrapBook:

Using IntelliJ Idea to monitor H2 Database

Much the same way, you can configure H2 DB Connectivity using IntelliJ idea. In order to do that, you need to install the Database Navigator plugin from the Settings | Plugin Panel:

Then restart your IDE. Open the DB Browser View and configure the H2 Database in it:

That’s all! Now you can check your H2 Database from the DB Browser View:

Troubleshooting H2 Issues:

Sorry, remote connections (‘webAllowOthers’) are disabled on this server.

If you get this error code it means that you are trying to access the Web console by a remote address (or also by the same host but using the IP address). You can enable remote connections in the h2 console property file which are stored in a configuration file called .h2.server.properties in you user home directory. Alternatively, just use http://localhost:8082 to access the Web console.

Preventing DB Reset

H2, by default, drops your in memory database if there are no active connections. To prevent this add DB_CLOSE_DELAY=-1 to the url (use a semicolon as a separator) eg: jdbc:h2:mem:play;MODE=MYSQL;DB_CLOSE_DELAY=-1

Uppercase for DB Tables

H2 DB, by default, creates tables with upper case names. Sometimes you don’t want this to happen. To prevent this add DATABASE_TO_UPPER=FALSE to the url (use a semicolon as a separator).

Syntax error in SQL statement “Create…..”; expected “identifier”

This error can happen if you are using reserved keywords such as USER is your SQL. As a matter of fact, USER is a reserved word in Standard SQL Standard and also is a keyword in H2. To solve it, force quotation of the identifiers. For example:

spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.jpa.properties.hibernate.globally_quoted_identifiers_skip_column_definitions = true

Also, you can add to the JDBC Connection String the list of keywords you want to exclude such as:

;NON_KEYWORDS=USER

If you need a H2 DB cheatsheet, then check the following one: H2 Database cheatsheet

Post Views:
3,699

Источник