Hosted by:
Unit testing expertise at your fingertips!
Home | Discuss | Lists

Testing Stored Procs with JUnit

On an early XP project, our application was mandated to use stored procedures being developed by another group. It seemed that every time we integrated our Java with their PLSQL code, we found serious bugs in the fundamental behavior of their stored procedures. We were were writing automated tests using JUnit for our code and we were sure that writing unit tests for the stored procedures would help clarify the interface contract and would improve the quality of their code but we couldn't force the other team to write unit tests. Nor had utPLSQL even been invented.

We decided to try writing unit tests for the stored procedures in the xUnit family member we were comfortable with: JUnit. Since we had to write JDBC code to access the stored procedures anyway, we defined JUnit tests for each stored procedure via the JDBC PreparedStatement classes that we had built. The tests exercised the basic behavior of the stored behaviors and a few of the more obvious failure cases. Whenever we received a new version of the stored procedures, we would run the JUnit tests before even trying to exercise them from our application code. Needless to say, many of the tests failed.

We sat down with the developers building the stored procedures and showed them our tests and how they were failing left, right and center. Needless to say, they were a bit embarrassed but they agreed that our tests were correct. They went off to fix the stored procedures and gave us a new version to test. This fared somewhat better but still had some failurs. Then a very important thing happened: they asked to have a copy of the tests we had written and for instruction on how to run them for themselves. Before long, they were writing their own PLSQL unit tests in JUnit!

Page generated at Wed Feb 09 16:39:03 +1100 2011

Copyright © 2003-2008 Gerard Meszaros all rights reserved

All Categories
Introductory Narratives
Web Site Instructions
Code Refactorings
Database Patterns
DfT Patterns
External Patterns
Fixture Setup Patterns
Fixture Teardown Patterns
Front Matter
Result Verification Patterns
Test Double Patterns
Test Organization
Test Refactorings
Test Smells
Test Strategy
Value Patterns
XUnit Basics
xUnit Members
All "Sidebars"
Class - Instance Duality
Database as SUT API?
Faster Tests Without Shared Fixtures
Testing Stored Procs with JUnit
There's Always an Exception
Transaction Rollback Pain
Unit Test Rulz
Using Delta Assertions to Detect Data Leakage
What's in a (Pattern) Name?
Why Do We Need 100 Customers?