Select from 2 Java tables

0
Hello everyone, I'm having trouble getting data from 2 different tables from the HSQLDB database, my DAO class looks like this:

@Override
public List<Animal> listar() throws Exception {
    List<Animal> lista = new ArrayList<>();
    try {
        statement = con.createStatement();
        String sql = "SELECT a.id,a.nome,a.nascimento,e.nome FROM animal a INNER JOIN especie e ON (a.especie_id = e.id)";
        rs = statement.executeQuery(sql);
        while (rs.next()) {
            int id = rs.getInt(1);
            String nome = rs.getString(2);
            Date nascimento = rs.getDate(3);
            String especie = rs.getString(4);
            Animal tempAnimal = new Animal(id,nome,nascimento);
            lista.add(tempAnimal);
            con.close();
        }
        return lista;
    }catch (Exception e){           
        con.close();
    }finally{
        con.close();
    }
    return null;
}

And my Animal Model class looks like this:

private Especie especie;
private int id;
private String Nome;
private Date DataDeNascimento;



public Animal() {

}


public Animal(int id, String nome, Date dataDeNascimento, Especie especie) {
    super();
    this.especie = especie;
    this.id = id;
    this.Nome = nome;
    this.DataDeNascimento = dataDeNascimento;
}

Model class Species

private Tipo_animal tipoanimal;

private int id;
private String nome;
private String Descricao;
private List<Animal> animais;


public Especie() {


}

public Especie(Tipo_animal tipoanimal, int id, String nome, String descricao, List<Animal> animais) {
    super();
    this.tipoanimal = tipoanimal;
    this.id = id;
    this.nome = nome;
    Descricao = descricao;
    this.animais = animais;
}

Database structure:

CREATE TABLE ANIMAL
(
ID             BIGINT IDENTITY NOT NULL ,
NOME           VARCHAR (50) NOT NULL ,
NASCIMENTO     DATE ,
ESPECIE_ID BIGINT NOT NULL ,
constraint ANIMAL_PK primary key (ID)
);

CREATE TABLE ESPECIE
(
ID                   BIGINT IDENTITY NOT NULL ,
NOME                 VARCHAR (50) NOT NULL ,
DESCRICAO            VARCHAR (100) ,
TIPO_ANIMAL_ACRONIMO CHAR (3) NOT NULL,
CONSTRAINT ESPECIE_PK PRIMARY KEY (ID)
);

ALTER TABLE ESPECIE ADD CONSTRAINT ESPECIE_NOME UNIQUE ( NOME ) ;

ALTER TABLE ANIMAL ADD CONSTRAINT ANIMAL_ESPECIE_FK FOREIGN KEY ( ESPECIE_ID ) REFERENCES ESPECIE ( ID ) ;

I need to get the species name of the animals and I'm not getting it, I can only bring the animal's data as the id, name and date of birth, when I put the species in my constructor method I can not bring his data along with the animal's data if anyone could help me would be grateful.

    
asked by anonymous 12.04.2018 / 19:13

0 answers