Jackson Siro Jackson Siro - 18 days ago 5
Android Question

How to navigate to next item in sqlite database table - android

I am developing a song book app and I have the following files:


  1. SbDatabase.java for creating a database

  2. SbProvider.java for creating a provider service to access the song

  3. Searchable.java for searching the for songs

  4. Song.java for showing one song that is picked from a listview of results



I need help to create an action that would allow a user switch to another song either ahead of before the current song being viewed. Here are my codes

1.SbDatabase.java

public class SbDatabase {
private static final String TAG = "SbDatabase";
public static final String KEY_WORD = SearchManager.SUGGEST_COLUMN_TEXT_1;
public static final String KEY_SONGCONT = SearchManager.SUGGEST_COLUMN_TEXT_2;
private static final String DATABASE_NAME = "songbook";
private static final String FTS_VIRTUAL_TABLE = "FTSsongbook";
private static final int DATABASE_VERSION = 2;

private final SongbookOpenHelper mDatabaseOpenHelper;
private static final HashMap<String,String> mColumnMap = buildColumnMap();

public SbDatabase(Context context) {
mDatabaseOpenHelper = new SongbookOpenHelper(context);
}

private static HashMap<String,String> buildColumnMap() {
HashMap<String,String> map = new HashMap<String,String>();
map.put(KEY_WORD, KEY_WORD);
map.put(KEY_SONGCONT, KEY_SONGCONT);
map.put(BaseColumns._ID, "rowid AS " +
BaseColumns._ID);
map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " +
SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS " +
SearchManager.SUGGEST_COLUMN_SHORTCUT_ID);
return map;
}

/**
* Returns a Cursor positioned at the song specified by rowId
*
* @param rowId id of song to retrieve
* @param columns The columns to include, if null then all are included
* @return Cursor positioned to matching song, or null if not found.
*/
public Cursor getSong(String rowId, String[] columns) {
String selection = "rowid = ?";
String[] selectionArgs = new String[] {rowId};

return query(selection, selectionArgs, columns);

/* This builds a query that looks like:
* SELECT <columns> FROM <table> WHERE rowid = <rowId>
*/
}

/**
* Returns a Cursor over all songs that match the given query
*
* @param query The string to search for
* @param columns The columns to include, if null then all are included
* @return Cursor over all songs that match, or null if none found.
*/
public Cursor getSongMatches(String query, String[] columns) {
String selection = KEY_WORD + " MATCH ?";
String[] selectionArgs = new String[] {query+"*"};

return query(selection, selectionArgs, columns);

/* This builds a query that looks like:
* SELECT <columns> FROM <table> WHERE <KEY_WORD> MATCH 'query*'
* which is an FTS3 search for the query text (plus a wildcard) inside the song column.
*
* - "rowid" is the unique id for all rows but we need this value for the "_id" column in
* order for the Adapters to work, so the columns need to make "_id" an alias for "rowid"
* - "rowid" also needs to be used by the SUGGEST_COLUMN_INTENT_DATA alias in order
* for suggestions to carry the proper intent data.
* These aliases are defined in the SbProvider when queries are made.
* - This can be revised to also search the songcont text with FTS3 by changing
* the selection clause to use FTS_VIRTUAL_TABLE instead of KEY_WORD (to search across
* the entire table, but sorting the relevance could be difficult.
*/
}

/**
* Performs a database query.
* @param selection The selection clause
* @param selectionArgs Selection arguments for "?" components in the selection
* @param columns The columns to return
* @return A Cursor over all rows matching the query
*/
private Cursor query(String selection, String[] selectionArgs, String[] columns) {
/* The SQLiteBuilder provides a map for all possible columns requested to
* actual columns in the database, creating a simple column alias mechanism
* by which the ContentProvider does not need to know the real column names
*/
SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
builder.setTables(FTS_VIRTUAL_TABLE);
builder.setProjectionMap(mColumnMap);

Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),
columns, selection, selectionArgs, null, null, null);

if (cursor == null) {
return null;
} else if (!cursor.moveToFirst()) {
cursor.close();
return null;
}
return cursor;
}


/**
* This creates/opens the database.
*/
private static class SongbookOpenHelper extends SQLiteOpenHelper {

private final Context mHelperContext;
private SQLiteDatabase mDatabase;

/* Note that FTS3 does not support column constraints and thus, you cannot
* declare a primary key. However, "rowid" is automatically used as a unique
* identifier, so when making requests, we will use "_id" as an alias for "rowid"
*/
private static final String FTS_TABLE_CREATE =
"CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE +
" USING fts3 (" +
KEY_WORD + ", " +
KEY_SONGCONT + ");";

SongbookOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
mHelperContext = context;
}

