SQLite开发精要

作者: ahking17 | 来源:发表于2017-03-17 12:10 被阅读45次

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, 数据获取层.
这篇文章写的很简洁, 以后写数据库类, 按照这个模式去实现就可以了.

SQLite开发精要

1. SQLite数据库介绍

2. SQLite命令行操作

3. 定义数据库的元数据

4. 使用SQLiteOpenHelper创建数据库

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

6. 使用原生SQL语句

7. 使用事务

游戏玩家管理案例

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

Android 开发精要系列文章