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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.