How to construct an insert query in JPA

Question

I am trying to insert data into a table having columns (NAME, VALUE) with

Query query = em.createQuery("INSERT INTO TestDataEntity (NAME, VALUE) VALUES (:name, :value)");
query.setParameter("name", name);
query.setParameter("value", value);
query.executeUpdate();

and getting the following exception:

ERROR org.hibernate.hql.internal.ast.ErrorCounter - line 1:42: unexpected token: VALUES

Also, I cannot insert a record using a native query either:

Query query = em.createNativeQuery("INSERT INTO TEST_DATA (NAME, VALUE) VALUES (:name, :value);");
query.setParameter("name", name);
query.setParameter("value", value);
query.executeUpdate();

Another exception is being thrown:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement

The question is:

What is wrong with the query string?

Many thanks.

In JPA you Setup objects and uses the entitymanagers persist method to save them — Jens, Apr 11 '17 at 13:40
@Jens: If I do select or update or delete similar to the above, everything is executed as they should be, then what's wrong with this query? — Armine, Apr 11 '17 at 13:42
Maybe [this](http://stackoverflow.com/questions/3085716/jpa-insert-statement) answers your question ( Google jpa insert) — Jens, Apr 11 '17 at 13:46
@Jens: Thanks for the link. I was thinking, that maybe there is no insert in hibernate, but I cannot insert a record via a native query either :(. — Armine, Apr 11 '17 at 14:03
@Jens: I tried it, but with no result :) Otherwise, I wouldn't write this question. — Armine, Apr 11 '17 at 14:06
@Jens: another exception is thrown mentioned in the updated question. — Armine, Apr 11 '17 at 14:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/141439/discussion-between-jens-and-armine). — Jens, Apr 11 '17 at 14:12

score 22 · Accepted Answer · edited May 23 '17 at 11:54

22

I solved the issue.

According to this,

There is no INSERT statement in JPA.

But I could solve the issue with native query: I have mistakenly put a redundant ; at the end of the query, so the issue solved by removing it.

edited May 23 '17 at 11:54

Community

1
1

answered Apr 11 '17 at 15:12

Armine

1,675
2
24
40

score 3 · Answer 2 · answered Aug 24 '17 at 09:07

3

I found two examples where the author uses the insert in a native query (first and second). Then, your query could be:

Query query = em.createQuery("INSERT INTO TestDataEntity (NAME, VALUE) VALUES (?, ?)");
query.setParameter(1, name);
query.setParameter(2, value);
query.executeUpdate();

Try this.

answered Aug 24 '17 at 09:07

Máťa - Stitod.cz

885
7
12

As I have mentioned in my answer, I was able to insert data into the table with the native query. There was another problem: a redundant ";" was at the end of the query and that was causing the problem. Once I removed it, the native query worked as expected. Anyway, thanks a lot for the answer. – Armine Sep 05 '17 at 11:53

score 1 · Answer 3 · answered Apr 28 '19 at 19:33

1

I was able to do this using the below - I just had a Table name Bike with 2 fields id and name . I used the below to insert that into the database.

Query query = em.createNativeQuery("INSERT INTO Bike (id, name) VALUES (:id , :name);");
em.getTransaction().begin();
query.setParameter("id", "5");
query.setParameter("name", "Harley");
query.executeUpdate();
em.getTransaction().commit();

answered Apr 28 '19 at 19:33

Rajdeep Singh

11
1

I doubt if you really could run the code as it is written in your case without exception since there is redundant semicolon - ';' which prevents the successful run. – Armine Apr 29 '19 at 07:55
1

Caused by: java.lang.IllegalStateException: Not allowed to create transaction on shared EntityManager - use Spring transactions or EJB CMT instead. Getting the above exception when start using begin() and commit(). – Paramesh Korrakuti May 29 '19 at 06:36
1st semicolon - end of the query and the next is to end the statement . This actually worked for me. – Rajdeep Singh Jun 12 '19 at 17:50

score 1 · Answer 4 · answered Dec 27 '21 at 11:19

For manually created queries, we can use the EntityManager#createNativeQuery method. It allows us to create any type of SQL query, not only ones supported by JPA. Let's add a new method to our repository class:

@Transactional
public void insertWithQuery(Person person) {
    entityManager.createNativeQuery("INSERT INTO person (id, first_name, last_name) VALUES (?,?,?)")
      .setParameter(1, person.getId())
      .setParameter(2, person.getFirstName())
      .setParameter(3, person.getLastName())
      .executeUpdate();
}

With this approach, we need to define a literal query including names of the columns and set their corresponding values.

How to construct an insert query in JPA

4 Answers4

Linked