Android and SQLite

Some application requiring a database, for Android we're using SQLite as database. so here I want to sharing about using SQLite database.

Here's the sampe code call DatabaseHandler.java
in this code, I have 2 table call rs_list and rs_admin and database name is rsmanager. DatabaseHandler is use to handle all query.

 package com.ademahendra.database;  
 import java.util.ArrayList;  
 import java.util.List;  
 import android.content.ContentValues;  
 import android.content.Context;  
 import android.database.Cursor;  
 import android.database.sqlite.SQLiteDatabase;  
 import android.database.sqlite.SQLiteOpenHelper;  
 public class DatabaseHandler extends SQLiteOpenHelper {  
   private static final int DATABASE_VERSION = 1;  
   private static final String DATABASE_NAME = "rsmanager";  
      public static final String KEY_ID = "_id";  
      public static final String KEY_NAME = "name";  
      public static final String KEY_EMAIL = "email";  
      public static final String KEY_ADDRESS = "address";  
      public static final String KEY_PHONE = "phone_number";  
      public static final String KEY_IMAGE = "image";  
      public static final String KEY_GLAT = "glat";  
      public static final String KEY_GLON = "glon";  
      public static final String KEY_TYPERS = "typers";  
      public static final String KEY_WEB = "web";  
      public static final String KEY_STATUS = "status";  
      private static final String DB_TABLE = "rs_list";    
      private static final String DB_TABLEADMIN = "rs_admin";  
      public static final String KEY_AID = "id";  
      public static final String KEY_ANAME = "name";  
      public static final String KEY_AUSER = "username";  
      public static final String KEY_APASSWORD = "password";  
   public DatabaseHandler(Context context) {  
     super(context, DATABASE_NAME, null, DATABASE_VERSION);  
   }  
   @Override  
   public void onCreate(SQLiteDatabase db) {  
        db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);  
     String CREATE_CONTACTS_TABLE =   
               "create table rs_list "  
                + "(_id integer primary key autoincrement, "  
                + "name varchar(200) not null,"  
                + "phone_number varchar(200),"  
                + "address varchar(200),"                 
                + "email varchar(200),"        
                + "image varchar(200),"  
                + "glat varchar(15),"  
                + "glon varchar(15),"  
                + "typers varchar(100),"  
                + "web varchar(225),"  
                + "status varchar(11) DEFAULT '0');";  
     db.execSQL(CREATE_CONTACTS_TABLE);  
     db.execSQL("DROP TABLE IF EXISTS " + DB_TABLEADMIN);  
     String CREATE_CONTACTS_TABLEADMIN =   
               "create table rs_admin "  
                + "(_id integer primary key autoincrement, "  
                + "name varchar(200) not null,"  
                + "username varchar(200),"  
                + "password varchar(200));";  
     db.execSQL(CREATE_CONTACTS_TABLEADMIN);  
     db.isOpen();  
   }  
   @Override  
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
     db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);  
     db.execSQL("DROP TABLE IF EXISTS " + DB_TABLEADMIN);  
     onCreate(db);  
   }  
      public void addRSList(RSList rslist) {  
     SQLiteDatabase db = this.getWritableDatabase();  
     ContentValues values = new ContentValues();  
     values.put(KEY_NAME, rslist.getName());      
     values.put(KEY_PHONE, rslist.getPhoneNumber());  
     values.put(KEY_EMAIL, rslist.getEmail());  
     values.put(KEY_ADDRESS, rslist.getAddress());  
     values.put(KEY_IMAGE, rslist.getImage());  
     values.put(KEY_GLAT, rslist.getGlat());  
     values.put(KEY_GLON, rslist.getGlon());  
     values.put(KEY_TYPERS, rslist.getTypers());  
     values.put(KEY_WEB, rslist.getWeb());  
     values.put(KEY_STATUS, rslist.getStatus());  
     db.insert(DB_TABLE, null, values);  
     db.close();   
   }  
   public void addRSAmin(Admin admin) {  
     SQLiteDatabase db = this.getWritableDatabase();  
     ContentValues values = new ContentValues();  
     values.put(KEY_ANAME, admin.getName());       
     values.put(KEY_AUSER, admin.getUsername());   
     values.put(KEY_APASSWORD, admin.getPassword());   
     db.insert(DB_TABLEADMIN, null, values);  
     db.close();  
   }  
   RSList getRSList(int id) {  
     SQLiteDatabase db = this.getReadableDatabase();  
     Cursor cursor = db.query(DB_TABLE, new String[] { KEY_ID,  
         KEY_NAME, KEY_PHONE, KEY_EMAIL, KEY_ADDRESS, KEY_IMAGE, KEY_GLAT, KEY_GLON, KEY_TYPERS, KEY_WEB, KEY_STATUS }, KEY_ID + "=?",  
         new String[] { String.valueOf(id) }, null, null, null, null);  
     if (cursor != null)  
       cursor.moveToFirst();  
     RSList rslist = new RSList(Integer.parseInt(cursor.getString(0)),  
         cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5)  
         , cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9), cursor.getString(10));  
     return rslist;  
   }  
   Admin getAdmin(int id) {  
     SQLiteDatabase db = this.getReadableDatabase();  
     Cursor cursor = db.query(DB_TABLEADMIN, new String[] { KEY_AID,  
         KEY_ANAME, KEY_AUSER, KEY_APASSWORD}, KEY_AID + "=?",  
         new String[] { String.valueOf(id) }, null, null, null, null);  
     if (cursor != null)  
       cursor.moveToFirst();  
     Admin admin = new Admin(Integer.parseInt(cursor.getString(0)),  
         cursor.getString(1), cursor.getString(2), cursor.getString(3));  
     return admin;  
   }   
   public List<RSList> getAllRSLists() {  
     List<RSList> rslistList = new ArrayList<RSList>();  
     String selectQuery = "SELECT * FROM " + DB_TABLE;  
     SQLiteDatabase db = this.getWritableDatabase();  
     Cursor cursor = db.rawQuery(selectQuery, null);  
     if (cursor.moveToFirst()) {  
       do {  
         RSList rslist = new RSList();  
         rslist.setID(Integer.parseInt(cursor.getString(0)));  
         rslist.setName(cursor.getString(1));  
         rslist.setPhoneNumber(cursor.getString(2));  
         rslist.setEmail(cursor.getString(3));  
         rslist.setAddress(cursor.getString(4));  
         rslist.setImage(cursor.getString(5));  
         rslist.setGlat(cursor.getString(6));  
         rslist.setGlon(cursor.getString(7));  
         rslist.setTypers(cursor.getString(8));  
         rslist.setWeb(cursor.getString(9));  
         rslist.setStatus(cursor.getString(10));  
         rslistList.add(rslist);  
       } while (cursor.moveToNext());  
     }  
     return rslistList;  
   }  
   public List<RSList> getAllRSListsByType(String keyword) {  
     List<RSList> rslistList = new ArrayList<RSList>();  
     String selectQuery = "SELECT * FROM " + DB_TABLE+" WHERE "+KEY_TYPERS+" = '"+keyword+"'";  
     SQLiteDatabase db = this.getWritableDatabase();  
     Cursor cursor = db.rawQuery(selectQuery, null);  
     if (cursor.moveToFirst()) {  
       do {  
         RSList rslist = new RSList();  
         rslist.setID(Integer.parseInt(cursor.getString(0)));  
         rslist.setName(cursor.getString(1));  
         rslist.setPhoneNumber(cursor.getString(2));  
         rslist.setEmail(cursor.getString(3));  
         rslist.setAddress(cursor.getString(4));  
         rslist.setImage(cursor.getString(5));  
         rslist.setGlat(cursor.getString(6));  
         rslist.setGlon(cursor.getString(7));  
         rslist.setTypers(cursor.getString(8));  
         rslist.setWeb(cursor.getString(9));  
         rslist.setStatus(cursor.getString(10));  
         rslistList.add(rslist);  
       } while (cursor.moveToNext());  
     }  
     return rslistList;  
   }    
   public List<RSList> getAllRSListsByName(String keyword) {  
     List<RSList> rslistList = new ArrayList<RSList>();  
     String selectQuery = "SELECT * FROM " + DB_TABLE+" WHERE "+KEY_NAME+" = '"+keyword+"'";  
     SQLiteDatabase db = this.getWritableDatabase();  
     Cursor cursor = db.rawQuery(selectQuery, null);  
     if (cursor.moveToFirst()) {  
       do {  
         RSList rslist = new RSList();  
         rslist.setID(Integer.parseInt(cursor.getString(0)));  
         rslist.setName(cursor.getString(1));  
         rslist.setPhoneNumber(cursor.getString(2));  
         rslist.setEmail(cursor.getString(3));  
         rslist.setAddress(cursor.getString(4));  
         rslist.setImage(cursor.getString(5));  
         rslist.setGlat(cursor.getString(6));  
         rslist.setGlon(cursor.getString(7));  
         rslist.setTypers(cursor.getString(8));  
         rslist.setWeb(cursor.getString(9));  
         rslist.setStatus(cursor.getString(10));  
         rslistList.add(rslist);  
       } while (cursor.moveToNext());  
     }  
     return rslistList;  
   }  
   public List<Admin> getAllAdmin(String keyword) {  
     List<Admin> adminList = new ArrayList<Admin>();  
     String selectQuery = "SELECT * FROM " + DB_TABLEADMIN+" WHERE "+KEY_ANAME+" = '"+keyword+"'";  
     SQLiteDatabase db = this.getWritableDatabase();  
     Cursor cursor = db.rawQuery(selectQuery, null);  
     if (cursor.moveToFirst()) {  
       do {  
         Admin admin = new Admin();  
         admin.setID(Integer.parseInt(cursor.getString(0)));  
         admin.setName(cursor.getString(1));  
         admin.setUsername(cursor.getString(2));  
         admin.setPassword(cursor.getString(3));  
         adminList.add(admin);  
       } while (cursor.moveToNext());  
     }  
     return adminList;  
   }   
   public List<Admin> getAllAdminUser(String keyword) {  
     List<Admin> adminList = new ArrayList<Admin>();  
     String selectQuery = "SELECT * FROM " + DB_TABLEADMIN+" WHERE "+KEY_AUSER+" = '"+keyword+"'";  
     SQLiteDatabase db = this.getWritableDatabase();  
     Cursor cursor = db.rawQuery(selectQuery, null);  
     if (cursor.moveToFirst()) {  
       do {  
         Admin admin = new Admin();  
         admin.setID(Integer.parseInt(cursor.getString(0)));  
         admin.setName(cursor.getString(1));  
         admin.setUsername(cursor.getString(2));  
         admin.setPassword(cursor.getString(3));  
         adminList.add(admin);  
       } while (cursor.moveToNext());  
     }  
     return adminList;  
   }    
   public Cursor getRSListsByType(String keyword) {  
     String selectQuery = "SELECT * FROM " + DB_TABLE+" WHERE "+KEY_TYPERS+" = '"+keyword+"'";  
     SQLiteDatabase db = this.getWritableDatabase();  
     Cursor cursor = db.rawQuery(selectQuery, null);  
     return cursor;  
   }  
   public int updateRSList(RSList rslist) {  
     SQLiteDatabase db = this.getWritableDatabase();  
     ContentValues values = new ContentValues();  
     values.put(KEY_NAME, rslist.getName());  
     values.put(KEY_PHONE, rslist.getPhoneNumber());  
     values.put(KEY_EMAIL, rslist.getEmail());  
     values.put(KEY_ADDRESS, rslist.getAddress());  
     values.put(KEY_IMAGE, rslist.getImage());  
     values.put(KEY_GLAT, rslist.getGlat());  
     values.put(KEY_GLON, rslist.getGlon());  
     values.put(KEY_TYPERS, rslist.getTypers());  
     values.put(KEY_WEB, rslist.getWeb());  
     values.put(KEY_STATUS, rslist.getStatus());  
     return db.update(DB_TABLE, values, KEY_ID + " = ?",  
         new String[] { String.valueOf(rslist.getID()) });  
   }  
   public int updateAdmin(Admin admin) {  
     SQLiteDatabase db = this.getWritableDatabase();  
     ContentValues values = new ContentValues();  
     values.put(KEY_ANAME, admin.getName());  
     values.put(KEY_AUSER, admin.getUsername());  
     values.put(KEY_APASSWORD, admin.getPassword());  
     return db.update(DB_TABLEADMIN, values, KEY_AID + " = ?",  
         new String[] { String.valueOf(admin.getID()) });  
   }    
   public void deleteRSList(RSList rslist) {  
     SQLiteDatabase db = this.getWritableDatabase();  
     db.delete(DB_TABLE, KEY_ID + " = ?",  
         new String[] { String.valueOf(rslist.getID()) });  
     db.close();  
   }  
   public int getRSListsCount() {  
     String countQuery = "SELECT * FROM " + DB_TABLE;  
     SQLiteDatabase db = this.getReadableDatabase();  
     Cursor cursor = db.rawQuery(countQuery, null);  
     cursor.close();  
     return cursor.getCount();  
   }  
   public int getRSListTypeCount(String keyword) {  
     String countQuery = "SELECT * FROM " + DB_TABLE+" WHERE "+KEY_TYPERS+" = '"+keyword+"'";  
     SQLiteDatabase db = this.getReadableDatabase();  
     Cursor cursor = db.rawQuery(countQuery, null);  
     cursor.close();  
     return cursor.getCount();  
   }  
   public int loginAdmin(String username, String password) {  
     String countQuery = "SELECT * FROM " + DB_TABLEADMIN+" WHERE "+KEY_AUSER+" = '"+username+"' AND "+KEY_APASSWORD+  
               "= '"+password+"'";  
     SQLiteDatabase db = this.getReadableDatabase();  
     Cursor cursor = db.rawQuery(countQuery, null);  
     cursor.close();  
     return cursor.getCount();  
   }   
   public List<Admin> getLoginDetail(String username, String password) {  
     List<Admin> adminList = new ArrayList<Admin>();  
     String selectQuery = "SELECT * FROM " + DB_TABLEADMIN+" WHERE "+KEY_AUSER+" = '"+username+"' AND "+KEY_APASSWORD+  
               "= '"+password+"'";  
     SQLiteDatabase db = this.getWritableDatabase();  
     Cursor cursor = db.rawQuery(selectQuery, null);  
     if (cursor.moveToFirst()) {  
       do {  
         Admin admin = new Admin();  
         admin.setID(Integer.parseInt(cursor.getString(0)));  
         admin.setName(cursor.getString(1));  
         admin.setUsername(cursor.getString(2));  
         admin.setPassword(cursor.getString(3));  
         adminList.add(admin);  
       } while (cursor.moveToNext());  
     }  
     return adminList;  
   }    
   public int getExistCount(String keyword) {  
     String countQuery = "SELECT * FROM " + DB_TABLE+" WHERE "+KEY_NAME+" = '"+keyword+"'";  
     SQLiteDatabase db = this.getReadableDatabase();  
     Cursor cursor = db.rawQuery(countQuery, null);  
     cursor.close();  
     return cursor.getCount();  
   }    
 }  

