Are there any advantages to use something like HikariCP with embedded databases like SQLite, H2 or HSQLDB? (the last two can also run in server mode but I'm not interested in that)
Apart from the performance benefit (that I suppose would be negligible with embedded databases) I'm also interested in other facilities provided by the connection pool that could make the code more concise and/or robust.
I think that this question is different from this other one in that it's more specific since it focuses on embedded databases, and, to a lesser degree, on HikariCP.
Connection pools exist primarily because opening a new connection from scratch is an expensive operation. Typically, it involves TCP/IP handshake, encryption and protocol negotiation and authentication. There is some overhead when closing connection, too.
So this ultimately boils down to implementation: is opening a new connection slow enough to warrant a reuse? A connection pool makes sense if the total time for opening connections is considerable compared to time consumed by other operations, or if opening a connection incurs a critical latency. For embedded databases, the difference should be minimal as they run in the same memory space as the program itself.
However, a connection pool also has a useful side effect because it limits the maximum number of simultaneous connections. On a server with no connection pooling, an attacker can easily send a flood of requests, exhausting memory or causing a denial of service.
From perspective of code clarity and abstraction, connection pools are also nice, because they are completely transparent. If you someday decide to move from embedded to client/server, you don't have to change anything.