Announcement Announcement Module
Collapse
No announcement yet.
how to retrieve relationships with jdbc-based dao Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • how to retrieve relationships with jdbc-based dao

    Suppose I have the following domain objects:
    Code:
    class Owner {
      int id
      String name
    }
    class Vet {
      int id
      String name
    }
    class Cat {
      int id
      String name
      Owner owner
      List vets
    }
    thus Cat has a many-to-one relationship with Owner and a many-to-many relationship with Vet (the inverse relation Owner.cats is not needed and not modeled)

    My business should retrieve a complete cat (with owner and vets) given a cat id.

    Which dao methods should I create ?

    For the many-to-one relationship I see 3 options:
    option 1
    * Cat getCat(int id)
    -> only retrieve primitive properties, no relations
    * void addOwner (Cat cat)
    -> select o.* from owner o, cat c where c.owner = o.id and c.id = ?
    * business method should call getCat and addOwner

    option 2
    * Cat getCat (int id)
    -> retrieve primitive properties and the owner-id
    -> cat.getOwner().setId (resultset.getInt("owner"))
    * Owner getOwner(int id)
    -> select * from owner where id = ?
    * business method should call getCat and getOwner

    option 3
    * like option 2 but getCat(int catId) calls getOwner(int ownerId) itself
    -> pro: prevents incomplete owner objects
    -> con: dependencies between dao-methods (possibly between dao's)

    I prefer option 2:
    * pro: in cases you only need the owner's id, you get away with one query
    * pro: you will probably need the method to retrieve an owner by id anyway
    * con: incomplete Owner object if you forget to call getOwner

    But for many-to-many relationships I think only option 1 is viable:

    * void addVets (Cat cat)
    -> finds all the vets and adds them to the cat
    -> select v.* from vet v, cat_vet cv where cv.vet = v.id and cv.cat = ?

    If I understood correctly, applying option 2 for many-to-many relations is known as the "n+1 problem" since you need a query per vet.

    What are your opinions and/or experiences ?
    Is it common to have dependencies between dao's ?
    Are there any better options (besides using Hibernate) ?

    Maarten

  • #2
    Re: how to retrieve relationships with jdbc-based dao

    Originally posted by Maarten
    Are there any better options (besides using Hibernate) ?

    IBatis maybe. It's a little less heavy-weight than Hibernate and might suit your needs. Have a look at the jPetstore example that comes with the distribution.

    Basically what you're trying to do is implementing you're own really simple O/R mapping tool, which I don't think is a good thing ;-)

    Comment


    • #3
      Basically what you're trying to do is implementing you're own really simple O/R mapping tool, which I don't think is a good thing
      Amen. I'm just reviewing a particularly nasty example of a home-grown ORM framework that has played a major part in a project failure. If you need relationship management use an existing ORM tool: don't reinvent the wheel.

      Comment


      • #4
        I am totally aware of the benefits of an ORM tool and I am convinced that we should migrate (to hibernate probably) sooner rather than later.

        However, for now I am using plain jdbc (with spring's jdbc support) - I don't think I am trying to develop my own (simplistic) ORM tool.

        If you need relationship management use an existing ORM tool
        I guess every domain object model has relationships and I was just wondering how people were retrieving these with jdbc-dao's without too much duplicate code. I wasn't asking how to do transparent relationship management with plain jdbc (since I know that's what an ORM is for).

        Just wonder how people are doing explicit relationship mgmt with jdbc, since I guess everyone using jdbc needs this.

        At first sight, the jPetstore example only contains relationship management for a N-1 relationship (Item-Product) but no 1-N or N-M, right ?

        Maarten

        Comment


        • #5
          Originally posted by Maarten
          However, for now I am using plain jdbc (with spring's jdbc support) - I don't think I am trying to develop my own (simplistic) ORM tool.

          Just wonder how people are doing explicit relationship mgmt with jdbc, since I guess everyone using jdbc needs this.
          Well, I don't want to repeat myself, but errr... iBatis? ;-).

          Well, joking aside, I really think you should have a look at iBatis. It's just what you're looking for, maybe only to understand what a simplistic (not in a negative way) JDBC abstraction layer looks like. If you want to manage relationships in your persistency layer yourself using plain JDBC, I bet you'll end up with a little bit of generic code that will manage the relationships for you. ibAtis does just that, plus a little additional abstraction functionality.

          As for managing relationships with plain and simple JDBC, it just comes down to decent knowledge of SQL I guess. With a database supporting inner-joins and with using the right SQL commands, you can retrieve any relationship you want. Petclinic does programmatic relationship management by the way. Not that this is the way to do it, but it mihgt give you a an idea how Thomas (?) implemented it.

          Comment

          Working...
          X