newbie: what should I use for connection pooling?

[Ian Clarke]

So, I realized that Jooq doesn't handle JDBC connection pooling, so I need an external solution.  Unfortunately it's not obvious which options are best.  Some appear hopelessly overcomplicated, others are unmaintained, and others require additional large dependencies (such as Tomcat).  I'm not using any framework in my project other than Jersey and Grizzly to implement a JSON-HTTP API.

Can anyone recommend any connection pool options that work nicely with Jooq?

Thanks,

Ian.

[Ian Clarke]

Ah, I just realized that Mysql's Java driver includes connection pooling by default, so I guess this question is probably moot.  Sorry for bugging you :-)

Ian.

[Ian Clarke]

Or maybe not... turns out I was looking at the documentation for the .net connector, so I need to reopen this question...

Ian.

[FractalizeR]

I think I can recommend BoneCP here. See my question on StackOverflow.

вторник, 6 марта 2012 г. 1:47:31 UTC+4 пользователь Ian Clarke написал:

[Andreas]

Hi Ian,

It seams to me that the MySQL Connector/J does support connection
pooling: com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
Read http://dev.mysql.com/tech-resources/articles/connection_pooling_with_connectorj.html
for almost helpful instructions.

Btw. Do you really need connection pooling? I've developed a fair
amount of application which are using a database to store there data,
and up to this point I never had performance problem with creating a
new database connection. Even when in one of my web application I
create >= 5 connection per request, the real bottleneck are the
queries. In my opinion connection pooling only cloaks stuff and should
only be used you are sure that you need it and you know what you are
doing.

I don't know what kind of application you are developing and what load
you expect, but because you wrote "newbie" I couldn't resist to give
you some advice.

Andreas

[Lukas Eder]

Hello guys,

@Ian: Good question. Yes, jOOQ explicitly avoids features like
connection pooling, transaction handling, second-level caching etc.
For all of these issues, there are already excellent tools out there
that suit end users' needs better than what I could re-implement with
jOOQ.

@Vladislav: That's a nice Stack Overflow question with good research.
I would probably have recommended c3p0, I wasn't aware that it has
gotten somewhat outdated. Good to know. Do you have working examples
with BoneCP and jOOQ? That might be something interesting for the jOOQ
manual...

@Andreas: Thanks for your input. You're probably right in this
specific setup (MySQL / newbie). Pooling a couple of
Oracle-connections in a J2EE system with 2000 - 10000 concurrent
sessions is worth the trouble, if you don't want to kill the database.
Tuning queries is another story :-)

Cheers
Lukas

2012/3/6 Andreas <andreas...@gmail.com>:

[Ian Clarke]

Thanks everyone.

I eventually went with BoneCP and it was fairly painless.  I was a little puzzled that all of their examples involve grabbing a connection, and then closing it.  Initially I thought that if you closed the connection it wouldn't return to the pool, but I then realized that this is probably the way you indicate that the connection can be returned to the pool.

It's also working quite nicely with Java 1.7's new try (Connection c = pool.getConnection()) syntax.

@Andreas, thanks for the pointer to the built-in Connector/J pooling, I think I'll stick with BoneCP as it seems simpler.  

It's been a while since I've done this kind of J2EE stuff, so much of the tooling is grossly overengineered, poorly documented, and they all manage to make the simplest use-cases seriously over-complicated (I'm looking at you Jersey).  jOOQ is a nice exception to that :-)

Ian.

--

i...@onespot.com

Co-founder and CTO

OneSpot

[Lukas Eder]

Hello Ian,

> I eventually went with BoneCP and it was fairly painless.  I was a little
> puzzled that all of their examples involve grabbing a connection, and then
> closing it.  Initially I thought that if you closed the connection it
> wouldn't return to the pool, but I then realized that this is probably the
> way you indicate that the connection can be returned to the pool.

Yes, with connection pooling, closing a connection has a new
semantics, just like "opening" a connection, which doesn't open a new
one, but gets one from the pool. In a way, the JDBC Connection's
lifecycle is still the same, though.

> It's been a while since I've done this kind of J2EE stuff, so much of the
> tooling is grossly overengineered, poorly documented, and they all manage to
> make the simplest use-cases seriously over-complicated (I'm looking at you
> Jersey).  jOOQ is a nice exception to that :-)

Thank you. You might enjoy these readings about architecture astronauts :-)
http://www.joelonsoftware.com/articles/fog0000000018.html
http://www.joelonsoftware.com/items/2005/10/21.html
http://www.joelonsoftware.com/items/2008/05/01.html

