That's exactly what happened. You've added to my SQL knowledge - thanks. This gives me something to work with, although a non-null X.MyY with null fields in the Y instance is a little odd. Maybe that's a new feature where the SQL Maps recognizes that case and instead nulls out MyY.
-----Original Message----- From: roberto [mailto:[EMAIL PROTECTED] Sent: Saturday, January 15, 2005 8:52 PM To: ibatis-user-cs@incubator.apache.org Subject: RE: How to do result mapping where a complex object property is null Have you tried an outer join to TY? MyY may end up initialized but with null properties (?). Roberto > -----Original Message----- > From: Kit Cragin [mailto:[EMAIL PROTECTED] > Sent: Saturday, January 15, 2005 9:06 PM > To: ibatis-user-cs@incubator.apache.org > Subject: RE: How to do result mapping where a complex object property is > null > > Clinton>> I don't know if I understand correctly. Do you WANT an object > created > Clinton>> when all of the columns are null? Or not? > > Sorry, Ill try to be a bit clearer (btw, I changed the table names in the > reply and my original message below to avoid some confusion). > > When I execute the Get.X statement which has this SQL: > > select * from TX, TY where TX.X_ID = #value# and TX.Y_ID = TY.Y_ID > > This results in an X instance being created where its X.MyY is a non-null > Y > instance with the proper values. However there are situations in my schema > where a row in table TX might have a NULL for it's Y_ID. In this case, > executing the SQL in Get.X results in zero rows, so I don't get an X > instance at all. > > My first thought was to change the SQL to: > > select * from TX, TY where TX.X_ID = #value# and (TX.Y_ID = TY.Y_ID or > TX.Y_ID is null) > > but that results in more than 1 row, and the columns used to reconstitue > X.MyY fill in X.MyY with information. What I want is for X.MyY == null > when > TX.Y_ID is null, and X.MyY == some new instance of Y with the correct > properties. > > So that's the problem at this point: I get all (X instance with a non-null > MyY) or nothing (no X instance). Either I'm not making the right query > (entirely possible!), there's something in SQL Maps I'm not using, or > perhaps this is a feature that could be added. > > FWIW, I use SQL Maps for both .NET and J2EE projects all the time now. > It's > great! > > - Kit > > > <[EMAIL PROTECTED]> wrote: > > I am trying to use a join to avoid an extra select when I retrieve an > object > > that has a property which is another object (not a primitive or native > > type). > > > > The relevant fragments of my XML file are: > > > > <resultMap id="YMap" class="Y"> > > <result property="ID" column="Y_ID"/> > > <result property="Name" column="Y_Name"/> > > <result property="Description" column="Y_Desc"/> > > > > </resultMap> > > > > <resultMap id="XMap" class="X"> > > <result property="ID" column="X_ID"/> > > <result property="Name" column="X_Name"/> > > <result property="Description" column="X_Desc"/> > > > > <result property="MyY" resultMapping="MyStatements.YMap"/> > > </resultMap> > > > > ... > > > > <select id="X.Get" resultMap="XMap"> > > select * from TX, TY where TX.X_ID = #value# and TX.Y_ID = > TY.Y_ID > > </select> > > > > This works fine as long as TX.Y_ID is non null. However, the database > schema > > allows TX.Y_ID to be null. From an application perspective, I'd like to > > reconstitute an instance of class X where it's MyY property (of type Y) > will > > be null when TX.Y_ID is null. > > > > ... > >
