How execute stored procedure in spring boot which not return resultset?

Question

I want to execute stored procedure which does not return any dataset. I have used this stored procedure for updating records.

My code is

 Query q1 = entityManager.createNativeQuery("call rrear_etl_processing.Sp_UpdateOATSROE( ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32, ?33, ?34, ?35, ?36, ?37, ?38, ?39, ?40, ?41, ?42, ?43, ?44, ?45, ?46, ?47, ?48, ?49, ?50, ?51, ?52, ?53, ?54, ?55, ?56, ?57, ?58, ?59, ?60, ?61, ?62, ?63, ?64, ?65, ?66, ?67, ?68, ?69, ?70, ?71, ?72, ?73, ?74, ?75, ?76, ?77, ?78, ?79, ?80, ?81, ?82, ?83, ?84, ?85, ?86, ?87, ?88, ?89, ?90, ?91, ?92, ?93, ?94, ?95, ?96, ?97, ?98, ?99, ?100, ?101, ?102, ?103, ?104, ?105, ?106, ?107, ?108, ?109, ?110, ?111, ?112, ?113 ,?114, ?115, ?116, ?117)")
     .setParameter(1,P_RowID)   
        .setParameter(2,P_Exception_CD) 
        .setParameter(3,P_Exception_Description)
        .setParameter(4,P_ORDER_EVENT_TYPE)
        .setParameter(5,P_OrderDatetime)
**remaining parameters
List result = q1.getResultList();

Take a look at this: https://stackoverflow.com/questions/3572626/calling-stored-procedure-from-java-jpa — CrazySabbath, Jun 25 '18 at 13:51
I dont need any resultset ..I got exception..'ResultSet is from UPDATE. No Data' — Nitin Wahale, Jun 26 '18 at 05:33

Nitin Wahale · Accepted Answer · 2018-09-21T10:19:03.783

Solved I have used .executeUpdate(); for not return any result set. hope its helpful to all

    Object q1 = entityManager.createNativeQuery("call rrear_etl_processing.Sp_UpdateOATSROE( ?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22, ?23, ?24, ?25, ?26, ?27, ?28, ?29, ?30, ?31, ?32, ?33, ?34, ?35, ?36, ?37, ?38, ?39, ?40, ?41, ?42, ?43, ?44, ?45, ?46, ?47, ?48, ?49, ?50, ?51, ?52, ?53, ?54, ?55, ?56, ?57, ?58, ?59, ?60, ?61, ?62, ?63, ?64, ?65, ?66, ?67, ?68, ?69, ?70, ?71, ?72, ?73, ?74, ?75, ?76, ?77, ?78, ?79, ?80, ?81, ?82, ?83, ?84, ?85, ?86, ?87, ?88, ?89, ?90, ?91, ?92, ?93, ?94, ?95, ?96, ?97, ?98, ?99, ?100, ?101, ?102, ?103, ?104, ?105, ?106, ?107, ?108, ?109, ?110, ?111, ?112, ?113 ,?114, ?115, ?116, ?117)")
                        .setParameter(1,P_RowID)
                        .setParameter(2,P_Exception_CD)
                        .setParameter(3,P_Exception_Description)
                        .setParameter(4,P_ORDER_EVENT_TYPE)
                        .setParameter(5,P_OrderDatetime)
                          *
                          *
                    .setParameter(115,P_MPID_OATS_Exc_ID )
                    .setParameter(116,P_Report_Header_ID)
                    .setParameter(117,P_Updated_By)
                    .executeUpdate();

Still better to use something named in a way that reflects what you are doing. — Jan Larsen, Jun 26 '18 at 14:02

score 0 · Answer 2 · edited Jun 25 '18 at 14:37

0

You need to use createStoredProcedureQuery(String procedureName) method

Query q1 = entityManager.createStoredProcedureQuery(...);
q1.execute();

edited Jun 25 '18 at 14:37

Elarbi Mohamed Aymen

1,617
2
14
26

answered Jun 25 '18 at 13:52

Jan Larsen

831
6
13

how set parameters – Nitin Wahale Jun 26 '18 at 05:31
Use `.registerStoredProcedureParameter()` and `.setParameter()` as you do – Jan Larsen Jun 26 '18 at 13:59

How execute stored procedure in spring boot which not return resultset?

2 Answers2