org.h2.jdbc.JdbcSQLException: Table not found

Question

I'm getting this exception:

org.h2.jdbc.JdbcSQLException:
Table "CUSTOMERS" not found; SQL statement:
SELECT * FROM CUSTOMERS

This is the H2 Console. I have created a table there:

I have the application.yml file. I have tried to add DB_CLOSE_DELAY=-1 and DATABASE_TO_UPPER=false as well:

spring:
  database:
    url: jdbc:h2:mem:testdb
  h2:
    console.enabled: true

Also, I have a configuration class, where I have created the H2 Embedded Database:

@Bean
public DataSource dataSource() {
    return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).build();
}

Finally, the query. The table is named CUSTOMERS:

public List<Customer> getAll() {
    return jdbcTemplate.query("SELECT * FROM CUSTOMERS", (resultSet, rowNum) -> {
        Customer customer = new Customer();
        customer.setId(resultSet.getLong("id"));
        customer.setName(resultSet.getString("name"));
        customer.setAge(resultSet.getInt("age"));
        return customer;
    });
}

What should I do?

What happens if you change `url: jdbc:h2:mem:testdb` to `url: jdbc:h2:~/test`? — Dai, Jun 07 '22 at 20:07
What if you use an absolute path (instead of `~`-home-relative path)? i.e. try `/home/$you/test` ? What user-identity is Spring running under? (this would explain why `~` doesn't work: your resolved `~` home path would be different to Spring's `~` home path). Does the Spring process have `chmod` permission to read/write to the file? Also, use a more specific filename than just `test` - if you use something more distinctive (e.g. `mytesth2db`) then you can more easily find it in the filesysterm. — Dai, Jun 07 '22 at 20:11
@OldProgrammer, you are right, the default name is testdb. I have tried this as well, still getting the issue. — , Jun 07 '22 at 20:12
@Dai, I do not have this database in the filesystem. I have not downloaded the H2, just run it on the server. Should I download it? — , Jun 07 '22 at 20:18
@EndlessForest "I do not have this database in the filesystem" - Did you save and correctly close the database when you were done in H2 Console? You should have a nonempty file at `/home/$you/test` because that's the file-name you told H2 Console to use. — Dai, Jun 07 '22 at 20:19
@Dai, I have just downloaded the H2. Now, I see the test.trace (Data Base File) in C:\Users\User. Is this a database file that I need to pass into the url? — , Jun 07 '22 at 20:46
Yes - but as you're on Windows then you should avoid using the tilde `~` character [because H2 and Java are weird](https://stackoverflow.com/questions/35854425/java-application-with-h2-database). — Dai, Jun 07 '22 at 20:47
@Dai, it has to be something like this: jdbc:h2:file:./mv.db, or I'm missing something? - https://stackoverflow.com/questions/6397716/where-are-my-h2-database-files — , Jun 07 '22 at 20:55
I have to find the actual database file and pass it into the url. Haven't found it yet. There're 2 files - test.mv(db) and test.trace(db, as well) — , Jun 07 '22 at 21:09
Use a tool like Voidtools Everything to find it: https://www.voidtools.com/ — Dai, Jun 07 '22 at 21:40
I'm trying to open localhost:8080/h2-console/, and this is my local database. I haven't downloaded anything, just creared this myself using console.enabled: true. But, I cannot understand whether I should download the H2 or not. What should I connect? Even if I don't write anything in the url, it works the same. — , Jun 08 '22 at 08:13

score 0 · Answer 1 · answered Jun 22 '22 at 08:30

I had the same concern as you for a few days. I solved it by adding this:

;TRACE_LEVEL_FILE=3;TRACE_LEVEL_SYSTEM_OUT=3

ie : jdbc:h2:mem:testdb;TRACE_LEVEL_FILE=3;TRACE_LEVEL_SYSTEM_OUT=3

It helps to know why H2 has a problem.

Usually it is a keyword problem. You can ignore it by using NON_KEYWORDS : https://www.h2database.com/html/commands.html#set_non_keywords

org.h2.jdbc.JdbcSQLException: Table not found

1 Answers1

Linked