Hi,
I have a method I want to write a unit test for. It returns a PreparedStatement. How do I do this? How can I analyse a PreparedStatement making sure all the values are right or is this impossible cos its already compiled?
I am using a JDBC driver for a SQLite database and the toString() method simply returns
String "lStmt.toString()"= "org.sqlite.PrepStmt@145c859"
Thanks
Message was edited by:
megasteo
> I have a method I want to write a unit test for. It
> returns a PreparedStatement.
Hopefully the scope of the connection is outside that of the prepared statement, otherwise that code is wrong.
Other than that the way I unit test database code is to run it. Not sure how else you would unit test anything. That means that it must hit a database.
Hi,
Thanks for the response. I dont want to run the code off the database because then the test is Dependant on that external resource and with unit testing I ONLY want to analyse the 15 or so lines that generate the prepared statement. How I've gotten around it is to use JMock - mock the database connection and tell the method PrepareStatement to return a mock preparedstatement
Mock lMockDbConnection = mock(Connection.class);
Mock lMockPreparedStatement = mock(PreparedStatement.class);
//The Database Connection will generate our prepared statement
lMockDbConnection.expects(once()).method("prepareStatement").will(returnValue((PreparedStatement)lMockPreparedStatement.proxy()));
On the mock preparedstatement Ive written a constraint to analyse the data being input for each of setBytes or setString or whatever I'm using.
lMockPreparedStatement.expects(once()).method("setBytes").with(eq(4), policyBlobConstraint());
Well, from a testing purity perspective, I think your efforts are noble. However, unit testing having a dependency on a database is usually tolerated. I normally only mock container dependencies (ala MockEJB, etc.) There is a middle ground, if you have written completely ANSI-compliant SQL: use something like HSQLDB which operates in memory and will return "real" prepared statements for you to test your bind variables on.
- Saish
> Hi,
> Thanks for the response. I dont want to run the code
> off the database because then the test is Dependant
> on that external resource
Any test is dependent on any number of external resources such as the VM, the OS, file system, unit test tool, etc.
> and with unit testing I
> ONLY want to analyse the 15 or so lines that generate
> the prepared statement. How I've gotten around it is
> to use JMock - mock the database connection and tell
> the method PrepareStatement to return a mock
> preparedstatement
>
Ok. And are you using a SQL parser? If not how are you going to verify that the syntax is correct? And if there are any non-standard extensions in use how are you going to verify those?
> Any test is dependent on any number of external
> resources such as the VM, the OS, file system, unit
> test tool, etc.
Ok, but I'm making a number of realistic assumptions that I can rely on these things. I want to test my block of code such that when the test runs, it will not have varying results based on whether or not a database is available or not.
> Ok. And are you using a SQL parser? If not how are
> you going to verify that the syntax is correct? And
> if there are any non-standard extensions in use how
> are you going to verify those?
I want to validate that the value I specified in setInt(1, theValue) or setByte etc etc are correct. I do not intent to parse the SQL. I am not unit testing the PreparedSatement class, I presume that works. I want to test my block of code that generates and specifies these values.
> > Any test is dependent on any number of external
> > resources such as the VM, the OS, file system, unit
> > test tool, etc.
>
> Ok, but I'm making a number of realistic assumptions
> that I can rely on these things.
When I test my database layer code I make assume that I will have a database to test against.
> I want to test my
> block of code such that when the test runs, it will
> not have varying results based on whether or not a
> database is available or not.
>
I understood that. What I am pointing out is that to correctly test that code you need to do what the database does - and that requires a parser that matches the syntax that the database supports.
> > Ok. And are you using a SQL parser? If not how are
> > you going to verify that the syntax is correct? And
> if there are any non-standard extensions in use how
> are you going to verify those?
>
> I want to validate that the value I specified in
> setInt(1, theValue) or setByte etc etc are correct. I
> do not intent to parse the SQL. I am not unit testing
> the PreparedSatement class,
That isn't what I said.
You are passing an expression that represents SQL to a interface. How are you going to differentiat the following where the first works and the second will not work?
select * from mytable where myfield='xxx'
mytable select * where myfield='xxx'
> When I test my database layer code I make assume that
> I will have a database to test against.
Thats grand if your happy with that. But networks and databases tend to be fickle in my experience. Seeing as my method is not concerned with these things, I don't want their availability or lack thereof affect my tests.
> That isn't what I said.
>
> You are passing an expression that represents SQL to
> a interface. How are you going to differentiat the
> following where the first works and the second will
> not work?
>
> select * from mytable where myfield='xxx'
> mytable select * where myfield='xxx'
My SQL is defined as a static string outside the method I am testing. I just want to know if it is correctly settings its values.
> My SQL is defined as a static string outside the
> method I am testing. I just want to know if it is
> correctly settings its values.
A prepared statement sets its values based on a specific JDBC driver.
So you have two choices.
1. Test with a specific database driver.
2. Create a mock driver.
For 1 you must have a database.
For 2 I don't see any point to the test that you are suggesting since it would actually be testing your mock driver (for the most part.)
But if 2 meets your purpose then just create one.
