[go: up one dir, main page]
Scribd
Upload
2 views23 pages

sqlite

Android databases utilize SQLite for structured data persistence, allowing applications to create and manage their own databases. SQLite is a lightweight, serverless relational database that supports dynamic typing and stores data in a single file, making it distinct from traditional databases. The document also covers database manipulation methods, including creation, insertion, updating, and deletion using both raw and parameterized queries, along with the role of helper classes like SQLiteOpenHelper.

Uploaded by

jasinjoji6
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views23 pages

sqlite

Android databases utilize SQLite for structured data persistence, allowing applications to create and manage their own databases. SQLite is a lightweight, serverless relational database that supports dynamic typing and stores data in a single file, making it distinct from traditional databases. The document also covers database manipulation methods, including creation, insertion, updating, and deletion using both raw and parameterized queries, along with the role of helper classes like SQLiteOpenHelper.

Uploaded by

jasinjoji6
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 23

INTRODUCING ANDROID DATABASES

▪Android provides structured data persistence through a combination of


SQLite databases and Content Providers.
▪ SQLite databases can be used to store application data using a managed,
structured approach.
▪ Android offers a full SQLite relational database library.
▪Every application can create its own databases over which it has complete
control
▪ Having created your underlying data store, Content Providers offer a generic,
well-defined interface for using and sharing data that provides a consistent
abstraction from the underlying data source.
▪SQLite is a well-regarded relational database management system (RDBMS).
▪ It is:
▪ Open-source
▪ Standards-compliant
▪ Lightweight
▪ Single-tier
▪It has been implemented as a compact C library that’s included as part of the
Android software stack
▪By being implemented as a library, rather than running as a separate ongoing
process, each SQLite database is an integrated part of the application that created
it.
▪This reduces external dependencies, minimizes latency, and simplifies
transaction locking and synchronization.
▪SQLite is a opensource SQL database that stores data to a text file on a
device.
▪ Android comes in with built in SQLite database implementation.
▪SQLite supports all the relational database features. In order to access this
database, you don't need to establish any kind of connections for it like
JDBC,ODBC e.t.c
SQLite is not like any other database

▪Though SQLite offers quite an impressive feature set given its size, it
differs in many aspects from a conventional database system:
▪SQLite is serverless
▪SQLite stores data in one database file
▪SQLite offers only a few data types
▪SQLite uses manifest typing instead of static types
▪SQLite has no fixed column length
▪SQLite uses cross-platform database files
SQLite data types
SQLite data types
▪SQLite offers fewer datatypes
▪If you use other types (like varchar) in your CREATE TABLE statement SQLite
maps them as closely as possible to any of these types.
▪Most SQL database uses static, rigid typing. With static typing, the datatype of a
value is determined by its container - the particular column in which the value is
stored.
▪SQLite uses a more general dynamic type system.
▪ In SQLite, the datatype of a value is associated with the value itself, not with its
container.
▪SQLite does not have a separate Boolean storage class. Instead, Boolean values are
stored as integers 0 (false) and 1 (true).
SQLite data types

▪SQLite does not have a storage class set aside for storing dates and/or times. Instead,
the built-in Date And Time Functions of SQLite are capable of storing dates and
times as TEXT, REAL, or INTEGER values:

