Hibernate 4 Delete from HT_tables where IN clause

问题:

Using Hibernate 4, the below generated query failed :

delete from ErpEmploye_AUD where (code, folder_codeId, REV) 
    IN 
(select code, folder_codeId, REV from HT_ErpEmploye_AUD where hib_sess_id=1)

Firebird Exception :

SQL error code = -104
Token unknown - line 1, column 39
,

---

In the org.hibernate.hql.spi.TableBasedDeleteHandlerImpl Class there is https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/hql/spi/TableBasedDeleteHandlerImpl.java

    private String generateDelete(String tableName, String[] columnNames, 
                    String idSubselect, String comment) {
        final Delete delete = new Delete()
            .setTableName( tableName )
            .setWhere( "(" + StringHelper.join( ", ", columnNames ) +
 ") IN (" + idSubselect + ")" );          
        return delete.toStatementString();
    }

So i want to adapt the code to Firebird, what is the nearest Firebird syntaxe to do same job?

i just migrate to hibernate 4, and it apeare that every delete from the temporary tables added in hibernate 4 (prefixed with HT_ ...) is based on this method so it looks as an important issue.

回答1:

First the nearest Firebird syntaxe is

delete from ErpEmploye_AUD e where 
exists( select code, folder_codeId, REV from HT_ErpEmploye_AUD ht
 where ( hib_sess_id=1 and e.code=ht.code and e.folder_codeId=ht.folder_codeId and e.REV=ht.REV ))

Second the right hibernate generateDelete code :

private String generateDelete(String tableName, String[] columnNames, String idSubselect, String comment) {
    String[] columnEquals = new String[columnNames.length];
    for (int i=0;i<columnNames.length;i++){
        columnEquals[i] = "tr."+columnNames[i]+"=ht."+columnNames[i];
    }
    if (idSubselect.contains("where"))
        idSubselect = idSubselect.replace("where", "ht where");
    else
        idSubselect = idSubselect + " ht where";
    final Delete delete = new Delete()
            .setTableName( tableName+" tr " )
            //.setWhere( "(" + StringHelper.join( ", ", columnNames ) + ") IN (" + idSubselect + ")" );
            .setWhere( "exists("+ idSubselect +" "+ StringHelper.join( " and ", columnEquals ) + ")" );     

    if ( factory().getSettings().isCommentsEnabled() ) {
        delete.setComment( comment );
    }
    return delete.toStatementString();
}

I hope that this should be generalised in hibernate, because this is not a standard code, or could be supported by Firebird