This week's book giveaway is in the Python forum.
We're giving away four copies of Python Continuous Integration and Delivery and have Moritz Lenz on-line!
See this thread for details.
Win a copy of Python Continuous Integration and Delivery this week in the Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Devaka Cooray
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Tim Holloway
  • Claude Moore
  • Stephan van Hulst
Bartenders:
  • Winston Gutkowski
  • Carey Brown
  • Frits Walraven

H2 Connection Pool Question  RSS feed

 
Bartender
Posts: 1647
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In this example, https://www.javatips.net/blog/h2-database-connection-pool-example, the logic creates a connection pool within a method and then deletes it at the end of the method.

Does that strategy make sense for a "Connection Pool"? I mean, shouldn't a connection pool be a persistent object future connections call where the Pool manages connections? Or do you look at the Pool from the point of view that in a multi-user environment, multiple users will run the same method and at that point the Connection Pool comes into play?

Just a bit confused here.

Thanks,

--mike
 
Saloon Keeper
Posts: 20502
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your first warning should have been this line:

It's probably been 15 years now since that was necessary. This person obviously isn't as clever as he thinks he is.

Secondly, it looks like the getConnectionPool method was attempting to return a singleton object. It obviously fails to do so. And to add insult to injury, it's not even in its own factory class - it's lumped in with the application code.

Moral of story: Not everything you find on the Internet is gold. No matter how high up in a Google search it might appear.
 
Mike London
Bartender
Posts: 1647
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:Your first warning should have been this line:

It's probably been 15 years now since that was necessary. This person obviously isn't as clever as he thinks he is.

Secondly, it looks like the getConnectionPool method was attempting to return a singleton object. It obviously fails to do so. And to add insult to injury, it's not even in its own factory class - it's lumped in with the application code.

Moral of story: Not everything you find on the Internet is gold. No matter how high up in a Google search it might appear.



Aside from the Class.forName, the code is quite similar to that on the H2 site itself:

http://www.h2database.com/javadoc/org/h2/jdbcx/JdbcConnectionPool.html
 
Tim Holloway
Saloon Keeper
Posts: 20502
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Mike London wrote:
Aside from the Class.forName, the code is quite similar to that on the H2 site itself:

http://www.h2database.com/javadoc/org/h2/jdbcx/JdbcConnectionPool.html



Well, yes. It's not exactly rocket science there. The difference is that on the H2 site, they're setting up, running an example, and shutting down. They didn't write code that leaks previous instances of a pool only to create a new pool instance every time you ask for it.
 
Mike London
Bartender
Posts: 1647
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:

Mike London wrote:
Aside from the Class.forName, the code is quite similar to that on the H2 site itself:

http://www.h2database.com/javadoc/org/h2/jdbcx/JdbcConnectionPool.html



Well, yes. It's not exactly rocket science there. The difference is that on the H2 site, they're setting up, running an example, and shutting down. They didn't write code that leaks previous instances of a pool only to create a new pool instance every time you ask for it.



I implemented the connection pool logic in a static method so it's more or less the same.

You never answered my actual question, that I see:  "Does that strategy make sense for a "Connection Pool"? I mean, shouldn't a connection pool be a persistent object future connections call where the Pool manages connections? Or do you look at the Pool from the point of view that in a multi-user environment, multiple users will run the same method and at that point the Connection Pool comes into play?"

Thanks,
 
Tim Holloway
Saloon Keeper
Posts: 20502
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure what you're asking. But a Connection Pool is a waste of resources for a single-threaded app. Pools are more suited for multi-threaded and/or multi-user apps where you don't want a given task to hang on to Connections, and you don't want to create a Connection from the ground up every time a task needs one.

