Delete query | Query

Execute a query deleting rows.

int rows = new QContact()
  .name.equalTo("rob@foo.com")
  .delete();

delete from contact where email = ?

Example 2

int rows = new QContact()
  .email.startsWith("rob")
  .firstName.eq("Rob")
  .delete();

delete from contact where email like ? escape'|'  and first_name = ?

Example 3

When a where expression is on a associated bean path like OneToMany then the sql delete uses a subquery.

int rows = new QCustomer()
  .contacts.email.contains("foo.com")
  .delete();

delete from customer where id in (
  select distinct t0.id
  from customer t0
  join contact u1 on u1.customer_id = t0.id
  where u1.email like ? escape'|'
)

Cascading delete

If the bean type that we are deleting has cascading delete defined by for example @ManyToOne(cascade=CascadeType.ALL) then the cascade will be honored.

Example

In the following example Customer cascades save and delete to billingAddress.

// setup - a customer with a billing address
Customer customer = new Customer("Bad Example");
customer.setBillingAddress(new Address("My Street", "Hopeful City"));
customer.save();


// deleting customer needs to cascade delete the billingAddress
int rows = new QCustomer()
.name.startsWith("Bad")
.delete();

The above ends up executing 4 statements in a transaction.

-- first select the ids of customers that will be deleted
select t0.id from customer t0 where t0.name like ? escape'' ; --bind(Bad%)

-- select the foreign keys that will be cascade deleted
select t0.id, t0.billing_address_id, t0.shipping_address_id from customer t0 where t0.id in (? )

-- delete the customers
delete from customer where id=?;

-- [cascade] delete the related billing addresses
delete from address where id=?;

persistCascade false

When the transaction has persist cascade turned off then the query will instead just execute the single delete statement.

try (Transaction transaction = DB.beginTransaction()) {

  // turn off persist cascade
  transaction.setPersistCascade(false);

  int deletedRows =
    new QCustomer()
      .name.startsWith("Bad")
      .delete();

  transaction.commit();
}

delete from customer where name like ? escape'' ; --bind(Bad%)

Compare SqlUpdate

If we want to use SQL directly we can instead use SqlUpdate to execute sql delete statements.