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

    hibernate or jpa and seam

      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
          chawax

          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
            gonorrhea

            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.


            example:


            @Name("storedProcedureDAO")
            @Stateless
            @AutoCreate
            public class StoredProcedureDAO implements StoredProcedureLocalDAO 
            {
                 private Connection con = null;
                 private CallableStatement cstmt = null;
                 private ResultSet rs = null;
                 
                 @In //for boBETS
                 private EntityManager entityManager;
                 
                 @Logger
                 private Log log;
                 
                 @In     
                 private JdbcUtils jdbcUtils;
                 
                 @In(required=false)
                 private String serialNumber;
                      
                 private final String DATASOURCE_NAME_FOR_SPROC = "java:boBETSDatasource";
                 
                 /*-----------------------------------------------------------BEGIN METHODS--------------------------------------------------------------------*/
                 
                 public List<NewWorkOrdersBean> getWorkOrdersBySerialNumber(Short siteId)
                 {
                      List<NewWorkOrdersBean> workOrderList = null;
                      
                      try
                      {                    
                           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);
                                cstmt.execute();
                                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();
                                     
                                     newWorkOrdersBean.setSerialNumber(serialNumber);
                                     newWorkOrdersBean.setWorkOrderNo(workOrderNo);
                                     newWorkOrdersBean.setTechId(techId);
                                     newWorkOrdersBean.setCheckInDate(checkInDate);
                                     newWorkOrdersBean.setFindingCodes(findingCodes);
                                     newWorkOrdersBean.setMacAddress(macAddress);
                                     newWorkOrdersBean.setWorkOrderType(workOrderType);
                                     newWorkOrdersBean.setModelNumber(modelNumber);
                                     newWorkOrdersBean.setInventoryReceiptDate(inventoryReceiptDate);
                                     
                                     if (!workOrderExists(equipmentRepairList, newWorkOrdersBean))                         
                                          workOrderList.add(newWorkOrdersBean);                         
                                }
                                
                                if (workOrderList != null)
                                     log.info("execStoredProc(): workOrderList.size() = " + workOrderList.size());
                                
                           }
                      }
                      catch (Exception e)
                      {
                           e.printStackTrace();
                      }
                      finally
                      {     
                           try 
                           {
                                jdbcUtils.cleanUp(con, cstmt, rs);
                           }
                           catch (SQLException e)
                           {
                                log.error("execStoredProc: SQLException caught: ", e);
                           }
                      }          
                      return workOrderList;
                 }
                 
                      
            }
            

            • 3. Re: hibernate or jpa and seam
              joblini

              Why not use a JPA native query?


              See: Make Love, Not Bugs