Android 如何对sqlite数据库进行增删改[insert、update和delete] 操作

旃麒雅

　　在android开发中，我们常常用sqlite来存储用户信息，如何进行sqlite常规操作呢，请参阅下面的示例代码：

package com.db.imgfornote;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;public class DBHelper extends SQLiteOpenHelper {final private static String mDbName="imgfornote";final private static int mDbVersion=1;private static DBHelper mInstance=null;private final static String mTUserPhoto="UserPhoto";final private static String mCreateSqlForNoteClass="create table if not exists NoteClass(classId integer primary key asc autoincrement,className NVARCHAR(100),rowTime timestamp default (datetime('now', 'localtime')))";final private static String mCreateSqlForUserPhoto="create table if not exists UserPhoto(photoId integer primary key asc autoincrement,photoName VARCHAR(200),userPt VARCHAR(200),title VARCHAR(255),classId integer,content NVARCHAR(250),tag NVARCHAR(200),remark text,status integer default 0,rowTime timestamp default (datetime('now', 'localtime')))";final private static String[] mInsertSqlForNoteClass={"insert into NoteClass(className) values('默认分类[私有]');","insert into NoteClass(className) values('读书笔记[私有]');","insert into NoteClass(className) values('电子资料[公开]');"};private DBHelper(Context context, CursorFactory factory) {super(context, mDbName, factory, mDbVersion);}public static DBHelper GetInstance(Context context, SQLiteDatabase.CursorFactory factory){if(mInstance==null){mInstance = new DBHelper(context,factory);}return mInstance;}@Overridepublic void onCreate(SQLiteDatabase db) {// 创建表db.execSQL(mCreateSqlForNoteClass);db.execSQL(mCreateSqlForUserPhoto);//初始化数据for(int i=0;i<mInsertSqlForNoteClass.length;i++)db.execSQL(mInsertSqlForNoteClass);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stub}private Cursor ExecSQLForCursor(String sql, String[] selectionArgs){SQLiteDatabase db =getWritableDatabase();Log.i("ExecSQLForCursor",sql);return db.rawQuery(sql, selectionArgs);}private void ExecSQL(String sql){try{SQLiteDatabase db =getWritableDatabase();ExecSQL(sql,db);}catch(Exception e){Log.e("ExecSQL Exception",e.getMessage());e.printStackTrace();}}private void ExecSQL(String sql,SQLiteDatabase db ){try{db.execSQL(sql);Log.i("ExecSQL",sql);}catch(Exception e){Log.e("ExecSQL Exception",e.getMessage());e.printStackTrace();}}//添加照片信息public long InsertUserPhoto(String photoName,String title){SQLiteDatabase db =getWritableDatabase();ContentValues cv = new ContentValues();cv.put("photoName", photoName);cv.put("title", title);return db.insert(mTUserPhoto, null, cv);}//查询照片信息public Cursor SearchPhoto(int row,String sort){Cursor cur = null;try{String ord = (sort==null|| sort.toLowerCase().startsWith("a"))?"asc":"desc";String sql = "select * from UserPhoto order by photoId "+ord;String[] args = {String.valueOf(row)};if(row>0){sql +=" limit ?";}else{args=null;}cur = ExecSQLForCursor(sql,args);}catch (Exception e) {cur = null;Log.e("SearchPhoto Exception",e.getMessage());e.printStackTrace();}return cur;}//修改照片信息public int UpdateUserPhoto(int photoId,int classId,String title,String content, String tag){SQLiteDatabase db =getWritableDatabase();ContentValues cv = new ContentValues();cv.put("classId", classId);cv.put("title", title);cv.put("content", content);cv.put("tag", tag);String[] args = {String.valueOf(photoId)};return db.update(mTUserPhoto, cv, "photoId=?",args);}//删除照片信息public int DeleteUserPhoto(int photoId){SQLiteDatabase db =getWritableDatabase();String[] args = {String.valueOf(photoId)};return db.delete(mTUserPhoto, "photoId=?", args);}}
主要用到的类：SQLiteOpenHelper、SQLiteDatabase及ContentValues