How to approach JUNIT tests for database queries

Question

I'm trying to retrieve some ResultSet from a MySQL database and would like to write JUnit tests for the public methods of my class (search & add).

How yould one approach writing JUnit tests for a database related class?

public class MetropolisModel implements MetropolisControl {

    private int rowsCount = 0;
    private int columesCount = 0;
    private ResultSet lastResult = null;

    @Override
    public ResultSet search(String metropolis, String continent, String population, boolean populationLargerThan, boolean exactMatch) {
        ResultSet rs = null;

        try {

            Connection c = MyDB.getConnection();
            String query = createSearchQuery(metropolis, continent, population, populationLargerThan, exactMatch);

            PreparedStatement ps = c.prepareStatement(query);

            int position = 1;

            if (!metropolis.isEmpty()) {
                ps.setString(position, addPercentageSign(metropolis, exactMatch));
                position++;
            }
            if (!continent.isEmpty()) {
                ps.setString(position, addPercentageSign(continent, exactMatch));
                position++;
            }
            if (!population.isEmpty()) {
                ps.setString(position, population);
            }

            rs = ps.executeQuery();

            setTable(rs);

            System.out.println("after " + ps + "%nFound records: " + rowsCount);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;

    }

    @Override
    public void add(String metropolis, String continent, String population) {

        try {

            Connection c = MyDB.getConnection();

            String query = "insert into metropolises values(?,?,?);";

            PreparedStatement ps = c.prepareStatement(query);

            ps.setString(1, metropolis);
            ps.setString(2, continent);

            if (population.isEmpty()) {
                ps.setNull(3, Types.BIGINT);
            } else {
                ps.setString(3, population);
            }

            ps.executeUpdate();

            search(metropolis, continent, population, true, true);

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private String createSearchQuery(String metropolis, String continent, String population, boolean populationLargerThan, boolean exactMatch) {
        String query = "select * from metropolises";

        if (!metropolis.isEmpty() || !continent.isEmpty() || !population.isEmpty()) {
            query += " where ";
            String operatorForString = exactMatch ? " = " : " like ";
            String operatorForInt = exactMatch ? " = " : populationLargerThan ? " > " : " < ";

            query += metropolis.isEmpty() ? "" : " metropolis " + operatorForString + " ?";
            query += continent.isEmpty() ? "" : addANDKeyword(" continent " + operatorForString + " ? ", !metropolis.isEmpty());
            query += population.isEmpty() ? "" : addANDKeyword(" population " + operatorForInt + " ? ", !metropolis.isEmpty() || !continent.isEmpty());
        }

        query += ";";
        System.out.println(query);
        return query;
    }

    private String addANDKeyword(String input, boolean needToAddKeyword) {
        if (!needToAddKeyword) {
            return input;
        } else {
            input = " and " + input;
            return input;
        }
    }

    private String addPercentageSign(String input, boolean exactMatch) {
        if (exactMatch) {
            return input;
        } else {
            return "%" + input + "%";
        }
    }

    private void setTable(ResultSet rs) throws SQLException {
        lastResult = rs;
        ResultSetMetaData rsmd = rs.getMetaData();
        columesCount = rsmd.getColumnCount();
        rs.last();
        rowsCount = rs.getRow();
    }
}

Mike Nakis · Accepted Answer · 2016-09-15 06:02:13Z

Use an in-memory instance of HSQLDB for speed. (Of course, you can only do that if your database access mechanism protects you from having to worry about differences in database syntax, because the syntax of whatever database you are using is likely to be slightly different from HSQLDB syntax.)
Each test class starts by creating the schema and populating the database with sample data.
Each test class uses its own full name (getClass().getName(), possibly after replacing dots with underscores,) as the database schema name, so as to guarantee that one test will not attempt to mess with another test's schema.
Each test method does the following:
1. Begins a transaction
2. Possibly adds more sample data
3. Performs a query
4. Receives the results
5. Ensures that the results are as expected
6. ROLLS BACK the transaction, so each test method leaves the database exactly as it found it.

Collectives™ on Stack Overflow

How to approach JUNIT tests for database queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related