Testing Database Connection

[s penumudi]

I am not sure if this should be posted in OracleAs forum or in this forum.
Please move this post if you think it is inappropriate to post here.

I am trying to write test case for my Database class whose responsibility is to get the connection using datasource by lookingup JNDI.

I am using Oracle 10g AS and Oracl 9i database.

My problem is how will I be able to write testcase for getConnection method. As my Test script will be running out of Oracle application server scope, I need to provide JNDI properties temporarily.

Oracle provides documentation for JNDI properties J2EE client application.
for which I will have to write application-client.xml.

Now, test case not a J2ee client and I am not sure what i should be giving for JNDI properties.

I would appreciate if you help me in solving my problem.

FYI: I can write cactus testcase with incontainer testing where I don't have to specify JNDI properties, But I have some limitations with IDE i am using hence i cannot use Cactus.

It is kind of urgent.
Please respond

[Jeanne Boyarsky]

I had the same problem in WebSphere. I wound up just running the test case as a J2EE client.

[Lasse Koskela]

What exactly are you trying to prove by testing this Database class? Do you really need to connect to the real JNDI tree in order to prove that it works? I would be interested in seeing the code for the class you're trying to test.

[s penumudi]

The code I am trying to test is very simple.
I am trying to test if I am able to lookup the datasource and able to get the connection successfully.

Here is the code:

public Connection getConnection() throws AppException{
    java.sql.Connection con = null;
 
    try{
      
      /*Create the Initial context to look for the Datasource registered in JNDI*/
      InitialContext ctx = new InitialContext();
 
      /*Get the DataSource */
      /*The JNDI lookup returns the Object, typecast it to DataSource object*/
      javax.sql.DataSource ds = (javax.sql.DataSource)(ctx.lookup("jdbc/AppDS"));
 
      if(ds == null){
AppException se = new AppException();
se.setErrorCode("1000");
throw se;
      }
 
      /*Get the connection*/
      con = ds.getConnection();
 
      if(con == null){
AppException se = new AppException();
se.setErrorCode("1001");
throw se;
      }
    }catch(NamingException ne){
      logger.error(ne.getExplanation());
      AppException se = new AppException(ne);
      se.setErrorCode("1000");
      throw se;
    }catch (SQLException sqle) {
      /*store error message into Error log*/
      logger.error(sqle.getStackTrace().toString());
    }catch(AppException e){
      /*store error message into Error log*/
      logger.error(e.generateStackTraceString());
      throw new AppException(e);
    }
    /* return connection */
    return con;
  }

There is one more thing. If I am not able to test the this method out of application server, then I will end up testing all my other class that makes use of this class to be tested in Application server. I cannot write test cases for them. These classes are simple POJO which I am using as utility classes that gets decode table data from the database.

I would also appreciate if you could let me know if you find any problems with the above code. any suggestions for improvement will be great help to me.

Thank you and I really appreciate your replys.

[Lasse Koskela]

Originally posted by s penumudi:

public Connection getConnection() throws AppException{
    // ctx = new InitialContext();
    // ds = ctx.lookup("jdbc/AppDS");
    // return ds.getConnection();
}

So you actually don't want to test this getConnection() method but instead test some other class that uses this method to get a database connection?

If that's the case, you just need to mock the getConnection() method to return a Connection you've created in your test. If that's the case, it would be helpful to know a bit more about how the "client" code obtains an instance of this class that has the getConnection() method.

Originally posted by s penumudi:
There is one more thing. If I am not able to test the this method out of application server, then I will end up testing all my other class that makes use of this class to be tested in Application server. I cannot write test cases for them. These classes are simple POJO which I am using as utility classes that gets decode table data from the database.

What you need is mock objects. I wrote an article about unit testing database code a while back. It's not too detailed but you'd probably benefit from reading it through and checking out the links I provided in the "Resources" section.

[s penumudi]

Thank you Lasse Koskela,
Article was helpful.
But I am using different approach to get the connection.

Example:

public class DecodeUtility{
  public ArrayList getTypes() throws AppException{
 
    /*getConnection method is implemented in this class*/
    DBConnectionManager dbc = new DBConnectionManager();
 
    /*get the connection*/
    java.sql.Connection con = dbc.getConnection();
 
    /*create statement*/    
    java.sql.Statement st = con.createStatement();
 
    ..................................
    ..................................
  }
}

I would appreciate if you share any of your thoughts on how to go abt this.

Thank you

[Lasse Koskela]

Your problem stems from the fact that the DecodeUtility is doing too much. Each class should ideally be responsible for one thing and one thing only, and do it well. DecodeUtility, as it stands, seems to be responsible for 1) obtaining a database connection, 2) executing an SQL query and parsing the ResultSet, and 3) processing the data somehow (decoding?).

As a quick fix, I might suggest externalizing those things into separate methods like this:

public class DecodeUtility{
 
