Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Local Database in Android

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 148
    Comment on it

    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)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: