SQL joins from JCR to database

[philip andrew <ph...@gmail.com>]

Hi,

I have a Jackrabbit running inside my H2 database set up by all those XML
files.

Is there any way to do a join between a normal database table and a Node in
the tree?

Currently, I store a ID in the node which matches the ID in the database,
but this means separate queries to database and JCR as I do not know a way
to do queries joined directly.

Thanks, Philip

Re: SQL joins from JCR to database

[Alexander Klimetschek <ak...@day.com>]

On Mon, Feb 1, 2010 at 15:55, philip andrew <ph...@gmail.com> wrote:
> I have a Jackrabbit running inside my H2 database set up by all those XML
> files.
>
> Is there any way to do a join between a normal database table and a Node in
> the tree?

Not directly. If you access the repository through the JCR API, it is
completely transparent how things are actually persisted. You would
run two queries at least, one in JCR to get a list of nodes and then
use that information for joining with your other data in the db.

When just going to the H2 DB directly, it will probably be hard to
build queries, as the standard persistence managers store the data in
a binary form, optimized for JCR usage. So you can't easily query for
property values, for example. (In Jackrabbit the query is done with
the help of Lucene indexes). The only thing readily available will be
the UUID of the nodes.

A possible solution is to put the DB data into the JCR.

Regards,
Alex

-- 
Alexander Klimetschek
alexander.klimetschek@day.com

Re: SQL joins from JCR to database

[Thomas Müller <th...@day.com>]

Hi,

Probably the easiest way is to create a 'Jackrabbit table' within H2,
and then join this table as you would join a regular database table.
See http://wiki.apache.org/jackrabbit/QueryUsingJdbc

Regards,
Thomas