  protected Connection getConnection() {
    DbConnectionManager mgr = ...
  }
 
  protected ResultSet executeTheQuery() {
    Connection con = getConnection();
    Statement stmt = con.createStatement();
    ...
  }
 
  public ArrayList getTypes() throws AppException{
    // 1. delegate the database stuff to another method
    ResultSet rs = executeTheQuery();
    
    // 2. process the ResultSet
  }
}

Now, in your tests, you can fake the database part as follows:

Instead of this:

public void testDecodeUtility {
    DecodeUtility util = new DecodeUtility();
    List types = util.getTypes();
    assertListContainsWhatYouExpect();
}

...you do this:

public void testDecodeUtility {
    DecodeUtility util = new DecodeUtility() {
        protected ResultSet executeTheQuery() {
            MockResultSet mockResultSet = new MockResultSet();
            // make the MockResultSet return some hardcoded data
            return mockResultSet;
        }
    };
    List types = util.getTypes();
    assertListContainsWhatYouExpect();
}

Did that help at all?

[s penumudi]

Yes it helped me a lot.
I also see some advantages of using it.
executeTheQuery() should be taking query:string as a parameter. I guess for this class I can do this because all the methods in the decodeutility class are just simple select queries.

I guess I cannot use the same strategy for some of the queries where I want to run them in a loop where I will be using prepared statements.

I also want to test all possible SqlExceptions. Do you have any reference where I can get all the possible errorcodes for SQL like Duplicate record, connection cannot be established, too many parameters, etc.,

I really appreciate all your help.

[Lasse Koskela]

Originally posted by s penumudi:
I guess I cannot use the same strategy for some of the queries where I want to run them in a loop where I will be using prepared statements.

Sure you can. What makes you think that it couldn't be done? Would you like to post a code snippet again?

Originally posted by s penumudi:
I also want to test all possible SqlExceptions. Do you have any reference where I can get all the possible errorcodes for SQL like Duplicate record, connection cannot be established, too many parameters, etc.,

There is no standard list of error codes -- each database vendor has its own. Then again, if you'd like to do some hard labour, each vendor has also documented the meaning of their respective error codes in their documentation. It might take a while, though, to go through them all...

Why do you want to test for those different SQLExceptions? Are you really doing different things to recoup from different SQLExceptions?

I would generally recommend testing for just "any" SQLException, i.e. faking your executeQuery() method to always throw an SQLException and verifying that your code handled it gracefully.

[s penumudi]

I am sorry, your right. This strategy works very well. I was thinking something else.

I should be having seperate methods for getting data, insert data, to update and to delete.

The reason why I wanted to check error codes is, let's say that I am using batch updates where I will be deleting some data, updating some data and adding some data in one transaction.

When something fails, then I wanted to know what has failed? if add/update/delete?.

I thought If I know the error codes then I will be able to capture this information.

I guess with the strategy of having sperate methods for add/update/delete should suffice, where I will know which operation has failed.

LEt me know if you have better approach for this.
Thank you very much

[s penumudi]

May be I am not quitely sure on how to go about this.
With the approach of having sperate methods to get Connection, executeQuery and so on.. when I will be closing my connections, statements and resultset.

I guess I cannot close connection before I get data from the result set. This is how I have implemented. Please let me know what you think solution would be.

/*I have made this protected in order to override this method from my test class. I actually wanted it to be private.*/
protected Connection getConnection(){
  /*get the connection from the pool*/
  return con;
}
private Resultset executeQuery(String query)throws AppException{
   ResultSet rs = null;
    Connection con = null;
    Statement st = null;
    try{
      con = getConnection();
      st = con.createStatement();
      rs = st.executeQuery(query);
    }catch(AppException e){
      throw e;
    }catch(SQLException se){
      SafariException e = new SafariException(se);
      throw e;
    }finally{
      try{
        if(con!=null){
          con.close(); //I should not be doing this as I did not get data from resultset
        }
        if(st != null){
          st.close();  //I should not be doing this as I did not get data from resultset
        }
      }catch(SQLException se){
        logger.error("Error while closing:"+se);
      }
    }
    return rs;
}
 
public ArrayList getTypes(){
 Resultset rs = executeQuery(query);
 /*create arraylist of data from resultset*/
 /*return arraylist*/
 /*finally code resultset*/
}

You suggestion would be a great help to me.
Thank you
[ January 25, 2005: Message edited by: s penumudi ]

[Lasse Koskela]

Ah. That's right. So you'll have to pass in the Connection to the executeQuery() method:

// pseudo-code...
public class Foo {
 
    public Connection getConnection() { ... }
 
    public ResultSet executeQuery(Connection c) { ... }
 
    public void businessMethod() {
        Connection c = getConnection();
        executeQuery(c);
        c.close();
    }
 
}

[s penumudi]

Seems like I should be creating statement object in my business methods along with Connection and pass it as the parameter along with query, connection objects to executeQuery method.

Thank you