Java spring data jdbc. JdbcSQLSyntaxErrorException when using annotation @Table

Question

If I use annotation @Table("Book_Author") then I get

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "Book_Author" not found (candidates are: "BOOK_AUTHOR") ...

@Table("Book_Author")
public class BookAuthor {
    private Integer author;

    public BookAuthor(Integer authorId){
        this.author =authorId;
    }
}

schema.sql:

create table Book_Author(
    author int not null ,
    book int not null ,
    primary key (author,book)
);

Without @Table everything works fine. Please tell me why?

Answer 1

The table name is unquated. It means that the table name is case insensitive in the database and it is converted to uppercase when you use create table SQL statement. But @Table annotation is using a table name in a case sensitive manner.

You should read more about quoted and unquated table names Make H2 treat quoted name and unquoted name as the same.

In that H2 behaves in the same way as Oracle. This is a bit different on how other databases like MySQL and PostgreSQL deal with identifier names. H2 has a compatibility feature: If you append ;DATABASE_TO_UPPER=FALSE to the database URL, unquotes identifiers are not converted to uppercase, that means they are case sensitive as well. But you need append this when creating the database, and each time you use it (if you append the setting for existing databases, the identifiers of existing objects are already converted to uppercase).

Answer 2

This code snippet has some problems. Do this:
1- if you use "hibernate.cfg.xml" for your hibernate configurations add this property :

<property name="hibernate.hbm2ddl.auto">update</property>

or if you use annotations instead of xml , add appropriate annotation for table definition in hibernate.
2-add a field to this class for serving as the id of the class, like this:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

it is better not to use this:

private Integer author;

Answer 3

When you specify a table name in @Table that name is case sensitive. It has to match exactly what you use in the database. Most databases when default to all upper case which is also the ISO standard. This is why you see BOOK_AUTHOR as a candidate.

You have two main options:

Quote the table name in the schema script and use it exactly as in the @Table annotation: create table "Book_Author" ...

Alternatively use all caps in the annotation. That would be @Table("BOOK_AUTHOR")

The same principle applies for columns including those specified in @MappedCollection.

See Make H2 treat quoted name and unquoted name as the same for details about case sensitivity of H2.

Answer 4

You can correct this error by changing the case of the table name in your annotation to match the case of the table name in your schema.sql file. For example:

@Table("BOOK_AUTHOR")
public class BookAuthor {
    private Integer author;

    public BookAuthor(Integer authorId){
        this.author =authorId;
    }
}