Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database Connection Singleton

 
lina wang
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is a singleton class which establish a connection to a database. Is anybody know what's going to happen if multiple thread share the same Connection object to select data from a database table concurrently.(Only read from database, no DML operation). Thanks for your help.
public class DBConnectionSingleton
{
private static DBConnectionSingleton instance = null;
private static Connection conn;
private DBConnectionSingleton()
{
String dbDriver = ..
String url = ..
String username= ..
String password = ..
try
{
Class.forName( dbDriver);
conn = DriverManager.getConnection(url,username, password);
}
catch(ClassNotFoundException cnfErr)
{
cnfErr.printStackTrace();
}
catch(SQLException err)
{
err.printStackTrace();
}
}
public static DBConnectionSingleton getInstance()
{
if(instance == null)
return new DBConnectionSingleton();
else
return instance;
}
public static Connection getConnection()
{
return conn;
}
}
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you aren't sure what the multi-threaded behaviour is, it isn't a Singleton.
Have you looked at using a Connection Pool instead?
 
Kevin Mukhar
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by David O'Meara:
If you aren't sure what the multi-threaded behaviour is, it isn't a Singleton.
Have you looked at using a Connection Pool instead?

A singleton pattern says nothing about the multi-threaded aspect of the class. All that singleton says is that there will only be one instance of the class in a JVM. That does not prevent multiple threads from executing in that one instance. In fact, in a multi-threaded program, it is more likely that multiple threads will execute in a Singleton class instance.
 
Kevin Mukhar
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by lina wang:
Here is a singleton class which establish a connection to a database. Is anybody know what's going to happen if multiple thread share the same Connection object to select data from a database table concurrently.(Only read from database, no DML operation). Thanks for your help.
public class DBConnectionSingleton
{
private static DBConnectionSingleton instance = null;
private static Connection conn;
/...
public static Connection getConnection()
{
return conn;
}
}

Even for read-only operations, you should not let two threads share the same connection object. It's definitely an invitation for problems. Every call to getConnection() should return a new connection instance. It's possible that two threads could safely share the connection; it's also possible that one or both threads will get invalid responses from the db.
As was suggested by the other answer, look into using a connection pool. There may be one provided by the database vendor, or the container if you are doing J2EE work.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I knew I had explained myself well enough
Firstly, due to the behaviour of the Singleton pattern, it's behaviour in a multi-threaded environment must be part of its design. As such, the code given above is not thread safe anyway since it doesn't protect the data during instantiation.
Besides that, the point I was trying to make was that if you aren't sure of the behaviour of Connections in a multi-threaded environamet, then the Singleton pattern is probably not what you are looking for and something else like a Connection Pool might be better.
Serves me right! (and thanks)
Dave
 
lina wang
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you all for your inputs. Connection Pool definitely is the approach I am going to take.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic