I have a Java class where one of the attributes is an Arraylist of another object. How to bring this from the database?

Asked

Viewed 114 times

1

I have a java object that looks like this:

public class Objeto1 { 
    private int id;
    private int nome;
    private ArrayList<Objeto2> lista;

    //construtor e Getters e Setters
}

How do I get this list from the database? Can be done in the same query or need to use a query for the main object and another to fill the list?

Here we use normal JDBC, we do not use any JPA library

To get the Object from the bank, we use something like this:

public Objeto1 selecionar(int identificador){
        Objeto1 obj1 = new Objeto1();

        try{
            if(conexao.isClosed()) abreConexao();
            PreparedStatement ps = conexao.prepareStatement("SP_BUSCA_OBJETO_1_IDTFD ?");
            ps.setInt(1, identificador);

            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                obj1.setId = (rs.getInt("id")) ;
                obj1.setNome = (rs.getString("nome"));
            }
        } catch (Exception e){
            obj1 = null;
        } finally {
            fechaConexao();
        }

        return obj1;
    }

How would a cleaner way to get the "list" attribute from the database?

java sql jdbc

  • You want to know how to get the list or refactor this code by getting the list ?

  • I wanted to know the best way to get the list. If you can do it in a single query, or if I would have to do it later (A query to popular Object1, another query to popular the list within Object1)

1 answer

0

Make a Join between the two tables:

Select obj1.id as id_obj1, obj1.nome, obj2.id as id_obj2, obj2.coluna_a, obj2.coluna_b FROM objeto1 obj1 JOIN objeto2 obj2 ON obj1.id = obj2.id_obj1

Then you go through the result set and fill in the list

public List<Objeto1> selecionar(int identificador){
    List<Objeto1> retorno =  new ArrayList<Objeto1>();
    try{
        if(conexao.isClosed()) abreConexao();
        PreparedStatement ps = conexao.prepareStatement("SP_BUSCA_OBJETO_1_IDTFD ?");
        ps.setInt(1, identificador);

        ResultSet rs = ps.executeQuery();

        while(rs.next()){
            Objeto1 obj1 = new Objeto1();
            obj1.setId = (rs.getInt("id_obj1")) ;
            if(retorno.contains(obj1)) {
              obj1 = retorno.get(retorno.indexOf(obj1));
              Objeto2 obj2 =  new Objeto2();
              obj2.setId(rs.getI("id_obj2));
              obj2.setColunaA(rs.getString("coluna_a)); 
              obj2.setColunaA(rs.getString("coluna_a));
              obj2.setObjeto1(obj1);

              obj1.getLista().add(obj2);
            }
            else {
              obj1.setLista(new ArrayList<Objeto2>());
              obj1.setNome = (rs.getString("nome"));
              Objeto2 obj2 =  new Objeto2();
              obj2.setId(rs.getI("id_obj2));
              obj2.setColunaA(rs.getString("coluna_a)); 
              obj2.setColunaA(rs.getString("coluna_a));
              obj2.setObjeto1(obj1);

              obj1.getLista().add(obj2);
              retorno.add(obj1);
            }

        }
    } catch (Exception e){
        retorno = null;
    } finally {
        fechaConexao();
    }


    return retorno;
}

Browser other questions tagged java sql jdbc

You are not signed in. Login or sign up in order to post.