CODEWITHSUNDEEP
javamysqlhibernatestored-procedures
Dzshean
Dzshean

Reputation: 314

How to call mysql store procedure using Hibernate JAVA

I'm Trying to call MySQL store procedure using Hibernate - JAVA

When i run the following java method to execute the procedure, my table is getting locked and not returning any response. But Procedure is success when i run as mysql command.

Procedure :

CREATE DEFINER = `root`@`%` PROCEDURE `getReceiptNumber`(IN bankcode VARCHAR(5),IN receipttype VARCHAR(20),IN curyear INT(4), IN curday INT(3),OUT seq VARCHAR(5))
BEGIN
IF EXISTS (SELECT 1 FROM receipt_number WHERE bank_code = bankcode AND receipt_type = receipttype) THEN
        IF NOT EXISTS(SELECT * FROM receipt_number WHERE bank_code = bankcode AND receipt_type = receipttype AND cur_year = curyear) THEN
            UPDATE receipt_number SET cur_year = curyear, seq_number = 0 WHERE bank_code = bankcode AND receipt_type = receipttype;
        END IF;
        IF NOT EXISTS(SELECT 1 FROM receipt_number WHERE bank_code = bankcode AND receipt_type = receipttype AND cur_year = curyear AND cur_date = curday) THEN
            UPDATE receipt_number SET cur_date = curday, seq_number = 0 WHERE bank_code = bankcode AND receipt_type = receipttype;
        END IF;
ELSE
    INSERT INTO receipt_number VALUES (bankcode,curday,curyear,receipttype,0);
END IF;
UPDATE receipt_number SET seq_number = (seq_number + 1) WHERE bank_code = bankcode AND receipt_type = receipttype;
SELECT LPAD(seq_number,5,'0') FROM receipt_number WHERE bank_code = bankcode AND receipt_type = receipttype INTO seq; 
END;

MySQL Request :

call getReceiptNumber("ABCD", "REC-PGM-BATCH",2018,42, @seq);
select @seq;

Java Method :

private String getBatchNumber(String bankCode, String receipttype, int year, int julianDay) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    String sequeceNumber = "";
    try {
        ProcedureCall call = session.createStoredProcedureCall("getReceiptNumber");
        call.registerParameter("bankcode", String.class, ParameterMode.IN).bindValue(bankCode);
        call.registerParameter("receipttype", String.class, ParameterMode.IN).bindValue(receipttype);
        call.registerParameter("curyear", Integer.class, ParameterMode.IN).bindValue(year);
        call.registerParameter("curday", Integer.class, ParameterMode.IN).bindValue(julianDay);
        call.registerParameter("seq", String.class, ParameterMode.OUT);

        ProcedureOutputs out = call.getOutputs();

        sequeceNumber = (String) out.getOutputParameterValue("seq");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.flush();
        session.close();
    }

    return sequeceNumber;
}

Upvotes: 2

Views: 132

Answers (2)

Prakash Jethava
Prakash Jethava

Reputation: 200

@NamedStoredProcedureQuery(
    name = "calculate", 
    procedureName = "calculate", 
    parameters = { 
        @StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "x"), 
        @StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "y"), 
        @StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "sum")
    }
)

**Use it in your program**
StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("calculate");
query.setParameter("x", 1.23d);
query.setParameter("y", 4.56d);
query.execute();
Double sum = (Double) query.getOutputParameterValue("sum");

If you are not able to solve then please refer this link

Upvotes: 0

Anuruddha wijesiri
Anuruddha wijesiri

Reputation: 31

Connection conn = getSession().connection();
CallableStatment stat = conn.prepareCall("{CALL insertComm (?,?)}");
stat.setString(1, remitNo); // Assuming both parameters are String
stat.setString(2, opt);

stat.executeUpdate();
stat.close();

like this post

i think its help full

Upvotes: 1

Related Questions