Need Algorithm SQLite Android

Question

What's the algorithm to get all string X from column Y and return as a cursor? I need a function which returns the cursor multiple times, so the algorithm has to account for that.

I'll really appreciate it.

UPDATE:

public Comment search1(){
        List<Comment> comments = new ArrayList<Comment>();

        Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS,
                allColumns, MySQLiteHelper.COLUMN_NAME +" LIKE" + "'%QUERY%'", null, null, null, null);

        cursor.moveToFirst();
        Comment newComment = new Comment();
        //set values of this comment
        newComment = cursorToComment(cursor);
        //Close cursor
        cursor.close();

        return newComment;
    }

Ignore the Comment part it's what I use for the ListView Adapter.

Thanks I get that but I'm just experimenting to see what works and what doesn't. It serves my short-term experimentation and is not permenant. However why is it so awful. — Ahmed Zafar, Aug 22 '13 at 20:10
Ok then. It's awful because it means nothing. One week without touching this method when you come back you wouldn't remember what this does and have to waste time reading the code to know. If you give a meaningful name, you wouldn't have to waste this time. — m0skit0, Aug 22 '13 at 20:43
Haha okay this is just for today and not for the week but yes you're right thanks for the input. Much easier this way for me then say name it public void method_where_I_try_another_search_method_done_xyz_way lol — Ahmed Zafar, Aug 22 '13 at 20:44

score 1 · Accepted Answer · answered Aug 22 '13 at 18:40

1

Assuming you have an SQLiteDatabase object, use one of its several query methods to return a Cursor. The query would be a simple SELECT of column Y values.

You might find an SQLiteOpenHelper useful for obtaining the data base object. See the guide topic Using Databases for more details.

answered Aug 22 '13 at 18:40

Ted Hopp

232,168
48
399
521

I have the cursor, Thing is it returns only the first value of the search result in the column. I want it to go on and get the rest in the column. – Ahmed Zafar Aug 22 '13 at 19:08
2

@AhmedZafar - Are you using the Cursor's `moveToNext()` method? Perhaps you should edit your question to show us your code. – Ted Hopp Aug 22 '13 at 19:26
done! it shows the first instance of `QUERY`. How do I get the rest using moveToNext(), and if possible can you tell me how to get an exact string instead of using `LIKE`? Thanks – Ahmed Zafar Aug 22 '13 at 19:55
1

The general pattern is to use `while(cursor.moveToNext()) { ... }` [(explained in this answer)](http://stackoverflow.com/a/10723771/1321716). A quick web search would tell you that in SQLite queries, an exact comparison is done with `=`, for example `column_name = 'value'`. – Mattias Buelens Aug 22 '13 at 20:02
@mattias- Do I still need to use moveToFirst()? I'm sorry but I know about moveToNext but don't know how to use it. To be precise I'm not sure exactly what it does. I've looked it up but somehow doesn't make sense to me. Does it move it to the next row with the result? Do I still need to use moveToFirst()? – Ahmed Zafar Aug 22 '13 at 20:14
1

@AhmedZafar - To process all the rows returned in a `Cursor`, the usual procedure is to use `moveToFirst()` to get to the first entry, then `moveToNext()` to get to each remaining one in turn. You should check the return value, which will be `false` if there is no such element (`moveToFirst()` will return `false` if the result set was empty). – Ted Hopp Aug 22 '13 at 20:24
2

@TedHopp Correct, although the initial `moveToFirst()` is not needed if you just retrieved the cursor from the database. The explanation is in the answer I linked in my previous comment. – Mattias Buelens Aug 22 '13 at 20:28
@ted- - Guys I've updated my question to show what worked but I want to ask two questions. A) Can I skip using moveToFirst() somehow and B) when I was using the moveToNext with while it was throwing an index out of bounds exception. Any idea why. Also C) Can you please tell me exactly how to change my query so that it only returns EXACT matches. I've tried SELECT * FROM = in rawQuery and it isn't working for me. And thanks I've made progress with this. – Ahmed Zafar Aug 22 '13 at 20:38
2

@AhmedZafar A) Yes, you can, see my first comment. B) Index out of bounds exceptions indicate that you're not moving through the cursor correctly. Your updated code seems to be fine though, and the simplified version without `moveToFirst()` should be fine as well. C) To put it simple: just replace `LIKE` with `=` in your current code, that should just work. No need to resort to raw queries for this simple use case. – Mattias Buelens Aug 22 '13 at 20:51
The = returns nothing. The query has spaces in between it can that be a problem? Do I have to use the percentage marks? I used ` Cursor cursor = database.query(MySQLiteHelper.TABLE_COMMENTS, allColumns, MySQLiteHelper.COLUMN_NAME +" =" + "'%EXACT SEARCH%'", null, null, null, null);` EDIT: It worked I just removed the percentage marks. What are those for? – Ahmed Zafar Aug 22 '13 at 20:54
Anyway thanks so much guys you guys are like awesome. It would be great if you could tell me what the percentage signs are for. Also can I please ask for your help in http://stackoverflow.com/questions/18390829/sqlite-query-less-than-or-greater-than-check. Thank you ! – Ahmed Zafar Aug 22 '13 at 21:07
1

@AhmedZafar - The `%` is an SQL wildcard character. It matches any number of characters (like `*` does in a regular expression). The `_` character in SQL matches any single character (like `?` does in regular expressions). See [the docs](http://www.sqlite.org/lang_expr.html#like). – Ted Hopp Aug 22 '13 at 21:26
So it's like QUERY and if QUERY exists in between just gimme the cursor kinda thing? – Ahmed Zafar Aug 22 '13 at 21:42
@AhmedZafar - Exactly. – Ted Hopp Aug 22 '13 at 21:56
@ted- I need to ask this here because it's not being answered. What's the best way to check if a database already exists. getApplicationContext().getDatabasePath("db.db") really isn't working. – Ahmed Zafar Aug 22 '13 at 22:35
1

@AhmedZafar - Subclass SQLiteOpenHelper. If the database does not exist, it's `onCreate` method will be called when you call `getReadableDatabase` or `getWritableDatabase`. It also helps manage database versioning, which is very helpful when you want to modify the database structure. – Ted Hopp Aug 22 '13 at 23:18

Need Algorithm SQLite Android

1 Answers1