JPA + Hibernate, bulk delete with IS NULL, SQL command not properly ended

Question

I am trying to do a bulk delete.

Query queryDeleteEntries = entityManager.createQuery("DELETE from
  Entry r where (r.person.emailAddress like :name) and (r.otherData is null)");
int deletedEntriesCount = queryDeleteEntries.setParameter("name",
  "tester." + emailAddressSuffix).executeUpdate();

I am getting an error:

Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) ~[DatabaseError.class:Oracle JDBC Driver version - "10.2.0.2.0"]

Without "and r.otherData is null" it works, so I don't see what the problem is with that?

otherData is a list and it is defined in the Entry class that way:

@OneToMany(fetch = FetchType.EAGER)
@JoinColumn(name = "ENTRY_ID", referencedColumnName = "ID")
private List<OtherData> otherData;

OtherData (name changed) class:

@Entity
@Table(name = "OTHERDATA")
@SequenceGenerator(name = "SEQ", allocationSize = 1, sequenceName = "SEQ_OTHERDATA_ID")
public class OtherData {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ")
    private Long id;

    ...

    @Column(name = "ENTRY_ID")
    private Long entryId;

Can I not use "is null" on lists?

EDIT

I tried it with "size(r.otherData) = 0" and with "r.otherData IS EMPTY", but both led to the same exception.

What DOES work is this:

Query queryDeleteEntries = entityManager.createQuery("DELETE from Entry r where id in "
  + "(select id from Entry r2 where r2.person.emailAddress like :name"
  + " and not exists (select 1 from OtherData a where a.entryId = r2.id))");
int deletedEntriesCount = queryDeleteEntries.setParameter("name", "tester."+emailAddressSuffix).executeUpdate();

But I think it shouldn't be that complicated! Isn't there an easier way to do that?

Thanks in advance for any hints!

Possible duplicate of [HQL Query to check if size of collection is 0 or empty](http://stackoverflow.com/questions/3760711/hql-query-to-check-if-size-of-collection-is-0-or-empty) — OH GOD SPIDERS, Oct 06 '16 at 16:26
I tried the suggested "is empty" but I am getting the same error. — Nina, Oct 06 '16 at 16:30

score 0 · Answer 1 · answered Oct 06 '16 at 16:44

I would recommend taking the generated query and see how hibernate is translating to native Oracle query, by logging the hibernate packages or simply make add this to hibernate.cfg.xml :

<property name="show_sql">true</property>

also try to add .otherData.idOtherData just to see the behaviour.

JPA + Hibernate, bulk delete with IS NULL, SQL command not properly ended

EDIT

1 Answers1