You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Thibaut Fagart <th...@hsbc.fr> on 2007/02/14 16:46:52 UTC
Re: [JDBC type = ARRAY / Java type = ?] howto : Degenerated case of
heterogenous lists.
Thanks for the tip but I'm not happy with this solutions, because in my case
the 3 addresses have different meanings (just as you would have a main
address and secondary ones).
I'm trying to create a use case for what I'm trying to do, will post it
later today or tomorrow
Brandon Goodin wrote:
>
> It could be solved if you combined address1,2,3 into a list and used the
> groupby functionality.
>
> Customer {
> firstName : string
> lastName : string
> addresses : List
> }
>
> If you want to return the address through address1, 2, 3 getters you could
> always add the getters and have them draw from the list indexes.
>
> getAddress1() {
> if (addresses.getSize() > 0) return addresses.get(0) : return null;
> }
>
> getAddress2() {
> if (addresses.getSize() > 1) return addresses.get(1) : return null;
> }
>
> getAddress3() {
> if (addresses.getSize() > 2) return addresses.get(2) : return null;
> }
>
>
>
> Brandon
>
> On 2/14/07, Thibaut Fagart <th...@hsbc.fr> wrote:
>>
>>
>> Hi,
>> I'm currently evaluating IBatis for the corporation I work with, and
>> trying
>> to use it to solve a case we have.
>>
>> We have a model like this
>>
>> Customer {
>> firstName : string
>> lastName : string
>> address1 : Address
>> address2 : Address
>> address3 : Address
>> }
>> where Address is a simple class.
>>
>> All the addresses happen to be stored in the same table, with a join to
>> the
>> customer table, and a flag indicating which address this is (address1,
>> address2 or address3).
>>
>> The request that used to be used to solve this case is something like
>> that
>>
>> select [customer columns], [address columns], address_flag from
>> customer,
>> address
>> where customer.id = address.id_customer
>>
>> This would return 3 rows, with the curstomer information duplicated, and
>> the
>> resultSet consuming code would sort out which address attributes
>> (address1,
>> address2 or address3) has to be set with the current row depending on
>> address_flag value.
>>
>> I've seen support for returning heterogenous lists using the
>> discriminator
>> tag, but this doesn't solve my problem, does it ?
>>
>> Would there be a way to solve this case with only 1 request to the
>> database
>> ?
>> --
>> View this message in context:
>> http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8966314
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>
>
--
View this message in context: http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8967489
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: [JDBC type = ARRAY / Java type = ?] howto : Degenerated case of heterogenous lists.
Posted by Brandon Goodin <br...@gmail.com>.
Oooooooh,
I see what you are saying now. Jeff is dead nuts on. flatten out the results
and map to your different address objects.
Brandon
On 2/14/07, Thibaut Fagart <th...@hsbc.fr> wrote:
>
>
> Here is the sqlMap I'm trying
>
> THe idea was to try to create "virtual" classes Address1, Address2 and
> Address3, and having IBatis only set the relevant atttriute for each row,
> but it does not seem to work.
>
>
>
>
> > <?xml version="1.0" encoding="UTF-8" ?>
> > <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> > "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> > <sqlMap namespace="TestIBatis">
> > <resultMap id="Customer" class="test.ibatis.Customer"
> groupBy="id">
> > <result property="id" column="id"/>
> > <result property="address1" resultMap="TestIBatis.Address1
> "/>
> > <result property="address2" resultMap="TestIBatis.Address2
> "/>
> > <result property="address3" resultMap="TestIBatis.Address3
> "/>
> > </resultMap>
> > <resultMap id="Address" class="test.ibatis.Address">
> > <result property="flag" column="flag"/>
> > <discriminator column="flag" javaType="string">
> > <subMap value="Address1" resultMap="
> TestIBatis.Address1"/>
> > <subMap value="Address2" resultMap="
> TestIBatis.Address2"/>
> > <subMap value="Address3" resultMap="
> TestIBatis.Address3"/>
> > </discriminator>
> > </resultMap>
> >
> > <resultMap id="Address1" class="test.ibatis.Address"
> extends="Address">
> > <result property="rue" column="rue"/>
> > </resultMap>
> > <resultMap id="Address2" class="test.ibatis.Address"
> extends="Address">
> > <result property="rue" column="rue"/>
> > </resultMap>
> > <resultMap id="Address3" class="test.ibatis.Address"
> extends="Address">
> > <result property="rue" column="rue"/>
> > </resultMap>
> >
> > <select id="getCustomerAndAddresses"
> > parameterClass="java.lang.Integer"
> > resultMap="Customer">
> > SELECT c.id, a.rue, a.flag
> > FROM customer c LEFT OUTER JOIN address a ON c.id =
> a.customer_id
> > WHERE c.id = #value#
> > </select>
> >
> > </sqlMap>
> >
>
> I have added some logging in the customer setAddressX methods and here is
> the output I get for the following tables
> customer (id)
> 1
>
> address (customer_id, tex, flag)
> (1,none1,Address1)
> (1,none2,Address2)
> (1,none3,Address3)
>
> And the logs
>
>
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,669;setAddress1 null
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,669;setAddress2 null
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,679;setAddress3 null
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,689;setAddress1
> none1
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,699;setAddress2
> none1
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,699;setAddress3
> none1
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,709;setAddress1
> none2
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,709;setAddress2
> none2
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,709;setAddress3
> none2
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,719;setAddress1
> none3
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,719;setAddress2
> none3
> > test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,719;setAddress3
> none3
> >
> >
>
>
> According to the log, it seems all the setters are called for each row ,
> although only one discriminator should match?
> --
> View this message in context:
> http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8967984
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: [JDBC type = ARRAY / Java type = ?] howto : Degenerated case of
heterogenous lists.
Posted by Thibaut Fagart <th...@hsbc.fr>.
Here is the sqlMap I'm trying
THe idea was to try to create "virtual" classes Address1, Address2 and
Address3, and having IBatis only set the relevant atttriute for each row,
but it does not seem to work.
> <?xml version="1.0" encoding="UTF-8" ?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap namespace="TestIBatis">
> <resultMap id="Customer" class="test.ibatis.Customer" groupBy="id">
> <result property="id" column="id"/>
> <result property="address1" resultMap="TestIBatis.Address1"/>
> <result property="address2" resultMap="TestIBatis.Address2"/>
> <result property="address3" resultMap="TestIBatis.Address3"/>
> </resultMap>
> <resultMap id="Address" class="test.ibatis.Address">
> <result property="flag" column="flag"/>
> <discriminator column="flag" javaType="string">
> <subMap value="Address1" resultMap="TestIBatis.Address1"/>
> <subMap value="Address2" resultMap="TestIBatis.Address2"/>
> <subMap value="Address3" resultMap="TestIBatis.Address3"/>
> </discriminator>
> </resultMap>
>
> <resultMap id="Address1" class="test.ibatis.Address" extends="Address">
> <result property="rue" column="rue"/>
> </resultMap>
> <resultMap id="Address2" class="test.ibatis.Address" extends="Address">
> <result property="rue" column="rue"/>
> </resultMap>
> <resultMap id="Address3" class="test.ibatis.Address" extends="Address">
> <result property="rue" column="rue"/>
> </resultMap>
>
> <select id="getCustomerAndAddresses"
> parameterClass="java.lang.Integer"
> resultMap="Customer">
> SELECT c.id, a.rue, a.flag
> FROM customer c LEFT OUTER JOIN address a ON c.id = a.customer_id
> WHERE c.id = #value#
> </select>
>
> </sqlMap>
>
I have added some logging in the customer setAddressX methods and here is
the output I get for the following tables
customer (id)
1
address (customer_id, tex, flag)
(1,none1,Address1)
(1,none2,Address2)
(1,none3,Address3)
And the logs
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,669;setAddress1 null
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,669;setAddress2 null
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,679;setAddress3 null
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,689;setAddress1 none1
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,699;setAddress2 none1
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,699;setAddress3 none1
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,709;setAddress1 none2
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,709;setAddress2 none2
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,709;setAddress3 none2
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,719;setAddress1 none3
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,719;setAddress2 none3
> test.ibatis.Customer;DEBUG;main;2007-02-14 17:01:53,719;setAddress3 none3
>
>
According to the log, it seems all the setters are called for each row ,
although only one discriminator should match?
--
View this message in context: http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8967984
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: [JDBC type = ARRAY / Java type = ?] howto : Degenerated case of heterogenous lists.
Posted by Jeff Butler <je...@gmail.com>.
I agree with Brandon - a list of addresses would be better. You've got a
properly normalized DB and a de-normalized object model. Usually it's the
other way around!
However, you can accompish what you want in a single SQL statement.
Assuming tables like this:
create table customer (
customerId int not null,
firstName varchar(30),
lastName varchar(30),
primary key(customerId)
)
create table address (
customerId int not null,
addressType int not null,
city varchar(30),
state char(2),
zip char(5),
primary key(customerId, addressType)
)
You could write an SQL statement like this:
select c.customerId, c.firstName, c.lastName,
a1.city as a1_city, a1.state as a1_state, a1.zip as a1_zip,
a2.city as a2_city, a2.state as a2_state, a2.zip as a2_zip,
a3.city as a3_city, a3.state as a3_state, a3.zip as a3_zip
from customer c
left join address a1
on c.customerId = a1.customerId and a1.addressType = 1
left join address a2
on c.customerId = a2.customerId and a2.addressType = 2
left join address a3
on c.customerId = a3.customerId and a3.addressType = 3
Jeff Butler
On 2/14/07, Thibaut Fagart <th...@hsbc.fr> wrote:
>
>
> Thanks for the tip but I'm not happy with this solutions, because in my
> case
> the 3 addresses have different meanings (just as you would have a main
> address and secondary ones).
>
> I'm trying to create a use case for what I'm trying to do, will post it
> later today or tomorrow
>
>
> Brandon Goodin wrote:
> >
> > It could be solved if you combined address1,2,3 into a list and used the
> > groupby functionality.
> >
> > Customer {
> > firstName : string
> > lastName : string
> > addresses : List
> > }
> >
> > If you want to return the address through address1, 2, 3 getters you
> could
> > always add the getters and have them draw from the list indexes.
> >
> > getAddress1() {
> > if (addresses.getSize() > 0) return addresses.get(0) : return null;
> > }
> >
> > getAddress2() {
> > if (addresses.getSize() > 1) return addresses.get(1) : return null;
> > }
> >
> > getAddress3() {
> > if (addresses.getSize() > 2) return addresses.get(2) : return null;
> > }
> >
> >
> >
> > Brandon
> >
> > On 2/14/07, Thibaut Fagart <th...@hsbc.fr> wrote:
> >>
> >>
> >> Hi,
> >> I'm currently evaluating IBatis for the corporation I work with, and
> >> trying
> >> to use it to solve a case we have.
> >>
> >> We have a model like this
> >>
> >> Customer {
> >> firstName : string
> >> lastName : string
> >> address1 : Address
> >> address2 : Address
> >> address3 : Address
> >> }
> >> where Address is a simple class.
> >>
> >> All the addresses happen to be stored in the same table, with a join to
> >> the
> >> customer table, and a flag indicating which address this is (address1,
> >> address2 or address3).
> >>
> >> The request that used to be used to solve this case is something like
> >> that
> >>
> >> select [customer columns], [address columns], address_flag from
> >> customer,
> >> address
> >> where customer.id = address.id_customer
> >>
> >> This would return 3 rows, with the curstomer information duplicated,
> and
> >> the
> >> resultSet consuming code would sort out which address attributes
> >> (address1,
> >> address2 or address3) has to be set with the current row depending on
> >> address_flag value.
> >>
> >> I've seen support for returning heterogenous lists using the
> >> discriminator
> >> tag, but this doesn't solve my problem, does it ?
> >>
> >> Would there be a way to solve this case with only 1 request to the
> >> database
> >> ?
> >> --
> >> View this message in context:
> >>
> http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8966314
> >> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8967489
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: [JDBC type = ARRAY / Java type = ?] howto : Degenerated case of
heterogenous lists.
Posted by Thibaut Fagart <th...@hsbc.fr>.
Thanks for the feedback
real model is something like
Customer {
properAssets : Assets // assets privately owned by the customer
joinedAssets : Assets // assets collectively owned by the customer and
his/her husband/spouse
// and another one which I don't know the real meaning :)
}
The fact is, both the Object Modeling and the databse structure exist, and
I'm the technical guy who's trying to see if Ibatis would be able to cope
with how our developers have been working :) As far as possible I would
prefer not putting constraints on either Object Model or Database because of
the data access technology
--
View this message in context: http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8968114
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: [JDBC type = ARRAY / Java type = ?] howto : Degenerated case of heterogenous lists.
Posted by Brandon Goodin <br...@gmail.com>.
How is this solution any different? Indexes versus 1,2,3? It would be the
same thing. There must be more that you are not describing. But, if it
doesn't satisfy your requirement...okay.
Brandon
On 2/14/07, Thibaut Fagart <th...@hsbc.fr> wrote:
>
>
> Thanks for the tip but I'm not happy with this solutions, because in my
> case
> the 3 addresses have different meanings (just as you would have a main
> address and secondary ones).
>
> I'm trying to create a use case for what I'm trying to do, will post it
> later today or tomorrow
>
>
> Brandon Goodin wrote:
> >
> > It could be solved if you combined address1,2,3 into a list and used the
> > groupby functionality.
> >
> > Customer {
> > firstName : string
> > lastName : string
> > addresses : List
> > }
> >
> > If you want to return the address through address1, 2, 3 getters you
> could
> > always add the getters and have them draw from the list indexes.
> >
> > getAddress1() {
> > if (addresses.getSize() > 0) return addresses.get(0) : return null;
> > }
> >
> > getAddress2() {
> > if (addresses.getSize() > 1) return addresses.get(1) : return null;
> > }
> >
> > getAddress3() {
> > if (addresses.getSize() > 2) return addresses.get(2) : return null;
> > }
> >
> >
> >
> > Brandon
> >
> > On 2/14/07, Thibaut Fagart <th...@hsbc.fr> wrote:
> >>
> >>
> >> Hi,
> >> I'm currently evaluating IBatis for the corporation I work with, and
> >> trying
> >> to use it to solve a case we have.
> >>
> >> We have a model like this
> >>
> >> Customer {
> >> firstName : string
> >> lastName : string
> >> address1 : Address
> >> address2 : Address
> >> address3 : Address
> >> }
> >> where Address is a simple class.
> >>
> >> All the addresses happen to be stored in the same table, with a join to
> >> the
> >> customer table, and a flag indicating which address this is (address1,
> >> address2 or address3).
> >>
> >> The request that used to be used to solve this case is something like
> >> that
> >>
> >> select [customer columns], [address columns], address_flag from
> >> customer,
> >> address
> >> where customer.id = address.id_customer
> >>
> >> This would return 3 rows, with the curstomer information duplicated,
> and
> >> the
> >> resultSet consuming code would sort out which address attributes
> >> (address1,
> >> address2 or address3) has to be set with the current row depending on
> >> address_flag value.
> >>
> >> I've seen support for returning heterogenous lists using the
> >> discriminator
> >> tag, but this doesn't solve my problem, does it ?
> >>
> >> Would there be a way to solve this case with only 1 request to the
> >> database
> >> ?
> >> --
> >> View this message in context:
> >>
> http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8966314
> >> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >>
> >>
> >
> >
>
> --
> View this message in context:
> http://www.nabble.com/howto-%3A-Degenerated-case-of-heterogenous-lists.-tf3227674.html#a8967489
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>