[depricated]

I'm working on a class which will basically act to handle database connections to MySQL. The point is to be able to save the connection information and automatically resume(reestablish) the connection when the program starts up. That all works well and good, and ironically the part I'm having trouble with is maintaining the connection while the program is running.

Basically what I want to do is tap into a database when the program starts and keep the connection open to run queries on it with said program. A simple tool, really. Here are the important functions:

public class MySQLConnection implements Serializable
{
    private Connection _con;

    GUI gui;

    public void open()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            gui.cli("Driver loaded. . .");
            String pass = new String(_pass);
            String url = "jdbc:mysql://" + _host + ":3306/" + _name;
            _con = DriverManager.getConnection(url, _user, pass);
            gui.cli("Connection established to " + url + " . . .");
            save();
        }
        catch(ClassNotFoundException e)
        {
            gui.cli(e.toString());
        }
        catch(SQLException e)
        {
            gui.cli(e.toString());
        }
    }
    
    public void close()
    {
        if(_con != null)
        {
            try
            {
                _con.close();
                gui.cli("Abolished connection to " + _host);
            }
            catch(SQLException e)
            {
                gui.cli(e.toString());
            }
            catch(Exception e)
            {
                gui.cli(e.toString());
            }
        }
    }
}

There's obviously more to the class, like save, load, constructors, etc - again this is just the part that works with the Connection

It connects just fine, no problem, but when I call close(even if I call it immediately) the Connection is already null. If I take out the check I even get a Null Pointer exception, so it looks like _con is closing itself as soon as the function finishes. I didn't think this would happen since the variable's scope is at the class level.

Anyone familiar with this conundrum, know a way around this?

[EdwinNameless]

What does the save method do exactly? I haven't played with MySQL in a good while, but I don't think the connection would get closed behind your back...

[nick2price]

Take a try with my database utilities class, it provides methods relating to databases

import java.sql.Connection;  //the importing of required libraries
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/**This class contains all methods which relate to database connection, closing and exception handling*/
public class DatabaseUtils
{   
    /**method which returns a connection*/
	public static Connection connect(String driver, String url) throws ClassNotFoundException, SQLException 
	{
		Class.forName(driver); 
		return DriverManager.getConnection(url);  
	}
	
	/**Method which closes a connectio*/
	public static void close(Connection connection)  
	{
		try  //try block (try doing this)
		{
			if (connection != null) 
			{
				connection.close(); 
			}
		
		}
		catch (SQLException e)  
		{
			e.printStackTrace(); 
		}
		
	}
	
	/**Method which closes a ResultSet*/
	public static void close(ResultSet resultSet)  
	{
		try  //try
		{
			if (resultSet != null)  //if resultset not equal to null
			{
				resultSet.close(); //close it
			}
		}
		catch (SQLException e)  //catch
		{
			e.printStackTrace();
		}
	}
	
