CODEWITHSUNDEEP
javamysqlspringunit-testing
Michael
Michael

Reputation: 433

How to test data access and SQL statements using Java?

I have a DAO method that utilizes Spring for JDBC access. It calculates a seller's success rate of selling an item.

Here is the code:

public BigDecimal getSellingSuccessRate(long seller_id) {
    String sql = "SELECT SUM(IF(sold_price IS NOT NULL, 1, 0))/SUM(1) 
                  FROM transaction WHERE seller_id = ?";
    Object[] args = {seller_id};
    return getJdbcTemplate().queryForObject(sql, args, BigDecimal.class);
}

How should I go about testing this method or any DAO method with JUnit? What are some best practices to test the data access logic? I am thinking of testing it against an embeddable database loaded with some data after, but shouldn't we do integration tests on a production environment?

Upvotes: 0

Views: 239

Answers (1)

PepperBob
PepperBob

Reputation: 707

What we do for unit tests is usually create the schema in an in-memory database and run the queries against it. Some of the unit tests pre-populate their data to check if the query returns the expected results. In simply cases we just skip that part and only check if the query actually runs and there are no typos, missing relations, etc.

What can be a problem here is that your queries might rely on a vendor-specific dialect which makes them not or hardly portable. Using an abstraction like JPA can avoid these issues.

As a general rule avoid putting (any) logic in the database layer using stored procedures, triggers or similar unless you have a real good reason to do so. Keeping the logic in the application makes it much more transparent and easier to test.

It's a good idea to do integration tests so verify the overall behavior of your application. Spring as a very good integration in tests so it should be really simple to bootstrap your application with Spring.

Upvotes: 1

Related Questions