-1

I want to use sql "like" operator to get data set from the database. But sql like operator didn't work for me collection of words(word phrases). There for I have to add words into arraylist and word by word pass into sql like operator. That is huge time consuming. My query take lot of time because of my databse contains 1 million rows. Isn't it a proper way to put word phrases into like operator?

This is my code:

     sb.append("SELECT Cor_Sentence FROM corpus Where ");
             for(int k=0;k<wordList.size();k++){
                 sb.append( " Cor_Sentence like '%" + wordList.get(k) + "%' OR ");
                } 
     sb.append(" 1=0");

Isn't there proper way to put whole content into between like operator :What is reason for below code type thing not work?

    WholeContentHere="abcd bcde defg ffog gghk ";
    sb.append("SELECT Cor_Sentence FROM corpus Where ");
                 sb.append( " Cor_Sentence like '%" WholeContentHere + "%'");
                }
Maduri
  • 249
  • 1
  • 5
  • 19
  • Why 1=0? What's the purpose of like then? – SMA Nov 29 '14 at 09:54
  • @almasshaikh Actually without that code didn't work. That also got from the stackoveflow answer. I'm also not clear that. But work properly. My main problem is each and every word query execute and huge time consume. How can I solve it? – Maduri Nov 29 '14 at 09:57
  • You say `WholeContentHere="abcd bcde defg ffog gghk "` so if you field containing something like "dhdhfhfhfhabcd bcde defg ffog gghk fkdjfj"? or you want individual words within above? – SMA Nov 29 '14 at 10:00
  • How should this `Cor_Sentence like '%abcd bcde defg ffog gghk %'` work? How should SQL know, that the whitespace is not actual content of that column and should be interpreted as delimiter? You're looking for something like this `Cor_Sentence in ('abcd', 'bcde', 'defg', 'ffog', 'gghk')`. – Tom Nov 29 '14 at 10:00
  • @almasshaikh yep that is something like "dddd hhhh hjjj kklll". I want to select whole raws match with any of given word. Because of that doesn't work I put word by word. I want to avoid that method. Isn't there any way to put whole content and get whole? – Maduri Nov 29 '14 at 10:03
  • @Tom I think that will work. Yep something like that. I didn't aware that "in" operator. I will try. Any how I want to select whole raws if that match with one or more word. Thanx I will try and comment. I think according to your suggestion first I put word into arraylist and then add word to your given way string variable and then put that variable inside in().Am I correct? – Maduri Nov 29 '14 at 10:05
  • It is up to you how you build your SQL string. There is no need to create an `ArrayList` and then a `String` (that contains each ArrayList element) first. – Tom Nov 29 '14 at 10:24
  • @Tom That didn't work for me:(.That in operator work exact matching things. But if I'm taking one row that contains more than one word.As a example if I'm search "dddd" the data base row may contain "dddd eeee". That doesn't select from "in" operator.I want to select whole raws if that match one word may enough. Is there any suggestion from you? some one minus vote. I don't why and what wrong with my question. – Maduri Nov 29 '14 at 10:35
  • Well, if you don't know the excact value of the column, then you have to use `like '%...%'`. You may improve the performance by using an [index](http://stackoverflow.com/questions/2955459/what-is-an-index-in-sql) on that specific column. – Tom Nov 29 '14 at 10:46
  • @Tom Can you briefly give idea about index? CREATE INDEX index_name11 ON corpus (Cor_Sentence). That way I can create. Then I can use that index name to search query instead of real column name. Is that only thing doing in the indexing in my problem? I know you sent me a link too. Thanx lot. Can you give me very brief idea? – Maduri Nov 29 '14 at 11:06
  • 1
    On another note, you should never use string pasting to construct SQL queries. It's both faster and more secure to use prepared statements. – chrylis -cautiouslyoptimistic- Nov 29 '14 at 11:16
  • @chrylis I'm using Statement. Not prepared statement. Isn't it a proper way to optimize my above code? one thing is word by word insert to query. Other thing is go through 1 million raws. Indexing not working I think because of database raws contains just sentences read from web pages. Not any integer values. Isn't your any suggestion? – Maduri Nov 29 '14 at 11:25
  • Any time that you're either (1) using external input as a value in a query or (2) repeating the same query with different parameters, use prepared statements. Now it's sounding like a relational database may be the wrong data model entirely, but that's a separate issue. – chrylis -cautiouslyoptimistic- Nov 29 '14 at 11:39
  • @chrylis Thanx dear. Can you suggest something to optimize my query? – Maduri Nov 29 '14 at 12:43

1 Answers1

0

Unless a typo when you entered the question in stackoverflow, you are missing '+' before WholeContentHere

WholeContentHere="abcd bcde defg ffog gghk ";
    sb.append("SELECT Cor_Sentence FROM corpus Where ");
                 sb.append( " Cor_Sentence like '%" + WholeContentHere + "%'");
                }
Metal Latem
  • 101
  • 4
  • Actually that above code I just give the code as a example. Your correct + mark should be there. But problem with the code. When code trying some sql executor we can see that when adding more words inside like that doesn't give all rows. I think main problem is I'm searching 1 million rows. Can you suggest any way to optimize it? huge time take to go through it? Indexing may be doesn't work because of rows content just collection of sentences. – Maduri Nov 29 '14 at 13:00
  • see - [link](http://stackoverflow.com/questions/6651930/fastest-way-to-find-string-by-substring-in-sql) Also it might help to create a stored procedure that accepts the search string as a parameter...it will further help in reducing time. – Metal Latem Nov 29 '14 at 13:33