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?