JDBC Template Design question

[dansouza]

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,

[Marten Deinum]

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.

[dansouza]

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:

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

[Marten Deinum]

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.

[eran_ha]

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

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...

[dansouza]

Thanks eran_ha.

That's what I was wondering...

Regards!