In certain cases, it is recommended to store specific data in the database. For this, one has to follow the given steps:-
In this example, I am storing song details with user id so that if users login with different names then he can get only his stored songs.
Step 1: Create a getter-setter class say-"AllSongDetails" for song detail that hold userId, songId, songTitle, songDuration,songArtist.
Step 2: Create a class that will extend "SQLiteOpenHelper" , this class will contain sql queries and will perform different operations according to users' requirement.
public class DatabaseHandler extends SQLiteOpenHelper
{
//... Contacts Table Columns names
private static final String KEY_USERID = "userId";
private static final String KEY_SONGID = "songId";
private static final String KEY_SONGTITLE = "songTitle";
private static final String KEY_SONGDURATION = "songDuration";
private static final String KEY_SONGARTIST = "songArtist";
static final int DATABASE_VERSION = 1; //...Database Version private
private static final String DATABASE_NAME = "myDb"; //... Database Name
/**************************************Constructor***********************************/
DataBaseHandler(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/**************************************Creating Table***********************************/
@Override
public void onCreate(SQLiteDatabase db)
{
String CREATE_SONGDETAIL_TABLE = "CREATE TABLE " + TABLE_DETAILS + "("+ KEY_USERID + " TEXT," + KEY_SONGID + " TEXT," + KEY_SONGTITLE + " TEXT," + KEY_SONGARTIST + " TEXT,"+ KEY_SONGDURATION + " TEXT,"+")"; db.execSQL(CREATE_SONGDETAIL_TABLE);
}
/********************************Upgrading database *******************************/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
//... Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_DETAILS);
//... Create tables again
onCreate(db);
}
/**********************************Adding new detail of song ***************************/
public void Add_Details(String userId,String songId,String songTitle,String songArtist,String songDuration)
{
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_USERID, UserId);
values.put(KEY_SONGID, songId);
values.put(KEY_SONGTITLE, songTitle);
values.put(KEY_SONGARTIST, songArtist);
values.put(KEY_SONGDURATION,songDuration);
//... Inserting Row
db.insert(TABLE_DETAILS, null, values);
db.close();
//... Closing database connection
}
/***************************Getting All song list********************************/
public ArrayList<AllSongDetails> getSongList(String userId)
{
ArrayList<AllSongDetails> songList=new ArrayList<AllSongDetails>();
try
{
songDetailList.clear(); //... Select All Query
String selectQuery = "SELECT * FROM " + TABLE_DETAILS+ " WHERE "+ KEY_USERID+" LIKE "+userId;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst())
{
do
{
AllSongDetails songDetails = new AllSongDetails();
songDetails.setUserId(cursor.getString(0));
songDetails.setSongId(cursor.getString(1));
songDetails.setSongTitle(cursor.getString(2));
songDetails.setSongArtist(cursor.getString(3));
songDetails.setSongDuration(cursor.getString(4));
songList.add(songDetails); // ...Adding song detail to list
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return songList;
}
catch (Exception e)
{
Log.e("all_contact", "" + e); }
return songList;
}
/********************************Deleting single detail********************************/
public void Delete_Song(String userId,String songId)
{
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_DETAILS, KEY_SONGID + " = ?", new String[] {songId,userId}); db.close();
}
/****************************checking song exist****************************/
public boolean songExists(String uId,String songId)
{
SQLiteDatabase db = this.getReadableDatabase();
boolean exists=false;
Cursor cursor = db.query(TABLE_DETAILS, new String[]{KEY_USERID,KEY_SONGID, KEY_SONGTITLE, KEY_SONGARTIST, KEY_SONGDURATION}, KEY_USERID + "=?",new String[ {uId,songId}, null, null, null, null);
int limit=cursor.getCount();
if((limit > 0))
{
if(cursor.moveToFirst())
{
do{
if(songId.equalsIgnoreCase(cursor.getString(1)) &&
songTitle.equalsIgnoreCase(cursor.getString(2)))
{
exists=true; break;
}
} while(cursor.moveToNext());
}
}
else
{
exists=false;
}
cursor.close();
return exists;
}
}
/*****************************************************************************************/
Step 3: create the instance of database on the activity where you want to perform sql operations
private static DataBaseHandler dbHandler;
private ArrayList<AllSongDetails> songListFromDatabase;
dbHandler = new DataBaseHandler(this);
songListFromDatabase = new ArrayList<AllSongDetails>();
dbHandler.Add_Details(userId,songId,songTitle,songArtist,songDuration); //to add details of particular user from database
songListFromDatabase = dbHandler.getSongList(userId); //to get songlist from db
dbHandler.songExists(userId,songId) //to check if a particular song of particular user exist in db
dbHandler.Delete_Song(userId,songId); //to delete particular song of particular user exist from db
0 Comment(s)