wood burning stoves 2.0*
The moose likes Testing and the fly likes Testing Database Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Engineering » Testing
Bookmark "Testing Database Connection" Watch "Testing Database Connection" New topic
Author

Testing Database Connection

s penumudi
Ranch Hand

Joined: Nov 17, 2004
Posts: 113
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30116
    
150

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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.


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
s penumudi
Ranch Hand

Joined: Nov 17, 2004
Posts: 113
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:


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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
Originally posted by s penumudi:

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
Ranch Hand

Joined: Nov 17, 2004
Posts: 113
Thank you Lasse Koskela,
Article was helpful.
But I am using different approach to get the connection.

Example:


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

Thank you
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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:

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

Instead of this:

...you do this:

Did that help at all?
s penumudi
Ranch Hand

Joined: Nov 17, 2004
Posts: 113
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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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
Ranch Hand

Joined: Nov 17, 2004
Posts: 113
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
Ranch Hand

Joined: Nov 17, 2004
Posts: 113
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.



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

Joined: Jan 23, 2002
Posts: 11962
    
    5
Ah. That's right. So you'll have to pass in the Connection to the executeQuery() method:
s penumudi
Ranch Hand

Joined: Nov 17, 2004
Posts: 113
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
 
jQuery in Action, 2nd edition
 
subject: Testing Database Connection
 
Similar Threads
Unit testting for Struts application -JUnit or Cactus?
problem in creating connection using cactus/junit in weblogic
Non J2EE transactional management in Java
Unit testing J2EE components that use WSJdbcDataSource
benefits of cactus?