April 14, 2012

Java string interpreted wrongly in SQL

Question by Shweta B. Patil

There is an string[] likely;
This array stores the name of column of database table dynamically while runtime.And the program understand the size of the likely in runtime.
Now to put this in sql query .I use a for loop for concatanation of string

for(int k=0;k<likely.length;k++)
    {
        temp1=""+likely["+k+"]+"='Likely' AND ";
        temp=temp.concat(temp1);                
    }

if the size is 3 the final temp will look like

temp = " "+likely[0]+"='Likely' AND "+
    likely[1]+"='Likely' AND "+
    likely[2]+"='Likely' AND "

Now i formulate sql query as

sql ="SELECT * FROM PUNE WHERE"+temp+"Arts_And_Museum='Yes'";

But during the

ps = con.prepareStatement(sql);

this statement is compiled like

SELECT * FROM PUNE 
WHERE [+likely[0]+]='Likely' 
AND [+likely[1]+]='Likely' 
AND [+likely[2]+]='Likely' AND Arts_And_Museum='Yes'

After deep investigation ,I came to conclusion that it interprets ” as [ or ] alternately..

As a result i get an error
How should i solve this problem?

I run a for loop and prepare a string
I am trying to write a sql syntax

Answer by Starx

The symbol is used for escaping. On doing this, you are escaping all the front characters.

Whenever you are asking for an item in array you can access it using likely[k] no need for likey["k"]

Here is how you should do it.

temp1="\"+likely[k]+"\='Likely' AND ";
...

Please fill the form - I will response as fast as I can!