-
The first problem I saw was that there was a } missing somewhere. I put the } that was missing (I assumed it is before the block catch , at the end of try ).
-
Another silly little problem I've seen is you've compared String s to == instead of equals . But this problem is simple to fix.
-
The idCompra variable is not declared in the code you gave. I'll assume it's a local variable. If it is not a local variable, then this is probably wrong because it probably should.
Well assuming the BancoDados.getConexao() method always brings a new connection, see the following:
-
First, if the BancoDados.getConexao() method always creates a new connection when it is called, then for each call to this method you create a new connection. As you call this method twice, you will have two connections to make your operation, where the first will execute the SQL of the INSERIRSQL and the second of the SQL of the PROCEDURE_COMPRA_ESTOQUE .
-
I do not know exactly what is in your SQL of INSERIRSQL , but if both connections are to handle the same record in the database, to ensure consistency and avoid conflicts between the two connections, the database will cause the second transaction to wait for the end of the first transaction.
-
However, since both transactions occur on the same thread, once the database makes the second transaction wait for the first transaction to finish, the running thread will be put to sleep while the first transaction does not finish. This is the same thread that manages the first transaction, and as a result it will never end. Your program will enter deadlock !
To fix all your problems with the connection:
-
You should ensure that only one connection is used.
-
Be sure to close the Connection , PreparedStatement , ResultSet and CallableStament objects properly. The best way to do this is by using the try-with-resources syntax of 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.
-
In%%, when there are parameters of type out , you must register them appropriately in order to read the data returned by procedure .
In this way, by applying the relevant modifications to your code in the light of the esplanade, your code looks something 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;
}
}
Please note:
-
I'm just calling CallableStatement in a single location.
-
Note that you record the out parameter of BancoDados.getConexao() with CallableStatement .
-
Note also the syntax of the cstmt.registerOutParameter(3, java.sql.Types.VARCHAR); blocks:
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'm not exactly sure what your try method does. If that 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, please accept my answer as a solution to the problem by clicking the green icon on the left of this answer.
