Constructing an SQL string in Java

[violinsoundcool]

I am trying to construct an sql string within a servlet that will execute based on what the user inputs. Thanks to Bryce, I know where in the servlet to put it, and I can do something like "SELECT thing1 FROM thing2 WHERE thing1=7...etc." However, I need the wherestring to depend on certain conditions. I am displaying an html page initially with four input fields. If the user leaves one of them out, then that field does not need to be included in the where clause. I have done this in SQL free format but never in java. Here is the basic setup that I'm guessing at:

String selectString = "SELECT ...";
String fromString = "FROM...";
String whereString = "WHERE ...";
         if  userInputNameField1 <> null
         {
              sql.append(" and ucase(NAME) Like ucase (userInputField1) ");
                           //I guess this is how you would add it to the where clause
         }
         if userInputNameField2 <> null
         {
              sql.append//same here
         }     //for all four fields

String sqlString = selectString + fromString + whereString + orderByString;

I could be completely wrong, but this is my guess. Do you guys have any advice?

[bryce4president]

Re: Constructing an SQL string in Java

I could have guessed this would be your next question

You need to do something like this

There are a couple ways to handle the next scenario. The way that I would suggest for you is as follows...

1) Use a string buffer.
StringBuffer sql = new StringBuffer();

then do something like...

sql.append("select mname, maddy, mphone from mylib/mcust where mname=' ");
sql.append(field1);
sql.append(" ' and mphone=");
sql.append(field2);

This is how you would build it with a StringBuffer. StringBuffers are faster than Strings because you don't have to recreate a new object every time you do a '+'.

I would say something like this would work well...