@Override
public void onCreate(SQLiteDatabase db) {
mDatabase = db;
mDatabase.execSQL(FTS_TABLE_CREATE);
loadSongbook();
}

/**
* Starts a thread to load the database table with songs
*/
private void loadSongbook() {
new Thread(new Runnable() {
public void run() {
try {
loadSongs();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}).start();
}

private void loadSongs() throws IOException {
Log.d(TAG, "Loading songs...");
final Resources resources = mHelperContext.getResources();
InputStream inputStream = resources.openRawResource(R.raw.songbook);
BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));

try {
String line;
String thesong;
while ((line = reader.readLine()) != null) {
thesong = line.replace("$", System.getProperty("line.separator"));
String[] strings = TextUtils.split(thesong, "%");
if (strings.length < 2) continue;
long id = addSong(strings[0].trim(), strings[1].trim());
if (id < 0) {
Log.e(TAG, "unable to add song: " + strings[0].trim());
}
}
} finally {
reader.close();
}
Log.d(TAG, "DONE loading songs.");
}

public long addSong(String song, String songcont) {
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_WORD, song);
initialValues.put(KEY_SONGCONT, songcont);

return mDatabase.insert(FTS_VIRTUAL_TABLE, null, initialValues);
}

@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 " + FTS_VIRTUAL_TABLE);
onCreate(db);
}
}



  1. SbProvider.java

    public SbDatabase(Context context) {

    mDatabaseOpenHelper = new SongbookOpenHelper(context);
    }

    private static HashMap<String,String> buildColumnMap() {
    HashMap<String,String> map = new HashMap<String,String>();
    map.put(KEY_WORD, KEY_WORD);
    map.put(KEY_SONGCONT, KEY_SONGCONT);
    map.put(BaseColumns._ID, "rowid AS " +
    BaseColumns._ID);
    map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " +
    SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
    map.put(SearchManager.SUGGEST_COLUMN_SHORTCUT_ID, "rowid AS " +
    SearchManager.SUGGEST_COLUMN_SHORTCUT_ID);
    return map;
    }

    /**
    * Returns a Cursor positioned at the song specified by rowId
    *
    * @param rowId id of song to retrieve
    * @param columns The columns to include, if null then all are included
    * @return Cursor positioned to matching song, or null if not found.
    */
    public Cursor getSong(String rowId, String[] columns) {
    String selection = "rowid = ?";
    String[] selectionArgs = new String[] {rowId};

    return query(selection, selectionArgs, columns);

    /* This builds a query that looks like:
    * SELECT <columns> FROM <table> WHERE rowid = <rowId>
    */
    }

    /**
    * Returns a Cursor over all songs that match the given query
    *
    * @param query The string to search for
    * @param columns The columns to include, if null then all are included
    * @return Cursor over all songs that match, or null if none found.
    */
    public Cursor getSongMatches(String query, String[] columns) {
    String selection = KEY_WORD + " MATCH ?";
    String[] selectionArgs = new String[] {query+"*"};

    return query(selection, selectionArgs, columns);

    /* This builds a query that looks like:
    * SELECT <columns> FROM <table> WHERE <KEY_WORD> MATCH 'query*'
    * which is an FTS3 search for the query text (plus a wildcard) inside the song column.
    *
    * - "rowid" is the unique id for all rows but we need this value for the "_id" column in
    * order for the Adapters to work, so the columns need to make "_id" an alias for "rowid"
    * - "rowid" also needs to be used by the SUGGEST_COLUMN_INTENT_DATA alias in order
    * for suggestions to carry the proper intent data.
    * These aliases are defined in the SbProvider when queries are made.
    * - This can be revised to also search the songcont text with FTS3 by changing
    * the selection clause to use FTS_VIRTUAL_TABLE instead of KEY_WORD (to search across
    * the entire table, but sorting the relevance could be difficult.
    */
    }

    /**
    * Performs a database query.
    * @param selection The selection clause
    * @param selectionArgs Selection arguments for "?" components in the selection
    * @param columns The columns to return
    * @return A Cursor over all rows matching the query
    */
    private Cursor query(String selection, String[] selectionArgs, String[] columns) {
    /* The SQLiteBuilder provides a map for all possible columns requested to
    * actual columns in the database, creating a simple column alias mechanism
    * by which the ContentProvider does not need to know the real column names
    */
    SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
    builder.setTables(FTS_VIRTUAL_TABLE);
    builder.setProjectionMap(mColumnMap);

    Cursor cursor = builder.query(mDatabaseOpenHelper.getReadableDatabase(),
    columns, selection, selectionArgs, null, null, null);

    if (cursor == null) {
    return null;
    } else if (!cursor.moveToFirst()) {
    cursor.close();
    return null;
    }
    return cursor;
    }


    /**
    * This creates/opens the database.
    */
    private static class SongbookOpenHelper extends SQLiteOpenHelper {

    private final Context mHelperContext;
    private SQLiteDatabase mDatabase;

    /* Note that FTS3 does not support column constraints and thus, you cannot
    * declare a primary key. However, "rowid" is automatically used as a unique
    * identifier, so when making requests, we will use "_id" as an alias for "rowid"
    */
    private static final String FTS_TABLE_CREATE =
    "CREATE VIRTUAL TABLE " + FTS_VIRTUAL_TABLE +
    " USING fts3 (" +
    KEY_WORD + ", " +
    KEY_SONGCONT + ");";

    SongbookOpenHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    mHelperContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    mDatabase = db;
    mDatabase.execSQL(FTS_TABLE_CREATE);
    loadSongbook();
    }

    /**
    * Starts a thread to load the database table with songs
    */
    private void loadSongbook() {
    new Thread(new Runnable() {
    public void run() {
    try {
    loadSongs();
    } catch (IOException e) {
    throw new RuntimeException(e);
    }
    }
    }).start();
    }

    private void loadSongs() throws IOException {
    Log.d(TAG, "Loading songs...");
    final Resources resources = mHelperContext.getResources();
    InputStream inputStream = resources.openRawResource(R.raw.songbook);
    BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));

    try {
    String line;
    String thesong;
    while ((line = reader.readLine()) != null) {
    thesong = line.replace("$", System.getProperty("line.separator"));
    String[] strings = TextUtils.split(thesong, "%");
    if (strings.length < 2) continue;
    long id = addSong(strings[0].trim(), strings[1].trim());
    if (id < 0) {
    Log.e(TAG, "unable to add song: " + strings[0].trim());
    }
    }
    } finally {
    reader.close();
    }
    Log.d(TAG, "DONE loading songs.");
    }

    public long addSong(String song, String songcont) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_WORD, song);
    initialValues.put(KEY_SONGCONT, songcont);

    return mDatabase.insert(FTS_VIRTUAL_TABLE, null, initialValues);
    }

    @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 " + FTS_VIRTUAL_TABLE);
    onCreate(db);
    }
    }

  2. Searchable.java

    public class Searchable extends ActionBarActivity {

    private TextView mTextView;
    private ListView mListView;
    LinearLayout MySong;

    @SuppressWarnings("deprecation")
    @Override
    public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.search);

    mTextView = (TextView) findViewById(R.id.text);
    mListView = (ListView) findViewById(R.id.list);

    handleIntent(getIntent());

    Cursor cursor = managedQuery(SbProvider.CONTENT_URI, null, null,
    new String[] {"Lotv"}, null);

    String[] from = new String[] { SbDatabase.KEY_WORD,
    SbDatabase.KEY_SONGCONT };

    int[] to = new int[] { R.id.song, R.id.songcont };

    SimpleCursorAdapter songs = new SimpleCursorAdapter(this,
    R.layout.result, cursor, from, to);
    mListView.setAdapter(songs);

    mListView.setOnItemClickListener(new OnItemClickListener() {

    @Override
    public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
    Intent songIntent = new Intent(getApplicationContext(), Song.class);
    Uri data = Uri.withAppendedPath(SbProvider.CONTENT_URI,
    String.valueOf(id));
    songIntent.setData(data);
    startActivity(songIntent);
    }
    });

    mTextView.setText("568 Songs of Worship!");

    }

    @Override
    protected void onNewIntent(Intent intent) {
    handleIntent(intent);
    }

    private void handleIntent(Intent intent) {
    if (Intent.ACTION_VIEW.equals(intent.getAction())) {
    Intent songIntent = new Intent(this, Song.class);
    songIntent.setData(intent.getData());
    startActivity(songIntent);
    } else if (Intent.ACTION_SEARCH.equals(intent.getAction())) {
    String query = intent.getStringExtra(SearchManager.QUERY);
    showResults(query);
    }
    }

    private void showResults(String query) {

    Cursor cursor = managedQuery(SbProvider.CONTENT_URI, null, null,
    new String[] {query}, null);

    String[] from = new String[] { SbDatabase.KEY_WORD,
    SbDatabase.KEY_SONGCONT };

    int[] to = new int[] { R.id.song,
    R.id.songcont };

    SimpleCursorAdapter songs = new SimpleCursorAdapter(this,
    R.layout.result, cursor, from, to);
    mListView.setAdapter(songs);

    mListView.setOnItemClickListener(new OnItemClickListener() {

    @Override
    public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
    Intent songIntent = new Intent(getApplicationContext(), Song.class);
    Uri data = Uri.withAppendedPath(SbProvider.CONTENT_URI,
    String.valueOf(id));
    songIntent.setData(data);
    startActivity(songIntent);
    }
    });

    if (cursor == null) {
    mTextView.setText(getString(R.string.no_songs, new Object[] {query}));
    } else {
    int count = cursor.getCount();
    String countString = getResources().getQuantityString(R.plurals.search_results,
    count, new Object[] {count, query});
    mTextView.setText(countString);
    }

    }
    }


    }

  3. Song.java is where I need you help to fix a button to allow a user go to another song on the ImageButton Next

    public class Song extends ActionBarActivity {

    RelativeLayout MySong;
    TextView songcont;
    private ImageButton Previous;
    private ImageButton Next;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.song);

    Uri uri = getIntent().getData();
    Cursor cursor = managedQuery(uri, null, null, null, null);

    if (cursor == null) {
    finish();
    } else {
    cursor.moveToFirst();

    TextView songcont = (TextView) findViewById(R.id.songcont);
    songcont.setMovementMethod(new ScrollingMovementMethod());
    int wIndex = cursor.getColumnIndexOrThrow(SbDatabase.KEY_WORD);
    int dIndex = cursor.getColumnIndexOrThrow(SbDatabase.KEY_SONGCONT);
    setTitle(cursor.getString(wIndex));
    songcont.setText(cursor.getString(dIndex));
    Previous = (ImageButton)findViewById (R.id.imageButton1 );
    Next = (ImageButton)findViewById (R.id.imageButton2 );
    Next.setOnClickListener(new OnClickListener() {

    public void onClick(View arg0) {

    Intent songIntent = new Intent(getApplicationContext(), Song.class);
    //Uri data = Uri.withAppendedPath(SbProvider.CONTENT_URI, String.valueOf(id));
    Uri data = Uri.withAppendedPath(SbProvider.CONTENT_URI, String.valueOf(id));

    songIntent.setData(data);
    startActivity(songIntent);
    }

    });
    } } }


