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

Question

I have a database, and I have the user table and the address table. I need to validate somehow so that a user can have at most 5 addresses in the registry. I use the Postgresql database and it is an application with Servlet. Any suggestions on how I could perform this validation in the database or Servlet? I tried to do 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();
}
} 

however do not know why the line where: result <= 5 is giving problem. The problem is related to the property I believe, but I don’t know what would be the correct property I could use for the code to work.

Answer 1

Celina, what Voce can do, is in your application, make a select in the database and take the amount 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 Voce will get the number of addresses for the client with code 1

take a look at this link

http://www.java2s.com/Code/Java/Database-SQL-JDBC/CountRecordsUsingPreparedStatement.htm

rs = pstmt.executeQuery();
      if (rs.next()) {
        int numberOfRows = rs.getInt(1);
        System.out.println("numberOfRows= " + numberOfRows);
      }

Answer 2

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