Hi all,
I've gotten only one reply on this proposal. Please post your
feedback.
thanks,
-Linda
On 3/28/2011 11:53 AM, Linda DeMichiel wrote:
> One of the features of JPQL that the criteria API lacks is the
> ability to issue bulk updates and deletes.
>
> Here's a cut at a strawman API for update and delete criteria queries.
> There are a number of issues here that are in need of feedback.
>
> There are 3 new interfaces, which I've attached to the message:
> UpdateQuery
> DeleteQuery
> CommonAbstractQuery
>
> I spent a long time mulling over whether to introduce a new type to
> model the target (i.e. entity/table) of the update operation because
> of the restricted semantics of bulk operation targets in SQL (i.e.,
> single table, no derived joins in the UPDATE clause) or whether to
> reuse Root. In the end, I decided to reuse Root, because of the
> extensions that many databases provide to support joins. In this
> case, the joined item is not itself updatable, but is used in the
> expression of constraints or as a source of new values. However, we
> will need to decide exactly what we state in terms of what is
> "defined" vs "undefined" in the spec. Probably the most conservative
> statement would be to state that the use of more than one root and/or
> the use of joins is undefined.
>
> Another issue is whether UpdateQuery and DeleteQuery should exist in
> any form of inheritance hierarchy to allow common information to be
> factored out. I've done this in the interfaces attached to this
> message. (The root of the factored hierarchy could certainly use a
> better name, however.)
>
>
> The CriteriaBuilder interface would be extended with methods
> like:
>
> <T> UpdateQuery<T> createUpdateQuery(Class<T> targetEntity);
> <T> UpdateQuery<T> createUpdateQuery(EntityType<T> targetEntity);
> <T> DeleteQuery<T> createDeleteQuery(Class<T> targetEntity);
> <T> DeleteQuery<T> createDeleteQuery(EntityType<T> targetEntity);
>
> And the EntityManager interface would be extended with methods like:
>
> public <T> TypedQuery createQuery(UpdateQuery<T> updateQuery)
> public <T> TypedQuery createQuery(DeleteQuery<T> deleteQuery)
>
>
> Example queries:
>
> // Simple Update
>
> UpdateQuery<Customer> q1 = cb.createUpdateQuery(Customer.class);
> Root<Customer> c1 = q1.from(Customer.class);
> q1.set(c1.get(Customer_.firstName), "Fred")
> .set(c1.get(Customer_.lastName), "Jones")
> .where(cb.equal(c1.get(Customer_.id), 1));
>
>
> // Simple Delete
>
> DeleteQuery<Customer> q2 = cb.createDeleteQuery(Customer.class);
> Root<Customer> c2 = q2.from(Customer.class);
> q2.where(cb.equal(c2.get(Customer_.id), 5));
>
>
> // Bulk Update
>
> UpdateQuery<Employee> q3 = cb.createUpdateQuery(Employee.class);
> Root<Employee> e3 = q3.from(Employee.class);
> q3.set(e3.get(Employee_.salary),
> cb.prod(e3.get(Employee_.salary), 1.1).as(BigDecimal.class))
> .where(cb.equal(e3.get(Employee_.dept).get(Department_.name),
> "Sales"));
>
>
> // Bulk Delete
>
> DeleteQuery<Employee> q4 = cb.createDeleteQuery(Employee.class);
> Root<Employee> e4 = q4.from(Employee.class);
> q4.where(cb.equal(e4.type(), Exempt.class));
>
>
> //Update query with multiple levels
>
> UpdateQuery<Customer> q5 = cb.createUpdateQuery(Customer.class);
> Root<Customer> c5 = q5.from(Customer.class);
> q5.set(c5.get(Customer_.address).get(Address_.city), "NewYork")
> .set(c5.get(Customer_.address).get(Address_.state), "NewYork")
> .where(cb.equal(c5.get(Customer_.id), 1));
>
>
> Opinions, please.....
>
> thanks,
>
> -Linda