ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: [JDBC type = ARRAY / Java type = ?] howto : Degenerated case of heterogenous lists.
Date Wed, 14 Feb 2007 16:09:00 GMT
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 <thibaut.fagart@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 <thibaut.fagart@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.
>
>

Mime
View raw message