views:

20

answers:

0

I'm using Hibernate and Derby. I've this class:

public class Sensorboard {
    //...
    @ManyToMany(fetch=FetchType.LAZY)
    @JoinTable(name="SENSORBOARD_SENSORBOARD_JOIN",
        joinColumns=
            @JoinColumn(name="SENSORBOARD_ID", referencedColumnName="ID"),
        inverseJoinColumns=
            @JoinColumn(name="NEXTSENSORBOARD_ID", referencedColumnName="ID")
    )
    private List<Sensorboard> nextSensorboard;
}

I'm looking for an efficient way to delete about 1 million connections of a certain type. From my understanding, I cannot use hibernate directly to perform such a bulk update, without reading 1 million instances from the database.

In apache derby ij, this statement works well:

DELETE FROM
    SENSORBOARD_SENSORBOARD_JOIN AS j
WHERE EXISTS (
    SELECT
        0
    from
        SENSORBOARD AS s1,
        SENSORBOARD AS s2,
        SENSORBOARDTYPE AS t
    WHERE
        j.SENSORBOARD_ID = s1.ID AND j.NEXTSENSORBOARD_ID = s2.ID
        AND (s1.SENSORBOARDTYPE_ID = t.ID OR s2.SENSORBOARDTYPE_ID = t.ID)
        AND t.NATIVETYPE = 'Simulator'
)

I want to run the statement native from my software, but this fails:

EntityManager.createNativeQuery(...)
//Leads to org.hibernate.exception.SQLGrammarException: 
//could not execute native bulk manipulation query

Even creating the query using a org.apache.derby.jdbc.ClientDriver instance directly, throws a derby created Exception.

So my only workaround right now is to call derbies ij and pass the statement by command line argument. Any ideas?