and the second I have file RSList.java. It's a class that used to handle table rs_list. here's the code:

 package com.ademahendra.database;  
 public class RSList {  
   int _id;  
   String _name;  
   String _phone_number;  
   String _email;  
   String _address;  
   String _image;  
   String _glat;  
   String _glon;  
   String _typers;  
   String _web;  
   String _status;  
   public RSList(){  
   }  
   public RSList(int id, String name, String _phone_number, String _email, String _address, String _image, String _glat, String _glon,  
   String _typers, String _web, String _status){  
     this._id = id;  
     this._name = name;  
     this._phone_number = _phone_number;  
     this._email = _email;  
     this._address = _address;  
     this._image = _image;  
     this._glat = _glat;  
     this._glon = _glon;  
     this._typers = _typers;  
     this._web = _web;  
     this._status = _status;  
   }  
   public RSList(String name, String _phone_number, String _email, String _address, String _image, String _glat, String _glon,  
          String _typers, String _web, String _status){  
     this._name = name;  
     this._phone_number = _phone_number;  
     this._email = _email;  
     this._address = _address;  
     this._image = _image;  
     this._glat = _glat;  
     this._glon = _glon;  
     this._typers = _typers;  
     this._web = _web;  
     this._status = _status;  
   }  
   public int getID(){  
     return this._id;  
   }  
   public void setID(int id){  
     this._id = id;  
   }  
   public String getName(){  
     return this._name;  
   }  
   public void setName(String name){  
     this._name = name;  
   }  
   public String getPhoneNumber(){  
     return this._phone_number;  
   }  
   public void setPhoneNumber(String phone_number){  
     this._phone_number = phone_number;  
   }  
   public String getAddress(){  
     return this._address;  
   }  
   public void setAddress(String address){  
     this._address = address;  
   }    
   public String getEmail(){  
     return this._email;  
   }  
   public void setEmail(String email){  
     this._email = email;  
   }   
   public String getImage(){  
     return this._address;  
   }  
   public void setImage(String image){  
     this._image = image;  
   }   
   public String getGlat(){  
     return this._glat;  
   }  
   public void setGlat(String glat){  
     this._glat = glat;  
   }   
   public String getGlon(){  
     return this._glon;  
   }  
   public void setGlon(String glon){  
     this._glon = glon;  
   }  
   public String getTypers(){  
     return this._typers;  
   }  
   public void setTypers(String typers){  
     this._typers = typers;  
   }   
   public String getWeb(){  
     return this._web;  
   }  
   public void setWeb(String web){  
     this._web = web;  
   }   
   public String getStatus(){  
     return this._status;  
   }  
   public void setStatus(String status){  
     this._status = status;  
   }   
 }  

and this is how to call the class for doing some query.


 package com.ademahendra;  
 import com.rumahsakitterdekat.database.DatabaseHandler;  
 import com.rumahsakitterdekat.database.RSList;  
 //...................... and another import ....................................  
 public class MainActivity extends Activity {  
 //...............................................................  
   private DatabaseHandler db;  
 //...............................................................  
   protected void onCreate(Bundle savedInstanceState) {  
     super.onCreate(savedInstanceState);      
     setContentView(R.layout.MainActivity);  
     db = new DatabaseHandler(this);  
     addDB();  
   }  
   public void addDB(){  
        List<RSList> rslist = db.getAllRSListsByName("My Hospita");     
        int total = rslist.toArray().length;  
        if(total < 1){  
             db.addRSList(new RSList("My Hospital", "+62 081 999999999", "The Address", "", "", "-7.982653", "112.628218", "Public Hospital", "", ""));  
        }  
   }  
 }  


No comments:

Post a Comment