I have a requirement to fetch data from database .
Persion: ID, Col1,Col2,Col3
Address: ID, ID2 ,Col5,Col6
Contact: ID, ID3,Col8,Col9
ID is the primart key for Persion
ID2 is the primart key for Address
ID3 is the primart key for Contact
The relation of the tables are as follows
Persion-Address: (one to many)
Persion-Contact(one to many)
Requirement is I will be given values for Col9,Col3,Col5 and I have to fetch the persion Object
I am thinking for following design
select ID from Persion
for each ID
seclect * from Address and populate address Object
seclect * from Contact and populate contact Object
end of loop
construct the persion Object and return
Is there any other beter way to do this because in my database there are more tables and if I go by for loop it will decrease the performance ,So looking for a beter way of design .
you may consider using cursor expressions.
This is Oracle feature that enable to embed results of nested subquery in the resultset of another (main) query.
With cursor expressions your query might look like this:
On client side (JDBC) you can retrieve cursor expression as a resultset - and process this resultset like any other resultset
The advantage is that only one query is submitted to the server instead of two or more unconnected queries that retrieves separately data from individual tables.
This query scans all necessary tables only once and it can be optimized more effectively by DB than two/more separate SQL.
There is however a disadvantage of the above solution - if the main table contains a lot of rows, there is a huge number of cursors created/opened/closed
and this could sometimes lead to problems.
This can be avoided if we create our query in this way:
Here we created only 4 cursors for the whole query - 3 within the query and one for JDBC prepared statement..
WITH clause first runs query on Persons table, save results in memory and reuse these results in subsequent queries.
But in this case contacts and addresses are not embed in individual person data, and all three tables are retrieved as 'flat' tables
so we must populate data on client side
- first load persons to a map (ID is key) , then read addresses and assign them to persons looking at ID value,
and the same must be done with data from contacts.