删除语句

基本用法

Delete语句用法如下

Java

BookTable book = Tables.BOOK_TABLE;

int affectedRowCount = sqlClient
    .createDelete(book)
    .where(book.name().eq("Learning GraphQL"))
    .execute();
System.out.println("Affected row count: " + affectedRowCount);

Kotlin

val affectedRowCount = sqlClient
    .createDelete(Book::class) {
        where(table.name eq "Learning GraphQL")
    }
    .execute()
println("Affected row count: $affectedRowCount")

生成的SQL如下：

delete 
from BOOK as tb_1_ 
where tb_1_.NAME = ?

使用JOIN

Delete语句用法如下支持JOIN子句，如下

Java

BookTable book = Tables.BOOK_TABLE;

int affectedRowCount = sqlClient
    .createDelete(book)
    .where(book.store().name().eq("MANNING"))
    .execute();
System.out.println("Affected row count: " + affectedRowCount);

Kotlin

val affectedRowCount = sqlClient
    .createDelete(Book::class) {
        where(table.store.name eq "MANNING")
    }
    .execute()
println("Affected row count: $affectedRowCount")

最终生成了3条SQL:

1. select 
       distinct tb_1_.ID 
   from BOOK as tb_1_ 
   inner join BOOK_STORE as tb_2_ 
       on tb_1_.STORE_ID = tb_2_.ID 
   where 
       tb_2_.NAME = ?

2. delete from BOOK_AUTHOR_MAPPING 
   where BOOK_ID in(?, ?, ?)

3. delete from BOOK 
   where ID in(?, ?, ?)

备注

如果在Delete语句中使用join，Jimmer会将之翻译成select + delete。先利用带有join子句的select语句查询到需要删除的数据id，然后使用Delete指令删除数据。

这个方案对任何数据库都有效。