SQLite是android的内置数据库,App开发中应用比较广泛,常见有三种使用场景,原生SQL基于SQLiteDatabase接口、ORM框架、ContentProvider+LoaderManager.

SQL


使用Android SDK提供的SQLiteDatebase等接口,执行原生SQL语句,或调用接口内的方法insert()、update()、query()、delete(),其实质也是根据参数凭借SQL,下面是调用SQLiteDatabase接口方法的Demo,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
public class DBHelper extends SQLiteOpenHelper {

private static final String DB_NAME = "sdmp.db";
private static final int DB_VERSION = 1;

public static final String TABLE_MESSAGE = "user";

public DBHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {

StringBuilder sql = new StringBuilder();
sql.append("CREATE TABLE IF NOT EXISTS ");
sql.append(TABLE_MESSAGE);
sql.append(" (");
sql.append("id INTEGER PRIMARY KEY NOT NULL, ");
sql.append("content TEXT, ");
sql.append("date INTEGER");
sql.append(");");
db.execSQL(sql.toString());
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGE);
onCreate(db);
}
}

定义数据访问类 MessageDao,调用DBHelper与数据库交互,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
public class MessageDao {

private static final String TAG = "MessageDao";

public static final String ID = "id";
public static final String CONTENT = "content";
public static final String DATE = "date";

private String[] MESSAGE_COLUMS = new String[]{"id", "content", "date"};

private Context context;
private DBHelper helper;

public MessageDao(Context context) {
this.context = context;
helper = new DBHelper(context);
}

public void insert(String content, String date) {
SQLiteDatabase db = null;

try {
db = helper.getReadableDatabase();
db.beginTransaction();

ContentValues values = new ContentValues();
values.put(MessageDao.ID, ThreadLocalRandom.current().nextInt(1, 99999 + 1));
values.put(MessageDao.CONTENT, content);
values.put(MessageDao.DATE, date);
db.insertOrThrow(DBHelper.TABLE_MESSAGE, "null", values);

db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
}

public List<Message> getAll() {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query(DBHelper.TABLE_MESSAGE, MESSAGE_COLUMS, null, null, null, null, null);
if (cursor.getCount() == 0) {
return null;
}
List<Message> messages = new ArrayList<>();
while (cursor.moveToNext()) {
messages.add(parseMessage(cursor));
}
return messages;
}

public void delete(int id) {
SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();
db.delete(DBHelper.TABLE_MESSAGE, "id=?", new String[]{String.valueOf(id)});
db.setTransactionSuccessful();
}

public void update(int id, String content) {
ContentValues values = new ContentValues();
values.put(CONTENT, content);

SQLiteDatabase db = helper.getWritableDatabase();
db.beginTransaction();

db.update(DBHelper.TABLE_MESSAGE, values, "id=?", new String[]{String.valueOf(id)});
db.setTransactionSuccessful();
}

private Message parseMessage(Cursor cursor) {
Message message = new Message();
message.setId(cursor.getInt(cursor.getColumnIndex("id")));
message.setContent(cursor.getString(cursor.getColumnIndex("content")));
message.setDate(cursor.getString(cursor.getColumnIndex("date")));
message.print();
return message;
}
}

Message为实体类,接触过JDBC编程的童鞋应该比较熟悉比较业务实体类,数据库查询结果映射到实体类,业务层调用更加方便,

1
2
3
4
5
6
7
8
9

public class Message extends BaseEntity {

int id;
String content;
String date;

... get set
}

使用Android SDK自带的框架写SQL比较麻烦,但是最好开发者都可以经历这一步,踩了这些坑才可以更好的理解其它框架的好,同时练习一下SQL语句的编写。学习总是这么自下向上的。

当然Android自带的数据库操作始终是比较重的方式,手写SQL怎么都不算优美,并且字符串形式的SQL得不到任何编译器的检查,只有在运行时才会报错,增大程序异常的概率,实践证明ORM是比较简洁优雅的数据库交互方案。

ORM


常用ORM框架性能对比

Realm的问题是不是sqlite,有自己独立的存储引擎,因此占用体积会比较大;
优点是专为移动端设计,也有iOS版本

Realm并不是传统的关系型数据库,有很多有用的特性,非常值得学习,官网有中文的wiki…. SqlBrite主要是提供了RxJava响应式的api,可以配合SqlDelight的代码生成一起使用,这两个比较轻量级….greenDao也开始支持RxJava了…..建议多了解greenDao和Realm,这两个的资料也比较多.