CODEWITHSUNDEEP
javaintegration-testing
Trace
Trace

Reputation: 18869

Integration testing DAO INSERT statement

I need to perform an integration test for an INSERT statement.
Basically, I want an automated test that ensures that the SQL written in that query is in fact correct.
I'm not sure on how to write this integration test.

You need to take into account the following thoughts:

I've seen a bunch of posts that immediately start about discussing unit testing. I don't want to unit test this, I want to write an integration test that ensures my SQL query is correct.

I am not looking for specific code, I am looking for the correct conceptual way to test my sql query performed in the code (in my case it happens to be Java).
Could you give me some advice?

Code: 

public void insertPostStat(PostStat postStat) {
    final String sql = "INSERT INTO post_stat(created_at, geom, google_place_id, google_place_name) " +
            "VALUES(?, ST_SetSRID(ST_MakePoint(? , ? ), 4326), ?, ?";

    KeyHolder holder = new GeneratedKeyHolder();
    SqlParameterSource param = new MapSqlParameterSource()
            .addValue("created_at", postStat.getCreatedAtMs())
            .addValue("longitude", postStat.getLongitude())
            .addValue("latitude", postStat.getLatitutde())
            .addValue("google_place_id", postStat.getGooglePlaceId())
            .addValue("google_place_name", postStat.getGooglePlaceName());
    template.update(sql,param, holder);
}

Upvotes: 1

Views: 1114

Answers (1)

jalynn2
jalynn2

Reputation: 6457

Typically, testing an insert involves trying to read the data back to ensure that it was written as expected. If there is no natural key, you can always select on all of the columns. If the table allows duplicate rows, you should count them first:

  • begin transaction
  • select count of rows that match criteria.
  • call your method under test
  • select count of rows that match criteria
  • rollback tx
  • assert that count2 - count1 = 1

If the table does not allow duplicate rows:

  • begin transaction
  • call your method under test
  • read row that matches criteria
  • rollback tx
  • assert row was read

If you don't have a transaction manager, you need to clean up manually: Delete the expected row both before and after running your insert function.

Upvotes: 2

Related Questions