▪BLOB stands for a binary large object that is a collection of binary data stored as a
value in the database. By using the BLOB, you can store the documents, images, and
other multimedia files in the database
Database manipulation using SQLLite
▪Database - Package
▪The main package is android.database.sqlite that contains the classes to manage
your own databases
▪Database - Creation
▪In order to create a database you just need to call this method
openOrCreateDatabase with your database name and mode as a parameter.
▪ It returns an instance of SQLite database which you have to receive in your own
object.
Database manipulation using SQLLite
▪Database - Creation
▪Syntax
▪SQLiteDatabase mydatabase = openOrCreateDatabase("your database
name",MODE_PRIVATE,null);
▪Table creation
▪mydatabase.execSQL("CREATE TABLE IF NOT EXISTS testtable(Username
text, Password text ) ;");
Database manipulation
▪To perform insert, read, delete, update operation there are two different ways:
▪Write parameterized queries (Recommended)
▪Write raw queries
▪Parameterized Queries: These are those queries which are performed using
inbuilt functions to insert, read, delete or update data. These operation related
functions are provided in SQLiteDatabase class.
▪Raw Queries: These are simple sql queries similar to other databases like
MySql, Sql Server etc , In this case user will have to write query as text and
passed the query string in rawQuery(String sql ,String [] selectionArgs) or
execSQL(String sql,Object [] bindArgs) method to perform operations.
Example of raw query to insert data:
◦ mydatabase.execSQL("INSERT INTO testtable VALUES('admin','admin');");

To perform insert operation using parameterize query


◦ call insert function available in SQLiteDatabase class
◦ insert() function has three parameters like public long insert(String tableName,String
nullColumnHack,ContentValues values)
◦ where tableName is name of table in which data to be inserted.
◦ NullColumnHack may be passed null, it require table column value in case we don’t put
column name in ContentValues object so a null value must be inserted for that particular
column
◦ ContentValues :values that needs to be inserted into the table
public void addItem(Item item) {
SQLiteDatabase db = getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("name",item.name);
// name - column
contentValues.put("description",item.description);
// description is column in items table, item.description has value for description
db.insert("Items", null, contentValues);//Items is table name
db.close();
}
Update
Example of raw query
String updateQuery ="UPDATE myTable SET salary = 5000 WHERE id=5”;
dbManager.RawQuery(updateQuery, null);
Parameterized query
Update function is available in SQLiteDatabase class
public int update(String tableName,ContentValues contentValues,String
whereClause,String[] whereArgs)
whereClause tells the database where to update data in table, it’s recommended to
pass ?s (questions) along with column name in whereClause String.
Similarly whereArgs array will contain values for those columns against ?s
Update function will return number of rows affected if success, 0 otherwise.
Update
public void updateItem(Item item) {
SQLiteDatabase db = getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("id", item.id);
contentValues.put("name", item.name);
contentValues.put("description", item.description);
String whereClause = "id=?";
String whereArgs[] = {item.id.toString()};
db.update("Items", contentValues, whereClause, whereArgs);
}
Delete
▪Example of raw query
String deleteQuery =“DELETE myTable WHERE id=5”;
dbManager.RawQuery(deleteQuery, null);
▪Parameterized query
▪Delete function is available in SQLiteDatabase class
▪public int delete(String tableName,String whereClause,String [] whereArgs)
▪Here whereClause is optional, passing null will delete all rows in table.
▪Delete function will return number of affected row if whereClause passed
otherwise will return 0.
▪If you want to remove all rows and require count of deleted ones also then
pass 1 as whereClause.
Delete
Parameterized query
.

public void deleteItem(Item item) {


SQLiteDatabase db = getWritableDatabase();
String whereClause = "id=?";
String whereArgs[] = {item.id.toString()};
db.delete("Items", whereClause, whereArgs);
}
Database - Helper class
▪For managing all the operations related to the database and for version
management, a helper class has been given and is called SQLiteOpenHelper.
▪ It automatically manages the creation and update of the database.
▪ Syntax

public class DBHelper extends SQLiteOpenHelper {


public DBHelper(){
super(context,DATABASE_NAME,null,1);
}
public void onCreate(SQLiteDatabase db) {}
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion)
{}
}
Android Database Classes
Java classes which are responsible to provide access to the SQLite functions

1. SQLite Database: This is the Android’s Java interface to its relational database,
SQLite. It also supports an SQL or Structured Query Language implementation which is
rich enough to handle any requirement for mobile application.
2.Cursor: A cursor is a container which holds the results of a database query and also
supports the commonly used MVC design pattern.
Cursors are very much similar to the JDBC result sets which returns the value of any
database query. A cursor also has the ability to represent multiple objects without
creating instance for each and every one.
The cursor returns the values depending upon the current position of the cursor index,
which we can increase by calling the method Cursor.moveToNext, or decrease by
calling Cursor.moveToPrevious as we need
Android Database Classes
3. SQLiteOpenHelper: The open helper provides a life cycle framework to
create and upgrade our application database. This class is used to provide
assistance in the critical task which transitions the data from one version of an
application to a new set of database tables in a newer version of same or another
application.
4. SQLiteQueryBuilder: The query builder provides a high-level abstraction to
create SQLite queries to be used in Android applications. In fact we can simplify
the task of writing queries for our application and it also saves an enormous
amount of time.

You might also like