Foreign keys in java

Question

Foreign keys in java

Navigation

#1 by (2 votes)

3

I'm having a bit of trouble implementing the foreign keys in my example. Can anyone help me mount 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);
        }
    }
}

StateDAO.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);
        }
    }
}

City.java

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;
    }       
}

State.java

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;
    }
}

mysql java jdbc

asked by anonymous 25.03.2015 / 13:17

1 answer

Insertion into the database through Webservice Upload system with rotate image automatically as in windows 8.1 or facebook

score 2 · Accepted Answer

City DAO can of course get the id from State by reading the city.getState (). getId () .

You can set the id state that was generated for it at the time it was entered by its DAO .

To get the id generated by an insert (of course we are talking about an auto-increment column) you must enter an additional parameter when creating the prepared statement , like this:

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

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

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

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 indeed lines in this result set. If you are adding these validations you also need to decide what to do if not (which can happen for example if the id field is not auto-increment). It may be valid to throw an exception that makes it easier to identify the problem at runtime.

You can now set the id state that was generated for it during insertion. The complete code for adding states looks something 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 CityDAO.add method gets a line to set the id state:

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

And it's ready.

Some good practices:

You can validate the mandatory properties of entities before inserting in order to have errors that are more descriptive than database exceptions or NullPointerException .
which are not of interest to the consumer, such as setId , which will be set automatically by DAO .