Cheers
Lukas

[Ian Clarke]

On Tue, Mar 6, 2012 at 7:56 AM, Lukas Eder <lukas...@gmail.com> wrote:

Thank you. You might enjoy these readings about architecture astronauts :-)
http://www.joelonsoftware.com/articles/fog0000000018.html
http://www.joelonsoftware.com/items/2005/10/21.html
http://www.joelonsoftware.com/items/2008/05/01.html

Hah, yes, I'd read those before but it is good to be reminded of them.  Unfortunately it seems that Java libraries from a certain stretch of time, perhaps 2001 to 2009, are especially vulnerable to this problem.  In particular, anything involving web containers.

The software I'm currently working on requires a JSON-REST API, and yet it's a real struggle to find a way to achieve this without completely repackaging everything into a war and pulling in huge dependencies like Tomcat, Spring, configuring web.xml files, etc.

XML is the worst, use of XML for anything other than its intended purpose, a text MARKUP language, is a sure sign of an architecture astronaut.  Whoever decided that XML was a great language for config files needs a good kicking IMHO.  I'm looking at you Maven.

We're lucky that modern Java library writers are finally recognizing these problems and fixing them.  For example, JSoup is a beautiful example of a Java library, simple, the easy use-cases are easy etc.  I'm hopeful that I'll develop a similar affection for Jooq.

Anyway, enough ranting for now :-) 

[Lukas Eder]

> XML is the worst, use of XML for anything other than its intended purpose, a
> text MARKUP language, is a sure sign of an architecture astronaut.  Whoever
> decided that XML was a great language for config files needs a good kicking
> IMHO.  I'm looking at you Maven.

Oops, then you probably don't like jOOQ's move from .properties files
to XML for jooq-codegen configuration :-)
I wouldn't worry too much about these things. I'm sure, we'll rant
about JSON in 5 years as much as we have ranted about XML/J2EE in the
past 5 years.

Cheers
Lukas

[Ian Clarke]

On Tue, Mar 6, 2012 at 9:42 AM, Lukas Eder <lukas...@gmail.com> wrote:

> XML is the worst, use of XML for anything other than its intended purpose, a
> text MARKUP language, is a sure sign of an architecture astronaut.  Whoever
> decided that XML was a great language for config files needs a good kicking
> IMHO.  I'm looking at you Maven.

Oops, then you probably don't like jOOQ's move from .properties files
to XML for jooq-codegen configuration :-)

Wasn't aware of that, but you're right :-)

 

I wouldn't worry too much about these things. I'm sure, we'll rant
about JSON in 5 years as much as we have ranted about XML/J2EE in the
past 5 years.

I'm not so sure, I think JSON is much better suited as a human-readable data representation.  Sure, there'll be better stuff in the future, but I don't think JSON will be viewed as the same kind of "blind alley" that XML has been.  It's just so much easier to read and to write.

XML, or its precursor SGML, were designed for embedding formatting in text (ie. "markup"), they were never designed as generic data representation languages.  Then, someone at Microsoft decided that we should use XML for everything, Sun went along with it, and it became this cargo-cult mind virus that infected the programming community, especially Java programmers.

Anyhoo, It's not my intention to start an XML versus JSON holy war :-)

[FractalizeR]

@Vladislav: That's a nice Stack Overflow question with good research.
I would probably have recommended c3p0, I wasn't aware that it has
gotten somewhat outdated. Good to know. Do you have working examples
with BoneCP and jOOQ? That might be something interesting for the jOOQ
manual...

I think connection pooling isn't related to Jooq because connection pool just gives you connections from pool. My code looks like this

            ServerConfiguration configuration = ServerConfiguration.getInstance();
            JDBCConnectionPool connectionPool = configuration.getConnectionPool();
            connection = connectionPool.getConnection();

            if (Distribucalc.DISTRIBUCALC.getName().equals(configuration.getDatabaseName())) {
                sqlFactory = new Factory(connection, configuration.getJooqFactoryName());
            } else {
                SchemaMapping mapping = new SchemaMapping();
                mapping.add(Distribucalc.DISTRIBUCALC, configuration.getDatabaseName());
                sqlFactory = new Factory(connection, configuration.getJooqFactoryName(), mapping);
            }
 

[Lukas Eder]

> I think connection pooling isn't related to Jooq because connection pool
> just gives you connections from pool. My code looks like this

Thanks for the example. True, there isn't much magic to it...