JDBC Callablestatement: Application hangs when calling Procedure

Question

When I run a Procedure by the application it hangs, but in the bank performs normal.

This is the way I use to call Procedure:

public int incluir(TOCompra compra) {
    try {
        PreparedStatement ps = BancoDados.getConexao().prepareStatement(INSERIRSQL, new String[]{"ID"});
        ps.setInt(1, compra.getId());
        ps.setDate(2, new java.sql.Date(compra.getData().getTime()));
        ps.setBigDecimal(3, compra.getValorTotal());
        ps.setBigDecimal(4, compra.getDesconto());
        ps.setBigDecimal(5, compra.getValorLiquido());
        ps.setString(6, compra.getStatus());
        ps.setInt(7, compra.getFornecedor().getId());
        ps.setInt(8, compra.getFuncionario().getId());
        ps.setInt(9, compra.getFormaPagamento().getId());
        ps.setString(10, compra.getObservacao());
        ps.executeUpdate();
        try (ResultSet rs = ps.getGeneratedKeys()) {
            rs.next();
            idCompra = (int) rs.getLong(1);
        }

        if ("F".equals(compra.getStatus())) {
            try (CallableStatement ctsmt = BancoDados.getConexao().prepareCall(PROCEDURE_COMPRA_ESTOQUE)) { //"{CALL COMPRA_ESTOQUE(?,?,?)}"
                ctsmt.setInt(1, idCompra);
                ctsmt.setString(2, "F");
                ctsmt.executeUpdate();
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return idCompra;
}

Here is the code of Procedure:

CREATE OR REPLACE PROCEDURE COMPRA_ESTOQUE(COD_COMPRA NUMBER,STATUS VARCHAR2) IS
   QUANT NUMBER;
   PROD NUMBER;
   BEGIN
       IF STATUS = 'F' THEN
       BEGIN
           FOR ITEM IN (
               SELECT ITEMCOMPRA.QUANTIDADE, ITEMCOMPRA.PRODUTO_ID
               INTO QUANT, PROD FROM ITEMCOMPRA,PRODUTO
               WHERE COMPRA = COD_COMPRA AND ITEMCOMPRA.PRODUTO_ID = PRODUTO.ID AND PRODUTO.CONTROLA_LOTE = 'NAO'
           ) LOOP
               UPDATE PRODUTO SET ESTOQUE = ESTOQUE + ITEM.QUANTIDADE
               WHERE ID = ITEM.PRODUTO_ID;
           END LOOP;
       END;
   END IF;
END; /

Answer 1

• The first little problem I saw was that there was a } missing somewhere. I put the } that was missing (I assumed that it is before the block catch, at the end of try).

• Another silly little problem I saw was you comparing Strings with == instead of using the equals. But this problem is simple to fix.

• The variable idCompra is not stated in the code you gave. I will assume it is a local variable. If it’s not a local variable, then it’s probably wrong because it should probably be.

Well, assuming the method BancoDados.getConexao() always bring a new connection, see the following:

• First, if the method BancoDados.getConexao() always create a new connection when called, so for each call to this method you create a new connection. As you call this method twice, you will have two connections to do your operation, where the first will run the SQL of the INSERIRSQL and the second to SQL do PROCEDURE_COMPRA_ESTOQUE.

• I don’t know exactly what’s in your SQL’s INSERIRSQL, but if both connections are manipulating the same record in the database, to ensure consistency and avoid conflicts between the two connections, the database will make the second transaction wait for the end of the first.

• However, since both transactions occur in the same thread, since the database makes the second transaction wait for the end of the first, the running thread will be put to sleep while the first transaction does not end. It turns out that this is the same thread that manages the first transaction, and as a result it will never end. Your program will go into deadlock!

To fix all your problems with the connection:

• You must ensure that only one connection will be used.

• Make sure to close the objects Connection, PreparedStatement, ResultSet and CallableStament properly. The best way to do this is by using the syntax Try-with-Resources Java 7 or higher. Avoid keeping such objects in use for longer than necessary unless you have a strong reason to do so, and in your case you do not have.

• In the CallableStatement, when there are type parameters out, you must register them properly in order to read the data returned by Procedure.

Thus, applying the relevant modifications to its code in the light of the above, its code looks like this:

public class DAOCompra {

    private static final String PROCEDURE_COMPRA_ESTOQUE = "{CALL COMPRA_ESTOQUE(?,?,?)}";

    private static final String INSERIRSQL = "..."; // Coloque sua SQL de INSERT aqui.

    public int incluir(TOCompra compra) {
        int idCompra = 0;
        try (
            Connection con = BancoDados.getConexao();
            PreparedStatement ps = con.prepareStatement(INSERIRSQL, new String[]{"ID"}))
        {
            ps.setInt(1, compra.getId());
            ps.setDate(2, new java.sql.Date(compra.getData().getTime()));
            ps.setBigDecimal(3, compra.getValorTotal());
            ps.setBigDecimal(4, compra.getDesconto());
            ps.setBigDecimal(5, compra.getValorLiquido());
            ps.setString(6, compra.getStatus());
            ps.setInt(7, compra.getFornecedor().getId());
            ps.setInt(8, compra.getFuncionario().getId());
            ps.setInt(9, compra.getFormaPagamento().getId());
            ps.setString(10, compra.getObservacao());
            System.out.println("insert sqlll " + INSERIRSQL);
            ps.executeUpdate();

            try (ResultSet rs = ps.getGeneratedKeys()) {
                rs.next();
                idCompra = (int) rs.getLong(1);
            }

            if ("F".equals(compra.getStatus())) {
                try (CallableStatement ctsmt = con.prepareCall(PROCEDURE_COMPRA_ESTOQUE)) { //"{CALL COMPRA_ESTOQUE(?,?,?)}"
                    cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
                    ctsmt.setInt(1, 195);
                    ctsmt.setString(2, "F");
                    ctsmt.execute();
                    String erro = ctsmt.getString(3); // Você decide o que fazer com isso.
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("erro de compra " + e);
        }
        return idCompra;
    }
}

Note that:

• I’m just calling BancoDados.getConexao() in a single location.

• Note that record the parameter out of CallableStatement with cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);.

• Also note the syntax of the blocks try:

  try (/*inicialização de um objeto que necessitará ser fechado*/) {
      // ...
      // ... trabalha com o objeto aqui
      // ...
  } // O compilador gera o código para fechar o objeto automagicamente.
  

Well, I don’t know exactly what your method BancoDados.getConexao() does. If this which I have shown above does not work, then edit the question by adding the code of this method so that we can continue to solve your problem. If this works, I ask you to accept my answer as a solution to the problem by clicking on the little green icon to the left of this answer.