Forums Register Login

Use only 1 connection to SQL Server (newbie ?)

+Pie Number of slices to send: Send
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);
+Pie Number of slices to send: Send
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();
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
 

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 ]
+Pie Number of slices to send: Send
Thanks for the correction there.
+Pie Number of slices to send: Send
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;
}
}
+Pie Number of slices to send: Send
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 ]
+Pie Number of slices to send: Send
Sorry, it took so long to reply.

Thank you for the help. It is working.
A berm makes a great wind break. And we all like to break wind once in a while. Like this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 2407 times.
Similar Threads
How to Insert Data into a table in mysql database
JDBC Exception in JNDI lookup
No suitable driver found for JDBC
SQL Server - netbeans Connection problem
No access to SQL Express 2005/2008 database
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 05:54:58.