You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Konstantin <kn...@gmail.com> on 2008/12/22 17:12:48 UTC
Restrict collection items
Hello All
We are migrating from Hibernate to OpenJPA and we faced the following
problem: it seems there is no alternative to Hibernate's @Where annotation
for collection properties. This annotation allowed us to add arbitrary SQL
for collections loading, e.g. @Where("IS_DELETED=0").
We used this to filter so called "marked as deleted" records.
Could anybody suggest a way to filter collection when loading them from
database?
Using LRS will be too heavyweight solution for us.
Thanks in advance.
--
View this message in context: http://n2.nabble.com/Restrict-collection-items-tp1690569p1690569.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Restrict collection items
Posted by Konstantin <kn...@gmail.com>.
Perhaps I should put here getWhere() method too, because its modification is
needed for lazily fetched collections. It is quite obscure, but may be it
will help to someone in similar situation.
protected SQLBuffer getWhere(Select sel, boolean forUpdate) {
Joins joins = sel.getJoins();
if (sel.getJoinSyntax() == SYNTAX_SQL92
|| joins == null || joins.isEmpty())
{
SQLBuffer buf = sel.getWhere();
Map<Table, String> tables = new HashMap<Table, String>();
Collection columns;
if (buf != null) {
columns = buf.getColumns();
}
else {
columns = sel.getSelects();
}
for (Object item : columns) {
if (item instanceof Column) {
Column col = (Column) item;
for (String s : (Collection<String>)
sel.getTableAliases()) {
int i = s.indexOf(' ');
String tableName = s.substring(0, i);
if (col.getTable().getName().equals(tableName)) {
if
(col.getTable().containsColumn(DELETE_TS_COL))
tables.put(col.getTable(), s.substring(i +
1));
break;
}
}
}
}
StringBuilder sb = new StringBuilder();
for (String alias : tables.values()) {
sb.append(alias).append(".").append(DELETE_TS_COL).append("
IS NULL");
}
sel.where(sb.toString());
return sel.getWhere();
}
...
}
Regards,
Konstantin
--
View this message in context: http://n2.nabble.com/Restrict-collection-items-tp1690569p1802209.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Restrict collection items
Posted by Konstantin <kn...@gmail.com>.
No problem, but I'm sure my solution is too custom and simple to use it "as
is".
I have made a descendant of DBDictionary and overridden this method:
public SQLBuffer toTraditionalJoin(Join join) {
...
// do column joins
SQLBuffer buf = new SQLBuffer(this);
int count = 0;
for (int i = 0; i < from.length; i++, count++) {
if (count > 0)
buf.append(" AND ");
buf.append(join.getAlias1()).append(".").append(from[i]);
buf.append(" = ");
buf.append(join.getAlias2()).append(".").append(to[i]);
// My addition is here:
if (inverse && to[i].getTable().containsColumn(DELETE_TS_COL))
{
buf.append(" AND ");
buf.append(join.getAlias2()).append(".").append(DELETE_TS_COL).append(" IS
NULL");
}
}
...
}
The point is that every table which I want to filter contains the column
with predefined name (DELETE_TS_COL constant), so I can easily add required
condition.
In fact I have overridden one more method (getWhere()) to avoid adding
"entity.deleteTs is null" in each query and after entityManager.find(), but
it is obviously out of scope of this topic.
Of course it would be great if OpenJPA supports adding filter conditions for
loading entities and collections. There should be a way to define such
filters as annotations and through EntityManager interface. The latter is
needed because some filters may be dynamic - e.g. I want to switch off
DELETE_TS filtering when I need to work with "deleted" instances.
Regards,
Konstantin
Pinaki Poddar wrote:
>
> Hi,
> > I have managed to modify generated SQL by overriding a couple of
> DBDictionary methods,
> That's excellent. Having to specify a conditional filter on collection
> is a useful and often requested feature.
> If you post the changes, they can be integrated with the code base.
>
>
>
--
View this message in context: http://n2.nabble.com/Restrict-collection-items-tp1690569p1802159.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Restrict collection items
Posted by Pinaki Poddar <pp...@apache.org>.
Hi,
> I have managed to modify generated SQL by overriding a couple of
DBDictionary methods,
That's excellent. Having to specify a conditional filter on collection is
a useful and often requested feature.
If you post the changes, they can be integrated with the code base.
--
View this message in context: http://n2.nabble.com/Restrict-collection-items-tp1690569p1771060.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Restrict collection items
Posted by Konstantin <kn...@gmail.com>.
Hello Milosz
Thank you for reply.
You are right, in theory constant join may do what I need, but it would be
too painful for us to annotate all collection properties so verbose. We use
"deferred delete" for most of entities, and I have managed to modify
generated SQL by overriding a couple of DBDictionary methods, thanks to
OpenJPA extendable architecture.
Miłosz Tylenda wrote:
>
> Konstantin,
>
> Maybe you could achieve a similar effect by using a "constant join". Have
> a look at the following section in the manual:
>
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_mapping_notes_nonstdjoins
>
> Regards,
> Milosz
>
>> Hello All
>>
>> We are migrating from Hibernate to OpenJPA and we faced the following
>> problem: it seems there is no alternative to Hibernate's @Where
>> annotation
>> for collection properties. This annotation allowed us to add arbitrary
>> SQL
>> for collections loading, e.g. @Where("IS_DELETED=0").
>> We used this to filter so called "marked as deleted" records.
>>
>> Could anybody suggest a way to filter collection when loading them from
>> database?
>> Using LRS will be too heavyweight solution for us.
>>
>> Thanks in advance.
>>
>> --
>> View this message in context:
>> http://n2.nabble.com/Restrict-collection-items-tp1690569p1690569.html
>> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>>
>>
>
>
--
View this message in context: http://n2.nabble.com/Restrict-collection-items-tp1690569p1712342.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Restrict collection items
Posted by Miłosz Tylenda <mt...@o2.pl>.
Konstantin,
Maybe you could achieve a similar effect by using a "constant join". Have a look at the following section in the manual:
http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_mapping_notes_nonstdjoins
Regards,
Milosz
> Hello All
>
> We are migrating from Hibernate to OpenJPA and we faced the following
> problem: it seems there is no alternative to Hibernate's @Where annotation
> for collection properties. This annotation allowed us to add arbitrary SQL
> for collections loading, e.g. @Where("IS_DELETED=0").
> We used this to filter so called "marked as deleted" records.
>
> Could anybody suggest a way to filter collection when loading them from
> database?
> Using LRS will be too heavyweight solution for us.
>
> Thanks in advance.
>
> --
> View this message in context: http://n2.nabble.com/Restrict-collection-items-tp1690569p1690569.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
>