Native Insert Query in Hibernate + Spring Data

native insert query in hibernate + spring data

I had to add nativeQuery = true to @Query

@Query(value = "insert into commit_activity_link (commit_id, activity_id) VALUES (?1, ?2)", nativeQuery = true)

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

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

Running a native insert query in spring boot

An insert statement doesn't return the inserted record. Since you're expecting a list to be returned executeQuery() is executed by Spring. What you want is the executiong of executeUpdate() which is only executed for void methods.

See also Cannot issue data manipulation statements with executeQuery()

Get Entity ID after executing Native insert query + Spring JPA

You cannot make the INSERT INTO statement return anything. Depending on your database's support, on that end you could execute multiple statements, e.g., INSERT INTO ...; SELECT ..., but Spring Data JDBC does not support this.

• What you can do is implement saveNewUser and perform the insert then select, sequentially and synchronously. See this: https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#repositories.single-repository-behavior.
• If your database supports it, you could create a stored procedure that performs the insert then select.

How to use native query (insert into) to save data to databse?

In order to make your query work :-

@Repository
public interface UserRepository extends JpaRepository < UserEntity, Long > {

    @Transactional
    @Modifying
    @Query(value = "insert into students(name, user_id) values(?1, ?2)", nativeQuery = true)
    void insertDate(String firstName, Integer lastName); // sample

}

Insert native query in spring

I was able to make it work. My mistake was not using @Modifying annotation on top of the function. I was simple using @Transactional only. If you require more then one table to be impacted simultaneously then use the annotation as well. It got the job done for me. exapme snippet below

  @Transactional
  @Modifying
  @Query(value = Query.YOUR_QUERY, nativeQuery = true)
  void insert(@Param("param") var param1);

Native Query INSERT is giving ERROR : Method is only allowed for a Query

I was able to fix the above issue.

I had to specify @Transactional and @Modifying to my Repository method.

Below solved the issue.

@Repository
public interface StoreDataRepository extends JpaRepository<StoreDataRepository, Long> {

    @Trsansactional
    @Modifying
    @Query(value ="insert into store (id, name) values(:id, :name)", nativeQuery = true)
    public void storeData(@Param("id") int id, @Param("name") String name);

---

Related Topics