Unit test of an SQL query in Junit

Question

I need some help to do the unit test on Junit.

How to do the @Test method below:

public static StringBuilder findSQLPA() {
    StringBuilder sql = new StringBuilder();
    sql.append(" select  fi1.co_carteira as carteira, ");
    sql.append("  fi1.nu_cpf_cnpj as cpf_cnpj, ");
    sql.append(" fi1.co_fundo_siart as siart, ");
    sql.append(" fi1.no_fundo as fundo,   ");
    sql.append(" fi1.no_cotista as cotista, ");
    sql.append(" fi1.nu_operacao_conta as op, ");
    sql.append(" fi1.nu_conta as conta,  ");
    sql.append(" fi1.dt_mvtco_antiga as dt_antiga, ");
    sql.append(" fi1.dt_mvtco_recente as dt_recente, ");
    sql.append(" fi1.pc_taxa_administracao as taxa, ");
    sql.append(" fd.no_tipo_macro_fundo as tipo_fundo, ");
    sql.append(" fi1.vr_saldo_bruto,    ");
    sql.append(" fi1.vr_saldo_liquido, ");
    sql.append(" fi1.vr_potencial_mes, ");
    sql.append(" fi1.vr_pontecial_ano, ");
    sql.append(" fi1.vr_potencial_12m , ");
    sql.append(" fi1.nu_unidade_dire as dire, ");
    sql.append(" fi1.nu_unidade_sr as sr,   ");
    sql.append(" fi1.nu_unidade as age  ");
    sql.append(" from cfism001.cfitb001_fundo_investimento fi1 ");
    sql.append(" inner join cfism001.cfitb007_resumo_cliente fi ");
    sql.append(" on fi.nu_cpf_cnpj = fi1.nu_cpf_cnpj  ");
    sql.append(" and fi.nu_unidade_dire = fi1.nu_unidade_dire ");
    sql.append(" and fi.nu_unidade_sr = fi1.nu_unidade_sr ");
    sql.append(" and fi.nu_unidade = fi1.nu_unidade ");
    sql.append(" and fi.dt_importacao = fi1.dt_importacao ");
    sql.append("   left join icosm001.icotbu24_unidade u ");
    sql.append(" on (fi1.nu_unidade = u.nu_unidade AND u.dt_fim is null AND u.ic_ultima_situacao = 'AT') ");
    sql.append(" left join  cfism001.cfitb002_tipo_macro_fundo fd     ");
    sql.append(" on (fi1.co_tipo_macro_fundo = fd.co_tipo_macro_fundo) ");
    sql.append(" where fi1.dt_importacao = :dt_importacao  and fi1.nu_unidade = :nu_unidade  @filtro ");
    return sql;
}

Answer 1

Tests involving database query are not called unit tests. These types of tests are called integration tests, although not necessarily only integration tests test the database (functional tests, for example, also do).

Integration tests are complicated to do without the help of some framework, as your test will involve the creation (or use) of a database for the test, a JDBC connection, preparing the data to be consulted, calling the method of the query class, etc.

Using a framework for this test makes it much easier. In Spring, for example, a test of your SQL would look something like:

@RunWith(SpringRunner.class)
@SpringBootTest
class FundoInvestimentoRepositoryIT {

    @Autowired
    private FundoInvestimentoRepository repository; //o SQL seu estaria implementado dentro de um método do FundoInvestimentoRepository

    public void testarConsulta() {

        repository.inserir(new FundoInvestimento("1", "23456")); //valores fictícios para dar um INSERT no banco de dados e consultá-los

        FundoInvestimentoVO fundoVO = repository.consultar();
        assertEquals("23456", fundoVO.getConta());
    }

}