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.
I mean H2 is only really useful in Java, but the perf compared to sqlite in Java is equal or better due to better JDBC integration and not having to deal with JNI.
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.