How to test data access and SQL statements using Java?

Question

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?

Take a look at Spring's AbstractTransactionalJUnit4SpringContextTests — diegomtassis
– diegomtassis, Commented Nov 26, 2013 at 13:46

PepperBob · Accepted Answer · 2013-11-24 06:07:38Z

1

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.

answered Nov 24, 2013 at 6:07

PepperBob

7073 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Over a year ago

Thank you. I will consider implementing JPA. What do you think of DBunit for my case?

PepperBob Over a year ago

Well, it really depends on the scenario you have: using abstractions like JPA lots of things you'd do with SQL are sucked into the application-code and thus can be tested easy. However, in a scenario where you have lots of SQL, logic in the database itself (stored procedures, triggers) or where you rely on vendor features, DBUnit would definitely offer some help dealing with testing. Always think about testing early (if not: first) and try to avoid putting code where it gives you a hard time being tested (that is: a database) because then you probably won't.

Collectives™ on Stack Overflow

How to test data access and SQL statements using Java?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related