searching appropriate String in a resultset and return the accurate result

[adam france]

hi
If I run the following Query
select P.ECMSKU ITEM_ID, P.ECMLDS ITEM_TITLE, 'ESH' ITEM_TYPE
from ECMMST P
where
P.ECMSTS = 'A'
AND UPPER(P.ECMLDS) LIKE UPPER('%?%');

Result:
suppose if I pass " yamaha uprirht piano"
I want to get reult like this
first search "yamaha uprirht piano"is available or not.If it is avaible display record.
If not there search the record starting with "yamaha upright".
If any one records r there display.
.If not there start "yamaha piano".suppose records r htere display .
if not there start yamaha or upright or piano.
like this i want to search.plz tell me how to solve this problem.
If u have any code plz give me.

[Jeanne Boyarsky]

Adam,
Welcome to JavaRanch!

The easiest way is to make multiple JDBC calls or use a stored procedure. You have the logic as explained in your post. Do query for phrase. If no results found, ...

[adam france]

hi,
I am not understanding.whar u telling.
plz tell me procedure to solve that problem.

[Jeanne Boyarsky]

Adam,
Which part do you need more details on? If it's the initial query, take a look at the JDBC tutorial for how to runa query through Java.

Tip: to see if a result set has data, call rs.next().

[adam france]

hi.
thaks for u cooperation to me.
my code is like this


