8

I have seen these links

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() ?

Improve this question

3 Answers 3

Reset to default
10

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

8

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

Improve this answer

1 Comment

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).
-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.

Improve this answer

1 Comment

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).