[Scorpiobuzz]

Alright, I asked a question earlier in the week and didn't get any replies, so I did some more research and I think I understand a little bit more about my problem and can therefore narrow my question.

I am trying to build a query method in my database helper class that will return a Cursor for a user-specified string. I have the string being passed into the ListActivity successfully. (I have a toast message that uses the bundled string, so I know that works.) So, I have the method like this:

public Cursor findByTitle(String title) throws SQLException {
    	//define cursor to return and execute database query
    	Cursor mCursor = 
    			
    			mDb.query(DATABASE_TABLE, KEY_PROTEIN, selection, selectionArgs, groupBy, having, orderBy)

        while (mCursor != null) {
            mCursor.moveToFirst();

        return mCursor;
        }
        
    }

I think my main problem is I can not find a clear example of the SQLite syntax for the selection, selectionArgs, etc. I simply don't know how to input those into the method. The database table has row id, title, protein, and notes for columns and I want the query to go over the entire database and pull every row that contains the user's string. Then I want to create a method that will take the cursor's information and display the title of row that held the user's string. Any help would be greatly appreciated. I am pretty new to this, but I really want to learn how it works.

[H3R3T1C]

Ill try to help you. Could you post the entire class for you database helper?

[Scorpiobuzz]

H3R3T1C, on 11 August 2010 - 05:17 PM, said:

Ill try to help you. Could you post the entire class for you database helper?

Here is the entire helper class - it is the google notepad example which I have been trying to understand and modify to suit my needs before I go and write a whole new database helper class of my own.

/*
 * Copyright (C) 2008 Google Inc.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package com.dinner.live;

import java.util.ArrayList;

import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.ArrayAdapter;
import android.widget.ListView;

/**
 * Simple notes database access helper class. Defines the basic CRUD operations
 * for the notepad example, and gives the ability to list all notes as well as
 * retrieve or modify a specific note.
 * 
 * This has been improved from the first version of this tutorial through the
 * addition of better error handling and also using returning a Cursor instead
 * of using a collection of inner classes (which is less scalable and not
 * recommended).
 */
public class NotesDbAdapter {

    public static final String KEY_TITLE = "title";
    public static final String KEY_BODY = "body";
    public static final String KEY_PROTEIN = "protein";   
    public static final String KEY_ROWID = "_id";

    private static final String TAG = "NotesDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    
    /**
     * Database creation sql statement
     */
    private static final String DATABASE_CREATE =
            "create table notes (_id integer primary key autoincrement, "
                    + "title text not null, protein text not null, body text not null);";

    private static final String DATABASE_NAME = "data";
    private static final String DATABASE_TABLE = "notes";
    private static final int DATABASE_VERSION = 2;

    private final Context mCtx;
    

    private static class DatabaseHelper extends SQLiteOpenHelper {

        

		DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL(DATABASE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS notes");
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public NotesDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public NotesDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
        mDbHelper.close();
    }


