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.

Manteltree

In JPA you Setup objects and uses the entitymanagers persist method to save them – Kampala

@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? – Manteltree

Maybe this answers your question ( Google jpa insert) – Kampala

@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 :(. – Manteltree

You can if you use em.createNativeQuery – Kampala

@Jens: I tried it, but with no result :) Otherwise, I wouldn't write this question. – Manteltree

What was the Problem with nativeQuery? – Kampala

@Jens: another exception is thrown mentioned in the updated question. – Manteltree

Let us continue this discussion in chat. – Kampala

This your query must remove last semicolon. @Manteltree – Ginetteginevra

Answer 1

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 is solved by removing it.

Manteltree

Answer 2

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.

Horripilate

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. – Manteltree

Answer 3

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

Gameness

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. – Manteltree

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(). – Sphincter

1st semicolon - end of the query and the next is to end the statement . This actually worked for me. – Gameness

Answer 4

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.

Adaptation