File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Java in General and the fly likes Need Help in to write query using StringBuffer Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Need Help in to write query using StringBuffer" Watch "Need Help in to write query using StringBuffer" New topic
Author

Need Help in to write query using StringBuffer

a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Hi All,

I have five textfields and a search button in my JSP page. I can fill any one of those five textfields or i can fill all five fields to search.
i need to take all the parameters in my action class and validate which one is not null and add that particular value to my query string. i found it difficult to my experience and thinking. can anyone give me an idea how to write a dynamic query for this using string buffer?

any idea is really appreciated.

thanks in advance
kishore
Jer Deras
Greenhorn

Joined: Sep 29, 2009
Posts: 10
i have one for two text fields.

may be extend it for five fields

StringBuffer query = new StringBuffer("select * from empmaster where");

if(null != userName && !userName.equalsIgnoreCase("")
&& null != userId && !userId.equalsIgnoreCase("")){
query.append("`EMPLOYEE NAME` = '"+userName+"' and ID = '"+userId+"'");
} else if (null != userName && !userName.equalsIgnoreCase("")){
query.append("`EMPLOYEE NAME` = '"+userName+"'");
} else if (null != userId && !userId.equalsIgnoreCase("")){
query.append("`ID` = '"+userId+"'");
}
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Appreciated Jer Deras,

your code for two fields are working fine. but how to make it simple and dynamic if there are three fields?


any ideas please,

thanks alot in advance,
kishore
Jer Deras
Greenhorn

Joined: Sep 29, 2009
Posts: 10
have a separate second variable for appending queries apart from the first variable which has 'select * from empmaster'
do the validation for first field as usual.
if its not null append it to the second variable. now the variable will not be null.
if the first field is null then you wont append it to the second variable. now the variable will be null.

for the next fields do a null check for the second variable and append 'and' keyword if its not null

finally append both first variable (which has select statement) and second variable (which has conditions)
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Thank Jer,

Your logic is working but i still get error. My peice of code for two fields is this:

sb = new StringBuffer();
sb2 = new StringBuffer();
sb.append("select BillNo,Client from details where ");

if (mainPageForm.getClient()!=null && !mainPageForm.getClient().toString().equalsIgnoreCase(""))
{
client = mainPageForm.getClient();
log.debug("log client: "+client);
sb2.append("client='"+client+"'");
}
if (null != sb2.toString() && !sb2.toString().equalsIgnoreCase("") && mainPageForm.getBillno()!=null && !mainPageForm.getBillno().toString().equalsIgnoreCase(""))
{
billno = mainPageForm.getBillno();
log.debug("billno e: "+billno);
sb2.append("and billno='"+billno+"'");
}else if (sb2.toString()==null && sb2.toString().equalsIgnoreCase("") && mainPageForm.getBillno()!=null && !mainPageForm.getBillno().toString().equalsIgnoreCase(""))
{
billno = mainPageForm.getBillno();
sb2.append("Billno='"+billno+"'");
}

String sql = sb.toString()+sb2.toString();

still i get this error:
search query: select BillNo,Client from details_old where
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

need to do any modifications..?
thank you,
kishore
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Hi Jer,

i modified my code based on your idea. this gives me good results with either of two fields for search


am i doing correct way, or is there any better way of writing.. please suggest

thank you,
kishore[edit]Break long lines. CR[/edit]
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39396
    
  28
Your code is difficult to read because you didn't use the CODE button.
And why are you using StringBuffer rather than StringBuilder?
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19719
    
  20

Please use code tags.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Thank Jer,

Your logic is working but i still get error. My peice of code for two fields is this:


still i get this error:
search query: select BillNo,Client from details_old where
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

need to do any modifications..?
thank you,
kishore
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
Campbell,

Can you pleae explain the difference between StringBuffer and StringBuilder and the advantage of using StringBuilder instead of the other, because i really need to implement it in my project.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39396
    
  28
StringBuilder class.
a kishore
Ranch Hand

Joined: Sep 16, 2009
Posts: 82
thank you
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39396
    
  28
You're welcome
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need Help in to write query using StringBuffer