Insert native query in spring

Question

I am trying to run an insert native query similar to the following

INSERT INTO t3(t1,t2)
SELECT t1.c1,sum(t1.c2) table1 t1 WHERE t1.c3 = 'something'
GROUP BY t1;

I am calling it using a repository and the system actually runs the query without any error. However, no value is being inserted. The query works when I try to run using the database interface.

score 0 · Answer 1 · answered Sep 01 '22 at 04:39

0

Run the query in your database, alternatively you dont need group by in insert scripts

answered Sep 01 '22 at 04:39

WARUTS

72
7

I require them to run as a corn job for reports – PKS Sep 01 '22 at 04:40
which database are you using? – WARUTS Sep 01 '22 at 04:44
oracle 11g. These Infos are mentioned – PKS Sep 01 '22 at 04:46
does your query run in your db interface? – WARUTS Sep 01 '22 at 04:48
yes. It also even fires in console – PKS Sep 01 '22 at 04:53

score 0 · Accepted Answer · answered Sep 27 '22 at 07:05

I was able to make it work. My mistake was not using @Modifying annotation on top of the function. I was simple using @Transactional only. If you require more then one table to be impacted simultaneously then use the annotation as well. It got the job done for me. exapme snippet below

  @Transactional
  @Modifying
  @Query(value = Query.YOUR_QUERY, nativeQuery = true)
  void insert(@Param("param") var param1);

Insert native query in spring

2 Answers2