    /**
     * Create a new note using the title and body provided. If the note is
     * successfully created return the new rowId for that note, otherwise return
     * a -1 to indicate failure.
     * 
     * @param title the title of the note
     * @param body the body of the note
     * @param protein the protein of the meal
     * @return rowId or -1 if failed
     */
    public long createNote(String title, String protein, String body) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_TITLE, title);
        initialValues.put(KEY_PROTEIN, protein);
        initialValues.put(KEY_BODY, body);

        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    /**
     * Delete the note with the given rowId
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteNote(long rowId) {

        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all notes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllNotes() {

        return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_PROTEIN,
                KEY_BODY}, null, null, null, null, null);
    }
   
    /**
     * Return a Cursor positioned at the note that matches the given rowId
     * 
     * @param rowId id of note to retrieve
     * @return Cursor positioned to matching note, if found
     * @throws SQLException if note could not be found/retrieved
     */
    public Cursor fetchNote(long rowId) throws SQLException {

        Cursor mCursor =

                mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                        KEY_TITLE,KEY_PROTEIN, KEY_BODY}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }
    public Cursor findByTitle(String title) throws SQLException {
    	
    	Cursor mCursor = 
    			
    			mDb.query(DATABASE_TABLE, KEY_PROTEIN, selection, selectionArgs, groupBy, having, orderBy)
    }


    /**
     * Update the note using the details provided. The note to be updated is
     * specified using the rowId, and it is altered to use the title and body
     * values passed in
     * 
     * @param rowId id of note to update
     * @param title value to set note title to
     * @param body value to set note body to
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean updateNote(long rowId, String title, String protein, String body) {
        ContentValues args = new ContentValues();
        args.put(KEY_TITLE, title);
        args.put(KEY_PROTEIN, protein);
        args.put(KEY_BODY, body);

        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}

[H3R3T1C]

Ok this should work. If you need more clarification on what is going on or you need more help feel free to ask

public String getNoteByName(String name)
    {
    	// here were going to get all the data from the database
    	Cursor c =
    		mDb.query( DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_TITLE, KEY_BODY}, null, null,
                    null, null, null, null);
    	// move to the first row
    	c.moveToFirst();
    	do{
    		// get the data the for the KEY_TITLE column
    		String title = c.getString(c.getColumnIndexOrThrow(this.KEY_TITLE));
    		if(name.equalsIgnoreCase(title))// is this the title were looking for?
    		{
    			// get the data for the KEY_BODY column
    			return c.getString(c.getColumnIndexOrThrow(this.KEY_BODY));
    		}
    	}while(c.moveToNext());// move the the next row
    	// was not found 
        return null;
    }

This post has been edited by H3R3T1C: 11 August 2010 - 07:23 PM

[Scorpiobuzz]

H3R3T1C, on 11 August 2010 - 06:22 PM, said:

Ok this should work. If you need more clarification on what is going on or you need more help feel free to ask

public String getNoteByName(String name)
    {
    	// here were going to get all the data from the database
    	Cursor c =
    		mDb.query( DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_TITLE, KEY_BODY}, null, null,
                    null, null, null, null);
    	// move to the first row
    	c.moveToFirst();
    	do{
    		// get the data the for the KEY_TITLE column
    		String title = c.getString(c.getColumnIndexOrThrow(this.KEY_TITLE));
    		if(name.equalsIgnoreCase(title))// is this the title were looking for?
    		{
    			// get the data for the KEY_BODY column
    			return c.getString(c.getColumnIndexOrThrow(this.KEY_BODY));
    		}
    	}while(c.moveToNext());// move the the next row
    	// was not found 
        return null;
    }

Alright, so I am still a little confused. The method you wrote, returns a string not a cursor? I see that you load the cursor up with the database information, but it looks like it returns a string? I am sorry, I am so new to this and you have been so helpful, but I am trying to wrap my brain around all this new stuff, I am so used to C++ and C#, this is my first venture into Java and my very first venture into anything with Sql. So, if I use a method that returns a string, how do I get it to display the way the others do with a method that looks like this?

private void fillData() {
        //here is where I was originally modifying my public cursor method but switched in yours to see if it would work
        Cursor notesCursor = mDbHelper.getNoteByName(mSearchString);
        startManagingCursor(notesCursor);
        
        // Create an array to specify the fields we want to display in the list (only TITLE)
        String[] from = new String[]{NotesDbAdapter.KEY_TITLE};
        
        // and an array of the fields we want to bind those fields to (in this case just text1)
        int[] to = new int[]{R.id.text1};
        
        // Now create a simple cursor adapter and set it to display
        SimpleCursorAdapter notes = 
        	    new SimpleCursorAdapter(this, R.layout.notes_row, notesCursor, from, to);
        setListAdapter(notes);
        
    }

I think I understand the method that you wrote and how it works - which I can't thank you enough for, by the way. (Really, thank you soooo much.) But how do I manipulate a method that will display a cursor loaded with a string? Am I making any sense?

[H3R3T1C]

One way you can display the returned result is using Dialogs like this:

showNote(mDbHelper.getNoteByName("Note 4"));

public void showNote(String s)
    {
    	new AlertDialog.Builder(this)
    	.setTitle("Note Info")
    	.setMessage("Info for note: "+s)
    	.setPositiveButton("Done", null)
    	.show();
    }

Another way is to modify my method to return a List with all the note data that have the same title:

public List<String> getNotesByName(String name)
    {
    	List<String> data = new ArrayList<String>();
    	// here were going to get all the data from the database
    	Cursor c =
    		mDb.query( DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_TITLE, KEY_BODY}, null, null,
                    null, null, null, null);
    	// move to the first row
    	c.moveToFirst();
    	do{
    		// get the data that for the KEY_TITLE column
    		String title = c.getString(c.getColumnIndexOrThrow(this.KEY_TITLE));
    		if(name.equalsIgnoreCase(title))// Note: could also use title.contains(name)to 
                 //see get data from ones that contain the title in them. 
                 //IE if name = "world" and title = hello world then this would contain the title world.
    		{
    			// get the data for the KEY_BODY column
    			data.add(c.getString(c.getColumnIndexOrThrow(this.KEY_BODY)));
    		}
    	}while(c.moveToNext());// move the the next row
    	// was not found 
        return data;
    }

then you can use an array adapter to show the data

private void showData(String title) {
        List<String>data = mDbHelper.getNotesByName(title);
        setListAdapter(new ArrayAdapter(this,R.layout.notes_row,R.id.text1,data));
    }

This post has been edited by H3R3T1C: 11 August 2010 - 09:07 PM

[Scorpiobuzz]

H3R3T1C, on 11 August 2010 - 07:32 PM, said:

One way you can display the returned result is using Dialogs like this:

showNote(mDbHelper.getNoteByName("Note 4"));

public void showNote(String s)
    {
    	new AlertDialog.Builder(this)
    	.setTitle("Note Info")
    	.setMessage("Info for note: "+s)
    	.setPositiveButton("Done", null)
    	.show();
    }

Another way is to modify my method to return a List with all the note data that have the same title:

public List<String> getNotesByName(String name)
    {
    	List<String> data = new ArrayList<String>();
    	// here were going to get all the data from the database
    	Cursor c =
    		mDb.query( DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_TITLE, KEY_BODY}, null, null,
                    null, null, null, null);
    	// move to the first row
    	c.moveToFirst();
    	do{
    		// get the data that for the KEY_TITLE column
    		String title = c.getString(c.getColumnIndexOrThrow(this.KEY_TITLE));
    		if(name.equalsIgnoreCase(title))// Note: could also use title.contains(name)to 
                 //see get data from ones that contain the title in them. 
                 //IE if name = "world" and title = hello world then this would contain the title world.
    		{
    			// get the data for the KEY_BODY column
    			data.add(c.getString(c.getColumnIndexOrThrow(this.KEY_BODY)));
    		}
    	}while(c.moveToNext());// move the the next row
    	// was not found 
        return data;
    }

then you can use an array adapter to show the data

private void showData(String title) {
        List<String>data = mDbHelper.getNotesByName(title);
        setListAdapter(new ArrayAdapter(this,R.layout.notes_row,R.id.text1,data));
    }

That is so cool, I didn't even know that those functions existed! I am hitting the hay for tonight, but thanks again for all your help. I will try to get this thing up and running tomorrow morning. Thanks for all your time and help, I really appreciate it. How do I give the big kudos for your rep?