Creating Query from Many Parameters

[Muggle]

Hello everyone,

I have a situation and need some help.

Our application is providing a web-service like service to a
presentation layer application. The presentation layer application
gathers the user inputs and send an XML request. Our application looks
up in the database, generates a response XML, and sends back.

The user can enter any combination of inputs(one or more), and there
are 15 of them. My task is to generate the query(the 'where' clause)
and pass it to a Weblogic database control. What I currently do is, I
put the incoming parameters in a HashMap with the corresponding column
names in the table as keys. Then I pass the Map to a helper method
which iterates through the map and generates the 'where' clause.

I think this approach is ugly because some of the paramters are muti-
valued, some are date-ranges and some include wild cards and therefore
require a lot of tinkering. Besides it is almost impossible to
accomodate any change in the business logic.

I apologise if the picture I present here is not clear. I would
greatly appreciate if anyone can shed some light or point to somewhere
I can look for samples.

(Please take into account the facts that this is JDK 1.4 and I have
absolutely zero control over the overall design)

Thanks in advance
Muggle

 

[Abhi]

Hi,
let me frame the prob again:
u have some input fields.you are extracting them nd putting in a map
where the keys are Column names like EmpId,EmpDept,DOB etc
now ur query will be like select * from EMP where
EMPID=?,EmpDept=?.....
depending on if these fields are presnt in ur map or not.i.e. you are
appending a where string in your query.Now your prob is what happns if
the date is range or if u want to use like operator.
pls correct me if I am wrong.

well can we have a specific scenario when this happns??I mean say ur
DOB is coming as a range sometimes and as a specific value sometimes
so what are the input parameters responsible for them (the XML
elements)etc....if possible can u post some part of the XML doc u r
getting as a request?

 

[Muggle]

Hello,

I should have provided a sample. Here is one with tag names changed :

<Request>
<Id></Id>
<Name>Dav*</Name>
<DOB>
<From>1960-01-01</From>
<To>1963-09-09</To>
</DOB>
<AnotherDate>
<From></From>
<To></To>
</AnotherDate>
<Hobby>
<Value>Dance</Value>
<Value>Soccer</Value>
</Hobby>
<Food>
<Value>Beef</Value>
<Value>Fish</Value>
</Food>
<State>NY</State>
</Request>

Here the where clause would be something like "Where Name LIKE 'Dav*'
AND (DOB between(xxxx-xx-xx) and (xxxx-xx-xx) ) AND (HOBBY in
('Dance','Soccer' ) )AND (Food in ('Beef', 'Fish')) AND (STATE='NY')

This is not trivial as the table I am issuing the query against is
going to have millions of records.

Thank you
Muggle

 

[Lew]

Hi,
let me frame the prob again:
u have some input fields.you are extracting them nd putting in a map
where the keys are Column names like EmpId,EmpDept,DOB etc
now ur query will be like select * from EMP where
EMPID=?,EmpDept=?.....
depending on if these fields are presnt in ur map or not.i.e. you are
appending a where string in your query.Now your prob is what happns if
the date is range or if u want to use like operator.
pls correct me if I am wrong.

I am with Andrew on this: please refrain from top-posting. Please follow
standard typographical conventions such as capitalizing the first word of each
sentence, placing a full stop at the end of each sentence, putting in
appropriate white space and for Pete's sake use real words, not "u" and "pls".
Your posts are quite nearly unreadable.

- Lew

 

[Muggle]

I beg your pardon ? Are you responding to a wrong thread ?

 

[Muggle]

Lew, I am sorry. I did not realize you were replying to the other
author. I apologise.

 

[Abhi]

Muggle
can you tell me how you are reading the xml file and converting the
objects in java?
I used Xpath.But the problem is you are using jdk1.4 and the package
javax.xml.xpath is with java5.Please see http://java.sun.com/developer/
technicalArticles/xml/validationxpath/.
Since you are already running the code so I am assuming you can query
the xml file.If you face problems please revert back.
=================================
Here's my logic:I am putting the details in a bean.Am storing the date
in a string array with constant width 2 as your xml file shows.But if
its changing then use growable arrays e.g Vector,ArrayList.
I am putting hobbies and food in a vector as those can change in
length.

I am pasting the code.See if it is of any help to you...........

The ouput was-->SELECT * FROM EMP
where name like Dav*
and DOB between 1960-01-01 and 1963-09-09
and hobby in'Dance','Soccer'
and food in'Beef','Fish'
and state =NY
******************************************************************************************
/*
* Created on Jan 31, 2007
*/
package help.Muggle;

import java.util.Vector;

/**
* @author Jboss
*/
public class Emp_Dtls {

String name;
String state;
Vector hobbies;
Vector Food;
String[] dob;
String[] another_Date;

public Emp_Dtls() {
hobbies = new Vector();
Food = new Vector();
dob = new String[2]; //since only 2 values From and To
another_Date = new String[2]; //since only 2 values From and To
}

/**
* @return String
*/
public String getName() {
return name;
}

/**
* @return String
*/
public String getState() {
return state;
}

/**
* @param String
*/
public void setName(String string) {
name = string;
}

/**
* @param string
*/
public void setState(String string) {
state = string;
}

public void populateDOB(String str1, String str2) {
dob[0] = str1;
dob[1] = str2;
}

public void populateAnother_Date(String str1, String str2) {
another_Date[0] = str1;
another_Date[1] = str2;
}

public void populateFood(String str) {
Food.add(str);
}

public void populateHobbies(String str) {
hobbies.add(str);
}


}
/*
* Created on Jan 31, 2007
*/
package help.Muggle;

