Foreign keys in java

Question

I’m having a little trouble implementing the foreign keys in my example. Can someone help me assemble the DAO class?

Cidadedao.java

public class CidadeDAO {
    private final Connection connection;

    public CidadeDAO() {
        try {
            this.connection = new ConnectionFactory().getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void adiciona(Cidade cidade){
        String sql= "insert into cidade (nome, cep, id_estado) values (?,?,?)";
        PreparedStatement stmt;
        try{
            stmt= connection.prepareStatement(sql);
            stmt.setString(1, cidade.getNome());
            stmt.setString(2, cidade.getCep());
            //  como faço o setString do estado?
            stmt.execute();
            stmt.close();  
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
}

Estadodao.java.

public class EstadoDAO {
private final Connection connection;

    public EstadoDAO() {
        try {
            this.connection = new ConnectionFactory().getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void adiciona(Estado estado){
        String sql= "insert into estado (nome, sigla, regiao) values (?,?,?)";
        PreparedStatement stmt;
        try{
            stmt= connection.prepareStatement(sql);
            stmt.setString(1, estado.getNome());
            stmt.setString(2, estado.getSigla());
            stmt.setString(3, estado.getRegiao());
            stmt.execute();
            stmt.close();  
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
}

Java city.

public class Cidade {
    private Long id;
    private String nome;
    private String cep;
    private Estado estado;

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getNome() {
        return nome;
    }
    public void setNome(String nome) {
        this.nome = nome;
    }
    public String getCep() {
        return cep;
    }
    public void setCep(String cep) {
        this.cep = cep;
    }
    public Estado getEstado() {
        return estado;
    }
    public void setEstado(Estado estado) {
        this.estado = estado;
    }       
}

Java state.

public class Estado {
    private Long id;
    private String nome;
    private String sigla;
    private String regiao;

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getNome() {
        return nome;
    }
    public void setNome(String nome) {
        this.nome = nome;
    }
    public String getSigla() {
        return sigla;
    }
    public void setSigla(String sigla) {
        this.sigla = sigla;
    }
    public String getRegiao() {
        return regiao;
    }
    public void setRegiao(String regiao) {
        this.regiao = regiao;
    }
}

Answer 1

The DAO of City can of course obtain the id of State reading the property city.getEstado(). getId().

You can set in the state the id which was generated for it at the time it was inserted by its respective DAO.

To obtain the id generated by an insertion (of course we are talking about an auto-increment column) you must enter an additional parameter when creating the Prepared statement, thus:

PreparedStatement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
stmt.executeUpdate();

Notice that I used executeUpdate, which is the method expressing a change in the database.

After executing the command, read the result set produced by the above command, thus:

ResultSet rs = stmt.getGeneratedKeys();
rs.next();
int idGerado = rs.getInt(1);

_{A good practice is to check whether a result set has actually been obtained and whether there are actually lines in this result set. If you are going to add these validations you also need to decide what to do in negative case (which may occur for example if the field id is not auto-increment). It may be valid to launch an exception that facilitates problem identification at runtime.}

Now you can set in the state the id which was generated for it during its insertion. The complete code for adding states looks like this:

public void adiciona(Estado estado){
    String sql= "insert into estado (nome, sigla, regiao) values (?,?,?)";
    PreparedStatement stmt;
    try{
        stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        stmt.setString(1, estado.getNome());
        stmt.setString(2, estado.getSigla());
        stmt.setString(3, estado.getRegiao());
        stmt.executeUpdate();
        
        // abaixo você obtém o id gerado para a coluna auto-incremento do MySql
        // e seta este id no objeto Estado que está sendo adicionado.
        ResultSet rs = stmt.getGeneratedKeys();
        rs.next();
        int idGerado = rs.getInt(1);
        estado.setId(idGerado);
        
        stmt.close();  
    }catch(SQLException e){
        throw new RuntimeException(e);
    }
}

The "main" method that creates these two objects looks something like this:

Estado estado = new Estado();
// ... estado.setXXX(...) - seta todas as propriedades do estado, exceto seu id.
estadoDAO.adiciona(estado);

Cidade cidade = new Cidade();
cidade.setEstado(estado);
// ... cidade.setXXX(...) - seta as demais propriedades da cidade.
cidadeDAO.adiciona(cidade);

And the method Citizenship. receives a line to set the id state-owned:

stmt.setInt(3, cidade.getEstado().getId());

And it’s ready.

Some good practices:

• You can validate the entities' required properties before entering so that you have more descriptive errors than database exceptions or Nullpointerexception.

• Try to keep the entity classes and their respective DAOs in the same package, a unique package, and then define as protected the methods set which are not in the interest of the consumer, such as setId, which will be automatically set by DAO.