Announcement Announcement Module
Collapse
No announcement yet.
JDBC Template Design question Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • JDBC Template Design question

    Hi all,

    I have the following situation:

    2 tables
    Costumer and Contract

    Contract need to have 2 costumers references, like this:

    Contract
    id
    description
    costumer_seller_id
    costumer_buyer_id


    I'm using JDBC template to load data from database.
    If I need to load a Contract, I have Contract object that have getBuyer() and getSeller() methods.

    To load buyer/seller I will have to do 2 queries to load my Contract object?
    Is that correct?
    Is there other way to do it?

    Important: I can't use JPA on my project, that's why I'm using JDBCTemplate.

    Regards,

  • #2
    You can create a query which returns everything at once, you don't need 3 queries (contract + 2 customer queries). Simply construct a query with joins, make sure you are able to determine which columns belong to which (buyer/seller) and you are good to go.

    I suggest a JDBC tutorial.

    Comment


    • #3
      Hi Marten,

      But, if I built my query with join, the resultset will return 2 rows, correct? I wanted to know if theres any way to return just 1 row with all the information or I will need to handle this manually on my code, I mean, extract the information from the query result and populate the Contract object with seller and buyer.

      Like this:

      Code:
      for (<result set list>) {
         contract.setSeller(seller);
      }
      Is that correct?
      Cause if I use JPA, with one call, I can get all the object with its children.

      Thanks in advance,

      Daniel

      Comment


      • #4
        It depends on how you write your query. If you are a bit creative with sql you can perfectly return 1 row which has the data for the contract and both the supplier and seller.

        Comment


        • #5
          You didn't give many details, but perhaps an example would do:

          Code:
          select * 
          from customer cust, contract buyer, contract seller
          where cust.id = :id
          and cust.costumer_seller_id = seller.id
          and cust.costumer_buyer_id = buyer.id
          This should return a single row with the data you need - assuming you are queering by id that is...

          Comment


          • #6
            Thanks eran_ha.

            That's what I was wondering...

            Regards!

            Comment

            Working...
            X