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数据库（名字为前面定义的数据库名）。
