CODEWITHSUNDEEP
hibernateinsertspring-dataspring-data-jpa
user3448327
user3448327

Reputation:

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

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

Upvotes: 8

Views: 41189

Answers (3)

Santhosh Hirekerur
Santhosh Hirekerur

Reputation: 876

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

Upvotes: 10

Sathyendran a
Sathyendran a

Reputation: 1819

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

Upvotes: -1

user3448327
user3448327

Reputation:

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

Upvotes: 8

Related Questions