Sharing a SQL connection between classes
The program I am making for work connects to an SQL database to post and get information. This worked fine when I would have it connect to the SQL database using port 3306 since that's the default, but since my work blocks access to that, I decided to set up forwarding in my router so incoming requests to port 23 (since I don't use Telnet) will be routed to port 3306. It works to an extent. I am now able to connection, but I think my problem is everytime I call a method that needs to connect to the database I am creating a new connection instead of using one that I haven't closed (I think). So, when I go to access the database, I keep calling the following every time:
try
{
// Load the JDBC driver
ResultSet rs =null;
String driverName ="com.mysql.jdbc.Driver";// MySQL MM JDBC driver
Class.forName (driverName);
// Create a connection to the database
serverName ="fedex.plastikracing.net";
mydatabase ="web5_db1";
url ="jdbc:mysql://" + serverName +":23/" + mydatabase;// a JDBC url
dbuser ="web5_u1";
dbpass ="password";
connection = DriverManager.getConnection (url, dbuser, dbpass);
Statement stmt = connection.createStatement ();
...// statement stuff here
}
catch (SQLException e)
{
// Could not connect to the database
JOptionPane.showMessageDialog (login.this,"Error: " + e.getMessage (),"Connection Error",
JOptionPane.ERROR_MESSAGE);
}
catch (ClassNotFoundException e)
{
// Could not find the database driver
JOptionPane.showMessageDialog (login.this,"Could not find the database driver. \n" + e.getMessage (),"Connection Error",
JOptionPane.ERROR_MESSAGE);
}
It takes a while to connect since I am forwarding, but that isn't an issue. If I go to create a new connection within a different class, I get an exception of some sort. I can't view it currently because the exception shows up in a JOptionPane and it goes way beyond the screen height.
What do I could to share this connection throughout all the classes?
I took the code to create the connection out and put it in its own class.
import java.sql.*;
import javax.swing.JOptionPane;
/*
* Database.java
* @author tristan
* Created on June 22, 2007, 12:14 PM
*/
public class Database
{
/** Creates a new instance of Database */
public Database ()
{
}
public static Connection sqlConnection ()
{
Connection connection = null;
String serverName = null;
String mydatabase = null;
String url = null; // a JDBC url
String dbuser = null;
String dbpass = null;
String port = "23";
try
{
System.out.println ("Trying port " + port + "...");
// Load the JDBC driver
String driverName = "com.mysql.jdbc.Driver"; // MySQL MM JDBC driver
Class.forName (driverName);
// Create a connection to the database
serverName = "www.plastikracing.net";
mydatabase = "web5_db1";
url = "jdbc:mysql://" + serverName + ":" + port + "/" + mydatabase; // a JDBC url
dbuser = "web5_u1";
dbpass = "password";
connection = DriverManager.getConnection (url, dbuser, dbpass);
}
catch (SQLException e)
{
// Could not connect to the database
JOptionPane.showMessageDialog (null,"<b>Unable to connect to the database.</b>\n\nPlease check your network connection."
+ "Contact the administrator if the problem persists.", "Connection Error",
JOptionPane.ERROR_MESSAGE);
System.out.println ("Error: " + e.getMessage ());
}
catch (ClassNotFoundException e)
{
// Could not find the database driver
System.out.println ("Error: " + e.getMessage ());
JOptionPane.showMessageDialog (null,"Could not find the database driver.", "Connection Error",
JOptionPane.ERROR_MESSAGE);
}
return connection;
}
}
and then in my other class that will "login" the user and get information about them I have:
try
{
ResultSet rs = null;
Statement stmt = Database.sqlConnection ().createStatement ();
String sql = "SELECT id,name,email,work,sort1,sort2,pos1,pos2 FROM users WHERE email = ? AND password = ?";
PreparedStatement pstmt = Database.sqlConnection ().prepareStatement (sql);
password = new String (t_pass.getPassword ());
// Set the values
pstmt.setString (1, t_email.getText ());
pstmt.setString (2, passHash (password));
rs = pstmt.executeQuery ();
if (rs.next ())
{
HubEvals.id = rs.getInt ("id");
HubEvals.name = rs.getString ("name");
HubEvals.email = rs.getString ("email");
HubEvals.work = rs.getInt ("work");
HubEvals.sort1 = rs.getInt ("sort1");
HubEvals.sort2 = rs.getInt ("sort2");
HubEvals.pos1 = rs.getInt ("pos1");
HubEvals.pos2 = rs.getInt ("pos2");
System.out.println (HubEvals.id);
System.out.println (HubEvals.name);
System.out.println (HubEvals.email);
System.out.println (HubEvals.work);
System.out.println (HubEvals.sort1);
System.out.println (HubEvals.sort2);
System.out.println (HubEvals.pos1);
System.out.println (HubEvals.pos2);
dialog.dispose ();
// Setup all the menus based on the user's account
enableMenus ();
HubEvals.setupRosterMenu ();
HubEvals.setupDocumentMenu ();
// Set the main panel visible
HubEvals.panelLayout ("main");
// Hide the login option and give them the option to logout
HubEvals.m_login.setVisible (false);
HubEvals.m_logout.setVisible (true);
}
else
{
JOptionPane.showMessageDialog (Login.this,"Login failed! Please check your e-mail address and password." , "Login Failure",
JOptionPane.INFORMATION_MESSAGE);
}
}
catch (SQLException e)
{
// Could not connect to the database
JOptionPane.showMessageDialog (null,"<b>Unable to connect to the database.</b>\n\nPlease check your network connection."
+ "Contact the administrator if the problem persists.", "Connection Error",
JOptionPane.ERROR_MESSAGE);
System.out.println ("Error: " + e.getMessage ());
}
I'm not closing the connection at all so there should always be a continuous connection right? And since I'm calling Database.sqlConnection () from the Database class, that method returns a Connection. So whenever I call that method it's using the connection already established and no making a new one correct?
