Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need guidance regarding connection pooling.

 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everybody, I am Chaitanya, I want to write my own connection pooling class where I want to create only 10 connection objects and return those when asked. I have worked around it to a little extent for a single connection object.

Now I want to write the same for 10 connections. So I wrote the following program. This code will create 10 connection objects when asked, if 10 connections have been initialized then randomly a connection is taken and given.

Is my approach right?
Can anyone please guide me?

Thank you all in advance.
 
yuvaraj KumarAmudhan
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

If you get 2 connection object from the pool, then there should be 8 connections remaining. But your code looks like that calculation is not available.

getConnection() - this method is to get a connection object from the pool?
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
getConnection() method is used to get a connection object from the pool. No programmer will take two connections from a pool at one instance. Using one connection itself we can do all sort of jdbc works I think.

However objects are also multithreaded in java.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anyone around here to tell me whether the approach is right or not?
 
Rob Spoor
Sheriff
Pie
Posts: 20744
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see one major flaw: random.nextInt() will return a number in the full range of int. You want to use random.nextInt(10) - this returns a random number between 0 (inclusive) and 10 (exclusive).

After that fix your solution works, to a degree. After 10 connections have been retrieved it's possible to return the same connection the next time. This may not be a problem, but it definitely will when you set auto commit to false and handle your own transactions. If thread 1 sets auto commit to false, then thread 2's next statements will be handled in the same transaction. Committing in thread 1 will also commit the changes from thread 2, but worse, rolling back in thread 1 will also roll back the changes from thread 2. And if you forget to set auto commit to true in thread 1, all changes from thread 2 after that may never be committed.

If you make sure you do not a) set auto commit to false, and b) close the connections, then your code would work after that little fix. Otherwise I suggest you look for existing connection pooling techniques.

I'll move this thread to JDBC, as that's a better location because of the specific JDBC issues you will encounter.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Rob, I have two doubt.

1>
I see one major flaw: random.nextInt() will return a number in the full range of int. You want to use random.nextInt(10) - this returns a random number between 0 (inclusive) and 10 (exclusive).

What is the problem here, I have initialized 10 connection objects 0 to 9 and using nextInt(10) method which will return a number from 0 to 9(including). I dont see any mistake here. Can you explain this?

2> Are you telling me that "I" should not set auto commit to false, or the person who is taking the connection from the pool must not make it false.

Is it the programmer who wrote the connection pool or the programmer who took the connection from the pool who should not set auto commit as false?
Otherwise I suggest you look for existing connection pooling techniques

I want to write my own connection pool, till now I am using an existing pool, I just wanted to know how it is coded.

Thank you in advance.
 
Rob Spoor
Sheriff
Pie
Posts: 20744
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chaitanya karthikk wrote:Thanks Rob, I have two doubt.

1>
I see one major flaw: random.nextInt() will return a number in the full range of int. You want to use random.nextInt(10) - this returns a random number between 0 (inclusive) and 10 (exclusive).

What is the problem here, I have initialized 10 connection objects 0 to 9 and using nextInt(10) method which will return a number from 0 to 9(including). I dont see any mistake here. Can you explain this?

True. But you are not calling nextInt(10), you are calling nextInt(). Which returns a number between Integer.MIN_VALUE and Integer.MAX_VALUE (both inclusive).

2> Are you telling me that "I" should not set auto commit to false, or the person who is taking the connection from the pool must not make it false.

Is it the programmer who wrote the connection pool or the programmer who took the connection from the pool who should not set auto commit as false?
Otherwise I suggest you look for existing connection pooling techniques

I want to write my own connection pool, till now I am using an existing pool, I just wanted to know how it is coded.

Thank you in advance.

The user of the connection pool must be aware that the connections may be shared. That means he/she cannot close connections. It also means that no guarantees can be made about whether things like auto commit are on or off. The best way is to tell all users to allow synchronized blocks for all interaction:
The thing is, because you don't know who has been using the same connection, you cannot make any assumptions about auto-commit, read-only, and all other configurable settings.

Most connection pools either create a new connection when the pool is exhausted, or they will make retrieving a connection block until one is released. Most also involve a Connection wrapper, that allow you to close() the connections. This close() will not close the actual connection but return it into the pool instead.

This wrapper usually looks something like this:
It's a bit more complex than this (e.g. all methods check if the connection hasn't been returned to the pool yet) but essentially this is the principle.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sincerely, I din't understand the second point Rob. I am sorry.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How can I force the user not call close() on Connection object
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35266
383
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
chaitanya karthikk wrote:How can I force the user not call close() on Connection object

You can wrap the Connection object in one of your own that implements Connection. That way you can intercept the close() call.
 
chaitanya karthikk
Ranch Hand
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Jeanne.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic