SQLite Database crud operations in android

SQLite Database

• SQLite is a opensource SQL database that stores data in the form of file on a device.
• Android comes in with worked in SQLite database usage.
• SQLite supports all the relational database features to store data permanently.
• In order to access this database, you don't need to establish any kind of connections for it like JDBC,ODBC, etc.
• Even you don’t need to install any Database Server.
• Android store your databases in private file space of your application.
• Your data is secure because by default android does not allow any other apps to access your database.
• The main package is android.database.sqlite that contains the classes to manage your own databases.
• All the operations performed on SQLite database of android are synchronized by default.
• This means there is no multithreading issue, but there is a possibility that database operation may take more time to complete.

There are basically two important classes used for database:

• SQLiteOpenHelper

-A helper class to manage database creation and version management.

• SQLiteDatabase

- Exposes methods to manage a SQLite database.

- SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.

Executing Query

We can create table or insert data into table using execSQLmethod defined in SQLiteDatabase class.

• Its syntax is given below:

   execSQL(String sql, Object[] bindArgs)

Example:

db.execSQL("CREATE TABLE IF NOT EXISTS T1(Username VARCHAR, Password VARCHAR);");

db.execSQL("INSERT INTO T1 VALUES('test','1234');");

Fetching Data

• We will call a method for this class called rawQuery and it will return a resultset with the cursor pointing the table.
• We can move the cursor forward and retrieve the data.

Example:

Cursor resultSet = mydatbase.rawQuery("Select * from T1",null);
resultSet.moveToFirst();
String username = resultSet.getString(0);

Cursor interface methods

Creating Table

Create a query as a string containing the SQLite CREATE statement, use the execSQL( ) method to execute it.

String createUser = "CREAT TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, fname TEXT, lname TEXT);
myDatabase.execSQL(createUser);

insert( )

long insert(String table, String nullColumnHack, ContentValues values)

import android.content.ContentValues;

ContentValues values = new ContentValues( );

values.put("firstname" , "Sarthak");

values.put("lastname" , "Varshney");

long newUserID = myDatabase.insert("tbl_users" , "" , values);

Update( )

int update(String table, ContentValues values, String whereClause, String[ ] whereArgs)

public void updateBookTitle(Integer bookId, String newTitle)

{

ContentValues values = new ContentValues();

values.put("title" , newTitle);

myDatabase.update("tbl_books", values, "id=?", new String[ ] {bookId.toString() } );

}

Delete( ) 

int delete(String table, String whereClause, String[] whereArgs)


public void deleteBook(Integer bookId)

{

myDatabase.delete("tbl_books" , "id=?" , new String[ ] { bookId.toString( ) } ) ;

}

Steps

Create a class extending SQLiteOpenHelper

- You need to define a Parameterized Constructor accepting context.

- Also, you need to override two methods onCreate( ) and onUpdate( ).

• SQLiteDatabase

- Create methods to create, delete, execute SQL commands with the help of query( ), insert( ), delete( ), update( ), etc.

SQLiteOpenHelper

public class SQLiteOpenHelperMy extends SQLiteOpenHelper
{
SQLiteDatabase db;
String q = "create table IF NOT EXISTS student
(rollno INTEGER, name TEXT, course TEXT)";
SQLiteOpenHelperMy(Context context, String dbName,
SQLiteDatabase.CursorFactory factory, int
dbVersion)
{
super(context, dbName, factory, dbVersion);
}

Override onCreate() and onUpgrade()

@Override public void onCreate(SQLiteDatabase db)

{

this.db = db; db.execSQL(q);

}
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

{

db.execSQL("drop table if exists student");

onCreate(db);

}

Note: Database and table will not be created until you insert something into table.

Insert Data

public void insertData()

{

db = getWritableDatabase();

ContentValues contentValues= new ContentValues();

contentValues.put("Rollno",1001);

contentValues.put("Name","Sarthak");

contentValues.put("Course","MCA");

db.insert("student",null,contentValues);

}

View Data

public String viewData()

{

db = getReadableDatabase();

StringBuilder s = new StringBuilder();

Cursor cursor= db.rawQuery("select * from student", null);

while (cursor.moveToNext())

  {

   s.append(cursor.getString(0)+"\t");

   s.append(cursor.getString(1)+"\t");

   s.append(cursor.getString(2)+"\n");

  } return s.toString();

}

Update Data

public void updateData()

{

db = getWritableDatabase();

ContentValues contentValues= new ContentValues();

contentValues.put("rollno",1001);

contentValues.put("name","Sarthak Varshney");

contentValues.put("course","MCA");

db.update("student",contentValues,"rollno = ?",new String[]{"1001"});

}

Delete Data

public boolean deleteData()

{

db = getWritableDatabase();

db.delete("student","rollno = ?",new String[]{"1001"});

return true;

}