Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I need to build complex sql queries programatically, based on large filter conditions.

For example, below are few sample/hypothitical filter conditions, based on which i need to fetch users

Country: india

States: Andhra Pradesh(AP), Gujarat(GUJ), karnataka(KTK)

Districts: All districts in AP except 3 district, 5 any districts from GUJ, all district from KTK except 1 district

Cities: All cities in AP, all cities except few, include only 50 specific cities from KTK

Villages: similar conditions like above with varies combinations...

Currently, we have a query builder, which is very complex in nature, and not easy to modify/re-factory for improvements. So, thinking of complete re-design of it.

Any suggesations on how to build this kind of complex query builders programmatically using some best practices/deisgn patterns?

share|improve this question
6  
So... basically you're asking for an end-to-end solution from us? Or is there a specific question here that I'm missing? –  Amos M. Carpenter Apr 14 '12 at 5:06
    
Edited my question to make it short and clear.. –  Ramraj Edagutti Apr 14 '12 at 7:14
    
try with torpedo query: torpedoquery.org –  carlo Apr 14 '12 at 9:29
    
torpedoquery api looks similar to hibernate criteria api, had a look at source code but I dont see anything on building very complex queries, or am I missing something.. –  Ramraj Edagutti Apr 17 '12 at 7:45

2 Answers 2

With torpedoquery your query could look like this

 public List<User> findUsers() {
    User from = from(User.class);
    City city = innerJoin(from.getCity());
    with(city.getCode()).in("one", "two").or(city.getCode()).notIn("three", "four");
    District district = innerJoin(city.getDistrict());
    with(district.getCode()).notIn("exclude1", "exclude2");
    State state = innerJoin(district.getState());
    with(state.getCode()).eq("AP").or(state.getCode()).eq("GUJ").or(state.getCode()).eq("KTK");
    with(state.getCountry().getCode()).eq("india");

    return select(from).list(manager);
}

I push on github the example if you want to take a look https://github.com/xjodoin/torpedoquery/tree/master/src/test/java/org/torpedoquery/jpa/examples

share|improve this answer

I am sure your queries can be divided into "N" different kinds of queries; E.G. some queries for geographical location, some for counts, etc.

If your database supports SELECTing from views, why don't you define one or more views for each type of query, and then do a SELECT from the view to filter by the parameter for that view?

You may also be able to rename columns in the view to standardized search parameters passed in from the application: E.G. in one case STATE could be PARAM1, CITY could be PARAM2, in another case, STATE could be PARAM1, ZIPCODE could be PARAM2. Having views with the some of the same column names will save your modules from having to be completely different. (You can have a lookup table that maps the views and parameters; this will allow you to easily add new types of searches).

For example, SEARCH1 contains PARAM1 and PARAM2; PARAM1 is STATE, and PARAM2 is city, and the view name and parameters for SEARCH1 are all stored in a lookup table.

This might make it easier for you to write your application; you can have one module for each kind of view etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.