How to call PLSQL (Oracle) package in Spring MVC?

Question

I can execute normal queries via javax.persistence.EntityManager.

I really have no idea as of the moment. I'll try to search further after this post and if you guys could shed some light, I'd really appreciate it.

Thank you :)

Answer 1

Using javax.persistence.EntityManager,

get a raw connection using

private Connection getConnection(final EntityManager em) {
      HibernateEntityManager hem = (HibernateEntityManager) em;
      SessionImplementor sim = (SessionImplementor) hem.getSession();
      return sim.connection();
}

then do something like

public boolean myProc(EntityManager pentityManager, String param1, BigDecimal param2, String param3) throws SQLException {

        boolean result = false;

        Connection conn = getConnection(pentityManager); /* code above */
        CallableStatement cs;

        cs = conn.prepareCall("{CALL myPackage.myProc( ?, ?, ?, ? )}");

        //IN params
        cs.setString(1, param1);
        cs.setBigDecimal(2, param2);
        cs.setString(3, param3);

        //OUT param
        cs.registerOutParameter(4, java.sql.Types.BOOLEAN);

        cs.executeUpdate();

        result = cs.getBoolean(4); //get OUT PARAM

        return result;
    }