Hi Tom,
Yes you are right i can always run a native query to fine tune, got your persistence context cache point but i am using ejb method to do jpa work and my entity manager is transaction demarcated not extended.
But since i am implementing a generic wrapper which should work for all entities i do not want to do that. Currently in my ejb i have an operation delete(BusinessObject bo); Now what should be the best implementation of this ejb method with following goals in mind:
1- Performance
2- Cascade delete relationships
3- Delete operation should require minimal possible data best case only primary key of the record to remove
4- Validate if entity being removed does exist in db
//Note: All my jpa entities inherit from BusinessObject interface
interface BusinessObject{
public Object getId();
}
I have following alternatives:
1- em.remove(em.merge(bo));
On transaction commit this is will first update in db and then delete it. This breaks goal 1 and 3 because if i send a BusinessObject instance with only id set it will generate all sorts of errors on updating the record with nulls in other properties / columns
2-
BusinessObject storedBo = em.read(bo.getId());
if(storedBo != null){
em.remove();
}
This defeats goal 1 of performance. But is still better than first case as it achieves goal 3.
I do not know how to achieve goal 2 using JPA apart from manually specifying ON CASCADE DELTE on fk relationships. I wonder why EclipseLink does not generate cascade constraints in schema script.
Secondly the question about manytoone relationship joins was a separate question. Most of my jpa entities have a manytoone relationship with a User entity. Whenever i do any operation on any entity i want to restrict that logged in user can perform operation on its own data (instance based authorization). For example one such operation is querying for data. User sends a query
SELECT o FROM Audio o
i will modify this query to make it
SELECT o FROM Audio o WHERE o.user.id = ?logged_in_userid
The above jpa query translates to a join with User table, even when Audio table has a foreign key user id. This is a performance hit and i want to avoid it. To add a such a condition to jpa string query is also an interesting part. You may want to hear it and give comments. I researched on it and found out that JPA 1.0 do not have support for manipulating queries dynamically, it is however provided in JPA 2.0 with Criteria API. So i have to write a little JpqlWrapper class. I searched EclipseLink sourced code to find a jpql parser that can make my code more robust and error free but failed. I know below case miss a lot from jpql BNF but it worked for my scenario. Any suggestion on this one are highly appreciated.
public class JpqlWrapper {
Log log = LogFactory.getLog(this.getClass());
StringBuilder query;
public JpqlWrapper(String query) {
this.query = new StringBuilder(query);
}
public void and(String condition) {
String lowercase = query.toString().toLowerCase();
if (!lowercase.isEmpty()) {
int appendAt = lowercase.lastIndexOf(" order by");
if (appendAt == -1) {
appendAt = lowercase.length();
}
if (lowercase.indexOf(" where ") == -1) {
query.insert(appendAt, " where " + condition + " ");
} else {
query.insert(appendAt, " and " + condition + " ");
}
}
}
public String getFirstIdentificationVariable() {
Pattern pattern = Pattern.compile("from +\\w+ +\\w+",
Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(query);
if (matcher.find()) {
return matcher.group().substring(
matcher.group().lastIndexOf(" ") + 1);
}
throw new IllegalArgumentException("Unexpected query: "
+ query.toString());
}
public String getFromObject() {
Pattern pattern = Pattern
.compile("from +\\w+", Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(query);
if (matcher.find()) {
log.debug("Match: " + matcher.group());
return matcher.group().substring(matcher.group().lastIndexOf(" ") + 1);
}
throw new IllegalArgumentException("Unexpected query: "
+ query.toString());
}
public String toString() {
return query.toString();
}
}
In my query ejb method i do something like.
if (entity instanceof UserAssociation) {
UserAssociation userAssociated = (UserAssociation) entity;
userAssociated.setAssociatedUser(getCallingUser());
jpql.and(jpql.getFirstIdentificationVariable() + "."+ userAssociated.getUserAssociation() + ".id = "+ userAssociated.getAssociatedUser().getId());
}
em.query(jpql.toString())
Note in my ejb i get the executing user id from jaas principals collections using weblogic specific code (bad i know but isCallerInRole and getCallerPrincipal were not working):
Set<Principal> principals = weblogic.security.Security.getCurrentSubject().getPrincipals();
So moving on i had to do the same instance based authorization in case of create, update, delete and read operations.
For create i create a new User instance and set it on the entity.
For update i have to read the entity, verify that if belongs to executing user and then set it on the instance,
For delete i again have to read the entity, verify if it belongs to executing user
For read i have to query instead of em.find() as i have to insert and condition for user.
To enable all of above, my entities inherit an interface
interface UserAssociation{
User getAssociatedUser();
void setAssociatedUser();
String getUserAssociation(); //return user association in form of string that i can concatenate
//in jpql e.g."user", "group.user" etc
}
If you have a better way to do this instance based authorization, please share.
Thanks for your time.
Regards,
Jehanzeb Qayyum
On Thu, Dec 24, 2009 at 7:09 PM, Tom Ware
<tom.ware@xxxxxxxxxx> wrote:
Hi jz,
You should be able to use a native query to run any pure SQL you want to run. If you are deleting with a native query, you will want to be careful that you definitely have not read the object yet since a native query will not remove objects from the persistence context or the cache.
Can you give an example of the query you are running and the SQL that is produced that causes your issue with m-1 relationships?
-Tom
jz wrote:
Hi,
How can i delete a detached entity without reading it first?
Why eclipselink creates a join sql in case of manytoone relationships if query contains only FK?
Regards,
Jehanzeb Qayyum
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
