How to approach JUNIT tests for database queries
<p>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 (<code>search</code> &amp; <code>add</code>).</p>

<p>How yould one approach writing JUnit tests for a database related class?</p>

<pre><code>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 ? " &gt; " : " &lt; ";

            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();
    }
}
</code></pre>