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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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.