if(action.equalsIgnoreCase("search"))
{

String searchKey=req.getParameter("searchfield");
String select = req.getParameter("select");
System.out.println("serch key is----> "+searchKey);
System.out.println("U r Selected is---->"+select);
con=DBconn();
PreparedStatement pst = null;
ResultSet rs;
ArrayList search= new ArrayList();;
try
{

if(select.equalsIgnoreCase("products"))
{
System.out.println("hi iam in products");


ArrayList list=new ArrayList();

String skey = "\'%"+searchKey+"%\'";

StringTokenizer test = new StringTokenizer(skey);

while (test.hasMoreTokens()) {
System.out.println("Count tokens are--->"+test.countTokens());
System.out.println("tokens are--->"+test.nextToken());



}


StringBuffer sqlBuffer = new StringBuffer();

System.out.println("Search key is-->"+searchKey);
sqlBuffer.append(" select P.ECMSKU ITEM_ID, P.ECMLDS ITEM_TITLE, 'ESH' ITEM_TYPE");
sqlBuffer.append(" from ECMMST P");
sqlBuffer.append(" where");
sqlBuffer.append(" P.ECMSTS = 'A'");
sqlBuffer.append(" AND UPPER(P.ECMLDS) LIKE UPPER("+skey+")");
pst=con.prepareStatement(sqlBuffer.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

rs=pst.executeQuery();


while(rs.next())
{
int i=0;
System.out.println("ID id-->"+rs.getInt(1));
System.out.println("Product title-->"+rs.getString(2));
System.out.println("product type is-->"+rs.getString(3));
searchBean bean = new searchBean();
bean.setId(rs.getInt(1));

bean.setTitle(rs.getString(2));
bean.setType(rs.getString(3));

search.add(bean);


req.setAttribute("products",search);
}


----------------------------------

If i Pass search key is: "yamaha piano".
Actually "yamaha piano" record is not there in data base.
so no record is displaying.
But want to get result like.
If any records avalible either starting with yamaha or piano.
my problem how to divide tokens for a given string(sentence). and how to pass that tokens are Query String.
plz tell me.

I am wating u r reply.

[Maximilian Xavier Stocker]

StringTokenizer tokens = new StringTokenizer(skey);
ArrayList keywords = new ArrayList();
String rootsql = "select P.ECMSKU ITEM_ID, P.ECMLDS ITEM_TITLE, 'ESH' ITEM_TYPE from ECMMST P where P.ECMSTS = 'A' AND (";
StringBuffer sqlbuffer = new StringBuffer(rootsql);
while(tokens.hasMoreTokens()){
  sqlbuffer.append("P.ECMLDS LIKE ?");
  keywords.add(tokens.nextToken());
  if(tokens.hasMoreTokens()){
    sqlbuffer.append(" OR ");
  }
}
sqlbuffer.append(")");
PreparedStatement ps = conn.prepareStatement(sqlbuffer.toString());
for(int n=0;n<keywords.size();n++){
  ps.setString(n+1,"%"+keywords.get(n).toString()+"%");
}
ResultSet rs = ps.executeQuery();

[ January 27, 2006: Message edited by: Maximilian Stocker ]

[adam france]

problem is:

if(action.equalsIgnoreCase("search"))
{

String searchKey=req.getParameter("searchfield");
String select = req.getParameter("select");
System.out.println("serch key is----> "+searchKey);
System.out.println("U r Selected is---->"+select);
con=DBconn();
PreparedStatement pst = null;
ResultSet rs;
ArrayList search= new ArrayList();;
try
{

if(select.equalsIgnoreCase("products"))
{
System.out.println("hi iam in products");
ArrayList list=new ArrayList();
StringTokenizer test = new StringTokenizer(searchKey);
String strtoken="";
while (test.hasMoreTokens())
{
strtoken=test.nextToken();
StringBuffer sqlBuffer = new StringBuffer();
System.out.println("Search key is-->"+searchKey);
sqlBuffer.append(" select P.ECMSKU ITEM_ID, P.ECMLDS ITEM_TITLE, 'ESH' ITEM_TYPE");
sqlBuffer.append(" from ECMMST P");
sqlBuffer.append(" where");
sqlBuffer.append(" P.ECMSTS = 'A'");
sqlBuffer.append(" AND UPPER(P.ECMLDS) LIKE UPPER('%"+searchKey+"%') or UPPER(P.ECMLDS) LIKE UPPER('%"+strtoken+"%') or UPPER(P.ECMLDS) LIKE UPPER('%"+strtoken+"%')");
pst=con.prepareStatement(sqlBuffer.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

rs=pst.executeQuery();
while(rs.next())
{
int i=0;
searchBean bean = new searchBean();
bean.setId(rs.getInt(1));
bean.setTitle(rs.getString(2));
bean.setType(rs.getString(3));
search.add(bean);
req.setAttribute("products",search);
}

}
----------------------------------

If i Pass search key is: "yamaha upright piano".
Actually "yamaha upright piano" record is avaible.
But accroding String tokenzer.
enter the string: "yamaha Electric piano". is divided into three tokens yamaha,Electric,piano.
So first i am getting yamaha related titles.
second iam getting upright related titles.
Third iam getting piano related titles.

But I want to get result like.

Examples of search string :
1. �Yamaha Upright� Piano
2. Yamaha Piano
3. �Yamaha Piano�
4. �Yamaha Upright� �Green Piano�

Data in tables
Product ID Description
111 | Yamaha Upright Blue Piano
112 | Yamaha Upright Ebony Green Piano
113 | Yamaha Electone � Sounds like a piano
114 | Piano Book for Yamaha Kids
115 | Sing along Piano Books




he SQL will be formulated according to the number of tokens. The formula will be (n! + n).
For example �A� �B� is treated as 2 tokens : (2! + 2) = 4
We will have �A� �B�, �B� �A�, �A�, �B�
For example, �A B� �C� �D� is treated as 3 tokens: (3! + 3) = 9
We will have �A B� �C�, �A B� �D�, �C� �A B�, �D� �A B�, �C� �D�, �D� �C�, �A B�, �C�, �D�

Based on the following searches :
1.�Yamaha Upright� Piano
Results Accuracy
Yamaha Upright Blue Piano 100%
Yamaha Upright Ebony Green Piano 100%
2.Yamaha Piano
Results Accuracy
Yamaha Upright Blue Piano 30%
Yamaha Upright Ebony Green Piano 30%
Yamaha Electone � Sounds like a piano 30%
Piano Book for Yamaha Kids 30%
Sing along Piano Books 10%

3.�Yamaha Piano�
Results Accuracy
0 results

4.�Yamaha Upright� �Green Piano�
Results Accuracy
Yamaha Upright Ebony Green Piano 100%
Yamaha Upright Blue Piano 50%

[Maximilian Xavier Stocker]

I give up Adam.

You cross posted this message in four different forums on the Sun forums along with here and you don't seem to read the advice that anybody gives you anywhere. Maybe if you stuck to one thread on one site you would find it easier to communicate? Possibly not. I have read all your threads and I just don't get sense that you are capable of incorporating the advice offered in various threads into your code.

[adam france]

ok.
sorry,
I will not post multiple forums.
plz help solving that problem.

[Maximilian Xavier Stocker]

Originally posted by adam france:
ok.
sorry,
I will not post multiple forums.
plz help solving that problem.

I think this a problem for you to solve now. Several people have give you code samples showing how to dynamically build queries. My earlier post in this thread has an example of doing it with a prepared statement.

If you want to return several outputs depending on how many keywords matched you need several queries. You need to write that code yourself I think.

[Jeanne Boyarsky]

Adam,
Take a look at your first post for the multiple queries that you need.

What Maximilian is trying to say is that JavaRanch isn't the type of place where people will do the whole project for you. He has been very generous and helpful in giving you code and guidance. After you try to run a second query based on the result of the first query, feel free to post back here with a specific problem.

One piece of advice: post the relevant code snippet causing problems. Everyone here is a volunteer and would appreciate being able to read the code quickly and efficiently.

Thanks,
Jeanne
JDBC Forum Bartender

[Maximilian Xavier Stocker]

UseTheForumNotEmail