In the current project, I had to do a search engine on my knee to the SQL database. Now I have this scheme:

<active_proxy_view> id | entity | started at | expired at <uuid> | <json> | <date> | <date> <archive_proxy_view> id | entity | started at | expired at <uuid> | <json> | <date> | <date> <metadata_reference> entity_type | entity_id | metadata_key | metadata_value proxy | <uuid> | visitors | 13 proxy | <uuid> | trial | yarr <tracking_reference> entity_type | entity_id | tracked_domain | tracked_type | tracked_id proxy | <uuid> | campaign | carnival | halloween'16

This all allows me to make samples of the following type "to find all valid proxies corresponding to Halloween 16, which began after hour X, which had thirteen visitors and that had trial access":

 SELECT v.entity FROM active_proxy_view AS v INNER JOIN metadata_reference AS m1 ON m1.entity_type = 'proxy' AND m1.entity_id = '?uuid' AND m1.metadata_key = 'visitors' AND m1.metadata_value = '13' INNER JOIN metadata_reference AS m2 ON m2.entity_type = 'proxy' AND m2.entity_id = '?uuid' AND m2.metadata_key = 'trial' AND m2.metadata_value = 'yarr' INNER JOIN tracking_reference AS t1 ON t1.entity_type = 'proxy' AND t1.entity_id = '?uuid' AND t1.tracking_domain = 'campaign' AND t1.tracked_type = 'carnival' AND t1.tracked_id = 'halloween\'16' WHERE started_at > '?hourX'

so far so good. problems start with the current stack, because everything is implemented through spring and hibernate. As a result, it turns out that records from index tables (tracking_reference, metadata_reference) have two related records in view tables, and Hibernate deletes related collections from at least one table when deleting a record, thus de-energizing the second table. At the moment it looks like this:

 @Entity @Inheritance(strategy = InheritanceType.TABLE_PER_CLASS) public class ProxyViewEntryTemplate { ... @OneToMany(fetch = FetchType.LAZY, cascade = {}) @Cascade({}) @JoinColumn(name = MetadataRef.REFERENCE_ID_COLUMN_NAME) @Where(clause = "entity_type = 'proxy'") @SQLDeleteAll(sql = "DELETE FROM metadata_reference WHERE entity_id = ? and entity_type = 'proxy'") @OnDelete(action = OnDeleteAction.NO_ACTION) private Collection<MetadataReference> metadata = new ArrayList<>();

And still, even with such straps, the hibernate strives to delete my records from the index tables, and I cannot do without specifying @OneToMany , because otherwise spring specification / criteria api will not work with joins. I can be saved by specifying the rubbish in @SQLDeleteAll (just add WHERE 0 = 1 ), but this is some kind of very crutch step. Are there any normal ways to prevent a hibernate from deleting related entities?

  • The SQLDeleteAll and OnDelete (action = OnDeleteAction.NO_ACTION) annotations may conflict. I, however, never used such, but it seems that @OnDelete prohibits deletion, while SQLDeleteAll tells you how to delete; what Hibernate chooses is unclear. About "criteria api will not be able to work with joins" - you can use the methods of NativeQuery and write a sample in pure SQL, and in the method specify the mapping of the final entity - (sql, Entity.class). At the same time, there is no need to map tables from a SQL query in the final entity. - bobzer
  • @bobzer native sql is not an option at all. One OnDelete I will try one - etki
  • @bobzer judging by the tests that I managed to get rid of (I only have a very slow car at hand), nothing has changed. - etki

0