CODEWITHSUNDEEP
javahibernatejpaentity
Armine
Armine

Reputation: 1695

How to construct an insert query in JPA

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:

Many thanks.

Upvotes: 15

Views: 83662

Answers (4)

Armine
Armine

Reputation: 1695

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.

Upvotes: 22

Ikbel
Ikbel

Reputation: 2203

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.

Upvotes: 1

Rajdeep Singh
Rajdeep Singh

Reputation: 11

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

Upvotes: 1

Máťa - Stitod.cz
Máťa - Stitod.cz

Reputation: 893

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.

Upvotes: 3

Related Questions