Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Use only 1 connection to SQL Server (newbie ?)

 
Michael McAuliffe
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Michael McAuliffe
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 36
Eclipse IDE Java ME Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Jon Parise
Ranch Hand
Posts: 81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the correction there.
 
Michael McAuliffe
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 36
Eclipse IDE Java ME Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, it took so long to reply.

Thank you for the help. It is working.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic