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