import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;

import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;

/**
* @author Jboss
*/
public class Test_Muggle {

public static void main(String[] args) {
Emp_Dtls empDtls = new Emp_Dtls();

XPath xp = XPathFactory.newInstance().newXPath();

InputSource inp = new InputSource("C:/Muggle/Request.xml");

try {

//store the name

//XML query string
String expr = "/Request/Name";
String name =
(String) xp.evaluate(expr, inp, XPathConstants.STRING);
System.out.print("\n name is " + name);
empDtls.setName(name);

// Store DOB

//query String
expr = "/Request/DOB/From";

String dobFrom =
(String) xp.evaluate(expr, inp, XPathConstants.STRING);

//query String
expr = "/Request/DOB/To";
String dobTo =
(String) xp.evaluate(expr, inp, XPathConstants.STRING);

System.out.print("\n dob" + dobFrom + "\t" + dobTo);

empDtls.populateDOB(dobFrom, dobTo);

//Store Another date

//repeat the above process

//store Hobby

//query
expr = "/Request/Hobby/Value";

NodeList hobbies =
(NodeList) xp.evaluate(expr, inp, XPathConstants.NODESET);
System.out.print("\n size" + hobbies.getLength());

for (int i = 0; i < hobbies.getLength(); i++) {
Node currNode = hobbies.item(i);
empDtls.populateHobbies(
currNode.getFirstChild().getNodeValue());
System.out.print(
"\n val node" + currNode.getFirstChild().getNodeValue());
}

//store Food

// query
expr = "/Request/Food/Value";

NodeList food =
(NodeList) xp.evaluate(expr, inp, XPathConstants.NODESET);
System.out.print("\n size" + food.getLength());

for (int i = 0; i < food.getLength(); i++) {
Node currNode = food.item(i);
empDtls.populateFood(currNode.getFirstChild().getNodeValue());
System.out.print(
"\n val node" + currNode.getFirstChild().getNodeValue());
}

//for state
expr = "/Request/State";
String state =
(String) xp.evaluate(expr, inp, XPathConstants.STRING);
System.out.print("\n" + state);
empDtls.setState(state);

String hobby_val = "";
for (int i = 0; i < empDtls.hobbies.size(); i++) {
hobby_val =hobby_val+"'"+ empDtls.hobbies.get(i) +"'"+ ",";
}
hobby_val = hobby_val.substring(0, hobby_val.length()-1);

String food_val = "";
for (int i = 0; i < empDtls.Food.size(); i++) {
food_val = food_val +"'"+ empDtls.Food.get(i) +"'"+ ",";
}
food_val = food_val.substring(0, food_val.length()-1);

// now form the string

String operator = name.contains("*") ? "like" : "=";
String appnd1 = "\r where name \t" + operator +
"\t"+empDtls.getName();
String appnd2 =
"\r and DOB between \t"
+ empDtls.dob[0]
+ "\t and \t"
+ empDtls.dob[1];
String appnd3 = "\r and hobby in" + hobby_val;
String appnd4 = "\r and food in" + food_val;
String appnd5 = "\r and state =" + state;
String sql =
"SELECT * FROM EMP "
+ appnd1
+ appnd2
+ appnd3
+ appnd4
+ appnd5;

System.out.print("\n sql" + sql);

} catch (XPathExpressionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

 

[Abhi]

Lew,
I am very sorry for irritating you and Andrew.I
apologise.Actually,this is a result of too much SMS texting bad habit
of young generation.Since this is a formal place I will refrain from
these things.

Regards

 

[Andrew Thompson]

Lew,
I am very sorry for irritating you and Andrew.I
apologise.Actually,this is a result of too much SMS texting bad habit
of young generation.Since this is a formal place I will refrain from
these things.

Thanks for that. Another point about helping
to communicate what you need to others, is
to make text easy to read. Note how each
of my sentences ends with two spaces? That
helps the reader to quickly jump from sentence
to sentence, scanning the words for clues to
the problem.

Here is how I might have written the text
you put above.

"I am very sorry for irritating you and Andrew. I
apologise. Actually, this is a result of too much
SMS texting bad habit of young generation. Since
this is a formal place I will refrain from these things."

With two spaces after each sentence, and one
after every comma, text becomes easier to
'read back'.

Hope you find good resolution to your technical
problem.

Oh, and I am not irritated with you. I might
have been at one moment or another, and
possibly in the future(?), but your effort to
stop use of SMS style text completely
removes that. (And from past experience
with Lew, I am guessing the same of them).

HTH

Andrew T.

 

[Lew]

Oh, and I am not irritated with you. I might
have been at one moment or another, and
possibly in the future(?), but your effort to
stop use of SMS style text completely
removes that. (And from past experience
with Lew, I am guessing the same of them).

I'm not irritated either. The advice was to help, not express irritation.

- Lew

 

[David Segall]

Thanks for that. Another point about helping
to communicate what you need to others, is
to make text easy to read. Note how each
of my sentences ends with two spaces?

Are you really, really old? Two spaces are recommended for people
using monospaced typewriters or, possibly, to help with dyslexia.
<http://en.wikipedia.org/wiki/Full_stop#Spacing_after_full_stop>