Android作为一个应用在移动设备上的操作系统，自然也就少不了数据的存储。然而SQLite作为一个轻型的关系型数据库，基于其轻量、跨平台、多语言接口及安全性等诸多因数考虑，因而Android较大的数据存储采用了SQLite。SQLite与大多数关系型数据库一样都遵循ACID，语法也非常相似。只要您懂得mysql、sqlserver等关系型数据库的操作，只要查看下SQLite的官方文档便可快速上手。SQLite语法您可通过http://sqlite.org/lang.html进行查看。
接下来看看Android是怎样操作SQLite的吧。Android下只要扩展SQLiteOpenHelper接口，并实现其Create和update方法便可以便捷地对SQLite进行管理。下面看下数据库管理类。
package com.example.ibm.myappsqllite; import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper; /**
* Created by ibm on 2015/9/11.
*/
public class Db extends SQLiteOpenHelper {
public Db(Context context, int version) {
super(context, "db", null, version);//可以根据最后一个参数的version来完成表的自动创建和升级
} @Override
public void onCreate(SQLiteDatabase db) {
String sql = "CREATE TABLE user(" +
"id integer primary key autoincrement, " +
"name TEXT DEFAULT \"\"," + "sex TEXT DEFAULT \"\")";
System.out.println("创建数据库：" + sql);
db.execSQL(sql);
} @Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {//更新数据库
String sql = "ALTER TABLE user ADD COLUMN mark TEXT DEFAULT \"\"";
System.out.println("更新数据库：" + sql);
db.execSQL(sql);
}
}
在MainActivity.java中的onCreate方法添加如下代码。
Db db = new Db(this, 1);
//清空数据
SQLiteDatabase sqLiteDb = db.getWritableDatabase();
System.out.println("------------清空表数据------------");
sqLiteDb.delete("user", null, null);
System.out.println("------------充值表自增ID------------");
sqLiteDb.execSQL("update sqlite_sequence SET seq = 0 where name ='user';");//重置id
sqLiteDb.close();
// 写入数据
System.out.println("------------写入数据------------");
sqLiteDb = db.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("name", "王五");
cv.put("sex", "男");
sqLiteDb.insert("user", null, cv);
cv = new ContentValues();
cv.put("name", "李利");
cv.put("sex", "女");
sqLiteDb.insert("user", null, cv);
sqLiteDb.close();
// 查询数据
db = new Db(this, 2);
System.out.println("------------查询数据1------------");
SQLiteDatabase sqLiteDbup = db.getReadableDatabase();
Cursor c = sqLiteDbup.query("user", new String[]{"id", "name", "sex"}, null, null, null, null, null);
while (c.moveToNext()) {
Integer id = c.getInt(c.getColumnIndex("id"));
String name = c.getString(c.getColumnIndex("name"));
String sex = c.getString(c.getColumnIndex("sex"));
System.out.println(String.format("id: %d, name: %s, sex: %s", id, name, sex));
}
sqLiteDbup.close();
//更新数据
System.out.println("------------更新数据------------");
SQLiteDatabase sqldb = db.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("sex", "gay");
contentValues.put("mark", "wang wu is gay");
String[] whereValues = {"王五"};
sqldb.update("user", contentValues, "name = ?", whereValues);
sqldb.close();
// 查询数据
System.out.println("------------查询数据2------------");
sqLiteDbup = db.getReadableDatabase();
c = sqLiteDbup.query("user", new String[]{"id", "name", "sex", "mark"}, null, null, null, null, null);
while (c.moveToNext()) {
Integer id = c.getInt(c.getColumnIndex("id"));
String name = c.getString(c.getColumnIndex("name"));
String sex = c.getString(c.getColumnIndex("sex"));
String mark = c.getString(c.getColumnIndex("mark"));
System.out.println(String.format("id: %d, name: %s, sex: %s, mark: %s", id, name, sex, mark));
}
sqLiteDbup.close();
//删除数据
System.out.println("------------删除数据------------");
sqldb = db.getWritableDatabase();
String[] delWhere = {"李利"};
sqldb.delete("user", "name = ?", delWhere);
sqldb.close();
// 查询数据
System.out.println("------------查询数据3------------");
sqLiteDbup = db.getReadableDatabase();
c = sqLiteDbup.query("user", new String[]{"id", "name", "sex", "mark"}, null, null, null, null, null);
while (c.moveToNext()) {
Integer id = c.getInt(c.getColumnIndex("id"));
String name = c.getString(c.getColumnIndex("name"));
String sex = c.getString(c.getColumnIndex("sex"));
String mark = c.getString(c.getColumnIndex("mark"));
System.out.println(String.format("id: %d, name: %s, sex: %s, mark: %s", id, name, sex, mark));
}
sqLiteDbup.close();
db.close();
将应用运行到虚拟机后，你可以看到如下结果
09-11 18:58:29.460 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ 创建数据库：CREATE TABLE user(id integer primary key autoincrement, name TEXT DEFAULT "",sex TEXT DEFAULT "")
09-11 18:58:29.500 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ ------------清空表数据------------
09-11 18:58:29.532 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ ------------充值表自增ID------------
09-11 18:58:29.532 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ ------------写入数据------------
09-11 18:58:29.616 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ ------------查询数据1------------
09-11 18:58:29.628 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ 更新数据库：ALTER TABLE user ADD COLUMN mark TEXT DEFAULT ""
09-11 18:58:29.640 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ id: 1, name: 王五, sex: 男
09-11 18:58:29.644 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ id: 2, name: 李利, sex: 女
09-11 18:58:29.644 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ ------------更新数据------------
09-11 18:58:29.668 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ ------------查询数据2------------
09-11 18:58:29.684 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ id: 1, name: 王五, sex: gay, mark: wang wu is gay
09-11 18:58:29.684 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ id: 2, name: 李利, sex: 女, mark:
09-11 18:58:29.688 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ ------------删除数据------------
09-11 18:58:29.708 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ ------------查询数据3------------
09-11 18:58:29.716 6647-6647/com.example.ibm.myappsqllite I/System.out﹕ id: 1, name: 王五, sex: gay, mark: wang wu is gay
此时可以查看下模拟器中的/data/data/项目包/databases/目录下，已经存在了一个db数据库（名字为前面定义的数据库名）。