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"字段记录下每个表的自增长字段目前已使用的最大值.
如果创建表时， 不指定"_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, 数据获取层.
这篇文章写的很简洁, 以后写数据库类, 按照这个模式去实现就可以了.
