0

Search Database with Keyword:

Is there a way to query the entire table of SQLite database for matching word. I am trying to place a word in keyword JTextField for it search the entire Job table and to return rows which contain that matching words. Each row representing a unique job.

I presume this below snipped structure would not achieve the result 

SELECT * FROM Job WHERE Job MATCH 'Microsoft';

Any quick and simple recommendations?

FrankS101
  • 2,112
  • 6
  • 26
  • 40
Hoody
  • 2,942
  • 5
  • 28
  • 32
  • This is not easy task. May be these links can help you out. [string value in all columns](http://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/) and [search all columns of all tables](http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm). – Smit Jan 12 '13 at 09:32

2 Answers2

0

It appears you can use the syntax you're suggesting as long as it's a full text search table:

http://www.phparch.com/2011/11/full-text-search-with-sqlite/

You should probably read the official SQLite documentation on FTS tables. Note that these are "virtual" tables and as such don't use the database file in the same way - this might not be what you want, but it matches the syntax you're looking for. The SQLite page linked has pretty good descriptions. Note that the FTS module is an optional extra which may not be installed.

You shouldn't need to worry about SQL injection attacks as long as you use placeholders. Look at this SO question, specifically the second answer with the code snippet. Essentially you put a ? in the statement, and then supply the string to substitute as a parameter to the query function. That should do SQL escaping for you.

Community
  • 1
  • 1
Cartroo
  • 4,233
  • 20
  • 22
  • I think you missed a point, I am not looking to query one column, rather the entire table. I also dont want sql injection attacks, so would pst.setString(1,text.getText()) work with it – Hoody Jan 12 '13 at 09:24
  • OK, sorry for the misunderstanding. I don't believe there's a simple way unless you query all the columns (`LIKE '%X%' OR LIKE '%Y%' OR...`). If you don't know the columns in advance, I guess there'd be a way of pulling the information from the schema, but that's not "simple". – Cartroo Jan 12 '13 at 09:29
  • sql statement not executing exception thrown, having tried the full-text idea from the first link youve provided – Hoody Jan 12 '13 at 10:20
  • I think fts3 has almost all the same features, just depends which is installed. – Cartroo Jan 12 '13 at 11:26
0

i don't know sqlite but in mysql we have full text search which is used as

select * from table where match(name) against('value')

just see weather full text search is there or not in sqlite, i found this link verify it

http://www.sqlite.org/fts3.html#section_3

vidyadhar
  • 3,118
  • 6
  • 22
  • 31