I think the big one is that it's written in Java so it runs seamlessly on the JVM. While JNI allows you to wrap native libraries for the JVM, it tends to mix poorly with the common artifact management and deployment pipelines.

The main use I've put it to over the years is as an in-memory database for integration tests.

Derby is/was the other alternative for an in JVM database.

For a long time, it was possible/practical to use SQLite from Java. Now, it is, but not if you want to keep things as pure Java (and another commenter mentioned). But really, in my mind, that’s the only real benefit for H2, the fact that’s it’s pure Java. So if you need that, you’re good.

But otherwise, I try to stick to SQLite.

It integrates better with JVM