The following two tabs change content below.
Hi, I have written and developed this site to share my experience and ideas with other colleagues. I also started to prepare interview questions and answers for job seekers. I hope it will help you a lot.

What is SQLite?
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process.The SQLite project was started on 2000. The future is always hard to predict, but the intent of the developers is to support SQLite through the year 2050.

How to use SQLite in android?
SQLite is already embedded with Android. So APIs you’ll need to use a database on Android are available in the android.database.sqlite package.

 

I have integrated all CRUD operations for SQLite and shown on the UI. Full application/code you can download from here SQLiteApplication

 

Define database

A useful set of APIs is available in the SQLiteOpenHelper class. When you use this class to obtain references to your database, the system performs the potentially long-running operations of creating and updating the database only when needed and not during app startup. All you need to do is call getWritableDatabase() or getReadableDatabase().

Note: Because they can be long-running, be sure that you call getWritableDatabase() or getReadableDatabase() in a background thread, such as with AsyncTask or IntentService.

SQLiteOpenHelper class is used for database creation and database version management. For performing any database operation, you have to provide the implementation of onCreate() and onUpgrade() methods of SQLiteOpenHelper class. Below is the code:

public DatabaseHandler(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
    String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_ADDRESS + "("+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"+ KEY_ADDRESS+ " TEXT" + ")";
    sqLiteDatabase.execSQL(CREATE_CONTACTS_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_ADDRESS);
    onCreate(sqLiteDatabase);
}

Next useful class is a SQLiteDatabase. For database CRUD operation will use SQLiteDatabase class. For example I am showing all CRUD operation on “Address” like add new address, update, delete and list of already stored address.

Address.java: Used as a model class for all operations

package com.kpblog.sqliteapplication.model;

/**
 * Created by Khushvinders on 05-Feb-17.
 */

public class Address {
    private int id;
    private String name;
    private String address;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

Add new address:

public void addNewAddress(Address address) {
     SQLiteDatabase db = this.getWritableDatabase();

     ContentValues values = new ContentValues();
     values.put(KEY_NAME, address.getName());
     values.put(KEY_ADDRESS, address.getAddress());

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

Get all saved records:

public List<Address> getAllAddress() {
    List<Address> addressList = new ArrayList<Address>();
    // Select All Query
    String selectQuery = "SELECT  * FROM " + TABLE_ADDRESS;
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    // Getting the address list which we already into our database
    if (cursor.moveToFirst()) {
        do {
            Address address = new Address();
            address.setId(Integer.parseInt(cursor.getString(0)));
            address.setName(cursor.getString(1));
            address.setAddress(cursor.getString(2));
            // Adding contact to list
            addressList.add(address);
            Log.d("Address: ", address.getId()+" , "+address.getName()+" , "+address.getAddress());
        } while (cursor.moveToNext());
    }

    // return contact list
    return addressList;
}

 

For address update:

// Updating the address based on address ID
public int updateAddress(Address address) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_NAME, address.getName());
    values.put(KEY_ADDRESS, address.getAddress());
    return db.update(TABLE_ADDRESS, values, KEY_ID + " = ?", new String[] { String.valueOf(address.getId()) });
}

For delete:

// Deleting single address based on ID
public void deleteAddress(Address address) {
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_ADDRESS, KEY_ID + " = ?", new String[] { String.valueOf(address.getId()) });
    db.close();
}

 

Below shared some screenshots of the application. 

 

addresslist

 

After delete first record:

 

deleteaddress

I have integrated all CRUD operations for SQLite and shown on the UI. Full application/code you can download from here SQLiteApplication

 

4,088 total views, 8 views today

One thought on “SQLite android tutorial with CRUD operations”

Leave a Reply

Your email address will not be published. Required fields are marked *