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

Issue

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

Solution

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

Answered By - user3448327