How to delete/getList using JPA in Spring Boot Application

Question

I have an entity:

@Entity
@Table(name ="cats")
public class Cat {
    @Id
    @Column(name="name")
    private String name;

    @Column(name="age")
    private int age;

    @Column(name="color")
    private String color;

    @Column(name="weight")
    private int weigth;
  ..
}

1. I need to delete it from database using EntityManager:

@Override
public void delete(Cat cat) {
    entityManager.remove(cat);
}

Problem: I have a Map<String, Cat> which contains all this elements. I get it by name from map IllegalArgumentException -> "Removing a detached instance com.entities.Cat#cats".

Question: How can i do it without getting from database by key?

2. I need to getList with limit and offset.

To get all the elements i can just use:
entityManager.createNativeQuery("SELECT name, age, color, weight FROM cats");

Without entityManager i used prepatedStatement with:

"SELECT name, age, color, weight FROM cats LIMIT ?,?"

Question:
How can i do it using entityManager?
Do entityManager have something like preparedStatement?

Ayrton · Accepted Answer · 2019-02-23T04:09:15.290

2

With EntityManager you can use Query objects. It provides you with several different methods to build your queries, which you can see in the Docs.

From there, you can use a Query to perform a select or execute an update into the db.

Update example:

//:id is a parameter you can set
Query query = entityManager.createQuery("delete from Entity e where e.id = :id");
query = query.setParameter("id", id);
query.executeUpdate();

Select example (using TypedQuery which implements Query:

String sql = "select e from Entity e";
TypedQuery<Entity> query = entityManager.createQuery(sql, Entity.class);
System.out.println(query.getResultList());

You can determine limit and offset like this:

query = query.setFirstResult(offset);
query = query.setMaxResults(limit);

If you have an entity at hand you can (and should) delete it using your EntityManager with remove(). You're getting that error because your entity is detached - that is, your EntityManager isn't aware of its existence.

To "attach" entities to your manager you can use merge(). However, if said entity doesn't exist in the database it will be inserted, and if it exists but has different fields from your object it will be updated.

public void delete(Cat cat) {
    if(!entityManager.contains(cat)) {
        entityManager.merge(cat);
    }

    entityManager.remove(cat);
}

To insert entities for the first time you can also use persist(). For the differences between merge() and persist(), see this.

edited Feb 23 '19 at 04:09

answered Feb 23 '19 at 03:38

Ayrton

2,218
1
14
25

I am having only a little problem with it - when i am trying to get list it works, but in String sql = "select e from Entity e" compiler says "Can't resolve symbol Entity" – Donatello Feb 24 '19 at 01:51
That's because you should replace "Entity" with your own class – Ayrton Feb 24 '19 at 02:03
I replaced, it was just for an example where i have a problem – Donatello Feb 24 '19 at 02:04
Please add stack trace – Ayrton Feb 24 '19 at 02:10
It works but intellij idea says "can't resolve symbol Cat" in my case. But when i am trying to run it, it gives me correct result, without exceptions, errors etc. – Donatello Feb 24 '19 at 02:11
If the query is working then it's working. Have you added all required imports? – Ayrton Feb 24 '19 at 02:21
Yes, it propose me to read more about it - "This inspection controls whether the Persistence QL Queries are error-checked". – Donatello Feb 24 '19 at 02:29
Check this out, might help: https://stackoverflow.com/questions/12420996/intellij-idea-highlights-entity-class-names-with-cannot-resolve-symbol-in-jpq – Ayrton Feb 24 '19 at 02:47
Project Structure - Facets - add - JPA. Just added it in my project(even without specifying config jpa xml) and now i don't have this error message. – Donatello Feb 24 '19 at 02:49
Is it possible to specify firstResult as: i have @ID of entity, i need some quatity of elements after an element with this @ID? – Donatello Mar 02 '19 at 14:41
The question is here https://stackoverflow.com/questions/54959811/how-to-getlist-using-jpa-when-the-first-entity-is-the-following-entity-after-the – Donatello Mar 02 '19 at 14:58
If you're ordering by ID and want from the first onwards you can just use an `ORDER BY` clause – Ayrton Mar 02 '19 at 15:19

score 1 · Answer 2 · answered Feb 23 '19 at 08:50

If you need to use EntityManager, then simply use reference:

entityManager.remove(entityManager.getReference(Cat.class, id));

This way the entity won't be fetched from db, but will be deleted.

Using query is also an option:

Query query = entityManager.createQuery("delete from Entity e where e = :entity");
query = query.setParameter("entity", entity);
query.executeUpdate();

You can create Query using EntityManager#createQuery. Then set parameters: firstResult and maxResults:
```
query.setFirstResult(10).setMaxResults(20);
```
This will take 20 entities starting from 10th.

How to delete/getList using JPA in Spring Boot Application

2 Answers2