spring-data-jpa to insert using @Query & @Modifying without using nativeQuery or save() or saveAndFlush()

Question

I have seen these links

How to use JPA Query to insert data into db? which uses nativeQuery=true
How to insert into db in spring-data? which suggests using built-in save method (there is also a saveAndFulsh() method)

My example is below:

Person is a simple Entity w/ 3 fields "Long id, String name, Integer age", and, maps to a corresponding Person table w/ 3 columns per above)

@Repository
public interface DualRepository extends JpaRepository<Dual,Long> {
    @Modifying
    @Query(? - what goes here - ?)
    public int modifyingQueryInsertPerson(@Param("id")Long id, @Param("name")String name, @Param("age")Integer age);
}

Is there way to do the insert by just using @Query & @Modifying (i.e. without using native SQL query & nativeQuery=true, or, save(), or, saveAndFlush() ?

score 9 · Answer 1 · answered Apr 14 '20 at 04:30

Rather passing all parameters you can pass java object like below

 @Modifying(clearAutomatically = true)
    @Transactional
    @Query(value = "insert into [xx_schema].[shipment_p] (gpn,qty,hscode,country_of_origin,created_date_time,shipment_id) "
            + "VALUES (:#{#sp.gpn},:#{#sp.qty},  :#{#sp.hscode} ,:#{#sp.countryOfOrigin}, :#{#sp.createdDateTime}, :#{#sp.id} )", nativeQuery = true)
    public void saveShipmentPRoducts(@Param("sp") ShipmentProducts sp);

score 7 · Accepted Answer · answered Aug 18 '17 at 03:08

After trying several things, there is a way to do this but it depends on the db you're using.

Below worked for me in Oracle & 1 row was inserted into the table (using Dual table because I can use "from Dual" after the "select"):

@Repository
public interface DualRepository extends JpaRepository<Dual,Long> {
    @Modifying
    @Query("insert into Person (id,name,age) select :id,:name,:age from Dual")
    public int modifyingQueryInsertPerson(@Param("id")Long id, @Param("name")String name, @Param("age")Integer age);
}

In MS SqlServer it's possible to have a "select" without a "from clause", so "select 10,'name10',100" works, so the below should work for MS Sqlserver (but have not tested this)

@Repository
public interface PersonRepository extends JpaRepository<Person,Long> {
    @Modifying
    @Query("insert into Person (id,name,age) select :id,:name,:age")
    public int modifyingQueryInsertPerson(@Param("id")Long id, @Param("name")String name, @Param("age")Integer age);
}

I've not tried w/ any other databases. Here's a link which shows (at the end) which db's support select stmts without a from clause : http://modern-sql.com/use-case/select-without-from

Upon further testing, it *does not* work in MS-SqlServer because MS-SqlServer does not provide a system dummy table (like DUAL in Oracle). It *does* work in Oracle & MySQL because they both have a DUAL dummy table. Since HQL requires a FROM after SELECT, above approach will work for INSERTs only if the underlying database has a dummy table (like DUAL in Oracle). — , Aug 19 '17 at 01:00

score -1 · Answer 3 · answered Aug 18 '17 at 07:56

-1

@Query Usually used to Create custom User Query to fetch the value from Data Base

@Query with @Modifying used to perform the update operation in database

save method used to insert the new records or update the records present in session.

answered Aug 18 '17 at 07:56

Sathyendran a

1,709
4
21
27

Please see my comment above. INSERT using @Query+@Modifying will work on databases which have a dummy table (like DUAL in Oracle) so that we can have a FROM clause after SELECT (which is what HQL requires). – Aug 19 '17 at 01:03

spring-data-jpa to insert using @Query & @Modifying without using nativeQuery or save() or saveAndFlush()

3 Answers3