In my opinion, the only ConnectionProvider to be used in a production system is the DataSourceConnectionProvider. With a DataSource, you can chain as many DataSource proxies as well want: connection pooling, statement logging, FlexyPool. This is the de facto way of getting a Connection when using Java EE or Spring

While having a ConnectionProvider allows us to work when there is no DataSource, in reality, we only do it during testing. For this reason, I wonder how many users actually use the hibernate-c3p0 and hibernate-hikari modules?