How unit test insert record in spring (no delete method)

Question

I have DAO using Spring's jdbcTemplate with Create Read Update (no Delete) operation.

Create method have ID parameter which is unique key in table.

Except mocking DAO, how can I actually test create without getting constraint violation?

Using random ID still can fail sometimes

Should I override setAutoCommit to avoid adding record? is it still consider a valid unit test?

Must I delete in SQL the record in database beforehand or is there spring option for this types of tests?

Or should I consider it as integration test and not unit test?

EDIT

I'm using Oracle, I can't use sequence for creating values for the ID

We have a few Data sources exists (not for testing) in production

Answer 1

It really depends on what is the purpose of such a test, not all the tests are "unit tests" with this respect.

If, for example, the goal is to test the "service" that encapsulates a business logic, but from this service, sometimes there are calls to DAO, then probably the best way is to just mock the DAO as you suggest. In this case, DAO obviously won't be covered by this test, but the service will.

If the purpose is to test SQL statements (and I assume that DAO contains nothing but SQL statements + maybe transforming them into the domain object), then mocking is not an option.

In this case, the test should include calls to some kind of database, but in this case, it's not called a unit test anymore (a unit test is something that runs really fast and only in memory, no DBs, no I/O, etc.) I'll call this an integration test (as you also suggest), but different people have probably different names for this kind of tests.

In practice, we need both kinds of tests because they test different things

So, how to test this?

First of all the decision should be made, which database should be used, there are 3 approaches here:

1. Run with a real database, shared between the users, tests assume its pre-installed
2. Run with an in-memory database
3. Run the DB docker image of the DB when the test suite runs, and destroy it afterwards

While the discussion of which approach is better is very interesting on its own, its out of scope for this question IMO, each choise has its implications.

Once you're done with this decision, you should decide how to work with this database from the code.

Usually spring tests use the following pattern:

1. open a transaction before the test
2. run the test (change the data, even change the schema - add columns, tables if you want). Do assertions
3. Regardless the tests result, rollback the transaction so that the data will be just like before the test

So if you follow this approach for all your tests, they'll start with "empty" data state so that no constraint violations are expected This effectively solves also the "deletion of the record" question because the data will be deleted anyway when the transactions is rolled back.

Now regarding the Deletion of the record outside a transaction.

An obvious approach is to execute an sql of deletion right from the test (outside the DAO), so that DAO (a production code won't be changed)

You can inject DataSource/JDBCTemplate right into the test (Spring test perfectly supports this) and call the required SQL from there