Re: Avoiding N+1 Selects
iBatis came back with an error message indicating too many objects were being returned for executeQueryForObject. Brad On 2/20/07, Brad Handy [EMAIL PROTECTED] wrote: Wouldn't I have to give unique column names for those identifying the child data? For example table parentChild some_foreign_key int id int description varchar(64) parent_id int rows id descriptionparent_id 0 grandparent null 1 parent 1 0 2 child 1 1 3 child 2 1 4 parent 2 0 5 child 3 4 6 parent 3 0 7 child 4 6 8 child 5 6 select p.id as parentId , p.description as parentDesc , p.parent_id as parentAncestorId , c.id as childId , c.description as childDesc , c.parent_id as childAncestorId from parentChild p , parentChild c where p.some_foreign_key = c.some_foreign_key and p.id = c.parent_id and p.id c.id order by p.id , c.id should return parentId parentDesc parentAncestorId childId childDescchildAncestorId 0 grandparent null 1 parent 10 0 grandparent null 4 parent 20 0 grandparent null 6 parent 30 1 parent 10 2 child 1 1 1 parent 10 3 child 2 1 4 parent 20 5 child 3 4 6 parent 30 7 child 4 6 6 parent 30 8 child 5 6 On 2/20/07, Clinton Begin [EMAIL PROTECTED] wrote: i.e. is it a join with a bridge table or not (1:M or M:N)? Regardless, I wonder if a recursive result map would workIt might. resultMap id=Node ... result name=children ... resultMap=Node/ ... I don't see any reason why that would cause any problems...perhaps try it and let us know. Cheers, Clinton On 2/19/07, Clinton Begin [EMAIL PROTECTED] wrote: Oh...is it a self join? Clinton On 2/19/07, Brad Handy [EMAIL PROTECTED] wrote: I guess I'll have to take a different approach. The depth isn't set to be a defined level; so if I go more than two levels below the grandparent, it won't work. On 2/19/07, Clinton Begin [EMAIL PROTECTED] wrote: You'll need one query with 3 result maps. The result maps will be chained together with collection properties using the resultMap attribute. resultMap id=Child ... resultMap id=Parent result ... resultMap=Child/ ... resultMap id=GrandParent result ... resultMap=Parent/ ... select ... resultMap=GrandParent ... The select statement should join the tables together and you may need to be very explicit with the column names. Clinton On 2/19/07, Brad Handy [EMAIL PROTECTED] wrote: I have a table which has all of the parent/child relationships in the same table. I would like to avoid the N+1 selects with this construct, but it's unclear from the documentation if this can be done. Let's say I have the following relationships defined in the table: Grand Parent Parent 1 Child 1 Child 2 Parent 2 Parent 3 Child 3 Child 4 When creating the child objects for Grand Parent, will the same Parent* objects be used to add the children Child* objects to the appropriate parents? Brad
Re: [JDBC type = ARRAY / Java type = ?] conditionally add a table to the from clause just once if either or both of two parameters is not empty
less ugly and more obvious to read - thanks Koka Kiknadze wrote: isNotEmpty property=supplierCompanyId , suppliers_for_parts sp /isNotEmpty isEmpty property=supplierCompanyId isNotEmpty property=supplierRef , suppliers_for_parts sp /isNotEmpty /isEmpty another ugly way I guess. -- View this message in context: http://www.nabble.com/conditionally-add-a-table-to-the-from-clause-just-once-if-either-or-both-of-two-parameters-is-not-empty-tf3272865.html#a9102673 Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: [JDBC type = ARRAY / Java type = ?] Deadlock found when trying to get lock (MySQL 5.0 with innodb)
No worries, and in all liklihood it isn't an ibatis specific bug, I had just held out hope that someone else might have encountered the issue and arrived at a solution. I've tried both simple and dbcp data sources witht the same results and I'm currently using mysql with innodb tables and row level locking...Just not sure what I might be able to set on the datasource that could have some impact on the situation. Thanks again for your insight, appreciate it. Koka Kiknadze wrote: I bet noone believes it's an iBatis problem. iBatis is database agnostic, and if there were some general locking issues with inserts/updates they'll affect every user on this list. I've not used MSQL for years, any chance its not using row level locking in your configuration? Have you tryed SIMPLE datasource instead of DBCP, or different JDBC driver? These are things I'd try if I were sure that locks are not because of bugs in my application, and if looking into ibatis logs revealed nothing... Not quite helpful, sry -- View this message in context: http://www.nabble.com/Deadlock-found-when-trying-to-get-lock-%28MySQL-5.0-with-innodb%29-tf2792164.html#a9103064 Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
RE: conditionally add a table to the from clause just once if either or both of two parameters is not empty
Perhaps moving that logic out of your xml file into Java land.
Assuming you have a Map as the parameter.
boolean useSupplierForParts =
Boolean.valueOf(parameters.containsKey(supplierCompanyId) ||
parameters.containsKey(supplierRef));
Parameters.put(supplierTable, useSupplierForParts ? ,
suppliers_for_parts sp : );
Or, if you have a parameter class:
class MyParameter {
// all sorts of goodies in here...
public String supplierTable() {
return (supplierCompanyId != null || supplierRef != null) ? ,
suppliers_for_parts sp : ;
}
}
In your map file:
SELECT whatever FROM whatever w, other_table ot, $supplierTable$
-Original Message-
From: Tim Azzopardi [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 22, 2007 5:58 AM
To: user-java@ibatis.apache.org
Subject: conditionally add a table to the from clause just once if
either or both of two parameters is not empty
ibatis2.2 java5 and 6 on winXpSp2
I'm using the code below to conditionally add a table to the from clause
of my sql if one or other or both of two parameters is not empty. (The
tricky bit is not to include add the table twice).
The following works, but I wondered if there was a simpler way that
avoids my ugly /*dummy*/ cludge.
(Without the /*dummy*/ nothing is ever generated.)
dynamic prepend=, suppliers_for_parts sp
isNotEmpty property=supplierCompanyId
/*dummy*/
/isNotEmpty
isNotEmpty property=supplierRef
/*dummy*/
/isNotEmpty
/dynamic
--
View this message in context:
http://www.nabble.com/conditionally-add-a-table-to-the-from-clause-just-
once-if-either-or-both-of-two-parameters-is-not-empty-tf3272865.html#a91
00250
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
parameters in select statements and performance
Just checking my understanding ... Is it the case that select statements that use # placeholders perform better than those using $ placeholders when executed multiple times? Is this because the compiled query is saved by the database so it can be reused later with different parameter values when # placeholders are used, but not when $ placeholders are used?
number of Initial connections
We have a problem where we exceed our maximum connections on our
database. Our setup is here. Does MaximumIdleConnections mean that the
database right away grabs 25 connections? Or is that the maximum idle
connections before it starts to clean up?
I see in the iBatis-SqlMaps-2.pdf it says Pool.MaximumIdleConnections -
The number of idle connections that will be stored in the pool. Is
that initially? Is there Pool.IntialSize?
transactionManager type=JDBC
dataSource type=SIMPLE
property name=JDBC.Driver value=${driver}/
property name=JDBC.ConnectionURL
value=${url}/
property name=JDBC.Username
value=${username}/
property name=JDBC.Password
value=${password}/
property name=Pool.MaximumActiveConnections
value=50/
property name=Pool.MaximumIdleConnections
value=25/
property name=Pool.MaximumWait value=600/
property name=Pool.LogAbandoned
value=true/
property name=Pool.RemoveAbandoned
value=true/
property name=Pool.RemoveAbandonedTimeout
value=5/
property name=Pool.PingQuery value=select *
from sysibm.SYSDUMMY1/
property name=Pool.PingEnabled value=true/
property name=Pool.PingConnectionsOlderThan
value=60/
property name=Pool.PingConnectionsNotUsedFor
value=60/
/dataSource
/transactionManager
Thanks,
Tom
Re: parameters in select statements and performance
Yes, almost all databases will cache the execution plans for queries with ? in them. Some will also try to do the same with values in them, too, but that is not as common. Larry On 2/22/07, Mark Volkmann [EMAIL PROTECTED] wrote: Just checking my understanding ... Is it the case that select statements that use # placeholders perform better than those using $ placeholders when executed multiple times? Is this because the compiled query is saved by the database so it can be reused later with different parameter values when # placeholders are used, but not when $ placeholders are used?
RE: Just Another Stored Procedure Question
Hi all, Sorry for the misunderstanding, but it was just a problem of copy and paste.. (because before i tried using parameters passed as raw HashMap).. actually i had this : procedure id=elions.bas.procCounter parameterMap=elions.bas.procCounter.param but still the same problem.. oh and to Mr. Larry, I think the . characters in the name is no problem, because i've been coding ibatis sqlmaps like that for quite some time (i know it's better using namespaces.. but we're a little occupied now to fix it) thank you... and please CMIIW.. yusuf -Original Message- From: Larry Meadors [mailto:[EMAIL PROTECTED] Sent: Thursday, February 22, 2007 12:37 PM To: user-java@ibatis.apache.org Subject: Re: Just Another Stored Procedure Question You misread Brad's comment, i think. You have this: procedure id=elions.bas.procCounter parameterClass=hashMap You need to have this: procedure id=elions.bas.procCounter parameterMap=elions.bas.procCounter.param ..which may still not work because of the . characters in the name. Larry
