Android SQLite Database

SQLite este o bază de date Open Source.  SQLite suportă sintaxă SQL, tranzacții și interogări. Acest tip de bază de date necesită aproximativ 250 KByte de memorie la runtime.

Fiecare device ce are ca sistem Android permite instalarea de aplicații care utilizează baze de date SQLite.

android_sqlite

Structura necesară în a utiliza o bază de date SQLite este destul de simplă

 

images

 

DBAdapater – se ocupă de toate acțiunile din cadrul bazei de date. Aici vor fi create și modificate tabelele și câmpurile acestora. Activitățile vor prelua datele necesare din DBAdapter, iar acesta la râdul său le va prelua din baza de date.

SQLiteOpenHelper – creare, deschidere și update baza de date. Conține obligatoriu două metode onCreate() și onUpgrade() .

SQLiteDatabase – utilizează obiectul SQLiteOpenHelper pentru a deschide baza de date în scopul scrierii. Utilizează metodele standard pentru a manipula datele salvate în tabele, adică insert(), update(), delete() și query() .

Cursor – permite navigarea îm cadrul datelor din tabele. Deține metodele moveToFirst(), moveToNext(), moveToPrevious() și moveToPosition() .

Pentru a exemplifica clasele enumerate mai sus vom realiza o aplicație care salvează contacte într-o bază de date SQLite.

 

Contact.java 


public class Contact {

//private variables
int _id;
String _name;
String _phone_number;

// Empty constructor
public Contact(){

}

// constructor
public Contact(int id, String name, String _phone_number){
this._id = id;
this._name = name;
this._phone_number = _phone_number;
}

// constructor
public Contact(String name, String _phone_number){
this._name = name;
this._phone_number = _phone_number;
}
// getting ID
public int getID(){
return this._id;
}

// setting id
public void setID(int id){
this._id = id;
}

// getting name
public String getName(){
return this._name;
}

// setting name
public void setName(String name){
this._name = name;
}

// getting phone number
public String getPhoneNumber(){
return this._phone_number;
}

// setting phone number
public void setPhoneNumber(String phone_number){
this._phone_number = phone_number;
}
}

 

DatabaseHandler.java

 


public class DatabaseHandler extends SQLiteOpenHelper
{
// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;

// Database Name
private static final String DATABASE_NAME = "contactsManager";

// Contacts table name
private static final String TABLE_CONTACTS = "contacts";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PH_NO = "phone_number";

public DatabaseHandler(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

// Creating Tables
@Override
public void onCreate(SQLiteDatabase db)
{
String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("
+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
+ KEY_PH_NO + " TEXT" + ")";
db.execSQL(CREATE_CONTACTS_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_CONTACTS);

// Create tables again
onCreate(db);
}

/**
* All CRUD(Create, Read, Update, Delete) Operations
*/

// Adding new contact
void addContact(Contact contact)
{
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName()); // Contact Name
values.put(KEY_PH_NO, contact.getPhoneNumber()); // Contact Phone

// Inserting Row
db.insert(TABLE_CONTACTS, null, values);
db.close(); // Closing database connection
}

// Getting single contact
Contact getContact(int id)
{
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
KEY_NAME, KEY_PH_NO }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();

Contact contact = new Contact(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return contact
return contact;
}

// Getting All Contacts
public List<Contact> getAllContacts()
{
List<Contact> contactList = new ArrayList<Contact>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_CONTACTS;

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);

// looping through all rows and adding to list
if (cursor.moveToFirst())
{
do
{
Contact contact = new Contact();
contact.setID(Integer.parseInt(cursor.getString(0)));
contact.setName(cursor.getString(1));
contact.setPhoneNumber(cursor.getString(2));
// Adding contact to list
contactList.add(contact);
} while (cursor.moveToNext());
}

// return contact list
return contactList;
}

// Updating single contact
public int updateContact(Contact contact)
{
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put(KEY_NAME, contact.getName());
values.put(KEY_PH_NO, contact.getPhoneNumber());

// updating row
return db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getID()) });
}

// Deleting single contact
public void deleteContact(Contact contact)
{
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
new String[] { String.valueOf(contact.getID()) });
db.close();
}
// Getting contacts Count
public int getContactsCount()
{
String countQuery = "SELECT * FROM " + TABLE_CONTACTS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();

// return count
return cursor.getCount();
}

}

 

AndroidSQLiteTutorialActivity

 


import java.util.List;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
public class AndroidSQLiteTutorialActivity extends Activity
{
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.main);

DatabaseHandler db = new DatabaseHandler(this);

/**
* CRUD Operations
* */
// Inserting Contacts
Log.d("Insert: ", "Inserting ..");
db.addContact(new Contact("User1", "9100000000"));
db.addContact(new Contact("User2", "9199999999"));
db.addContact(new Contact("User3", "9522222222"));
db.addContact(new Contact("User4", "9533333333"));

// Reading all contacts
Log.e("Reading: ", "Reading all contacts..");
List<Contact> contacts = db.getAllContacts();

for (Contact cn : contacts)
{
String log = "Id: " + cn.getID() + " ,Name: " + cn.getName() + " ,Phone: " + cn.getPhoneNumber();
// Writing Contacts to log
Log.e("Name: ", log);
}
}
}

Advertisements
Android SQLite Database

One thought on “Android SQLite Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s