Proceduce Hibernate

1. Create store proceduce

----- STORED PROCEDURE QUERY #1 -----
DELIMITER $
CREATE PROCEDURE findAllEmployees ()
    BEGIN
        SELECT * FROM employee;
    END $
DELIMITER ;


----- STORED PROCEDURE QUERY #2 -----
DELIMITER $
CREATE PROCEDURE findEmployeeByDepartment (IN emp_department VARCHAR(200))
    BEGIN
        SELECT * FROM employee emp WHERE emp.edept = emp_department;
    END $
DELIMITER ;


----- STORED PROCEDURE QUERY #3 -----
DELIMITER $
CREATE PROCEDURE findEmployeeCountByDesignation (IN emp_designation VARCHAR(200), OUT designation_count INT(50))
    BEGIN
        SELECT COUNT(*) INTO designation_count FROM employee emp WHERE emp.edesig = emp_designation;
    END $
DELIMITER ;

2. Create Entity

@Entity
@Table(name= "employee")
public class Employee {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private int eid;

    private String ename;

    private String edesig;

    private String edept;

    private int esal;

    public int getEid() {
        return eid;
    }

    public void setEid(int eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getEdesig() {
        return edesig;
    }

    public void setEdesig(String edesig) {
        this.edesig = edesig;
    }

    public String getEdept() {
        return edept;
    }

    public void setEdept(String edept) {
        this.edept = edept;
    }

    public int getEsal() {
        return esal;
    }

    public void setEsal(int esal) {
        this.esal = esal;
    }

    @Override
    public String toString() {
        return "Employee [eid=" + eid + ", ename=" + ename + ", edesig=" + edesig + ", edept=" + edept + ", esal="
                + esal + "]";
    }
}

3.  Test call proceduce by hibernate

 // Hibernate session object to start the db transaction.
        Session s = config.buildSessionFactory().openSession();

        // Fetching the data from the database using stored procedure queries.

        // Stored procedure query #1
        System.out.println(":::: Find all employees ::::");

        StoredProcedureQuery allemployees = s.createStoredProcedureQuery("findAllEmployees", Employee.class);

        List elist = (List) allemployees.getResultList();

        for(Employee employee : elist) {
            System.out.println(employee.toString());
        }

        // Stored procedure query #2
        System.out.println("\n:::: Find employees department wise ::::");

        StoredProcedureQuery department = s.createStoredProcedureQuery("findEmployeeByDepartment", Employee.class);
        department.registerStoredProcedureParameter("emp_department", String.class, ParameterMode.IN);

        String dparam = "Technology";
        department.setParameter("emp_department", dparam);

        List dlist = (List) department.getResultList();

        for(Employee employee : dlist) {
            System.out.println(employee.toString());
        }

        // Stored procedure query #3
        System.out.println("\n:::: Find employee count by designation ::::");

        StoredProcedureQuery count = s.createStoredProcedureQuery("findEmployeeCountByDesignation");
        count.registerStoredProcedureParameter("emp_designation", String.class, ParameterMode.IN);
        count.registerStoredProcedureParameter("designation_count", Integer.class, ParameterMode.OUT);

        String param = "Lead";
        count.setParameter("emp_designation", param);
        count.execute();

        Integer employee_count = (Integer) count.getOutputParameterValue("designation_count");
        System.out.println("Employee count for designation= " + param + " is= " + employee_count);

        // Closing the session object.
        s.close();