	/**Method which closes a Statement*/
	public static void close(Statement statement)  
	{
		try
		{
			if (statement != null)
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	/**Method which rolls back the connection*/
	public static void rollback(Connection connection) 
	{
		try
		{
			if (connection != null)
			{
				connection.rollback();
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}	
}
 

Then in the class you want to perform your quiries, declare your local variables. YOu need to change them accordingly.

private Connection con = null;   
	
public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver"; 
public static final String URL = "jdbc:odbc:Competitor";    

then whereever you need a connection, do

con = DatabaseUtils.connect(DRIVER, URL); 

and then when you finish, just call the close method passing it the connection

DatabaseUtils.close(con); 

Need any advise, just give a shout out

[depricated]

thanks nick

I implemented(and slightly modified, just changed the db connection function to accept username and password also) the class you gave me. That seems to work as intended and probably just fine, but I receive the same problem, which leads me to believe it's something with my class.

Doing some more work on this, but that DBUtils class is great. Thank you!

import java.sql.*;
import java.io.*;
import java.util.zip.*;
/**
 *
 * @author mcherry
 */
public class MySQLConnection implements Serializable
{
    private String _driver = "com.mysql.jdbc.Driver"; //The location of the Connection/J Driver
    private String _host; // The Host Address of the Database
    private String _name; // The Name of the Database
    private String _user; // The Username for accessing the Database
    private char[] _pass; // The password for accessing the Database
    private Connection _con = null;
    private GUI _gui;

    /**
     * @return the _host
     */
    public MySQLConnection(GUI gui, String host, String name, String user, char[] pass)
    {
        _gui = gui;
        
        _host = host;
        _name = name;
        _user = user;
        _pass = pass;
    }
    public MySQLConnection(GUI gui)
    {
        //creates an empty connection with just a gui attached
        _gui = gui;
    }

    private MySQLConnection()
    {
        //creates an empty connection
        //this is a private constructor
        //because we never want to create
        //a null connection except to save
    }


    public String getHost() {
        return _host;
    }

    /**
     * @param host the _host to set
     */
    public void setHost(String host) {
        this._host = host;
    }

    /**
     * @return the _name
     */
    public String getName() {
        return _name;
    }

    /**
     * @param name the _name to set
     */
    public void setName(String name) {
        this._name = name;
    }

    /**
     * @return the _user
     */
    public String getUser() {
        return _user;
    }

    /**
     * @param user the _user to set
     */
    public void setUser(String user) {
        this._user = user;
    }

    /**
     * @return the _pass
     */
    public char[] getPass() {
        return _pass;
    }

    /**
     * @param pass the _pass to set
     */
    public void setPass(char[] pass) {
        this._pass = pass;
    }

    /**
     * @return the con
     */
    public Connection getConnection() {
        return _con;
    }

    public void open()
    {
        try
        {
            Class.forName(_driver);
            _gui.cli("Driver loaded. . .");
            String pass = new String(_pass);
            String url = "jdbc:mysql://" + _host + ":3306/" + _name;
            _con = DBUtils.connect(_driver, url, _user, pass);
            if(_con != null)
            {
                _gui.cli("Connection established to " + url + " . . .");
                save();
            }
        }
        catch(ClassNotFoundException e)
        {
            _gui.cli(e.toString());
        }
        catch(SQLException e)
        {
            _gui.cli(e.toString());
        }
    }
    
    public void close()
    {
        if(_con != null)
        {
            DBUtils.close(_con);
            _gui.cli("Abolished connection to " + _host);
        }
    }

    public void save()
    {

        //creating a copy of this connection without a connection and without a gui
        //in short, we simply copy private vars to a new MySQLConnection
        MySQLConnection c = new MySQLConnection();
        c.setHost(_host);
        c.setName(_name);
        c.setUser(_user);
        c.setPass(_pass);

        try
        {
            FileOutputStream fout = new FileOutputStream("csijdbcsql.con"); //save to file
            GZIPOutputStream gzout = new GZIPOutputStream(fout); //compress
            ObjectOutputStream out = new ObjectOutputStream(gzout); //serialize this


            out.writeObject(c);
            out.flush();
            out.close();
        }
        catch(IOException e)
        {
            System.out.println(e);
        }
    }

    public boolean load()
    {
        File f = new File("csijdbcsql.con");
        if(f.exists())
        {
            try
            {
                FileInputStream fin = new FileInputStream("csijdbcsql.con");
                GZIPInputStream gzin = new GZIPInputStream(fin);
                ObjectInputStream in = new ObjectInputStream(gzin);

                MySQLConnection c = (MySQLConnection)in.readObject();
                in.close();
                _gui.cli("Loading last successful connection parameters.");
                _host = c.getHost();
                _name = c.getName();
                _user = c.getUser();
                _pass = c.getPass();
                _con = null;
                
                return true;
            }
            catch(IOException e)
            {
                System.out.println(e);
                return false;
            }
            catch(ClassNotFoundException e)
            {
                System.out.println(e);
                return false;
            }
        }
        else
        {
            return false;
        }
    }
}

Here's the full class, as I'm currently using it. This is where I'm still getting the problem of the db not remaining opened.

I've tested the DB in PHP and can maintain an open connection there without a problem, so it appears to be java-related and not the db itself.

This post has been edited by depricated: 08 February 2010 - 11:15 PM