Bringing an Object for a Resultset

Question

I’m having trouble bringing the data of a Java survey, I’m using Postgresql and JDBC database for persistence,

public List<Tapete> listarTapetes() {
    PreparedStatement state = null;
    ResultSet rs = null;
    try{
        String sql = "select * from Tapete order by preco";

        state = ConexaoPostgres.getConPostGres().prepareStatement(sql);

        rs = state.executeQuery();
        List<Tapete> listaTapetes = new ArrayList<Tapete>();
        while (rs.next()) {
            Tapete tapete = new Tapete(//DUVIDA);
            listaTapetes.add(tapete);
        }

        return listaTapetes;
    }

my problem is the following one where you have the comment (/DOUBT). In my object Tapete, I have a variable like Forma and another of the kind Material:

private Material material;
private Forma forma;

But I can’t do what mine ResultSet bring me these objects, on my bench I defined material and shape as integers.

What should I do?

Answer 1

I’ve done something similar to what you need to do, you’ll get the following classes:

• Java carpet.
• Tapetebd.java.

My example is assuming that Material and Form are also tables of your bank, in this case they are foreign keys. In my example I will put a NAME attribute just for illustration.

The Tapete.java class will be our bean, in which you will only have the attributes and their respective getters and setters, the Forma and Material classes will also need to have this same bean, and the same database class (assuming material and form are in the database).

class Tapete {
     private String nome;
     private Material material;
     private Forma forma;

     //getters and setters
}

In the Tapetebd class we will have a method similar to your listTapetes(), we will call getTapetes() which will be responsible for the query and will return the list.

class TapeteBD {
    //Métodos de conexão etc.

    private ResultSet rs = null; 
    private MaterialBD mbd = new MaterialBD(); //Criamos a classe que irá retornar o objeto Material.
    private FormaBD fbd = new FormaBD(); //Criamos a classe que irá retornar o objeto Forma.

   /* Essaes dois objetos, formaBD e MaterialBD serão responsáveis por chamar um método getMaterial() e getForma() respectivamente, já que o tapete recebe também dois objetos. */

public List<Tapete> getTapetes() throws SQLException {

    String sqlQuery = "SELECT * FROM Tapete ORDER BY preco";
    Tapete tapete = null; //Definimos o objeto tapete que terá seus atributos recuperados do banco de dados posteriormente.
    Material material = null; //Definimos o objeto material que será recuperado do banco de dados posteriormente.
    Forma forma = null; //Definimos o objeto forma que será recuperado do banco de dados posteriormente.
    List<Tapete> tapetes = new ArrayList<Tapete>;

    state = ConexaoPostgres.getConPostGres().prepareStatement(sql); //Essa parte da conexão eu não sei se está correta, pois não to com os arquivos aqui, só copiei o que você já tinha feito.
    rs = state.executeQuery();

    if(rs!=null) {
         rs.beforeFirst(); //Posiciona o cursos antes do primeiro elemento.
         while(rs.next()) {
             tapete = new Tapete(); //Criamos o tapete para setarmos os atributos de acordo com os dados recuperados do banco.
             tapete.setNome(rs.getString("nome"); //Setamos o nome de acordo com o recuperado do campo nome da tabela tapete no banco de dados.
             material = mbd.getMaterial(rs.getInt("codigo_material"); //Temos que ter uma classe de banco de dados para material para poder recuperar o objeto Material de acordo com sua chave estrangeira.
             forma = fbd.getForma(rs.getInt("codigo_forma");

             if(material != null) {
                  tapete.setMaterial(material); 
             }

             if(forma != null) {
                  tapete.setForma(forma);
             }

             tapetes.add(tapete); //Adicionamos o tapete com os atributos na lista.
         }
    }
return tapetes; //Retornamos a lista para o método que o chamou.
}

It’s been a long time since I made this code, and I don’t have it on me anymore and I don’t have IDE to test it on here, so there must be some mistakes, but I think you can understand. Anything just ask. A hug.

EDITED

Hadn’t read that you had set Material and Shape as integers, these integers are foreign keys to another table? If you are the example it fits perfectly. You only have to create the classes:

• Java form.
• Formabd.java.
• Java material.
• Materialbd.java

With the structure a bit similar, I think with this you should already know more or less how to proceed.

Answer 2

At each execution of rs.next() the cursor that "points" to the result brought by your SELECT down a line (contrary to what you may think it starts above the first line and not directly over the first line). So that after the first rs.next() he’s on line 1, then on line 2, and so on until he returns false and therefore get out of the while, indicating that the lines have ended.

Its object rs has methods that return the value of each column of the row where the cursor is currently positioned. See the documentation for Resultset and seek to know its main methods, such as getInt() to bring an INTEGER field, getString() to bring a CHAR or VARCHAR field, etc.

Note that these methods can be called by passing the name of a table column as a parameter or the numbering of the table depending on the order the columns are in the database, for example getInt("material") or getInt(1). Choose the way you think best.