Creating a "check" method in a DAO class

Question

I have a question when creating a method that checks if there is already a record in my database.

In my database I have two tables:

Table User: (id, login, password); Order table: (id, Qtd, status,id_user);

I would like to check the status in the Requests table, and the User table has a relationship with the Requests table.

My idea is to check the status of an order whenever a particular user tries to place a new order, if you have an open order the order will not be added to the database.

Below is my method check:

public int verificaStatus(String status, Usuario u) {
    int id = 0;
    Connection conn = null;
    ResultSet resultSet = null;
    PreparedStatement stmt = null;
    conn = getConnection();

    try {
        stmt = conn
                .prepareStatement("SELECT pedidos.status=?, pedidos.id_usuario=?" +
                        "FROM pedidos" +
                        "INNER JOIN usuario ON pedidos.id_usuario = usuario.id" +
                        "WHERE pedidos.id_usuario = '" + u.getId() + "' ");
        stmt.setString(1, status);
        resultSet = stmt.executeQuery();
        if (resultSet.next()) {
            id = resultSet.getInt("id_usuario");
        }
    } catch (SQLException e) {
    } finally {
        closeConnection(conn, (Statement) stmt, resultSet);
    }
    System.out.println("ID do Usuário: " + id);
    System.out.println("ID do u.getId(): " + u.getId());
    return id;
}

Follow my "insert method":

public int inserir(Pedidos pedidos) {
 Connection conn = null;
 conn = getConnection();
 int sucesso = 0;
 Usuario u = new Usuario();
 int pedidosRealizados = verificaStatus(pedidos.getStatus()); //Nesse linha possui um erro, passei apenas o "status" como parâmetro, no método verifica é passado como parâmetro duas vaiáveis.

 if (pedidosRealizados == 0) {
     PreparedStatement stmt = null;
     try {
         stmt = conn.prepareStatement("INSERT INTO "
                 + "pedidos (loja, status, comentario, quantidade, id_usuario) VALUES(?,?,?,?,?)");

         stmt.setString(1, pedidos.getLoja());
         stmt.setString(2, pedidos.getStatus());
         stmt.setString(3, pedidos.getComentario());
         stmt.setInt(4, pedidos.getQuantidade());
         stmt.setInt(5, pedidos.getId_usuario());

         sucesso = stmt.executeUpdate();

         if (sucesso > 0) {
             System.out.println("PEDIDO REALIZADO COM SUCESSO!");
             System.out.println("Pedidos realizados: " + pedidosRealizados);
             System.out.println("Status: " + pedidos.getStatus());
         }

     } catch (SQLException e) {
         System.out.println("ERRO AO REALIZAR O PEDIDO!");
     } finally {
         closeConnection(conn, (Statement) stmt);
     }
 } else {
     System.out.println("ERRO: EXISTE UM PEDIDO EM ABERTO");
     closeConnection(conn);
 }
 return sucesso;
}

Answer 1

I don’t understand very well, but if you want to check if a particular user already has an open order you can do the following:

   public int verificaStatus(Usuario usuario) {
        int id = 0;
        Connection conn = null;
        ResultSet resultSet = null;
        PreparedStatement stmt = null;
        conn = getConnection();
        try {
            stmt = conn
                    .prepareStatement("SELECT pedidos.status=? pedidos.id_usuario=? FROM pedidos INNER JOIN usuario ON "
                            + "pedidos.id_usuario = usuario.id" 
                            + "WHERE pedidos.id_usuario = '" + usuario.getId() + "' "
                            + "AND pedido.status = 'EM ABERTO' ");
            stmt.setString(1, status);
            resultSet = stmt.executeQuery();
            if (resultSet.next()) {
                id = resultSet.getInt("id_usuario");
                System.out.println("ID do Usuário: " + id);
            }
        } catch (SQLException e) {
        } finally {
            closeConnection(conn, (Statement) stmt, resultSet);
        }
        return id;
    }

I changed two points in your code. The first is the parameter passed, I’m assuming you have a current user, ie the user who is logged in.

The second point is the conditions added in the clause WHERE:

"WHERE pedidos.id_usuario = '"+usuario.getId()+"' "

And

 "AND pedido.status = 'EM ABERTO' "

The first rule checks if any user id is equal to the parameter passed. And the second rule checks if the order status is open.

I couldn’t quite understand your question, if it’s not something very similar, then just adapt your code.