Hi all.

I'm involved in a project that aim to gather, store and mine archeology data. In addition, the mining needs to be visualized online and since I know java best, I've talked my project leader into using java (with JSP and JSF) for server side operations. For database access the Hibernate project seems like the choice for us too, since regular persisitence layer (JPA) is a bit too restricted. The database server we run is postresql 8.3.

Now to the problem:
The database consisits of some > 60 tables, each of which can potentially be used for data restriction criteria by a user. Further, the target values to gather for analysis are not fixed either. So, in effect, any user can search for any type of data stored using any criteria existing in the database (even if the criteria makes no sense to us).

This means that I need access to the search path between any tables in the database. Which , in turn, prompted me to implement a graph and a search algorithm for that. This search method give me a path and the association between tables (properties and their association type). So far so good.

Now I'm uncertain on how to continue; which is my best option using hibernate to create the queries, filtering the data appropriately.

Four choices are obvious to me (there may be other that I should know about):
1. generate SQL based on the gathered search path and the user supplied criteria.

2. generate HQL.

3. Use the Criteria API.

4. Gather data recursively through repeated queries, one for each step in the search path, applying criteria as neeed.

The first 3 options are, in my opinion, the best for the problem. But I have tried to get an effective generation of queries and have not succeeded to 100%.

The biggest problem have been the Criteria API which supply the tools I need, but I've been unsuccessful in traveling down a search path.

Building SQL and HQL carries has generated successful result for some queries, but as they become more complex the risk of getting Cartesian products from joins increase.

Thus I ask the forum and see if anyone else has experience writing something like this and what your input may be.

Greatful for any answers.

Erik

I am guessing this comes bit too late, but I am having similar proble as you Erik, except that I am stil one big step behind, need to figure out the algorithm to trace paths in database. If by some case you see this post, could you please provide me some help on that, it would help me a lot.

Thanx

Yeah, a bit late.

Still use a graph toolkit to build a model of the database based on their foreign key relationships. I ended up using Dijkstra algorithm for the database graph traversal implemented in JGraph. I also had to remove some tables from the graph since the introduced (for my business model) unwanted paths.

To get the relationships you can use SQL but the storage of the relationships are database dependent.

/Erik