1.What is SQLite
SQLite is a public domain RDBMS database engine, and is an Open Source Database which is embedded into Android and Mac OS X.
SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition, it requires only little memory at runtime (approx. 250 KByte).
SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before saving them in the database.
More information about SQLite can be found on the SQLite website:
http://www.sqlite.org.
SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. In addition, it requires only little memory at runtime (approx. 250 KByte).
SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before saving them in the database.
More information about SQLite can be found on the SQLite website:
http://www.sqlite.org.
2.Download page
3.SQLite on Windows
sqlite-shell-win32-x86-3071700.zip
sqlite-dll-win32-x86-3071700.zip
sqlite-analyzer-win32-x86-3071700.zip
4.SQLite on Linux
/* For Debian or Ubuntu /*
$ sudo apt-get install sqlite3 sqlite3-dev
/* For RedHat, CentOS, or Fedora/*
$ yum install SQLite3 sqlite3-dev
5.SQLite on Mac OS X
It is already intalled, and is available on Mac OS X.
6. SQLite in Android
SQLite is available on every Android device.Using an SQLite database in Android does not require any database setup or administration.
You only have to define the SQL statements for creating and updating the database.
Afterwards the database is automatically managed for you by the Android platform.
If application creates a database, this database is by default saved in the directory: DATA/data/APP_NAME/databases/FILENAME.
8.Android SQLite Architecture
8.1. Packages
The package android.database contains all general classes for working with databases. android.database.sqlite contains the SQLite specific classes.
8.2. SQLiteOpenHelper
To create and upgrade a database in your application you usually subclass SQLiteOpenHelper.
In this class you need to override the onCreate() and onUpgrade() methods.
onCreate() is called by the framework, if the database does not exists.
onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema.
8.3. SQLiteDatabase
SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.
More specifically SQLiteDatabase provides the insert(), update() and delete() methods.
In addition it provides the execSQL() method, which allows to execute an SQL statement directly.
Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .
rawQuery() directly accepts an SQL select statement as input.
query() provides a structured interface for specifying the SQL query.
SQLiteQueryBuilder is a convenience class that helps to build SQL queries.
For details, you can refer to http://www.vogella.com/articles/AndroidSQLite/article.html
7.Example
Create SQLite Database
sqlite3 test.db
Create table
sqlite> create table mytable(id integer primary key, value text);
Insert data
sqlite> insert into mytable(id, value) values(1, 'Micheal');
sqlite> insert into mytable(id, value) values(2, 'Alice');
View data
sqlite> select * from mytable;
1|Micheal
2|Alice
8.Android SQLite Architecture
8.1. Packages
The package android.database contains all general classes for working with databases. android.database.sqlite contains the SQLite specific classes.
8.2. SQLiteOpenHelper
To create and upgrade a database in your application you usually subclass SQLiteOpenHelper.
In this class you need to override the onCreate() and onUpgrade() methods.
onCreate() is called by the framework, if the database does not exists.
onUpgrade() is called, if the database version is increased in your application code. This method allows you to update the database schema.
8.3. SQLiteDatabase
SQLiteDatabase is the base class for working with a SQLite database in Android and provides methods to open, query, update and close the database.
More specifically SQLiteDatabase provides the insert(), update() and delete() methods.
In addition it provides the execSQL() method, which allows to execute an SQL statement directly.
Queries can be created via the rawQuery() and query() methods or via the SQLiteQueryBuilder class .
rawQuery() directly accepts an SQL select statement as input.
query() provides a structured interface for specifying the SQL query.
SQLiteQueryBuilder is a convenience class that helps to build SQL queries.
For details, you can refer to http://www.vogella.com/articles/AndroidSQLite/article.html
9.Using SQLite Sample
The following demonstrates how to work with an SQLite database.Add following code in Android Activity class to study SQLite's using.
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
//open and create test.db
SQLiteDatabase db = openOrCreateDatabase("test.db", Context.MODE_PRIVATE, null);
db.execSQL("DROP TABLE IF EXISTS person");
//insert person table
db.execSQL("CREATE TABLE person (_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR, age SMALLINT)");
Person person = new Person();
person.name = "john";
person.age = 30;
//insert data
db.execSQL("INSERT INTO person VALUES (NULL, ?, ?)", new Object[]{person.name, person.age});
person.name = "david";
person.age = 33;
//save ContentValues key-value data
ContentValues cv = new ContentValues();
cv.put("name", person.name);
cv.put("age", person.age);
//insert ContentValues data
db.insert("person", null, cv);
cv = new ContentValues();
cv.put("age", 35);
//update data
db.update("person", cv, "name = ?", new String[]{"john"});
Cursor c = db.rawQuery("SELECT * FROM person WHERE age >= ?", new String[]{"33"});
while (c.moveToNext()) {
int _id = c.getInt(c.getColumnIndex("_id"));
String name = c.getString(c.getColumnIndex("name"));
int age = c.getInt(c.getColumnIndex("age"));
Log.i("db", "_id=>" + _id + ", name=>" + name + ", age=>" + age);
}
c.close();
//delete data
db.delete("person", "age < ?", new String[]{"35"});
//close db
db.close();
//delete test.db
// deleteDatabase("test.db");
}
After running the app, we can see "test.db" file in /data/data/[PACKAGE_NAME]/databases folder.
Other Link:
http://search.cpan.org/~msergeant/DBD-SQLite-0.31/lib/DBD/SQLite.pm
http://developer.android.com/reference/android/database/sqlite/package-summary.html
http://developer.android.com/reference/android/database/sqlite/package-summary.html
Some Tools for pre-seeded databases:
-MesaSQLite
-SQLiteManager
-SQLite Database Browser
