aspose file tools*
The moose likes JDBC and the fly likes Use only 1 connection to SQL Server (newbie ?) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Use only 1 connection to SQL Server (newbie ?)" Watch "Use only 1 connection to SQL Server (newbie ?)" New topic
Author

Use only 1 connection to SQL Server (newbie ?)

Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
I'm new to Java and this is my first program.

I have coded a 2 private classes that use the Sql Server database in each class.

Is it possible to code a separate class (databaseConenction) that issue the connection and it is usable in the other classes?

Code:

// Create a variable for the connection string
String connectionUrl ="jdbc:sqlserver://xxxxxxxxx.xxxx.xxxx.xxxx:1433;databaseName=name;user=user;password=pass";
// Declare the JDBC objects
Connection con = null;
// Establish the connection
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);
Jon Parise
Ranch Hand

Joined: Jul 03, 2007
Posts: 81
What you need here is what is typically called a Singleton. You need a class that handles the database that there can only ever be one instance of.

What you need to do is create a class to wrap you database.



That should only allow 1 connection to the database. You use it like this:

DBHandler dbHandler = DBHandler.getInsatnce();

dbHandler.connect();
ResultSet rs = dbHandler.executeQuery("SELECT * FROM BLAH");
dbHandler.disconnect();
Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
Jon

Thank you for the reply, it was very helpful

Just another question.

public static void main(String[] args) {
try {

DBHandler dbHandler = new DBHandler.getInstance();
dbHandler.connect();

CHRISInterface.LoadFile();
CHRISInterface.validateData();
dbHandler.disconnect();

}

Using the code in main, would I pass the dbHandler to the LoadFile and validateData classes? This is where the SQL is located.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

OK, I have to ask: why is it important you only use one connection? And if it is important, why are you not implementing this by configuing SQL Server itself?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
Being new to Java and learning JDBC.

I was informed that each connection to the database has a large overhead and that it is better to make only 1 connection and reuse.

I'm not the DBA, not able to configure SQL Server
T.A. Nguyen
Ranch Hand

Joined: Sep 02, 2008
Posts: 36

Originally posted by Michael McAuliffe:
Jon

Thank you for the reply, it was very helpful

Just another question.

public static void main(String[] args) {
try {

DBHandler dbHandler = new DBHandler.getInstance();
dbHandler.connect();

CHRISInterface.LoadFile();
CHRISInterface.validateData();
dbHandler.disconnect();

}

Using the code in main, would I pass the dbHandler to the LoadFile and validateData classes? This is where the SQL is located.


Your code where:

is wrong, it should be:


Better yet, use this:


and in CHRISInterface you would do the same, just use DBHandler.getConnection() whenever you need a connection. Yes, you will need to insert try/catch to the above sample codes.

enjoy!

[ September 03, 2008: Message edited by: T.A. Nguyen ]
[ September 22, 2008: Message edited by: T.A. Nguyen ]

T.A. Nguyen
http://ta.cnci.org
http://www.linkedin.com/in/nguyenta
Jon Parise
Ranch Hand

Joined: Jul 03, 2007
Posts: 81
Thanks for the correction there.
Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
Thanks to everyone that has offered assistance.

I tried the following, but receiving errors.

1. private static Connection connection;

"The field connection cannot be declared static; static fields can only be declared in static or top level types"

2. Statement s = DBHandler.getConnection.createStatement();
"Cannot make a static reference to the non-static method getConnection() from the type CHRISInterface.DBHandler"

I changed the class to static and the getConnection method to static

Is there a reason why this had to be done?

[B}public static class DBHandler { [/B]
// The Instance
private static Connection connection;

//Private so one can access it externally
private DBHandler() {
}

// Connect to DB
public static Connection getConnection() {
try {
if (connection == null) {
// Create a variable for the connection string
String connectionUrl =
"jdbc:sqlserver://testServer:1433;databaseName=test;" +
"user=testuser;password=testpass";
// Declare the JDBC objects
// Establish the conenction
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
connection = DriverManager.getConnection(connectionUrl);
}
} // end try
catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return connection;
}
}
T.A. Nguyen
Ranch Hand

Joined: Sep 02, 2008
Posts: 36

Here is the complete codes for DBHandler.java, please note the I move the main (test) method inside DBHandler.java, what's missing here is the actual code for CHRISInterface... have fun.

This is a typical singleton implementation. Accessing via a static method, the method control creation/access of the class static field. Remember, this is NOT the only way...


[ September 22, 2008: Message edited by: T.A. Nguyen ]
Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
Sorry, it took so long to reply.

Thank you for the help. It is working.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Use only 1 connection to SQL Server (newbie ?)