SQLite开发精要

2017-03-17 本文已影响45人 ahking17

1. SQLite数据库介绍

开源, 支持NULL, INTEGER, REAL(浮点数)， TEXT(字符串), BLOB(二进制数据)这5种类型.
有一个特点是，往某个字段存储数据时的实际类型可以和声明类型不一致，例如, 字段声明类型为INTEGER, 但你也可以把一个字符串存入这个字段，不会出现错误，但实际开发中，为避免混乱，并不会这么操作.

2. SQLite命令行操作

adb shell进去后，运行“sqlite3”命令操作数据库，但大部分手机里没有集成这个命令.
ubuntu下，可以把*.db文件adb pull 出来，然后用“sqliteman”工具查看数据库内容.

～/$ sqliteman downloads.db

创建表的原生sql语句:

create table film(_id integer primary key autoincrement, title text, length int, year int, starring text);

refer to:
http://www.jianshu.com/p/96ef716f9fdd

注:
在我的ubuntu上, 因为缺少一些依赖包的原因, sqliteman和sqlitebrowser都装不上, 暂时还解决不了, 想在chrome上找个sqlite查看工具, 也没找到能用的, 最后, 在firefox的add-on里, 搜到了一个插件, Sqlite Manager 可以用, 简直太好了.

主键字段的概念
主键用来唯一的标识某一条记录

几个注意点:

在sql语句字段的声明中, 只要声明为primary key，就说明这是一个主键字段
主键字段默认就包含了not null 和unique两个约束
主键应当是对用户没有意义的
如果在创建表时，声明主键字段时加上autoincrement，那么当添加一条记录时，不用指定这个字段的值，这条记录中这个字段的值被自动设置为"表的行数+1".

Sqlite中，一个自增长字段定义为INTEGER PRIMARY KEY AUTOINCREMENT，那么在插入一个新数据时，只需要将这个字段的值指定为NULL，即可由引擎自动设定其值，引擎会设定为最大的rowid+1。
使用自增长字段的话，引擎会自动产生一个sqlite_sequence表，里面的"seq"字段记录下每个表的自增长字段目前已使用的最大值.

sqlite_1.png

如果创建表时，不指定"_id integer primary key autoincrement", sqlite也会为表默认添加上一个字段"rowid"作为主键, 但不建议这样做，因为对数据库进行一些操作后， rowid的值会被修改.
具体参考这篇文章:
http://blog.sina.com.cn/s/blog_61f4999d0101b752.html
(不建议使用rowid作为sqlite主键)
From the official documentation: “Rowids can change at any time and without notice."

所以实际开发中，创建表的操作，一定要加上“_id integer primary key autoincrement”显式的创建一个字段名为"_id"的自增长主键字段.

3. 定义数据库的元数据

所谓定义数据库的元数据，就是说写一个类，里面定义一些字符串常量, 定义表的名字，以及表中的各个字段的名字.
这样在之后的数据库操作时，引用这些常量名就可以了，这样写代码更加规范.

public class BookmarkColumns implements BaseColumns {
    public static final String TABLE_NAME = "history";
    public static final String URL = "url";
    public static final String VISITS = "visits";
    public static final String DATE = "date";
}

BaseColumns接口中，默认给定了2个字段名.

public interface BaseColumns
{
    /**
     * The unique ID for a row.
     * <P>Type: INTEGER (long)</P>
     */
    public static final String _ID = "_id";

    /**
     * The count of rows in a directory.
     * <P>Type: INTEGER</P>
     */
    public static final String _COUNT = "_count";
}

4. 使用SQLiteOpenHelper创建数据库

public abstract class SQLiteOpenHelper， 是一个抽象类.

你需要写一个子类，实现里面的onCreate(), onUpdate(), onDowngrade()方法.
这个类的目的是，创建*.db文件中的表结构，以及提供getReadableDatabase()和getWritableDatabase() 两个API，获取SQLiteDatabase对象.

public class MyDatabaseHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "pet.db";
    private static final int VERSION = 1;

    private static final String CREATE_TABLE_DOG = "CREATE TABLE dog(_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                            "name TEXT, age INTEGER)";
    private static final String DROP_TABLE_DOG = "DROP TABLE IF EXISTS dog";

    public MyDatabaseHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_DOG);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_TABLE_DOG);
        db.execSQL(CREATE_TABLE_DOG);
    }
}

5. 用SQLiteDatabase提供封装后的API实现, 增删改查数据.

public class DatabaseAdapter {


    public void add(Dog dog) {
        SQLiteDatabase db = dpHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(PetMetaData.DogTable.NAME, dog.getName());
        values.put(PetMetaData.DogTable.AGE, dog.getAge());

        //参数: 表名, null, 数据键值对
        db.insert(PetMetaData.DogTable.TABLE_NAME, null, values);
        db.close();
    }


    public void delete(int id) {
        SQLiteDatabase db = dpHelper.getWritableDatabase();
        String whereClause = PetMetaData.DogTable._ID + "=?";
        String[] whereArgs = {String.valueOf(id)};

        // ? 是占位符
        //参数: 表名, 删除的条件，条件的值
        db.delete(PetMetaData.DogTable.TABLE_NAME, whereClause, whereArgs);
        db.close();
    }

