How to write unit test cases for DAO layer with native JDBC SQL queries?

Question

I want to write unit test cases for the following piece of code. These test will also run while deployment and code coverage will be displayed on a dashboard, so local DB mock can't be used.

QCConstants.selectQuery => select query string

QCConstants.updateQuery => insert/delete query string

    public List<SomeModel> getData() {
        Connection conn;
        try {
            conn = SQLDBConfig.getConnection();
            PreparedStatement ps;
            ResultSet rs;
            ps = conn.prepareStatement(QCConstants.updateQuery);
            ps.executeUpdate();
            ps = conn.prepareStatement(QCConstants.selectQuery)
            rs = ps.executeQuery();
            List<SomeModel> list = new ArrayList();
            while (rs.next()) {
                SomeModel someModel = new SomeModel();
                someModel.set_someId(rs.getInt(1));
                someModel.set_someName(rs.getString(2));
                someModel.set_someDesc(rs.getString(3));
                list.add(someModel);
            }
            return list;
        } catch (Exception e){
            log.error("Exception occurred " + e.getMessage());
            return null;
        }
    }

You would just have to doNothing for ps.executeUpdate();, and create appropriate objects for ps.executeQuery(); and similar code. Just create a ResultSet Object of size 1 with expected properties and mock ps.executeQuery to return that object — Deepanshu Rathi, Aug 02 '21 at 16:22

josejuan · Answer 1 · 2021-08-02T17:09:46.490

It is not possible or desirable to test all/certain codes, the reason being, quality costs money, it is not free. Of course, poor quality code also costs money. The right balance depends on many factors (e.g. bad programmers writing tests is worse than good programmers not writing them) and is often subjective.

I say all this because the concrete solution to your problem (the right approach to take) depends heavily on this subjective balance.

The best way in your case is to decouple the backend (e.g. using some ORM like Spring Boot) and create a Derby or similar local schema populating data with import.sql only for testing, it can run on your CI/CD servers without problems.

Too long question to write here but here the two main questions to use:

"certain code" but "all" is great too, thank you. – josejuan Aug 02 '21 at 17:10 — josejuan, Aug 02 '21 at 17:10

How to write unit test cases for DAO layer with native JDBC SQL queries?

1 Answers1