If My Table Has 4 Colums And I Want To Fetch The 3rd Column What Do I Do.

here is colums in my table Song song = new Song(); song.setMsong_id(cursor.getLong(0)); song.setmSong_name(cursor.getString(1)); song.setmSong_year(cursor.getLong(2)

Solution 1:

The important aspect is not the table or tables but the query itself as that determines the column order.

For example if the query were based upon SELECT * FROM your_table (and the columns in the table were defined as id, songname, songyear, songpath) then the columnd is the cursor would be as per the definition.

However, if you did SELECT songname, songpath, songid, songyear FROM your_table;

The the order would be as per the SELECT statement i.e. songname (0), songpath (1), songid (2), songyear (3).

However, that is one of the issues with using offsets you are tied to the order as per the SELECT.

Now if you used the Cursor's getColumnIndex method then that returns the column offset according to it's name.

So cursor.getString(cursor.getColumnIndex("songpath")); would get the songpath column irrespective of it's offset/position in the Cursor (IF THE CURSOR INCLUDES THAT COLUMN).

Recalling your previous question you basically had SELECT songpath FROM your_table, As such there is just a single column in the resultant Cursor so you could only use get either :-

cursor.getString(0); 

or :-

cursor.getString(cursor.getColumnIndex("songpath"));

The latter is the recommended method (BUT ideally have the column names defined as constants)

Matters can get more complicated though, for example consider

SELECT songpath||songname AS myconfusingcolumn FROM yourtable;

This would return a single column named myconfusingcolumn that consists of the songpath concatenated with the songname. That is the AS keyword is followed by an alias for the column (without the AS the column name would be even more confusing/difficult as it would be songpath||songname) (this example is relatively simple).

Another thing to be wary of is, are ambiguous columns (duplicated column names) for example, if you had two tables song and artist and song the the additional column artist_id so you have :-

The song table with columns id, songname, songyear, songpath, artist_id

The artists table with columns id and artist_name

and you then used

SELECT * FROM song JOIN artist ON song.artist_id = artist.id;

• Note that as the id column of the artist table, if used in the statement, it has to be prefixed with the respective table name otherwise an AMBIGUOUS column error would be raised i.e. the SQL parser wouldn't know which id column you mean.

Furthermore you would end up with a Cursor having columns :-

id, songname, songyear, songpath, artist_id, id, artist_name

csr.getLong(csr.getColumnIndex("id")); could getconfused (I believe it actually gets the last AMBIGUOUS column)

long songid = csr.getLong(0); would get the id column from the song table.
long artistid = csr.getLong(5); would get the id column from the artist table.
long artistid = csr.getLong(4); would also get the artist_id same as (5).

To recap/summarise :-

The columns, order and name, in a Cursor are wholly dependant upon the SELECT query. They will have an offset and name as per the query. When accessing the Cursor the underlying table name(s) are not usable just the column names or offsets.

It is more flexible to access columns by their names rather than by their offstes. That is make use of the Cursor's getColumnIndex method as it negates the need to calculate offsets and more especially missing re-calculation should a query be changed.

Using CONSTANTS for column names will likely result in reduced issues such as typing errors.

Additional

Using Toast.makeText(this, mListSongs+"", Toast.LENGTH_SHORT).show();

Will get the unusual result [{}] because mListSongs is the entire container for the Songs. You need to loop though each element and then get the properties/values for each member/variable from the element(Song object).