My objections to the sample code you pointed to initially were based on the sloppiness of the design and code, not its utility. Both it and the H2 sample are merely trying to show how to set up a Connection Pool and use it. They're illustrative, not real-world uses. In the real world there'd be a lot more going on. And yes, normally you create the pool(s) at or near start-up and would only destroy them at shutdown. Because creating a Connection Pool is at least as much overhead as creating a raw connection, and depending on whether it builds a minimum pool in advance, potentially much more. Pools only pay for themselves when you're asking for Connections multiple times and releasing them in between.
 
Mike London
Bartender
Posts: 1647
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:I'm not sure what you're asking. But a Connection Pool is a waste of resources for a single-threaded app. Pools are more suited for multi-threaded and/or multi-user apps where you don't want a given task to hang on to Connections, and you don't want to create a Connection from the ground up every time a task needs one.

My objections to the sample code you pointed to initially were based on the sloppiness of the design and code, not its utility. Both it and the H2 sample are merely trying to show how to set up a Connection Pool and use it. They're illustrative, not real-world uses. In the real world there'd be a lot more going on. And yes, normally you create the pool(s) at or near start-up and would only destroy them at shutdown. Because creating a Connection Pool is at least as much overhead as creating a raw connection, and depending on whether it builds a minimum pool in advance, potentially much more. Pools only pay for themselves when you're asking for Connections multiple times and releasing them in between.



Use-case:
In a micro-service, for example, you have local micro-service method with lifetimes the same as any other method (variables go away at the end of the method). So, on those methods, my question really: "shouldn't you really, instead, create the connection pool instance outside a micro-service (local) method and THEN create connections from that external (persistent) instance from inside the local methods so the connection pool persists?"

Sorry for my lack of clarity. I hope this use-case helps clarify my initial question.

Thanks,

-- mike
 
Tim Holloway
Saloon Keeper
Posts: 20502
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Assuming that the micro-service is running in some sort of container (web, OSGi, whatever), then yes, the connection pool would preferably be owned and managed by the service container, and the service should be able to tap into it. As, for example, how J2EE webapps can locate the pool services using a JNDI lookup.

Because, as I said, starting an entire connection pool is even more work than starting a connection from scratch. And if we assume that the point of a micro-service is to be lightweight, that would mean that it's better to be provided with a ready-made source of connections than have to repeatedly create and destroy the whole shebang.
 
Mike London
Bartender
Posts: 1647
17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:

Because, as I said, starting an entire connection pool is even more work than starting a connection from scratch. And if we assume that the point of a micro-service is to be lightweight, that would mean that it's better to be provided with a ready-made source of connections than have to repeatedly create and destroy the whole shebang.



Well, it takes about 20ms to do a basic query with 1,000 records with the current code and I don't see any examples of how to create connections up front but I like your idea at least in principle.

Thanks Tim,

- mike
 
Tim Holloway
Saloon Keeper
Posts: 20502
115
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The connection pool is a collection of pre-made java.sql.Connection objects created using the URL and credentials supplied to the pool when it was created. Generally there's a minimum and maximum value.

When the pool is first created, it uses DriverManager getConnection() to obtains the indicated minimum/initial number of Connections. Thereafter, whenever a client requests a Connection from the pool, the pool wraps a façade object around that Connection and gives it to the user. The façade object mostly just passes method calls down to the underlying Connection object, but it overrides the close() method. The override method does not close the Connection object, but simply returns it to the pool for another use, eliminating the need to create a Connection object from the ground up (which is a lot of work, compared to just pulling a ready-made Connection from the pool).

If more Connections are made than were in the Pool's collection, a new Connection will be created for the pool and returned, up to the configured maximum. Attempting to obtain more that the maximum number of Connections simultaneously will simply fail. This keeps runaway apps from eating all available memory and/or network resources.

So a Connection pool has no real effect on how fast a database request is. What it affects is how fast you can connect to the database and how much of a burden you put on the JVM and OS in order to get the Connection.
 
The moustache of a titan! The ad of a flea:
Become a Java guru with IntelliJ IDEA
https://www.jetbrains.com/idea/
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!