[hibernate-dev] Multi-table Bulk id strategy using Common Table Expressions

[Steve Ebersole]

How does one reference a CTE across multiple deletes via JDBC?  A CTE, by
definition, is available only during the execution of a single query.  In
most databases you "work around" that by grouping statements into a block
(GO, BEGIN/END etc).  This is what JDBC poorly attempt to mimic with
PreparedStatement batching.  So practically I am just not sure how we'd do
this from JDBC.  Are you thinking we'd build database-specific blocks?


On Wed, Mar 23, 2016 at 8:05 AM Vlad Mihalcea <mihalcea.vlad at gmail.com>
wrote:

> Hi,
>
> I found a very interesting solution for the Bulk id multi-table strategy
> for those cases when the DB management policy forbids creating Temporary
> Tables.
> This situation is documented in this Forum post:
>
> https://forum.hibernate.org/viewtopic.php?f=1&t=1043080&p=2489096#p2489096
>
> After doing some investigation, I found this project on GitHub:
>
> https://github.com/epiresdasilva/cte-multi-table-bulk-id-stategy
>
> After migrating it for Hibernate 5.x, I tested it and it works very nice,
> generating queries like this:
>
> with HT_announecement ( id ) as (
> values
>     (?), (?) )
> delete
>     from
>         announcement
>     where
>         (
>             id
>         ) IN (
>             select
>                 id
>             from
>                 HT_announecement
>         )
>
> I think we should take this idea and write and come up with a built-in
> strategy using CTE.
>
> What do you think?
>
> Vlad
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>