What pattern would solve this challenge

[zooxmusic]

Hi all,
I have a situation that I believe a pattern can solve and want to really start understanding how to apply these to real-life (my real life) situations.

I have a QueryBuilder class that needs to build a select statement based off of what has content.

Something like

public class QueryBuilder


public String buildQuery(Query query)
{
StringBuffer buffer = new StringBuffer();
buffer.append("select * from some_table ");

if(Util.textHasContent(query.getField1()))
{
buffer.append(" field1 = '" + query.getField1() + "' ");
}
if(Util.textHasContent(query.getField2()))
{
buffer.append(" field2 = '" + query.getField2() + "' ");
}
etc...

return(buffer.toString());
}


now I need to know when to put in the

"WHERE" and the "AND" keywords but I know there is an elegant way to accomplish what I need to do. Maybe with a String.format function that is new to JDK 1.5 or a Formatter or something. But if there is a pattern I can go look at please let me know. For some reason I keep thinking little language but it seems so overwhelming for me.

Thanks in advance

Brian

Spend like you don't need the money,
love like you've never been hurt and dance like nobody's watching!

 

[EdwardJS024]

Hi Zoo,
look into the Intepreter Pattern.

 

[zooxmusic]

I shall sir, gracias

Spend like you don't need the money,
love like you've never been hurt and dance like nobody's watching!

 

[EdwardJS024]

Interestingly enough Zoo, I came across this today in a project i am working on. I am a bit stuck but trying out a few interesting things.

OK, remember Repositories, with the very sophisticated collection-like api? Incase you dont, repositories provide a layer of indirection between the domain objects and the ugly outside storage world. Their job is to find and return domain objects. Clients see them as a collection. For example you might have a CarRepository or a CustomerRepository, incharge of finding customers for a client.

As my design goes right now, this is what i have so far:

I have a Repository that takes a criteria from a client and submits it to a DataAccess layer. I have 2 possible DAL's so far, OracleFinder and XmlFinder. They find data and hand it back through a common datatype(datatable) to the repository.

To find the data, they take the criteria as is and uses an appropriate Query Translator to translate the criteria into a syntax that the DAL knows.

Right now my XmlFinder dal uses a XPathTranslator like this:

string xpathQuery = new XPathTranslator(aCriteria).ToString()

The translators override ToString to build a query for the dal in its native language. The XmlFinder can then query the xml document with it's new xpath expression and OracleFinder can query oracle with Sql. So, same criteria different query syntax. Remember, never limit your self to a single storage medium, you never know when your boss will say something like: "ok, for the 3.5 release we need to move the data over to Oracle, that won't be a problem will it"?

sadly enough I got burnt by just that.

Now, to be honest i am trying to figure out exactly how a translator should translate the criteria. So i am where you are at.

 

[EdwardJS024]

Any suggestions let me know

 

[zooxmusic]

Jay,
(note: Forgive me if you are aware of this already and understand that it is not correct. I may end up there soon)

I did find a Formatter object in java that is similar to MFC's CString.format(). I haven't completed the thought but it seems like it will do the trick. In java 1.5 there is a String.format() method (and previous versions a Formatter class existed by itself). It will allow me to use placeholders in a string and then pass in an array of parameters of some sort and I can dynamically build the query.

I Don't know if this is correct but since I am under the gun I am going to use this method right now instead of a long "if-mess".

Example;

String sqlStatement = "select * from %s where %s %s %s %d etc..."


String sql = String.format(sqlStatement, parameters[]);


please if you see fault with this immediately because I may be too nearsighted let me know.

Brian


Spend like you don't need the money,
love like you've never been hurt and dance like nobody's watching!

 

[mooxie]

Well, what about generating a query like
SELECT * FROM some_table WHERE 1 AND ... AND ... AND ... etc..

like so:
StringBuffer buffer = new StringBuffer();
buffer.append("select * from some_table WHERE 1 ");

if(Util.textHasContent(query.getField1()))
{
buffer.append("AND field1 = '" + query.getField1() + "' ");
}
if(Util.textHasContent(query.getField2()))
{
buffer.append("AND field2 = '" + query.getField2() + "' ");

A little bit of boolean magic =)

 

[zooxmusic]

Thanx mooxie but that is exactly what I wasn't trying to do. The reason being is this: lets say I don't have any condition at all, I would already have the WHERE clause appended but that would not be correct. So you have to check some counter of whether or not a condition has been added to for each and every field. And on that same note if I have field2 but no field1 I can't have an AND in there because the syntax would be wrong, and I can't just put an AND at the end because there may not be a next field. So on each and every field I would have to check to see if there was a previouse field added and if not I have to then append the WHERE and possibly the AND. And if I start wanting OR's in there... forget about it.

And as I am now getting a firmer grip on OO I have learned that switch statements (if's also) are very UN-Object Orientated. Actually the opposite of OO but I don't have a firm enough grasp on it to figure this out quickly. The example that I was given was a calculate function which is very similar to this. (pseudo of course)

if(operator.equals("+"))
return operand1 + operand2;
else if(operator.equals("-"))
return operand1 - operand2
etc....

OO way of doing it

public abstract class Operator
{
public abstract int calculate(int operand1, int operand2)
}
public class Add extends Operator
{
public int calculate(int operand1, int operand2)
{
return(operand1 + operand2);
}
}
public class Subtract extends Operator
{
public int calculate(int operand1, int operand2)
{
return(operand1 - operand2);
}
}
etc...

//then create your operator class
//at the time you definitely know
//what you need to do

Operator operator = new Add();

int result = operator.calculate(10, 20);

and it really hit home for me

But thank you very much.


Brian


Spend like you don't need the money,
love like you've never been hurt and dance like nobody's watching!