3 Replies Latest reply on Mar 25, 2009 2:34 AM by Ingo Jobling

    hibernate or jpa and seam

    Kevin Taylor Newbie

      I really need to call a stored procedure that updates an entry in the database.  Is this possible using hibernate/jpa.  And if so, could someone please provide some sample code and any links to documentation that explains this further.  I also need to call one query that returns a result set depending upon parameters passed into it.  Any help would be greatly appreciated.

        • 1. Re: hibernate or jpa and seam
          Olivier Thierry Apprentice

          Hibernate3 introduced support for queries via stored procedures, but I don't know if it includes update queries. Have a look there ;)

          • 2. Re: hibernate or jpa and seam
            Arbi Sookazian Master

            Read the Bauer/King book on JPA/Hibernate for examples on how to use sprocs with Hibernate.  sprocs are not supported in JPA 1.0 or 2.0.  You must use JDBC.  I have used sprocs for read-only (no CUD) in my Seam/JPA app w/ Hibernate as persistence provider.

            The recommendation for sprocs with Seam/JPA is JDBC.


            public class StoredProcedureDAO implements StoredProcedureLocalDAO 
                 private Connection con = null;
                 private CallableStatement cstmt = null;
                 private ResultSet rs = null;
                 @In //for boBETS
                 private EntityManager entityManager;
                 private Log log;
                 private JdbcUtils jdbcUtils;
                 private String serialNumber;
                 private final String DATASOURCE_NAME_FOR_SPROC = "java:boBETSDatasource";
                 /*-----------------------------------------------------------BEGIN METHODS--------------------------------------------------------------------*/
                 public List<NewWorkOrdersBean> getWorkOrdersBySerialNumber(Short siteId)
                      List<NewWorkOrdersBean> workOrderList = null;
                           con = jdbcUtils.getConnection(DATASOURCE_NAME_FOR_SPROC);
                           if (con != null)
                                cstmt = con.prepareCall("{call [dbo].[usp_getWorkOrdersBySerialNumber](?,?)}");
                                cstmt.setString(1, serialNumber);
                                cstmt.setShort(2, siteId);
                                rs = (ResultSet) cstmt.getResultSet();
                                workOrderList = new ArrayList();
                                //get all EquipmentRepair entities
                                List<EquipmentRepair> equipmentRepairList = entityManager.createQuery("select er from EquipmentRepair er").getResultList();
                                while (rs.next())
                                     String serialNumber           = rs.getString("SERIAL_NUMBER");
                                     String workOrderNo                = rs.getString("WORK_ORDER_NUMBER");
                                     String techId                     = rs.getString("ASSIGNED_INSTALLER");
                                     String checkInDate                = rs.getString("DateTimeEntered");
                                     String findingCodes           = rs.getString("FindingCode");
                                     String macAddress                = rs.getString("EQUIPMENT_ADDRESS");
                                     String workOrderType         = rs.getString("WO_TYPE");
                                     String modelNumber               = rs.getString("ITEM_NUMBER");
                                     java.util.Date inventoryReceiptDate   = new java.util.Date(rs.getString("INVENTORY_RECEIPT_DATE")); 
                                     NewWorkOrdersBean newWorkOrdersBean = new NewWorkOrdersBean();
                                     if (!workOrderExists(equipmentRepairList, newWorkOrdersBean))                         
                                if (workOrderList != null)
                                     log.info("execStoredProc(): workOrderList.size() = " + workOrderList.size());
                      catch (Exception e)
                                jdbcUtils.cleanUp(con, cstmt, rs);
                           catch (SQLException e)
                                log.error("execStoredProc: SQLException caught: ", e);
                      return workOrderList;

            • 3. Re: hibernate or jpa and seam
              Ingo Jobling Master

              Why not use a JPA native query?

              See: Make Love, Not Bugs