How to limit the amount of addresses that a user can have in the database

1

I have a database, and I have the user table and the address table. I need to validate in some way so that a user can have a maximum of 5 addresses in the registry. I use the PostgreSQL database and treats an application with Servlet. Any suggestions on how I could perform this validation in the database or Servlet? I tried doing this validation in the application:

public void register (address, user user) throws ClassNotFoundException, SQLException {

    Connection con = FabricaConexao.getConexao(); //conexão com o banco de dados

    PreparedStatement comand = con.prepareStatement("select count(*) from Endereco where usuario_id=?");
    comand.setInt(1,1);

    ResultSet result = comand.executeQuery(); //result é o resultado do select certo?

    if (result>=5){  //entao aqui eu tento realizar: Se o resultado do select for menor que 5, entao eu vou cadastrar, porem esta dando erro aqui.
        PreparedStatement comando = con.prepareStatement("insert into endereco (cep,numero,bairro,rua,estado,cidade,complemento,usuario_id) values (?,?,?,?,?,?,?,?)");
        comando.setString(1,endereco.getCep());
        comando.setInt(2,endereco.getNumero());
        comando.setString(3,endereco.getBairro());
        comando.setString(4,endereco.getRua());
        comando.setString(5,endereco.getEstado());
        comando.setString(6,endereco.getCidade());
        comando.setString(7,endereco.getComplemento());
        comando.setInt(8,usuario.getId());

        comando.execute();
        con.close();
    }else{
    con.close();
}
} 

But I do not know why the line where: result

asked by anonymous 24.11.2016 / 20:23

2 answers

1

Celina, what you can do, is in your application, make a select in the database and take the number of records (addresses) and block the inclusion of a new address if it is greater than 5.

select count(*) from TabelaEndereco where CodigoCliente='1'

So you will have the number of addresses for the customer with code 1

take a look at this link

link

rs = pstmt.executeQuery();
      if (rs.next()) {
        int numberOfRows = rs.getInt(1);
        System.out.println("numberOfRows= " + numberOfRows);
      }
    
24.11.2016 / 20:27
0

public void register (address, user user) throws ClassNotFoundException, SQLException {

    Connection con = FabricaConexao.getConexao(); //conexão com o banco de dados

    PreparedStatement comand = con.prepareStatement("select count(*) from Endereco where usuario_id=?");
    comand.setInt(1,1);

    ResultSet result= comand.executeQuery();

    if (result.next()) {
        int numberOfRows = result.getInt(1);
        System.out.println("numberOfRows= " + numberOfRows);

    if (numberOfRows<5){
        PreparedStatement comando = con.prepareStatement("insert into endereco (cep,numero,bairro,rua,estado,cidade,complemento,usuario_id) values (?,?,?,?,?,?,?,?)");
        comando.setString(1,endereco.getCep());
        comando.setInt(2,endereco.getNumero());
        comando.setString(3,endereco.getBairro());
        comando.setString(4,endereco.getRua());
        comando.setString(5,endereco.getEstado());
        comando.setString(6,endereco.getCidade());
        comando.setString(7,endereco.getComplemento());
        comando.setInt(8,usuario.getId());

        comando.execute();
        con.close();
    }
    }else {
        System.out.println("error: could not get the record counts");
    }

con.close ();     }

    
24.11.2016 / 22:19