Connection pooling in hsqldb

Question

What is the best way to implement connection pooling in hsqldb, without compromising on the speed?

Answer 1

If you used Hibernate's own connection pooler, you could consider using c3p0? (If you're already using c3p0, I can't help further) I haven't used HSQLDB myself but I think that could be worth trying.

Answer 2

You are comparing apples and oranges:

1. If you want orm compare the performance of different orm tools against the same db.
2. If you want connection pooling compare different connection pooling libraries against the same db.

Performing ORM incurs extra effort so it will never be as fast as direct JDBC access. That said, hibernate goes to great lengths (and very successfuly) to minimise this additional overhead. With ORM you are trading off significantly increased development productivity against a relatively small drop in performance.

Connection pooling is an orthogonal problem to orm. Most obviously, hibernate allows you to select your own connection pooling infrastructure.

Also, be aware that in practise there is often a fairly tight coupling between connection pooling and transaction mgmt. For example, a typical J2EE application will leave connection pooling to the container (via the JDBC Datasource API) and rely on declarative transactions. In this case connections and transactions are managed (approximately) together.

If you aren't in a J2EE container and you don't need orm I would simply compare C3P0, commons-pool, etc.

Answer 3

Hibernate gets connections from a DataSource, uses them and closes them. You need a connection pool or it will be very inefficient, consuming a lot of resources both on your app and on the DBMS, regardless of the database server you use.

You should try out commons-dbcp from Apache-Jakarta, it's very efficient and really simple to set up. It depends on commons-pool. You just define a BasicDataSource with DBCP and it will manage the connections from whatever JDBC driver you tell it to use. It has connection validation and lots of other stuff. Of, if you're writing a web app, configure a connection pool on the container you will be using and use that, instead of defining your own pool.