How to make insert custom query in spring data jpa by using @Query annotation

Question

I am using mssql and spring data JPA, I want to insert new records to a table by using custom @Query annotation.

public interface CustomerRepository extends JpaRepository<Customers, String>{


    @Modifying
    @Query("insert into Customers values (?1 , ?2)")
    public void saveCutomer(int custId, Customer cust);

}

Its giving error,

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting OPEN, found 'values' near line 1, column 23 [insert into Customers values (?1 , ?2)]

I tried below also, same error.

@Modifying
    @Query("insert into Customers select ?1 , ?2")
    public void saveCutomer(int custId, Customer cust);

Maciej Kowalski · Accepted Answer · 2019-08-13 08:46:48Z

2

You don't do that using JPQL. You have batch DELETE and UPDATE but that's it.

Your options are:

1) Mark the query as native if you really make explicit insert (not recommended unless you intend to use some database-specific syntax not supported by JPQL.

2) Use standard save(Entity) method on your repository which is of course preferable.

answered Aug 13, 2019 at 8:46

Maciej Kowalski

26.7k12 gold badges59 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mohan Over a year ago

i tried the below, its working @Query(value="insert into Customers values (?1 , ?2)", nativeQuery = true) i am not using save(Entity) because i am using ms sql and i am trying to keep all data in a single column. any idea, how to do this?? I am using Angular 8 for the UI.

Maciej Kowalski Over a year ago

You have to stick with the native then

mohan Over a year ago

thanks for your time, i am getting below error while inserting into mssql, com.microsoft.sqlserver.jdbc.SQLServerException: An explicit value for the identity column in table 'Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Collectives™ on Stack Overflow

How to make insert custom query in spring data jpa by using @Query annotation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related