JDBC class check

[DavidBainbridge]

Hi,

I have got worked a java class which connects to a database and gets information out. It works fine. My problem is trying to make the code "object oriented" and split it up so I dont have to copy and paste the connection information for each and every class. I simply want a connection class.

I have created the following but is it the right way to go about it?

Any suggestions / tips?

Thanks!

Main Class

import java.sql.*;
import java.util.Properties;

private Statment stmt = null;
private ResultSet rs = null;

public static void main(String[] args) throws Exception
{
	Connect Financedb = new Connect();
	
	stmt = Financedb.getConnection(con.createStatement());
	
	String SQLStatement = "SELECT * FROM person;";
	
	rs = stmt.executeQuery(SQLStatement);

	rs.next();
	
 	String name = rs.getString("person_name");
	System.out.println(name)

	Financedb.closeConnection();
}

Connect Class

import java.sql.*;
import java.util.Properties;

public class Connect
{
	private static final String dbClassName = "com.mysql.jdbc.Driver";
	private static final String connectionurl = "jdbc:mysql://127.0.0.1/finance";
     	private java.sql.Connection con = null;
	
	private java.sql.Connection getConnection() throws ClassNotFoundException,SQLException
	{
		Class.forName(dbClassName);
    		Properties p = new Properties();
    		p.put("user","<user>");
    		p.put("password","<password>");
    		con = DriverManager.getConnection(connectionurl,p);

		return con
	}

     	private void closeConnection()
	{
        	if(con!=null)
		{
        		con.close();
        	}
		con=null;
	}
}

[DavidBainbridge]

I have managed to work it out to the following but I get a NullPointerException error. If Any one had any ideas or suggestions then please let me know!

Error

run:
java.lang.NullPointerException
        at connect.getResults(connect.java:45)
        at Finance.main(Finance.java:13)
Exception in thread "main" java.lang.NullPointerException
        at Finance.main(Finance.java:15)
Java Result: 1
BUILD SUCCESSFUL (total time: 1 second)

Connector Class

import java.sql.*;
import java.util.Properties;

public class connect{

    private static final String dbClassName = "com.mysql.jdbc.Driver";
    private static final String connectionurl = "jdbc:mysql://127.0.0.1/finance";
    private static Connection con = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;
    private Properties p = new Properties();

     // Constructor
     public connect()
     {

     }


    private java.sql.Connection getConnection()
    {
        try
        {
            p.put("user",<user>);
            p.put("password",<password>);

            Class.forName(dbClassName);
            con = DriverManager.getConnection(connectionurl,p);
            if(con!=null) System.out.println("Connection Successful!");
        }
        catch(Exception e)
        {
            e.printStackTrace();
            System.out.println("Error Trace in getConnection() : " + e.getMessage());
        }
        return con;
    }

    public java.sql.ResultSet getResults()
    {
        try
        {
            String SQLStatement = "SELECT * FROM person;";

            rs = stmt.executeQuery(SQLStatement);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        closeConnection();

        return rs;
    }


    public void displayDbProperties()
    {

        java.sql.DatabaseMetaData dm = null;
        java.sql.ResultSet rs = null;
        try
        {
            con= this.getConnection();
            if(con!=null)
            {
                dm = con.getMetaData();
                System.out.println("Driver Information");
                System.out.println("\tDriver Name: "+ dm.getDriverName());
                System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
                System.out.println("\nDatabase Information ");
                System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
                System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
                System.out.println("Avalilable Catalogs ");
                rs = dm.getCatalogs();
                
                while(rs.next())
                {
                    System.out.println("\tcatalog: "+ rs.getString(1));
                }

                rs.close();
                rs = null;
                closeConnection();
            }
            else System.out.println("Error: No active Connection");

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        dm=null;
    }

    private void closeConnection()
    {
        try
        {
            if(con!=null)
            con.close();
            con=null;
        }

        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

Main Class

import java.sql.*;
import java.util.Properties;

public class Finance
{

    private static ResultSet rs = null;

    public static void main(String[] args) throws Exception
    {
        connect myDbTest = new connect();

        rs = myDbTest.getResults();

        rs.next();

        String name = rs.getString("person_name");

        System.out.println(name);
    }

}

[DavidBainbridge]

Never mind I solved it myself! For anyone wondering the correct code is:

Connection Class

import java.sql.*;
import java.util.Properties;

public class connect{

    private static final String dbClassName = "com.mysql.jdbc.Driver";
    private static final String connectionurl = "jdbc:mysql://127.0.0.1/finance";
    private static Connection con = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;
    private Properties p = new Properties();

     // Constructor
     public connect()
     {

     }


    private java.sql.Connection getConnection()
    {
        try
        {
            p.put("user",<user>);
            p.put("password",<password>);

            Class.forName(dbClassName);
            con = DriverManager.getConnection(connectionurl,p);
            if(con!=null) System.out.println("Connection Successful!");
        }
        catch(Exception e)
        {
            e.printStackTrace();
            System.out.println("Error Trace in getConnection() : " + e.getMessage());
        }
        return con;
    }

    public java.sql.ResultSet getResults()
    {
        try
        {
            con= this.getConnection();
            stmt = con.createStatement();

            String SQLStatement = "SELECT * FROM person;";

            rs = stmt.executeQuery(SQLStatement);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return rs;
    }


    public void displayDbProperties()
    {

        java.sql.DatabaseMetaData dm = null;
        java.sql.ResultSet rs = null;
        try
        {
            con= this.getConnection();
            if(con!=null)
            {
                dm = con.getMetaData();
                System.out.println("Driver Information");
                System.out.println("\tDriver Name: "+ dm.getDriverName());
                System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
                System.out.println("\nDatabase Information ");
                System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
                System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
                System.out.println("Avalilable Catalogs ");
                rs = dm.getCatalogs();
                
                while(rs.next())
                {
                    System.out.println("\tcatalog: "+ rs.getString(1));
                }

                rs.close();
                rs = null;
                closeConnection();
            }
            else System.out.println("Error: No active Connection");

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        dm=null;
    }

    private void closeConnection()
    {
        try
        {
            if(con!=null)
            con.close();
            con=null;
        }

        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

Main Class

import java.sql.*;

public class Finance
{

    private static ResultSet rs = null;

    public static void main(String[] args) throws Exception
    {
        connect myDbTest = new connect();

        rs = myDbTest.getResults();
        rs.next();

        String name = rs.getString("person_name");

        System.out.println(name);
    }

}

[emceenugget]

Looks pretty good. You could try encapsulating all sql stuff into the connection class and having the methods returning your own bean class as well, instead of accessing your ResultSet outside. Not better or required or necessarily advised, but it does provide a little more practice in making things "object oriented"

[DavidBainbridge]

Looks pretty good. You could try encapsulating all sql stuff into the connection class and having the methods returning your own bean class as well, instead of accessing your ResultSet outside. Not better or required or necessarily advised, but it does provide a little more practice in making things "object oriented"

Whats the best way of going about this?

My only concern would be that the recordset data is different for each select call.

What would I return from the connection class? If its raw data (strings and ints etc) I would need to have a connect method for each data call?!?

Is there an easier way to do it?