Alice Blog: SQLite Database Basic Knowledge

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.

2.Download page

http://www.sqlite.org/download.html

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.

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.

Alice Blog

2013年7月2日火曜日

SQLite Database Basic Knowledge

1 件のコメント :