sub query extraction

**mkarthik90** · 02-08-2012, 08:59 PM

Do we have any inbuilt code in java for extracting sub query from the nested queries?
Can anyone suggest me any open source projects on this or get me the code for extracting the query from nested query
For example if u have the input ass nested query the inner query needs to be obtained and then the outer query.. once i execute the inner query i need to get a result set and the outer query needs to use this result set for further processing.

**Tolls** · 02-09-2012, 09:44 AM

Um....why?
Are you writing a database by any chance?
If not then...why?

**mkarthik90** · 02-09-2012, 12:44 PM

nope.
actually i am implementing an IEEE paper and i have a greesy heuristics algorithm which does this process of sub query extraction. SO i wanted to know is there any method to perform. Because of that the performance of the system gets improved.

**Tolls** · 02-09-2012, 12:58 PM

When most people see "query" in a Java context it usually means SQL.
You'll probably have to provide more information of what you're dealing with so someone here might be able to pick it up.

**mkarthik90** · 02-09-2012, 02:21 PM

actually in my program , i will have a prepared statement containing the query
select temp from weather where date in (select date from weather where place='chennai' AND latitude='20' AND longitude='30')

What is wanted to do is this split this nested query into sub query as below

select date from weather where place='chennai' AND latitude='20' AND longtitude='30'
select temp from weather where date in ( solution of above query will be passed over here)

Can anyone suggest me how to code this. ?

**Tolls** · 02-09-2012, 02:24 PM

I still don't see why bother, as it gives you no info about how the underlying database is working.
There's unlikely to be an API for it, so you'd have to manually break it up.
ALso, since it's a PreparedStatement, the query will be:

Java Code:

select temp from weather where date in (
select date from weather where place=? AND latitude=? AND longtitude=?)

And getting at the bind variables might be entertaining.

**mkarthik90** · 02-09-2012, 05:52 PM

yeah thats correct . The data for the place, latitude, longtitude will be entered by the user in a jsp. These values will be passed to a servlet and it will be updated in the prepared statement.
So can you explain me how can i proceed now.?
And you mentioned me about the API . Can you tell me about the available API ?

**Tolls** · 02-09-2012, 06:06 PM

I said there isn't likely to be one.
You'll have to figure out how to break up the query string yourself.

In fact, since it's in your code why not simply store the parts of the query somewhere and look at those?
Or is the user entering the full SQL?

**mkarthik90** · 02-09-2012, 06:12 PM

Nope i am forming the SQL Query. In my project i need to show the first SQL Multiple Query and then i need to apply the algorithm to form the sub Query . And the3rd module deals with executing the sub query on the data aggregators.
And i have an idea of
1.converting the prepared Statement to a String using the toString method.
2. Now splitting the query by using String split method at the select .
3. And then executing the query
Will these steps work out?

**Tolls** · 02-10-2012, 09:48 AM

I realise this is a project, but I'm still struggling to see the goal (and "splitting the query" is not a goal, IMO).

Anyway, yes, you could do that. As to whether they "work" that is all dependent on the actual goal of this thing.

**mkarthik90** · 02-10-2012, 02:59 PM

The main aim is to process the continuous aggregation queries efficiently.
If possible you can have a look at the IEEE paper with the title "Query planning for continous aggregation of queries over a network of Data aggregators."

**Tolls** · 02-10-2012, 03:11 PM

Ah.
So it's querying scattered data, so you break down the request into the sub clauses, each of which can hit a different data source (aggregator in the case of the paper).

Now I have that in my head...a SQL parser might help?
ZQL is a possibility, if maybe a bit unsupported.
There will be parsers in most JDBC drivers, though they may simply do a quick check of the query rather than breaking it down into components like ZQL. The one in Apache Derby might be worth a look.

**mkarthik90** · 02-10-2012, 04:24 PM

Can apache derby breakdown my query .
but i dont have any idea about that . Can you suggest me some examples?

**mkarthik90** · 02-10-2012, 04:26 PM

Is derby a complete database like MySql?

**Tolls** · 02-10-2012, 04:36 PM

Yes, Derby is a complete databse, written in Java and it's open source.
Somewhere in there will be a SQL parser.
It's possible that might break down a query into it's constituent clauses in a way you could use, but it might be too tied into how Derby views things for you to be able to hack it.
(I actually meant "most databases" above, and not "most drivers", which wouldn't make too much sense).

Also, thinking about it, Hibernate has its own SQL, so there is likely to be a parser in there as well. Again, though, it is likely to be very particular to how Hibernate views the world.

Albeit a bit old I'd still give ZQL a look, just in case.

**mkarthik90** · 02-10-2012, 06:04 PM

I am totally confused now. Can you tell me the best one for parsing the query.
How about the way i told u by placing PreparedStatement in a String.

**Tolls** · 02-13-2012, 11:21 AM

The problem there is that I don't know what restrictions you have on your queries.
And, as I say, the bind variables are not so easy to get at.

**mkarthik90** · 02-13-2012, 02:34 PM

Hey i used a zql parser to split according to my requirements. <br/>
If anyone wants to do any operation on sql queries plz suggest them with GNU projects like ZQL.<br/>
parser can help you in some way but a few coding needs to be done by us to get the exact expected result.<br/>
Anyways Thanks for helping me out to get to a solution.