Reputation: 797

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

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

SbDatabase.java for creating a database
SbProvider.java for creating a provider service to access the song
Searchable.java for searching the for songs
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);
        }
    }

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);
    }
}

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);
          }

    }
}

}

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);
        }

    });
} } }

Upvotes: 1

Answers (3)

user7200062

Reputation:

Here is what worked for me:

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);
}

Upvotes: 0

user7117291

Reputation:

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);
}

Upvotes: 1

user3563954

Reputation: 37

In Song class add dowhile loop Then call moveToNext() to move next record in database .

do {

} while (cur.moveToNext());

Upvotes: 0

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

Answers (3)

Related Questions