• 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.
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!