Answer

This code can work for you

public class Song extends ActionBarActivity {
    TextView songcont;
    private SbDatabase mSongbook;


@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.song);

    Uri uri = getIntent().getData();
    Cursor cursor = managedQuery(uri, null, null, null, null);

    cursor.moveToFirst();        
    TextView songcont = (TextView) findViewById(R.id.songcont);
    songcont.setMovementMethod(new ScrollingMovementMethod());

    int wIndex = cursor.getColumnIndexOrThrow(SbDatabase.KEY_WORD);
    int dIndex = cursor.getColumnIndexOrThrow(SbDatabase.KEY_SONGCONT);

    String thetitle = (cursor.getString(wIndex));
    songcont.setText(cursor.getString(dIndex));
    setTitle(thetitle.replace("#", ""));

    Previous = (ImageButton)findViewById (R.id.imageButton1 );
    Previous.setOnClickListener(new OnClickListener() {

        public void onClick(View arg0) {
            Uri uri = getIntent().getData();
          Cursor cursor = managedQuery(uri, null, null, null, null);
          cursor.moveToFirst();                     
          int wIndex = cursor.getColumnIndexOrThrow(SbDatabase.KEY_WORD);               

          String Title = (cursor.getString(wIndex));        
          String[] strings = TextUtils.split(Title, "#");
          String MyTitle = strings[0].trim();
          int id = Integer.parseInt(MyTitle);

          Uri data = Uri.withAppendedPath(SbProvider.CONTENT_URI, String.valueOf(id-1));                
          Intent songIntent = new Intent(getApplicationContext(), Song.class);
          songIntent.setData(data);
          startActivity(songIntent);

        }

    });

    Next = (ImageButton)findViewById (R.id.imageButton2 ); 
    Next.setOnClickListener(new OnClickListener() {

        public void onClick(View arg0) {
            Uri uri = getIntent().getData();
          Cursor cursor = managedQuery(uri, null, null, null, null);
          cursor.moveToFirst();                     
          int wIndex = cursor.getColumnIndexOrThrow(SbDatabase.KEY_WORD);               

          String Title = (cursor.getString(wIndex));        
          String[] strings = TextUtils.split(Title, "#");
          String MyTitle = strings[0].trim();
          int id = Integer.parseInt(MyTitle);

          Uri data = Uri.withAppendedPath(SbProvider.CONTENT_URI, String.valueOf(id+1));                
          Intent songIntent = new Intent(getApplicationContext(), Song.class);
          songIntent.setData(data);
          startActivity(songIntent);

        }

    });

    detector = new SimpleGestureFilter(this,this);
}
Comments