Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (herse.apache.org: local policy)
From: "Meindert" <meindert@pastelebusiness.com>
To: <user-java@ibatis.apache.org>
Subject: RE: Conditional joins?
Date: Thu, 17 May 2007 16:54:59 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AceYkK8QDEk0t5I4SiySN0kl52RWDgAAeGNA
In-Reply-To: <20070517143613.2E88014D82A@smtp-2.sys.kth.se>
Message-ID: <AS-SMTP04HV5EIKcQEF0000816d@as-smtp04.he.businessgateway.co.za>

I'm doing it by checking the Foreign Key value and the use of <isNotNull>

If I don't want to join I set the Foreign Key value to null
And in the statement
SELECT ....
<isNotNull property="foreignkeyfield">
   {Fields from the joined table}
</isNotNull>  
FROM maintable
<isNotNull property="foreignkeyfield">
   JOIN ...
</isNotNull>  

Kind Regards
 Meindert

-----Original Message-----
From: Matthias Karlsson [mailto:mc@kth.se] 
Sent: 17 May 2007 04:36 PM
To: user-java@ibatis.apache.org
Subject: Conditional joins?

Hi,

I'm currently evaluating frameworks for writing DAO layers for future
projects, replacing a custom one I previously wrote. The current one works
well, but it needs much more work. Long story short, it relies on being able
to retrieve values using table aliases in the ResultSet::getXXX-methods,
which works with MySQL but not (for example) with PostgreSQL.

Anyway, I'm currently looking into Hibernate and iBatis (and possibly
working on our custom...). Hibernate seems advanced enough, but I think its
"black box" is a little too big for me. Furthermore, I like SQL and would
very much prefer to be able to continue to use it, which was what made me
find iBatis to begin with.

Browsing through your documentation it seems to be able to do pretty much
the only thing I want: write SQL and have it mapped to beans in a way I see
fit. Hibernate seems to think that the Java model should dictate the
structure of the database, which I do not agree with. The database is the
most important model you have, and I would much rather have it properly
structured, even if it means the Java model is far from optimal (from a OOP
point-of-view).

Anyway, to get to the point, one of the reasons for me writing a custom
framework to begin with was that we (felt that we) really needed to have as
much control as possible as a user of the framework when it came to joins
and such. As a result, a user of the framework, when querying for an object
A mapped to a table T (our objects are always fully mapped to tables) is
able to specify in the method being called what should be joined in the
query.

For example, let's say there are foreign keys in T to U and V, mapped to
objects B and C respectively. If a user of the framework knows that he will
need B and C along with A, he can say so and they will be joined and
automatically fetched in one query. If not, just A can be queried for and if
B and C would be needed at the later stage anyway, they can be fetched
explicitly (though then of course with additional queries).

I liked the <dynamic>-tag when I read about it, but is there any support for
conditional joins (e.g., if parameter hash h contains value v, join this
table) or would you need to handle the different cases explicitly by
creating different queries (possibly using fragments to cut down on the
copied code)?.

I understand that this is very special case and I'm probably trying to solve
a problem that isn't even worth thinking about. It's just that I come from a
background where every query counts so I was forced to be able to handle all
these cases.

Any feedback would be much appreciated.

Sincerely,
Matthias