SQLite存储数据,完成CURD操作
SQLite是android上集成的一个嵌入式关系型数据库,支持NULL,INTEGER,REAL,TEXT和BLOB数据类型,也支持其他数据类型(varchar(n)),但是在保存或运算时,会转成对应五种数据类型。你可以将各种类型的数据保存到任何字段(定义成INTEGER PRIMARY KEY的字段例外,只能存储64位整数),此外,辨析CREATE TABLE语句时候,可以省略字段后面的数据类型信息。例如
CREATE TABLE person (person integer primary key autoincrement,name)
SQLite的分页sql与MySQL类似, select * from person limit 5 offset 3 或 seletc * from person limit 3,5 查询五条记录,跳过前面记录(查询4-8条记录)。
1.创建数据库
SQLiteOpenHelper是一个抽象类,我们可以写一个数据库辅助类(继承SQLiteOpenHelper)来创建和打开数据库。
该类主要有三个方法,一个是继承的构造方法 另外是onCreate(SQLiteDatabase db)方法,第一次创建数据库时调用 onUpgrade(SQLiteDatabse dv, int oldVersion,int new Version)方法数据库版本号变化的时候调用,如下事例。还有一个可以选择性实现的onOpen方法,此方法每次打开数据库的时候调用
public class DBopenHelper extends SQLiteOpenHelper {
public DBopenHelper(Context context) {
//@param context 上下文对象
//@param name 生成的数据库的名字 默认保存在 <包>/databases/
//@param factory 游标工厂
//param version 数据库的版本号(大于零)
super(context, "com.db", null, 2);
}
@Override
//数据库第一次被创建的时候调用
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE person(personid integer primary key autoincrement,name varchar(20))");
}
//数据库文件的版本号发生改变的时候调用
@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL");
}
}
我们可以通过
SQLiteOpenHelper的getReadableDatabase()或者getWritableDatabase()方法得到SQLiteDatabase对象
PS:getReadableDatabase()与getWritableDatabase()的区别
getReadableDatabase()方法内部先尝试调用getWritableDatabase(),若成功,则返回调用getWritableDatabase()得到的返回对象。 当数据库文件达到磁盘空间大小即数据库磁盘空间满了的时候,调用getWritableDatabase()会抛出一个异常,getReadableDatabase()方法内部捕获这个异常,将会以只读方式打开数据库。
2.SQLiteOpenHelper自动创建数据库的原理实现
附上 getWritableDatabase方法的源码可以很清晰的看到创建数据库的原理
public SQLiteDatabase More ...getReadableDatabase() {
187 synchronized (this) {
188 return getDatabaseLocked(false);
189 }
190 }
191
192 private SQLiteDatabase More ...getDatabaseLocked(boolean writable) {
193 if (mDatabase != null) {
194 if (!mDatabase.isOpen()) {
195 // Darn! The user closed the database by calling mDatabase.close().
196 mDatabase = null;
197 } else if (!writable || !mDatabase.isReadOnly()) {
198 // The database is already open for business.
199 return mDatabase;
200 }
201 }
202
203 if (mIsInitializing) {
204 throw new IllegalStateException("getDatabase called recursively");
205 }
206
207 SQLiteDatabase db = mDatabase;
208 try {
209 mIsInitializing = true;
210
211 if (db != null) {
212 if (writable && db.isReadOnly()) {
213 db.reopenReadWrite();
214 }
215 } else if (mName == null) {
216 db = SQLiteDatabase.create(null);
217 } else {
218 try {
219 if (DEBUG_STRICT_READONLY && !writable) {
220 final String path = mContext.getDatabasePath(mName).getPath();
221 db = SQLiteDatabase.openDatabase(path, mFactory,
222 SQLiteDatabase.OPEN_READONLY, mErrorHandler);
223 } else {
224 db = mContext.openOrCreateDatabase(mName, mEnableWriteAheadLogging ?
225 Context.MODE_ENABLE_WRITE_AHEAD_LOGGING : 0,
226 mFactory, mErrorHandler);
227 }
228 } catch (SQLiteException ex) {
229 if (writable) {
230 throw ex;
231 }
232 Log.e(TAG, "Couldn't open " + mName
233 + " for writing (will try read-only):", ex);
234 final String path = mContext.getDatabasePath(mName).getPath();
235 db = SQLiteDatabase.openDatabase(path, mFactory,
236 SQLiteDatabase.OPEN_READONLY, mErrorHandler);
237 }
238 }
239
240 onConfigure(db);
241 //第一次 version的值为 0
242 final int version = db.getVersion();
243 if (version != mNewVersion) {
244 if (db.isReadOnly()) {
245 throw new SQLiteException("Can't upgrade read-only database from version " +
246 db.getVersion() + " to " + mNewVersion + ": " + mName);
247 }
248
249 db.beginTransaction();
250 try {
251 if (version == 0) {
252 onCreate(db);
253 } else {
254 if (version > mNewVersion) {
255 onDowngrade(db, version, mNewVersion);
256 } else {
257 onUpgrade(db, version, mNewVersion);
258 }
259 }
260 db.setVersion(mNewVersion);
261 db.setTransactionSuccessful();
262 } finally {
263 db.endTransaction();
264 }
265 }
266
267 onOpen(db);
268
269 if (db.isReadOnly()) {
270 Log.w(TAG, "Opened " + mName + " in read-only mode");
271 }
272
273 mDatabase = db;
274 return db;
275 } finally {
276 mIsInitializing = false;
277 if (db != null && db != mDatabase) {
278 db.close();
279 }
280 }
281 }
下面是构造器的源码,方便查看参数
public More ...SQLiteOpenHelper(Context context, String name, CursorFactory factory, int version,
99 DatabaseErrorHandler errorHandler) {
100 if (version < 1) throw new IllegalArgumentException("Version must be >= 1, was " + version);
101
102 mContext = context;
103 mName = name;
104 mFactory = factory;
105 mNewVersion = version;
106 mErrorHandler = errorHandler;
107 }
3.数据库的版本变化
查看上面的getWritableDatabase()方法源码可以看见当数据库的版本号变化时候,会调用onUpgrade方法。
4.编写代码完成增删改查
public void save(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("insert into person(name,phone) values(?,?)",
new Object[]{person.getName(),person.getPhone()});
db.close();
}
public void delete(Integer id){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("delete from person where personid=?", new Object[]{id});
}
public void update(Person person){
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
db.execSQL("update person set name=?,phone=? where personid=?",
new Object[]{person.getName(),person.getPhone(),person.getId()});
}
public Person find(Integer id){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person where personid=?", new String[]{id.toString()});
if(cursor.moveToFirst()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("personid"));
String phone = cursor.getString(cursor.getColumnIndex("personid"));
return new Person(name,id,phone);
}
cursor.close();
return null;
}
此外,SQLiteDatabase还提供有增删改查的方法 insert () delete() updata() query()
5.分页语句示例
public List<Person> getScrollData(int offset,int maxResult){
List<Person> persons = new ArrayList<Person>();
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from person order by personid asc limit ?,?",
new String[]{String.valueOf(offset),String.valueOf(maxResult)});
while(cursor.moveToNext()){
int personid = cursor.getInt(cursor.getColumnIndex("personid"));
String name = cursor.getString(cursor.getColumnIndex("name"));
String phone = cursor.getString(cursor.getColumnIndex("phone"));
persons.add(new Person(name,personid,phone));
}
cursor.close();
return persons;
}
public long getCount(){
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(*) from person",null);
cursor.moveToFirst();
long result = cursor.getLong(0);
return result;
}