sql.append("select mname, maddy, mphone from mylib/mcust where ");
if("".equals(request.getParameter("field1")) && !"".equals(request.getParameter("fieldn"))
{
sql.append(the where clause stuff);
...
sql.append(var);
}
else if(more conditions based on the fields used)
{
....
}


then you will do a

myResultSet = RS.myRS(sql.toString(), opnConn);



Have fun playing

[violinsoundcool]

Re: Constructing an SQL string in Java

I think I understand the first half, but not the second. I'm unclear on whether the second part is another option or if it's part of the same process as the first. So far, I have something like this:

sql.append("SELECT tdydno, tdload, tltype, tlacctno, taname, tlcymd, tlamount,
                tdstatus, tdsrn FROM SCF031J3 ");           
sql.append(userInputName);
sql.append(" and tacity=");
sql.append(userInputCity);
sql.append(" and tastate=");
sql.append(userInputState);
sql.append(" and tditem=");
sql.append(userInputItem);

At first, I thought both parts of the message were one option. Then, I just wondered why you would build the stringBuffer twice.

[bryce4president]

Re: Constructing an SQL string in Java

Sorry about the confusion. The first part is just an example of building a StringBuffer in general. The second part is how you could go about implementing it for your needs.

Just looking over your sql that you provided...you don't have a where clause anywhere

[violinsoundcool]

Re: Constructing an SQL string in Java

Right. I was unsure how to make it conditional, or was that what you were referring to in the second part?

[violinsoundcool]

Re: Constructing an SQL string in Java

It looks like it was. So, here is what I have now:

sql.append("SELECT tdydno, tdload, tltype, tlacctno, taname, tlcymd, tlamount, 
                tdstatus, tdsrn FROM SCF031J3 WHERE tdstatus='C' ");           
if(taname.equals(request.getParameter(userInputName))&&!tacity.equals
                (request.getParameter(userInputCity))&&!tastate.equals
                (request.getParameter(userInputState))&&!tditem.equals
                (request.getParameter(userInputItem));
{
       sql.append("AND ucase(taname) LIKE ucase (userInputName) ");
       sql.append("AND ucase(tacity) LIKE ucase (userInputCity) ");
       sql.append("AND ucase(tastate) LIKE ucase (userInputState) ");
       sql.append("AND ucase(tditem) LIKE ucase (userInputItem) ");
       sql.append(var);
}

Does it look okay?

[bryce4president]

Re: Constructing an SQL string in Java

That is what the second part tries to illustrate. You build the select/from part and then you use a conditional section to build with the information that was entered. If you have 4 fields and all the field combinations are valid then you will have 2^4 combinations which is 16. So your if structure could get pretty long, but that's what happens when you build a search feature like that. That is what my current project is right now. I have 3 fields that can be in combination with each other plus two other fields that each stand alone. Its been quite the project so far. And the search is only 25% of my project Fun fun.

so build up a nice large if/elseif/else conditional section and give 'er hell.

[bryce4president]

Re: Constructing an SQL string in Java

Yeah, that looks pretty good. You will need to make sure that when you do a request.getParameter() that the value inside the parens is in quotes "" such as.... request.getParameter("userInputName");

You want to take their search terms and do a query against the DB right?

[violinsoundcool]

Re: Constructing an SQL string in Java

Yes. Should it look something like I had? Or, something like this?

sql.append("SELECT tdydno, tdload, tltype, tlacctno, taname, tlcymd, tlamount, 
                  tdstatus, tdsrn FROM SCF031J3 WHERE tdstatus='C' ");
     if(taname.equals(request.getParameter("userInputName"));
     {
           sql.append("AND ucase(taname) LIKE ucase (userInputName) ");
      }

if(tacity.equals(request.getParameter("userInputName"));
      {
           sql.append("AND ucase(tacity) LIKE ucase (userInputCity) ");
       }

if(tastate.equals(request.getParameter("userInputName"));
       {
            sql.append("AND ucase(tastate) LIKE ucase (userInputState) ");
        }
			
if(tditem.equals(request.getParameter("userInputName"));
        {
             sql.append("AND ucase(tditem) LIKE ucase (userInputItem) ");
         }

[bryce4president]

Re: Constructing an SQL string in Java

It should look something like you just posted... I have one question though...what are taname, tacity, tastate, tditem? I would think that you want to be testing the parameters as

"".equals() that way you can see if they are left blank by the user or if they have something in them....

[violinsoundcool]

Re: Constructing an SQL string in Java

Oh........Yeah, tacity, tastate, etc are the fields in the datafile. Shouldn't I be testing to see if there is anything in the field, and then if so, attaching that section of the where clause?

[bryce4president]

Re: Constructing an SQL string in Java

Its impossible to know if the input fields match those fields in the datafile because you haven't queried the datafile yet. You are just building the sql string to be able to query it. You won't actually know if there are results from the search until you get your ResultSet back and check to see if there is anything in it.

[violinsoundcool]

Re: Constructing an SQL string in Java

So the logic will go more like: if the field is blank, don't do anything; else attach that section of the where clause. I gotcha. I was just thinking of it as though the only time APPEND should happen is if something is in the field. Sorry. I think I got it now.

[violinsoundcool]

Re: Constructing an SQL string in Java

What about this?

StringBuffer sql = new StringBuffer(); 
     sql.append("SELECT tdydno, tdload, tltype, tlacctno, taname, tlcymd, 
          tlamount, tdstatus, tdsrn FROM SCF031J3 WHERE tdstatus='C' ");           
if("".equals(request.getParameter("userInputName"));
     {}
     else
     {
          sql.append("AND ucase(taname) LIKE ucase (userInputName) ");
     }

if("".equals(request.getParameter("userInputName"));
     {}
     else
     {
          sql.append("AND ucase(tacity) LIKE ucase (userInputCity) ");
     }

if("".equals(request.getParameter("userInputName"));
     {}
     else
     {
          sql.append("AND ucase(tastate) LIKE ucase (userInputState) ")
     }
			
if("".equals(request.getParameter("userInputName"))
     {}
     else
     {
          sql.append("AND ucase(tditem) LIKE ucase (userInputItem) ");
     }

sql.append("ORDER BY tlcymd DESC ");

[bryce4president]

Re: Constructing an SQL string in Java

Yes, but just one more thing....I would change them to

!"".equals(request.getParameter("input1"))
{
sql.append("some text");
}


by useing the '!' you can get rid of those empty brackets and all those else statements.