title: How to navigate to next item in sqlite database table - android
body: <p>I am developing a song book app and I have the following files:</p>
  
  <ol>
  <li>SbDatabase.java for creating a database</li>
  <li>SbProvider.java for creating a provider service to access the song</li>
  <li>Searchable.java for searching the for songs</li>
  <li>Song.java for showing one song that is picked from a listview of results</li>
  </ol>
  
  <p>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</p>
  
  <p>1.SbDatabase.java</p>
  
  <pre><code>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&lt;String,String&gt; mColumnMap = buildColumnMap();
  
      public SbDatabase(Context context) {
          mDatabaseOpenHelper = new SongbookOpenHelper(context);
      }
  
      private static HashMap&lt;String,String&gt; buildColumnMap() {
          HashMap&lt;String,String&gt; map = new HashMap&lt;String,String&gt;();
          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 &lt;columns&gt; FROM &lt;table&gt; WHERE rowid = &lt;rowId&gt;
           */
      }
  
      /**
       * 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 &lt;columns&gt; FROM &lt;table&gt; WHERE &lt;KEY_WORD&gt; 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 &lt; 2) continue;
                      long id = addSong(strings[0].trim(), strings[1].trim());
                      if (id &lt; 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);
          }
      }
  </code></pre>
  
  <ol start="2">
  <li><p>SbProvider.java</p>
  
  <pre><code>public SbDatabase(Context context) {
  
      mDatabaseOpenHelper = new SongbookOpenHelper(context);
  }
  
  private static HashMap&lt;String,String&gt; buildColumnMap() {
      HashMap&lt;String,String&gt; map = new HashMap&lt;String,String&gt;();
      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 &lt;columns&gt; FROM &lt;table&gt; WHERE rowid = &lt;rowId&gt;
       */
  }
  
  /**
   * 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 &lt;columns&gt; FROM &lt;table&gt; WHERE &lt;KEY_WORD&gt; 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 &lt; 2) continue;
                  long id = addSong(strings[0].trim(), strings[1].trim());
                  if (id &lt; 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);
      }
  }
  </code></pre></li>
  <li><p>Searchable.java</p>
  
  <p>public class Searchable extends ActionBarActivity {<br>
      private TextView mTextView;
      private ListView mListView;
      LinearLayout MySong;</p>
  
  <pre><code>@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&lt;?&gt; 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&lt;?&gt; 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);
            }
  
      }
  }
  </code></pre>
  
  <p>}</p></li>
  <li><p>Song.java is where I need you help to fix a button to allow a user go to another song on the ImageButton Next</p>
  
  <p>public class Song extends ActionBarActivity {</p>
  
  <pre><code>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);
          }
  
      });
  } } }
  </code></pre></li>
  </ol>