Database testing using Generic Fixture

As I have mentioned in my Introduction to Generic Fixture post that we can use Generic Fixture to write acceptance tests to validate almost any type of Java application. In my previous posts I demonstrated how to make use of Generic Fixture to write automated web tests and testing EJBs. Let’s now see how to use Generic Fixture for writing queries against an Oracle database.

We shall keep the database connection parameters in a separate SetUp page as well as the connection closing code in a separate TearDown page. After that we shall write our query in main test page. That way we can utilize SetUp and TearDown pages in all of our test pages without repeating that code again and again in for each test.

I am assuming that your FitNesse server process is running on localhost on post 8000. If that is not the case then please change the URLs accordingly.

To test a database we shall need the database vendor supplied JDBC driver to be able to make JDBC calls. In this example since we are using Oracle we are going to need Oracle’s JDBC driver jar file. You can place your JDBC driver jar file anywhere on your system where FitNesse server is running and give the path in !path directive of FitNesse.

Open a browser and enter this URL: http://localhost:8000/FitNesse.OracleSuite
Since this page doesn’t already exist, it will give you a link “create this page”, go ahead the click on this link to create a new page. Enter following text in the new page’s source.

!*> Class Path
!path ojdbc14.jar
 * >SelectQuery

Please note that here I kept JDBC driver jar file (ojdbc14.jar) in FitNesse install directory therefore I could give this directive !path ojdbc14.jar. If you want to keep ojdbc14.jar at some other path then just provide your path here using !path /your/path/to/ojdbc14.jar

Save your changes. Now FitNesse will show question marks ? links next to >SetUp, >TearDown and >SelectQuery text, since these pages do not already exist. Go ahead and click on the ? link next to >SetUp text and enter following text in the new page’s source.

'''Connect to Database'''
!| Generic Fixture | oracle.jdbc.pool.OracleDataSource |
| setDriverType | thin |
| setServerName | localhost |
| setPortNumber | 1521 |
| setDatabaseName | testdb |
| setUser | scott |
| setPassword | tiger |
| conn=getConnection |

Customize database connection parameters above to suite your environment. Save your changes. Now click on the ? link next to >TearDown text and enter following text in the new page’s source.

'''Close the Statement'''
!| Generic Fixture | stmt= |
| close |

'''Close the Connection'''
!| Generic Fixture | conn= |
| close |
| is closed | | true |

Save your changes. Now click on the ? link next to >SelectQuery text and enter following text in the new page’s source.

'''Prepare your query'''
!| Generic Fixture | conn= |
| stmt=prepareStatement | select  age, title from emp where id = ? |

'''Execute your query'''
!| Generic Fixture | stmt= |
| setInt | 1 | 1234 |
| resultSet=executeQuery |

'''Examine the Result Set'''
!| Generic Fixture | resultSet= |
| meta=getMetaData |
| meta=.getColumnCount | | 2 |
| next |
| getInt | age | | 30 |
| getString | title | | Sr Manager |

Save your changes. Click on properties and check the Test check-box to make FitNesse.OracleSuite.SelectQuery a test page. Click on “Save Properties”.

Click on the Test button in top left menu to run your database query and view the results. Here in above test I am expecting result of select query to be 30, Sr Manager. FitNesse will mark these 2 cells green or red depending upon the actual query results.

Even though I took Oracle as database in my example above but Generic Fixture is not tied with any database. You can drop the jar file of any JDBC driver and set the classpath as instructed above to test any database. I have personally used Oracle, Sybase and MySQL in my testing without any problems.

So you can see how easy it is to do database testing using Generic Fixture without ever writing a single line of Java code.

12 thoughts on “Database testing using Generic Fixture

  1. Hi Kiran,

    In your code I didn’t see GenericFixture any where? Are you even using this fixture or want help on something else?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s