    public void update(Dog dog) {
        SQLiteDatabase db = dpHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(PetMetaData.DogTable.NAME, dog.getName());
        values.put(PetMetaData.DogTable.AGE, dog.getAge());

        String whereClause = PetMetaData.DogTable._ID + "=?";
        String[] whereArgs = {String.valueOf(dog.getId())};

        //参数: 表名, 数据键值对, 条件， 条件的值
        db.update(PetMetaData.DogTable.TABLE_NAME, values, whereClause, whereArgs);
        db.close();
    }

    public ArrayList<Dog> findAll() {
        SQLiteDatabase db = dpHelper.getReadableDatabase();
        String[] columns = {PetMetaData.DogTable._ID, PetMetaData.DogTable.NAME, PetMetaData.DogTable.AGE};

        
        //是否去除重复记录, 表名, 要查询的列， 查询条件， 查询条件的值, 分组条件， 分组条件的值, 排序， 分页条件.
        Cursor c = db.query(true, PetMetaData.DogTable.TABLE_NAME, columns, null, null, null, null, null, null);
    
        ArrayList<Dog> dogs = new ArrayList<Dog>();
        Dog dog = null;
    
        while(c.moveToNext()) {
            dog = new Dog();
            dog.setId(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
        
            dogs.add(dog);
        }

        c.close();
        db.close();
    
        return dogs;
    }

    public Dog findById(int id) {
        SQLiteDatabase db = dpHelper.getReadableDatabase();
        String[] columns = {PetMetaData.DogTable._ID, PetMetaData.DogTable.NAME, PetMetaData.DogTable.AGE};
        //是否去除重复记录, 表名, 要查询的列， 查询条件， 查询条件的值, 分组条件， 分组条件的值, 排序， 分页条件.
        Cursor c = db.query(true, PetMetaData.DogTable.TABLE_NAME, columns, PetMetaData.DogTable._ID+"=?", id, null, null, null, null);

        Dog dog = null;
        if(c.moveToNext()) {
            dog = new Dog();
            dog.setId(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
        }

        c.close();
        db.close();

        return dog;
    }

}

6. 使用原生SQL语句

public void rawAdd(Dog dog) {
    SQLiteDatabase db = dpHelper.getWritableDatabase();
    String sql = "insert into dog(name, age) values (?,?)";
    Object[] args = {dog.getName(), dog.getAge()};
    db.execSQL(sql, args);
    db.close();
}

public void rawDelete(int id) {
    SQLiteDatabase db = dpHelper.getWritableDatabase();
    String sql = "delete from dog where id =?";
    Object[] args = {id};
    db.execSQL(sql, args);
    db.close();
}

public void rawUpdate(Dog dog) {
    SQLiteDatabase db = dpHelper.getWritableDatabase();
    String sql = "update dog set name=?, age=? where id=?";
    Object[] args = {dog.getName(), dog.getAge(), dog.getId()};
    db.execSQL(sql, args);
    db.close();
}

public Dog rawFindById(int id) {
    SQLiteDatabase db = dpHelper.getReadableDatabase();
    String sql = "select _id, name, age from dog where _id=?";
    Cursor c = db.rawQuery(sql, new String[]{String.valueOf(id)});
    Dog dog = null;

    if(c.moveToNext()) {
        dog = new Dog();
        dog.setId(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
        dog.setName(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
        dog.setAge(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));
    }
    c.close();
    db.close();
    return dog;
}

public ArrayList<Dog> rawFindAll() {
    SQLiteDatabase db = dpHelper.getReadableDatabase();
    String sql = "select _id, name, age from dog";
    Cursor c = db.rawQuery(sql,null);
    ArrayList<Dog> dogs = new ArrayList<Dog>();

    Dog dog = null;
    while(c.moveToNext()) {
        dog = new Dog();
        dog.setId(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
        dog.setName(c.getString(c.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
        dog.setAge(c.getInt(c.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));
        dogs.add(add);
    }
    c.close();
    db.close();
    return dogs;
}

这里有一个重要的优化点:
select sql语句中，不要用"星号"，要明示具体查询的列的名字. 因为"星号"是通配符, 在底层构建完整的sql语句时，还是会把*转换为具体的列的名字, 用"星号"会影响一定的性能.

7. 使用事务

当多个sql语句要一起执行时，可以使用事务，要么一起成功，要么一起失败. 使用事务可以提高一些性能.

SQLiteDatabase db = dpHelper.getWritableDatabase();
db.beginTransaction(); //开始使用事务
try {
    db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"tony", 25});
    db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"yoyo", 28});
    db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"ahking", 35});

    //调用到此方法， 底层就是给事务的标志位设置成功标记. 
    //会在执行到db.endTransaction()时提交当前事务， 如果不调用此方法， db.endTransaction()会回滚事务.
    db.setTransactionSuccessful();

} finally {
    db.endTransaction(); //由事务的标志决定是提交事务， 还是回滚事务.
}
db.close();

游戏玩家管理案例

实际开发中, 按照文件下载时间的倒序去查询数据.

String sql = "select * from " + TAB_NAME + " ORDER BY" + " downloadtime" +  " DESC";

refer to:
http://www.runoob.com/sqlite/sqlite-order-by.html

http://wale.oyediran.me/2015/04/02/android-sqlite-dao-design/
Android SQLite DAO Design
DAO的简写: Data Access Object, 数据获取层.
这篇文章写的很简洁, 以后写数据库类, 按照这个模式去实现就可以了.