Programming Help

Web Development



What's Here?

  • Members: 150,038
  • Replies: 507,562
  • Topics: 80,012
  • Snippets: 2,668
  • Tutorials: 705
  • Total Online: 1,586
  • Members: 100
  • Guests: 1,486

Welcome to Dream.In.Code
Become a Java Expert!

Join 150,038 Java Programmers for FREE! Get instant access to thousands of Java experts, tutorials, code snippets, and more! There are 1,586 people online right now. Registration is fast and FREE... Join Now!




Serious JDBC issues

 
 Reply to this topicStart new topic

Serious JDBC issues, Problems with passing connection objects
nick2price
7 Jun, 2008 - 04:35 PM
Post #1

D.I.C Regular
***

Joined: 23 Nov, 2007
Posts: 337



Thanked: 12 times
My Contributions
I have one class, DatabaseUtils, which creates all the methods for my connection.
CODE
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DatabaseUtils
{
   public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
   public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
   public static final String USERNAME = "nick2price";
   public static final String PASSWORD = "hello";

   private Connection connection;

   public static void main(String[] args)
   {
      Connection connection = null;
      try
      {
         if (args.length > 0)
         {
            connection = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            DatabaseUtils utils = new DatabaseUtils(connection);

         }
         else
         {
            System.out.println("Usage: DatabaseUtils <sql query>");
         }
      }
      catch (Exception e)
      {
         e.printStackTrace();
      }
      finally
      {
         close(connection);
      }
   }

   public DatabaseUtils(Connection connection)
   {
      this.connection = connection;
   }

   public static Connection connect(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException
   {
      Class.forName(driver);
      return DriverManager.getConnection(url, username, password);
   }

   public static void close(Connection connection)
   {
      try
      {
         if (connection != null)
         {
            connection.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void close(Statement statement)
   {
      try
      {
         if (statement != null)
         {
            statement.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void close(ResultSet resultSet)
   {
      try
      {
         if (resultSet != null)
         {
            resultSet.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void rollback(Connection connection)
   {
      try
      {
         if (connection != null)
         {
            connection.rollback();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }
}


I then have another class create the connection
CODE
import java.sql.*;

class PersonService
{
    private PersonDAO personDao;
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
    public static final String USERNAME = "nick2price";
    public static final String PASSWORD = "hello";
  
    public PersonService(PersonInfo p)
    {
        Connection c = null;
        personDao = new PersonDAO();
        
        try
        {

            c = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            c.setAutoCommit(false);
            personDao.setConnection(c);
            personDao.savePerson(p);
            c.commit();
        }
        catch (Exception e)
        {
            DatabaseUtils.rollback(c);
            e.printStackTrace();
        }
        finally
        {
            DatabaseUtils.close(c);
        }
    }
}


My final class should take this connection through the setConnection method, and use it to execute a query.
CODE
import java.util.*;
import java.sql.*;

public class PersonDAO{
  
   private Connection con;
  
public void setConnection(Connection dbc)
{
this.con = dbc;
}

public void savePerson(PersonInfo person)
{
      try
        {
            
            String sql = "INSERT INTO Person(name, idNo, " +
                            "userName, passWord) VALUES (?,?,?,?) ";

            // Create a Preparedstatement
            PreparedStatement ps = con.prepareStatement(sql);
    
            ps.setString(1, person.getName());
            ps.setString(2, person.getIdNo());
            ps.setString(3, person.getUsername());
            ps.setString(4, person.getPassword());

            ps.executeUpdate();
        }
    
    catch(Exception e)
        {
            System.out.println(e);
            e.printStackTrace();
        }
    
}


I am being returned with a NullPointerException so somewhere i am making a mistake with the passing around of the connection. Can anyone see where i am going wrong?
Stack trace is looking like this:
java.lang.NullPointerException
java.lang.NullPointerException
at PersonDAO.savePerson(PersonDAO.java:22)
at Register.save(Register.java:196)
at Register.actionPerformed(Register.java:166)

Any help would be great. Cheers
User is offlineProfile CardPM
+Quote Post

pbl
RE: Serious JDBC Issues
7 Jun, 2008 - 08:03 PM
Post #2

D.I.C Lover
Group Icon

Joined: 6 Mar, 2008
Posts: 3,587



Thanked: 233 times
Dream Kudos: 75
My Contributions
What is a need for a connection if you close it right away ?

CODE

finally
      {
         close(connection);
      }


And

CODE

private Connection connection;    // <----- this will always be null

   public static void main(String[] args)
   {
      Connection connection = null;     // <---- because you create a new one in you try/catch group
      try
      {


This post has been edited by pbl: 7 Jun, 2008 - 08:05 PM
User is online!Profile CardPM
+Quote Post

nick2price
RE: Serious JDBC Issues
8 Jun, 2008 - 04:44 AM
Post #3

D.I.C Regular
***

Joined: 23 Nov, 2007
Posts: 337



Thanked: 12 times
My Contributions
I see what you mean by connection always being null. But if i remove
CODE
Connection connection = null;

from main, then it tells me that non static varible connection cannot be referenced from a static context.
I still cant see my error

This post has been edited by nick2price: 8 Jun, 2008 - 10:16 AM
User is offlineProfile CardPM
+Quote Post

nick2price
RE: Serious JDBC Issues
8 Jun, 2008 - 10:37 AM
Post #4

D.I.C Regular
***

Joined: 23 Nov, 2007
Posts: 337



Thanked: 12 times
My Contributions
In my PersonDao class, just before i carry out the prepared statement i have added the line
CODE
System.out.println("con is null? " + (con == null));


I am being returned back true, so this shows that it is to do with this class not recieving the connection object. I have tried your advice but still no change. Do you see any problem with the way i am passing the connection around?
User is offlineProfile CardPM
+Quote Post

pbl
RE: Serious JDBC Issues
8 Jun, 2008 - 05:23 PM
Post #5

D.I.C Lover
Group Icon

Joined: 6 Mar, 2008
Posts: 3,587



Thanked: 233 times
Dream Kudos: 75
My Contributions
QUOTE(nick2price @ 8 Jun, 2008 - 05:44 AM) *

I see what you mean by connection always being null. But if i remove
CODE
Connection connection = null;

from main, then it tells me that non static varible connection cannot be referenced from a static context.
I still cant see my error


Two ways of fixing the problem:

If you are going to have only one Connection in all your program
You can make

static Connection connection;

then your main() method will be able to access it an create a Connection.

Elewhere is your program if you want to access this connection you can always refer to:

DatabaseUtils.connection

and you don't have to pass it as parameter to your other methods:

CODE

public class DatabaseUtils
{
   public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
   public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
   public static final String USERNAME = "nick2price";
   public static final String PASSWORD = "hello";

   public static Connection connection;

   public static void main(String[] args)
   {
      try
      {
         if (args.length > 0)
         {
            connection = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            DatabaseUtils utils = new DatabaseUtils(connection);

         }
         else
         {
            System.out.println("Usage: DatabaseUtils <sql query>");
            return;
         }
      }
      catch (Exception e)
      {
         e.printStackTrace();
         return;
      }
////      finally
////      {
////         close(connection);
////      }
   }

///   public DatabaseUtils(Connection connection)
///   {
///      this.connection = connection;
///   }

   public static Connection connect(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException
   {
      Class.forName(driver);
      return DriverManager.getConnection(url, username, password);
   }

   public static void close()
   {
      if(connection == null)
        return;
      try
      {
            connection.close();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void close(Statement statement)
   {
      try
      {
         if (statement != null)
         {
            statement.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void close(ResultSet resultSet)
   {
      try
      {
         if (resultSet != null)
         {
            resultSet.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void rollback(Connection connection)
   {
      if(connection == null)
        return;
      try
      {
            connection.rollback();
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }
}


If you want to handle mutltiple connections then you will have to use another connection

CODE

public class DatabaseUtils
{
   public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
   public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
   public static final String USERNAME = "nick2price";
   public static final String PASSWORD = "hello";

   private Connection connection;

   public static void main(String[] args)
   {
      try
      {
         if (args.length > 0)
         {
            
            Connection con = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            DatabaseUtils utils = new DatabaseUtils(connection);

         }
         else
         {
            System.out.println("Usage: DatabaseUtils <sql query>");
         }
      }
      catch (Exception e)
      {
         e.printStackTrace();
      }
//      finally
//      {
//         close(con);
//      }
   }


But this kind of utils because your newly created object DatabaseUtils named util is out of scope (cannont be seen by anybody else);

And remove your close(con) because by closing that one you close also the one you created by calling the constructor with a Connection
User is online!Profile CardPM
+Quote Post

nick2price
RE: Serious JDBC Issues
8 Jun, 2008 - 05:49 PM
Post #6

D.I.C Regular
***

Joined: 23 Nov, 2007
Posts: 337



Thanked: 12 times
My Contributions
i have changed it a bit since then. I have removed the main from DatabaseUtils so that it only contains connection methods.
CODE
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;


public class DatabaseUtils
{
   public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
   public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
   public static final String USERNAME = "nick2price";
   public static final String PASSWORD = "hello";

   private Connection connection;


   public static Connection connect(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException
   {
      Class.forName(driver);
      return DriverManager.getConnection(url, username, password);
   }

   public static void close(Connection connection)
   {
      try
      {
         if (connection != null)
         {
            connection.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void close(Statement statement)
   {
      try
      {
         if (statement != null)
         {
            statement.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void close(ResultSet resultSet)
   {
      try
      {
         if (resultSet != null)
         {
            resultSet.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void rollback(Connection connection)
   {
      try
      {
         if (connection != null)
         {
            connection.rollback();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }
}




This is where i am getting stuck. Now i use the connect method in this class
CODE
import java.sql.*;

class PersonService
{
    private PersonDAO personDao;
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
    public static final String USERNAME = "nick2price";
    public static final String PASSWORD = "hello";
  
    public PersonService(PersonInfo p)
    {
        Connection c = null;
        personDao = new PersonDAO();
        
        try
        {

            c = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            c.setAutoCommit(false);
            personDao.setConnection(c);
            personDao.savePerson(p);
            c.commit();
        }
        catch (Exception e)
        {
            DatabaseUtils.rollback(c);
            e.printStackTrace();
        }
    }
}


Now that connection should be sent too personDao.setConnection©; which is this method
CODE
public class PersonDAO{
  
   private Connection con;
  
public void setConnection(Connection dbc)
{
this.con = dbc;
}


So now the above method should have given the connection to the variable con in my personDAO class? But then when i try and use this in my savePerson method, there is no connection. Where have i lost my connection. i should be doing this
DatabaseUtils class:-creates connection methods
PersonService class:-creates connection and sends it to personDAO class.
PersonDAO class:- gets connection from PersonService class and uses this connecion to execute a query.

Can you see where the connection is lost?
User is offlineProfile CardPM
+Quote Post

pbl
RE: Serious JDBC Issues
8 Jun, 2008 - 06:02 PM
Post #7

D.I.C Lover
Group Icon

Joined: 6 Mar, 2008
Posts: 3,587



Thanked: 233 times
Dream Kudos: 75
My Contributions
QUOTE(nick2price @ 8 Jun, 2008 - 06:49 PM) *

i have changed it a bit since then. I have removed the main from DatabaseUtils so that it only contains connection methods.
CODE
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;


public class DatabaseUtils
{
   public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
   public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
   public static final String USERNAME = "nick2price";
   public static final String PASSWORD = "hello";

   private Connection connection;


   public static Connection connect(String driver, String url, String username, String password) throws ClassNotFoundException, SQLException
   {
      Class.forName(driver);
      return DriverManager.getConnection(url, username, password);
   }

   public static void close(Connection connection)
   {
      try
      {
         if (connection != null)
         {
            connection.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void close(Statement statement)
   {
      try
      {
         if (statement != null)
         {
            statement.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void close(ResultSet resultSet)
   {
      try
      {
         if (resultSet != null)
         {
            resultSet.close();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }

   public static void rollback(Connection connection)
   {
      try
      {
         if (connection != null)
         {
            connection.rollback();
         }
      }
      catch (SQLException e)
      {
         e.printStackTrace();
      }
   }
}




This is where i am getting stuck. Now i use the connect method in this class
CODE
import java.sql.*;

class PersonService
{
    private PersonDAO personDao;
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
    public static final String USERNAME = "nick2price";
    public static final String PASSWORD = "hello";
  
    public PersonService(PersonInfo p)
    {
        Connection c = null;
        personDao = new PersonDAO();
        
        try
        {

            c = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            c.setAutoCommit(false);
            personDao.setConnection(c);
            personDao.savePerson(p);
            c.commit();
        }
        catch (Exception e)
        {
            DatabaseUtils.rollback(c);
            e.printStackTrace();
        }
    }
}


Now that connection should be sent too personDao.setConnectionŠ; which is this method
CODE
public class PersonDAO{
  
   private Connection con;
  
public void setConnection(Connection dbc)
{
this.con = dbc;
}


So now the above method should have given the connection to the variable con in my personDAO class? But then when i try and use this in my savePerson method, there is no connection. Where have i lost my connection. i should be doing this
DatabaseUtils class:-creates connection methods
PersonService class:-creates connection and sends it to personDAO class.
PersonDAO class:- gets connection from PersonService class and uses this connecion to execute a query.

Can you see where the connection is lost?


Without seeing the method PersonDAO.savePerson(PersonInfo p) I cannot speculate on what wrong you are doing
And just to make your code clearer (and avoid the setConnection() method why don't you pass the Connection into the constructor of PersonDAO ?

CODE

class PersonService
{
    private PersonDAO personDao;
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
    public static final String USERNAME = "nick2price";
    public static final String PASSWORD = "hello";
  
    public PersonService(PersonInfo p)
    {
        
        try
        {

            Connection c = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            c.setAutoCommit(false);
            personDao = new PersonDAO(c);
            personDao.savePerson(p);
            c.commit();
        }
        catch (Exception e)
        {
            DatabaseUtils.rollback(c);
            e.printStackTrace();
        }
    }
}


public class PersonDAO{
  
   private Connection con;
  
   public PersonDao(Connection dbc)
   {
        this.con = dbc;
    }
User is online!Profile CardPM
+Quote Post

nick2price
RE: Serious JDBC Issues
8 Jun, 2008 - 06:16 PM
Post #8

D.I.C Regular
***

Joined: 23 Nov, 2007
Posts: 337



Thanked: 12 times
My Contributions
You can see my savePerson() in my original post. I have put the connection in the personDAO constructor. Now how would pass this constructor the connection from my PersonService class?
CODE
        try
        {

            c = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            c.setAutoCommit(false);
            personDao(c);  //it wont work like a method e.g. PersonDAO.personDao(c)
            c.commit();
        }
User is offlineProfile CardPM
+Quote Post

pbl
RE: Serious JDBC Issues
8 Jun, 2008 - 06:53 PM
Post #9

D.I.C Lover
Group Icon

Joined: 6 Mar, 2008
Posts: 3,587



Thanked: 233 times
Dream Kudos: 75
My Contributions
Oups... your code is not easy to follow:

CODE

class PersonService
{
    private PersonDAO personDao;
    public static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    public static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
    public static final String USERNAME = "nick2price";
    public static final String PASSWORD = "hello";
  
    public PersonService(PersonInfo p)
    {
        Connection c = null;
        personDao = new PersonDAO();
        
        try
        {

            c = DatabaseUtils.connect(DRIVER, URL, USERNAME, PASSWORD);
            c.setAutoCommit(false);
            personDao.setConnection(c);
            personDao.savePerson(p);
            c.commit();
        }
        catch (Exception e)
        {
            DatabaseUtils.rollback(c);
            e.printStackTrace();
        }
        finally
        {
            DatabaseUtils.close(c);
        }
    }
}


Are you telling me that... every time you will want to save a PersonInfo in your database you will:

- create a new PersonService object
- that will create a new connection to the database
- turn autoCommit to false (even if later you will just insert a row)
- create a new PersonDAO object
- set the cconnection handle to this PersonDAO object
- PersonDAO object will created a PrepareStatement
- PersonDAO object will perform the SQL Insert
- do a commit (just for 1 row but auto commit was at off)
- perform a rollBack if the commit doesn't work
- close the connection meaning that the just created PersonServices and PersonDAO objects are now useless
(including its prepareStatement what is the use of making a PrepareStatement if it is used only one time ?)

Not a good idea at all

You need to seriously review your design

You PersonDAO should look like that:

CODE

class PersonDAO {
     static Connection con;                   // connection used by all PersonDAO
     static PrepareStatement insert;      // one prepareStement for all PersonDAO
     // init these static fields
     static {
        try {
             con = .......;
             insert = con.prepareStatement("Insert into ....... ?, ?, ?, ?");
        }
        catch(SQLException e) {
              Display error or throw new IllegalStateException()
        }
      }

      // make it synchronized if if can be called by more than one thread
      static synchonized boolean insert(PersonalInfo p) {
          try {
                insert.setString(1, p....
                insert.setString(2, p....
                ps.executeUpdate();
          }
          catch(SQLException e) {
                System.out.println("PersonDao error: " + e);
                return false;
          }
          return true;
      }
}


Now you don't even need PersonService anymode
where you were creating one just do:
CODE

        if(PersonDAO.insert(p))
           System.out.println("It worked");
        else
            System.out.println("It failed");
    }
User is online!Profile CardPM
+Quote Post

nick2price
RE: Serious JDBC Issues
8 Jun, 2008 - 07:25 PM
Post #10

D.I.C Regular
***

Joined: 23 Nov, 2007
Posts: 337



Thanked: 12 times
My Contributions
Really, what i have shown you is part of a bigger picture. So far i have in total 7 classes. Each time i will need to create a personService object because this is being used for a register/login form. So the connection will depend on the user choosing to register, not me manually inputting information. Also, i was told it is bad coding creating the connection in the PersonDAO class because the DAO can't know if it's part of a larger transaction. That's up to that other class, which is really a service.
User is offlineProfile CardPM
+Quote Post

pbl
RE: Serious JDBC Issues
8 Jun, 2008 - 07:31 PM
Post #11

D.I.C Lover
Group Icon

Joined: 6 Mar, 2008
Posts: 3,587



Thanked: 233 times
Dream Kudos: 75
My Contributions
Then, you PersonDAO can implement others static methods like Delete and Update all sharing the same con

CODE

class PersonDAO {
     static Connection con;    // connection used by all PersonDAO
     static PreparedStatement insert;      // one prepareStement for all PersonDAO
                 static PreparedStatement delete;
                 static PreparedStatement update;
     // init these static fields
     static {
        try {
             con = .......;
             insert = con.prepareStatement("Insert into ....... ?, ?, ?, ?");
                                                 delete = con.prepareStatement("Delete from ... where ... = ?");
                                                 update = con.prepareStatement("Update (....,...., ) from ... where....");
        }
        catch(SQLException e) {
              Display error or throw new IllegalStateException()
        }
      }

      // make it synchronized if if can be called by more than one thread
      static synchonized boolean insert(PersonalInfo p) {
          try {
                insert.setString(1, p....
                insert.setString(2, p....
                ps.executeUpdate();
          }
          catch(SQLException e) {
                System.out.println("PersonDao error on insert: " + e);
                return false;
          }
          return true;
      }
      static synchonized boolean delete(PersonalInfo p) {
          try {
                delete.setString(1, p....
                ps.executeUpdate();
          }
          catch(SQLException e) {
                System.out.println("PersonDao error on delete: " + e);
                return false;
          }
          return true;
      }
      static synchonized boolean update(PersonalInfo p) {
          try {
                update.setString(1, p....
                update.setString(2, p....
                ps.executeUpdate();
          }
          catch(SQLException e) {
                System.out.println("PersonDao error on update: " + e);
                return false;
          }
          return true;
      }

}